MySQL DDL 执行指南：锁表原理、三种算法与生产环境避坑

MySQL 上执行 DDL，看起来就是几条 ALTER 语句的事，但真正在业务环境里动过手的朋友都清楚，这是数据库运维里最容易“翻车”的一类操作。尤其是表到了千万级、亿级，哪怕只是加一个索引，一条 SQL 都可能把主库拖到报警，甚至把整个业务卡死。

这篇东西不打算给你背语法清单，MySQL 官方文档里的 ALTER TABLE 语法写得很全，缺的是“哪种操作会锁表”“哪个版本支持秒级加列”“线上大表怎么安全动手”这些实战经验。我会把 DDL 的底层实现路径、常见场景该选哪种方案、生产环境的执行流程，以及我实际踩过的坑，全部拆开讲一遍。适合 DBA、后端开发、运维，也适合准备架构面试的读者参考。

1. MySQL DDL 背后的三种执行路径

1.1 COPY：最笨重但兼容性最强的方案

COPY 算法本质上就是建一张新表：MySQL 会按 ALTER 之后的表结构创建一张空表，然后把原表数据一条条搬过去，搬完后再把原表删掉，把新表重命名成原表的名字。

这个过程中，表一直处于被锁的状态。MySQL 5.5 及更早版本只能这么干，所以那个年代做 DDL 基本等于停机维护。5.6 之后虽然引入了 Online DDL，但 COPY 仍然是一个 fallback 选项，一旦你指定的操作不支持 INPLACE 或 INSTANT，MySQL 会悄悄退回到 COPY。

COPY 的实际代价有两块：一是备份数据的时间成本，数据量越大越慢；二是锁表期间的业务阻断，写入请求会直接排队。而且 COPY 不是“先备份再切换”的逻辑，它是边复制边锁表，所以不存在“快照切换”这种说法，整体耗时通常远超同样数据量的 INPLACE 操作。

1.2 INPLACE：比较平衡的默认选择

INPLACE 算法的关键是不复制整表数据，而是直接在原表的表空间上做修改。比如加索引时，它会扫描表数据，在已有的数据页上构建索引页，而不是重建整个表。这个过程中 MySQL 会尽量减少对 DML 的阻塞，配合ALGORITHM=INPLACE, LOCK=NONE可以实现读写并发。

但注意，INPLACE 不等于“一定不重建表”。有些 INPLACE 操作仍然需要重建聚簇索引，比如修改主键、变更列的顺序、调整字段类型导致行格式变化等。所谓 INPLACE 只是说“不用 COPY 一份完整的新表”，但内部可能还是会把数据重新组织一遍，这个过程同样耗 CPU、IO 和磁盘空间。

实际使用中，我见过不少人误以为加了ALGORITHM=INPLACE就万事大吉，结果执行到一半发现临时文件把磁盘打满了。原因就是他们把“修改字段类型”这种需要重建聚簇索引的操作当成普通 INPLACE，忽略了它内部的物理重建成本。

1.3 INSTANT：MySQL 8.0 的秒级快车道

INSTANT 算法是 MySQL 8.0.12 开始引入的，它的特点是只修改表定义（元数据），不触碰数据文件。所以执行时间是毫秒级或者秒级，不管表有多大，都不会因为数据量增长而变慢。

哪些操作可以用 INSTANT？主要是：在表末尾新增列、修改列默认值、删除列（8.0.29 起支持，不同小版本有差异）、修改列名为新的名字、增加 ENUM 和 SET 类型允许值等。

这里最实用的场景就是“线上大表加字段”。在 8.0.12 之前，大表加字段要么忍受长锁，要么用 gh-ost 这类工具绕。8.0.12 之后，如果在表末尾加一个可空或者带默认值的列，理论上可以瞬间完成。这也成了很多团队升级 8.0 的核心动力之一。

但要特别注意，INSTANT 只支持“在末尾加列”。如果你用ALTER TABLE t ADD COLUMN c INT NOT NULL AFTER id，把新列加到中间位置，那就不能走 INSTANT，MySQL 会选择 INPLACE 并且很可能需要重建表。另一个限制是 INSTANT 加列会占用 row 中的可变长度空间，加的列越多，行格式的元数据占用量越大，后续可能因为行大小限制没法继续走 INSTANT。

1.4 ALGORITHM 和 LOCK 参数组合怎么选

执行 ALTER TABLE 时可以手动指定ALGORITHM和LOCK，比如：

ALTER TABLE user ADD INDEX idx_name(name), ALGORITHM=INPLACE, LOCK=NONE;

LOCK 的取值有 NONE、SHARED、EXCLUSIVE：

