5000 万行大表加索引：pt-online-schema-change 耗时 4h12min，比原生 ALTER 慢 67% 的根因与决策清单

作者陈星河-382

MySQL在线DDLpt-online-schema-change索引慢查询治理DBA

一、现象：「更安全」的工具反而更慢、更贵

去年给一套 ERP 系统做索引补建。核心表 t_voucher_entry（凭证分录表）规模：5280 万行、单表 11.7GB、InnoDB、MySQL 8.0.36、8C16G 云主机、SSD 云盘。业务方要求在不锁写的前提下加一个复合索引 idx_voucher_date_type (voucher_date, entry_type)。

团队第一反应是「上 pt-online-schema-change，安全、不阻塞业务」。实际执行结果：

| 方案 | 耗时 | 峰值主从延迟 | 回滚成本 | |------|------|-------------|---------| | pt-online-schema-change (v2.4.6) | 4h12min | 最大 14.2s | 需手动 DROP shadow 表 | | ALTER TABLE ... ALGORITHM=INPLACE, LOCK=NONE | 2h31min | 最大 3.8s | 自动回滚 | | gh-ost (v1.1.6) | 3h48min | 最大 9.6s | 需手动 cleanup |

pt-osc 比原生 INPLACE 慢了 67%，主从延迟翻了近 4 倍。更关键的是：那次 pt-osc 跑到 62% 时，因为 shadow 表数据拷贝速度跟不上业务写入（峰值 QPS 3800 INSERT/s），触发了一次 11 分钟的「假死」——业务 INSERT 全部堆积在 trigger 里排队，应用侧连接池打满，告警炸了 20 多分钟。

事后复盘，根因不是工具「有 bug」，而是 pt-osc 的 trigger 机制天然引入了 2x 写入放大 + 串行化瓶颈，在高写入吞吐场景下，它的「安全」是用「慢 + 延迟尖刺」换来的。

二、根因：三种方案的写入路径完全不同

原生 `ALGORITHM=INPLACE`

ALTER TABLE t_voucher_entry
  ADD INDEX idx_voucher_date_type (voucher_date, entry_type),
  ALGORITHM=INPLACE, LOCK=NONE;

InnoDB 内部走 index build 路径：

在后台线程中全量扫描聚簇索引，构建二级索引 B+ 树（2 个 pass，sort 阶段用临时表，8G 内存下 sort buffer 设 256MB）。
构建过程中通过 Change Buffer / 内存中的 in-place 日志 记录增量 DML，build 完成后回放。
最后一步：获取 MDL 排他锁（仅持锁 3~8ms，因为 metadata 变更极小），切换数据字典。

关键点：写入不经过 trigger，INSERT/UPDATE 直接写聚簇索引 + 已存在的二级索引，新增索引的增量在后台异步追。写入放大 ≈ 1.0x。

pt-online-schema-change

pt-online-schema-change \
  --alter="ADD INDEX idx_voucher_date_type (voucher_date, entry_type)" \
  --chunk-time=0.5 --max-lag=1 \
  D=t_erp_db,t=t_voucher_entry

执行路径：

创建 shadow 表 _t_voucher_entry_new（结构 = 原表 + 新索引），耗时 28min（11.7GB 全量 COPY，走 INSERT INTO ... SELECT，受 --chunk-size=1000 限制）。
建 Trigger：在原表上建 BEFORE INSERT / BEFORE UPDATE / BEFORE DELETE 三个 trigger，每条 DML 先写原表、再写 shadow 表 → 写入放大 2x。
逐 chunk 拷贝存量数据（--chunk-time=0.5 意味着每 0.5s 切一次 chunk，约 8000~12000 行/chunk）。
RENAME 切换：RENAME TABLE t_voucher_entry TO _old, _new TO t_voucher_entry（需 MDL 排他锁，持锁 12ms）。
删 trigger、DROP 旧表。

瓶颈在哪？

Trigger 是行级串行执行：InnoDB 的 trigger 在事务内同步执行，3800 QPS 的 INSERT 意味着每秒 3800 次「写原表 + 写 shadow 表」的同步调用，CPU 和 redo log 写入量翻倍。
chunk 拷贝与 trigger 写入竞争 buffer pool：拷贝线程大量顺序读（11.7GB / 4h ≈ 820MB/h），业务随机写也争抢同一 buffer pool（我们设的是 8G），命中率从 99.2% 掉到 94.7%，物理 IO 飙升。
--max-lag=1 的限流逻辑：每秒查一次主从延迟，延迟 >1s 就 sleep。但 sleep 期间 trigger 还在写，导致「追数据」和「追增量」交替卡顿，表现为 11 分钟假死。

gh-ost

gh-ost 用 binlog 监听 + 行级 apply 替代 trigger，写入放大降到 ≈1.0x（原表正常写，gh-ost 从 binlog 异步追）。所以它比 pt-osc 快 11%，但比原生 INPLACE 还是慢 50%——因为 全量 COPY 阶段无法避免（它必须建一张完整的新表），而原生 INPLACE 的 index build 不需要复制整张表。

三、具体做法：决策树 + 可复现命令

决策树（5000 万行 / 10GB 级别）

Q1: 表上是否有长事务（>30s）？
  → 有：先 kill 或等结束，否则任何方案 RENAME 阶段都会卡 MDL
  → 无：继续

Q2: 峰值写入 QPS？
  → < 500：pt-osc / gh-ost / 原生 INPLACE 均可，选原生（最快）
  → 500~2000：优先原生 INPLACE；若 DBA 不允许（MDL 风险），选 gh-ost
  → > 2000：原生 INPLACE（trigger 方案在此量级必出问题）
    或：业务低峰期 + 原生 INPLACE + pt-osc 做兜底（仅用于 < 1000 万行小表）

