从库数据慢了几分钟但 Seconds_Behind_Master 是 0：MySQL 主从延迟的三层指标与六个根因排查

  1.  Home
  2. Text  
    3. Share to：

Seconds_Behind_Master 是 0，从库数据却慢了几分钟

先说一个很多人踩过的坑：MySQL 主从复制延迟，你看 SHOW SLAVE STATUS 里的 Seconds_Behind_Master（下称 SBM），它显示 0，但业务那边报上来的现象是"从库查到的订单状态比主库旧了好几分钟"。这时候最危险的反应是："SBM 都 0 了，肯定不是延迟问题"，然后转头去查别的方向，白白浪费几个小时。

本文只做一件事：讲清楚 SBM 这个指标为什么会骗人、真正的延迟该用什么指标看、以及怎么从 SQL 线程、relay log、并行复制三条线索里定位到根因。全文基于 MySQL 5.7 与 8.0（两者在此问题上机制一致，8.0 多了 performance_schema.replication_applier_status_by_worker 这个更细的视角）。

一、SBM 到底在算什么东西

MySQL 文档对 SBM 的定义是：从库当前 SQL 线程正在执行的这个 event 的时间戳，与从库当前系统时间的差值。注意两个关键词：当前正在执行和从库系统时间。

换句话说，SBM 度量的是"SQL 线程手上这个 binlog event 有多老"，而不是"从库落后主库多少数据"。这个定义带来了三个必然的失真场景：

1. SQL 线程空闲时，SBM 直接归零

如果主库写入很稀疏，从库 SQL 线程大部分时间在等新 event（Slave_SQL_Running_State 显示 Waiting for master to send event 或 Slave has read all relay log），此时没有任何 event 在手，MySQL 就把 SBM 报成 0 或 NULL。

这意味着：延迟是"尖峰型"的应用，最容易骗过 SBM。 比如你的业务每天上午 10 点批量导入一次数据，导入 5 万行花了 40 秒，从库需要 90 秒才追平。在 10:00:45 这一刻去查 SBM，如果这 40 秒内 SQL 线程刚好把 relay log 追平了一个间隙，SBM 可能正显示 0，而实际上从库还差 3 万行没应用。等你去查业务表，数据就是缺的。

顺便说一句：在 MySQL 5.6 里 SQL 线程空闲时 SBM 常显示 NULL，5.7 之后倾向于显示 0，8.0 在多线程复制下更常见 0。三种表现，同一个谎言。

2. 从库系统时钟漂移，SBM 直接错

SBM 是用从库本机时间减去 event 里带的时间戳（该时间戳来自主库）。如果你从库的 NTP 没配好，比主库慢 30 秒，那 SBM 就恒定为 30 秒左右，你会以为一直在延迟；反过来从库快 30 秒，一个真实 60 秒的延迟会被算成 30 秒。

这个坑的排查成本极低但经常被跳过，先做：

# 主库
mysql -e "SELECT NOW(6), @@hostname"
# 从库
mysql -e "SELECT NOW(6), @@hostname"
# 两边对比，差值超过 1 秒就该去查 chrony/NTP 了
chronyc tracking     # System time 那一行就是本机偏差

3. 大事务期间 SBM 会"冻住"

一个 event 如果是一个 2GB 的大事务（比如 DELETE FROM logs WHERE ... 删了 2000 万行），SQL 线程在应用它的整个过程中，SBM 显示的是这个事务开始时间与当前的差值。事务执行 5 分钟，SBM 就一直是 5 分钟往上爬，看起来延迟在涨；等它一提交，SBM 瞬间掉回 0。你看到的曲线是一根锯齿，而不是真实的风险画像。

二、不用 SBM，那用什么

推荐三层指标一起看，互为交叉验证：

第一层：用 GTID 差集算真实落后事务数

这是最直观也是最不容易骗人的方法。在主库和从库分别执行：

-- 主库
SELECT @@global.gtid_executed;

-- 从库
SELECT @@global.gtid_executed;
SELECT @@global.gtid_purged;

-- 从库上直接算差集
SELECT GTID_SUBTRACT(@@global.gtid_executed, '主库那一大串');

把主库的 gtid_executed 填进去，从库这边返回的就是"主库有、从库还没有"的事务集合。返回空字符串 = 真追平了。返回值里有几十个 UUID:区间 = 真的落后。这个指标不受时钟影响、不受 SQL 线程空闲影响，是判断延迟的第一权威。

