MySQL 8.0 凌晨批处理把 undo 撑到 12GB，主从延迟 40s，怎么定位是事务粒度问题还是回放瓶颈

背景：MySQL 8.0.36，主从架构（单主 + 1 从，半同步），主库一台 16C/64G/ESSD，从库同规格。业务在每天 02:00–03:30 跑一次订单归档批处理：把 orders 表里 create_time 早于 90 天的数据 UPDATE 状态为 archived，再 DELETE 掉。总行数约 800 万，按 2 万行一批拆成 400 批，每批之间 SLEEP 0.5s，应用侧用同一个连接、同一个事务包裹（即一个大事务内 400 次 UPDATE/DELETE）。

现象：

1. 批处理期间，主库 SHOW ENGINE INNODB STATUS 里 History list length 从 0 涨到 800 万+，undo 表空间文件从 200MB 涨到 12GB；
2. 从库 Seconds_Behind_Master 间歇冲到 40s，主库本身 QPS 正常，慢日志里没有单条慢 SQL；
3. 批处理结束后 History list length 缓慢回落，undo 文件不会自动缩回去（这是预期）；
4. 业务侧反馈凌晨这 1.5 小时，在线查询 P99 没有明显恶化，但有一个走 orders 表的大查询从 200ms 涨到 1.2s。

已尝试：

• 把 400 批拆成 800 批（每批 1 万行），SLEEP 调到 1s，undo 峰值从 12GB 降到 6GB，主从延迟峰值从 40s 降到 18s，但批处理总耗时从 50 分钟涨到 110 分钟，业务不接受；
• 检查过从库的 slave_parallel_workers=8、slave_parallel_type=LOGICAL_CLOCK，但批处理期间从库 apply 线程 CPU 只用了 30%，看起来不是回放算力瓶颈；
• 怀疑过 MVCC 长事务导致在线查询扫描旧版本，但在线那条慢查询 EXPLAIN ANALYZE 显示扫描行数没变，只是每行耗时涨了，怀疑是 undo 链太长导致回表变慢，没有进一步验证。

想请教：

1. 这种「一个大事务内拆成多批 + SLEEP」的写法，InnoDB 的 purge 线程是不是要等整个事务 commit 之后才开始回收 undo？如果是，那 SLEEP 间隔其实对 undo 峰值没有帮助，真正能压 undo 的是「拆成多个独立事务 + 每批之间 COMMIT」？想确认一下这个理解对不对。
2. 从库 apply 线程 CPU 只有 30% 但延迟 40s，这个组合更可能是「单事务在从库侧串行回放」还是「行锁 / gap 锁冲突导致 apply 等待」？有没有更直接的指标（比如 performance_schema 的 events_waits 或 replica 状态表）能区分这两种情况？
3. 在线查询变慢但扫描行数不变，这个现象除了 undo 链过长，还有哪些可能？有没有低成本的验证手段（比如对比 trx_history_len 和查询耗时的相关性）？

结论先行：你的三个怀疑全部成立，且可以合并成一句话——根因是「单一大事务」，SLEEP 对 undo 峰值、对主从延迟、对在线查询慢，三件事全都没帮上忙。把 400 批拆成 400 个独立事务 + 每批之间显式 COMMIT，undo 峰值预计从 12GB 降到 1–2GB 量级，主从延迟峰值预计从 40s 降到个位数秒，在线查询那条 200ms→1.2s 大概率也一并消失。下面逐条展开依据和可执行验证。

1) purge 与 SLEEP 的关系：你的理解完全正确

InnoDB 的 purge 线程回收 undo 的判据是「该 undo 记录对应的版本是否已经没有任何活跃事务需要读」，而判断「是否还有活跃事务」依赖 trx_purge_view，其可见性由 MIN_TRX_ID 决定——只要那个 800 万行的大事务还没 commit，MIN_TRX_ID 就被钉死在事务开始前的值，期间所有被它覆盖行的旧版本 undo 一条都不能回收。

所以：

