MySQL主从复制延迟原因是什么

共计 2500 个字符，预计需要花费 7 分钟才能阅读完成。

本篇内容主要讲解“MySQL 主从复制延迟原因是什么”，感兴趣的朋友不妨来看看。本文介绍的方法操作简单快捷，实用性强。下面就让丸趣 TV 小编来带大家学习“MySQL 主从复制延迟原因是什么”吧!

在异步或半同步的复制结构中，从库出现延迟是一件十分正常的事。
虽出现延迟正常，但是否需要关注，则一般是由业务来评估。
如：从库上有需要较高一致性的读业务，并且要求延迟小于某个值，那么则需要关注。

首先，简单概述一下复制逻辑：
1、主库将对数据库实例的变更记录到 binlog 中。 
2、主库会有 binlog dump 线程实时监测 binlog 的变更并将这些新的 events 推给从库（Master has sent all binlog to slave; waiting for more updates）
3、从库的 IO Thread 接收这些 events，并将其记录入 relaylog。
4、从库的 SQL Thread 读取 relaylog 的 events，并将这些 events 应用（或称为重放）到从库实例。

上述为默认的异步复制逻辑，半同步复制又有些许不同，此处不再赘述。

此外，判断从库有延迟是十分简单的一件事：
在从库上通过 SHOW SLAVE STATUS
检查 Seconds_Behind_Master 值即可。

产生延迟的原因及处理思路
〇 主库 DML 请求频繁（tps 较大）

即主库写请求较多，有大量 insert、delete、update 并发操作，短时间产生了大量的 binlog。

【原因分析】
主库并发写入数据，而从库 SQL Thread 为单线程应用日志，很容易造成 relaylog 堆积，产生延迟。

【解决思路】
做 sharding，通过 scale out 打散写请求。或考虑升级到 MySQL 5.7+，开启基于逻辑时钟的并行复制。

〇 主库执行大事务

比如大量导入数据，INSERT INTO $tb1 SELECT * FROM $tb2、LOAD DATA INFILE 等
比如 UPDATE、DELETE 了全表等
Exec_Master_Log_Pos 一直未变，Slave_SQL_Running_State 为 Reading event from the relay log
分析主库 binlog，看主库当前执行的事务也可知晓。

【原因分析】
假如主库花费 200s 更新了一张大表，在主从库配置相近的情况下，从库也需要花几乎同样的时间更新这张大表，此时从库延迟开始堆积，后续的 events 无法更新。

【解决思路】
拆分大事务，及时提交。

〇 主库对大表执行 DDL 语句

现象和主库执行大事务相近。
检查 Exec_Master_Log_Pos 一直未动，也有可能是在执行 DDL。
分析主库 binlog，看主库当前执行的事务也可知晓。

【原因分析】
1、DDL 未开始，被阻塞，SHOW SLAVE STATUS 检查到 Slave_SQL_Running_State 为 waiting for table metadata lock，且 Exec_Master_Log_Pos 不变。
2、DDL 正在执行，SQL Thread 单线程应用导致延迟增加。Slave_SQL_Running_State 为 altering table，Exec_Master_Log_Pos 不变

【解决思路】
通过 processlist 或 information_schema.innodb_trx 来找到阻塞 DDL 语句的查询，干掉该查询，让 DDL 正常在从库执行。
DDL 本身造成的延迟难以避免，建议考虑：
① 业务低峰期执行  
② set sql_log_bin= 0 后，分别在主从库上手动执行 DDL（此操作对于某些 DDL 操作会造成数据不一致，请务必严格测试）

〇 主库与从库配置不一致：

【原因分析】
硬件上：主库实例服务器使用 SSD，而从库实例服务器使用普通 SAS 盘、cpu 主频不一致等
配置上：如 RAID 卡写策略不一致，OS 内核参数设置不一致，MySQL 落盘策略不一致等

【解决思路】
尽量统一 DB 机器的配置（包括硬件及选项参数）
甚至对于某些 OLAP 业务，从库实例硬件配置高于主库等

〇 表缺乏主键或唯一索引

binlog_format=row 的情况下，如果表缺乏主键或唯一索引，在 UPDATE、DELETE 的时候可能会造成从库延迟骤增。
此时 Slave_SQL_Running_State 为 Reading event from the relay log。
并且 SHOW OPEN TABLES WHERE in_use= 1 的表一直存在。
Exec_Master_Log_Pos 不变。
mysqld 进程的 cpu 几近 100%（无读业务时），io 压力不大

【原因分析】
做个极端情况下的假设，主库更新一张 500w 表中的 20w 行数据，该 update 语句需要全表扫描
而 row 格式下，记录到 binlog 的为 20w 次 update 操作，此时 SQL Thread 重放将特别慢，每一次 update 可能需要进行一次全表扫描

【解决思路】
检查表结构，保证每个表都有显式自增主键，并建立合适索引。

〇 从库自身压力过大

【原因分析】
从库执行大量 select 请求，或业务大部分 select 请求被路由到从库实例上，甚至大量 OLAP 业务，或者从库正在备份等。
此时可能造成 cpu 负载过高，io 利用率过高等，导致 SQL Thread 应用过慢。

【解决思路】
建立更多从库，打散读请求，降低现有从库实例的压力。

〇 MyISAM 存储引擎

此时从库 Slave_SQL_Running_State 为 Waiting for table level lock

【原因分析】
MyISAM 只支持表级锁，并且读写不可并发操作。
主库在设置 @@concurrent_insert 对应值的情况下，能并发在 select 时执行 insert，但从库 SQL Thread 重放时并不可并发，有兴趣可以再去看看 myisam 这块的实现。

【解决思路】
当然是选择原谅它了，既然选择了 MyISAM，那么也应该要有心理准备。（还存在其他场景，也不推荐 MyISAM 在复制结构中使用）
改成 InnoDB 吧。

到此，相信大家对“MySQL 主从复制延迟原因是什么”有了更深的了解，不妨来实际操作一番吧！这里是丸趣 TV 网站，更多相关内容可以进入相关频道进行查询，关注我们，继续学习！