大表加字段的在线 DDL 方案

一条 ALTER TABLE，把整个订单库堵死了

3 月 22 号下午两点，我在 t_order_item 上执行了一条自认为很安全的语句：

ALTER TABLE t_order_item ADD COLUMN promotion_type TINYINT NOT NULL DEFAULT 0 COMMENT '促销类型';

这张表 4200 万行。我预期它跑个十几分钟。结果两分钟后，监控开始炸：订单库活跃连接数从 40 飙到 1200，接口大面积超时。

mysql> SHOW PROCESSLIST;
+------+------+------------------+---------+---------+------+---------------------------------+----------------------------------+
| Id   | User | db               | Command | Time    | State| Info                            |
+------+------+------------------+---------+---------+------+---------------------------------+----------------------------------+
| 8912 | app  | order_db         | Query   |     127 | Waiting for table metadata lock | SELECT * FROM t_order_item WHERE order_id=... |
| 8913 | app  | order_db         | Query   |     126 | Waiting for table metadata lock | SELECT * FROM t_order_item WHERE order_id=... |
| 8914 | app  | order_db         | Query   |     125 | Waiting for table metadata lock | INSERT INTO t_order_item ...     |
| ...  |      |                  |         |         |      |                                 |
| 9021 | root | order_db         | Query   |     131 | Waiting for table metadata lock | ALTER TABLE t_order_item ADD COLUMN ...  |
+------+------+------------------+---------+---------+------+---------------------------------+-------------+

一千多个连接全在等 metadata lock。我赶紧 kill 掉那条 ALTER，连接数在 40 秒内恢复正常。

MDL 锁：为什么一条 DDL 能堵死整张表

MDL（metadata lock）是 MySQL 5.5 引入的表级元数据锁。它的规则是：

• 对表做增删改查（DML）时，自动加 MDL 读锁；
• 对表做结构变更（DDL）时，需要 MDL 写锁；
• 读锁之间兼容，读写锁之间互斥。

灾难发生的过程是这样：

时刻 T1  某个慢查询开始，持有了 t_order_item 的 MDL 读锁
时刻 T2  我的 ALTER 进来，申请 MDL 写锁，被阻塞（要等 T1 结束）
时刻 T3  后续所有对这张表的查询，申请 MDL 读锁，但因为有写锁在排队，
         它们也必须排在我后面！
时刻 T4  连接池打满，整个服务不可用

关键点在 T3：MySQL 为了防止 DDL 被饿死，让后来的读锁排在等待中的写锁之后。所以一旦 DDL 卡住，这张表就彻底不可用了，哪怕 DDL 本身还没开始执行。

我事后在测试环境完整复现了一遍，四个会话：

-- Session A：开启事务，随便查一下，不提交
BEGIN;
SELECT * FROM t_order_item LIMIT 1;
-- 此时 A 持有 MDL 读锁

-- Session B：ALTER，会被阻塞
ALTER TABLE t_order_item ADD COLUMN test_col INT;
-- 卡住，State 变成 Waiting for table metadata lock

-- Session C：普通查询，居然也被阻塞了！
SELECT * FROM t_order_item WHERE id = 1;
-- 卡住！

-- Session D：看谁在等
SELECT * FROM performance_schema.metadata_locks WHERE object_name='t_order_item';

复现完我出了一身冷汗：如果那条慢查询一直不结束，我这条 ALTER 会一直等，整个订单服务会一直挂着。

MySQL 8.0 的 instant add column

先说个好消息：MySQL 8.0.12（2018 年）引入了 ALGORITHM=INSTANT，加列可以只改元数据、不重建表，秒级完成。

ALTER TABLE t_order_item
  ADD COLUMN promotion_type TINYINT NOT NULL DEFAULT 0,
  ALGORITHM=INSTANT;
-- Query OK, 0 rows affected (0.08 sec)

0.08 秒。但它有严格的限制：

• 只能在表的最后加列（8.0.29 才支持任意位置，我们 8.0.22 不行）
• 不能用于 ROW_FORMAT=COMPRESSED 的表
• 不能用于含全文索引的表
• 不能是临时表
• 每张表有 64 次 instant 变更的上限，超了要重建

我们的表是 DYNAMIC 行格式、加列在最后，正好符合。所以这次事故本来根本不该发生，我只要加一个 ALGORITHM=INSTANT 就行了。

但很多变更不支持 INSTANT，比如改列类型、加索引、改字符集，那就得用下面的方案。

pt-osc 和 gh-ost

两个工具的核心思路一样：建一张影子表（新结构），把老表数据拷过去，同时同步增量变更，最后 RENAME 换名。差别在"怎么同步增量"。

pt-online-schema-change：用触发器

$ pt-online-schema-change \
    --host=10.0.1.10 --user=dba --ask-pass \
    D=order_db,t=t_order_item \
    --alter "ADD COLUMN promotion_type TINYINT NOT NULL DEFAULT 0" \
    --chunk-size=2000 \
    --max-load="Threads_running=50" \
    --critical-load="Threads_running=200" \
    --max-lag=2 \
    --check-interval=1 \
    --no-drop-old-table \
    --execute

