主从复制延迟从 0.5s 涨到 40s：一条 2.3M 行大事务拖垮 SQL 线程的根因与三方案取舍

作者陈星河-882

MySQL主从复制大事务MTS慢查询治理

结论先行：从库延迟不是"网络慢"，而是主库一条 2,348,000 行的大事务被从库单线程 SQL 线程串行回放。修它有三条路——拆事务（治本）、开 MTS 并行回放（缓解）、分库（重锤），三者各有代价，不能只挑一个。

一、现象：凌晨对账批处理一跑，从库延迟从 0.5s 爬到 42s

去年给一套订单系统做从库巡检。架构是 MySQL 8.0.32 一主一从，GTID 已开，从库走 replica_parallel_type=LOGICAL_CLOCK、replica_parallel_workers=16。平时从库延迟稳定在 0.3–0.5s，业务查询走从库没问题。

但每天凌晨 02:30 的对账批处理一跑，从库 Seconds_Behind_Source 就开始爬：

02:31 延迟 2s
02:34 延迟 19s
02:37 延迟 42s，持续 3 分多钟才回落

业务侧表现是"从库查到的对账结果比主库旧"，客服后台一度把已入账的订单判成未入账。

二、根因：不是网络，是单线程 SQL 线程被一个大事务"锁死"

先排除网络：从库到主库 RTT 只有 0.4ms，SHOW REPLICA STATUS 里 Replica_IO_Running=Yes，IO 线程一直在拉 binlog，说明数据早就到了，是回放（apply）跟不上。

真正的瓶颈在主库那条 SQL。用 mysqlbinlog 把当日的 binlog 翻出来，发现 02:30 有一个事务：

# at 118,204,331
#231011 02:30:14 server id 1  end_log_pos 121,455,902  Transaction coordinator: 0x1f3a
BINLOG '...'

这一个 transaction 从 start_log_pos 到 end_log_pos 占了 3.1 GB，对应业务侧一条：

UPDATE order_settle s
   JOIN order_detail d ON d.order_id = s.order_id
   SET s.state = 'SETTLED'
 WHERE d.batch_id = 20231011003 AND s.state = 'PENDING';

单条 UPDATE 命中 2,348,000 行。

机制上为什么它会把从库拖死：

InnoDB 的 binlog 是按事务边界切 event 的，这个大事务在 binlog 里是一个不可拆分的单元；
从库 SQL 线程回放事务时，为了保证和主库一致的可见性与主键顺序，同一个事务内部的事件必须由同一个 worker 串行处理——哪怕你开了 16 个 replica_parallel_workers，这个事务内部照样是单线程；
2.3M 行的行更新 + 二级索引维护，从库单机 apply 能力大概 20–30 万行/秒，光回放就要 80–120s，期间它后面排队的几百个小事务全被堵在队尾。

所以 replica_parallel_workers 再高也救不了单个大事务，这是很多人开完 MTS 还延迟的误区。

三、三方案取舍：拆事务（治本）/ MTS（缓解）/ 分库（重锤）

方案 A：拆小事务（治本，首选）

把一条 2.3M 行的 UPDATE 按 batch_id + 时间片 拆成一批批，每批控制行数：

-- 拆成每批 5000 行，提交一次
UPDATE order_settle s
   JOIN order_detail d ON d.order_id = s.order_id
   SET s.state = 'SETTLED'
 WHERE d.batch_id = 20231011003
   AND s.state = 'PENDING'
   AND d.id BETWEEN :lo AND :hi
LIMIT 5000;
-- 应用层循环推进 :lo/:hi，每批独立 COMMIT

拆完后单事务最大 5000 行，binlog 里每个事务 < 40 KB，从库延迟回到 0.3s。

方案 B：开 MTS 并行回放（缓解，救"多事务排队"不救"单大事务"）

# my.cnf（从库）
replica_parallel_type = LOGICAL_CLOCK
replica_parallel_workers = 16

适用于"大量小事务串行回放"的场景。但要确认业务没有同一主键的跨事务写依赖（否则并行会破坏顺序），需要业务侧保证"同一 order_id 的写集中在一个逻辑批次"。

方案 C：分库（重锤，成本最高）

按 batch_id 或租户把对账流量拆到不同实例。收益是彻底隔离，代价是应用改造、路由层、跨库 join 变分布式，投入以周计。只在 A+B 都压不住、且业务在高速增长时再上。

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

开了 16 worker 还延迟：MTS 并行的是"事务之间"，不是"事务内部"。单大事务永远单线程。
把 binlog_row_value_options=MINIMAL 当省空间手段：我们一度为压 binlog 体积设了 MINIMAL，结果某张表 deleted 行的旧值丢了，从库回放一个 UPDATE 时拿不到完整前镜像，直接报 ER_BINLOG_ROW_IMAGE_TOO_LARGE 卡死。改回 FULL 才恢复。
Seconds_Behind_Source 会骗人：它只统计 SQL 线程回放到哪个 GTID，不区分"IO 没拉到"还是"apply 慢"。这次 IO 线程早就拉完了，光看这个数字会误判成网络问题。要看 performance_schema.replication_applier_status_by_worker 里各 worker 的 THREAD_ID 有没有在动。
拆事务别用 LIMIT 一把梭：UPDATE ... LIMIT 5000 不带范围条件时，每批都要全表扫找 5000 行，反而更慢。必须带 d.id BETWEEN 这种可走索引的范围。

五、结论与可复用清单

从库延迟先分三档定位：IO 没拉到（网络/GTID）→ apply 慢（单大事务/单线程）→ 排队（多事务串行），对应 IO 线程、SQL 线程、MTS 三个抓手。
单大事务治本只有一条路：拆小事务，控制在单事务 < 5000–10000 行、binlog 单事务 < 1MB。
MTS 是缓解"多事务排队"，不是救"单大事务"；开之前确认没有同主键跨事务写依赖。
分库是重锤，A+B 压不住且业务在涨再上。
巡检命令清单：
- SHOW REPLICA STATUS\G 看 Seconds_Behind_Source、Retrieved_Gtid_Set vs Executed_Gtid_Set 的差值
- mysqlbinlog --start-position=... binlog.000xxx 找大事务的 end_log_pos - start_log_pos
- SELECT * FROM performance_schema.replication_applier_status_by_worker\G 看每个 worker 是否在动
- 应用侧：批处理加"单批行数 + 可走索引的范围条件 + 每批独立 COMMIT"

赞 0 · 收藏 0 · 评论 0 · 浏览 0

暂无评论。