MySQL 主从延迟问题全面梳理

前言

主从延迟是 MySQL 高可用架构中最高频的问题之一。很多人对延迟的理解停留在"大事务导致延迟"这个层面，遇到问题时无从下手。本文从复制链路的两个阶段出发——IO 线程和 SQL 线程——分别梳理常见延迟原因，并结合真实案例深入分析原理。

一、主从复制链路简述

理解延迟，先要清楚复制的完整链路：

主库写入事务
    ↓
主库 Dump 线程：读取 binlog 发送给从库
    ↓  （网络传输）
从库 IO 线程：接收 binlog，写入 relay log
    ↓
从库 SQL 线程（或并行 Worker）：读取 relay log，回放事务
    ↓
从库数据与主库一致

延迟分两类：

IO 延迟：从库 IO 线程落后于主库，relay log 还没接收完整
SQL 延迟：relay log 已收到，但 SQL 线程回放跟不上

用 SHOW SLAVE STATUS\G 区分：

1
2
3
4
5
6
7
8
SHOW SLAVE STATUS\G

-- 判断是 IO 延迟还是 SQL 延迟：
-- Master_Log_File / Read_Master_Log_Pos       → IO 线程已读到的位置
-- Relay_Master_Log_File / Exec_Master_Log_Pos → SQL 线程已执行的位置

-- 如果 Read_Master_Log_Pos 远落后于主库最新位置 → IO 延迟
-- 如果 Read_Master_Log_Pos 接近主库，但 Exec_Master_Log_Pos 落后 → SQL 延迟

二、IO 线程延迟的常见原因

原因 1：网络带宽不足

原理：

主库的 Dump 线程将 binlog 通过 TCP 推送给从库 IO 线程。如果主库写入量大，binlog 生成速率超过网络传输速率，从库就会持续落后。

排查方法：

1
2
3
4
5
6
7
8
9
# 在主库上查看 binlog 生成速率
mysqladmin -uroot -p extended-status | grep Binlog_bytes_written

# 测试主从之间实际带宽
iperf3 -s                      # 从库执行（服务端）
iperf3 -c <主库IP> -t 30      # 主库执行（客户端）

# 查看当前网络流量
sar -n DEV 1 5

案例场景：

某公司主从之间走公网专线，带宽 100Mbps。某天主库执行了一批图片数据迁移，binlog 瞬间增长到 150Mbps，从库 IO 线程开始积压，Seconds_Behind_Master 快速攀升到 600 秒。

解决方案：

1
2
3
4
5
# 开启 binlog 压缩传输（MySQL 8.0.20+）
binlog_transaction_compression = ON

# 旧版本使用压缩协议（从库配置）
slave_compressed_protocol = ON

压缩对 CPU 有轻微消耗，但可将 binlog 传输量降低 30%~70%，对文本类数据效果显著。

原因 2：主库 Dump 线程压力大

主库的每个从库对应一个 Dump 线程。从库数量多时，主库磁盘读压力增大，每个从库获取的 binlog 速度都会下降。

排查方法：

1
2
3
4
5
6
-- 查看主库当前有多少 Dump 线程
SHOW PROCESSLIST;
-- 筛选 Command = 'Binlog Dump' 或 'Binlog Dump GTID' 的行

-- 查看主库磁盘 IO 情况（在主库 OS 上执行）
iostat -x 1 5

解决方案： 使用级联复制（主 → 中继从 → 多个从），减少主库 Dump 线程数量。

三、SQL 线程延迟的常见原因

SQL 线程延迟是最常见的延迟类型，原因也更复杂。

原因 1：大事务 + ROW 格式 + 无索引 → 全表扫描地狱

这是最典型、最容易被忽视的延迟场景，值得深入分析。

案例：主库 1000 万条数据无主键无索引，ROW 模式删除 100 万行

场景还原：

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
-- 主库上有这样一张表
CREATE TABLE user_log (
    uid      INT,
    action   VARCHAR(50),
    log_time DATETIME
) ENGINE=InnoDB;
-- 注意：没有主键，没有任何索引，共 1000 万行

-- 主库执行删除操作
DELETE FROM user_log WHERE uid = 1001;
-- 假设符合条件的有 100 万行

ROW 模式下从库回放的原理：

ROW 格式 binlog 记录的是每一行的变更。主库删除 100 万行，binlog 里就有 100 万条 delete event，每条记录了该行的完整前镜像（所有字段的值）。

从库 SQL 线程回放时，针对每一条 delete event，需要找到对应的行并删除。InnoDB 通过以下优先顺序来定位行：

