企业官网建设流程全解析

周二下午正开着会，监控群里突然弹出一条告警：生产库某个核心表的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_SHARED（S锁，共享读锁）：执行SELECT、INSERT、UPDATE、DELETE等DML语句时，需要对表的元数据加S锁。多个S锁可以同时存在，互不阻塞。
• MDL_EXCLUSIVE（X锁，独占写锁）：执行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.7），ALTER TABLE ADD INDEX本身就有一段锁表的窗口期。虽然引入了ALGORITHM=INPLACE可以避免拷贝整表数据，但在整个DDL执行期间的某个时刻，依然需要获取MDL X锁来完成元数据的切换。

我画个朴素的时间线你就明白了：

1. 你执行ALTER TABLE t ADD INDEX idx_name(col)。
2. MySQL先尝试获取表的MDL X锁。
3. 假设此刻有一个业务事务正在执行UPDATE t SET ...，它持有MDL S锁。
4. 你的ALTER只能进入等待队列，状态显示Waiting for table metadata lock。
5. 直到那个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 和行锁的对比：别搞混了两个等待场景

为了彻底说清楚，我把两种"改索引会遇到的等待"放到一张表里对比：

行锁等待一般发生在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_trx

MDL 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的事务——这种最常见：应用侧开启了事务（autocommit=0），执行了几条SQL，然后啥也不干就挂在那儿。从trx_started能看出它已经存活多久，配合业务排期来判断是直接kill还是通知业务侧先提交。

再补一个容易被忽略的点：SHOW PROCESSLIST里面State为Sleep但Time特别大的连接也值得警惕。很多连接池里的连接，事务都快超时了，看起来却是"安静的Sleep"状态，实际上手里可能攥着MDL S锁不放。

2.4 实操时我的习惯顺序

排查多了之后，我形成了一套固定的肌肉记忆，基本三十秒内能定位到源头：

1. 先SHOW FULL PROCESSLIST，确认状态是Waiting for table metadata lock，拿到这个ALTER进程的ID。
2. 立刻查sys.schema_table_lock_waits，定位blocking_pid。
3. 用information_schema.innodb_trx看阻塞事务的开始时间和SQL状态。
4. 三步确认后，根据业务情况决定是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是RUNNING，trx_started是31分钟前，但trx_query为NULL。也就是说，真正的罪魁祸首是这个事务本身并没有在执行这31分钟，SELECT语句早就执行完了，但事务一直没提交，就那样开着。为什么blocking_query显示的是那条SELECT？因为那是该事务最近一次执行过的语句，而MDL锁是事务级别的，不随语句结束而释放。

这其实是一个典型的应用侧问题：连接池里的连接开启了事务，代码执行完查询忘了commit（或者事务边界控制不当），事务悬挂在那，S锁也就一直挂着。

3.4 处理决策：kill还是等

按当时的情况，凌晨两点，业务被堵了。31分钟的等待已经不短了，继续等下去只会让更多业务请求超时。我看了一眼PID 156是应用连接池发起的连接，KILL掉它会让那个连接上的事务回滚，但因为该事务唯一的SQL是SELECT，回滚成本为零，对业务无副作用。

果断执行：

KILL 156;

再回头看processlist，PID 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的真实代价

网上很多文章把ALGORITHM=INPLACE说成"加索引不影响业务"，这句话害人不浅。INPLACE只是避免了拷贝整表数据，但它没有解决MDL锁排队的问题，也没有解决DDL内部某些阶段依然需要锁的问题。我详细拆一下。

4.1 MySQL 8.0修改索引的三种算法

在MySQL 8.0中，ALTER TABLE可以通过ALGORITHM参数显式指定三种方式：

注意看表格里的"是否需要写锁"这一列。即便是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 容易被忽略的几个等锁场景

除了长事务这个头号元凶，还有几个场景实战中经常踩到：

1. 全文索引和空间索引：MySQL对这类索引的在线构建支持有限，就算你指定INPLACE，某些阶段还是会退化为COPY，需要的锁也更多。8.0文档里明确列了哪些DDL支持INPLACE，表里没列到的，别硬指定。
2. 主键索引变更：ALTER TABLE ... DROP PRIMARY KEY或ADD PRIMARY KEY，即使8.0也可能需要重建整个聚簇索引，期间X锁窗口比普通二级索引长得多。
3. 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：