写成自动化检查脚本（放到从库上跑，用监控定时执行）：

#!/bin/bash
# check_repl_lag.sh  放在从库，需能读到主库 gtid_executed
MASTER_GTID=$(mysql -h10.0.0.1 -uroot -p'xxx' -N -B -e "SELECT @@global.gtid_executed;")
LOCAL_GTID=$(mysql -N -B -e "SELECT @@global.gtid_executed;")
DIFF=$(mysql -N -B -e "SELECT GTID_SUBTRACT('$MASTER_GTID','$LOCAL_GTID');")

if [ -n "$DIFF" ]; then
  # 数一下差集里有多少个区间
  CNT=$(echo "$DIFF" | tr ',' '\n' | grep -c ':')
  echo "LAG: $CNT intervals behind"
  echo "$DIFF" | head -c 500
  exit 1
else
  echo "OK: fully caught up"
  exit 0
fi

注意 GTID 模式下这个脚本才成立。如果是传统 binlog + position 复制，就得改读主从的 SHOW MASTER STATUS 与 SHOW SLAVE STATUS 的 Read_Master_Log_Pos 做对比（只能判断 IO 线程读到哪，不如 GTID 精确）。

第二层：看未应用的 relay log 还剩多少字节

relay log 是 IO 线程从主库拉回来、SQL 线程还没消费的缓冲区。它的堆积量直接反映"从库欠了多少作业"：

-- 从库
SHOW SLAVE STATUS\G
-- 关键字段：
--   Master_Log_File / Read_Master_Log_Pos   IO 线程读到哪
--   Relay_Master_Log_File / Exec_Master_Log_Pos  SQL 线程执行到哪
--   这两组的差值 = 还没应用的数据量

-- 更直接的：看磁盘上 relay log 文件总大小
du -sh /var/lib/mysql/relay-log*

实操中最有用的是"位移差"这个连续指标：(Read_Master_Log_Pos + 已滚过的文件大小) - Exec_Master_Log_Pos。它单调上涨说明 SQL 线程彻底追不上，稳定在某个小值说明只是在消化大事务。

第三层：看每条 SQL 实际花了多久

MySQL 5.7.7+ 提供：

SELECT * FROM performance_schema.replication_applier_status_by_worker\G

这里每个 worker 一行，重点看 LAST_APPLIED_TRANSACTION 和两个耗时字段：APPLYING_TRANSACTION_START_APPLY_TIMESTAMP、LAST_APPLIED_TRANSACTION_END_APPLY_TIMESTAMP。8.0 更完善，直接能看出哪个 worker 卡在哪个事务上。如果某个 worker 一行的事务应用了几十秒，那这个事务就是瓶颈。

还有一个更便宜的视角——把慢日志用在从库上：

-- 从库开启慢日志（注意别开 long_query_time=0，从库本身没流量）
SET GLOBAL slow_query_log = ON;
SET GLOBAL long_query_time = 2;
SET GLOBAL log_queries_not_using_indexes = OFF;

-- 然后 tail 慢日志：
tail -f /var/lib/mysql/slow.log

从库的慢查询很有价值：从库没有业务查询干扰，慢日志里出现的每一条几乎都是复制应用语句。如果里面反复出现同一条 UPDATE ... WHERE，说明主库那张表的索引在从库侧不适用（见下文"从库索引缺失"一节）。

三、定位到根因：六个最常见的延迟来源

1. 单线程 SQL 线程 vs 主库高并发写入

传统复制（slave_parallel_workers=0）下，无论主库并发多高，从库只有一条 SQL 线程串行应用 binlog。主库 16 核并行写，从库 1 核串行追——落后是必然的。判断方法很干脆：

SHOW VARIABLES LIKE 'slave_parallel_workers';
SHOW VARIABLES LIKE 'slave_parallel_type';
SHOW VARIABLES LIKE 'slave_preserve_commit_order';

如果 slave_parallel_workers=0，那就别查了，问题基本就在这里。改成并行复制（数字按从库核数给，一般 CPU 核数的一半到等量）：

-- MySQL 5.7 / 8.0，基于 LOGICAL_CLOCK 组提交并行
STOP SLAVE SQL_THREAD;
SET GLOBAL slave_parallel_type = 'LOGICAL_CLOCK';
SET GLOBAL slave_parallel_workers = 8;
SET GLOBAL slave_preserve_commit_order = ON;   -- 8.0 建议开，保证提交顺序
START SLAVE SQL_THREAD;

