大家好我是数据库小学妹 凌晨两点多我被一个告警短信炸醒。订单服务响应时间从五十毫秒飙到了八秒所有接口都在超时。我打开数据库一看几十条连接全卡在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引入的。只在数据字典里改个标记零数据拷贝秒级完成。但支持的操作非常有限目前主要是加列加在表末尾和删除列。我画了一张对照表方便快速判断操作ALGORITHM是否锁表适用场景末尾加列MySQL 8.0INSTANT否日常需求加索引INPLACE短暂元数据锁性能优化改列类型COPY全程写锁慎用需评估加约束INPLACE/COPY视操作而定大表需走在线工具删除列INSTANT8.0.12否日常需求关键教训**执行ALTER之前先确认ALGORITHM是什么。**可以在语句里显式指定ALGORITHMINPLACE如果不支持MySQL会直接报错而不是默默降级到COPY。-- 显式指定算法不支持则报错避免意外降级ALTERTABLEordersADDCOLUMNtenant_idBIGINT,ALGORITHMINPLACE,LOCKNONE;不过有一个容易被忽略的坑ALGORITHMINSTANT即使指定了MySQL也可能悄悄降级。当列包含BLOB类型、表有全文索引、或者使用了某些特殊存储格式时INSTANT会被静默退化为INPLACE。我有一次加一个字段明明指定了INSTANT结果跑了四十多分钟。后来查了才发现表里有个被遗忘的BLOB列INSTANT不支持退化为INPLACE后又碰上了MDL锁排队。从那以后我每次DDL执行完都会跑一遍SHOW PROCESSLIST确认实际生效的算法是不是自己指定的那个。LOCKNONE的意思是执行期间允许并发读写。如果操作不支持同样会报错。这两个参数加上去至少不会在生产上悄无声息地锁表。在线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或MIXEDgh-ost跑不起来。后来我们团队基本都用gh-ost了。原因很简单触发器这个东西能不用就不用。它藏在表结构里不容易被发现出问题也难排查。binlog解析虽然配置麻烦一点但透明度高。用gh-ost之前要检查几件事binlog_format必须是ROW目标表的写入频率高并发下shadow表的同步延迟要评估目标库有足够的磁盘空间存两张表执行前先--dry-run确认没问题再切--execute。# gh-ost 干跑测试不真正执行gh-ost\--userdba--passwordxxx--host127.0.0.1\--databaseshop--tableorders\--alterADD COLUMN status TINYINT DEFAULT 0\--max-loadThreads_running50\--critical-loadThreads_running100\--dry-runMDL锁阻塞ALTER执行了但应用全卡住你以为用了正确的算法或在线工具就安全了不一定。我有一次在业务低峰期执行ALTER TABLEALGORITHMINPLACELOCKNONE按理说不影响读写。但执行后应用还是全部卡住了。查慢查询日志满屏的Waiting for table metadata lock。MDLMetadata Lock元数据锁是MySQL 5.5引入的。只要有人在读这张表哪怕是一条简单的SELECTMySQL就会给这张表加上MDL读锁。此时如果要对表结构做变更就需要获取MDL写锁。写锁和读锁互斥ALTER就得等那个SELECT执行完。但实际情况往往是这样的一个长事务在跑SELECT持有了MDL读锁。这时候ALTER TABLE来了需要MDL写锁被阻塞。后面所有对这个表的查询全被ALTER堵住。一个ALTER拖垮整张表。排查方法-- 查找 MDL 锁等待SELECTp.ID,p.USER,p.HOST,p.DB,p.COMMAND,p.TIME,p.STATE,p.INFOFROMinformation_schema.processlist pWHEREp.STATELIKE%metadata lock%ORp.INFOLIKE%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算法锁住千万级表四十分钟。每次变更都要走流程写清楚内容、影响范围和回滚方案。**第二大表变更之前先用ALGORITHMINPLACE, LOCKNONE探路。**如果MySQL不支持会直接报错不会默默降级到COPY。更稳妥的做法是用gh-ost或pt-osc把影响降到最低。**第三回滚方案必须是可秒级执行的反向操作不是从备份恢复。**变更前做完整备份是底线但备份恢复太慢救不了生产故障。回滚脚本提前写好在预发环境验证过这才是真正的回滚能力。**第四每次变更都记录操作日志、执行时长、遇到的坑。**三个月后你会感谢现在的自己。这些记录是团队最宝贵的经验资产比任何文档都有价值。回到那个凌晨的事故。那条锁了四十分钟的ALTER TABLE后来成了我们团队变更管理改革的起点。现在回头看问题的根源不是技术是习惯。大家都觉得改个表结构而已没人当回事。但生产环境的每一次变更都是一次风险。你能做的不是避免风险而是控制风险。理解底层原理准备好回滚方案把流程立起来。这三件事做好了改库就不再是走钢丝。朋友你在生产环境做变更时踩过哪些坑欢迎聊聊。我是数据库小学妹咱们下篇见