1. 表数据量不大（我个人的阈值为单表500万行以内），DDL可以在几分钟内完成。
2. 当前不存在未提交的长事务，且低峰期有明确窗口。
3. 表上有充足冗余空间，至少是表数据量的1.5倍（INPLACE依然需要额外空间用于临时日志和排序）。

如果少了任何一个，老老实实走下面的工具路线。别拿"测试环境试过了很快"来赌生产——测试环境没有并发事务打底，跟生产完全两码事。

5.2 pt-online-schema-change的原理与实操

Percona Toolkit的pt-osc是处理在线DDL最成熟的方案。它的核心思路是：

1. 创建一个与原始表结构相同的新表（_table_new）。
2. 在新表上执行你要的ALTER（此时新表空，ALTER秒完成，不存在MDL阻塞问题）。
3. 通过触发器（AFTER INSERT/UPDATE/DELETE）把原表上的增量变更实时同步到新表。
4. 然后按主键分批把原表数据拷贝到新表。
5. 拷贝完成后，通过原子性的RENAME TABLE交换新旧表名（这一步只获取极短时间的MDL X锁）。

实操命令：

pt-online-schema-change \ --alter "ADD INDEX idx_user_id(user_id)" \ --host=127.0.0.1 \ --port=3306 \ --user=dba \ --password=xxx \ --max-load="Threads_running=30" \ --critical-load="Threads_running=50" \ --chunk-size=2000 \ --chunk-time=2 \ --sleep=1 \ --max-lag=5 \ D=order_db,t=order_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 \ --host=127.0.0.1 \ --user=dba \ --password=xxx \ --database=order_db \ --table=order_info \ --alter="ADD INDEX idx_user_id(user_id)" \ --max-load="Threads_running=30" \ --critical-load="Threads_running=50" \ --chunk-size=2000 \ --panic-flag-file=/tmp/ghost.panic \ --execute

但gh-ost对binlog格式有要求，binlog_format必须是ROW，且binlog必须是ROW模式才能解析出增量数据。我遇到过一些老环境还是MIXED模式的，gh-ost会直接拒绝执行。另外gh-ost需要额外的端点和权限来模拟从库连接，网络策略复杂的环境里落地成本会高一些。

5.4 主从架构下的额外检查项

国内大部分生产环境都是主从架构，改索引不只是主库单点的事。我整理了一份检查清单，每次执行前后过一遍：

在这里多说一句：我一直强调要验证备份，不是走形式。之前遇到过一台机器上备份脚本天天报错但没人看，真正需要恢复的时候才发现备份文件是坏的。真到那一步，不管用什么工具改索引都没意义了，数据都找不回来。改索引这种事，最坏的情况下，你要能接受用备份重建一张表。

5.5 改完索引之后的收尾动作

DDL跑完之后，别急着收拾东西下班。我习惯再做三件事：

1. SHOW CREATE TABLE确认索引结构符合预期，索引名、列顺序别搞错。
2. 用EXPLAIN SELECT ... WHERE user_id = xxx验证查询计划真的走了新索引。
3. 观察主从延迟是否回落、慢查询数量是否下降，确认这次变更达到了预期效果。

如果是分库分表环境，一个库改完之后，其他分片可以用脚本批量执行，但注意错峰，别让所有分片同一秒同时开始DDL，把IO打满。

最后再分享一个小技巧：在执行大型ALTER之前，先执行SELECT COUNT(*) FROM order_info这种访问量级极小的查询来确认表没有被锁；或者用LOCK TABLES order_info READ瞬间获取再释放来测试表是否可读。这个小探测只要0.1秒，却能提前发现锁风险，避免发出一个注定要等半小时的ALTER。

写了这么多，核心就是一句话：MySQL修改索引等待，等十次有九次都是MDL锁，而MDL锁的本质是事务生命周期管理问题。先学会定位阻塞源头，再根据表的大小和业务场景选择直连ALTER还是专用工具。我自己在线上跑过几百次索引变更，用pt-osc后几乎没有再因为改索引出过大事故。希望这篇排查手册能让你少走我当年走过的弯路。