MySQL 中读写分离数据延迟

• MySQL 中读写分离可能遇到的问题
  • 前言
  • 读写分离的架构
    • 基于客户端实现读写分离
    • 基于中间代理实现读写分离
  • MySQL 中如何保证主从数据一致
    • 循环复制问题
  • 主从同步延迟
  • 主从同步延迟的原因
  • 主从延迟如何处理
    • 强制走主库方案
    • Sleep 方案
    • 判断主从无延迟方案
    • 配合semi-sync
    • 等主库位点方案
    • 等 GTID 方案
  • 总结
  • 参考

MySQL 中读写分离可能遇到的问题

前言

MySQL 中读写分离是经常用到了的架构了，通过读写分离实现横向扩展的能力，写入和更新操作在源服务器上进行，从服务器中进行数据的读取操作，通过增大从服务器的个数，能够极大的增强数据库的读取能力。

MySQL 中的高可用架构越已经呈现出越来越复杂的趋势，但是都是才能够最基本的一主一从演化而来的，所以这里来弄明白主从的基本原理。

首先来弄明白主从和主备,以及双主模式之间的区别

双主

有两个主库，每个主库都能进行读写，并且两个主库之间都能进行数据的同步操作。

主从

主从中，数据写入在主节点中进行，数据读取在从节点中进行，主库会同步数据到从库中。

主备

主备，备库只是用来进行数据备份，没有读写操作的发生，数据的读取和写入发生在主库中，主库会同步数据到备库中。

下面讨论的数据延迟，主要是基于主从模式。主从，主备，双主数据同步原理一样，这里不展开分析了。

读写分离的架构

常用的读写分离有下面两种实现：

1、客户端实现读写分离；

2、基于中间代理层实现读写分离。

基于客户端实现读写分离

客户端主动做负载均衡，根据 select、insert 进行路由分类，读请求发送到读库中，写请求转发到写库中。

这种方式的特点是性能较好，代码中直接实现，不需要额外的硬件支持，架构简单，排查问题更方便。

缺点需要嵌入到代码中，需要开发人员去实现，运维无从干预，大型代码，实现读写分离需要改动的代码比较多。

基于中间代理实现读写分离

中间代理层实现读写分离，在 MySQL 和客户端之间有一个中间代理层 proxy，客户端只连接 proxy， 由 proxy 根据请求类型和上下文决定请求的分发路由。

带 proxy 的架构，对客户端比较友好。客户端不需要关注后端细节，连接维护、后端信息维护等工作，都是由 proxy 完成的。但这样的话，对后端维护团队的要求会更高。而且，proxy 也需要有高可用架构。因此，带 proxy 架构的整体就相对比较复杂。

不过那种部署方式都会遇到读写分离主从延迟的问题，因为主从延迟的存在，客户端刚执行完成一个更新事务，然后马上发起查询，如果选择查询的是从库，可能读取到的状态是更新之前的状态。

MySQL 中如何保证主从数据一致

MySQL 数据进行主从同步，主要是通过 binlog 实现的，从库利用主库上的binlog进行重播，实现主从同步。

来看下实现原理

在主从复制中，从库利用主库上的 binlog 进行重播，实现主从同步，复制的过程中蛀主要使用到了 dump thread，I/O thread，sql thread 这三个线程。

IO thread: 在从库执行 start slave 语句时创建，负责连接主库，请求 binlog，接收 binlog 并写入 relay-log；

dump thread：用于主库同步 binlog 给从库，负责响应从 IO thread 的请求。主库会给每个从库的连接创建一个 dump thread，然后同步 binlog 给从库；

sql thread：读取 relay log 执行命令实现从库数据的更新。

来看下复制的流程：

1、主库收到更新命令，执行更新操作，生成 binlog;

2、从库在主从之间建立长连接；

3、主库 dump_thread 从本地读取 binlog 传送刚给从库；

4、从库从主库获取到 binlog 后存储到本地，成为 relay log（中继日志）；

5、sql_thread 线程读取 relay log 解析、执行命令更新数据。

需要注意的是

一开始创建主从关系的时候，同步是由从库指定的。比如基于位点的主从关系，从库说“我要从 binlog 文件 A 的位置 P ”开始同步， 主库就从这个指定的位置开始往后发。

