如何通过优化MySQL长事务管理，减少其对DDL操作的影响？

本文共计1165个文字，预计阅读时间需要5分钟。

MySQL（尤其是InnoDB）在执行ALTER TABLE时，在多种情况下需要获取表的SX（共享-独占）锁或X（独占）锁。只有当一个活跃的事务正在访问该表（例如，正在执行SELECT操作）且尚未提交，DDL操作（如ALTER TABLE）就会排队等待——这不是等待语句执行完毕，而是等待整个事务结束。

常见现象是，使用SHOW PROCESSLIST查看时，会看到ALTER TABLE操作处于Waiting for table metadata lock状态。

根本原因不是“查询慢”，而是事务生命周期太长：比如应用层开了事务但忘了 COMMIT，或用 ORM 自动开启长事务处理批量逻辑，又或者监控脚本跑了几十分钟的 SELECT ... FOR UPDATE。

• 显式事务中未提交的 SELECT、INSERT、UPDATE 都会持有 MDL（Metadata Lock）读锁，阻塞 DDL 写锁
• READ-COMMITTED 隔离级别下，普通 SELECT 不加锁，但事务一旦开始（BEGIN），后续所有语句都在同一事务上下文中，MDL 锁持续到 COMMIT
• Percona Toolkit 的 pt-online-schema-change 或 gh-ost 虽能绕过锁表，但它们本身也会探测并规避长事务——如果发现有活跃事务超过阈值，会直接退出

如何快速定位并终止干扰事务

别靠猜。先查谁在占着表，再决定是否干掉它。重点看 TRX_STARTED 时间和 TRX_STATE，而不是只盯 TRX_QUERY。

SELECT trx_id, trx_started, trx_state, trx_mysql_thread_id, SUBSTRING_INDEX(trx_query, ' ', 4) AS short_query FROM information_schema.INNODB_TRX WHERE trx_started < DATE_SUB(NOW(), INTERVAL 60 SECOND) ORDER BY trx_started;

• 用 trx_started < DATE_SUB(NOW(), INTERVAL 60 SECOND) 过滤“明显异常”的长事务（可根据业务调整为 30s 或 5m）
• trx_state = 'RUNNING' 未必安全——它可能刚执行完一条语句，但事务还开着；真正要看的是 trx_started 时间戳
• 确认无误后，用 KILL <thread_id> 终止，不是 KILL QUERY（后者只杀当前语句，事务仍存活）
• 避免在高峰期对核心业务线程执行 KILL，优先联系对应服务 owner 共同判断

ALTER TABLE 加上 ALGORITHM=INSTANT 真的不锁表吗

只对极少数变更有效：ADD COLUMN（末尾）、DROP COLUMN（非主键/索引列）、RENAME COLUMN、CHANGE COLUMN（仅改注释）。而且必须满足：表格式是 Dynamic 或 Compressed，MySQL 版本 ≥ 8.0.12，且没有全文索引、空间索引、生成列依赖该字段。

• ADD COLUMN 加在中间位置？不支持 INSTANT，自动退化为 COPY 或 INPLACE，仍需锁表
• MODIFY COLUMN 改类型（如 VARCHAR(100) → VARCHAR(200)）在 8.0.29+ 支持 INSTANT，但改成 TEXT 或加 NOT NULL 默认值就不行
• 执行前务必检查 SHOW CREATE TABLE 输出里的 ROW_FORMAT，如果是 Redundant 或 Compact，INSTANT 直接报错
• 即便支持 INSTANT，也要注意 binlog 记录方式变化：它写的是“元数据变更”，从库回放时若版本不一致，可能失败

线上 DDL 变更前必须做的三件事

DDL 不是“跑一下就完事”，它是数据库最危险的操作之一。没做这三步，等于裸奔。

• 在从库或影子库上用真实数据量压测完整流程，观察 INFORMATION_SCHEMA.INNODB_METRICS 中的 dml_reads、dml_writes 是否突增，以及磁盘 IO 峰值
• 检查 innodb_lock_wait_timeout 和 lock_wait_timeout 设置——默认 50 秒，如果 DDL 等锁超时，会报 Lock wait timeout exceeded，但事务可能已部分执行（如已建临时表），导致残留状态
• 确保 binlog_format = ROW，且从库开启了 slave_parallel_workers > 0；否则 DDL 在从库串行回放，可能拖慢复制，甚至因锁冲突卡死

真正麻烦的从来不是语法对不对，而是事务边界模糊、锁等待链隐藏得深、还有人把 DDL 当成低风险操作去凌晨三点手动执行。