一个 1.2 亿行 UPDATE 让从库复制延迟 31 分钟：大事务的拆解、定位与治理

一、现象与背景

上个月我们的一条离线对账任务，把订单宽表 t_order_stat（约 1.2 亿行、InnoDB、MySQL 8.0.32）里 status=0 AND created_at < '2024-01-01' 的存量记录一次性刷成 status=9。这条语句在主库跑了 4 分 12 秒就提交了，但下游读从库的报表服务，在提交后整整 31 分钟才看到这批数据生效。

影响是实打实的：

• 报表大盘延迟告警连跳 6 轮，值班同学以为是网络或磁盘问题；
• 期间有 3 个对账批次的结果"对不上"，被当成数据 bug 反复排查；
• 从库 Seconds_Behind_Master 一路爬到 1870s（约 31 分钟）才回落到 0。

关键点：主库 4 分钟干完的活，从库要 31 分钟才追平。这个 7.75 倍的差距，就是大事务 + 单线程 SQL 线程复制的教科书式后果。

二、原理与根因

先把机制讲清楚，否则调参就是碰运气。

1. 复制是"按事务"搬运，不是按行。 binlog 里一个事务是一个不可拆的单元。SQL 线程（slave applier）默认是单线程（replica_parallel_type 未开或为 LOGICAL_CLOCK 但事务串行），它必须等主库这个事务完整提交后，才能在从库开始回放，而且回放这个事务期间不能并行处理别的。1.2 亿行的 UPDATE 是一个巨型事务，从库只能"一口气"重放它。

2. 主库能 4 分钟，从库凭什么 31 分钟？ 三个差异叠加：

• 主库执行时索引、缓冲池是热的，且写的是自己的 redo；从库回放要走 row-based binlog 反解 + 逐行 apply，CPU 与锁开销更大；
• 主库这条 UPDATE 走的是范围扫描，命中 idx_status_created_at，但其中约 3800 万行 status 实际已是 9，仍被锁住并产生 binlog 事件（"无效更新"也写 binlog）；
• 从库这台机器配置比主库低一档（16C → 8C，盘是 ESSD PL1 vs 主库 PL2），回放是 CPU+IO 双瓶颈，SHOW REPLICA STATUS 里 Executed_Gtid_Set 长时间停在同一个 GTID 上。

3. 为什么延迟是"台阶式"的，而不是平滑爬升？ 因为大事务期间，SQL 线程被这一个事务占满，前后其它小事务全部排队。你看到的延迟曲线是：事务开始 → 直线爬升 → 事务回放完 → 瞬间回落到 0。这是判断"是不是大事务"的最直接证据。

三、具体做法（可复现）

第一步：确认是不是大事务，别急着调线程数。

-- 从库上看当前在回放的 GTID 与队列
SHOW REPLICA STATUS\G
-- 关注：
--   Executed_Gtid_Set      长时间不推进 = 卡在某个大事务
--   Replica_SQL_Running_State  出现 "Applying a big transaction" / "queueing"
--   Seconds_Behind_Master

配合主库 binlog 定位当事务大小：

mysqlbinlog --base64-output=decode-rows -v \
  --start-position=<binlog_pos> \
  --stop-position=<end_pos> \
  /var/lib/mysql/mysql-bin.000123 | grep -c "### UPDATE"

实测那条语句在 binlog 里产生了约 1.21 亿条 row 事件，事务体积 ~4.7GB，这就是回放慢的直接量。

第二步：把大事务拆小（治本）。 离线刷数绝不要用一条 UPDATE。改成主键分片批量提交，每批一个事务、控制单事务行数：

-- 应用层循环，每批 5 万行、提交后 sleep 50ms，把单事务限制在可回放范围内
UPDATE t_order_stat
   SET status = 9
 WHERE status = 0
   AND created_at < '2024-01-01'
   AND id BETWEEN :lo AND :hi;   -- 按 id 主键分片，单批 ~5w 行

改造后同样的数据量，单事务最大 ~2 万行，从库最大延迟从 31 分钟降到 40 秒以内，且延迟曲线变成小幅锯齿而非台阶。

第三步：开启并行复制（治标但必要）。 MySQL 8.0 默认 replica_parallel_type=LOGICAL_CLOCK、replica_preserve_commit_order=ON，确认并行度别被设成 1：

# my.cnf（从库）
[mysqld]
replica_parallel_type = LOGICAL_CLOCK
replica_parallel_workers = 16
replica_preserve_commit_order = ON

注意：并行复制救不了单个大事务本身（一个事务内部不能拆），它只能让大事务前后的其它事务并行，从而缩短"排队"。所以拆事务才是关键，并行度是配套。

第四步：给离线刷数加护栏。 在应用层强制：单事务影响行数 > 50000 即拒绝，必须走分片；离线任务走专用低优先级连接，避开业务高峰。

四、踩过的坑与反直觉之处

1. Seconds_Behind_Master 会骗人。 它只统计"已提交事务"的时间差，大事务正在回放、未提交时它可能显示 0 或跳变，别只看这一个指标，要看 Executed_Gtid_Set 是否停滞 + 队列深度。
2. 把 replica_parallel_workers 从 1 拉到 32 之后延迟只降了一点点。 因为瓶颈是那个 4.7GB 的单事务，内部串行，加线程无效。一度误导我们以为是并行度不够。
3. "无效更新也写 binlog"。 那 3800 万行 status 本已是 9 的记录仍被锁、仍进 binlog、仍要回放。UPDATE ... WHERE status=0 如果过滤条件不精确，浪费是实打实的。
4. 回滚比提交更伤从库。 中途我们试过一次没分片的 UPDATE，跑到一半 kill，主库回滚了 2 亿行 redo，从库回放回滚又花了 18 分钟——回滚事务同样是大事务。

五、结论与可复用清单

结论一句话：从库复制延迟大，先怀疑大事务，再谈并行度；治本靠拆事务，治标靠并行复制，护栏靠行数上限。

可复用清单：

• [ ] 延迟告警触发时，第一动作看 Replica_SQL_Running_State 与 Executed_Gtid_Set，而非只盯 Seconds_Behind_Master；
• [ ] 用 mysqlbinlog 量化当事务 row 事件数与体积，作为定位证据；
• [ ] 离线刷数一律主键分片、单批 ≤ 5 万行、批间 sleep，禁止单条大 UPDATE；
• [ ] 应用层加"单事务影响行数 > 50000 即拒绝"的硬护栏；
• [ ] 从库确认 replica_parallel_type=LOGICAL_CLOCK + replica_parallel_workers≥8 + preserve_commit_order=ON；
• [ ] 离线任务走低优先级连接，避开业务高峰窗口；
• [ ] 记住：并行复制救不了单个大事务内部，拆事务才是唯一治本手段。

参考规模：1.2 亿行 / ~4.7GB 单事务 / 主库 4m12s vs 从库 31m，拆成单批 ≤2 万行后从库最大延迟降到 40s 内。