而主从复制关系搭建完成以后，是主库来决定“要发数据给从库”的。只有主库有新的日志，就会发送给从库。

binlog 有三种格式 statement，row 和 mixed。

1、Statement(Statement-Based Replication,SBR)：每一条会修改数据的 SQL 都会记录在 binlog 中，里面记录的是执行的 SQL;

Statement 模式只记录执行的 SQL，不需要记录每一行数据的变化，因此极大的减少了 binlog 的日志量，避免了大量的 IO 操作，提升了系统的性能。

正是由于 Statement 模式只记录 SQL，而如果一些 SQL 中包含了函数，那么可能会出现执行结果不一致的情况。

比如说 uuid() 函数，每次执行的时候都会生成一个随机字符串，在 master 中记录了 uuid，当同步到 slave 之后，再次执行，就获取到另外一个结果了。

所以使用 Statement 格式会出现一些数据一致性问题。

2、Row(Row-Based Replication,RBR)：不记录 SQL 语句上下文信息，仅仅只需要记录某一条记录被修改成什么样子;

Row 格式的日志内容会非常清楚的记录下每一行数据修改的细节，这样就不会出现 Statement 中存在的那种数据无法被正常复制的情况。

比如一个修改，满足条件的数据有 100 行，则会把这 100 行数据详细记录在 binlog 中。当然此时，binlog 文件的内容要比第一种多很多。

不过 Row 格式也有一个很大的问题，那就是日志量太大了，特别是批量 update、整表 delete、alter 表等操作，由于要记录每一行数据的变化，此时会产生大量的日志，大量的日志也会带来 IO 性能问题。

3、Mixed(Mixed-Based Replication,MBR)：Statement 和 Row 的混合体。

因为有些 statement 格式的 binlog 可能会导致主从不一致，所以要使用 row 格式。

但 row 格式的缺点是，很占空间。比如你用一个 delete 语句删掉10万行数据，用 statement 的话就是一个SQL语句被记录到binlog中，占用几十个字节的空间。但如果用 row 格式的 binlog，就要把这10万条记录都写到 binlog 中。这样做，不仅会占用更大的空间，同时写 binlog 也要耗费IO资源，影响执行速度。所以，MySQL就取了个折中方案，也就是有了 mixed 格式的 binlog。

mixed 格式的意思是，MySQL 自己会判断这条SQL语句是否可能引起主从不一致，如果有可能，就用 row 格式，否则就用 statement 格式。也就是说，mixed 格式可以利用 statment 格式的优点，同时又避免了数据不一致的风险。

不过现在越来越多的场景会把把 MySQL 的 binlog 格式设置成 row，例如下面的场景数据恢复。

这里从 delete、insert 和 update 这三种 SQL 语句的角度，来看看数据恢复的问题。

1、delete：如果执行的是 delete 操作，需要恢复数据。row 格式的 binlog 也会把被删掉的行的整行信息保存起来。所以，如果在执行完一条 delete 语句以后，发现删错数据了，可以直接把 binlog 中记录的 delete 语句转成 insert，把被错删的数据插入回去就可以恢复了；

2、insert：如果执行的是 insert 操作，需要恢复数据。row 格式下，insert 语句的 binlog 里会记录所有的字段信息，这些信息可以用来精确定位刚刚被插入的那一行。这时，可以直接把 insert 语句转成 delete 语句，删除掉这被误插入的一行数据就可以了；

3、update：如果执行的是 update 操作，需要进行数据恢复。binlog里面会记录修改前整行的数据和修改后的整行数据。所以，如果误执行了 update 语句的话，只需要把这个 event 前后的两行信息对调一下，再去数据库里面执行，就能恢复这个更新操作了。

循环复制问题

上面 binlog 的原理，我们可以知道 MySQL 中主从数据同步就是通过 binlog 来保持数据一致的。

不过，双 M 结构可能存在循环复制的问题。

什么是双 M 架构，双 M 结构和 M-S 结构，其实区别只是多了一条线，即：节点 A 和 B 之间总是互为主从关系。这样在切换的时候就不用再修改主从关系。

业务逻辑在节点 A 中更新了一条语句，然后把生成的 binlog 发送给节点 B，节点 B 同样也会生成 binlog 。