• SLEEP 0.5s / 1s 对 undo 峰值没有帮助——它只是让应用线程睡一会儿，事务边界没变，purge 视角里「活跃事务」还是那一个。
• 拆批（400 → 800）能降 undo，本质是降单事务覆盖行数（每事务 2 万 → 1 万），不是 SLEEP 的功劳。
• 真正能压 undo 的是「每批独立事务 + COMMIT」。COMMIT 之后 MIN_TRX_ID 前进，purge 线程才能批量回收该事务产生的 undo。

验证命令（批处理跑的过程中，主库上同时跑）：

-- 看长事务列表，确认是不是那一个事务钉着 MIN_TRX_ID
SELECT trx_id, trx_state, trx_started,
       TIMESTAMPDIFF(SECOND, trx_started, NOW()) AS age_sec,
       trx_mysql_thread_id, trx_query
FROM information_schema.innodb_trx
ORDER BY trx_started;

-- 看 purge 视图（8.0.19+ 有）
SHOW ENGINE INNODB STATUS\G  -- 找 "Purge done for trx" 和 "History list length"

你会看到批处理期间 innodb_trx 里始终只有一个大事务在跑，age 持续增长——这就是 undo 涨到 12GB 的直接证据。

改造建议（最小改动）：

-- 伪代码：应用侧批处理循环
for batch in batches:  -- 400 批
    BEGIN;
    UPDATE orders SET status='archived' WHERE id IN (...2 万行...);
    DELETE FROM orders WHERE id IN (...2 万行...);
    COMMIT;            -- 关键：显式 COMMIT，让 MIN_TRX_ID 前进
    SLEEP(0.2);        -- SLEEP 可以保留，但作用从「让 purge 跟得上」变成「让主从半同步 ACK 追上来」
end for

改造后预期：undo 峰值 ≈ 单批 2 万行的 undo 量（12GB / 400 ≈ 30MB，实际会略高），History list length 峰值从 800 万降到几千级别，purge 能跟上。

2) 从库 CPU 30% + 延迟 40s：更可能是「单事务串行回放」，不是锁冲突

判断依据：

• 行锁 / gap 锁冲突的特征是 apply 线程长时间阻塞在 lock_wait 上，CPU 接近 0%（不是 30%），且 performance_schema.events_waits_summary_global_by_event_name 里 wait/io/table/sql/handlock 或 wait/synch/mutex/innodb 会显著上升。
• 单事务串行回放的特征是 CPU 有占用（30% 正好是「在干活但干得慢」），且 SHOW REPLICA STATUS 里 Seconds_Behind_Source 持续增长但 Read_Source_Log_Pos 与 Exec_Master_Log_Pos 差值也持续增长——说明不是没在 apply，是 apply 速度跟不上。

为什么 LOGICAL_CLOCK + 8 workers 救不了你： slave_parallel_type=LOGICAL_CLOCK 的并行度依赖「不同事务的 GTID 提交顺序」来分组并行。你的大事务在 binlog 里是一个完整的 BEGIN...COMMIT 单元，从库的 applier 拿到这个事务后必须整体串行回放（单事务内 InnoDB 的行级并行在 8.0 还没开放），8 个 worker 线程对这个事务一个都帮不上忙。这就是「CPU 30% 但延迟 40s」的完整解释——不是算力不够，是并行度被事务边界锁死在 1。

直接验证命令（从库上跑，批处理期间）：

-- 1) 看 apply 线程在等什么
SELECT EVENT_NAME, COUNT_STAR, SUM_TIMER_WAIT/1000000000 AS total_ms
FROM performance_schema.events_waits_summary_global_by_event_name
WHERE EVENT_NAME LIKE 'wait/synch/%'
ORDER BY SUM_TIMER_WAIT DESC LIMIT 10;

-- 2) 看 replica 状态，确认 Exec 落后 Read
SHOW REPLICA STATUS\G  -- 关注 Read_Source_Log_Pos / Exec_Master_Log_Pos / Seconds_Behind_Source

-- 3) 看 applier 线程状态
SELECT thread_id, state, info
FROM information_schema.processlist
WHERE command = 'Slave SQL' OR user = 'system user';

