VillageSQL is a drop-in replacement for MySQL with extensions.
All examples on this page work on VillageSQL. Install Now →
ALTER TABLE 会引出一个问题:这条语句在运行时会阻塞应用程序吗?本指南在一张真实的表上直接测量这一点,并且全程有一个并发连接在观察。MySQL 中的模式迁移介绍了如何组织迁移文件并进行版本管理。本指南介绍的是,当其中一个迁移文件针对线上表运行时,这张表实际上会发生什么。
测试设置
本指南中的每个数字都来自同一张表:在本地 VillageSQL 服务器上加载了 3,276,802 行(168.7 MB 数据)的orders 表。
INSERT ... SELECT 翻倍几次,达到几百万行。确切的大小对下面的结论并不重要,但这张表必须足够大,使得一次完整的表重建耗时超过几分之一秒,否则您无法观察到任何与之并发运行的操作。
在线 ALTER 不会阻塞流量
在上面这张 327 万行的表上,用ALGORITHM=INPLACE, LOCK=NONE 添加一个二级索引:
SHOW PROCESSLIST 显示它是一个真实的、正在进行中的操作:
information_schema.INNODB_TRX 把同一张表上另一次单独的 ADD INDEX 显示为一个正在运行的事务:
ALTER 仍处于 altering table 状态时打开第三个连接,从中针对同一张表运行的 INSERT 和 SELECT 都立即完成:
ALTER 完成。这正是 LOCK=NONE 的全部意义:表重建在后台进行,使用 InnoDB 的在线 DDL 日志捕获并发变更,针对该表的 DML 继续在原有的数据结构上执行,直到重建追上进度并完成切换。
ALGORITHM=INSTANT 与 ALGORITHM=INPLACE
并非每个在线操作的代价都相同。用ALGORITHM=INSTANT 在表末尾添加一列只会更改表的元数据;它从不读取或重写任何一行:
ALGORITHM=INPLACE 执行:
INSTANT 比 INPLACE 快了大约 20 倍。INPLACE 仍然需要触及表的数据字典条目,并与任何并发 DML 协调;INSTANT 两者都不需要。这个差距随表的大小而增长:INSTANT 的耗时与行数无关,保持恒定,而 INPLACE 随行数增长。
INSTANT 并非对每个操作都可用。添加带 BLOB/TEXT 默认值的列,或更改现有列的类型,会回退到 INPLACE 或 COPY。在任意位置(包括表的中间)添加列也使用 INSTANT(MySQL 8.0.29+)。
在线 DDL 无能为力时:列类型变更
并非每个ALTER TABLE 都能避免锁。更改列的数据类型需要 MySQL 重写每一行,而 INPLACE 做不到这一点:
ALGORITHM=INSTANT 会以同样的方式失败,只是原因不同:
COPY,它会重建整张表,并在整个过程中持有排他锁。针对同一张表运行它,并在重建期间从第二个连接插入一行,直接展示了这种差别:
ALTER 开始 0.3 秒后发出的并发 INSERT,直到 ALTER 接近完成时才返回:
SHOW PROCESSLIST 显示 ALTER 处于 copy to tmp table 状态,而被阻塞的 INSERT 在它旁边处于 Waiting for table metadata lock 状态。这个状态就是诊断依据:它指出了被卡住的语句,并告诉您它是在锁后面排队,而不是运行得慢。对这同一条语句用 LOCK=NONE 尝试快速失败,正是让您在针对生产表运行它之前就知道,它将在重写期间一直阻塞写入者的方法。
外键与在线 DDL
外键增加的限制与ALGORITHM 和 LOCK 无关。在带有 FOREIGN KEY (order_id) REFERENCES orders(id) 的子表 order_items 上,普通的 ADD COLUMN 仍然能用 ALGORITHM=INSTANT 在毫秒内完成,即使表上带有外键也是如此。但有两个操作无论用什么算法都会直接失败:
删除外键所依赖的父表主键:
用 gh-ost 完成在线 DDL 无法安全完成的变更
上面的MODIFY COLUMN ... ALGORITHM=COPY 就是在线 DDL 无能为力的情形:它需要完整的行重写,但您不想在线上表上持锁这么久。gh-ost 的处理方式是:用新模式构建该表的一个副本(“ghost” 表),然后在后台把现有行复制进去,同时通过流式读取二进制日志(而不是使用触发器)来重放复制期间对原表的每一次写入。最后用一次短暂的原子重命名完成切换。
gh-ost 拒绝迁移被其他表以外键引用的表:上一节中的 order_items 表有一个 FOREIGN KEY (order_id) REFERENCES orders(id),在该约束仍然存在的情况下针对 orders 运行 gh-ost 会立即失败,并报 FATAL found 1 parent-side foreign keys on ... orders. Parent-side foreign keys are not supported. Bailing out。在针对被其他表引用的表运行 gh-ost 之前,请先删除该外键(或传入 --discard-foreign-keys,它会替您删除外键且不会重新创建)。
在删除了 order_items 的外键之后,正是为了这类变更,针对同一张 3,276,802 行的表安装并运行了 gh-ost:
-conf 文件传入,而不是在命令行上使用 --password——命令行上的密码可以被主机上的任何其他用户通过 ps 看到,并且会留在 shell 历史记录中。
不带 --execute 时,gh-ost 以试运行模式运行:它连接、流式读取 binlog,并打印它将用于 ghost 表的 CREATE TABLE 语句,但不做任何更改。带上 --execute 后,它在 13.112 秒内完成了整个迁移,并在过程中打印进度:
orders 表的一条并发 INSERT 在 0.007 秒内返回,gh-ost 自己的进度行也确认它捕获并重放了这次写入(Applied: 1):
SHOW CREATE TABLE orders 显示 customer_id 为 bigint,行数完全吻合(3,276,803,即原来的行数加上迁移期间插入的那一行),并且在迁移中途插入的那一行也带着正确的值存在。gh-ost 把原表重命名为 _orders_del 而不是删除它,因此迁移前的表仍然在那里,供您在确认切换后进行比较或手动删除。
这次运行针对的是一台已经设置了 binlog_format=ROW 和 log_bin=ON 的独立服务器,gh-ost 需要这些设置来流式读取变更;它不适用于基于语句的复制。它还要求表有主键或唯一键。这里两者都不需要额外设置,因为它们是常见的默认值,但在依赖 gh-ost 处理一张您尚未检查过的表之前,请先确认这两点。
常见问题
我如何提前知道 ALTER TABLE 会不会阻塞我的表?
在运行语句之前给它加上ALGORITHM=INPLACE, LOCK=NONE(或只加 LOCK=NONE)。如果 MySQL 能在不阻塞的情况下完成变更,它就会运行。如果不能,它会立即返回错误,而不是运行并阻塞。无论哪种情况,您都能得到一个明确的答案,无需猜测或阅读源代码。
为什么 ALGORITHM=INSTANT 在列类型变更时失败?
INSTANT 只在 MySQL 能把变更表达为纯元数据更新时才有效。更改列的存储类型意味着每一现有行的磁盘表示对新类型来说都是错的,这需要读取并重写各行。这排除了 INSTANT,而且专门对于类型变更,也排除了 INPLACE;只有 COPY 能做到。
gh-ost 需要设置复制吗?
不需要。它读取被迁移服务器的二进制日志,任何设置了binlog_format=ROW 的服务器都会生成该日志,无论它是否有副本。gh-ost 也支持针对副本运行并在主库上切换,这对于非常大或非常热的表是更安全的模式,但那是一种部署选择,而不是让它运行起来的必要条件。
gh-ost 完成后旧表会怎样?
默认情况下,gh-ost 会给原表加上后缀重命名(在上面捕获的运行中是 _del),而不是删除它。它会一直留在模式中,不被使用,直到您自己删除它。
故障排除
另请参阅
- MySQL 中的模式迁移——组织并版本管理那些生成本指南所运行的
ALTER TABLE语句的迁移文件 - 理解 MySQL 索引——MySQL 在在线
ADD INDEX期间构建的索引之后如何影响查询性能 - 在 MySQL 中阅读 EXPLAIN 输出——在迁移完成后检查新添加的索引是否真的被使用