• NONE：允许并发的读和写，也就是最理想的在线操作
• SHARED：允许并发读，但阻塞写
• EXCLUSIVE：读和写都不允许，完全锁住

我建议在写生产环境的 DDL 脚本时，明确把 ALGORITHM 和 LOCK 写出来，而不是省略。这样有两个好处：一是如果 MySQL 认为该操作不支持你指定的组合，它会直接报错，而不是默默降级成更重量级的算法；二是脚本的意图一目了然，后面接手的人不会误判。

举个例子，如果你执行ALTER TABLE t MODIFY id BIGINT, ALGORITHM=INPLACE, LOCK=NONE，MySQL 检查到该操作需要重建表，但 LOCK=NONE 不被该场景支持，就会直接报错。这比让它自己选一个锁表的方案要安全得多——我宁可它报错，也不希望它在我没注意的时候锁全表。

2. 常见 DDL 场景实战拆解

2.1 加字段：用 INSTANT 还是 INPLACE

加字段是日常最高频的 DDL。先看 MySQL 版本，8.0.12 以上优先用 INSTANT。语法上加不加 ALGORITHM 都行，但建议显式指定：

ALTER TABLE orders ADD COLUMN remark VARCHAR(64) DEFAULT '' , ALGORITHM=INSTANT;

注意，这个操作要求新列加在表末尾，而且不能是 AUTO_INCREMENT。如果新列有DEFAULT且非随机值，INSTANT 是可以处理的，因为它只是记录“新增了一个字段”，历史行的该字段值在读出来时都会返回默认值。

如果不满足 INSTANT 条件，只好走 INPLACE。这里要特别留意：在 8.0.12 之前，加列即使写在末尾，也可能导致聚簇索引重建，因为行格式中列的位置信息会变化。所以老版本跑大表加字段，我建议优先用业务低峰期执行，或者直接用 gh-ost。

2.2 改字段类型和长度：最容易翻车的地方

改字段类型这个操作，大多数情况下逃不过重建表。比如把 INT 改成 BIGINT、把 VARCHAR 改成 TEXT、把 DATETIME 改成 TIMESTAMP，这些都涉及行内数据的重新编码，通常只能走 COPY 或重聚簇的 INPLACE。

字段长度变更稍微特殊一点。VARCHAR 长度增大到一定程度之前是 INPLACE 支持的，原因是 VARCHAR 存储时有一个字节数组来记录长度，长度扩展不超过最大值限制时，MySQL 能原地修改。但如果扩展后需要改变行格式或页内存储布局，就会退化为 COPY。长度缩小也一样，基本都要重建表。

这里我踩过一个具体的坑：有一张日志表，字段 content 原来是 VARCHAR(100)，业务需求要改成 VARCHAR(5000)。我一开始信心满满地跑在线 DDL，觉得只是长度变化，应该很快，结果它直接重建了整个表，跑了十几分钟，IO 和从库延迟都飙起来了。事后查文档才发现，VARCHAR 长度过大时，MySQL 会把存储格式从“短行”切到“长行”，这属于行格式变化，没法原地完成。

所以我的建议是：改字段类型前，先确认目标类型和源类型在存储上是否兼容，再决定工具和窗口。别因为一句“ALTER 而已”就轻视它。

2.3 索引操作：从需求角度而非语法角度选

加索引和删索引是 DDL 里的“高频操作”，也是 Online DDL 支持最完善的部分。ADD INDEX在 5.6 之后基本可以做到 INPLACE + LOCK=NONE，用户可以在索引构建期间继续读写。

但有几个例外：

• 主键索引的增删改不能走 LOCK=NONE，因为主键是聚簇索引，修改主键必然重建整张表
• 唯一索引的添加在构建过程中会有专门的唯一性检查阶段，这个阶段对并发写有限制，锁级别经常会退到 SHARED
• 全文索引、空间索引的处理逻辑又和普通 B-Tree 索引不同，不能一概而论

索引操作中还有一个容易忽视的点：重复索引。很多团队遇到慢查询，第一反应是加索引，但没检查是否已经存在一个能覆盖当前需求的联合索引。加索引本身是 DDL，再小也有成本，更重要的是多出来一个冗余索引会拖慢写入，占磁盘空间。我在代码评审阶段就要求先跑SHOW INDEX FROM table，确认没有现成索引可用才允许走 DDL。

2.4 字符集、排序规则和表级属性调整

改表的字符集，比如从 utf8mb3 改成 utf8mb4，很多人以为只是改个元数据。真实情况是，MySQL 需要把每一行的字符串数据都重新编码，这是典型的全表扫描 + 数据重建操作。就算表里全是数字，只要列类型里有 CHAR/VARCHAR/TEXT，就可能触发数据转换。