如果节点 A 同时又是节点 B 的从库，节点 B 同样也会传递 binlog 到节点 A，节点 A 会执行节点 B 传过来的 binlog。这样，节点 A 和节点 B 会不断的循环这个更新语句，这就是循环复制。

如何解决呢？

1、规定两个库的 server id 必须不同，如果相同，则它们之间不能设定为主从关系；

2、一个从库接到 binlog 并在重放的过程中，生成与原 binlog 的 server id 相同的新的 binlog；

3、每个库在收到从自己的主库发过来的日志后，先判断 server id，如果跟自己的相同，表示这个日志是自己生成的，就直接丢弃这个日志。

这样当设置了双 M 结构，日志的执行流就会变成这样：

1、从节点 A 更新的事务，binlog 里面记的都是 A 的 server id；

2、传到节点 B 执行一次以后，节点 B 生成的 binlog 的 server id 也是 A 的 server id；

3、再传回给节点 A，A 判断到这个 server id 与自己的相同，就不会再处理这个日志。所以，死循环在这里就断掉了。

主从同步延迟

主从同步延迟，就是同一个事务，在从库执行完成的时间和主库执行完成的时间之间的差值。

1、主库 A 执行完成一个事务，并且写入到 binlog ，记录这个时间为 T1；

2、传递数据给从库，从库接收这个 binlog，接收完成的时间记为 T2;

3、从库 B 执行完成这个接收的事务，这个时间记为 T3。

主从延迟的时间就是 T3-T1 之间的时间差。

通过 show slave status 命令能到 seconds_behind_master 这个值就表示当前从库延迟了多少秒。

seconds_behind_master 的计算方式：

1、每个事务的 binlog 都有一个时间字段，用于记录主库写入的时间；

2、从库取出当前正在执行的事务的时间字段的值，计算他与当前系统时间的差值，就能得到 seconds_behind_master。

简单的讲 seconds_behind_master 就是上面 T3 -T1 的时间差值。

如果主从机器的时间设置的不一致，会不会导致主从延迟的不准确？

答案是不会的，从库连接到主库，会通过 SELECT UNIX_TIMESTAMP()函数来获取当前主库的时间，如果这时候发现主库系统时间与自己的不一致，从库在执行 seconds_behind_master 计算的时候会主动扣减掉这差值。

主从同步延迟的原因

主从同步延迟可能存在的原因：

1、从库的性能比主库所在的机器性能较差；

从库的性能比较查，如果从库的复制能力，低于主库，那么在主库写入压力很大的情况下，就会造成从库长时间数据延迟的情况出现。

2、从库的压力大；

大量查询放在从库上，可能会导致从库上耗费了大量的 CPU 资源，进而影响了同步速度，造成主从延迟。

3、大事务的执行；

有事务产生的时候，主库必须要等待事务完成之后才能写入到 binlog，假定执行的事务是一个非常大的数据插入，这些数据传输到从库，从库同步这些数据也需要一定的时间，就会导致从节点出现数据延迟。

4、主库异常发生主从或主从切换切换。

发生主库切换的时候，可能会出现数据的不一致，主从切换会有下面两种策略：

可靠性优先策略：

1、首先判断下从库的 seconds_behind_master ，如果小于某个可以容忍的值，就进行下一步，否则持续重试这一步；

2、把主库 A 改成只读状态，设置 readonly 为 true;

3、判断从库 B 的 seconds_behind_master，直到这个值变成 0 为止；

4、把从库 B 改成可读写状态，设置 readonly 为 false，从库 B 变成新的主库;

5、更新业务请求会切换到到从库 B。

这个切换的过程中是存在不可用时间的，在步骤 2 之后，主库 A 和从库 B 都处于 readonly 状态，这时候系统处于不可写状态，知道从库库 B readonly 状态变成 false，这时候才能正常的接收写请求。

步骤 3 判断 seconds_behind_master 为 0，这个操作是最耗时的，通过步骤 1 中的提前判断，可以确保 seconds_behind_master 的值足够小，能够减少步骤 3 的等待时间。

可用性优先策略：

如果把步骤4、5调整到最开始执行，不等主库的数据同步，直接把连接切到从库 B，让从库 B 可以直接读写，这样系统就几乎没有不可用时间了。

