生产事故复盘：大事务导致主从延迟

提出问题

MySQL 主从复制是读写分离架构的基石，但很多团队在线上遇到的主从延迟问题，根源往往不是硬件不够、也不是网络抖动，而是一个被忽略的元凶——大事务。

大事务就像一颗定时炸弹：在主库上执行时可能只是慢几秒钟，但到了从库，由于 SQL 线程单线程回放的特性，一个 10 万行更新的 Binlog Event 就能让从库延迟从 0 飙升到数分钟。更可怕的是，读写分离的业务中，读请求从延迟的从库读到的是过时数据，导致订单状态不一致、支付回调失败、库存超卖等连锁事故。

本文用一个真实案例复盘大事务如何一步步拖垮主从同步，并给出从监控、预防到兜底的完整方案。

时序复盘：大事务如何一步步拖垮从库

下面用时间线还原事故全过程（假设主库 TPS ≈ 500）：

主库时间线                         从库时间线
─────────────────────────────────────────────────────
T0  BEGIN (10万行 UPDATE 开始)     
T0+8s  COMMIT → Binlog 写入 280MB  
                                         T0+8.1s  IO 线程拉取 280MB Binlog
                                         T0+8.5s  Relay Log 写入完毕
T0+8.5s~T0+55s                          
  主库持续提交 200+ 个小事务                  
  (支付、库存扣减、用户注册等)              
                                                  T0+8.5s  SQL 线程开始回放 10万行
                                                  T0+8.5s~T0+55.5s  逐行回放，单线程
                                                  T0+55.5s  10万行更新完成
                                                  → 此时 Relay Log 已积压 200+ 个事务
                                                  → 继续排队回放
                                                  T0+55.5s~T0+300s  逐行回放积压事务
                                                  → 延迟峰值 5 分钟
T0+300s  
  业务告警开始：读取订单状态为旧值
  → 支付回调认为订单未支付 → 重复通知
  → 库存扣减读到旧库存量 → 超卖风险

关键节点：从 T0+8.5s 到 T0+55.5s 这 47 秒内，主库上正常提交了 200+ 个事务（订单支付、库存扣减、用户注册），它们的 Binlog Event 堆积在 Relay Log 中，等待前面的 10 万行更新执行完。主从延迟从 0 秒跳到 47 秒，随后继续增长到 5 分钟，因为后续的小事务在单线程排队。

大事务为什么在从库上更慢

很多人不理解：主库上 8 秒执行完的 UPDATE，为什么从库上需要 47 秒？

原因在于执行路径的差异：

对比维度	主库	从库
执行方式	走 create_time 索引定位 10 万行，逐行更新	逐行回放 Binlog Event（Row 格式）
锁竞争	行锁，但单个事务无竞争	无锁竞争（数据已提交），但每行都要写 undo log
IO 模式	聚集索引 + 二级索引更新，随机 IO	同主库，但少了 MVCC 判断
并行度	影响的只有这一个事务	后续所有事务都在排队
耗时	8 秒（含 SQL 解析、索引查找、行锁等）	47 秒（纯粹是逐行数据变更的物理时间）

主库的 8 秒里包含了 SQL 解析、索引查找、行锁等开销；从库的 47 秒纯粹是逐行数据变更的物理时间。更关键的是，从库 SQL 线程是单线程（即使开启并行复制，一个大事务内部也无法并行），而主库可以有多个事务并发写入。

Row 格式的放大效应：Row-Based Binlog 下，每行变更记录 before 和 after 两个镜像。以一个 10 字段的行记录为例，before 约 200 字节，after 约 200 字节，加上行头信息，10 万行 ≈ 40MB 元数据 + 实际数据 ≈ 280MB。如果用的是 STATEMENT 格式，Binlog 只有一条 SQL 语句，但 Statement 格式不安全（非确定性函数、LIMIT 等），生产环境几乎都用 Row 格式。

生产环境中真实的大事务来源

来源 1：批量定时任务（最常见）

-- 凌晨批量更新订单状态 —— 真实事故
UPDATE order SET status = 2, update_time = NOW()
WHERE create_time < '2025-10-01' AND status = 1;
-- 影响行数: 103,847 行

这条 SQL 在主库上执行耗时 8 秒（走 create_time 索引，回表更新 10 万行）。看似正常，但事故从 COMMIT 那个瞬间才开始。

来源 2：循环中逐条执行未提交

// 反面教材 —— 真实踩坑
Connection conn = dataSource.getConnection();
conn.setAutoCommit(false);
for (Order order : orderList) {  // orderList 有 5 万条
    ps.executeUpdate("UPDATE order SET ... WHERE id = ?", order.getId());
    // 忘记调 conn.commit()
}
// 循环结束才 COMMIT —— 5 万条更新在一个事务里
conn.commit();

这种写法比单条大 SQL 更危险：因为循环中每条 SQL 都持有了行锁，主库上其他事务对这个订单表的所有写操作都会被阻塞。

来源 3：DDL 操作

-- 直接 ALTER 大表（10GB 级别）
ALTER TABLE huge_table ADD COLUMN new_col INT NOT NULL DEFAULT 0;

在 MySQL 5.6 之前，ALTER TABLE 会锁全表且不开在线 DDL。即使 5.6+ 有了 ALGORITHM=INPLACE，对大表的 DDL 操作也会产生大量 Binlog，导致主从延迟。

如何快速定位大事务

生产中如果你怀疑大事务导致延迟，用以下命令快速验证：

-- 1. 查看当前正在执行的事务，关注 TIME 列
SHOW FULL PROCESSLIST;

