MySQL主从同步中大事务导致的延迟_如何拆分大事务优化同步-mysql教程-PHP中文网

大事务导致从库延迟飙升，因单线程sql thread串行回放row格式长事务；应分批update（limit+游标）、避免子查询、用gtid差值等精准监控延迟。

mysql主从同步中大事务导致的延迟_如何拆分大事务优化同步

大事务为什么让从库延迟飙升

主库一个 UPDATE 改 50 万行，binlog 里就是一条长事务日志；从库只能串行回放——不是它不想快，是 MySQL 的复制线程（SQL Thread）默认单线程重放，卡在这条事务上，后面所有日志都得排队。你看到的 Seconds_Behind_Master 突然跳到几千秒，往往就源于此。

常见错误现象：SHOW SLAVE STATUS 里 Seconds_Behind_Master 持续上涨、Exec_Master_Log_Pos 几乎不动、Slave_SQL_Running_State 停在 executing event；同时主库 SHOW PROCESSLIST 早结束了，但从库 SHOW PROCESSLIST 还卡着一条 Update_rows_log_event 或 Query_log_event。

关键点：不是数据量大就一定慢，而是「单个事务内修改行数多 + 行锁时间长 + binlog 格式为 ROW」三者叠加，最容易触发同步瓶颈。

怎么拆？用 LIMIT + WHERE 分批提交，别信 OFFSET

直接 UPDATE ... LIMIT 1000 是最常用也最稳妥的拆法，但必须配合确定性排序和游标式推进，否则会漏行或重复。

正确姿势：

先加索引：确保 WHERE 条件字段有高效索引（比如 status = 'pending'，且 status 上有索引）
用自增主键或时间戳做游标：比如 WHERE id > 100000 AND status = 'pending' ORDER BY id LIMIT 1000，每次取完记录最大 id，下一轮从它开始
每次执行后显式 COMMIT，确保每个分片是独立事务
绝对不用 LIMIT 1000 OFFSET 10000：OFFSET 越大越慢，且并发时可能跳过或重复

示例片段（伪代码）：

MySQL

编写正确的MySQL查询，避免字符集、索引和锁方面的常见陷阱。

下载

SET @last_id = 0;
WHILE (SELECT COUNT(*) FROM orders WHERE id > @last_id AND status = 'pending') > 0 DO
  UPDATE orders SET status = 'processed' 
  WHERE id > @last_id AND status = 'pending' 
  ORDER BY id LIMIT 1000;
  SELECT @last_id := MAX(id) FROM orders WHERE id > @last_id AND status = 'processed' LIMIT 1;
  COMMIT;
END WHILE;

ROW 格式下避免全表 UPDATE，尤其带子查询

ROW 格式 binlog 会记录每一行变更前后的镜像，如果 UPDATE t1 SET a=(SELECT b FROM t2 WHERE t2.id=t1.id) 扫了 10 万行，binlog 就写 10 万条 Update_rows_log_event，体积暴涨，网络传输+解析都变慢。

更糟的是：这类语句在从库回放时，子查询还要重新执行一遍，如果 t2 没走索引，等于在从库又做一次全表扫描。

实操建议：

把子查询提前物化成临时表，再 JOIN 更新：CREATE TEMPORARY TABLE tmp_map AS SELECT id, b FROM t2; ALTER TABLE tmp_map ADD PRIMARY KEY(id); UPDATE t1 JOIN tmp_map ON t1.id = tmp_map.id SET t1.a = tmp_map.b;
确认 binlog_row_image 是 MINIMAL（5.6+ 默认），避免记录无变更字段
对大表批量更新，优先用 LOAD DATA INFILE 替代 INSERT ... SELECT，后者在 ROW 模式下也会生成大量事件