这种策略能最大可能保障服务的可用性，但是会出现数据不一致的情况，因为写请求直接切换到从库 B 中，也就是设置 B 为新的主库，因为 B 库中还没有同步到最新的数据，变成主库之后，这部分数据就丢失了。

主从延迟如何处理

面对主从延迟，有下面几种应对方案：

1、强制走主库方案；

2、sleep方案；

3、判断主从无延迟方案；

4、配合 semi-sync 方案；

5、等主库位点方案；

6、等 GTID 方案。

强制走主库方案

强制走主库方案是实质上就是将查询进行分类，将不能容忍同步延迟的查询直接在主库中进行。

1、对于必须要拿到最新结果的请求，强制将其发到主库上。

2、对于可以读到旧数据的请求，才将其发到从库上。

这种方式有点投机的意思，如果所有的查询都不允许有延迟的出现，也就意味所有的查询，都会在主库中出现。这样就相当于放弃读写分离，所有的读写压力都在主库，等同于放弃了扩展性。

Sleep 方案

主库更新完成，从库在读取数据之前，首先 sleep 一会。具体的方案就是，类似于执行一条 select sleep(1) 命令。

这个方案的假设是，大多数情况下主从延迟在1秒之内，做一个sleep可以有很大概率拿到最新的数据。

这种方式不是一个靠谱的方案

1、如果一个查询请求本来 0.5 秒就可以在从库上拿到正确结果，也会等 1 秒；

2、如果延迟超过1秒，还是会出现过期读。

判断主从无延迟方案

可以通过主从是否有延迟，没有延迟就在从库中执行查询操作，有下面三种方法。

1、判断 seconds_behind_master；

每次查询之前首先判断 seconds_behind_master 的值是否等于 0 。如果不等于 0 表示还有延迟，直到等于 0 才进行查询操作。

2、对比位点确保主从无延迟；

• Master_Log_File 和 Read_Master_Log_Pos，表示的是读到的主库的最新位点；

• Relay_Master_Log_File 和 Exec_Master_Log_Pos，表示的是从库执行的最新位点。

如果 Master_Log_File 和 Relay_Master_Log_File、Read_Master_Log_Pos 和 Exec_Master_Log_Pos 这两组值完全相同，就表示接收到的日志已经同步完成。

3、对比 GTID 集合确保主从无延迟：

• Auto_Position=1 ，表示这对主从关系使用了 GTID 协议；

• Retrieved_Gtid_Set，是从库收到的所有日志的 GTID 集合；

• Executed_Gtid_Set，是从库所有已经执行完成的GTID集合。

如果 Retrieved_Gtid_Set 和 Executed_Gtid_Set 这两个集合相同就表示从库接收的日志已经同步完成。

什么是 GTID ？

GTID 的全称是 Global Transaction Identifier，也就是全局事务 ID，是一个事务在提交的时候生成的，是这个事务的唯一标识。

它由两部分组成，格式是：

GTID=server_uuid:gno

• server_uuid 是一个实例第一次启动时自动生成的，是一个全局唯一的值；

• gno 是一个整数，初始值是1，每次提交事务的时候分配给这个事务，并加1。

在 GTID 模式下，每一个事务都会跟一个 GTID 一一对应。GTID 有两种生成方式，通过 session 变量 gtid_next 的值来决定：

1、如果 gtid_next=automatic，代表使用默认值。这时，MySQL 就会把 server_uuid:gno 分配给这个事务。

a、记录 binlog 的时候，先记录一行 SET @@SESSION.GTID_NEXT=‘server_uuid:gno’;

b、把这个 GTID 加入本实例的 GTID 集合。

2、如果 gtid_next 是一个指定的GTID的值，比如通过 set gtid_next='current_gtid’ 指定为 current_gtid，那么就有两种可能：

a、如果 current_gtid 已经存在于实例的GTID集合中，接下来执行的这个事务会直接被系统忽略；

b、如果 current_gtid 没有存在于实例的 GTID 集合中，就将这个 current_gtid 分配给接下来要执行的事务，也就是说系统不需要给这个事务生成新的 GTID，因此 gno 也不用加1。

一个 current_gtid 只能给一个事务使用。这个事务提交后，如果要执行下一个事务，就要执行set 命令，把 gtid_next 设置成另外一个 gtid 或者 automatic。

