大表 DDL 变更方案对比：pt-osc vs gh-ost vs MySQL 8.0 INSTANT DDL

读者：DBA / 数据平台工程师 | 环境：MySQL 8.0.32，InnoDB，1 主 2 从（半同步），最大单表 8000 万行 × 2KB，12 个二级索引，业务写入 TPS ~3000，DDL 窗口 02:00–05:00

1. 三种方案原理速览

1.1 pt-online-schema-change (pt-osc 2.4.1)

核心机制三步走：① 创建影子表 __xxx_new，结构与目标 DDL 一致；② 在原表上创建 AFTER INSERT / AFTER UPDATE / AFTER DELETE 三组 trigger，将增量写入同步到影子表；③ 按主键范围分批 INSERT ... SELECT 拷贝存量数据，每批 --chunk-time=0.5s（默认），控制单批耗时；④ 拷贝完成后 RENAME TABLE 一次切换（需短暂持有 MDL 排他锁）。

关键约束：trigger 在高 TPS 场景下每次写入多一次影子表操作，3000 TPS 实测额外延迟约 5–15%；rename 瞬间若原表有长事务持有 MDL，会卡住。

1.2 gh-ost (1.1.6)

核心机制：① 创建影子表；② 无 trigger，通过 ROW 格式 binlog tail 捕获增量变更并 apply 到影子表；③ 按主键范围分批 copy；④ 达到 cut-over 阈值后（默认 19s 窗口内），执行 RENAME 切换。

关键约束：要求 binlog_format=ROW、binlog_row_image=FULL；cut-over 窗口内需短暂锁原表（--cut-over-lock-timeout=19）；binlog tail 有 50–200ms 延迟，对切表一致性有微弱影响。

1.3 MySQL 8.0 INSTANT DDL

核心机制：仅修改 InnoDB 数据字典（DD）中的列元数据，不重写任何数据页、不重建二级索引。支持的操作（8.0.29+）：

• ALTER TABLE t ADD COLUMN c INT DEFAULT 0（末尾或 AFTER 指定位置）
• ALTER TABLE t DROP COLUMN c
• ALTER TABLE t RENAME COLUMN old TO new

不支持：ADD INDEX、DROP INDEX、MODIFY COLUMN（涉及行格式变更）、ADD CONSTRAINT 等。

关键约束：DROP COLUMN 在 8.0 中不可逆（标记为 dropped，实际空间回收需后续 OPTIMIZE TABLE）；INSTANT 操作若失败可安全重试（幂等）。

2. 逐方案对比表

| 维度 | pt-osc 2.4.1 | gh-ost 1.1.6 | MySQL 8.0 INSTANT DDL | |------|-------------|-------------|----------------------| | 锁粒度 | 初始 CREATE + 最终 RENAME 各一次 MDL 排他（毫秒级） | 最终 cut-over 一次 MDL 排他（--cut-over-lock-timeout=19s） | 无锁（仅 DD 元数据修改，< 1ms） | | 对写入影响 | trigger 额外开销：TPS 3000 下延迟 +5–15%，CPU +8–12% | 无 trigger，binlog tail 解析开销可忽略（<2% CPU） | 无 | | 回滚难度 | 中：DROP TABLE __xxx_new + DROP TRIGGER，原表不受影响 | 中：gh-ost --allow-on-master=false --execute 中断后 DROP TABLE 影子表 | 极难：DROP COLUMN 不可逆，只能从备份恢复 | | 适用 DDL 类型 | 几乎所有 ALTER（ADD/DROP/MODIFY/ADD INDEX/RENAME） | 几乎所有 ALTER（同 pt-osc） | 仅 ADD/DROP/RENAME COLUMN（8.0.29+） | | 资源占用 | 中：CPU（trigger + copy）+ IO（批量 INSERT） | 中：CPU（binlog 解析 + copy）+ IO | 极低：仅 DD 写入，无数据页 IO | | 对从库影响 | 无直接压力（不产生额外 binlog） | 无直接压力（tail 的是主库 binlog） | 无 | | 依赖条件 | 需 SUPER 权限创建 trigger | 需 binlog ROW + FULL image，需 SUPER 或 REPLICATION CLIENT | 无额外依赖 | | 8000 万行预估耗时 | 2–5 小时（取决于 chunk 大小与 IO） | 1.5–4 小时（binlog tail 延迟略高） | < 1ms | | 风险点 | trigger 导致写入放大；rename 时 MDL 等待 | cut-over 窗口锁表；binlog 延迟导致数据不一致窗口 | DROP COLUMN 不可逆；不支持 ADD INDEX |

