【踩坑总结】表只有 1000 条数据，执行 ALTER TABLE 却卡死？排查与解决全过程

前言

在 MySQL 运维和日常开发中，我们都知道大表加字段（ALTER TABLE）容易锁表阻塞业务。但你有没有遇到过这种情况：一张只有不到 1000 条数据的小表，执行ALTER TABLE却一直卡住不动，甚至连RENAME TABLE都卡死？

今天在给一张仅 968 条数据的表device_update_xxxx添加update_content字段时，就遇到了这个诡异的问题。本文记录了完整的排查思路与最终定位原因的经历，希望对大家有所帮助。

现象描述

执行如下简单的加列 SQL：

SQL

ALTER TABLE `device_update_xxxx` ADD COLUMN `update_content` VARCHAR(1000) DEFAULT NULL COMMENT '升级内容';

本来以为几毫秒就能搞定的操作，结果执行框一直在转圈，长时间无响应。

尝试将字段改成VARCHAR(100)、甚至尝试新建新表数据迁移做RENAME TABLE，全都在关键一步无脑卡死。

排查过程与踩坑路线

1. 难道是数据量或字段问题？（排除）

检查了表数据量：仅968 条！

在 InnoDB 引擎下，1000 条数据的 DDL 即使是重建表也是瞬间完成的。因此排除数据量大导致的磁盘 I/O 瓶颈。

2. 查找常规元数据锁（MDL Lock）

既然卡住了，大概率是碰到了 MySQL 的MDL（Metadata Lock，元数据锁）。

在 MySQL 中，任何 DDL 操作（改结构、改表名等）都需要获取表的排他元数据锁（MDL Exclusive Lock）。如果有其他连接占着这张表的读锁不放，DDL 就会陷入等待（Pending）。

尝试运行：

SQL

SELECT ID, USER, HOST, DB, COMMAND, TIME, STATE, INFO FROM information_schema.processlist WHERE info LIKE '%device_update_xxxx%' OR state LIKE '%lock%';

结果：除了我自己刚发起的这行查询 SQL 外，什么都没有查出来！INFO和STATE一片空白。

又去查了performance_schema.metadata_locks，依然是一无所获。

3. 寻找“隐蔽”的长事务

为什么没有任何 SQL 在查这张表，DDL 还会卡住？

因为 MySQL 有个机制：只要某个连接开启了事务（BEGIN），并在事务内查过这张表，即使后续 SQL 执行完毕了，只要该事务没有COMMIT或ROLLBACK，它就会一直握着 MDL 共享读锁不释放！这种连接在processlist里通常显示为COMMAND = 'Sleep'，INFO为NULL，极其隐蔽。

尝试运行事务表联合查询：

SQL

SELECT p.ID AS connection_id, p.USER, p.HOST, p.COMMAND, p.TIME AS sleep_seconds, t.trx_started FROM information_schema.innodb_trx t JOIN information_schema.processlist p ON t.trx_mysql_thread_id = p.id;

排查了一圈未提交事务，终于在展开全量SHOW PROCESSLIST时发现了“终极元凶”！

真相大白：罪魁祸首竟然是它！

在线程列表中，发现了一个已经运行了15686 秒（近 4.5 个小时）的超级大查询（ID:79607049）：

SQL

SELECT loc FROM ( SELECT 'account_xxxx_xxxx.name' AS loc FROM `account_xxxx_xxxx` WHERE `name`='xx-xx-xx-20230718883783' UNION ALL SELECT 'account_xxxx_xxxx.name.target_customer_xxx' AS loc FROM `account_xxxx_xxxx.name` WHERE `target_customer_xxx`='xx-xx-xx-20230718883783' UNION ALL SELECT 'account_xxxx_xxxx.product_code' AS loc FROM `account_xxxx_xxxx ` WHERE `product_code`='xx-xx-xx-20230718883783' UNION ALL ... (下略无数个 UNION ALL)

原因分析：

1. 全库字符串搜索：某个开发/运维人员使用客户端工具在数据库里做“全库字符串搜索”，生成了一个包含几十上百个UNION ALL的巨型 SQL。

2. 锁链扩散：这个 SQL 会依次扫描全库的每一张表（其中就包含我们的device_update_firmware表）。

3. 持有锁不释放：由于 SQL 跑了 4 个多小时还没结束，它一直持有着所有被扫描表的 MDL 读锁。当我的ALTER TABLE请求排队等待排他锁时，整个表的结构变更就被彻底挂起了。

解决办法

确定了这个 ID 为79607049的长查询是无用/异常的全表扫描后，直接强行杀掉该线程：

SQL

KILL 79607049;

杀掉该线程的瞬间，再次执行ALTER TABLE：

SQL

ALTER TABLE `device_update_firmware` ADD COLUMN `update_content` VARCHAR(1000) DEFAULT NULL COMMENT '升级内容';

秒过！执行成功！

总结与经验教训

1. 表再小，也怕 MDL 锁：ALTER TABLE慢不一定是数据量大，极有可能是拿不到 MDL 锁。

2. 警惕“全库搜索”工具：尽量不要在共享开发库或生产库直接使用 Navicat/DBeaver 的“全库查找字符串”功能，这会产生大量全表扫描并长时间占用 MDL 锁，严重影响他人开发和线上业务。

3. 排查 MDL 锁的标准姿势：

   • 先看是否有显式锁表的 SQL：SHOW PROCESSLIST;

   • 再看是否有未提交的长事务：查询information_schema.innodb_trx结合processlist寻找处于Sleep状态但占有事务的连接。

   • 关注运行时间极长（Time数值巨大）的慢查询，即使表面上看起来跟你改的表无关，也可能因为全局扫描或关联查询隐式锁定了你的表。