改成某种排序规则（COLLATE）也类似。如果表里某些列参与 JOIN 或 WHERE 条件，排序规则变更可能导致索引无法使用。我之前经历过一次事故：某张用户表的 nickname 列从 utf8mb4_general_ci 改成 utf8mb4_0900_ai_ci，结果和另一张关联表的排序规则不一致，导致关联查询全表扫描。生产环境 30 分钟后才被业务方反馈，排查半天才发现是 COLLATE 冲突。

涉及字符集和排序规则的 DDL，执行前要做三件事：确认所有关联表的排序规则、检查所有 JOIN 字段是否同为一致、执行后跑一遍关键查询的慢日志，确认没有新出现的全表扫描。

3. 生产环境执行 DDL 的可落地方案

3.1 执行前：先查这五样东西

我在生产环境执行任何 DDL 之前，都会先跑一组检查，缺一不可：

1. 表行数和物理大小：行数决定了 DDL 最低耗时，物理大小决定了重建表时的磁盘压力
2. 当前连接数和慢查询：连接池打满时执行 DDL，会加剧锁等待
3. 主从结构：从库是否有延迟，是否在主库窗口内
4. 磁盘剩余空间：重建表需要的临时空间大概是表大小的 1 到 2 倍
5. 参数配置：innodb_online_alter_log_size（在线变更日志缓冲）、innodb_sort_buffer_size（排序缓冲）等

这些信息一条 SQL 或一条命令就能拿，但真正每次执行前都查的人不多。我见过有人加一个索引，跑到一半报磁盘满，查了才发现当时 /data 分区剩余空间不足 5%。这种低级事故完全可以通过事前检查避免。

3.2 大表 DDL 的三条路线

遇到几十 GB 甚至几百 GB 的表，我一般会按情况选择下面三条路线：

第一条：用 MySQL 原生的 Online DDL，只适用于确定支持 INSTANT 或 INPLACE 且锁影响小的场景，比如在表尾加列。风险点是执行时间不可控，磁盘压力大。

第二条：用第三方工具，常见的是 gh-ost 和我后面会介绍的 pt-osc。它通过影子表迁移数据，完成后切换表名。优点是不直接阻塞主库读写，适合超大表。

第三条：业务侧切换。先新建一张新结构的表，应用写入切到新表，再批量迁移旧表数据。最重但最可控，尤其是需要调整表结构而且不能容忍写阻塞的场景。

三条路线不是互斥的，很多时候我会先用原生 DDL 跑一个语法验证，再决定要不要上工具。

3.3 执行中：看哪些指标

执行中的监控比执行前检查更重要。原生 Online DDL 执行时，我至少盯三层指标：

• 进程状态：SHOW PROCESSLIST里能看到 DDL 的进度，虽然显示不够精细
• 磁盘空间：如果是重建表或建大索引，临时文件会持续增大
• 主从延迟：主库 DDL 产生的负载会传递到从库，尤其从库执行同样 DDL 时可能追不上主库

还有一个容易忽略的点：innodb_online_alter_log_size决定 DDL 期间并发 DML 产生的在线日志能缓冲多少。如果这个值设得太小，而 DDL 期间业务写入又很大，缓冲会被填满，MySQL 只能报错中止 DDL。所以执行前最好评估一下业务写入速率，需要时把它改大一些。

3.4 回滚与误操作挽救

DDL 不像事务，没有简单的 ROLLBACK。MySQL 8.0 的原子 DDL 确实能让失败时不留中间状态，但那也只是“失败即回滚”，不是“成功后还能撤销”。

所以真正的备份策略只能在 DDL 之前做。我的习惯是：

• 小表：在测试环境或者本机导出一份 SQL 文件
• 大表：按主键范围分批 mysqldump，或者依赖已有的物理备份/从库快照
• 更保险的做法：给实例挂一个延迟从库，设置延迟同步时间，比如 1 小时。如果 DDL 后发现了问题，可以直接把延迟从库的旧数据导出来回填

很多团队舍不得搭延迟从库，我强烈建议大表相关的 DDL 前先花半小时把这件事做了。它可能救你一次。

3.5 用 gh-ost 处理超大表 DDL 的标准姿势

gh-ost 的原理我简单说下：它先创建一张影子表，结构是 DDL 之后的目标结构。然后从原表以 chunks 形式读取数据，同时订阅 binlog 事件，把 DDL 执行期间产生的增量变更也同步到影子表。等数据追平后，再通过原子 rename 把影子表切换成原表。