每个 MySQL 实例都维护了一个 GTID 集合，用来对应“这个实例执行过的所有事务”。

明白了 GTID 的概念，再来看下基于 GTID 的主从复制的用法。

在 GTID 模式下，从库 C 要设置为主从库 B 的从库的语法如下：

我们把现在这个时刻，实例 B 的 GTID 集合记为set_b，实例 C 的 GTID 集合记为 set_c。接下来，我们就看看现在的主从切换逻辑。

我们在实例 C 上执行 start slave 命令，取 binlog 的逻辑是这样的：

1、实例 C 指定主库 B，基于主从协议建立连接。

2、实例 C 把 set_c 发给主库 B。

3、实例 B 算出 set_b 与 set_c 的差集，也就是所有存在于 set_b，但是不存在于 set_c 的 GITD 的集合，判断 B 本地是否包含了这个差集需要的所有 binlog 事务。

a、如果不包含，表示 B 已经把实例 C 需要的 binlog 给删掉了，直接返回错误；

b、如果确认全部包含，B 从自己的 binlog 文件里面，找出第一个不在 set_c 的事务，发给 C；

之后就从这个事务开始，往后读文件，按顺序取 binlog 发给 C 去执行。

这个逻辑里面包含了一个设计思想：在基于 GTID 的主从关系里，系统认为只要建立主从关系，就必须保证主库发给从库的日志是完整的。因此，如果实例 C 需要的日志已经不存在，B 就拒绝把日志发给 C。

这跟基于位点的主从协议不同。基于位点的协议，是由从库决定的，从库指定哪个位点，主库就发哪个位点，不做日志的完整性判断。

我们再来看看引入 GTID 后，一主多从的切换场景下，主从切换是如何实现的。

由于不需要找位点了，所以从库 C、D 只需要分别执行 change master 命令指向实例 B 即可。

其实，严谨地说，主从切换不是不需要找位点了，而是找位点这个工作，在实例 B 内部就已经自动完成了。但由于这个工作是自动的，所以对HA系统的开发人员来说，非常友好。

之后这个系统就由新主库 B 写入，主库 B 的自己生成的 binlog 中的 GTID 集合格式是：server_uuid_of_B:1-M。

GTID 主从同步设置时，主库 A 发现需同步的 GTID 日志有删掉的，那么 A 就会报错。

解决办法：

从库 C 在启动同步前需要设置 gtid_purged，指定 GTID 同步的起点，使用备份搭建从库时需要这样设置。

如果在从库上执行了单独的操作，导致主库上缺少 GTID，那么可以在主库上模拟一个与从库 C 上 GTID 一样的空事务，这样主从同步就不会报错了。

配合semi-sync

MySQL 有三种同步模式，分别是：

1、异步复制：MySQL 中默认的复制是异步的，主库在执行完客户端提交的事务后会立即将结果返回给客户端，并不关心从库是否已经接收并且处理。存在问题就是，如果主库的日志没有及时同步到从库，然后主库宕机了，这时候执行故障转移，在从库冲选主，可能会存在选出的主库中数据不完整；

2、全同步复制：指当主库执行完一个事务，并且等到所有从库也执行完成这个事务的时候，主库在提交事务，并且返回数据给客户端。因为要等待所有从库都同步到主库中的数据才返回数据，所以能够保证主从数据的一致性，但是数据库的性能必然受到影响；

3、半同步复制：是介于全同步和全异步同步的一种，主库至少需要等待一个从库接收并写入到 Relay Log 文件即可，主库不需要等待所有从库给主库返回 ACK。主库收到 ACK ，标识这个事务完成，返回数据给客户端。

MySQL 中默认的复制是异步的，所以主库和从库的同步会存在一定的延迟，更重要的是异步复制还可能引起数据的丢失。全同步复制的性能又太差了，所以从 MySQL 5.5 开始，MySQL 以插件的形式支持 semi-sync 半同步复制。

因为本同步复制不需要等待所有的从库同步成功，这时候在从库中的查询，就会面临两种情况：

1、如果查询是落在这个响应了 ack 的从库上，是能够确保读到最新数据；

2、如果是查询落到其他从库上，它们可能还没有收到最新的日志，就会产生过期读的问题。

等主库位点方案