主键
唯一索引
如果都没有 → 全表扫描

没有主键、没有索引，从库每删除 1 行，就要对 1000 万行进行一次全表扫描！

延迟估算：

100 万次全表扫描 × 每次扫描耗时

假设 1000 万行扫描一次需要 0.1 秒（内存充足时）：
100 万 × 0.1 秒 = 10 万秒 ← 约 27 小时的延迟

即使有缓存加速，实际延迟也会是数小时级别

验证方法：

1
2
3
4
5
-- 在从库上查看 SQL 线程正在执行什么
SHOW PROCESSLIST;

-- 查看当前正在回放的事件
SHOW RELAYLOG EVENTS IN 'relay-bin.000012' LIMIT 10;

临时缓解（已经在延迟中）：

1
2
3
-- 开启哈希扫描，对无索引大表回放有明显加速效果
SET GLOBAL slave_rows_search_algorithms = 'INDEX_SCAN,HASH_SCAN';
-- 哈希扫描会将一批 event 先构建哈希表，批量匹配，避免逐行全表扫描

根本解决：

1
2
3
4
5
-- 任何业务表都必须有主键
ALTER TABLE user_log ADD COLUMN id BIGINT AUTO_INCREMENT PRIMARY KEY FIRST;

-- 或者在 WHERE 条件字段上建索引
ALTER TABLE user_log ADD INDEX idx_uid (uid);

防范参数：

1
2
# 主库配置：要求所有表必须有主键，否则拒绝建表
sql_require_primary_key = ON   # MySQL 8.0.13+

原因 2：从库未提交事务 + 主库 DDL → MDL 锁等待

场景还原：

1
2
3
4
5
6
7
-- 从库上，某个业务连接正在执行（未提交）
BEGIN;
SELECT * FROM orders WHERE status = 0 FOR UPDATE;
-- 事务未提交，持有 orders 表的 MDL 读锁

-- 此时，主库执行了 DDL：
ALTER TABLE orders ADD COLUMN remark VARCHAR(200);

从库回放 DDL 的原理：

从库 SQL 线程回放 DDL 时，需要获取该表的 MDL 写锁（Metadata Lock）。

而从库上那个未提交的 SELECT ... FOR UPDATE 事务，在整个事务期间持有 MDL 读锁。

锁冲突：

SQL 线程需要：MDL 写锁（互斥锁）
    ↑ 等待
未提交事务持有：MDL 读锁

SQL 线程被阻塞，relay log 积压，Seconds_Behind_Master 持续增长，直到从库上的未提交事务提交或回滚，MDL 读锁释放，SQL 线程才能继续回放。

排查方法：

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
-- 查看 MDL 锁等待（MySQL 5.7+）
SELECT * FROM performance_schema.metadata_locks
WHERE LOCK_STATUS = 'PENDING';

-- 查看持有 MDL 锁的线程
SELECT * FROM performance_schema.metadata_locks
WHERE LOCK_STATUS = 'GRANTED' AND OBJECT_NAME = 'orders';

-- 找到阻塞的事务
SELECT * FROM information_schema.INNODB_TRX;

解决方案：

1
2
3
4
5
6
7
8
9
-- 找到阻塞线程，将其 kill
KILL <thread_id>;

-- 设置从库事务超时，防止长事务长期持有 MDL 锁
SET GLOBAL interactive_timeout = 300;
SET GLOBAL wait_timeout = 300;

-- 设置 DDL 等待锁超时（超时后 DDL 报错，但 SQL 线程不会卡死）
SET GLOBAL lock_wait_timeout = 30;

根本预防：

从库应设置 super_read_only = ON，禁止业务连接开启写事务
主库执行 DDL 前，先检查是否有长事务：SELECT * FROM information_schema.INNODB_TRX
使用 pt-online-schema-change 代替直接 DDL

原因 3：单线程回放 vs 主库并发写入

原理：

主库是多线程并发写入，N 个线程同时提交事务。而默认情况下，从库 SQL 线程是单线程串行回放。

主库：线程1、线程2、线程3 并发提交 → binlog 串行记录
从库：单个 SQL 线程串行回放

主库并发优势在从库完全消失，写入压力越大，延迟越明显。

解决方案：并行复制（Parallel Replication）

MySQL 5.7 引入基于 LOGICAL_CLOCK 的并行复制。核心思想：在主库上同一组提交（Group Commit）内的事务，没有锁冲突，可以在从库并行回放。

如何确定合适的 Worker 数量？

通过分析 binlog 中的 last_committed 字段来判断主库组提交的批次大小：

