MySQL大表变更最佳实践：在线DDL工具选型与回滚方案设计

大家好，我是数据库小学妹 👋

凌晨两点多，我被一个告警短信炸醒。订单服务响应时间从五十毫秒飙到了八秒，所有接口都在超时。我打开数据库一看，几十条连接全卡在Waiting for table metadata lock。

排查了半个小时，发现是一个同事下午执行了一条ALTER TABLE加字段，没有指定任何算法参数。MySQL按默认方式执行，需要先获取整张表的MDL排他锁才能开始变更。但当时有几个长查询在跑，ALTER一直等锁等不到。更麻烦的是，ALTER拿到锁队列的优先权之后，后面所有对新请求的SELECT和INSERT全被这条ALTER堵住了。连接池瞬间被打满，应用端跟着雪崩。那条ALTER等了四十分钟才跑完，服务恢复的时候，天都快亮了。

今天把这段经历和后来总结的流程写出来，希望能帮你避开这个坑。

MySQL三种ALTER TABLE算法，到底有什么区别

那次事故之后，我把MySQL的ALTER TABLE底层机制翻了个底朝天。原来ALTER TABLE不是只有一种执行方式，它有三种算法，性能和影响完全不同。

**第一种是COPY。**创建一张新表，按新结构把旧表的数据一行行拷过去，拷完之后删除旧表，把新表重命名过来。整个过程源表是锁的，写入全部阻塞。数据量越大，锁的时间越长。我那晚遇到的就是这种。

**第二种是INPLACE。**在原表上直接修改元数据，不需要拷数据。大部分写入操作可以继续执行，只有一瞬间的元数据锁。这是在线DDL的核心。但不是所有操作都支持INPLACE。加索引支持，改列类型就不一定支持。

第三种是INSTANT。 MySQL 8.0引入的。只在数据字典里改个标记，零数据拷贝，秒级完成。但支持的操作非常有限，目前主要是加列（加在表末尾）和删除列。

我画了一张对照表，方便快速判断：

关键教训：**执行ALTER之前，先确认ALGORITHM是什么。**可以在语句里显式指定 ALGORITHM=INPLACE，如果不支持，MySQL会直接报错而不是默默降级到COPY。

-- 显式指定算法，不支持则报错，避免意外降级
ALTER TABLE orders 
    ADD COLUMN tenant_id BIGINT,
    ALGORITHM=INPLACE, 
    LOCK=NONE;

不过有一个容易被忽略的坑：ALGORITHM=INSTANT 即使指定了，MySQL也可能悄悄降级。当列包含BLOB类型、表有全文索引、或者使用了某些特殊存储格式时，INSTANT会被静默退化为INPLACE。我有一次加一个字段，明明指定了INSTANT，结果跑了四十多分钟。后来查了才发现，表里有个被遗忘的BLOB列，INSTANT不支持，退化为INPLACE后又碰上了MDL锁排队。从那以后，我每次DDL执行完都会跑一遍 SHOW PROCESSLIST，确认实际生效的算法是不是自己指定的那个。

LOCK=NONE 的意思是执行期间允许并发读写。如果操作不支持，同样会报错。这两个参数加上去，至少不会在生产上悄无声息地锁表。

在线DDL工具：pt-osc和gh-ost

但问题是，有些操作MySQL原生的INPLACE也不支持。比如大表加字段（表末尾以外位置）、加外键约束、改字符集。这些场景只能靠第三方在线DDL工具。

我用过的有两个：pt-online-schema-change（简称pt-osc）和gh-ost。

**pt-osc的思路是影子表加触发器。**创建一张新表，按新结构建好。然后在源表上加INSERT、UPDATE、DELETE三个触发器，把变更同步到新表。同步完数据后，原子替换两张表。这个方案的优点是成熟稳定，Percona出品，用的人多。但触发器本身有性能开销。源表写入量特别大时，触发器会成为瓶颈。而且触发器不能和已有的触发器共存，源表如果已经有触发器，pt-osc就用不了。

**gh-ost的思路是影子表加binlog解析。**它也创建影子表，但不依赖触发器。它把自己伪装成一个从库，通过解析binlog来捕获源表的变更，然后应用到影子表上。没有触发器，性能影响更小。而且支持暂停、限速、动态调整。但前提是binlog格式必须是ROW。如果你的库用的是STATEMENT或MIXED，gh-ost跑不起来。后来我们团队基本都用gh-ost了。原因很简单：触发器这个东西，能不用就不用。它藏在表结构里，不容易被发现，出问题也难排查。binlog解析虽然配置麻烦一点，但透明度高。

用gh-ost之前要检查几件事：binlog_format必须是ROW；目标表的写入频率，高并发下shadow表的同步延迟要评估；目标库有足够的磁盘空间存两张表；执行前先 --dry-run，确认没问题再切 --execute。

# gh-ost 干跑测试，不真正执行
gh-ost \
  --user="dba" --password="xxx" --host="127.0.0.1" \
  --database="shop" --table="orders" \
  --alter="ADD COLUMN status TINYINT DEFAULT 0" \
  --max-load="Threads_running=50" \
  --critical-load="Threads_running=100" \
  --dry-run

MDL锁阻塞：ALTER执行了，但应用全卡住

你以为用了正确的算法或在线工具就安全了？不一定。

我有一次在业务低峰期执行ALTER TABLE，ALGORITHM=INPLACE，LOCK=NONE，按理说不影响读写。但执行后，应用还是全部卡住了。查慢查询日志，满屏的Waiting for table metadata lock。