它在原表上建三个触发器（INSERT / UPDATE / DELETE），把变更实时应用到影子表。

触发器的问题：

• 触发器本身是开销。我们对这张表 QPS 约 800，加了触发器后写延迟涨了约 30%。
• 不能对已有触发器的表使用。MySQL 一个表同一事件只能有一个触发器（5.7 之前），有冲突直接失败。
• 外键依赖处理麻烦，要加 --alter-foreign-keys-method，我们没敢用。
• 拷贝大表时如果撞上业务高峰，只能靠 --max-load 被动暂停，控制粒度粗。

gh-ost：解析 binlog，不用触发器

gh-ost（GitHub Online Schema Transmogrifier）的思路不同：它伪装成一个从库，拉取 binlog，把增量变更应用到影子表。

$ gh-ost \
    --host=10.0.1.10 --port=3306 --user=ghost --password=*** \
    --database=order_db --table=t_order_item \
    --alter="ADD COLUMN promotion_type TINYINT NOT NULL DEFAULT 0" \
    --allow-on-master \
    --chunk-size=2000 \
    --max-lag-millis=1500 \
    --throttle-flag-file=/tmp/ghost.throttle \
    --max-load=Threads_running=80 \
    --critical-load=Threads_running=200 \
    --nice-ratio=0.5 \
    --switch-to-rbr \
    --exact-rowcount \
    --execute

我们最后选了 gh-ost（版本 1.1.2），主要看中三件事：

1. 无触发器，对线上写入几乎无影响；
2. 可以动态限流。创建 /tmp/ghost.throttle 这个文件，gh-ost 立刻暂停，删掉就继续。不用重启进程；
3. 可控的切换。它默认不会自动完成最后的 RENAME，而是等你发指令，可以挑一个低峰窗口执行。

# 暂停
$ touch /tmp/ghost.throttle

# 查看进度
$ echo status | nc -U /tmp/ghost.sock
Copy: 21000000/42134778 49.8%; Applied: 18234; Backlog: 120/1000; Time: 34m12s(total), 34m12s(copy); streamer: mysql-bin.000412:882341203; State: migrating; ETA: 34m30s

# 恢复
$ rm /tmp/ghost.throttle

# 手动触发切换（如果没配 --approve-renamed-columns 等自动选项）
$ echo unpostpone | nc -U /tmp/ghost.sock

实测数据

4200 万行、18 GB 的表，加一个 TINYINT NOT NULL DEFAULT 0 列：

gh-ost 那个 42 分钟里，我在业务高峰时段手动 touch 了两次 throttle 文件，各停了 20 分钟左右，实际拷贝时间只有约 1 小时。

四个必须提前检查的前提

• binlog 必须是 ROW 格式。gh-ost 靠解析行事件工作，STATEMENT 格式拿不到行数据。--switch-to-rbr 可以让它自动切，但切格式本身要重启连接，我们提前改好了配置。
• 表必须有主键（最好是单一整型主键）。没有主键 gh-ost 无法分片拷贝，会直接拒绝。
• 磁盘空间要留够。影子表是全量拷贝，我们的表 18 GB，磁盘当时只剩 60 GB，勉强够。建议留出表大小的 1.5 倍以上。
• 没有外键引用这张表。gh-ost 不支持外键，我们的表没有，但要确认清楚。

顺便说：MySQL 8.0 的 Online DDL 已经很强了

不是所有 DDL 都要上 gh-ost。MySQL 8.0 对很多操作支持 ALGORITHM=INPLACE, LOCK=NONE：

我的判断标准：能用 INSTANT 就用 INSTANT，能用 INPLACE 且表小于 500 万行就直接干，超过 1000 万行或者操作要 COPY，一律上 gh-ost。

小结

• MDL 锁的致命之处：DDL 等待期间，后续所有对该表的读写都会排在它后面。不是"DDL 慢"，是"整张表不可用"。
• MySQL 8.0.12+ 加列用 ALGORITHM=INSTANT，0.08 秒完成。前提是加在最后且不是压缩表。我那次事故完全可以不发生。
• pt-osc 用触发器，写延迟涨 31%；gh-ost 解析 binlog，写延迟只涨 3%。
• gh-ost 的 --throttle-flag-file 是救命功能，业务高峰时 touch 一下就暂停。
• gh-ost 的前提：binlog 为 ROW、表有主键、磁盘留 1.5 倍空间、无外键。
• 小表（500 万行以下）直接 ALGORITHM=INPLACE, LOCK=NONE 就行，别为了用工具而用工具。

事后我把这次事故写进了团队的 DDL 规范，第一条是：任何 DDL 执行前，先跑一遍 SELECT * FROM performance_schema.metadata_locks，确认目标表上没有长事务。第二条：超过 1000 万行的表，一律走 gh-ost。

参考

• 官方文档：database-sql
• Baeldung 上的相关实战
• StackOverflow 上的相关讨论