MySQL 大事务导致主从延迟飙升怎么拆分优化？

Question

仓乃文 · Accepted Answer

MySQL 主从复制中，从库的 SQL 线程必须完整执行一个事务后才更新复制进度。大事务（如批量 INSERT INTO SELECT、大范围 UPDATE）会让从库长时间卡在一个事务上，期间主库产生的所有 binlog 都在排队等待，延迟瞬间飙升。更隐蔽的是 Seconds_Behind_Master 在事务执行期间可能显示 0，监控完全失灵。问题场景： ```sql -- 典型大事务：一次性迁移/更新百万行 BEGIN; INSERT INTO target SELECT * FROM source WHERE create_time < "2025-01-01"; -- 500 万行，执行 30 秒，从库延迟 30 秒+ COMMIT; -- 大范围 UPDATE UPDATE orders SET status = 2 WHERE status = 1; -- 锁住大量行，从库回放慢 ``` 拆分优化方案： ```sql -- 方案 1：按主键分段拆分（推荐） -- 每批 5000-10000 行，独立事务提交 -- 伪代码： SET @offset = 0; SET @batch = 10000; WHILE @offset < max_id DO BEGIN; INSERT INTO target SELECT * FROM source WHERE id BETWEEN @offset AND @offset + @batch - 1; COMMIT; SET @offset = @offset + @batch; -- 可选：SLEEP(0.1) 降低从库瞬时压力 END WHILE; -- 方案 2：分批 UPDATE/DELETE UPDATE orders SET status = 2 WHERE status = 1 ORDER BY id LIMIT 5000; -- 循环执行直到 ROW_COUNT() = 0 -- 方案 3：开启并行复制（MySQL 5.7+） SET GLOBAL binlog_transaction_dependency_tracking = WRITESET; SET GLOBAL transaction_write_set_extraction = XXHASH64; SET GLOBAL slave_parallel_type = LOGICAL_CLOCK; SET GLOBAL slave_parallel_workers = 8; ``` 拆分避坑： ```sql -- 不要用 OFFSET 分页：LIMIT 10000, 5000 越往后越慢 -- 用主键范围：WHERE id BETWEEN 10000 AND 14999 -- 按时间字段分段注意边界： -- WHERE create_time >= "2025-01-01" AND create_time < "2025-02-01" -- 不要用 > 和 <= 混用，可能漏或重 -- 拆分后校验一致性： SELECT COUNT(*), SUM(id) FROM target; ``` 补充说明： - Seconds_Behind_Master 在大事务执行期间可能为 0，不可信 - 用 pt-heartbeat 工具测量真实延迟 - 规则：单个事务执行时间控制在 2 秒以内，批量操作必须拆分