如果 events_waits 里 lock_wait 占比很低、Exec_Master_Log_Pos 滞后 Read_Source_Log_Pos 持续增长、applier 线程 state 是 Waiting for slave SQL thread to read or execute 之类，就是串行回放，不是锁冲突。

改造后这个症状会自动消失：拆成 400 个独立事务后，LOGICAL_CLOCK 能把它们分到 8 个 worker 并行 apply（前提是这些事务的提交顺序在 binlog 里是交错的，你的批处理是单连接顺序提交，所以并行度仍然受限于提交顺序——这点要额外注意）。

进一步提并行度的可选方案：

• 把批处理改成多连接并发提交（比如 4 个连接各跑 100 批），binlog 里事务提交顺序交错，LOGICAL_CLOCK 才能真正并行。
• 或者从库开 slave_preserve_commit_order=OFF（有乱序风险，慎用）。
• 8.0.27+ 可以试 slave_parallel_workers=16 + 多连接并发，但收益递减。

3) 在线查询变慢但扫描行数不变：undo 链过长是主因，但还有 2 个次要嫌疑

主因：undo 链过长导致回表变慢——你的怀疑是对的，但「EXPLAIN ANALYZE 扫描行数没变」这个观察其实不能排除 undo 链问题，因为：

• EXPLAIN ANALYZE 的「扫描行数」统计的是逻辑行数（索引/表扫描的行数），不统计每行回表时读 undo 版本的次数。
• InnoDB 的 MVCC 读取路径：先读聚簇索引拿到当前版本，如果当前版本不可见（被未提交事务修改过），就沿 undo 链往回找可见版本。你的大事务在批处理期间持续修改 orders 表，那条在线大查询如果走二级索引回表，每行都可能要沿 undo 链走 N 步（N = 该行的修改历史长度），这一步的耗时 EXPLAIN ANALYZE 不单独显示，只体现在「每行耗时」上。

验证手段（低成本，批处理期间跑）：

-- 1) 看那条慢查询的实际扫描行数与耗时
SELECT query_id, sql_text, rows_examined, total_latency,
       total_latency / NULLIF(rows_examined, 0) AS latency_per_row
FROM sys.statements
WHERE sql_text LIKE '%orders%'
ORDER BY total_latency DESC LIMIT 5;

-- 2) 对比批处理期间与批处理结束后的同一查询
-- 关键指标：latency_per_row 在批处理期间应该显著升高
-- 如果升高 5–10 倍，基本就是 undo 链问题

-- 3) 直接看 undo 链长度（8.0 没有直接指标，但可以间接看）
SHOW ENGINE INNODB STATUS\G  -- History list length
-- History list length 800 万+ 时，单行 undo 链长度平均可达几十到几百

两个次要嫌疑（按概率排序）：

1. Buffer Pool 污染：批处理把 800 万行 orders 数据的页刷进 Buffer Pool，把在线查询热页挤出去，导致在线查询回表时更多走磁盘。验证：SHOW ENGINE INNODB STATUS 看 Buffer pool hit rate，批处理期间如果从 99.9% 掉到 95% 以下，就是这个原因。
2. ** redo log 刷盘竞争**：大事务产生大量 redo，刷盘时 ESSD 的 IO 队列被占，在线查询的 commit 等待变长。验证：iostat -x 1 看批处理期间 ESSD 的 await 和 avgqu-sz。

一句话总结

把 400 批拆成 400 个独立事务 + 每批 COMMIT，undo 峰值 / 主从延迟 / 在线查询慢三个问题会一起消失。SLEEP 保留 0.2s 足够（作用变成「让半同步 ACK 追上来」），批处理总耗时预计回到 50 分钟左右（因为 COMMIT 之后的 purge 开销被均摊到 400 个事务，单事务的 COMMIT 开销远小于 400 事务打包成一个的开销）。

参考：MySQL 8.0 官方 InnoDB undo logs、Replication and Binary Log。

