MySQL大表在线DDL方案选型&线上避坑笔记

MySQL大表在线DDL方案选型&线上避坑笔记

一、核心背景：直接ALTER大表为什么极易线上翻车

1. 原生ALTER致命风险（90%故障根源）

1. 长时间MDL排他锁
   执行DDL期间持有表元数据排他锁，阻塞全表增删改查；高并发下连接打满、服务超时雪崩。
2. 全表拷贝（COPY模式）
   改字段类型、删列、增索引、改主键会重建整张表，打满CPU/IO；亿级表执行几十分钟至数小时。
3. 主从延迟爆炸
   从库单线程回放DDL，主库几秒完成，从库阻塞几小时，读写分离读脏数据。
4. 磁盘空间耗尽
   拷贝临时表需要额外1倍数据表空间，大表极易占满磁盘导致数据库宕机。
5. 中途失败回滚代价极大，容易出现表损坏、数据不一致。

2. 三种DDL底层算法区分

1. INSTANT（8.0专属最优）
   仅末尾新增列、修改默认值等轻量操作；仅改元数据，毫秒完成、无锁、不拷贝数据。
2. INPLACE
   原地修改表，不拷贝全表；但仍会短暂阻塞读写，大表高并发不推荐。
3. COPY
   全表重建，整表锁定读写，千万级大表线上严禁直接执行。

二、三大主流在线无锁DDL方案对比

方案1：MySQL 8.0原生Online DDL（INSTANT）

原理

内置轻量变更，无需外部工具，仅修改元数据。

优点

零额外工具、无触发器/binlog开销、速度极快、无磁盘双倍占用。

缺点

仅支持少量操作（末尾加列、改默认值、重命名列）；增索引、改字段类型、删列不支持INSTANT，会退化成COPY锁表；仍受长事务阻塞MDL锁。

适用场景

MySQL8.0、仅简单新增末尾字段、低并发中小表。

方案2：pt-online-schema-change（pt-osc，Percona工具）

原理

创建影子表→执行DDL→分批拷贝存量数据→触发器同步增量DML→原子RENAME切换表名。

优点

兼容MySQL5.6/5.7/8.0；支持限流、主从延迟自动暂停；中途可终止，回滚成本低；行业成熟稳定。

翻车坑点

1. 触发器给原表写入增加额外性能开销，高并发业务压力翻倍；
2. 有外键、触发器的表兼容性差；
3. 切换表名瞬间需要短暂MDL锁，长事务会卡住切换；
4. 触发器异常可能造成主表、影子表数据不一致。

适用场景

千万级大表、低并发写入、5.7及以下老版本MySQL。

方案3：gh-ost（GitHub开源，binlog解析方案）

原理

伪装成从库拉取binlog解析增量变更，无触发器；影子表同步存量+回放binlog，最后原子换表。

优点

无触发器、主库写入性能损耗最小；限速、延迟控制完善；支持动态暂停、安全退出；故障风险更低。

缺点

需要开启binlog=ROW模式；依赖binlog同步逻辑，架构复杂一点。

适用场景

高并发写入大表、互联网核心业务、新项目首选。

三、线上90%翻车高频坑点（必看）

1. 不分场景直接执行原生ALTER
   千万级表改字段、加索引，直接触发COPY全表锁表，业务全阻塞。
2. 忽略INSTANT限制
   误以为8.0所有ADD COLUMN都秒执行，中间加列、改字段类型仍会锁表。
3. 磁盘空间未提前校验
   pt-osc/gh-ost需要双倍空间，磁盘不足中途崩溃，表残留。
4. 未限制负载/主从延迟
   拷贝数据打满CPU/IO，未配置--max-load、--max-lag，拖垮主从。
5. 存在长事务未清理
   切换表名阶段需要MDL锁，长事务持有锁导致切换卡死，流量堆积。
6. pt-osc触发器冲突
   原表已有业务触发器，双重触发器引发数据错乱。
7. 业务高峰期执行
   拷贝+增量同步放大数据库压力，QPS叠加直接超时雪崩。
8. 变更后不保留旧表
   pt-osc默认删除旧表，数据异常无回滚依据。

四、线上DDL选型决策标准

1. MySQL8.0、仅末尾新增字段 → 原生INSTANT Online DDL
2. MySQL5.6/5.7、低并发写入、千万级大表 → pt-osc
3. MySQL8.0、高并发核心订单/流量表、频繁写入 → gh-ost
4. 改字段类型、删除列、新增索引、修改主键 → 一律禁用原生ALTER，必须用pt-osc/gh-ost

五、生产执行标准流程（规避故障）

1. 预检查
   • 查询表行数、占用磁盘，预留至少1.5倍存储空间；
   • 确认binlog=ROW（gh-ost强制要求）；
   • 杀掉长时间运行事务；
2. 低峰执行（凌晨流量低谷）；
3. 开启限流、主从延迟阈值自动暂停；
4. 先在从库完整跑一遍，观察CPU/IO/延迟；
5. 执行完成保留旧表7天，确认业务无异常再清理；
6. 全程监控慢日志、主从延迟、连接数、接口超时。

六、极简记忆总结

1. 小改动8.0末尾加列：用原生INSTANT，最快无开销；
2. 老版本低并发大表：pt-osc（触发器）；
3. 高并发核心大表：gh-ost（binlog无触发器，压力最小）；
4. 任何千万级表改类型/加索引，禁止直接ALTER，必锁表翻车。