理解等主库位点方案之前，首先来看下下面的这条命令的含义

select master_pos_wait(file, pos[, timeout]);

看下这条命令的几个参数：

1、这条命令是在从库中执行的；

2、参数 file 和 pos 指的是主库上的文件名和位置；

3、timeout 可选，设置为正整数 N 表示这个函数最多等待 N 秒。

这条命令的正常返回是一个正整数 M ，表示从命令开始执行，到应用完 file 和 pos 表示的 binlog 位置，成功执行的事务个数。

如果从库执行这个命令，表示从库同步完 file 和 pos 表示的 binlog 位置，同步的事务数量，正常返回 M 表示从库数据在 timeout 时间内相对于主库已经没有延迟了。

除了正常返回，也会有其它的执行返回信息

1、如果执行期间从库同步发生异常，就返回 NULL;

2、如果等待超过 N 秒，聚返回 -1；

3、如果刚开始执行的时候，就发现已经执行过这个位置了，就返回 0。

弄明白了这条命令的作用，我们再来看下等主库位点方案的具体执行流程，如果我们在主库中执行一个数据更新，然后在从库中查询，执行的过程就是:

1、更新事务执行完成，马上执行 show master status 得到当前主库执行到的 File 和 Position；

2、选定一个从库执行查询；

3、在从库上执行 select master_pos_wait(File, Position, 1)；

4、如果返回值是 >=0 的正整数，则在这个从库执行查询语句；

5、否则到主库执行查询。

如果从库的延迟都超过了的等待的 timeout 时间，那么所有的请求，最总还是会落到主库中，查询的压力还是会爬到主库中。

等 GTID 方案

如果开启了 GTID 模式，对应的也有等待 GTID 的方案。

MySQL 中同样提供了等待同步的命令

 select wait_for_executed_gtid_set(gtid_set, 1);

执行逻辑：

1、从库在执行命令的时候携带事务的 gtid，如果返回 0 表示该事务已经正常同步到从库中，可以在从库中执行查询；

2、超时测返回 1。

在前面等位点的方案中，我们执行完事务后，还要主动去主库执行 show master status。而 MySQL 5.7.6 版本开始，允许在执行完更新类事务后，把这个事务的GTID返回给客户端，这样等 GTID 的方案就可以减少一次查询。

等 GTID 方案的流程就是

1、主库中执行完成一个事务，从返回包直接获取这个事务的 GTID，记为 gtid1；

2、选定一个从库执行查询语句；

3、从库中执行 select wait_for_executed_gtid_set(gtid1, 1)；

4、如果返回值是 0，就在从库中执行查询；

5、返回值不是 0，表示该事务还没有同步到从库中，就需要到主库中执行查询了。

和等主库位点方案的方案一样，等待超时就需要向主库中发起查询了。

总结

1、MySQL 中读写分离是经常用到了的架构了，通过读写分离实现横向扩展的能力，写入和更新操作在源服务器上进行，从服务器中进行数据的读取操作，通过增大从服务器的个数，能够极大的增强数据库的读取能力；

2、MySQL 数据进行主从同步，主要是通过 binlog 实现的，从库利用主库上的binlog进行重播，实现主从同步；

3、数据同步会存在一个问题及时同步延迟；

4、主从同步延迟可能存在的原因：

• 1、从库的性能比主库所在的机器性能较差；

• 2、从库的压力大；

• 3、大事务的执行；

• 4、主库异常发生主从或主从切换切换。

5、主从延迟应该如何处理？

面对主从延迟，有下面几种应对方案：

• 1、强制走主库方案；

• 2、sleep方案；

• 3、判断主从无延迟方案；

• 4、配合 semi-sync 方案；

• 5、等主库位点方案；

• 6、等 GTID 方案。

参考

【高性能MySQL(第3版)】https://book.douban.com/subject/23008813/
【MySQL 实战 45 讲】https://time.geekbang.org/column/100020801
【MySQL技术内幕】https://book.douban.com/subject/24708143/
【MySQL学习笔记】https://github.com/boilingfrog/Go-POINT/tree/master/mysql
【MySQL文档】https://dev.mysql.com/doc/refman/8.0/en/replication.html
【浅谈 MySQL binlog 主从同步】http://www.linkedkeeper.com/1503.html