我在线上跑 gh-ost 的常用命令是这样：

gh-ost \ --host=127.0.0.1 \ --user=ddl_user \ --password=xxx \ --database=appdb \ --table=orders \ --alter="ADD COLUMN remark VARCHAR(64) DEFAULT ''" \ --execute \ --initially-drop-ghost-table=true \ --initially-drop-old-table=true \ --max-load="Threads_running=50" \ --critical-load="Threads_running=200" \ --chunk-size=1000 \ --max-lag-millis=1500 \ --approximate-old-rowcount

跑的时候，我会在另一个终端用SHOW PROCESSLIST观察它的进展。gh-ost 的好处是能根据负载自动节流，比如 Threads_running 超过阈值就暂停迁移。这个机制比原生 DDL 的“一条 SQL 跑到底”要温柔得多。

需要提醒的是，gh-ost 要求 binlog_format 是 ROW，并且需要主库的 binlog 权限。如果生产环境的历史 binlog 配置不符合要求，它根本跑不起来。

4. 我遇到的 MySQL DDL 故障排查记录

4.1 Metadata Lock：一句话把整个库锁死

这是 MySQL DDL 最经典的坑。现象是 ALTER 语句一直卡在Waiting for table metadata lock状态，而后面的所有读写请求全部堆积。根本原因通常是有一个长时间未提交的事务，比如代码里开了事务但没 commit，或者一个跑了很久的 SELECT 一直没结束。

一次事故我印象很深：有人在凌晨跑了一个大表的加列 DDL，结果第二天早上业务告警，应用日志里全是连接超时。排查时发现，一个后台报表任务在 DDL 执行前拿到了表的 metadata lock，但任务异常挂起了，事务一直没提交。ALTER 被卡住后，所有新请求都在等锁，形成了一个连锁雪崩。

解决方式是查到阻塞源头并 KILL：

SELECT * FROM performance_schema.metadata_locks;

或者用SHOW PROCESSLIST找出Sleep状态且事务未关闭的连接。

自动规避办法是给 DDL 加超时，不要让它无限等：

SET SESSION lock_wait_timeout = 5; ALTER TABLE t ADD COLUMN c INT, ALGORITHM=INPLACE, LOCK=NONE;

只要在会话里设置lock_wait_timeout，DDL 等待 metadata lock 超过 5 秒就直接报错退出。这虽然不能完成操作，但至少不会让整个库被卡死。

4.2 Row size too large：加列加到一个临界点突然报错

8.0 里，如果一张表已经有接近行大小上限的宽列，再往中间加列或者加一个很长的字符列，就可能遇到：

ERROR 1118 (42000): Row size too large. The maximum row size for the used table type.

MySQL 的 InnoDB 行格式默认是 dynamic，数据页 16KB，理论上单行数据不能超过约 65535 字节。一个 VARCHAR(255) 在 utf8mb4 下可能占 1020 字节，二十来个这样的列就能接近上限。

出现这个报错时，说明单纯的 ALTER 已经无法解决结构问题。我通常给的方案是三类：

• 把超长列改成 TEXT/BLOB，让数据存到溢出页
• 拆表，把宽列拆到一张独立的扩展表
• 调整行格式，看是否能用 DYNAMIC 减少行长度的元数据开销

这属于“表结构设计”层面的问题，DDL 只是引子。建议在设计表结构时，就给未来的容量留出余量，别把字段加得太满。

4.3 字符集和排序规则不一致：加索引反而让查询更慢

这个坑很反直觉。某次业务反馈，某张表加了索引之后，关联查询反而更慢了。我看了一下执行计划，发现索引根本没有被用上。原因是这个字段的排序规则和 JOIN 另一张表的字段排序规则不一致，MySQL 只能做隐式转换，导致索引失效。

比如表 A 的 user_id 是 utf8mb4_general_ci，表 B 的 user_id 是 utf8mb4_0900_ai_ci，JOIN 时 MySQL 必须先把两边转成同一种排序规则才能比较，结果就是不使用索引。

排查这种问题的思路是对比两张表相关字段的 charset 和 collation：

SELECT TABLE_NAME, COLUMN_NAME, CHARACTER_SET_NAME, COLLATION_NAME FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = 'appdb' AND COLUMN_NAME IN ('user_id', 'nickname');

如果发现不一致，需要统一排序规则。改成哪一种没有绝对答案，但团队内部至少要做到每个库、每张表用同一套规则，这样才能避免隐式转换的坑。

4.4 索引失效或重复索引：DDL 完成后先检查再上线