MDL（Metadata Lock，元数据锁）是MySQL 5.5引入的。只要有人在读这张表（哪怕是一条简单的SELECT），MySQL就会给这张表加上MDL读锁。此时如果要对表结构做变更，就需要获取MDL写锁。写锁和读锁互斥，ALTER就得等那个SELECT执行完。但实际情况往往是这样的：一个长事务在跑SELECT，持有了MDL读锁。这时候ALTER TABLE来了，需要MDL写锁，被阻塞。后面所有对这个表的查询，全被ALTER堵住。一个ALTER，拖垮整张表。

排查方法：

-- 查找 MDL 锁等待
SELECT 
    p.ID,
    p.USER,
    p.HOST,
    p.DB,
    p.COMMAND,
    p.TIME,
    p.STATE,
    p.INFO
FROM information_schema.processlist p
WHERE p.STATE LIKE '%metadata lock%'
   OR p.INFO LIKE '%ALTER%';

找到持有MDL锁的事务后，不能直接KILL。要先看这个事务在干什么，评估杀掉的影响。如果是报表类的只读查询，杀掉重跑就行。如果是核心业务的事务，得等它自然结束。后来我们在团队里立了个规矩：生产库上的ALTER，执行前先检查有没有长事务在跑。用 SHOW PROCESSLIST 看一遍，有超过十秒的SELECT就先不执行。

回滚方案：别等出了事才想退路

很多人做变更只想着"怎么改成功"，没想过"改坏了怎么退回来"。

回滚ALTER TABLE，不是你想的那么简单。末尾加列的回滚，如果用的INSTANT，回滚很快，删掉列就行。但如果走了COPY或INPLACE，回滚的代价和正向操作一样大，要再跑一次ALTER。改列类型的回滚就更麻烦了，数据已经被转换了。从VARCHAR改成INT，原来的字符串格式丢了，回滚不回来。只能从备份恢复。删除列的回滚也一样，列删了，数据就没了。回滚就是重新加列，但数据找不回来。

所以，真正的回滚方案应该在变更前就准备好：变更前做完整备份，确认备份可用。加列或改结构，保留旧列的数据映射关系。删除列之前，先把数据导出到临时表。回滚脚本提前写好，验证过能用。别把"备份"当成回滚方案。备份恢复需要时间，生产故障等不了那么久。真正可用的回滚，是执行完之后几秒钟内就能生效的反向操作。

一套可直接复用的变更管理SOP

那次事故之后，我跟leader提了变更流程的想法，拉着几个同事一起整理了一套SOP。刚开始大家觉得麻烦，后来出了两次线上问题，再也没人抱怨了。第一步是需求评审，写清楚变更内容，评估影响范围。是大表还是小表？在业务高峰期还是低峰期？影响哪些接口？第二步是SQL评审，确认ALGORITHM，确认LOCK级别。大表操作必须用在线DDL工具，不能直接用原生ALTER。第三步是回滚方案，写好回滚脚本，在预发环境验证。不能只写"从备份恢复"，要有具体的反向操作。第四步是预发验证，用和生产同等数据量的预发库跑一遍。记录执行时间，观察性能影响。第五步是时间窗口，选业务低峰期执行。提前通知相关方，准备好回滚条件。第六步是执行与监控，执行过程中实时监控连接数、慢查询、CPU。超过阈值立即暂停。第七步是验证，确认表结构正确，抽样数据无误，性能指标正常。第八步是观察，变更后观察一到两天，确认没有慢查询和连接异常，关闭变更工单。

听起来繁琐，但比起凌晨三点起来救火，这点麻烦算不了什么。

信创场景下的变更管理

后来我接触了信创项目，用的是KingbaseES。发现国产数据库在变更安全这块做了不少工作。

KES对DDL执行有更细粒度的安全管控，类似Oracle的权限体系。大表结构变更需要更高级别的审批。它的INPLACE支持和MySQL有些差异，迁移前需要逐项验证哪些操作能在线做，哪些必须停机。另外，KES的安全审计模块会自动记录所有DDL操作，包括执行人、时间、SQL内容和执行结果。事后追溯非常方便。这在金融和政企场景里是硬性要求。

国产数据库在安全合规上确实下了功夫。变更流程更严格不是坏事，至少能让你少犯低级错误。但也不能被流程捆住手脚。紧急情况下，得有快速通道，不能因为等审批耽误故障恢复。

避坑清单

**第一，永远不要在生产库上直接执行没评审过的ALTER TABLE。**你以为只是一行加字段，但它可能是COPY算法，锁住千万级表四十分钟。每次变更都要走流程，写清楚内容、影响范围和回滚方案。

**第二，大表变更之前，先用 ALGORITHM=INPLACE, LOCK=NONE 探路。**如果MySQL不支持会直接报错，不会默默降级到COPY。更稳妥的做法是用gh-ost或pt-osc，把影响降到最低。

**第三，回滚方案必须是可秒级执行的反向操作，不是"从备份恢复"。**变更前做完整备份是底线，但备份恢复太慢，救不了生产故障。回滚脚本提前写好，在预发环境验证过，这才是真正的回滚能力。

**第四，每次变更都记录操作日志、执行时长、遇到的坑。**三个月后你会感谢现在的自己。这些记录是团队最宝贵的经验资产，比任何文档都有价值。

回到那个凌晨的事故。那条锁了四十分钟的ALTER TABLE，后来成了我们团队变更管理改革的起点。现在回头看，问题的根源不是技术，是习惯。大家都觉得"改个表结构而已"，没人当回事。但生产环境的每一次变更，都是一次风险。你能做的不是避免风险，而是控制风险。

理解底层原理，准备好回滚方案，把流程立起来。这三件事做好了，改库就不再是走钢丝。

朋友，你在生产环境做变更时踩过哪些坑？欢迎聊聊。

我是数据库小学妹，咱们下篇见 👋