接手过线上MySQL的人大概都经历过这种时刻业务方催着上线你敲下一句ALTER TABLE想加个字段结果整个表的 DML 被锁死show processlist里密密麻麻全是Waiting for table metadata lock。MySQL DDL 操作最迷惑人的地方在于它看起来就是一条 SQL但真正执行起来牵扯到 MDL 锁、临时文件、undo log、主从复制延迟任何一个环节没想清楚线上就给你颜色看。这篇文章我不打算给你背官方文档而是把 DDL 从敲个 SQL到安全落地整条链路拆开讲包括三种实现算法的取舍、Online DDL 的真实边界、大表操作的决策清单、以及一次 ALTER 卡死的完整排查过程。适合正在维护线上生产库的 DBA / 后端开发也适合刚入门想搞懂ALTER TABLE为什么会锁表的新手。1. DDL 的真正成本不是扫描了多少行而是锁是怎么排队的很多人第一次意识到 DDL 有成本是发现一条ALTER TABLE t ADD COLUMN c INT跑了十分钟还没结束。MySQL 不是不能执行这条语句而是在执行前后要跟所有并发访问者抢一把叫 MDLMetadata Lock元数据锁的锁。这玩意比行锁、表锁更隐蔽因为它连SELECT都会参与排队。1.1 先理解 MDL 锁DML 和 DDL 抢的是同一把钥匙MySQL 5.5 开始引入 MDL目的是保护表结构在并发访问时不出现读到改了一半的结构这种问题。具体规则可以用一个粗粒度模型理解任何 DMLSELECT/UPDATE/DELETE/INSERT执行前需要拿到表的MDL 读锁Shared Lock多个事务可以同时持有读锁互相不冲突任何 DDLALTER/DROP/RENAME/TRUNCATE执行前需要拿到表的MDL 写锁Exclusive Lock写锁和读锁互斥也和其他写锁互斥。问题出在排队机制上。假设事务 A 一直持有着读锁不释放事务 B 发起了ALTER TABLE它排队等待写锁。此时事务 C 发起一条普通SELECT它也需要读锁——但 MySQL 的 MDL 等待队列是公平的B 排在 C 前面所以 C 也得等着。这就是线上经典事故的成因一个慢查询拖着锁不放一条 DDL 排队排到队首后续所有读写全部堵死。这个现象我在生产环境已经见过不止一次。有一次凌晨跑批任务一个事务开了没提交第二天早上同事执行ALTER TABLE加索引结果从早上九点开始整个表的读请求全部堆积直到那个跑批事务被 kill 掉才恢复。所以排查 DDL 卡死时第一件事永远不是看 DDL 本身而是看谁持有 MDL 读锁。1.2 手把手排查一条 ALTER 卡住的完整链路下面这套排查顺序是我每次处理ALTER 执行不下去时的标准动作按这个顺序走基本能在两分钟内定位问题源头。第一步看当前进程状态。执行show full processlist;重点看State字段。如果是Waiting for table metadata lock说明 DDL 卡在 MDL 等待上如果是copy to tmp table说明已经开始拷贝数据了属于执行阶段如果是Waiting for table flush那多半是FLUSH TABLES或者表缓存刷新被阻塞。第二步找谁持有元数据锁。MySQL 5.7 里可以用 sys 库SELECT * FROM sys.schema_table_lock_waits\G这个视图会直接告诉你谁在等锁waiting_pid、谁挡住了它blocking_pid、以及阻塞线程当前在跑什么 SQL。5.7 没有 sys 库的可以查performance_schema.metadata_locksSELECT OBJECT_SCHEMA, OBJECT_NAME, LOCK_TYPE, LOCK_STATUS, OWNER_THREAD_ID FROM performance_schema.metadata_locks WHERE OBJECT_SCHEMA your_db AND OBJECT_NAME your_table; -- 配合 performance_schema.threads 把 THREAD_ID 转成 PROCESSLIST_ID第三步查正在运行的长事务。如果上一步定位到 blocking_pid接着看information_schema.innodb_trxSELECT * FROM information_schema.innodb_trx WHERE trx_mysql_thread_id 阻塞线程ID\G重点看trx_started如果事务已经跑了几个小时还没提交那基本就是它没跑了。定位到根因以后处理方式分两种事务能快速结束就等它不能等就直接KILL thread_id但 kill 之前一定要跟业务方确认因为一个跑了几小时的事务被 kill 掉它的修改会全部回滚代价不低。提示lock_wait_timeout默认 31536000 秒一年也就是说 MDL 等待默认不会被超时中断。生产环境建议显式调成较小的值比如 50 秒防止 DDL 无限排队把业务拖死。2. 三种 DDL 实现算法INSTANT、INPLACE、COPY 到底在背后做了什么搞清楚锁的排队之后再往深一层问DDL 申请到写锁以后到底以什么方式物理地修改表MySQL 给出了三种算法COPY、INPLACE、INSTANT。8.0 里可以在ALTER TABLE后面用ALGORITHM子句指定也可以不指定让它自动选择。2.1 COPY 算法最古老也最危险的路径ALGORITHMCOPY的实现方式是创建一个结构改好的临时表把原表数据一行行拷贝进去然后删除原表、把临时表重命名。整个过程相当于把整张表的数据复制了一遍。COPY 算法有两个致命特征第一执行期间原表不允许并发写MySQL 会对原表加锁阻塞 DML第二需要额外一份完整表空间的磁盘空间。一张 500GB 的表走 COPY磁盘没留够 500GB 空闲DDL 直接报Table xxx already exists或者干脆磁盘写满。为什么还有人在用因为部分操作只有 COPY 路径能做。比如MODIFY COLUMN修改列类型int - bigint、修改字符集utf8mb4_general_ci - utf8mb4_unicode_ci、把默认值从常量改成表达式这些没有 INPLACE 实现。2.2 INPLACE 算法原地修改但不是全程无锁ALGORITHMINPLACE是 5.6 之后 Online DDL 的核心。它不会创建整张临时表而是在表内部进行修改某些阶段可以允许并发的 DML。注意这里的online两个含义很多人会混淆允许并发 DML 不等于不锁表。INPLACE 在执行前和收尾阶段各有一个短暂的瞬间需要EXCLUSIVEMDL 锁只是这个时间极短毫秒级而中间真正的数据重组阶段可以放行读和写。加索引就是典型的 INPLACE。5.6 之后给表加个二级索引不需要拷贝整表只需扫描聚簇索引、构建索引结构期间 DML 照常运行MySQL 会用日志记录 DDL 期间的增量变更最后统一应用到新索引上。这个机制很聪明跟在线备份做增量日志一个思路。2.3 INSTANT 算法8.0 的秒级加列8.0.12 引入了ALGORITHMINSTANT目前支持的操作有在表末尾加列、加列时指定默认值、删除列、修改列的默认值。它的原理是只修改数据字典中的表定义不动数据文件。所以 8.0 上给一张 1TB 的表末尾加一个带默认值的列理论上也是秒级完成这就是我跟很多人安利升级 8.0 的理由之一。INSTANT 的限制也要清楚只支持往表末尾加列。如果AFTER col指定了中间位置会自动降级为 INPLACE不支持压缩表ROW_FORMATCOMPRESSED不支持有全文索引的表加列后的记录长度不能超过行大小上限。提示8.0 里ALTER TABLE ... ALGORITHMINSTANT, LOCKNONE是最理想的组合但如果操作不满足 INSTANT 条件MySQL 会直接报错Unsupported algorithm而不是自动降级。线上执行前先小表试一下或者干脆不指定 ALGORITHM让优化器自己选最合适的。下面用一张表总结三个算法最实用的差异点方便快速判断对比项COPYINPLACEINSTANT是否重建数据文件全表拷贝部分重建/索引构建不碰数据是否阻塞并发 DML全程阻塞多数可并发视操作而定并发 DML 不受影响磁盘占用额外一份表空间少量临时文件几乎为零执行速度最慢中等秒级典型场景改列类型/改字符集加索引/加列5.7/删列加列8.0/删列8.03. 用 ALGORITHM 和 LOCK 参数给 DDL 上保险MySQL 的ALTER TABLE允许你主动声明两个约束ALGORITHM控制物理实现方式LOCK控制允许的并发级别。这两个参数是保护线上环境最重要的工具虽然多数时候不写也能跑但写上之后就等于告诉 MySQL如果做不到我要求的并发级别你就别执行宁可报错也不能锁表。3.1 LOCK 的四个级别怎么选LOCKNONE表示执行期间允许并发读写能接受这个条件就做不能就报错。加索引、加列8.0 INSTANT这类操作都能满足。LOCKSHARED表示允许并发读但写要阻塞。比如添加外键约束这种需要防止写入破坏约束一致性的操作就用它。LOCKEXCLUSIVE表示读写全部阻塞整个 DDL 期间表都不对外服务。某些 COPY 类操作默认就是这个级别。LOCKDEFAULT让 MySQL 自己判断通常它会选择并发度最高的可用方案。3.2 一个生产环境的执行姿势我的习惯是在 DDL 里显式声明ALTER TABLE your_db.your_table ADD COLUMN new_col INT NOT NULL DEFAULT 0 COMMENT 新字段, ALGORITHMINPLACE, LOCKNONE;要求 DDL 只能以 INPLACE 且允许并发 DML 的方式执行。如果这张表的 DDL 实际降级成了 COPYMySQL 会直接抛错而不是默默锁表这样我能第一时间发现问题而不是等到业务侧告警。8.0 上尽量优先 INSTANTALTER TABLE your_db.your_table ADD COLUMN new_col VARCHAR(32) DEFAULT NULL COMMENT 新字段, ALGORITHMINSTANT, LOCKNONE;这里有个小技巧8.0 里ALGORITHMINSTANT只支持末尾加列如果业务要求字段必须加在某个位置可以分两步先用 INSTANT 加在末尾再用MODIFY ... AFTER调位置这个操作会变成 INPLACE 且复制数据大表不建议。要理解一个观点字段顺序本身不影响 SQL 正确性除非代码里用了SELECT *并且按位置拼结果集。所以线上加列我通常直接加末尾少折腾。3.3 为什么我不建议顺手 optimize table很多同学喜欢在 DDL 时顺手执行OPTIMIZE TABLE因为 5.6 后它也叫 Online DDL。但至少在我的场景里OPTIMIZE TABLE默认走 INPLACE 重建表会在磁盘上产生接近表大小的临时文件期间 IO 压力很大而且结束阶段同样要拿短时间写锁。大表执行前务必先看磁盘空间和 IO 负载。这不是不能做而是要在低峰期做并提前留好双倍空间。4. 大表 DDL 落地的完整决策清单从检查到执行到回滚上面讲了原理和参数但真正实操时决定一次大表 DDL 成败的往往是一些看起来跟 SQL 无关的检查项。我每次给百 G 级别的表执行 DDL都会三步走评估、准备、执行。4.1 评估这一步先回答三个问题第一个问题这张表的 DDL 会走哪种算法官网有详细的 Online DDL 支持矩阵但记不住也没关系本地有测试库的话直接EXPLAIN SELECT 1 FROM your_table; -- 这只是确认连接正常更靠谱的办法是直接小表试一把或者用ALGORITHMINPLACE, LOCKNONE强制约束让数据库替你把关。第二个问题执行时间窗口够不够预估时间不能靠猜社区常用的做法是从备份库找一份结构相同的表按行数比例估算。比如测试库 1000 万行的表加索引耗时 90 秒线上 1 亿行的表粗略估计就是 900 秒的 IO 密集操作再留 2-3 倍余量。第三个问题失败后怎么回滚需要提前说清楚DDL 不像 DML没有事务性回滚8.0 提供了原子 DDL指失败时不会留下半成品表结构但不会针对大表重建回滚。比如 ALTER 加列跑到一半失败MySQL 会自动清理临时表但这是一次失败的自动清理不是回滚。真正可靠的回滚方案是提前用旧结构建一张影子表或者用备份库恢复。4.2 准备阶段检查单逐条过# 1. 磁盘空间至少留出表大小 1.5 倍的空闲 df -h /data # 2. InnoDB 临时表空间ibtmp1会不会暴涨 # 大表 INPLACE 重建时可能在临时表空间写入大量数据 ls -lh /var/lib/mysql/ibtmp1 # 3. 主从延迟基线确认从库有足够能力追平 SHOW SLAVE STATUS\G -- 看 Seconds_Behind_Master磁盘空间这条最容易翻车。一次ALTER TABLE ... ADD INDEXINPLACE 会在表所在目录生成临时文件基本上是表大小的一份拷贝另一个坑是old_alter_table或系统变量被某些团队设置为ON导致所有 DDL 强制走 COPY磁盘直接爆掉。执行 DDL 前先查一下SHOW VARIABLES LIKE old_alter_table;如果这个值是 ON你在 5.7 / 8.0 下的所有Online DDL其实都是 COPY 算法锁表、占空间、慢三件事全占。这个变量是兼容老版本用的生产环境绝大多数场景应该关闭。4.3 执行期监控开三个窗口盯数据DDL 执行期间SSH 到数据库机器上分别开三个终端终端一看 DDL 线程当前状态show full processlist里 State 是altering table就还在正常走终端二实时看磁盘watch -n 5 df -h /data临时文件的大小变化是 DDL 进度的最好信号终端三看 undo 和临时表空间大小du -sh /var/lib/mysql/*.ibd。提示MySQL 8.0 下可以查performance_schema.session_status线程正在 DDL 时可以看innodb_alter_table_temp_size之类的状态量比看 processlist 更直观。4.4 低峰期执行 限速策略DDL 本质是 IO 密集的大表 DDL 期间整个实例的读写吞吐都会明显下降所以窗口期选择比技巧更重要。我的原则是单表超过 100GB必须在业务低谷执行如果业务 24 小时都有流量就要考虑限速执行——但 MySQL 本身没有 DDL 限速参数这时就得靠 pt-osc 这类工具控制每次拷贝的行数。5. 主从环境下执行 DDL最大的坑不在主库单机 MySQL 做 DDL 已经够紧张了主从架构下的 DDL 则还要考虑复制链路的工作方式。很多人以为主库执行完 DDL 就万事大吉但主从复制下 DDL 会在从库重放也就是说你在主库上经受的那次 IO 高峰从库会原样经受一次而且因为从库多数时候是单线程回放 DDL 相关事件延迟会非常明显。5.1 Statement 复制下 DDL 的等效效应MySQL 主从复制默认是 Statement 格式主库的ALTER TABLE会被记录为 SQL 语句在从库上重新执行。于是问题来了从库重放这条 DDL 时一样要做表重建、一样要占磁盘、一样会卡住从库上所有针对这张表的查询。主库执行 1 小时从库延迟就多出至少 1 小时。这一点在 5.7 之前尤其严重。5.6 / 5.7 在部分 DDL 上做了优化主库用 Online DDL 时从库也有一定程度改善但不要指望从库能像主库一样并发 DML 不阻塞大 DDL 期间从库出现延迟是常态。5.2 我的从库 DDL 落地顺序对于必须执行的 DDL如果数据量极大我的操作顺序是先在从库执行一遍让它承担首轮风险确认 SQL 本身没问题、时间可控再在主库执行。这里的逻辑是从库出问题好兜底主库出问题就是事故。如果 DDL 不可控比如磁盘空间不确定先摘掉一个从库的复制在它上面执行验证再放回去追平。还有一种常见做法是采用 gh-ost / pt-osc 这类在线变更工具它们不直接执行 ALTER而是创建一张影子表、通过复制 binlog 增量把数据同步到新表最后以原子换名完成 DDL。好处是执行期间几乎不锁表、可控限速、可以随时暂停和清理缺点是架构和操作复杂度显著上升大表 DDL 我的优先级排序是能用 INSTANT 就 INSTANT不能就评估 INPLACE 窗口期再不行才上 gh-ost。5.3 备份和灰度意识DDL 前一定先有备份这是 DBA 的底线。大表用逻辑备份不现实优先xtrabackup物理备份。备份不是走形式而是给失败以后能站起来留后路。有一次我加列前没有给自己留物理备份结果执行到 90% 时磁盘写满MySQL 自动回滚临时表还好表本身没损坏但那条 DDL 白跑了 6 个小时整个运维侧背上问责。从那时起备份这步再也没省过。6. 高频 DDL 操作的写法与避坑笔记最后整理一份我觉得最常用的 DDL 操作速查顺便把几个针对 5.7 / 8.0 的差异点标出来。建表、加列、改列、加索引、删列删索引这些是日常出现频率最高的 DDL。6.1 建表一次把约束和索引想清楚CREATE TABLE user_order ( id BIGINT NOT NULL AUTO_INCREMENT COMMENT 主键, user_id INT NOT NULL COMMENT 用户ID, order_no VARCHAR(64) NOT NULL COMMENT 订单号, amount DECIMAL(10,2) NOT NULL DEFAULT 0.00 COMMENT 金额, status TINYINT NOT NULL DEFAULT 0 COMMENT 0-待支付 1-已支付 2-已取消, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 创建时间, PRIMARY KEY (id), UNIQUE KEY uk_order_no (order_no), KEY idx_user_id_created (user_id, created_at) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COLLATEutf8mb4_0900_ai_ci COMMENT用户订单表;几个容易忽略的点utf8mb4必须别用 utf8不是真正的 utf8排序规则 8.0 默认就是utf8mb4_0900_ai_ci5.7 用utf8mb4_general_ci也可以别跟线上已有库混用DECIMAL别用FLOAT金额精度是底线。6.2 加列与改列的易错写法加列一定要带 COMMENT这是个好习惯ALTER TABLE user_order ADD COLUMN pay_time DATETIME NULL DEFAULT NULL COMMENT 支付时间, ALGORITHMINPLACE, LOCKNONE;改列类型时最容易踩的坑是类型收窄导致数据截断。VARCHAR(100)改VARCHAR(50)已有数据长度超过 50 时 DDL 会失败INT改TINYINT同理。而且这类 MODIFY 通常走 COPY 算法5.7 下会锁表。改之前先统计一下最大长度SELECT MAX(CHAR_LENGTH(order_no)) FROM user_order;改列名用CHANGEALTER TABLE user_order CHANGE COLUMN pay_time paid_at DATETIME NULL DEFAULT NULL COMMENT 支付时间;注意 CHANGE 必须重复一遍完整的列定义少写了默认值或注释这些属性会被清掉。这是好多人踩过的坑。6.3 索引操作的取舍加索引推荐独立写、显式命名ALTER TABLE user_order ADD KEY idx_status_created (status, created_at), ALGORITHMINPLACE, LOCKNONE;复合索引的列顺序有讲究。idx_status_created能覆盖按状态查创建时间区间的查询但按创建时间查状态用不上它。核心原则是最左前缀把区分度高的列放前面。而且 MySQL 8.0 支持函数索引ALTER TABLE user_order ADD KEY idx_order_year ((YEAR(created_at))), ALGORITHMINPLACE, LOCKNONE;这个对按年份统计的查询很有用5.7 想实现只能额外加一个冗余列。删除列之前先确认没有索引依赖它更别手滑把全表索引删干净ALTER TABLE user_order DROP INDEX idx_status_created, DROP COLUMN status;6.4 TRUNCATE 不等于 DELETE它也是 DDLTRUNCATE TABLE在 MySQL 里归类为 DDL8.0 之前隐式提交后不记录具体行操作方式本质是 drop recreate所以它不能回滚、瞬间释放全部空间、并且需要 MDL 写锁。如果只想清数据保留结构并且数据量不大DELETE反而更可控但会留下碎片。TRUNCATE是删了就不回头执行前问自己有没有备份有没有外键依赖在 8.0 下TRUNCATE依然不是普通的 DML 事务操作千万别把它当成可以 rollback 的 DELETE。6.5 RENAME 比大多数 ALTER 安全但也有细节RENAME TABLE old_name TO new_name;RENAME 是纯元数据操作不碰数据文件所以通常毫秒级完成对业务影响极小只在瞬间持有 MDL 写锁。它还有个隐藏用法同时交换两张表RENAME TABLE user_order TO user_order_bak, user_order_new TO user_order;这条语句是原子的两个名字同时生效经典的表结构替换流程就靠它了。用它做发布时可以把加列 数据同步全放到影子表里完成最后一条 RENAME 瞬间切换这也是 gh-ost 底层的基本思路。实际操作中我最后的体会是MySQL 的 DDL 其实越来越像一个资源调度问题而不是单纯的 SQL 语法问题。8.0 的 INSTANT、原子 DDL 已经把窗口期压缩到极短但如果你还在 5.x 版本维护大表那么每次 DDL 前老老实实把 MDL 状态查一遍、把磁盘空间量一遍、把备份留一份这些动作比任何技巧都值钱。踩过的坑多了你就知道能跑通和能稳着跑之间差的正是这些细活。