有些索引相关 DDL 执行成功后，发现查询计划仍然走全表扫描。原因可能是统计信息没更新。MySQL 的优化器依赖统计信息来选择索引，大表结构变更后统计信息可能没跟上。

处理方式很简单，执行完 DDL 后顺手跑一条：

ANALYZE TABLE t;

这个动作成本低，收益却很大。尤其对 5.7 之前的版本来说，统计信息更新不及时的问题更明显。8.0 的自动统计能力好一些，但也不代表能完全省掉这一步。

还有一种情况是重复索引。ALTER TABLE t ADD INDEX idx_a(a)，但表里已经存在(a, b)联合索引，此时 idx_a 是多余的。虽然 DDL 不会报错，但它浪费空间，影响写入性能。事前用SHOW INDEX比对一次，能省掉后面的一堆麻烦。

4.5 磁盘空间不足导致 DDL 中断

大表重建时，InnoDB 会先在临时目录或者表空间里生成临时文件。如果磁盘剩余空间不够，DDL 会直接失败，而且可能留下半截临时文件。

我有个经验值：执行任何需要重建聚簇索引的 DDL 前，磁盘剩余空间最好不低于表大小的 1.5 倍。更紧张的话也要至少留出 1 倍，否则执行中一旦临时文件超过剩余空间，整件事就非常被动。

排查方法很直接：

df -h /var/lib/mysql du -sh /var/lib/mysql/dbname/t.ibd

如果磁盘空间不足，先清理日志、归档数据，或者给实例加临时盘。千万别硬跑。

4.6 版本差异：5.7 和 8.0 的行为完全不同

MySQL 5.7 没有 INSTANT 算法，8.0.12 之后才有，这让加字段的体验天差地别。另一个重要差异是原子 DDL。8.0 的 DDL 是原子的，失败后不会留下部分修改；5.7 及之前，一个很大的 ALTER 中途失败，可能留下新列但数据不完整，或者索引状态异常的情况。

还有一个容易被忽略的点：8.0 的RENAME COLUMN支持 INSTANT，5.7 完全没有这个功能。如果你们代码里用了这种语法，但运维环境还是 5.7，会直接语法报错。

所以我建议，凡是涉及生产环境 DDL 的流程，第一件事就是确认 MySQL 小版本。同一类操作在 5.7.44 和 8.0.33 里执行计划可能完全不同。

5. 工具选择：原生 DDL 之外的第二条路

5.1 pt-osc 和 gh-ost 的取舍

pt-osc（Percona Toolkit 的 pt-online-schema-change）是老牌工具，原理是用触发器捕获原表变更，再同步到新表。它的优点是有 MySQL 就能跑，不需要额外配置；缺点是触发器的开销不小，对高写入场景影响明显，而且触发器本身也可能成为瓶颈。

gh-ost 不需要触发器，依靠 binlog 同步增量，对主库的压力更小。它更适合高并发写入的在线环境。不过 gh-ost 对部署要求高一些：需要 MySQL 开启 binlog ROW 格式，需要能够连接主库获取 binlog。

我的选择标准是这样的：

5.2 pt-osc 的基本用法

如果你环境里只有 Percona Toolkit，用起来也不复杂：

pt-online-schema-change \ --alter="ADD INDEX idx_status(status)" \ --host=127.0.0.1 \ --user=ddl_user \ --password=xxx \ --max-lag=2 \ --chunk-size=200 \ --execute \ D=appdb,t=orders

执行过程中它会自动控制 chunk 大小，如果从库延迟超过--max-lag，会自动暂停。--chunk-size默认值有时候偏大，对 IO 压力大的机器建议调小一点。

5.3 工具执行完之后的收尾动作

不管是 gh-ost 还是 pt-osc，表切换完成后，新表会成为正式表。这时候有几件事必须做：

• 检查新表的数据量和原表是否一致，用SELECT COUNT(*)对比
• 检查索引是否和目标结构一致，用SHOW CREATE TABLE
• 更新统计信息
• 观察从库延迟和主库负载是否恢复正常

我见过最离谱的一次，是跑完 pt-osc 后，原表的触发器没清理干净，导致每一条写操作都额外触发一份重复同步，性能下降了一半。Percona Toolkit 正常情况下会清理触发器，但如果你中途手动中断过，很可能残留。所以收尾检查不是可选项，是必须项。

最后再分享一个个人习惯：任何 DDL，哪怕是加一个普通索引，在正式执行前，我都先在测试环境跑一遍，用一个模拟数据的表验证执行时间和参数配置。这一步看起来花费时间，实际上能帮你避开 90% 的低级错误。毕竟线上环境和测试环境的差异很大，提前踩一遍总比线上踩完再回头修要好。