MySQL读写分离

读写分离的基本结构如下图：

读写分离的主要目的就是分摊主库的压力。上图中的结构是客户端主动做负载均衡，这种模式下一般会把数据库的连接信息放在客户端的连接层。由客户端来选择后端数据库进行查询

还有一种架构就是在MySQL和客户端之间有一个中间代理层proxy，客户端只连接proxy，由proxy根据请求类型和上下文决定请求的分发路由

作者：Java编程宇宙 链接：https://zhuanlan.zhihu.com/p/394921571 来源：知乎 著作权归作者所有。商业转载请联系作者获得授权，非商业转载请注明出处。

1. 客户端直连方案，因此少了一层proxy转发，所以查询性能稍微好一点，并且整体架构简单，排查问题更方便。但是这种方案，由于要了解后端部署细节，所以在出现主备切换、库迁移等操作的时候，客户端都会感知到，并且需要调整数据库连接信息。一般采用这样的架构，一定会伴随一个负责管理后端的组件，比如Zookeeper，尽量让业务端只专注于业务逻辑开发
2. 带proxy的架构，对客户端比较友好。客户端不需要关注后端细节，连接维护、后端信息维护等工作，都是由proxy完成的。但这样的话，对后端维护团队的要求会更高，而且proxy也需要有高可用架构

在从库上会读到系统的一个过期状态的现象称为过期读

1、强制走主库方案

强制走主库方案其实就是将查询请求做分类。通常情况下，可以分为这么两类：

1. 对于必须要拿到最新结果的请求，强制将其发到主库上
2. 对于可以读到旧数据的请求，才将其发到从库上

这个方案最大的问题在于，有时候可能会遇到所有查询都不能是过期读的需求，比如一些金融类的业务。这样的话，就需要放弃读写分离，所有读写压力都在主库，等同于放弃了扩展性

2、Sleep方案

主库更新后，读从库之前先sleep一下。具体的方案就是，类似于执行一条select sleep(1)命令。这个方案的假设是，大多数情况下主备延迟在1秒之内，做一个sleep可以很大概率拿到最新的数据

以买家发布商品为例，商品发布后，用Ajax直接把客户端输入的内容作为最新商品显示在页面上，而不是真正地去数据库做查询。这样，卖家就可以通过这个显示，来确认产品已经发布成功了。等到卖家再刷新页面，去查看商品的时候，其实已经过了一段时间，也就达到了sleep的目的，进而也就解决了过期读的问题。

但这个方案并不精确：

1. 如果这个查询请求本来0.5秒就可以在从库上拿到正确结果，也会等1秒
2. 如果延迟超过1秒，还是会出现过期读

3、判断主备无延迟方案

show slave status结果里的seconds_behind_master参数的值，可以用来衡量主备延迟时间的长短

1.第一种确保主备无延迟的方法是，每次从库执行查询请求前，先判断seconds_behind_master是否已经等于0。如果还不等于0，那就必须等到这个参数变为0才能执行查询请求

show slave status结果的部分截图如下：

2.第二种方法，对比位点确保主备无延迟：

• Master_Log_File和Read_Master_Log_Pos表示的是读到的主库的最新位点
• Relay_Master_Log_File和Exec_Master_Log_Pos表示的是备库执行的最新位点

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

3.第三种方法，对比GTID集合确保主备无延迟：

• Auto_Position=1表示这堆主备关系使用了GTID协议
• Retrieved_Gitid_Set是备库收到的所有日志的GTID集合
• Executed_Gitid_Set是备库所有已经执行完成的GTID集合

如果这两个集合相同，也表示备库接收到的日志都已经同步完成

4.一个事务的binlog在主备库之间的状态：

• 主库执行完成，写入binlog，并反馈给客户端
• binlog被从主库发送给备库，备库收到
• 在备库执行binlog完成

上面判断主备无延迟的逻辑是备库收到的日志都执行完成了。但是，从binlog在主备之间状态的分析中，有一部分日志，处于客户端已经收到提交确认，而备库还没收到日志的状态

这时，主库上执行完成了三个事务trx1、trx2和trx3，其中：

• trx1和trx2已经传到从库，并且已经执行完成了
• trx3在主库执行完成，并且已经回复给客户端，但是还没有传到从库中

如果这时候在从库B上执行查询请求，按照上面的逻辑，从库认为已经没有同步延迟，但还是查不到trx3的

4、配合semi-sync

要解决上面的问题，就要引入半同步复制。semi-sync做了这样的设计：

1. 事务提交的时候，主库把binlog发送给从库
2. 从库收到binlog以后，发回给主库一个ack，表示收到了
3. 主库收到这个ack以后，才能给客户端返回事务完成的确认

如果启用了semi-sync，就表示所有给客户端发送过确认的事务，都确保了备库已经收到了这个日志

semi-sync+位点判断的方案，只对一主一备的场景是成立的。在一主多从场景中，主库只要等到一个从库的ack，就开始给客户端返回确认。这时，在从库上执行查询请求，就有两种情况：

1. 如果查询是落在这个响应了ack的从库上，是能够确保读到最新数据
2. 但如果查询落到其他从库上，它们可能还没有收到最新的日志，就会产生过期读的问题

判断同步位点的方案还有另外一个潜在的问题，即：如果在业务更新的高峰期，主库的位点或者GTID集合更新很快，那么上面的两个位点等值判断就会一直不成立，很有可能出现从库上迟迟无法响应查询请求的情况

上图从状态1到状态4，一直处于延迟一个事务的状态。但是，其实客户端是在发完trx1更新后发起的select语句，我们只需要确保trx1已经执行完成就可以执行select语句了。也就是说，如果在状态3执行查询请求，得到的就是预期结果了。

semi-sync配合主备无延迟的方案，存在两个问题：

1. 一主多从的时候，在某些从库执行查询请求会存在过期读的现象
2. 在持续延迟的情况下，可能出现过度等待的问题

5、等主库位点方案

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

这条命令的逻辑如下：

1. 它是在从库执行的
2. 参数file和pos指的是主库上的文件名和位置
3. timeout可选，设置为正整数N表示这个函数最多等待N秒

这个命令正常返回的结果是一个正整数M，表示从命令开始执行，到应用完file和pos表示的binlog位置，执行了多少事务

1. 如果执行期间，备库同步线程发生异常，则返回NULL
2. 如果等待超过N秒，就返回-1
3. 如果刚开始执行的时候，就发现已经执行过这个位置了，则返回0