1
2
3
4
5
6
7
8
9
# 解析 binlog，查看组提交分布
mysqlbinlog --verbose /var/lib/mysql/binlog.000023 | grep last_committed | head -50

# 输出示例：
# last_committed=0  sequence_number=1
# last_committed=0  sequence_number=2
# last_committed=0  sequence_number=3   ← 同一组（last_committed=0），3 个事务可并行
# last_committed=3  sequence_number=4
# last_committed=3  sequence_number=5   ← 同一组（last_committed=3），2 个事务可并行

last_committed 相同的事务属于同一个 Group Commit，可以并行回放。

统计每组平均事务数：

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
# 统计每个 last_committed 值对应多少个事务
mysqlbinlog --verbose /var/lib/mysql/binlog.000023 \
  | grep "last_committed" \
  | awk '{for(i=1;i<=NF;i++) if($i~/last_committed=/) print $i}' \
  | sort | uniq -c | sort -rn | head -20

# 输出示例（第一列是该组事务数，第二列是 last_committed 值）：
#  8 last_committed=5     ← 这一组有 8 个事务可并行
#  6 last_committed=12
#  5 last_committed=3
# 平均每组约 6 个事务

根据分析结果设置 Worker 数（通常设置为平均组大小的 1~2 倍）：

1
2
3
4
5
6
-- 如果平均每组有 6 个事务，Worker 设置为 8
SET GLOBAL slave_parallel_type = 'LOGICAL_CLOCK';
SET GLOBAL slave_parallel_workers = 8;

-- 保证提交顺序与主库一致（避免数据不一致问题）
SET GLOBAL slave_preserve_commit_order = ON;

主库端：优化 Group Commit 批次大小

1
2
3
4
5
6
7
8
# my.cnf 主库配置
# 延迟 1ms 等待更多事务进入同一组（单位：微秒）
binlog_group_commit_sync_delay = 1000

# 或者达到 10 个事务就立即提交，不再等待
binlog_group_commit_sync_no_delay_count = 10

# 注意：sync_delay 会轻微增加主库事务响应时间，需根据业务延迟容忍度调整

验证 Group Commit 效果：

1
2
3
4
5
6
7
-- 主库查看 Group Commit 统计
SHOW GLOBAL STATUS LIKE 'Binlog_commits';
SHOW GLOBAL STATUS LIKE 'Binlog_group_commits';

-- 平均每组事务数 = Binlog_commits / Binlog_group_commits
-- 接近 1 → Group Commit 效果差，大部分事务单独提交，并行复制收益有限
-- 达到 5~10 → Group Commit 效果好，并行复制能充分发挥

原因 4：从库慢查询占用资源

从库 SQL 线程和业务查询共享 CPU、内存、IO 资源。如果从库上有大量慢查询（报表、全表扫描等），会导致 SQL 线程资源不足。

排查：

1
2
3
4
5
6
7
8
9
-- 查看从库当前执行的慢查询
SELECT * FROM information_schema.PROCESSLIST
WHERE TIME > 30 AND COMMAND != 'Sleep'
ORDER BY TIME DESC;

-- 查看 InnoDB buffer pool 命中率
SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool_read%';
-- Innodb_buffer_pool_reads（物理读）/ Innodb_buffer_pool_read_requests（总读请求）
-- 命中率 < 95% 说明有大量扫描操作破坏了缓存

解决方案：

1
2
3
4
5
6
7
8
-- 从库设置只读，禁止业务写操作干扰
SET GLOBAL read_only = ON;
SET GLOBAL super_read_only = ON;

-- 限制单条查询最大执行时间（MySQL 5.7.8+）
SET GLOBAL max_execution_time = 30000;   -- 30 秒

-- 大型报表查询路由到专用报表从库，不和复制混用

四、Seconds_Behind_Master 的局限性

Seconds_Behind_Master 不是真正的延迟时间，它的计算方式是：

当前时间 - relay log 中正在执行事务的 binlog 时间戳

它会失真的场景：

网络中断后恢复：IO 线程重连后快速拉取大量 relay log，SQL 线程回放时事务时间戳是历史值，Seconds_Behind_Master 显示很大（如 3600），但 relay log 其实已经追上了
主从时钟不同步：服务器时间差会导致该值持续偏大或偏小
IO 线程停止期间：值为 NULL，重启后可能瞬间显示为 0

更准确的方式（GTID 模式）：

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
-- 主库查看已执行的 GTID 范围
SELECT @@GLOBAL.GTID_EXECUTED;
-- 输出示例：abc:1-10000

