大表加索引导致主从延迟怎么使用 pt-online-schema-change 优化

pt-online-schema-change 通过创建新表并分批复制数据来避免长锁，适合大表加索引场景，但需配置 `--max-lag` 参数控制主从延迟风险。

先说结论：使用 pt-online-schema-change 配合 `--max-lag` 参数是在线加索引的标准方案，能显著降低锁表时间，但无法完全消除因数据复制带来的从库延迟。

先定位：确认表大小、主从延迟阈值及从库复制性能。
先做：设置 `--max-lag` 和 `--chunk-size` 限制操作速度，避免压垮从库。
再验证：监控 Seconds_Behind_Master 指标和工具执行日志。

命令速用版

pt-online-schema-change \
  `--user`=admin `--password`=*** `--host`=127.0.0.1 \
  `--alter`="ADD INDEX idx_name (column_name)" \
  `--max-lag`=5s \
  `--chunk-size`=1000 \
  `--recursion-method`=processlist \
  D=database_name,t=table_name \
  `--execute`

上述命令中 `--max-lag` 用于检测主从延迟，超过阈值会自动暂停操作；`--chunk-size` 控制每次处理的数据行数，减小单次事务压力。

为什么会这样

原生 ALTER TABLE 加索引会锁表，导致主库写入阻塞，从库回放时同样需要长时间锁定，从而产生严重延迟。

pt-online-schema-change 的原理是创建结构相同的新表，在原表上建立触发器同步数据，分批将旧数据复制到新表，最后重命名交换。虽然避免了长锁，但数据复制过程会产生大量 binlog，从库回放这些日志仍需时间。如果从库硬件性能低于主库，或 `--chunk-size` 设置过大，依然会出现主从延迟。

分步处理

1. 环境检查

确认主从架构已开启 binlog，且从库具有唯一 server-id。检查主从账号拥有 ALTER、CREATE、TRIGGER 权限。

2. 预执行检查

先使用 `--dry-run` 参数运行，不实际修改数据，检查工具是否能正常连接、识别表结构及触发器冲突。

pt-online-schema-change ... `--dry-run`

3. 设置流控参数

根据从库负载情况调整 `--max-lag`。生产环境建议设置为 5s 到 10s 之间。如果从库延迟敏感，调小 `--chunk-size` 值，例如 500 或 1000 行。

大表加索引导致主从延迟怎么使用 pt-online-schema-change 优化

4. 执行变更

加上 `--execute` 参数正式运行。运行期间保持终端连接，或使用 screen/tmux 防止会话断开导致工具退出。

5. 异常回滚

如果执行中途报错，工具通常会保留新表（_new 后缀）和触发器。需手动删除触发器和新表，避免影响原表写入性能。

怎么验证是否生效

1. 检查表结构

使用 SHOW CREATE TABLE 命令确认新索引已存在，且表名已变回原名。

2. 监控主从延迟

在从库执行 SHOW SLAVE STATUS\G，观察 Seconds_Behind_Master 数值。在工具运行期间，该数值应在 `--max-lag` 设定范围内波动，不应持续飙升。

3. 检查触发器清理

查询 information_schema.triggers 表，确认原表上由工具创建的触发器已被自动删除。

常见坑

1. 磁盘空间不足

操作期间需要额外存储一份完整表数据及新索引。如果磁盘剩余空间小于原表大小，操作会失败甚至导致主库宕机。

2. 外键约束限制

如果表存在外键，pt-online-schema-change 处理逻辑复杂，可能锁表。建议先移除外键，操作完成后再添加，或使用 `--alter-foreign-keys-method` 参数。

3. 触发器冲突

原表如果已存在业务触发器，工具无法创建同步触发器。需先评估业务触发器逻辑，或改用其他方案如 gh-ost。

4. 主键缺失

工具依赖主键或唯一索引进行分块复制。如果表没有主键，工具无法高效定位数据行，可能导致全表扫描或失败。

常见问题

主从延迟能完全避免吗？

不能，只能控制。pt-online-schema-change 通过暂停复制来防止延迟扩大，但无法改变从库回放速度慢于主库写入速度的物理事实。

需要多少磁盘空间？

至少需要原表大小的空间。新表构建期间，旧表和新表同时存在，索引构建还会消耗额外临时空间。

支持外键吗？

支持有限。默认情况下工具会尝试处理外键，但复杂外键关系可能导致锁表，建议参考官方文档使用特定参数或临时移除外键。

参考来源

Percona Toolkit Official Documentation, pt-online-schema-change, https://www.percona.com/doc/percona-toolkit/LATEST/pt-online-schema-change.html