Q3: 是否有从库且业务读走从库？
  → 是：关注主从延迟。原生 INPLACE 延迟尖刺 < 5s，pt-osc 可达 10~15s
  → 否：影响面小，工具选择更灵活

推荐命令（本次场景：5280 万行、3800 QPS 写入）

-- 1. 前置检查：确认无长事务
SELECT id, user, host, db, command, time, state, info
FROM information_schema.processlist
WHERE command != 'Sleep' AND time > 30;

-- 2. 确认 buffer pool 配置（避免拷贝阶段 IO 争抢）
SHOW VARIABLES LIKE 'innodb_buffer_pool_size';  -- 我们设了 8G（物理内存 16G）

-- 3. 执行原生 INPLACE（低峰期 02:00 执行）
SET lock_wait_timeout = 5;  -- MDL 获取超时 5s，避免卡死
ALTER TABLE t_voucher_entry
  ADD INDEX idx_voucher_date_type (voucher_date, entry_type),
  ALGORITHM=INPLACE, LOCK=NONE;

-- 4. 验证
SHOW INDEX FROM t_voucher_entry WHERE Key_name = 'idx_voucher_date_type';
EXPLAIN SELECT * FROM t_voucher_entry WHERE voucher_date = '2024-11-01' AND entry_type = 'RECEIPT';
-- 期望：type=ref, key=idx_voucher_date_type, rows≈12000

如果必须用 pt-osc（小表 < 500 万行、低写入 < 500 QPS）

pt-online-schema-change \
  --alter="ADD INDEX idx_xxx (col_a, col_b)" \
  --chunk-time=1 \          # 放宽到 1s，减少 chunk 切换开销
  --max-lag=0.5 \           # 延迟阈值收紧，避免堆积
  --critical-load="Threads_running>50" \  # 线程数超 50 自动暂停
  --no-drop-old-table \     # 先不删旧表，人工确认后再删
  D=t_erp_db,t=t_small_table

四、踩过的坑（反直觉之处）

「安全」≠「无代价」。pt-osc 的 trigger 机制让每条写入多一次同步 IO，在 3800 QPS 下，redo log 写入量从 120MB/s 飙到 240MB/s，SSD 云盘的 IOPS 上限（我们买的 8000 IOPS）被打满，表现为「数据库没报错但就是慢」。
ALGORITHM=INPLACE 的 MDL 排他锁只持 3~8ms，不是网上说的「锁表」。真正危险的是：如果你在 RENAME 那一刻恰好有一个跑了 200s 的事务没提交，MDL 会排队等它 → 后续所有 DML 全部阻塞。所以 SET lock_wait_timeout=5 是保命参数，超时就报错回滚，而不是无限等。
buffer pool 8G 对 11.7GB 表不够。INPLACE 的 index build 阶段需要全表扫描，如果 buffer pool 装不下热数据，扫描期间会大量换出/换入，IO 放大 3~5 倍。本次我们把 innodb_buffer_pool_size 临时调到 12G（物理内存 16G，留 4G 给 OS + redo），index build 时间从预估 3h 降到 2h31min。
gh-ost 的 --cut-over=atomic 在某些版本有 bug（v1.1.6 以下），RENAME 和 DROP trigger 不是原子的，极端情况下会出现「旧表已删、新表还没切」的窗口。v1.1.6+ 已修复，但如果你用旧版本，务必先确认。

五、结论与可复用清单

一句话结论

5000 万行以上、写入 QPS > 1000 的表加索引，原生 ALGORITHM=INPLACE 是耗时最短、延迟尖刺最小、回滚最省心的方案；pt-osc 的「不锁写」优势在这个量级已被 trigger 瓶颈完全抵消，反而更慢、更危险。

可复用清单

| 项 | 值 / 做法 | |----|----------| | 前置检查 | processlist 无 >30s 长事务；buffer pool ≥ 表大小 × 0.8 | | MDL 保护 | SET lock_wait_timeout = 5（单位秒） | | 执行时间窗 | 低峰期（02:00~05:00），预留 2x 预估耗时 | | 监控指标 | Threads_running、主从延迟（Seconds_Behind_Master）、buffer pool 命中率 | | 回滚预案 | INPLACE 自动回滚；pt-osc 需手动 DROP TABLE _xxx_new; DROP TRIGGER ... | | 工具版本 | pt-osc ≥ 2.4.6；gh-ost ≥ 1.1.6；MySQL ≥ 8.0.29（INPLACE 优化） | | 适用边界 | < 500 万行 + QPS < 500 → pt-osc 可用；≥ 5000 万行 → 优先原生 INPLACE |

成本收益权衡（我的核心观点）

pt-osc 的设计初衷是 MySQL 5.5 时代没有 INPLACE 算法，那时候 ALTER TABLE 就是 ALGORITHM=COPY（锁全表、重建整张表），pt-osc 的 trigger 方案确实是唯一选择。但 MySQL 5.6+ 引入 INPLACE、5.7+ 完善后，pt-osc 的「安全」优势已经大幅缩水，而它的 2x 写入放大和串行 trigger 瓶颈是 结构性问题，无法通过调参消除。2024 年了，除非你仍在 5.5/5.6 的老系统上，否则没有理由默认选 pt-osc。

环境：MySQL 8.0.36 / 8C16G / SSD 云盘 8000 IOPS / pt-osc 2.4.6 / gh-ost 1.1.6

赞 0 · 收藏 0 · 评论 0 · 浏览 0

暂无评论。