周二下午正开着会监控群里突然弹出一条告警生产库某个核心表的ALTER TABLE ADD INDEX执行了四十多分钟还没完成钉钉上已经开始有业务方在问是不是数据库挂了。我赶紧登上服务器看了一眼processlist果然那条DDL的状态栏明晃晃写着Waiting for table metadata lock。这个场景做MySQL运维或后端开发的朋友应该都不陌生。MySQL里修改索引等待绝大部分情况下等的不是索引本身而是一把元数据锁——MDL锁。这篇文章我就把这几年在线上处理这类问题的经验完整梳理一遍MDL锁到底怎么运作、为什么加个索引会被卡住、怎么快速定位是谁在阻塞、以及在生产环境改索引的正确姿势。不管你是刚入门MySQL的开发还是已经带过生产库的DBA这篇都能直接拿来当排查手册用。1. 修改索引被卡住根源大多是MDL锁而不是行锁先纠正一个常见的误区。很多人看到修改索引等待第一反应是是不是表里有大事务在改数据行锁冲突了。其实对于ALTER TABLE ADD INDEX这种操作来说卡住的原因绝大多数不是行锁而是MDL——Metadata Lock元数据锁。1.1 MDL锁的本质保护表结构不被搞乱套MySQL从5.5版本开始引入了MDL锁。它的作用很直观保护表结构定义也就是元数据的一致性。你可以把它理解成图书馆里的馆藏目录——每个人借书还书DML操作都要查这个目录而有人要重新编目DDL操作时就得先确保没有其他人正在用旧目录借书。MDL锁分两种主要类型MDL_SHAREDS锁共享读锁执行SELECT、INSERT、UPDATE、DELETE等DML语句时需要对表的元数据加S锁。多个S锁可以同时存在互不阻塞。MDL_EXCLUSIVEX锁独占写锁执行ALTER TABLE、DROP TABLE等DDL语句时需要拿X锁。X锁和任何其他锁都互斥。关键点就在这里所有DML语句在开始执行时都会先请求MDL S锁而且是在事务结束COMMIT或ROLLBACK时才释放。这也就意味着只要有一个事务一直开着没提交哪怕它只是一条普通的SELECT已经跑完了只要事务没关它持有的MDL S锁就一直占着。这时候ALTER TABLE进来了它需要X锁发现S锁还在就只能排队等。1.2 为什么加索引这种轻量操作也会排队MySQL 8.0之前的版本包括现在还在大量服役的5.7ALTER TABLE ADD INDEX本身就有一段锁表的窗口期。虽然引入了ALGORITHMINPLACE可以避免拷贝整表数据但在整个DDL执行期间的某个时刻依然需要获取MDL X锁来完成元数据的切换。我画个朴素的时间线你就明白了你执行ALTER TABLE t ADD INDEX idx_name(col)。MySQL先尝试获取表的MDL X锁。假设此刻有一个业务事务正在执行UPDATE t SET ...它持有MDL S锁。你的ALTER只能进入等待队列状态显示Waiting for table metadata lock。直到那个UPDATE事务提交或回滚MDL S锁释放ALTER才拿到X锁继续执行。这里有个特别坑的点排队中的ALTER还会反过来阻塞后续所有的DML请求。因为MySQL的MDL锁请求队列是先来先服务的一旦有一个X锁请求在排队后面来的S锁请求全部都要排到X锁后面去。这就是大家常说的一条DDL卡死整个表的读写在——不是DDL本身慢而是它一旦等不到锁整张表的读写全部被堵住了。所以这种等待一旦发生必须尽快处理哪怕最终决定要kill DDL也得先把阻塞源头解决掉。补充一个8.0版本的新变化MySQL 8.0引入了MDL锁的原子性获取但对DDL等待的排队逻辑并没有本质改变。8.0里information_schema.metadata_locks表能看到更详细的锁信息这一点后面排查章节会用到。1.3 和行锁的对比别搞混了两个等待场景为了彻底说清楚我把两种改索引会遇到的等待放到一张表里对比等待类型锁对象触发条件典型报错/状态最常见原因MDL锁等待表结构元数据DDL需要X锁但DML事务持有S锁未释放Waiting for table metadata lock长事务未提交、长查询、unauthenticated连接行锁等待具体数据行DDL过程中需要修改数据行但行被其他事务锁住Lock wait timeout exceeded; try restarting transaction行被UPDATE/DELETE锁住且迟迟不提交行锁等待一般发生在ALTER TABLE内部阶段——比如把旧数据迁移到新表结构时需要逐行处理而这些行恰好被并发事务锁住。但这种情况通常几秒内就会超时。真正让你在processlist里干瞪眼半小时的99%是MDL锁等待。2. 动手前先学会定位三张视图快速锁定阻塞源头一旦确认状态是Waiting for table metadata lock接下来要做的事情只有一件找到谁拿着S锁不撒手。我习惯按下面三步走每一步都有一个对应的系统视图。2.1 第一板斧performance_schema.metadata_locks这是最直接的一张表MySQL 5.7.6以上版本默认开启。它记录了当前所有MDL锁的持有和等待情况。查询SQL我一般这样写SELECT OBJECT_TYPE, OBJECT_SCHEMA, OBJECT_NAME, LOCK_TYPE, LOCK_STATUS, OWNER_THREAD_ID, OWNER_EVENT_ID FROM performance_schema.metadata_locks WHERE OBJECT_SCHEMA your_db AND OBJECT_NAME your_table ORDER BY LOCK_STATUS DESC;输出里你会看到两类记录LOCK_STATUS GRANTED已经拿到锁的会话大概率是阻塞源头。LOCK_STATUS PENDING正在等锁的会话很可能就是你的ALTER。OWNER_THREAD_ID这个字段是关键线索。拿到它之后通过performance_schema.threads表关联出PROCESSLIST_ID再对应到information_schema.PROCESSLIST就能看到是哪个连接了SELECT t.PROCESSLIST_ID, t.PROCESSLIST_USER, t.PROCESSLIST_HOST, t.PROCESSLIST_DB, t.PROCESSLIST_COMMAND, t.PROCESSLIST_TIME, t.PROCESSLIST_INFO FROM performance_schema.threads t WHERE t.THREAD_ID 刚才查到的OWNER_THREAD_ID;2.2 第二板斧sys.schema_table_lock_waits如果你觉得上面的关联查询太麻烦MySQL附带的sys库直接封装好了一张视图sys.schema_table_lock_waits。一条SQL就能把谁在等、等谁、等了多久全列出来SELECT waiting_pid, waiting_query, blocking_pid, blocking_query, wait_age FROM sys.schema_table_lock_waits WHERE object_schema your_db AND object_name your_table\G这视图等于帮我把metadata_locks和threads做了join直接输出进程ID和对应的SQL文本省了不少事。blocking_pid那一列就是你要找的元凶。2.3 第三板斧information_schema.innodb_trxMDL S锁的持有时间跟事务生命周期绑定所以还得看有没有查完了不提交的空闲事务。information_schema.innodb_trx是必查项SELECT trx_id, trx_state, trx_started, trx_mysql_thread_id, trx_query, trx_rows_locked, trx_rows_modified FROM information_schema.innodb_trx ORDER BY trx_started ASC;这里要特别注意trx_state是RUNNING但trx_query为NULL的事务——这种最常见应用侧开启了事务autocommit0执行了几条SQL然后啥也不干就挂在那儿。从trx_started能看出它已经存活多久配合业务排期来判断是直接kill还是通知业务侧先提交。再补一个容易被忽略的点SHOW PROCESSLIST里面State为Sleep但Time特别大的连接也值得警惕。很多连接池里的连接事务都快超时了看起来却是安静的Sleep状态实际上手里可能攥着MDL S锁不放。2.4 实操时我的习惯顺序排查多了之后我形成了一套固定的肌肉记忆基本三十秒内能定位到源头先SHOW FULL PROCESSLIST确认状态是Waiting for table metadata lock拿到这个ALTER进程的ID。立刻查sys.schema_table_lock_waits定位blocking_pid。用information_schema.innodb_trx看阻塞事务的开始时间和SQL状态。三步确认后根据业务情况决定是KILL阻塞会话还是等待它自然结束。注意sys.schema_table_lock_waits在某些5.7小版本上存在权限要求如果查询为空但明显在等待可以退回到第2.1节的metadata_locks手工关联别死磕一张视图。3. 一次真实等待的完整排查链路从告警到解决光讲理论容易飘我拿一次真实的生产事故复盘来串一遍。这是MySQL 8.0.28的双主架构业务表order_info有三千多万行运行在低峰期我们准备给user_id字段加一个普通二级索引。3.1 告警与初步确认凌晨两点半Zabbix发出告警order_info表的主从延迟超过300秒。从库延迟通常意味着主库有大DDL或大事务我登录主库执行SHOW FULL PROCESSLIST看到这样一条------------------------------------------------------------------------------------------------------ | Id | User | Host | db | Command | Time | State | Info | ------------------------------------------------------------------------------------------------------ | 201 | root | app_srv_1 | order_db | Query | 1870 | Waiting for table metadata lock | ALTER TABLE order_info ADD INDEX idx_user_id(user_id) | ------------------------------------------------------------------------------------------------------State和Info一眼就锁定问题ALTER在等MDL锁已经等了31分钟。这期间主库上所有针对order_info的DML估计都堵住了——这也解释了从库延迟主库写不进去从库自然没有新binlog可应用。3.2 用sys视图揪出阻塞者接着执行SELECT waiting_pid, blocking_pid, blocking_query, wait_age FROM sys.schema_table_lock_waits WHERE object_name order_info\G输出结果waiting_pid: 201 blocking_pid: 156 blocking_query: SELECT id, user_id, amount FROM order_info WHERE status 1 AND create_time 2023-11-01 wait_age: 00:31:12阻塞者是PID 156一个看起来普通的SELECT查询。但这个查询从wait_age看已经阻塞了31分钟而它本身居然还在执行——这不是一个好信号。3.3 追查事务状态确认元凶性质我用innodb_trx查PID 156对应的事务信息SELECT trx_id, trx_state, trx_started, trx_mysql_thread_id, trx_query FROM information_schema.innodb_trx WHERE trx_mysql_thread_id 156\G结果发现trx_state是RUNNINGtrx_started是31分钟前但trx_query为NULL。也就是说真正的罪魁祸首是这个事务本身并没有在执行这31分钟SELECT语句早就执行完了但事务一直没提交就那样开着。为什么blocking_query显示的是那条SELECT因为那是该事务最近一次执行过的语句而MDL锁是事务级别的不随语句结束而释放。这其实是一个典型的应用侧问题连接池里的连接开启了事务代码执行完查询忘了commit或者事务边界控制不当事务悬挂在那S锁也就一直挂着。3.4 处理决策kill还是等按当时的情况凌晨两点业务被堵了。31分钟的等待已经不短了继续等下去只会让更多业务请求超时。我看了一眼PID 156是应用连接池发起的连接KILL掉它会让那个连接上的事务回滚但因为该事务唯一的SQL是SELECT回滚成本为零对业务无副作用。果断执行KILL 156;再回头看processlistPID 201的ALTER立刻从Waiting for table metadata lock变成了Copy to tmp table阶段因为8.0 INPLACE算法最终执行完整个DDL只花了1分50秒——真正的DDL本体并不慢慢的是那31分钟的锁等待。3.5 事后反思等锁期间为什么整表读写全挂这次事故还有一个值得复盘的点那条ALTER等待的31分钟里为什么连简单的SELECT都变慢了原因就是前面提到的MDL锁排队机制——ALTER的X锁请求排在队首后续所有S锁请求全部堵在后面。整个order_info表的读写完全停摆大量连接堆积连接池被打满。所以我的教训是一旦确认ALTER在等MDL锁超过一两分钟不要心存侥幸等它自己结束立即按上面三步排查要么kill阻塞会话要么kill那条DDL二选一不能让两边干耗着。顺带说一句如果阻塞事务是一个正在执行大UPDATE或批量DELETE的长事务kill之前要评估回滚代价。我见过一次kill掉一个跑了40分钟的大事务后回滚又花了30分钟的情况那段时间锁照样不释放。遇到这种反而可以考虑让DDL退后先让大事务跑完再执行。4. online DDL不是银弹INPLACE与COPY的真实代价网上很多文章把ALGORITHMINPLACE说成加索引不影响业务这句话害人不浅。INPLACE只是避免了拷贝整表数据但它没有解决MDL锁排队的问题也没有解决DDL内部某些阶段依然需要锁的问题。我详细拆一下。4.1 MySQL 8.0修改索引的三种算法在MySQL 8.0中ALTER TABLE可以通过ALGORITHM参数显式指定三种方式算法处理方式是否需要拷贝数据是否需要写锁X锁典型适用场景INSTANT只修改元数据字典否是的但极短新增字段8.0新增功能INPLACE原地构建索引结构否但可能需要重建聚簇索引执行期间短暂获取分阶段普通二级索引增删COPY创建新表并拷贝数据是全程需要老版本遗留、某些特殊DDL注意看表格里的是否需要写锁这一列。即便是INPLACE也不是全程不需要X锁。以ADD INDEX为例MySQL在DDL开始前和结束时都需要短暂获取MDL X锁来完成元数据切换只是执行过程中的数据准备阶段允许DML并发执行。4.2 为什么短暂获取X锁也会卡死理论上INPLACE的MDL X锁只持有几毫秒但问题在于如果你执行ADD INDEX之前表上已经有别人持有的S锁你这短暂的X锁请求一样要排队。排队期间后续DML的S锁请求全部堵死。说得直白点在线DDL解决的是DDL执行过程中能不能让人读没解决DDL被锁等待时会不会堵住后面所有人。后者纯粹是MDL排队机制的问题跟算法选哪个无关。4.3 容易被忽略的几个等锁场景除了长事务这个头号元凶还有几个场景实战中经常踩到全文索引和空间索引MySQL对这类索引的在线构建支持有限就算你指定INPLACE某些阶段还是会退化为COPY需要的锁也更多。8.0文档里明确列了哪些DDL支持INPLACE表里没列到的别硬指定。主键索引变更ALTER TABLE ... DROP PRIMARY KEY或ADD PRIMARY KEY即使8.0也可能需要重建整个聚簇索引期间X锁窗口比普通二级索引长得多。5.7和8.0的行为差异5.7的ADD INDEX虽然也支持INPLACE但5.7的MDL锁等待超时控制不如8.0精细而且ALGORITHM默认行为在不同表引擎下会悄悄降级。你执行ALTER TABLE ... ADD INDEX它到底用了INPLACE还是COPY得通过SHOW WARNINGS或者开启performance_schema的DDL事件才能确认。4.4 两个直接相关的参数遇到修改索引等待有两个参数值得提前设置好-- 全局DDL锁等待超时默认31536000秒一年线上建议改小 SET GLOBAL lock_wait_timeout 3600; -- 在线DDL阶段允许排队等待的锁时长5.7.30 / 8.0支持 SET GLOBAL innodb_lock_wait_timeout 50;lock_wait_timeout控制的是MDL锁等待的最大时长默认一年根本就是无限等。我之前会把生产库这个值单独调小到3600秒配合监控告警如果ALTER等锁超过阈值会自动被数据库杀掉而不是无限期堵下去。这个参数务必要在业务代码里也检查一下因为JDBC连接可能是默认值覆盖了服务端参数。另外一个参数innodb_online_alter_buffer_max_size默认256MB控制在线DDL期间用于记录DML增量修改的内存缓冲。如果这个缓冲不够大INPLACE执行过程中会把大量DML变更记录临时写在磁盘上拖慢整个DDL。在内存充裕的机器上我习惯把它调到1GB配合大表的索引添加操作SET GLOBAL innodb_online_alter_buffer_max_size 1073741824;4.5 我的选型原则简单总结下我的经验表行数小于500万服务器负载低低峰期窗口充足直接ALTER TABLE ... ADD INDEX完全没问题。表行数几千万且是核心业务表哪怕有维护窗口我也不会直接上ALTER——而是用percona工具链或gh-ost这类外部工具。无论表多大如果当前已经有长事务风险先清事务再改这比任何工具选型都重要。5. 生产环境修改索引的正确姿势从直连ALTER到专用工具最后这部分我把它当成一份可以直接抄作业的生产改索引操作手册来讲。很多朋友问我到底什么时候可以直接ALTER什么时候必须上工具我的回答很直白如果你不确定一律用工具。5.1 直连ALTER的三个前提条件满足以下所有条件时我才建议直接执行ALTER TABLE ADD INDEX表数据量不大我个人的阈值为单表500万行以内DDL可以在几分钟内完成。当前不存在未提交的长事务且低峰期有明确窗口。表上有充足冗余空间至少是表数据量的1.5倍INPLACE依然需要额外空间用于临时日志和排序。如果少了任何一个老老实实走下面的工具路线。别拿测试环境试过了很快来赌生产——测试环境没有并发事务打底跟生产完全两码事。5.2 pt-online-schema-change的原理与实操Percona Toolkit的pt-osc是处理在线DDL最成熟的方案。它的核心思路是创建一个与原始表结构相同的新表_table_new。在新表上执行你要的ALTER此时新表空ALTER秒完成不存在MDL阻塞问题。通过触发器AFTER INSERT/UPDATE/DELETE把原表上的增量变更实时同步到新表。然后按主键分批把原表数据拷贝到新表。拷贝完成后通过原子性的RENAME TABLE交换新旧表名这一步只获取极短时间的MDL X锁。实操命令pt-online-schema-change \ --alter ADD INDEX idx_user_id(user_id) \ --host127.0.0.1 \ --port3306 \ --userdba \ --passwordxxx \ --max-loadThreads_running30 \ --critical-loadThreads_running50 \ --chunk-size2000 \ --chunk-time2 \ --sleep1 \ --max-lag5 \ Dorder_db,torder_info几个关键参数的解释--max-load和--critical-load当服务器线程数超过阈值时工具会放慢或直接暂停拷贝这是保护线上业务的核心。--chunk-size和--chunk-time控制每次拷贝多少行、耗时多少秒控制单次DML对主从的压力。--max-lag控制从库延迟超过5秒工具会暂停等待。用pt-osc最大的好处是就算原表上有DML在跑也不影响数据同步触发器会忠实记录增量。MDL锁只出现在最后RENAME那一瞬间窗口小到毫秒级。5.3 gh-ost的优势与局限如果对触发器方案有顾虑比如表上触发器已经很多或者不想在线上表加额外的触发器可以考虑GitHub开源的gh-ost。它的设计更激进不依赖触发器而是通过模拟从库读取binlog来实现增量同步对原表几乎没有侵入。gh-ost的典型命令gh-ost \ --host127.0.0.1 \ --userdba \ --passwordxxx \ --databaseorder_db \ --tableorder_info \ --alterADD INDEX idx_user_id(user_id) \ --max-loadThreads_running30 \ --critical-loadThreads_running50 \ --chunk-size2000 \ --panic-flag-file/tmp/ghost.panic \ --execute但gh-ost对binlog格式有要求binlog_format必须是ROW且binlog必须是ROW模式才能解析出增量数据。我遇到过一些老环境还是MIXED模式的gh-ost会直接拒绝执行。另外gh-ost需要额外的端点和权限来模拟从库连接网络策略复杂的环境里落地成本会高一些。5.4 主从架构下的额外检查项国内大部分生产环境都是主从架构改索引不只是主库单点的事。我整理了一份检查清单每次执行前后过一遍检查项说明主库磁盘空间拷贝临时表/临时文件需要额外空间至少预留表体积的50%从库延迟工具自身有max-lag控制但主库DDL开始前就应有延迟阈值告警binlog格式gh-ost需要ROW格式pt-osc无此限制连接数水位DDL期间避免触发新的批量任务防止连接池被打满备份验证执行前务必有最近一次的有效备份且验证过恢复可用性回滚预案明确记录如果异常kill工具进程后原表结构是否受影响pt-osc/gh-ost中途退出不会影响原表在这里多说一句我一直强调要验证备份不是走形式。之前遇到过一台机器上备份脚本天天报错但没人看真正需要恢复的时候才发现备份文件是坏的。真到那一步不管用什么工具改索引都没意义了数据都找不回来。改索引这种事最坏的情况下你要能接受用备份重建一张表。5.5 改完索引之后的收尾动作DDL跑完之后别急着收拾东西下班。我习惯再做三件事SHOW CREATE TABLE确认索引结构符合预期索引名、列顺序别搞错。用EXPLAIN SELECT ... WHERE user_id xxx验证查询计划真的走了新索引。观察主从延迟是否回落、慢查询数量是否下降确认这次变更达到了预期效果。如果是分库分表环境一个库改完之后其他分片可以用脚本批量执行但注意错峰别让所有分片同一秒同时开始DDL把IO打满。最后再分享一个小技巧在执行大型ALTER之前先执行SELECT COUNT(*) FROM order_info这种访问量级极小的查询来确认表没有被锁或者用LOCK TABLES order_info READ瞬间获取再释放来测试表是否可读。这个小探测只要0.1秒却能提前发现锁风险避免发出一个注定要等半小时的ALTER。写了这么多核心就是一句话MySQL修改索引等待等十次有九次都是MDL锁而MDL锁的本质是事务生命周期管理问题。先学会定位阻塞源头再根据表的大小和业务场景选择直连ALTER还是专用工具。我自己在线上跑过几百次索引变更用pt-osc后几乎没有再因为改索引出过大事故。希望这篇排查手册能让你少走我当年走过的弯路。