-- 2. 从库上查看延迟状态
SHOW SLAVE STATUS\G
-- 重点关注:
--   Seconds_Behind_Master     -> 延迟秒数
--   Relay_Log_Space           -> relay log 积压大小（MB 级别才危险）
--   Exec_Master_Log_Pos       -> 当前执行到的 binlog 位置

-- 3. 查看 InnoDB 事务详情，找未提交的长事务
SELECT * FROM information_schema.INNODB_TRX
WHERE TIME_TO_SEC(TIMEDIFF(NOW(), trx_started)) > 30;

-- 4. 通过 Binlog 定位大事务位置
-- 先找到当前从库执行到的 binlog
SHOW SLAVE STATUS\G
-- 得到 Relay_Master_Log_File + Exec_Master_Log_Pos
-- 然后在主库上分析该 binlog 中事务的大小
SHOW BINLOG EVENTS IN 'mysql-bin.000123' FROM 456789 LIMIT 10;

此外，还可以通过 performance_schema 监控大事务：

-- 查询执行时间超过 5 秒的事务
SELECT THREAD_ID, EVENT_NAME, SQL_TEXT, ROWS_AFFECTED,
       TIMER_WAIT/1000000000 AS wait_ms
FROM performance_schema.events_statements_history
WHERE SQL_TEXT LIKE '%UPDATE%'
  AND ROWS_AFFECTED > 10000
ORDER BY TIMER_WAIT DESC;

监控方案：建立秒级大事务告警

-- 创建定时任务，每 5 秒检查一次长事务
CREATE EVENT check_long_trx
ON SCHEDULE EVERY 5 SECOND
DO
  INSERT INTO alert_long_trx_log
    (trx_id, trx_started, trx_mysql_thread_id, trx_rows_locked, now)
  SELECT trx_id, trx_started, trx_mysql_thread_id,
         trx_rows_locked, NOW()
  FROM information_schema.INNODB_TRX
  WHERE TIME_TO_SEC(TIMEDIFF(NOW(), trx_started)) > 30
    AND trx_mysql_thread_id != CONNECTION_ID();

配合告警平台，大事务执行超过 30 秒就自动触发钉钉/飞书告警，附带 trx_mysql_thread_id，运维可以直接 KILL CONNECTION 杀事务。

批量操作的正确写法

-- 正确的拆批写法
DELIMITER $$
CREATE PROCEDURE batch_update_order()
BEGIN
  DECLARE done INT DEFAULT FALSE;
  DECLARE affected_rows INT DEFAULT 0;
  REPEAT
    START TRANSACTION;
    UPDATE order SET status = 2, update_time = NOW()
    WHERE create_time < '2025-10-01' AND status = 1
    LIMIT 1000;
    SET affected_rows = ROW_COUNT();
    COMMIT;
    -- 每批休眠 100ms，给主库喘气时间
    DO SLEEP(0.1);
  UNTIL affected_rows = 0 END REPEAT;
END$$
DELIMITER ;

对应 Java 代码：

// 正确做法：分批提交 + 休眠
int batchSize = 1000;
int offset = 0;
while (true) {
    int updated = jdbcTemplate.update(
        "UPDATE order SET status = 2, update_time = NOW() " +
        "WHERE create_time < ? AND status = 1 LIMIT ?",
        cutoffDate, batchSize);
    if (updated == 0) break;
    offset += updated;
    Thread.sleep(100); // 每批 100ms 间隔
}

为什么是 1000 行一批 + 100ms 休眠？

• 1000 行更新 ≈ 2.8MB Binlog，从库回放约 0.5~1 秒
• 100ms 间隔 ≈ 主库 TPS 可以恢复到正常水平，不会持续压满
• 总耗时 ≈ 10 万 / 1000 × (0.1s + 0.5s) ≈ 60 秒，远小于 5 分钟延迟

面试官追问：并行复制能否解决大事务问题？

追问：并行复制（slave_parallel_workers + binlog_transaction_dependency_tracking=WRITESET）能否解决大事务问题？

答案：不能。

原因：

1. 并行复制只对不同数据库/不同事务组的事务有效。一个大事务内部的 Binlog Event 属于同一个 last_committed 组，无法被拆分到多个 Worker 线程并行回放。
2. 即使 binlog_transaction_dependency_tracking=WRITESET，大事务的 WRITESET 会包含大量行，无法与其他事务并行。
3. 并行复制开启后，Seconds_Behind_Master 的计算方式会变化，延迟可能看起来变小了，但大事务的拖累依然存在。

换一个角度问：从库上能不能跳过这个大事务？技术上可以设置 sql_slave_skip_counter 或 slave_skip_errors，但这么干意味着数据不一致，生产环境严禁使用。

总结

大事务导致主从延迟的根本原因在于：MySQL 从库 SQL 线程单线程回放的特性，使得一个事务的数据量越大，对后续事务的阻塞时间就越长。

生产环境的三道防线：

• 预防：所有批量操作必须拆成小事务（每次 UPDATE ... LIMIT 1000，COMMIT 后 SLEEP(0.1)），监控 information_schema.INNODB_TRX 中超过 30秒 的事务
• 检测：Seconds_Behind_Master 设置 10 秒告警阈值，配合 Relay_Log_Space 看积压量
• 兜底：延迟敏感业务强制读主库，或从库用 Canal 同步到 Redis/ES 做异构数据源

一个灵魂拷问：如果你的业务必须执行 10 万行更新怎么办？三条路——拆批、异步（先标记再逐步处理）、或者接受延迟并让读关键数据强制走主库。没有银弹。

参考：MySQL 官方文档 - Replication Implementation；《高性能 MySQL》第 10 章复制