一、现象：索引没问题、统计信息没问题，但点查就是慢了 1000 倍
今年 3 月给一套供应链结算系统做慢查询治理。核心表 t_settle_detail：MySQL 8.0.36、InnoDB、单表 8.4 亿行、表 + 索引合计 312 GB，innodb_buffer_pool_size=128G。
业务方甩来一条"不可能慢"的 SQL——按主键点查：
SELECT settle_id, order_no, amount, status, created_at
FROM t_settle_detail
WHERE settle_id = 98237461;
平时 P99 稳定在 1.8 ~ 2.4 ms（buffer pool 命中率 99.7%，一次 B+ 树定位 + 一行返回）。但 3 月 12 日凌晨 4 点到早上 9 点，同一 SQL 的 P99 飙到 2.3 s，持续约 5 小时。
诡异点：
EXPLAIN输出完全没变：type=const, key=PRIMARY, rows=1, Extra=NULL——优化器选的路径 100% 正确；innodb_buffer_pool_reads / innodb_buffer_pool_read_requests比值仍是 0.003，没有冷读；- 没有锁等待：
SELECT * FROM information_schema.innodb_trx WHERE trx_state='RUNNING'只有 1 条事务，trx_rows_locked=0； - 磁盘 IO：
iostat显示await从 0.1ms 涨到 87ms，但avgqu-sz只有 1.2——不是排队多，是单次读慢； - CPU 反而下降（从 62% 跌到 21%），说明线程在等 IO 而非计算。
二、根因：MVCC 版本链 + undo log 溢出到磁盘
机制拆解
InnoDB 的"一致性读"靠 MVCC：每次读都要拿到一个 Read View，然后沿着该行的 undo log 版本链回退到 Read View 创建时刻可见的那一版。如果该行近期被反复 UPDATE，版本链就长，每次读都要遍历多个 undo record。
关键：undo log 存在 undo tablespace 里。当 undo 总量超过 innodb_undo_log_truncate 阈值（8.0 默认 1 GB）时，InnoDB 会异步 truncate 释放空间；但如果长事务一直持有 Read View（即阻止 purge 线程回收旧版本），undo tablespace 就会持续膨胀，最终触发磁盘 IO（因为 buffer pool 装不下全部 undo 页）。
这次的触发链
凌晨 3:50，一个 T+1 对账 ETL 任务启动：
-- ETL 伪代码：一条大事务更新 4200 万行
BEGIN;
UPDATE t_settle_detail
SET status = 'SETTLED', settled_at = NOW()
WHERE batch_id = 20250311 AND status IN ('PENDING','PROCESSING');
-- 耗时 4 小时 37 分钟，4200 万行 UPDATE 在 4 小时里慢慢 commit 前的 DML 都在同一事务内
COMMIT;
这条事务持有 Read View 不释放 → purge 线程无法回收旧版本 → undo tablespace 从 3 GB 涨到 37 GB → 其中大量 undo 页被驱逐出 buffer pool → 点查时如果命中"近期被 ETL 更新过的行"（4200 万行中任何一行），就要从磁盘读 undo 页回溯版本链。
单行版本链平均长度：这次 ETL 更新了 status 和 settled_at 两列，加上之前一天内可能还有 2~3 次状态翻转，平均版本链 4~6 个 undo record。每个 undo record 约 120~180 字节，链上总共 500~1000 字节——但它们在 undo tablespace 的不同页上，每读一个 record 可能触发一次 page read（如果不在 buffer pool 里）。
2.3s ÷ 5 个版本 ≈ 460ms/次 undo 页读取——这正好对应 iostat 里 await=87ms 加上 buffer pool 未命中时的随机读延迟（SSD 随机 4K read 约 60~90μs，但这里是 16KB 页 + 页内定位 + 可能的 double write 检查，实测单次冷读 8~15ms；2.3s 说明大量 undo 页已经不在 buffer pool 且 IO 队头阻塞）。
与"统计信息漂移"的区别
社区里陈寒山-172 写过一篇统计信息漂移的经典案例：索引没变、SQL 没变，但优化器选错了路径。而这次是：路径完全正确（PRIMARY KEY 点查），但正确路径本身被版本链拖慢了。两者的排查起点完全不同——前者看 EXPLAIN 的 rows 估算，后者看 SHOW ENGINE INNODB STATUS 的 TRANSACTIONS 段和 undo tablespace 大小。
三、具体做法：排查 → 止血 → 根治
3.1 排查：3 条命令定位长事务
-- 1. 找到"最老"的活跃事务（它阻止了 purge）
SELECT trx_id, trx_state, trx_started, trx_mysql_thread_id, trx_query
FROM information_schema.innodb_trx
ORDER BY trx_started ASC
LIMIT 5;
-- 2. 看 undo tablespace 当前大小（8.0 新增）
SELECT name, file_size / 1024 / 1024 AS size_mb
FROM information_schema.innodb_tablespaces
WHERE name LIKE 'innodb_undo_%';
-- 3. 看 purge 是否被阻塞（历史 5.7 看 SHOW ENGINE INNODB STATUS 的 "History list length"）
-- 8.0 里 History list length 仍在 SHOW ENGINE INNODB STATUS 的 TRANSACTIONS 段
-- 如果 > 100000，说明有长事务卡着 purge
本次排查结果：
trx_started = 2025-03-12 03:50:12，trx_mysql_thread_id = 48291- undo tablespace 合计 37.2 GB（正常态 2.8 GB）
- History list length：28,437,162（正常 < 5000）
3.2 止血：KILL + 控制影响
-- 确认该线程是 ETL 任务（不是业务写入）后：
KILL 48291;
注意：KILL 一个已跑 4 小时 37 分钟、更新了 4200 万行的大事务，回滚本身也需要时间——本次回滚耗时 22 分钟（InnoDB 回滚是逐行 undo，4200 万行 × 平均 2 列 ≈ 8400 万条 undo record）。回滚期间：
- 点查 P99 从 2.3s 逐步降到 380ms（回滚释放部分版本链）；
- 回滚完成后 P99 回到 2.1ms；
- undo tablespace 在 purge 线程追赶后 15 分钟内回到 3.1 GB。
3.3 根治：拆事务 + 监控告警
拆事务（核心）：ETL 的 4200 万行大 UPDATE 拆成 每 5 万行一批 commit：
-- 改造后
SET @batch_size = 50000;
SET @offset = 0;
WHILE @offset < 42000000 DO
UPDATE t_settle_detail
SET status = 'SETTLED', settled_at = NOW()
WHERE batch_id = 20250311
AND status IN ('PENDING','PROCESSING')
AND settle_id >= @offset
AND settle_id < @offset + @batch_size
LIMIT @batch_size;
COMMIT;
SET @offset = @offset + @batch_size;
DO SLEEP 0.05; -- 每批间隔 50ms，控制对在线查询的 IO 冲击
END WHILE;
效果：单事务最长持有时间从 4h37min 降到 ~8 秒（5 万行 UPDATE + commit），purge 线程可以持续回收版本，undo tablespace 峰值从 37 GB 降到 4.2 GB。
监控告警（加到现有 Zabbix / Prometheus 里）：
# Prometheus 告警规则
- alert: InnoDBHistoryListTooLong
expr: innodb_trx_history_list_length > 10000
for: 2m
labels:
severity: warning
annotations:
summary: "InnoDB History List 过长（{{ $value }}），可能有长事务阻止 purge"
- alert: UndoTablespaceBloat
expr: innodb_tablespace_size_bytes{tablespace=~"innodb_undo.*"} / 1024 / 1024 / 1024 > 10
for: 5m
labels:
severity: warning
annotations:
summary: "Undo tablespace 超过 10GB，MVCC 版本链可能影响在线查询"
参数调优（非必须，但建议）：
# my.cnf
innodb_purge_threads = 4 # 默认 4，大表场景可提到 8
innodb_undo_log_truncate = ON # 8.0 默认 ON，确认没被改
innodb_max_undo_log_size = 1G # 默认 1G，不要调太大
四、踩过的坑与反直觉之处
坑 1：SHOW PROCESSLIST 看不到"伤害"
那个 ETL 线程在 SHOW PROCESSLIST 里显示 State = Updating，Time = 16620（秒），但它的 trx_rows_locked = 0（因为 UPDATE 用的是 batch_id 范围扫描 + 逐行加锁，已锁的行在 information_schema.innodb_trx 里才看得到）。只看 PROCESSLIST 的 Time 会误判为"慢查询"，实际它不是慢，是长。长事务对在线查询的伤害不来自锁，来自 MVCC 版本链。
坑 2：KILL 之后不是立刻恢复
很多人以为 KILL 了长事务，点查就立刻回到 2ms。实际不是——回滚 22 分钟期间，那些行的版本链还在（回滚本身就是写 undo），只是不再"增长"。真正的恢复要等 purge 线程把旧版本清完（本次约 15 分钟）。所以 KILL 后 P99 是渐进恢复：2.3s → 380ms → 120ms → 2.1ms，总共约 37 分钟。
坑 3：buffer pool 命中率"看起来正常"
innodb_buffer_pool_read_requests 包含了 undo 页的读取，但 SHOW GLOBAL STATUS 里的命中率是全局比值——128G buffer pool 里 37G 被 undo 页占满后，热数据页被驱逐，但命中率分母（总请求数）里点查只占极小比例，所以全局命中率仍显示 99.7%。必须看 iostat 的 await 和 r_await 才能发现"读变慢了"。
坑 4：innodb_rollback_on_timeout 不会救你
innodb_lock_wait_timeout 默认 50s，超时后回滚当前语句（不是整个事务）。ETL 那种"一条 UPDATE 4200 万行"的场景，语句本身不会超时（它不阻塞在锁等待上，是在慢慢跑 DML），所以这个参数完全不起作用。
反直觉：拆事务反而让 ETL 总耗时变长了
原来一条大 UPDATE 4200 万行：4h37min。拆成 840 批 × 5 万行：总耗时 5h12min（多了 35 分钟）。但在线点查 P99 从 2.3s 降到 2.1ms。用 12% 的 ETL 耗时换 100% 的在线 SLA——这笔账对结算系统来说绝对值。
五、结论与可复用清单
核心结论
当
EXPLAIN显示执行计划完全正确（type=const, key=PRIMARY, rows=1），但点查 P99 仍然飙高时，第一个要查的不是索引、不是统计信息，而是 MVCC 版本链：看 History list length 和 undo tablespace 大小。
可复用排查清单
| 步骤 | 命令 / 操作 | 判断标准 |
|------|------------|---------|
| 1 | EXPLAIN 确认执行计划 | 如果 type=const, rows=1 但实际慢 → 不是优化器问题 |
| 2 | information_schema.innodb_trx ORDER BY trx_started | 找最老的活跃事务 |
| 3 | information_schema.innodb_tablespaces WHERE name LIKE 'innodb_undo_%' | undo > 5GB 且持续增长 → 版本链膨胀 |
| 4 | SHOW ENGINE INNODB STATUS → TRANSACTIONS 段 → History list length | > 10000 → purge 被阻塞 |
| 5 | iostat -x 1 5 看 await / r_await | await 从 <1ms 涨到 >50ms → 磁盘 IO 是瓶颈 |
| 6 | 确认长事务来源后 KILL（业务允许时） | 回滚时间 ≈ DML 行数 / 回滚速率 |
| 7 | 根治：拆事务（每 5 万行 commit）+ 加监控 | undo 峰值 < 5GB，History list < 5000 |
适用场景
- 大表（> 1 亿行）有高频点查 + 批量 ETL/对账/状态翻转的场景；
- MySQL 8.0.x（5.7 机制相同，但 undo tablespace 监控方式不同——5.7 没有
innodb_tablespaces视图，看SHOW ENGINE INNODB STATUS的 LOG 段）； - 不适用于：纯 OLAP 场景（没有在线点查 SLA 要求）、小表（undo 链再短也就几十字节，不会触发磁盘 IO）。
与"统计信息漂移"的鉴别
两者都是"索引没变、SQL 没变、但变慢了"，但排查方向完全不同：
- 统计信息漂移：
EXPLAIN的rows估算值明显偏大/偏小，优化器选错了索引或访问路径； - MVCC 版本链：
EXPLAIN完全正确，优化器选对了路径但路径本身变慢（版本链回溯 + undo 页磁盘读）。
遇到"EXPLAIN 正确但慢"时，先查 History list length，再查统计信息——因为前者是更常见、更隐蔽的原因。