MySQL加索引锁表真相：在线DDL、MDL与长事务的博弈

被半夜的报警电话叫醒才知道线上的核心订单表出了乱子。一上去看所有 INSERT 和 UPDATE 全部卡住业务连接数蹭蹭往上涨应用日志里全是超时。翻了一下变更记录发现半小时前刚执行过一条ALTER TABLE ... ADD INDEX。当时脑子里唯一的念头就是完了加索引把表锁死了。这个场景我相信很多运维都遇到过而且第一反应几乎都一样——因为老教程从来都是这么教的MySQL 加索引会锁表要停业务、挑低峰期。但那次查到最后我发现自己其实冤枉了 MySQL。真正把写入堵住的东西跟“加索引要重建表”这个老结论没有关系而是另一个更隐蔽的机制。后来我在测试环境里反复验证、翻文档总算把这个事彻底想通了。这篇就把我验证过的东西完整写出来MySQL 加索引到底什么时候不锁表什么时候会假锁表以及生产环境里到底该怎么安全操作。想彻底弄明白的话建议耐心看完。1. 我经历的加索引锁死写库事件先看现场再看原理1.1 事故现场的表象所有INSERT全部卡住那天凌晨的故障表现非常典型监控面板上写请求的耗时段位从几十毫秒瞬间飙到几十秒数据库的 Threads_running 一路冲到一两百连接数打满应用侧甚至开始拒绝新请求。单独看每一条 SQL 都挺正常但就是执行不完。当时的直觉是“索引构建期间整个表无法写入”也就是老运维口里常说的锁表。为了确认我立刻执行了SHOW FULL PROCESSLIST结果看到的情况比想象中复杂一些。1.2 打开会话列表后看到的两个关键State现场有一堆会话处在同一个状态Waiting for table metadata lock。这个状态我之前见过但从来没想到它跟加索引有这么大关系。再看最前面那条ALTER TABLE它本身已经在执行中了但它的执行状态并不是阻塞而是正常的alter table构建过程。换句话说阻塞的并不是加索根本身而是后面排队的所有业务 SQL 被一堵看不见的墙挡住了。这堵墙就是 MySQL 的元数据锁MDLMetadata Lock。后来查information_schema.innodb_trx发现有一笔事务早就开启了一直没提交。这条ALTER需要拿一个比较高级别的元数据锁但它前面有一笔长事务占着不放于是ALTER只能等在队列里。更要命的是MySQL 的元数据锁有排队机制只要 DDL 在等它后面的所有 SQL 都得排着一锅端。1.3 结论前置锁表与不锁表的分水岭是版本和算法那次事故的根因处理完了长事务一提交ALTER顺利拿到锁后续的写入也马上恢复了。整个过程里加的索引本身并没有长时间阻塞业务。所以真相是只要是支持在线 DDL 的版本并且你用的是合适的算法给普通 InnoDB 表添加二级索引确实可以在写入不中断的情况下完成但如果你忽略长事务、不检查 MDL 排队、不指定算法实际操作中仍然会看到“写入全卡”的恐怖现场。这就是“顿悟”的核心不是 MySQL 不锁表而是它在很多场景下可以做到不锁表但我们得把前置条件全部控制好。2. 在线DDL为什么会在线INPLACE算法与ROW日志的配合2.1 从老大难COPY算法说起在 MySQL 5.5 及更早的版本里ALTER TABLE只有一种做法官方叫 COPY 算法。流程非常粗暴按照新表结构创建一张全新的空表。对原表加排他锁此时任何读写都进不来。把原表的所有数据一行一行拷贝到新表边拷贝边重建索引。拷贝完成后把原表删除把新表改名成原表名。释放锁。这个流程听一下就知道了表多大就得持锁多久。一张几百 GB 的表重建索引可能就是几个小时这几小时里业务全部停摆。所以在那时候生产环境加索引是需要申请变更窗口的半夜三更干活不是没道理。2.2 INPLACE构建二级索引的两个阶段MySQL 5.6 引入了 INPLACE 算法理念完全变了不再重建整张表而是直接在原表上“原地”构建新索引。拿添加普通二级索引举例大致分两个阶段。第一阶段是扫描和构建。InnoDB 会扫描全表的聚集索引把新索引的键值提取出来进行排序后写入索引结构。这个阶段比较耗时但它对原表只持有较低的锁级别业务写入照常可以进行。第二阶段是日志回放。因为第一阶段执行期间业务还在不断写入这些写入可能涉及新索引的键值所以 InnoDB 会用一种叫在线变更日志online log的东西把 DDL 期间发生的所有变更记录下来。第二阶段就是把这份日志重新合并到新索引里让索引最终达到一致状态。这个思路很妙本质上跟“热备份期间使用增量日志”是一个道理。整个过程里原表数据始终可读可写业务无感知。MySQL 之所以能这么干不是因为它魔法般地避免了锁而是它把“构建索引”这项工作的粒度拆碎了让最重的部分不占排他锁。不同的 INPLACE 操作实际持锁级别不同。二级索引构建这类操作通常可以做到LOCKNONE也就是读写都不阻塞但有些操作虽然也 INPLACE却只做得到LOCKSHARED即写阻塞、读不阻塞还有一些操作连 SHARED 都做不到必须LOCKEXCLUSIVE读写全堵。2.3 DDL开始和结束时那两次短暂MDL锁是怎么一回事既然 INPLACE 这么先进为什么生产环境还会出现“写入全卡”刚才说了因为 MDL。INPLACE 并不意味着完全不需要元数据锁而是在 DDL 开始和提交这两个时间点依然需要拿一把比较重的 MDL 锁。更关键的是元数据锁的排队规则。假设事务 A 持有某张表的 MDL 读锁一直不释放事务 B 发起 DDL 需要 MDL 写锁它就会排在队尾等待。一旦 B 开始等事务 C、D、E 无论只是普通查询还是写入它们都要拿 MDL 读锁但因为前面有一个写锁需求在等待全部得排在 B 后面。这就像单车道会车DDL 这辆大车横在路口前后所有的车都动不了。所以实际生产里你看到的“加索引锁表”往往根本不是索引构建过程锁的而是某个旧事务把 DDL 卡住之后DDL 又把后面的所有 SQL 卡住。这是两码事但表象极像。3. 什么情况下加索引真的会锁表需要避开的例外清单3.1 FULLTEXT/SPATIAL索引只能COPY的硬限制并不是所有“加索引”都支持 INPLACE。至少在 MySQL 5.65.7 的主流版本里给 InnoDB 表添加全文索引FULLTEXT时只能用 COPY 算法而且锁级别大概率为LOCKSHARED意思是允许读但写入必须停止。如果你用的是比较老的做法直接执行ALTER TABLE t ADD FULLTEXT INDEX ft_idx(content)MySQL 会默默走 COPY 流程整个过程持锁。表越大业务受损时间越长。这种硬限制不是参数能解决的只能提前规避。类似地部分空间索引SPATIAL操作在不同版本上支持度不一样早期版本同样存在 COPY 回退的可能。我自己的习惯是执行任何加索引操作之前先显式声明ALGORITHMINPLACE和LOCKNONE这样做的好处是如果当前操作不具备在线能力MySQL 会直接报错而不是静默降级成锁表的 COPY 流程。3.2 长事务导致的MDL排队比锁本身更隐蔽的杀手排第一的坑不是算法是长事务。我那次事故就是这样。一个连接开启了事务做了一堆操作但一直没提交也没回滚。它就像占着茅坑不拉屎把 MDL 读锁拽在手里。此时任何需要升级 MDL 锁的 DDL 都要等它。DDL 一进入等待状态排队效应开始发酵后续所有普通 SQL 也被堵死。最讽刺的是你甚至会用SHOW PROCESSLIST看到一堆Waiting for table metadata lock却找不到“罪魁祸首”因为那个长事务可能早就进入了 Sleep 状态。处置手段也直接查information_schema.innodb_trx找到trx_started很早、trx_stateRUNNING的事务评估后 kill 掉。如果长事务来自应用连接kill 之前最好尽快通知业务侧否则连接池里的连接会继续复用出错的状态。3.3 磁盘空间与BINLOG放大不锁表不代表没有代价在线加索引不阻塞写入不代表“零成本”。INPLACE 构建索引需要临时空间二级索引越大中间排序文件越占磁盘。如果磁盘没预留足够余量DDL 执行到一半可能直接失败回滚。回滚本身也是一次重活相当折磨人。还有两个隐性代价容易被忽略一个是 binlog 放大一个是主从复制延迟。DDL 在主库执行完成后binlog 里记录的是整条变更从库拿到这条 DDL 后还得在自己的数据上重新执行一次索引构建。从库资源一般比主库弱所以从库延迟经常在索引构建期间剧烈抖动。我的经验是大表加索引之前先看三样东西。磁盘剩余空间至少要是表大小的 1.5 倍以上主从延迟要有容忍度如果 DDL 期间业务写入量很大那在线变更日志的消耗也会很夸张需要预留足够空间。4. 安全添加索引的完整操作路径从预检到执行4.1 理清ALGORITHM和LOCK子句的语义从 5.6 开始MySQL 已经支持在 DDL 语句里显式声明算法和锁级别常用的组合如下。子句含义典型场景ALGORITHMINPLACE原地变更不重建表添加普通二级索引ALGORITHMCOPY复制整表锁时间长列类型变更、全文索引等ALGORITHMINSTANT只修改元数据瞬间完成8.0.12 部分加列操作ALGORITHMDEFAULT让 MySQL 自动选择不推荐在大表上依赖默认值LOCKNONE允许并发读写理想的在线 DDLLOCKSHARED允许读禁止写全文索引LOCKEXCLUSIVE读写都禁止少数 COPY 流程真正安全的大表索引变更语句长这样ALTER TABLE orders ADD INDEX idx_create_time (create_time), ALGORITHMINPLACE, LOCKNONE;如果 MySQL 认为这个操作无法满足 INPLACE NONE会直接报错而不会偷偷降级执行。这个特性非常有用相当于给操作上了一道保险。4.2 执行前的三张检查表第一张表是查长事务。SELECT trx_id, trx_state, trx_started, TIMESTAMPDIFF(SECOND, trx_started, NOW()) AS trx_age FROM information_schema.innodb_trx ORDER BY trx_started ASC;trx_age超过几十秒甚至几分钟的事务在执行 DDL 前必须处理。不要以为只有写事务会阻挠 MDL读事务只要持有了 MDL 读锁一样会卡住需要写锁的 DDL。第二张表是查当前会话和锁等待。SHOW FULL PROCESSLIST; SELECT * FROM performance_schema.metadata_locks WHERE OBJECT_SCHEMA your_db AND OBJECT_NAME orders\G重点看有没有Waiting for table metadata lock的会话以及 metadata_locks 里锁类型和状态的具体分布。第三张表是查表容量和磁盘。SELECT table_name, ROUND(data_length / 1024 / 1024) AS data_mb, ROUND(index_length / 1024 / 1024) AS index_mb FROM information_schema.tables WHERE table_schema your_db AND table_name orders; -- 查看磁盘剩余空间 SHOW VARIABLES LIKE innodb_data_home_dir; df -h临时文件一般会放在 InnoDB 临时表空间所在目录务必提前确认磁盘余量足够。大表构建一个二级索引临时空间往往不是索引本身那么大就完了排序合并过程会再额外占用一份。4.3 执行期间的实时监控与提交阶段判断执行 DDL 的会话单独开一个不要混在业务连接池里。执行期间持续观察SHOW FULL PROCESSLIST看 DDL 的 state 是否还在正常推进。performance_schema.events_statements_current定位当前会话执行的具体语句和耗时。information_schema.innodb_trx确认没有新增长事务切入。监控主从延迟SHOW SLAVE STATUS\G里的Seconds_Behind_Master。小技巧DDL 结束后不要立刻确认成功就完事要把元数据锁监控多开几分钟。因为提交阶段还需要短暂拿一次 MDL 写锁如果刚好又有新长事务插入它一样会卡在最后一步。等metadata_locks里彻底看不到相关锁等待了整个变更才算真正收工。5. 版本演进带来的策略差异5.6/5.7/8.0该怎么取舍5.1 不同版本的默认行为和锁模型5.6 是分水岭它第一次引入了 INPLACE 和 LOCK 语义但文档和工具链都不太成熟很多 DBA 依然沿用旧习惯。5.7 默认对大部分ADD INDEX会选择更优算法但依然有 COPY 回退的场景尤其是涉及全文索引、临时表或者某些特殊类型时。8.0 又往前走了一大步。第一个大变化是原子 DDLDDL 要么成功要么失败不会再出现执行到一半留下脏状态的情况。第二个大变化是新增了ALGORITHMINSTANT部分加列操作可以只改数据字典几乎瞬间完成。第三个变化是默认行为更激进地往 INPLACE 上靠。但这不意味着 8.0 就能彻底躺平。INSTANT 加列有限制全文索引依然不是 NONE 锁长事务的 MDL 问题也依然存在。升级版本能降低风险不能消除风险。5.2 8.0的原子DDL和INSTANT算法如何改变运维习惯8.0 的用户如果在 8.0.12 及以上版本一些加列场景可以直接用ALTER TABLE orders ADD COLUMN remark VARCHAR(64) NULL, ALGORITHMINSTANT;这种操作瞬间完成不产生大数据拷贝变更窗口基本可以忽略。它跟加索引不太一样但如果你的需求是“加一列 建索引”可以先 INSTANT 加列再 INPLACE 加索引两条变更的负担都小很多。8.0 的原子 DDL 对运维有一个直接好处如果 DDL 执行失败系统不会留下一半新一半旧的状态数据字典和存储引擎能回滚一致。这比 5.7 时代“失败后必须人工检查表结构是否残留”要舒服得多。5.3 留给自己的几点红线与心得我整理过一张自己的变更红线清单每次动手前都会过一遍明确写ALGORITHMINPLACE, LOCKNONE绝不依赖默认值。先清理所有长事务再发起 DDL。磁盘空间预留充足不然中途失败更难看。变更窗口仍然要申请不锁表不意味着业务可以无感主从延迟、CPU 抖动都可能是窗口期成本。操作会话和业务连接池隔离避免误 kill。变更后继续观察 MDL 队列几分钟别急着下结论。把这几条做到你也能像我一样在凌晨三点把“加索引锁表”的事故处理成“加索引安全完成”的案例。第一次体会到那个顿悟时刻你会发现 MySQL 的在线 DDL 其实没那么玄它只是一套把锁粒度精细化的机制值得每个人把它用满。最后再分享一个小习惯现在遇到大表加索引我反而会先主动确认一下这个 DDL 会不会因为LOCKNONE不支持而报错。报错不可怕可怕的是 MySQL 不报错、直接默默降级成锁表执行。显式声明这两个子句是成本最低的保护。