3. 五个边界场景的决策路径

场景 A：大事务期间执行 DDL

现象：业务端有一个 30 秒的 BEGIN ... COMMIT 大事务（如批量导入 5 万行），此时执行 ALTER TABLE ... ADD INDEX。

决策：

1. INSTANT DDL：若操作是 ADD/DROP/RENAME COLUMN → 直接用 INSTANT，大事务不影响（不持有数据页锁）。
2. pt-osc / gh-ost：大事务期间禁止启动。原因：
   • pt-osc 初始 CREATE TABLE LIKE 需 MDL 共享锁，大事务持有的 MDL 排他会阻塞。
   • gh-ost cut-over 的 RENAME 需 MDL 排他，大事务未 COMMIT 前会卡住。
3. 检查命令：

-- 查看当前长事务（执行时间 > 10s）
SELECT id, user, host, db, command, time, state, info
FROM information_schema.processlist
WHERE command != 'Sleep' AND time > 10;

-- InnoDB 活跃事务
SELECT * FROM information_schema.innodb_trx WHERE trx_state = 'RUNNING';

4. 策略：等长事务结束（或主动 KILL 低优先级事务）后再启动 pt-osc / gh-ost。

场景 B：从库半同步回放延迟

现象：gh-ost 切表窗口内，从库回放延迟飙到 12 分钟，业务只读流量打到从库后超时。

根因：gh-ost 的 RENAME TABLE 在主库执行后，binlog 产生 RENAME TABLE 事件。从库半同步回放该事件时，若从库正有大量 DML 回放，RENAME 会排队等待，导致：

• 从库上旧表名仍存在（业务只读流量继续打旧表名）
• 从库上影子表名已存在但数据可能不一致
• 最终 RENAME 完成后，旧表名消失，业务流量 404

决策：

1. gh-ost 优先：在 --cut-over-method 参数中显式选择：
   • atomic（默认）：RENAME 一次完成，从库延迟窗口 = binlog 回放延迟
   • two_steps：先 RENAME 旧表为 _old，再 RENAME 影子表为新名（两次 RENAME，窗口更长但每次锁更短）
2. pt-osc 替代：pt-osc 的 RENAME 也是一次性，但可通过 --set-names 提前通知应用切换连接。
3. INSTANT DDL：RENAME COLUMN 不涉及表名变更，从库不受影响。
4. 监控：

-- 从库延迟监控
SHOW SLAVE STATUS\G  -- Seconds_Behind_Master
-- 或
SELECT * FROM performance_schema.replication_connection_status;

5. 策略：切表前确认从库延迟 < 5s；若延迟 > 30s，暂停 gh-ost（--pause），等延迟追平后 --resume。

场景 C：磁盘 IO 打满

现象：pt-osc 执行 INSERT ... SELECT 批量拷贝时，磁盘 IO util 100%，业务写入延迟从 2ms 飙到 50ms。

根因：pt-osc 默认 --chunk-time=0.5s，每批拷贝约 5000 行（8000 万行 / 16000 批），在 SSD 上 IO 压力大；HDD 上更严重。

决策：

1. gh-ost 更优：gh-ost 的 copy 速率通过 --max-lag-millis 控制（默认 1500ms），会自动降速；pt-osc 的 --chunk-time 是固定值，不会自适应。
2. 降速参数：
   • pt-osc：--chunk-time=1.0 --max-lag=2s
   • gh-ost：--max-lag-millis=1500 --chunk-size=1000
