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 或 INSTANTMySQL 会悄悄退回到 COPY。COPY 的实际代价有两块一是备份数据的时间成本数据量越大越慢二是锁表期间的业务阻断写入请求会直接排队。而且 COPY 不是“先备份再切换”的逻辑它是边复制边锁表所以不存在“快照切换”这种说法整体耗时通常远超同样数据量的 INPLACE 操作。1.2 INPLACE比较平衡的默认选择INPLACE 算法的关键是不复制整表数据而是直接在原表的表空间上做修改。比如加索引时它会扫描表数据在已有的数据页上构建索引页而不是重建整个表。这个过程中 MySQL 会尽量减少对 DML 的阻塞配合ALGORITHMINPLACE, LOCKNONE可以实现读写并发。但注意INPLACE 不等于“一定不重建表”。有些 INPLACE 操作仍然需要重建聚簇索引比如修改主键、变更列的顺序、调整字段类型导致行格式变化等。所谓 INPLACE 只是说“不用 COPY 一份完整的新表”但内部可能还是会把数据重新组织一遍这个过程同样耗 CPU、IO 和磁盘空间。实际使用中我见过不少人误以为加了ALGORITHMINPLACE就万事大吉结果执行到一半发现临时文件把磁盘打满了。原因就是他们把“修改字段类型”这种需要重建聚簇索引的操作当成普通 INPLACE忽略了它内部的物理重建成本。1.3 INSTANTMySQL 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把新列加到中间位置那就不能走 INSTANTMySQL 会选择 INPLACE 并且很可能需要重建表。另一个限制是 INSTANT 加列会占用 row 中的可变长度空间加的列越多行格式的元数据占用量越大后续可能因为行大小限制没法继续走 INSTANT。1.4 ALGORITHM 和 LOCK 参数组合怎么选执行 ALTER TABLE 时可以手动指定ALGORITHM和LOCK比如ALTER TABLE user ADD INDEX idx_name(name), ALGORITHMINPLACE, LOCKNONE;LOCK 的取值有 NONE、SHARED、EXCLUSIVENONE允许并发的读和写也就是最理想的在线操作SHARED允许并发读但阻塞写EXCLUSIVE读和写都不允许完全锁住我建议在写生产环境的 DDL 脚本时明确把 ALGORITHM 和 LOCK 写出来而不是省略。这样有两个好处一是如果 MySQL 认为该操作不支持你指定的组合它会直接报错而不是默默降级成更重量级的算法二是脚本的意图一目了然后面接手的人不会误判。举个例子如果你执行ALTER TABLE t MODIFY id BIGINT, ALGORITHMINPLACE, LOCKNONEMySQL 检查到该操作需要重建表但 LOCKNONE 不被该场景支持就会直接报错。这比让它自己选一个锁表的方案要安全得多——我宁可它报错也不希望它在我没注意的时候锁全表。2. 常见 DDL 场景实战拆解2.1 加字段用 INSTANT 还是 INPLACE加字段是日常最高频的 DDL。先看 MySQL 版本8.0.12 以上优先用 INSTANT。语法上加不加 ALGORITHM 都行但建议显式指定ALTER TABLE orders ADD COLUMN remark VARCHAR(64) DEFAULT , ALGORITHMINSTANT;注意这个操作要求新列加在表末尾而且不能是 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 LOCKNONE用户可以在索引构建期间继续读写。但有几个例外主键索引的增删改不能走 LOCKNONE因为主键是聚簇索引修改主键必然重建整张表唯一索引的添加在构建过程中会有专门的唯一性检查阶段这个阶段对并发写有限制锁级别经常会退到 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 之前都会先跑一组检查缺一不可表行数和物理大小行数决定了 DDL 最低耗时物理大小决定了重建表时的磁盘压力当前连接数和慢查询连接池打满时执行 DDL会加剧锁等待主从结构从库是否有延迟是否在主库窗口内磁盘剩余空间重建表需要的临时空间大概是表大小的 1 到 2 倍参数配置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 \ --host127.0.0.1 \ --userddl_user \ --passwordxxx \ --databaseappdb \ --tableorders \ --alterADD COLUMN remark VARCHAR(64) DEFAULT \ --execute \ --initially-drop-ghost-tabletrue \ --initially-drop-old-tabletrue \ --max-loadThreads_running50 \ --critical-loadThreads_running200 \ --chunk-size1000 \ --max-lag-millis1500 \ --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 被卡住后所有新请求都在等锁形成了一个连锁雪崩。解决方式是查到阻塞源头并 KILLSELECT * FROM performance_schema.metadata_locks;或者用SHOW PROCESSLIST找出Sleep状态且事务未关闭的连接。自动规避办法是给 DDL 加超时不要让它无限等SET SESSION lock_wait_timeout 5; ALTER TABLE t ADD COLUMN c INT, ALGORITHMINPLACE, LOCKNONE;只要在会话里设置lock_wait_timeoutDDL 等待 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_ciJOIN 时 MySQL 必须先把两边转成同一种排序规则才能比较结果就是不使用索引。排查这种问题的思路是对比两张表相关字段的 charset 和 collationSELECT 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支持 INSTANT5.7 完全没有这个功能。如果你们代码里用了这种语法但运维环境还是 5.7会直接语法报错。所以我建议凡是涉及生产环境 DDL 的流程第一件事就是确认 MySQL 小版本。同一类操作在 5.7.44 和 8.0.33 里执行计划可能完全不同。5. 工具选择原生 DDL 之外的第二条路5.1 pt-osc 和 gh-ost 的取舍pt-oscPercona Toolkit 的 pt-online-schema-change是老牌工具原理是用触发器捕获原表变更再同步到新表。它的优点是有 MySQL 就能跑不需要额外配置缺点是触发器的开销不小对高写入场景影响明显而且触发器本身也可能成为瓶颈。gh-ost 不需要触发器依靠 binlog 同步增量对主库的压力更小。它更适合高并发写入的在线环境。不过 gh-ost 对部署要求高一些需要 MySQL 开启 binlog ROW 格式需要能够连接主库获取 binlog。我的选择标准是这样的场景推荐方式理由大表加索引/加列gh-ost对主库负载影响小可动态调节环境不允许开启 binlog ROWpt-osc用触发器也能完成同步8.0.12 以上仅加末尾列原生 INSTANT毫秒级完成根本不需要工具表损坏或数据一致性要求极高原生 DDL 窗口期多工具反而复杂不如停写做变更5.2 pt-osc 的基本用法如果你环境里只有 Percona Toolkit用起来也不复杂pt-online-schema-change \ --alterADD INDEX idx_status(status) \ --host127.0.0.1 \ --userddl_user \ --passwordxxx \ --max-lag2 \ --chunk-size200 \ --execute \ Dappdb,torders执行过程中它会自动控制 chunk 大小如果从库延迟超过--max-lag会自动暂停。--chunk-size默认值有时候偏大对 IO 压力大的机器建议调小一点。5.3 工具执行完之后的收尾动作不管是 gh-ost 还是 pt-osc表切换完成后新表会成为正式表。这时候有几件事必须做检查新表的数据量和原表是否一致用SELECT COUNT(*)对比检查索引是否和目标结构一致用SHOW CREATE TABLE更新统计信息观察从库延迟和主库负载是否恢复正常我见过最离谱的一次是跑完 pt-osc 后原表的触发器没清理干净导致每一条写操作都额外触发一份重复同步性能下降了一半。Percona Toolkit 正常情况下会清理触发器但如果你中途手动中断过很可能残留。所以收尾检查不是可选项是必须项。最后再分享一个个人习惯任何 DDL哪怕是加一个普通索引在正式执行前我都先在测试环境跑一遍用一个模拟数据的表验证执行时间和参数配置。这一步看起来花费时间实际上能帮你避开 90% 的低级错误。毕竟线上环境和测试环境的差异很大提前踩一遍总比线上踩完再回头修要好。