给千万行大表加字段不锁表：pt-online-schema-change 与 gh-ost 实战

有回要给一张 5000 万行的订单表加个 coupon_id 字段，开发说"就加个列，很快吧"。我脑子一热，在从库上先试了句 ALTER TABLE orders ADD COLUMN coupon_id BIGINT，本以为 8.0 的 instant DDL 秒过——结果字段是加上了，但主库上一跑，写入直接堵死，连接数从 200 飙到 max_connections 上限，从库延迟一度到了七分钟。才想起来那个表上还有个 FULLTEXT 索引，instant 算法不支持，退化成了拷表，全程拿 MDL 写锁。

自那以后，凡是行数过百万的表要做结构变更，我一律走影子表工具，绝不再直接 ALTER。MySQL 的 ALGORITHM=INSTANT（8.0.12+）确实能秒加列、不加索引的前提下很好用，但有硬限制：只能加列（且不能含默认值表达式的某些情况）、不能改列类型、不能加减索引、表上不能有全文索引或隐藏索引冲突等。只要变更稍微复杂一点，就掉回 INPLACE 甚至 COPY，锁就来了。

所以大表变更的两条成熟路线是：pt-online-schema-change（Percona Toolkit，触发器驱动）和 gh-ost（GitHub 出品，binlog 驱动、无触发器）。下面把两条都跑通。

影子表方案的共同思路

不管是哪个工具，核心套路都一样：

建一张结构变更后的空影子表（_orders_new）。
把原表数据分批拷贝进影子表，过程中原表照常读写。
变更期间的增量写入，通过某种机制同步到影子表（pt-osc 用触发器，gh-ost 读 binlog）。
拷贝追上后，原表和影子表原子切换（改名），业务几乎无感。
删掉旧表（或先留着观察）。

差别就在第 3 步"增量怎么同步"，这决定了它们的适用场景和坑。

方案一：pt-online-schema-change

装 Percona Toolkit（以 RHEL / Alibaba Cloud Linux 为例）：

# 有 percona 源就直接 yum/dnf
dnf install -y percona-toolkit
# 或下载 rpm
# https://www.percona.com/downloads/percona-toolkit/

which pt-online-schema-change        # 确认装好了

最基础的一次加列（先 dry-run 看它要干啥，再 --execute 真的跑）：

pt-online-schema-change \
  --alter "ADD COLUMN coupon_id BIGINT NULL" \
  --host=127.0.0.1 --port=3306 \
  --user=dba --password='你的密码' \
  --database=shop --table=orders \
  --charset=utf8mb4 \
  --no-version-check \
  --dry-run

--dry-run 不会真的建表，只是把执行计划打出来：会建哪张影子表、加哪三个触发器（INSERT/UPDATE/DELETE 各一个）、会不会删旧表。确认无误再换成 --execute：

pt-online-schema-change \
  --alter "ADD COLUMN coupon_id BIGINT NULL, ADD INDEX idx_coupon (coupon_id)" \
  --host=127.0.0.1 --port=3306 \
  --user=dba --password='你的密码' \
  --database=shop --table=orders \
  --charset=utf8mb4 \
  --chunk-size=2000 \
  --max-load="Threads_running=50" \
  --critical-load="Threads_running=100" \
  --recursion-method=none \
  --no-drop-old-table \
  --execute

几个关键参数，都是踩过才记住的：

--chunk-size：每批拷多少行。太大一次锁太久、binlog 暴涨；太小则总时间长。大表一般 1000~5000，按主键分批。
--max-load：当 Threads_running 超过这个值就暂停拷贝，等负载降下来再继续。这是保护线上不被拖垮的关键，一定要设。
--critical-load：超过这个值直接中止整个操作（不是暂停）。设成比 max-load 高一档，当机子快挂了时保命。
--recursion-method=none：如果没配从库自动发现，显式关掉，不然它会去连从库探测，连不上就卡住。有从库的环境用 processlist 或 hosts 让它自动找。
--no-drop-old-table：强烈建议开启。变更成功后原表会被改名成 _orders_old，默认工具会删掉它。留着观察几天、确认无误再手动 DROP，万一新表有问题还能换回去。磁盘够的话这步千万别省。

pt-osc 用的三个触发器，是它最大的双刃剑。触发器在源表上做增删改时，把同样的变更应用到影子表。坑来了：

源表本来就已有触发器的话，pt-osc 没法再加（一张表同事件只能有一个触发器），直接报错退出。这种表只能换 gh-ost，或者先把原触发器合并进去。
触发器本身有开销，高并发写入下表上多三个触发器，写入性能会掉一截，但通常能接受。
中途如果工具被 kill，触发器不会自动清理，得手动删掉那三个 _orders_* 触发器，否则源表写入会持续往影子表写，越写越乱。

方案二：gh-ost

gh-ost 的思路更"优雅"：它完全不用触发器，而是伪装成一个从库去读主库的 binlog，把变更期间的增量重放到影子表。好处是源表上零触发器开销，对写入几乎无感；坏处是依赖 binlog 为 ROW 格式（binlog_format=ROW），并且得能连上主库或从库。

装：

# 二进制直接下
# https://github.com/github/gh-ost/releases
mv gh-ost /usr/local/bin/
chmod +x /usr/local/bin/gh-ost
gh-ost --version