3. IO 限流：

# 用 ionice 降低 pt-osc 进程 IO 优先级
ionice -c2 -n7 pt-online-schema-change ...

# 或用 cgroup 限制
echo "100000" > /sys/fs/cgroup/blkio/pt-osc/blkio.throttle.read_bps_device

4. INSTANT DDL：完全无 IO 压力，首选。
5. 监控：

# 实时 IO 监控
iostat -x 1 5  # 关注 %util, await, svctm

场景 D：INSTANT DDL 不支持的 DDL 类型回退策略

现象：DBA 尝试 ALTER TABLE orders ADD INDEX idx_create_time (create_time)，期望 INSTANT 完成，但 MySQL 8.0 不支持 ADD INDEX 的 INSTANT 模式。

回退策略决策树：

DDL 类型 → 是否支持 INSTANT？
├── ADD COLUMN (末尾/AFTER) → ✅ INSTANT，直接执行
├── DROP COLUMN (8.0.29+) → ✅ INSTANT，直接执行
├── RENAME COLUMN → ✅ INSTANT，直接执行
├── ADD INDEX → ❌ 回退
│   ├── 表行数 < 100 万 → 直接 ALTER（锁 < 1s）
│   ├── 表行数 100 万 ~ 5000 万 → gh-ost（无 trigger 开销，binlog tail）
│   └── 表行数 > 5000 万 且含 10+ 二级索引 → gh-ost（优于 pt-osc，因为：
│       ① 无 trigger 写入放大，3000 TPS 下延迟增量 < 2% vs pt-osc 的 5-15%
│       ② binlog tail 自适应降速，IO 打满时自动 pause
│       ③ cut-over 窗口可配置（--cut-over-lock-timeout），比 pt-osc 的 RENAME 更可控）
├── DROP INDEX → ❌ 回退（同上，按行数选 pt-osc / gh-ost）
└── MODIFY COLUMN (类型变更) → ❌ 回退（必须 copy 数据，选 gh-ost）

关键取舍依据：

• 当表行数 > 5000 万且含 10+ 二级索引时，gh-ost 优于 pt-osc，因为：
  1. pt-osc 的 trigger 在每次 INSERT/UPDATE/DELETE 时都多一次影子表操作，12 个二级索引意味着每次写入的 trigger 开销 ×12（二级索引维护）+ 1（影子表），总写入放大 ~25%
  2. gh-ost 通过 binlog tail 异步 apply，对主库写入零侵入
  3. gh-ost 的 --max-lag-millis 自适应降速，在 IO 打满场景下比 pt-osc 的固定 --chunk-time 更安全

场景 E：多表联合变更

现象：需要同时变更 5 张关联表（如 orders、order_items、order_logs、order_tags、order_refunds），每张表 2000–8000 万行。

决策：

1. 禁止并行执行：pt-osc 和 gh-ost 都依赖 MDL，多表并行会导致 MDL 锁竞争，主库 CPU 飙升。
2. 串行执行顺序：从叶子表到根表（先改 order_tags，最后改 orders），减少外键依赖的 DDL 冲突。
3. 间隔时间：每张表之间间隔 ≥ 30s，让 binlog 回放追平。
4. 监控：

-- 执行中监控 MDL 等待
SELECT * FROM performance_schema.metadata_locks
WHERE OBJECT_NAME IN ('orders','order_items','order_logs','order_tags','order_refunds');

5. INSTANT DDL 批量：若 5 张表都是 ADD/DROP/RENAME COLUMN，可一次性批量执行（INSTANT 无锁，无竞争）：

ALTER TABLE orders ADD COLUMN discount DECIMAL(10,2) DEFAULT 0;
ALTER TABLE order_items ADD COLUMN sku_code VARCHAR(64);
ALTER TABLE order_logs ADD COLUMN operator_id BIGINT;
ALTER TABLE order_tags ADD COLUMN tag_source TINYINT;
ALTER TABLE order_refunds ADD COLUMN refund_reason VARCHAR(256);

