MySQL Online DDL 实战：原理、不锁表操作清单与避坑建议

线上业务大表变更一直是 DBA 和后端同学的高风险操作。本文基于生产环境实践，梳理 MySQL Online DDL 的执行原理、哪些 ALTER 可以不锁表、哪些必须 COPY，以及避坑清单。

一、Online DDL 的核心原理

MySQL 5.6 起支持 Online DDL，将表结构变更拆为三个阶段：

1）Prepare：短暂获取 MDL 共享锁（毫秒级），校验表结构并确定执行算法（COPY / INPLACE / INSTANT）；

2）Execute：持有共享锁但不阻塞 DML，后台执行变更，期间并发的增删改写入 online log；

3）Commit：重放增量、升级排他锁完成元数据切换。

注意：Online DDL 不等于零锁表。Prepare 与 Commit 阶段仍需 MDL 锁，长事务未提交会阻塞 DDL，甚至引发后续请求排队雪崩。

二、可以不锁表（LOCK=NONE）的操作清单

• ADD INDEX / DROP INDEX / RENAME INDEX：INPLACE；

• 加列：8.0.12+ INSTANT（秒级），5.7 为 INPLACE 重建表；

• 删列：8.0.29+ INSTANT；

• 改默认值、自增起始值、注释：INSTANT，仅改元数据；

• RENAME COLUMN（8.0）：INPLACE；线上业务大表变更一直是 DBA 和后端同学的高风险操作。本文基于生产实践，梳理 MySQL Online DDL 的执行原理、哪些 ALTER 可以不锁表、哪些必须 COPY，以及避坑清单。

一、Online DDL 的核心原理

MySQL 5.6 起支持 Online DDL，将表结构变更拆为三个阶段：

1）Prepare：短暂获取 MDL 共享锁（毫秒级），校验表结构并确定执行算法（COPY / INPLACE / INSTANT）；

2）Execute：持有共享锁但不阻塞 DML，后台执行变更，期间并发的增删改写入 online log；

3）Commit：重放增量、升级排他锁完成元数据切换。

注意：Online DDL 不等于零锁表。Prepare 与 Commit 阶段仍需 MDL 锁，长事务未提交会阻塞 DDL，甚至引发后续请求排队雪崩。

二、可以不锁表（LOCK=NONE）的操作清单

• ADD INDEX / DROP INDEX / RENAME INDEX：INPLACE；

• 加列：8.0.12+ INSTANT（秒级），5.7 为 INPLACE 重建表；

• 删列：8.0.29+ INSTANT；

• 改默认值、自增起始值、注释：INSTANT，仅改元数据；

• RENAME COLUMN（8.0）：INPLACE；

• 扩展 VARCHAR 长度（不跨 255 边界）：INPLACE；

• 加删外键（foreign_key_checks=0）：INPLACE。

三、会锁表或必须 COPY 的操作

• 缩短 VARCHAR、修改 DECIMAL 精度、修改列类型（部分可 INPLACE）；

• CONVERT TO CHARACTER SET 表级字符集转换：COPY，全程锁写；

• 新增/修改主键：多数场景 COPY；

• 加 FULLTEXT 索引：INPLACE 但重建全文索引期间写入下降。

四、避坑建议

1）显式写 ALGORITHM=INPLACE, LOCK=NONE，无法满足时直接报错，避免静默锁表；

2）执行前检查长事务：SELECT * FROM information_schema.innodb_trx;；

3）监控 innodb_online_alter_log_max_size（默认 128M），高并发写入下 online log 溢出会导致 DDL 回滚；

4）超大表优先考虑 gh-ost 或 pt-online-schema-change，限流可控；

5）主从架构注意复制延迟，先在从库或影子表演练。

• 扩展 VARCHAR 长度（不跨 255 边界）：INPLACE；

• 加删外键（foreign_key_checks=0）：INPLACE。

三、会锁表或必须 COPY 的操作

• 缩短 VARCHAR、修改 DECIMAL 精度、修改列类型（部分可 INPLACE）；

• CONVERT TO CHARACTER SET 表级字符集转换：COPY，全程锁写；

• 新增/修改主键：多数场景 COPY；

• 加 FULLTEXT 索引：INPLACE 但重建全文索引期间写入下降。

四、避坑建议

1）显式写 ALGORITHM=INPLACE, LOCK=NONE，无法满足时直接报错，避免静默锁表；

2）执行前检查长事务：SELECT * FROM information_schema.innodb_trx;；

3）监控 innodb_online_alter_log_max_size（默认 128M），高并发写入下 online log 溢出会导致 DDL 回滚；

4）超大表优先考虑 gh-ost 或 pt-online-schema-change，限流可控；

5）主从架构注意复制延迟，先在从库或影子表演练。