-- 持久化到配置文件
# my.cnf
# slave_parallel_type = LOGICAL_CLOCK
# slave_parallel_workers = 8
# slave_preserve_commit_order = ON

关键提醒：slave_parallel_type='DATABASE'（5.7 默认）只在跨库写入时才能并行，单库单表高并发写入完全无效。必须显式切到 LOGICAL_CLOCK，才能按组提交并行。

2. 从库上有大事务（批量 DDL / DELETE）

SQL 线程是单条 event 应用的，一个 30 分钟的 ALTER TABLE 或一次 DELETE 2000 万行，从库就阻塞 30 分钟。判断：

-- 从库上看当前正在执行的语句
SHOW PROCESSLIST;
-- 关注 State 为 "Waiting for table metadata lock" 或正在跑的 UPDATE/DELETE/ALTER

解法是拆批。把一次性大 DELETE 改成循环小批（每批 5000 行、间隔 200ms），把 ALTER TABLE 改成 pt-online-schema-change 或 8.0 的 ALGORITHM=INSTANT（仅部分操作支持）。这部分不是复制问题，是业务写入模式问题，但体现为复制延迟。

3. 从库单条语句执行本身就慢：索引缺失

主库上跑的 UPDATE orders SET status=2 WHERE user_id=123 AND created_at > '2026-01-01'，主库有 idx(user_id)，很快；但有人为了省空间把从库那个索引删了，或者从库建表时漏掉了。结果主库 5ms，从库 5s，延迟就这么来的。

排查手法：对比主从两边的索引

-- 主库
SELECT TABLE_SCHEMA, TABLE_NAME, INDEX_NAME, GROUP_CONCAT(COLUMN_NAME ORDER BY SEQ_IN_INDEX)
FROM information_schema.STATISTICS
WHERE TABLE_SCHEMA='yourdb' GROUP BY 1,2,3;

-- 从库同样执行，然后 diff
# 主库导出
mysql -N -B -e "..." > /tmp/master_idx.txt
# 从库导出
mysql -N -B -e "..." > /tmp/slave_idx.txt
diff /tmp/master_idx.txt /tmp/slave_idx.txt

diff 出来任何一行，都要认真核对。从库索引策略上，除了明显写放大严重的场景，一般建议索引与主库保持一致——省下的那点写开销，远不值一次生产延迟事故。

4. binlog 传输层被拉慢（网络 / 未压缩）

如果 Slave_IO_Running 是 Connecting 或者频繁断开重连，那是 IO 线程的问题，跟 SQL 线程无关。跨公网复制时 binlog 传输是明文（5.7 之前），行格式 binlog 体积大，跨机房带宽打满很正常。

SHOW SLAVE STATUS\G
-- Slave_IO_Running: Yes 才是正常
-- Last_IO_Error / Last_IO_Errno 是排查关键

-- 开启 binlog 传输压缩（5.7+，同时主从都要设）
SET GLOBAL slave_compressed_protocol = ON;
-- 配置文件：slave_compressed_protocol = ON

-- 或者干脆上 SSL 加密复制
# master: require_secure_transport=ON
# slave: CHANGE MASTER TO ... MASTER_SSL=1, MASTER_SSL_CA='/path/ca.pem'

另外确认 binlog 格式：binlog_format=ROW 是复制可靠性的前提，也影响主从一致性；MIXED 会让某些语句在从库表现为非确定性（如 NOW()、RAND() 在 STATEMENT 格式下主从不一致），间接造成"数据看起来延迟/不一致"的假象。

5. relay log 写入的磁盘 I/O 竞争

从库要同时干三件事：IO 线程写 relay log、SQL 线程读 relay log 应用、应用时又写数据文件和 binlog（如果从库也开了 log_bin）。这三路 I/O 挤在同一块盘上，如果还是机械盘或者云盘的 IOPS 打满，SQL 线程就慢。

iostat -x 2
# 看 %util 是否长期接近 100，await 是否远高于 svctm
iotop -o   # 看是哪几个进程在写

-- 从库减少自身 binlog 开销（若从库不需要做下一级主库）
SET GLOBAL log_bin = OFF;   -- 需重启生效，配置文件注释 log_bin
-- 或只记录必要数据
SET GLOBAL binlog_row_image = MINIMAL;   -- 减少行镜像体积

6. relay_log_recovery 与 relay log 数量配置不当