结论先行：你的三个问题里，#1 的理解是对的——「一个大事务 + SLEEP」对压 undo 峰值几乎没用，真正能压 undo 的是「拆成多个独立事务 + 每批之间 COMMIT」；#2 在 LOGICAL_CLOCK 下更可能是「单事务串行回放 + 单 worker 打满」，CPU 30% 是全局均值，掩盖了 worker 单核打满的事实；#3 除了 undo 链过长，还要重点查「在线查询是否命中了未提交事务锁定的行」，这个用 SHOW ENGINE INNODB STATUS 的 LATEST DETECTED DEADLOCK / TRANSACTIONS 段就能低成本验证。

逐条展开。

一、关于「大事务 + SLEEP 不压 undo」

你的理解正确，但补一个机制细节。InnoDB 的 undo 记录在事务 commit 之前完全不可 purge，原因是 MVCC 的 ReadView 需要能回溯到该事务的 undo 版本。purge 线程（page_cleaner 里的 purge 子任务）判断一条 undo 能否回收，看的是 trx_purge_view——只有当所有活跃 ReadView 都不再需要这条 undo 时才会回收。一个未 commit 的事务会让它的 ReadView 一直「活着」，所以：

• 大事务内 400 批 UPDATE/DELETE，每批产生的 undo 都挂在这个事务名下；
• SLEEP 0.5s 期间事务没 commit，purge 线程一条都回收不了；
• 你观察到的「拆 800 批 + SLEEP 1s，undo 峰值从 12GB 降到 6GB」，这个降幅不是 SLEEP 带来的，而是「每批行数减半 → 每批 undo 体积减半 → 单批 undo 峰值减半」。SLEEP 本身对 undo 峰值零贡献。

正确改法：把 400 批拆成 400 个独立事务，每批之间显式 COMMIT（或应用层 autocommit + 每批一个连接）。这样每批 commit 后，只要没有长连接 ReadView 卡住（比如某个在线查询的游标没关），purge 就能立刻回收这批的 undo，undo 峰值从「800 万行 × 单行 undo 体积」降到「2 万行 × 单行 undo 体积」量级，通常能压到几百 MB。代价是每批一次 commit 的 redo 落盘，800 万行 / 400 批 = 每批 2 万行，commit 开销可忽略。

验证命令：

-- 批处理期间，每秒采样一次
SHOW ENGINE INNODB STATUS\G
-- 关注 History list length 和 trx_rseg_history_len
-- 如果改成独立事务后，History list length 峰值从 800 万+ 降到 < 50 万，说明改对了

二、关于「从库 CPU 30% 但延迟 40s」

这个组合在 slave_parallel_type=LOGICAL_CLOCK 下，几乎可以确定是单事务串行回放，不是行锁冲突。理由：

1. LOGICAL_CLOCK 的并行粒度是「主库事务的 commit 顺序 + 表级冲突检测」。你主库跑的是一个大事务（400 批全在一个事务里），从库收到这个事务后，它无法并行——因为 LOGICAL_CLOCK 的并行前提是「不同事务之间无表级冲突」，而你的 800 万行 UPDATE/DELETE 全在一个事务里，从库只能单 worker 串行回放。

2. 单 worker 串行回放 800 万行，假设每行 0.5ms（ESSD 随机写），纯回放就要 800 万 × 0.5ms = 4000s ≈ 66 分钟。你观察到 1.5 小时延迟 40s，完全吻合「单 worker 打满、其他 7 个 worker 闲着」的特征。

3. 行锁 / gap 锁冲突在从库侧几乎不会出现——从库是单线程 apply（或 LOGICAL_CLOCK 下的 worker），apply 线程拿的是「全局锁」，不存在并发 apply 线程之间的锁冲突。行锁冲突只可能发生在「apply 线程 vs 从库上的在线查询」之间，但你的在线查询是 SELECT（一致性读），不拿锁，不会阻塞 apply。

验证命令：

-- 从库上，批处理期间执行
SELECT * FROM performance_schema.events_waits_summary_global_by_event_name
WHERE event_name LIKE 'wait/io/%'
ORDER BY sum_timer_wait DESC LIMIT 10;

-- 看 worker 线程的 IO 等待：如果是单 worker 打满，
-- 这个 worker 的 io/sql 等待会接近 100%，其他 worker 接近 0
SELECT thread_id, event_name, sum_timer_wait/1000000000 as wait_ms
FROM performance_schema.events_waits_history
WHERE thread_id IN (SELECT thread_id FROM performance_schema.threads
                   WHERE name LIKE '%repl%')