4. 三个生产事故复盘

事故一：pt-osc RENAME 卡住导致写入阻塞 40 秒

时间线：

• 02:15:00 pt-osc 启动，开始 INSERT ... SELECT 拷贝
• 03:42:17 拷贝完成（8000 万行，耗时 1h27m）
• 03:42:18 执行 RENAME TABLE orders TO orders_old, orders_new TO orders
• 03:42:18 RENAME 开始等待 MDL 排他锁
• 03:42:19 发现一个业务事务（BEGIN; SELECT ... FOR UPDATE; ...）持有 orders 表的 MDL 共享锁，已运行 12 秒
• 03:42:58 该事务 COMMIT，MDL 释放
• 03:42:58 RENAME 完成（耗时 40 秒）
• 03:42:58 业务写入恢复

根因：

• pt-osc 的 RENAME 需要 MDL 排他锁，但业务端有一个 SELECT ... FOR UPDATE 长事务（12 秒）持有 MDL 共享锁
• pt-osc 默认 --max-lag=1s，但 RENAME 阶段不检查 lag，直接等待 MDL
• 业务端未意识到 DDL 窗口内有长事务

改进措施：

1. DDL 执行前检查长事务：

SELECT id, time, state, info FROM information_schema.processlist WHERE time > 5;

2. 若有长事务，等待或 KILL：

KILL <thread_id>;

3. pt-osc 增加 --max-lag=0.5s（更保守），但 RENAME 阶段无法避免 MDL 等待
4. 改用 gh-ost：--cut-over-lock-timeout=19，若 19s 内拿不到 MDL 则自动回退，不会无限等待

事故二：gh-ost 切表窗口从库延迟 12 分钟

时间线：

• 02:30:00 gh-ost 启动
• 04:15:22 拷贝完成，开始 cut-over
• 04:15:23 主库执行 RENAME TABLE，耗时 3ms
• 04:15:23 binlog 产生 RENAME TABLE 事件
• 04:15:24 从库开始回放 RENAME TABLE
• 04:27:00 从库回放完成（延迟 11m36s）
• 04:15:24 ~ 04:27:00 期间，从库上 orders 表仍为旧结构（无新列），业务只读流量查询新列报 Unknown column 错误

根因：

• 从库半同步回放延迟：从库正回放一个大批量 INSERT（50 万行），RENAME TABLE 事件排队等待
• gh-ost 的 --cut-over-method=atomic（默认），主库 RENAME 后立即生效，但从库延迟 12 分钟才生效
• 业务端未做读写分离的降级策略

改进措施：

1. 切表前确认从库延迟：

SHOW SLAVE STATUS\G  -- Seconds_Behind_Master < 5s

2. 若延迟 > 30s，暂停 gh-ost：

curl -X POST http://localhost:8080/pause

3. 等延迟追平后恢复：

curl -X POST http://localhost:8080/resume

4. 业务端做读写分离降级：若从库查询报 Unknown column，自动 fallback 到主库
5. 改用 --cut-over-method=two_steps：两次 RENAME，每次锁更短，从库回放窗口更小

事故三：INSTANT DDL DROP COLUMN 后空间未回收

时间线：

• 03:00:00 执行 ALTER TABLE orders DROP COLUMN legacy_flag（INSTANT，耗时 < 1ms）
• 03:00:00 业务端确认列已删除
• 03:00:00 表文件大小未变（仍为 180GB）
• 03:30:00 DBA 执行 OPTIMIZE TABLE orders（耗时 45 分钟，锁表）
• 04:15:00 表文件大小降至 172GB（回收 8GB）

根因：

• MySQL 8.0 INSTANT DDL 的 DROP COLUMN 仅标记为 DROPPED，实际数据页未删除
• 空间回收需 OPTIMIZE TABLE（重建表）或 ALTER TABLE ... ENGINE=InnoDB（隐式 OPTIMIZE）
• OPTIMIZE TABLE 会锁表，8000 万行耗时 45 分钟

改进措施：