从库崩溃重启后，如果没开 relay_log_recovery=ON，可能会重复应用或从错误位置继续，表现为启动后延迟暴涨。这个参数建议始终开启：

# my.cnf
relay_log_recovery = ON
# 崩溃后从 SQL 线程最新位置重新拉取，避免 relay log 损坏导致的复制中断

四、一个完整的排查决策流程

按这个顺序走，通常 15 分钟内能定位到方向：

  1. 先排除假象：主从 NOW(6) 对时，确认不是时钟问题；再用 GTID_SUBTRACT 确认是否真的落后。返回空就说明业务感觉的"延迟"可能是读到刚写入的主库数据、或者应用缓存导致的，不是复制问题。
  2. 确认真的是数据落后后，看 SHOW SLAVE STATUS 的 Slave_SQL_Running_State：
    • Waiting for dependent transaction to commit → 并行复制 worker 在等依赖，正常
    • Waiting for table metadata lock → 有 DDL 或长事务堵着，去 SHOW PROCESSLIST 找 MDL 持有者
    • 正在执行某个 UPDATE/DELETE/ALTER → 大事务，去看是哪条
  3. 看并行度：slave_parallel_workers 若为 0 且主库写入并发高，直接切 LOGICAL_CLOCK，这是投入产出比最高的一步。
  4. 看单条语句耗时：从库慢日志 + replication_applier_status_by_worker，找出具体慢的应用语句。
  5. 对比索引：主从索引 diff，这是最容易被忽略、也最容易一击命中的一步。
  6. 看 IO：iostat -x / iotop，确认磁盘是否成为瓶颈。

五、把监控补上（避免下次被动）

最后给一个可以直接丢进 Zabbix/Prometheus 的自定义指标采集脚本，输出三个数：GTID 落后区间数、relay log 堆积字节、并行 worker 中最长事务耗时。

#!/bin/bash
# mysql_repl_metrics.sh  从库执行，输出 Prometheus 文本格式
set -uo pipefail

MYSQL="mysql -N -B"
CRED="-uroot -p'xxx'"

# 1) GTID 落后区间数
MASTER_GTID=$($MYSQL $CRED -h10.0.0.1 -e "SELECT @@global.gtid_executed;" 2>/dev/null)
LOCAL_GTID=$($MYSQL $CRED -e "SELECT @@global.gtid_executed;" 2>/dev/null)
DIFF=$($MYSQL $CRED -e "SELECT GTID_SUBTRACT('$MASTER_GTID','$LOCAL_GTID');" 2>/dev/null)

if [ -z "$DIFF" ]; then
  BEHIND=0
else
  BEHIND=$(echo "$DIFF" | tr ',' '\n' | grep -c ':' || true)
fi
echo "mysql_repl_gtid_intervals_behind $BEHIND"

# 2) relay log 堆积字节（Read_Master_Log_Pos - Exec_Master_Log_Pos 分段更准，这里用文件大小近似）
RL_BYTES=$(du -sb /var/lib/mysql/relay-log* 2>/dev/null | awk '{s+=$1} END {print s+0}')
echo "mysql_repl_relay_bytes $RL_BYTES"

# 3) 最慢 worker 事务耗时
$MYSQL $CRED -e "
SELECT MAX(TIMESTAMPDIFF(MICROSECOND, APPLYING_TRANSACTION_START_APPLY_TIMESTAMP, NOW())/1000000)
FROM performance_schema.replication_applier_status_by_worker
WHERE APPLYING_TRANSACTION_START_APPLY_TIMESTAMP IS NOT NULL;" 2>/dev/null \
| awk '{print "mysql_repl_longest_apply_seconds " ($1=="" ? 0 : $1)}'

# 4) 复制线程健康（1=正常，0=异常）
STS=$($MYSQL $CRED -e "SHOW SLAVE STATUS\G" 2>/dev/null)
IO=$(echo "$STS" | grep -c "Slave_IO_Running: Yes")
SQL=$(echo "$STS" | grep -c "Slave_SQL_Running: Yes")
echo "mysql_repl_io_running $IO"
echo "mysql_repl_sql_running $SQL"

把它挂到 crontab，配合告警规则 mysql_repl_gtid_intervals_behind > 100 for 5m，你就能在主从真正脱节的第一时间知道，而不是等业务来投诉。SBM 那个指标，从今天起请当成参考值，不要当成结论。

Last modification：September 27th, 2026 at 07:25 pm
© 允许规范转载

相关文章

Leave a Comment