-- 从库查看已执行的 GTID 范围
SELECT @@GLOBAL.GTID_EXECUTED;
-- 输出示例：abc:1-9500

-- 差距：abc:9501-10000，共 500 个事务未回放
-- 结合每秒回放 TPS，估算真实延迟

或者使用 pt-heartbeat 工具进行精准心跳检测：

1
2
3
4
5
# 主库写入心跳
pt-heartbeat --update --host=主库IP --user=root --password=xxx --daemonize

# 从库监控延迟
pt-heartbeat --monitor --host=从库IP --user=root --password=xxx

五、延迟排查决策流程

发现延迟（Seconds_Behind_Master > 阈值）
    ↓
区分 IO 延迟 vs SQL 延迟
    ├── Read_Master_Log_Pos 落后主库很多 → IO 延迟
    │       ↓
    │   检查网络带宽 → iperf3 测试
    │   开启压缩传输 / 升级带宽 / 级联复制
    │
    └── Read 接近主库，但 Exec 落后 → SQL 延迟
            ↓
        SHOW PROCESSLIST 查看 SQL 线程状态
            ├── 等待 MDL 锁 → 找到阻塞事务，KILL 之
            ├── 正在执行单条 SQL 很慢 → 检查是否无索引全表扫描
            │       → 临时开启 HASH_SCAN
            │       → 根本上为表添加主键/索引
            ├── SQL 线程空闲但延迟大 → 检查并行复制配置
            │       → 分析 binlog last_committed 分布
            │       → 调整 parallel_workers
            └── 从库 CPU/IO 跑满 → 隔离业务查询到专用从库

六、关键参数速查

参数	说明	推荐值
`slave_parallel_type`	并行复制模式	`LOGICAL_CLOCK`
`slave_parallel_workers`	并行 Worker 数	根据 Group Commit 分析，通常 4~16
`slave_preserve_commit_order`	保证提交顺序	`ON`
`slave_compressed_protocol`	启用压缩传输	`ON`（跨机房/公网）
`slave_rows_search_algorithms`	行查找算法	`INDEX_SCAN,HASH_SCAN`
`binlog_group_commit_sync_delay`	主库 Group Commit 等待（微秒）	100~1000
`binlog_group_commit_sync_no_delay_count`	达到 N 个事务立即提交	10~50
`sql_require_primary_key`	强制表必须有主键	`ON`（MySQL 8.0.13+）
`lock_wait_timeout`	MDL 锁等待超时（秒）	`30`
`super_read_only`	从库禁止所有写入	`ON`

七、总结

主从延迟不是一个单一问题，而是一个症状。同样是"延迟增大"，背后的原因可能完全不同：

延迟类型	典型场景	核心原因
IO 延迟	大批量写入、跨机房复制	带宽不足，binlog 传输慢
SQL 延迟	无索引大表 ROW 模式删除	从库回放变成逐行全表扫描
SQL 延迟	从库有未提交事务时主库执行 DDL	MDL 读写锁互斥，SQL 线程卡死
SQL 延迟	主库高并发写入	从库单线程回放追不上主库并发
SQL 延迟	从库跑大量报表查询	CPU/IO 资源被业务查询抢占

核心原则：先定位是 IO 延迟还是 SQL 延迟，再根据具体原因针对性处理。不要上来就无脑调大 slave_parallel_workers，首先搞清楚瓶颈在哪里。

前言#

一、主从复制链路简述#

二、IO 线程延迟的常见原因#

原因 1：网络带宽不足#

原因 2：主库 Dump 线程压力大#

三、SQL 线程延迟的常见原因#

原因 1：大事务 + ROW 格式 + 无索引 → 全表扫描地狱#

原因 2：从库未提交事务 + 主库 DDL → MDL 锁等待#

原因 3：单线程回放 vs 主库并发写入#

原因 4：从库慢查询占用资源#

四、Seconds_Behind_Master 的局限性#

五、延迟排查决策流程#

六、关键参数速查#

七、总结#

前言

一、主从复制链路简述

二、IO 线程延迟的常见原因

原因 1：网络带宽不足

原因 2：主库 Dump 线程压力大

三、SQL 线程延迟的常见原因

原因 1：大事务 + ROW 格式 + 无索引 → 全表扫描地狱

原因 2：从库未提交事务 + 主库 DDL → MDL 锁等待

原因 3：单线程回放 vs 主库并发写入

原因 4：从库慢查询占用资源

四、Seconds_Behind_Master 的局限性

五、延迟排查决策流程

六、关键参数速查

七、总结