最常用的是"连从库、但写主库"模式（生产推荐，对主库压力最小）：

gh-ost \
  --host=从库IP --port=3306 \
  --user=dba --password='你的密码' \
  --database=shop --table=orders \
  --alter="ADD COLUMN coupon_id BIGINT NULL" \
  --allow-online-ddl \
  --max-load=Threads_running=50 \
  --critical-load=Threads_running=100 \
  --chunk-size=2000 \
  --throttle-control-replicas="从库IP:3306" \
  --throttle-query="SELECT 1" \
  --serve-socket-file=/tmp/gh-ost.sock \
  --execute

gh-ost 的几个特性让它比 pt-osc 在某些场景更省心：

自动节流（throttle）：它内置了"看从库延迟"的能力，只要从库延迟超阈值就自动暂停，不用像 pt-osc 那样只盯 Threads_running。--throttle-control-replicas 指定从库地址，延迟一高它自己就歇着，从库追上了再继续。
可交互控制：--serve-socket-file 开一个 unix socket，你能在另一个终端动态发指令暂停/恢复/改限速，比如 echo throttle | socat - /tmp/gh-ost.sock。大促前想先停一下？一条命令的事。
测试模式：--test-on-replica 可以在从库上完整跑一遍变更，切表前自动停从库复制、切完再回滚，专门用来验证变更对不对、要多久，对主库零风险。上线前必跑一次。
切换方式：gh-ost 默认用 RENAME TABLE 做原子切换，原表和 _orders_ghc / _orders_gho 影子表瞬间互换。切换瞬间会拿一个极短的锁（通常毫秒级），业务基本无感。

gh-ost 的坑相对少，但有两个要注意：一是必须 binlog_format=ROW，如果是 STATEMENT 就废了（现在新建实例基本都是 ROW，老库得确认）；二是它对有外键的表支持有限，外键场景下它要求你显式声明 --force-table-names 之类的绕过，或者改用 pt-osc 配 --alter-foreign-keys-method。

到底选哪个

没有绝对答案，按场景取舍：

源表已有触发器 → 只能 gh-ost（pt-osc 加不进去）。
写入极高、对源表性能敏感 → 选 gh-ost，无触发器零额外写入开销。
有外键 → pt-osc 的 --alter-foreign-keys-method=auto 处理得更顺，gh-ost 这里更别扭。
环境简单、就想快糙猛搞定 → pt-osc 部署简单、文档多、社区熟，很多老脚本里都是它。
想有动态节制 + 测试模式保障 → gh-ost 的交互和 --test-on-replica 更香。

我自己现在的默认是：能 gh-ost 就 gh-ost，尤其是主库写入重、又有从库可监控延迟的场景；遇到外键或触发器冲突的遗留表，退回 pt-osc。

上线前和上线后的 checklist

无论用哪个，下面这些别省：

# 1. 变更前先确认表行数和体积，心里有数
SELECT table_rows, ROUND(data_length/1024/1024) AS mb
FROM information_schema.tables
WHERE table_schema='shop' AND table_name='orders';

# 2. 确认从库延迟基线（gh-ost 尤其要看）
SHOW SLAVE STATUS\G    # 看 Seconds_Behind_Mource

# 3. 变更期间盯着负载和延迟，别跑了就走人
mysql -e "SHOW PROCESSLIST" | grep -c "copy"   # pt-osc 拷贝线程
# gh-ost 看它的 throttle 日志输出

# 4. 切换完成后，核对行数一致
SELECT COUNT(*) FROM orders;
SELECT COUNT(*) FROM _orders_old;     # 旧表（pt-osc 留着的）
# 两者应一致；gh-ost 旧表名通常是 _orders_del

# 5. 验证新结构真的生效
DESC orders;
SHOW INDEX FROM orders;

切换完成后别急着删旧表。我习惯留 _orders_old / _orders_del 观察 3~7 天，期间对比业务数据、确认没有因变更引入问题，再在低峰手动 DROP TABLE。之前有次加完索引发现某个老查询的执行计划全变了、反而更慢，幸亏旧表还在，能立刻 RENAME 换回去争取排查时间。

还有一点：影子表工具解决的是"锁表"问题，不解决"变更本身对业务语义的影响"。加 NOT NULL 无默认值列、改列类型导致隐式转换、删列前确认没代码引用——这些得你自己在变更前用 pt-online-schema-change --dry-run 模拟、用 gh-ost --test-on-replica 验证，工具不会替你想业务后果。

回到开头那张 5000 万行的表。后来我改用 gh-ost，连从库、设了从库延迟节流，整个过程从库延迟最高也就 20 秒、主库 Threads_running 一直压在 50 以下，业务侧零投诉。切表那一刻的锁短到监控曲线都没抖一下。那天之后我对"大表 ALTER"的恐惧，算是被这两工具治好了大半。

给千万行大表加字段不锁表：pt-online-schema-change 与 gh-ost 实战

影子表方案的共同思路

方案一：pt-online-schema-change

方案二：gh-ost

到底选哪个

上线前和上线后的 checklist

相关文章

索引不是加得越多越好：一次索引重构的记录

MySQL 5.7 升 8.0，别直接 yum update

评论区