解决MySQL主从复制延迟问题，看这篇就够了
解决MySQL主从复制延迟问题，看这篇就够了
MySQL主从复制延迟是一个常见的数据库管理问题，它可能严重影响业务系统的性能和数据一致性。本文将深入探讨主从复制延迟的原因，并提供详细的优化和监控方案，帮助数据库管理员和开发人员解决这一难题。
一、逻辑日志的优缺点
MySQL复制基于的二进制日志是一种逻辑日志，其写入的是每个事务中已变更的每条记录的前项、后项。有了每条记录的变化内容，用户可以方便地通过分析MySQL的二进制日志内容，准时地将MySQL中的数据同步到异构的数据平台，如HBase、ES、Hive等大数据平台。
逻辑日志简单易懂，方便数据之间的同步，但它的缺点是：事务不能太大，否则会导致二进制日志非常大，一个大事务的提交会非常慢。
假设有个DELETE删除操作，删除当月数据，由于数据量可能有1亿条记录，可能会产生100G的二进制日志，则这条SQL在提交时需要等待100G的二进制日志写入磁盘，如果二进制日志磁盘每秒写入速度为100M/秒，至少要等待1000秒才能完成这个事务的提交。
所以在MySQL中，一定要对大事务特别对待，总结起来就是：
- 设计时，把DELETE删除操作转化为DROPTABLE/PARTITION操作；
- 业务设计时，把大事务拆成小事务。
对于第一点（把DELETE删除操作转化为DROPTABLE/PARTITION操作），主要是在设计时把流水或日志类的表按时间分表或者分区，这样在删除时，二进制日志内容就是一条DROPTABLE/PARITION的SQL，写入速度就非常快了。
而第二点（把大事务拆分成小事务）也能控制二进制日志的大小。比如对于前面的DELETE操作，如果设计时没有分表或分区，那么可以进行如下面的小事务拆分：
DELETE FROM ...
WHERE time between ... and ...
LIMIT 1000;
上面的SQL就是把一个大的DELETE操作拆分成了每次删除1000条记录的小操作。而小事务的另一个优势是：可以进行多线程的并发操作，进一步提升删除效率。
MySQL数据库中，大事务除了会导致提交速度变慢，还会导致主从复制延迟。
比如：一个大事务在主服务器上运行了30分钟，那么在从服务器上也需要运行30分钟。在从机回放这个大事务的过程中，主从服务器之间的数据就产生了延迟；产生大事务的另一种可能性是主服务上没有创建索引，导致一个简单的操作时间变得非常长。这样在从机回放时，也会需要很长的时间从而导致主从的复制延迟。
二、主从复制延迟优化
要彻底避免MySQL主从复制延迟，数据库版本至少要升级到5.7，因为之前的MySQL版本从机回放二进制都是单线程的（5.6是基于库级别的单线程）。
MySQL的从机并行复制有两种模式。
- COMMITORDER：主机怎么并行，从机就怎么并行。
- WRITESET：基于每个事务，只要事务更新的记录不冲突，就可以并行。
COMMITORDER模式的从机并行复制，从机完全根据主服务的并行度进行回放。理论上来说，主从延迟极小。但如果主服务器上并行度非常小，事务并不小，比如单线程每次插入1000条记录，则从机单线程回放，也会存在一些复制延迟的情况。
WRITESET模式是基于每个事务并行，如果事务间更新的记录不冲突，就可以并行。还是以“单线程每次插入1000条记录”为例，如果插入的记录没有冲突，比如唯一索引冲突，那么虽然主机是单线程，但从机可以是多线程并行回放！！！
所以在WRITESET模式下，主从复制几乎没有延迟。那么要启用WRITESET复制模式，我们可以对数据库做如下设置：
binlog_transaction_dependency_tracking = WRITESET
transaction_write_set_extraction = XXHASH64
slave-parallel-type = LOGICAL_CLOCK
slave-parallel-workers = 16
三、主从复制延迟监控
1、Seconds_Behind_Master
大家或许知道通过命令SHOWSLAVESTATUS，其中的Seconds_Behind_Master可以查看复制延迟，如：
但是，Seconds_Behind_Master不准确！用于严格判断主从延迟的问题并不合适，有这样三个原因。
- 1、它计算规则是（当前回放二进制时间 - 二进制日志中的时间），如果I/O线程有延迟，那么Second_Behind_Master为0，这时可能已经落后非常多了，例如存在有大事务的情况下；
- 2、对于级联复制，最下游的从服务器延迟是不准确的，因为它只表示和上一级主服务器之间的延迟；
- 3、若主从时区不一样，那么second_behind_master也不准确；
总的来说，线上业务通过Seconds_Begind_Master值观察主从复制延迟并不准确，需要额外引入一张表，才能真正监控主从的复制延迟情况。
2、心跳表
想要实时准确地监控主从复制延迟，可以在主服务器上引入一张心跳表heartbeat，用于定期更新时间（比如每3秒一次）。于主从复制机制，主机上写入的时间会被复制到从机，这时对于主从复制延迟的判断可以根据如下规则：主从延迟 = 从机当前时间 - 表heartbeat中的时间。
这可以很好解决上述Seconds_Behind_Master值存在的问题。表heartbeat和定期更新时间可以根据类似的设计：
USE DBA;
CREATE TABLE heartbeat (
server-uuid VARCHAR(36) PRIMARY KEY,
ts TIMESTAMP(6) NOT NULL
);
REPLACE INTO heartbeat(@@server_uuid, NOW())
上面的设计中，我们创建了DBA库，以及库下的一张表heartbeat，用于记录当前时间。
REPLACE语句用于定期更新当前时间，并存入到表heartbeat，表heartbeat在正常运行情况下只有一条记录。定期执行REPLACE语句可以使用定期的脚本调度程序，也可以使用MySQL自带的事件调度器（event scheduler），如：
CREATE EVENT e_heartbeat
ON SCHEDULE
EVERY 3 SECOND
DO
BEGIN
REPLACE INTO DBA.heartbeat VALUES (@@server_uuid,NOW())
END
3、读写分离设计
读写分离设计是指：把对数据库的读写请求分布到不同的数据库服务器上。对于写入操作只能请求主服务器，而对读取操作则可以将读取请求分布到不同的从服务器上。
这样能有效降低主服务器的负载，提升从服务器资源利用率，从而进一步提升整体业务的性能。下面这张图显示了一种常见的业务读写分离的架构设计：
上图引入了LoadBalance负载均衡的组件，这样Server对于数据库的请求不用关心后面有多少个从机，对于业务来说也就是透明的，只需访问LoadBalance服务器的IP或域名就可以。
通过配置LoadBalance服务，还能将读取请求平均或按照权重平均分布到不同的从服务器。这可以根据架构的需要做灵活的设计。
注意：读写分离设计的前提是从机不能落后主机很多，最好是能准实时数据同步，务必一定要开始并行复制，并确保线上已经将大事务拆成小事务。
当然，若是一些报表类的查询，只要不影响最终结果，业务是能够容忍一些延迟的。但无论如何，一定要在线上数据库环境中做好主从复制延迟的监控。