1. INSTANT DDL 后不要立即执行 OPTIMIZE TABLE，而是：
   • 在低峰期（如 05:00–06:00）执行，避开业务高峰
   • 或用 pt-online-schema-change 执行 ALTER TABLE orders ENGINE=InnoDB（无锁重建）
2. 监控表文件大小：

SELECT table_name, data_length/1024/1024/1024 AS size_gb
FROM information_schema.tables
WHERE table_name = 'orders';

3. 若空间回收不紧急（如 DROP COLUMN 释放 < 5GB），可忽略，等下次 OPTIMIZE TABLE 统一回收

5. DDL 变更前 Checklist

- [ ] 1. 确认 DDL 类型是否支持 INSTANT DDL（ADD/DROP/RENAME COLUMN）
- [ ] 2. 检查当前长事务（`SELECT ... FROM processlist WHERE time > 5`）
- [ ] 3. 确认从库延迟（`SHOW SLAVE STATUS\G` → Seconds_Behind_Master < 5s）
- [ ] 4. 确认磁盘空间充足（影子表需 2x 原表大小，8000 万行 × 2KB × 2 = 320GB）
- [ ] 5. 确认 binlog_format=ROW 且 binlog_row_image=FULL（gh-ost 前置条件）
- [ ] 6. 确认 SUPER 权限（pt-osc 创建 trigger 需要）
- [ ] 7. 备份原表（`mysqldump --single-transaction orders > orders_backup.sql`）
- [ ] 8. 通知业务端 DDL 窗口（02:00–05:00），确认无长事务
- [ ] 9. 准备回滚方案（pt-osc / gh-ost：DROP 影子表；INSTANT：不可逆，需从备份恢复）
- [ ] 10. 监控就绪（iostat, pt-query-digest, gh-ost HTTP API）
- [ ] 11. 确认 max_connections 充足（pt-osc / gh-ost 各需 2–3 个连接）
- [ ] 12. 记录当前表结构（`SHOW CREATE TABLE orders`）
- [ ] 13. 确认 DDL 窗口内无其他 DDL / 大查询
- [ ] 14. 准备 KILL 命令（若长事务卡住 MDL）
- [ ] 15. 确认业务端读写分离降级策略（若从库延迟导致列不存在）

6. 附录：完整可复现命令

6.1 pt-osc 2.4.1

# 场景：8000 万行表 ADD INDEX idx_create_time
# 前置检查
mysql -h <host> -u <user> -p -e "SELECT table_rows FROM information_schema.tables WHERE table_name='orders';"
mysql -h <host> -u <user> -p -e "SELECT id, time, state FROM information_schema.processlist WHERE time > 5;"
mysql -h <host> -u <user> -p -e "SHOW SLAVE STATUS\G" | grep Seconds_Behind_Master

# 执行 pt-osc
ionice -c2 -n7 nice -n 10 pt-online-schema-change \
  --alter="ADD INDEX idx_create_time (create_time)" \
  --database=shop \
  --table=orders \
  --execute \
  --chunk-time=0.5 \
  --max-lag=1s \
  --chunk-size=1000 \
  --critical-load="Threads_running=50" \
  --set-names \
  --no-drop-old-table \
  --no-drop-new-table

# 回滚（若失败）
mysql -h <host> -u <user> -p -e "DROP TABLE IF EXISTS shop.__orders_new;"
mysql -h <host> -u <user> -p -e "DROP TRIGGER IF EXISTS shop.__orders_new_AI; DROP TRIGGER IF EXISTS shop.__orders_new_AU; DROP TRIGGER IF EXISTS shop.__orders_new_AD;"

# 确认结果
mysql -h <host> -u <user> -p -e "SHOW INDEX FROM shop.orders WHERE Key_name='idx_create_time';"

6.2 gh-ost 1.1.6

# 场景：8000 万行表 ADD INDEX idx_create_time
# 前置检查（同上）

