【踩坑总结】表只有 1000 条数据执行 ALTER TABLE 却卡死排查与解决全过程前言在 MySQL 运维和日常开发中我们都知道大表加字段ALTER TABLE容易锁表阻塞业务。但你有没有遇到过这种情况一张只有不到 1000 条数据的小表执行ALTER TABLE却一直卡住不动甚至连RENAME TABLE都卡死今天在给一张仅 968 条数据的表device_update_xxxx添加update_content字段时就遇到了这个诡异的问题。本文记录了完整的排查思路与最终定位原因的经历希望对大家有所帮助。现象描述执行如下简单的加列 SQLSQLALTER 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 的MDLMetadata Lock元数据锁。在 MySQL 中任何 DDL 操作改结构、改表名等都需要获取表的排他元数据锁MDL Exclusive Lock。如果有其他连接占着这张表的读锁不放DDL 就会陷入等待Pending。尝试运行SQLSELECT 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 SleepINFO为NULL极其隐蔽。尝试运行事务表联合查询SQLSELECT 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:79607049SQLSELECT loc FROM ( SELECT account_xxxx_xxxx.name AS loc FROM account_xxxx_xxxx WHERE namexx-xx-xx-20230718883783 UNION ALL SELECT account_xxxx_xxxx.name.target_customer_xxx AS loc FROM account_xxxx_xxxx.name WHERE target_customer_xxxxx-xx-xx-20230718883783 UNION ALL SELECT account_xxxx_xxxx.product_code AS loc FROM account_xxxx_xxxx WHERE product_codexx-xx-xx-20230718883783 UNION ALL ... (下略无数个 UNION ALL)原因分析全库字符串搜索某个开发/运维人员使用客户端工具在数据库里做“全库字符串搜索”生成了一个包含几十上百个UNION ALL的巨型 SQL。锁链扩散这个 SQL 会依次扫描全库的每一张表其中就包含我们的device_update_firmware表。持有锁不释放由于 SQL 跑了 4 个多小时还没结束它一直持有着所有被扫描表的 MDL 读锁。当我的ALTER TABLE请求排队等待排他锁时整个表的结构变更就被彻底挂起了。解决办法确定了这个 ID 为79607049的长查询是无用/异常的全表扫描后直接强行杀掉该线程SQLKILL 79607049;杀掉该线程的瞬间再次执行ALTER TABLESQLALTER TABLE device_update_firmware ADD COLUMN update_content VARCHAR(1000) DEFAULT NULL COMMENT 升级内容;秒过执行成功总结与经验教训表再小也怕 MDL 锁ALTER TABLE慢不一定是数据量大极有可能是拿不到 MDL 锁。警惕“全库搜索”工具尽量不要在共享开发库或生产库直接使用 Navicat/DBeaver 的“全库查找字符串”功能这会产生大量全表扫描并长时间占用 MDL 锁严重影响他人开发和线上业务。排查 MDL 锁的标准姿势先看是否有显式锁表的 SQLSHOW PROCESSLIST;再看是否有未提交的长事务查询information_schema.innodb_trx结合processlist寻找处于Sleep状态但占有事务的连接。关注运行时间极长Time数值巨大的慢查询即使表面上看起来跟你改的表无关也可能因为全局扫描或关联查询隐式锁定了你的表。
【踩坑总结】表只有 1000 条数据执行 ALTER TABLE 却卡死排查与解决全过程前言在 MySQL 运维和日常开发中我们都知道大表加字段ALTER TABLE容易锁表阻塞业务。但你有没有遇到过这种情况一张只有不到 1000 条数据的小表执行ALTER TABLE却一直卡住不动甚至连RENAME TABLE都卡死今天在给一张仅 968 条数据的表device_update_xxxx添加update_content字段时就遇到了这个诡异的问题。本文记录了完整的排查思路与最终定位原因的经历希望对大家有所帮助。现象描述执行如下简单的加列 SQLSQLALTER 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 的MDLMetadata Lock元数据锁。在 MySQL 中任何 DDL 操作改结构、改表名等都需要获取表的排他元数据锁MDL Exclusive Lock。如果有其他连接占着这张表的读锁不放DDL 就会陷入等待Pending。尝试运行SQLSELECT 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 SleepINFO为NULL极其隐蔽。尝试运行事务表联合查询SQLSELECT 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:79607049SQLSELECT loc FROM ( SELECT account_xxxx_xxxx.name AS loc FROM account_xxxx_xxxx WHERE namexx-xx-xx-20230718883783 UNION ALL SELECT account_xxxx_xxxx.name.target_customer_xxx AS loc FROM account_xxxx_xxxx.name WHERE target_customer_xxxxx-xx-xx-20230718883783 UNION ALL SELECT account_xxxx_xxxx.product_code AS loc FROM account_xxxx_xxxx WHERE product_codexx-xx-xx-20230718883783 UNION ALL ... (下略无数个 UNION ALL)原因分析全库字符串搜索某个开发/运维人员使用客户端工具在数据库里做“全库字符串搜索”生成了一个包含几十上百个UNION ALL的巨型 SQL。锁链扩散这个 SQL 会依次扫描全库的每一张表其中就包含我们的device_update_firmware表。持有锁不释放由于 SQL 跑了 4 个多小时还没结束它一直持有着所有被扫描表的 MDL 读锁。当我的ALTER TABLE请求排队等待排他锁时整个表的结构变更就被彻底挂起了。解决办法确定了这个 ID 为79607049的长查询是无用/异常的全表扫描后直接强行杀掉该线程SQLKILL 79607049;杀掉该线程的瞬间再次执行ALTER TABLESQLALTER TABLE device_update_firmware ADD COLUMN update_content VARCHAR(1000) DEFAULT NULL COMMENT 升级内容;秒过执行成功总结与经验教训表再小也怕 MDL 锁ALTER TABLE慢不一定是数据量大极有可能是拿不到 MDL 锁。警惕“全库搜索”工具尽量不要在共享开发库或生产库直接使用 Navicat/DBeaver 的“全库查找字符串”功能这会产生大量全表扫描并长时间占用 MDL 锁严重影响他人开发和线上业务。排查 MDL 锁的标准姿势先看是否有显式锁表的 SQLSHOW PROCESSLIST;再看是否有未提交的长事务查询information_schema.innodb_trx结合processlist寻找处于Sleep状态但占有事务的连接。关注运行时间极长Time数值巨大的慢查询即使表面上看起来跟你改的表无关也可能因为全局扫描或关联查询隐式锁定了你的表。