ORDER BY sum_timer_wait DESC LIMIT 20;

如果看到「1 个 worker 的 io 等待占 90%+，其他 7 个 worker 接近 0」，就是单事务串行回放。

三、关于「在线查询扫描行数不变但变慢」

除了 undo 链过长，还有两个高概率原因，按排查优先级排：

原因 1（最高概率）：在线查询命中了未提交事务锁定的行，触发了锁等待超时或锁等待

你主库的大事务在 UPDATE/DELETE 时，会对这 800 万行加 X 锁（行锁 + gap 锁）。如果在线那条大查询是 SELECT ... FOR UPDATE 或 SELECT ... LOCK IN SHARE MODE（或者你的隔离级别是 READ COMMITTED 但查询走了锁定的索引路径），它会阻塞在锁等待上，直到大事务 commit。这种情况下：

• 扫描行数不变（EXPLAIN ANALYZE 的 rows 是估算值，不受锁影响）；
• 每行耗时涨（实际是「等锁」的时间，不是「回表」的时间）；
• 锁等待期间 CPU 不高（线程在 sleep 等锁）。

验证：

-- 在线查询变慢时，立刻在主库执行
SHOW ENGINE INNODB STATUS\G
-- 看 TRANSACTIONS 段，找到那个大事务的 trx_id
-- 看 LATEST DETECTED DEADLOCK 段（如果有死锁）
-- 看 LOCK STRUCTURE 段（8.0 已移除，改用 information_schema.innodb_lock_waits）

-- 8.0 用这个：
SELECT * FROM performance_schema.data_lock_waits;
SELECT * FROM performance_schema.data_locks WHERE lock_mode LIKE '%X%';

原因 2（次高概率）：undo 链过长导致 MVCC 回表变慢

InnoDB 的一致性读（SELECT 不加锁）会沿着 undo 链回溯到 ReadView 可见的版本。如果一条记录被 UPDATE 了 50 次（你的批处理里，同一行可能被 UPDATE 一次 + DELETE 一次 = 2 次 undo），undo 链长度 2，回表开销不大。但如果是 800 万行都在同一个事务里，ReadView 需要遍历的 undo 记录数 = 800 万，每次回表都要遍历整条 undo 链，这才是真正的瓶颈。

验证：

-- 在线查询变慢时，对比 trx_history_len 和查询耗时
-- 写一个脚本，每秒采样一次：
SELECT NOW(),
       (SELECT VARIABLE_VALUE FROM performance_schema.global_status
        WHERE VARIABLE_NAME='Innodb_history_list_length') as hl,
       (SELECT VARIABLE_VALUE FROM performance_schema.global_status
        WHERE VARIABLE_NAME='Innodb_rows_read') as rows_read;
-- 如果 hl 和查询耗时正相关（hl 涨 → 耗时涨），就是 undo 链问题

原因 3（低概率但值得排除）：Buffer Pool 被 undo 页挤占

800 万行 UPDATE/DELETE 产生的 undo 页会占 Buffer Pool。如果 Buffer Pool 是 32G，undo 页占 12GB，剩余 20GB 给数据页，在线查询的数据页可能被 evict 出去，导致「扫描行数不变但每次回表都要从磁盘读」。验证：

SHOW ENGINE INNODB STATUS\G
-- 看 BUFFER POOL AND MEMORY 段的
-- Buffer pool hit rate（如果从 99.9% 降到 95%，说明 evict 严重）
-- Pages made not young（如果暴涨，说明数据页被挤出去）

最终建议（按优先级）：

1. 把大事务拆成独立事务 + 每批 COMMIT，这是根治 undo 峰值和主从延迟的唯一正确解法；
2. 拆完之后，如果在线查询还慢，再查锁等待（原因 1）和 Buffer Pool 挤占（原因 3）；
3. 从库的 LOGICAL_CLOCK 并行配置不用改，拆成独立事务后，从库自然能并行回放。