# 执行 gh-ost
docker run --rm -e "ALLOW_ON_MASTER=false" -e "CONCURRENT_ROW_COUNT=false" \
  -e "CUT_OVER_LOCK_TIMEOUT=19" -e "MAX_LAG_MILLIS=1500" \
  -e "CHUNK_SIZE=1000" -e "THREADS=2" \
  -v /var/run/docker.sock:/var/run/docker.sock \
  gh-ost/gh-ost \
  --host=<host> \
  --user=<user> \
  --password=<password> \
  --database=shop \
  --table=orders \
  --alter="ADD INDEX idx_create_time (create_time)" \
  --execute \
  --allow-on-master=false \
  --exact-rowcopy \
  --max-lag-millis=1500 \
  --chunk-size=1000 \
  --cut-over-lock-timeout=19 \
  --assume-rbr

# 监控 gh-ost
curl -s http://localhost:8080/status | jq .

# 暂停 / 恢复
curl -X POST http://localhost:8080/pause
curl -X POST http://localhost:8080/resume

# 回滚（若失败）
mysql -h <host> -u <user> -p -e "DROP TABLE IF EXISTS shop._orders_new;"

# 确认结果
mysql -h <host> -u <user> -p -e "SHOW INDEX FROM shop.orders WHERE Key_name='idx_create_time';"

6.3 MySQL 8.0 INSTANT DDL

# 场景：8000 万行表 ADD COLUMN discount
# 前置检查
mysql -h <host> -u <user> -p -e "SELECT VERSION();"  # 确认 8.0.29+

# 执行 INSTANT DDL
mysql -h <host> -u <user> -p -e "ALTER TABLE shop.orders ADD COLUMN discount DECIMAL(10,2) DEFAULT 0.00;"

# 确认结果（INSTANT 操作，< 1ms）
mysql -h <host> -u <user> -p -e "SHOW COLUMNS FROM shop.orders LIKE 'discount';"
mysql -h <host> -u <user> -p -e "SELECT table_name, data_length/1024/1024/1024 AS size_gb FROM information_schema.tables WHERE table_name='orders';"

# 回滚（DROP COLUMN 不可逆，需从备份恢复）
# 方案 1：从备份恢复（需停写）
# mysqldump --single-transaction --databases shop | mysql -h <host> -u <user> -p
# 方案 2：ADD COLUMN 回来（若只是加错了列名）
mysql -h <host> -u <user> -p -e "ALTER TABLE shop.orders RENAME COLUMN discount TO discount_backup;"

# 空间回收（低峰期执行）
mysql -h <host> -u <user> -p -e "OPTIMIZE TABLE shop.orders;"  # 8000 万行耗时 30–60 分钟，锁表

7. 总结与取舍建议

| 场景 | 推荐方案 | 理由 | |------|---------|------| | ADD/DROP/RENAME COLUMN | INSTANT DDL | 毫秒级完成，零锁，零 IO | | ADD/DROP INDEX（< 100 万行） | 直接 ALTER | 锁 < 1s，无需复杂工具 | | ADD/DROP INDEX（100 万 ~ 5000 万行） | gh-ost | 无 trigger 开销，binlog tail 自适应降速 | | ADD/DROP INDEX（> 5000 万行，10+ 二级索引） | gh-ost | 优于 pt-osc：写入放大 < 2% vs 5-15%，IO 打满时自动 pause | | MODIFY COLUMN（类型变更） | gh-ost | 必须 copy 数据，gh-ost 无 trigger 开销 | | 多表联合变更（都是 COLUMN 操作） | INSTANT DDL 批量 | 无锁，可一次性执行 | | 多表联合变更（含 INDEX 操作） | gh-ost 串行 | 从叶子表到根表，每张表间隔 ≥ 30s |

核心原则：

1. 能用 INSTANT 就用 INSTANT（零风险，零成本）
2. 不能用 INSTANT 且表 > 5000 万行 → gh-ost（无 trigger，自适应降速）
3. 不能用 INSTANT 且表 < 5000 万行 → pt-osc（更成熟，团队熟悉度高）
4. 任何 DDL 前必须检查长事务 + 从库延迟 + 磁盘空间