分别在MySQL5.7和8.0中测试主从复制中主库表缺失主键会导致主从延迟的情况

简介

检查延迟的方法：在从库上通过SHOW SLAVE STATUS检查Seconds_Behind_Master值即可获取主从复制延迟的秒数。

主从复制延迟，可能的原因有主库和从库方面：

　主库写binlog不及时。

　dump线程压力大

　IO线程阻塞

表缺乏主键或唯一索引（常见）

假设主库更新一张500w表中的20w行数据，该update语句仅需要全表扫描1次；而在row格式下，记录到binlog日志中的SQL为20w次update操作，此时SQL Thread重放将特别慢，因为每一次update都需要进行一次全表扫描，即从库需要执行20w次的全表扫描。

　主库DML请求频繁（tps较大）

　主库执行大事务,导致从库SQL慢

　主库对大表执行DDL语句

　主库与从库硬件配置不一致

　从库自身压力过大

　MyISAM存储引擎

　主从复制的服务器时钟是否一致。主从同步延迟与系统时间的关系，查看主从两台机器间系统时间差

　网络通信是否存在延时。主从同步延迟与压力、网络、机器性能的关系，查看从库的IO，cpu，mem及网络压力

　从库查询是否优化（比如存在查询慢，导致从库性能差，处理不过来）

　是否启用了延迟复制，使用“show slave status”查看SQL_Delay是否大于0

今天我们就通过实验的方式来验证第4种情况。

MySQL 5.7 环境准备

MySQL环境初始化

主库配置

从库配置

主从查询

MySQL 5.7实验过程

主库创建表

主库先创建一张8万行的大表：

主库做更新操作

可以看出，主库基本在1s就更新完成，变化的行数为2万行。

从库查询延迟，

可以发现，最长延迟270秒左右，相当于5分钟左右。

分析主库的binlog日志

可以看出，在ROW模式下，在主库上执行了一条UPDATE语句，更新了2万行记录，但是在binlog中，记录了2万行的UPDATE语句。

分析从库的中继日志

可以看出，在从库上也是2万行的UPDATE语句，也是一条一条的进行更新。由于没有主键和索引，所以，就会导致在从库进行2万次的全表扫描，这样也就拖慢了从库APPLY的效率。

尝试添加并行

解决延迟：表添加主键

可以看到，在有主键的情况下，从库基本无延迟。

MySQL 8.0.27环境实验

可见，主库更新2万行数据，从库延迟不超过5秒，但若主库更新6万行，则从库延迟接近20秒。说明，在MySQL 8中，性能有所提升，但仍然需要主键。

总结

1、在MySQL 5.7的主从复制架构中，若存在大表，那么一定要有主键或唯一索引，否则将导致很大的主从延迟。从库即使添加并行复制，也不能改善这种情况。

2、从MySQL 8.0开始的主从复制架构中，若主库大表没有主键，仍然会导致从库的延迟，但是，延迟的现象没有5.7那么严重，所以，我们仍然建议主库的大表一定需要有主键。