数据库——MySQL读写分离后的延迟解决方案

背景：
根据上图可以看到QPS：10.73k,实际上真实的并发大量数据到达的时候，我这里最高的QPS是将近15k.而目前单个数据库分片（实例）4CPU8G内存的配置下，最高的性能是7k的QPS。

基于上篇我进行了分库分表是对于性能有很大的提高，分库分表实践和中间件的引申
我这里讲解的例子是目前4主8从库（12个实例），以下每个实例都会称为分片。单个分片配置mysql版本5.7.19（一会说明不同版本是读写分离的不同策略）,12CPU16G内存,128G的磁盘，Raid:10。

读写分离实践

读写分离可以参考上篇文章的分库分表实践中的中间件的用法来实现。主流一般会使用mycat，但是每个中间件都有自己的优点可以择优和业务特点而用。接下来讲读写分离后的后遗症。

读写分离的延迟和实时insert/update和查询操作

比如我这里的一个场景：由于数据量大，以人维度的情况下，商品量20w~50w。然后需要分页查询未同步下游状态，进行数据同步后再更新该分页数据。我当时设定了如下的四种场景，最后选择了读写分离和不分离同时存在，针对于实时要求结果高的依然是master主库读写，变动需求量小的数据，全部转移slave从库。

如下是四种场景的方案：

1、 完全分离：全量读->从库，全量读写->主库
前提：第一页查询逻辑不变
特点：半同步复制，目前是1主2从库，利用半同步复制原理，1/2的可能性会重复查询，当然这个几率需要和延时性进行测试计算可得，也就是最坏的结果可能性是重复查询50%的可能性。目前反馈主从同步延时1s
方案：
(1)冗余性：去重校验，对于50%的可能性查询出的重复数据。
(2)性能：重复数据和校验会使性能有所降低，但是从库是2个分摊QPS的压力，会使性能有所提高，相互抵消一部分。

2、 不完全分离：商品读写模块依然master主库，其他地方读->从库，写->主库。
前提：第一页查询逻辑不变
特点：由于联合营销系统场景单一，主要是围绕SKU进行。但是会改善一部分压力。
方案：
(1) 冗余性：代码冗余地方多，风格不统一。
(2) 性能：会有部分改善，但是从整体看，数据量大的时候，依然是master主库读写压力大。

3、 完全分离：全量读->从库，全量读写->主库。
前提：分页查询（不加同步状态）
特点：分页查询随着页数和数据量大的情况呈正相关也会时间越来越大。
方案：
(1) 冗余性： 会重复查询，由于分页和性能成正相关，数据量越大，耗时越大。
(2) 分页查询解决性能损耗来减少性能响应时间的方案
（2.1）可以采用延时关联策略（弹性数据库不支持）
（2.2）采用id序列（利用数据库id索引过滤）和limit组合使用（效果不大）。

4、 完全分离：全量读->从库，全量读写->主库
前提：分页查询（加同步状态），最后一次结果集退出的时候进行兜底全量count查询并重新执行上述逻辑。
特点：分页查询随着页数和数据量大的情况呈正相关也会时间越来越大。
方案：与上诉3的方案相同。但避免了查询出重复数据。

读写分离和非分离同时存在，改造后的效果图（我这里的数据量2亿）：
读写分离之前master主库CPU使用率95%~99%

读写分离之后master主库CPU使用率10%以下。

从上述来看我们的读写分离实践效果还是蛮不错的，但是这里如下几个问题：

• 0、MySQL主从集群主要解决的问题？
• 1、MySQL主从同步的几种策略？以及区别？
• 2、MySQL的主从延迟到底有多大？
• 3、多少的延迟时间我们能接受？
• 4、主从延迟的根本原因是什么？
• 5、当数据量大读写分离只要有写的地方依然会出现延迟导致的数据不一致情况，该如何解决？

• 0、MySQL主从集群主要解决的问题？

• 多主库原因：
  高并发的情况下，单台MySQL数据库的连接数多，这样QPS/OPS就会非常大。就像上述我提到的我这里的压测结果，MySQL最大7k的QPS。随着并发数再多，QPS的处理能力也会下降。那么如何解决这个瓶颈。这个时候就会分库，分摊QPS/OPS的能力，本来单台master库的QPS/OPS的请求是2w,我这里分片4个master主库，则相当于每个master主库分摊5000请求量。（如果不好理解可以比喻成服务器集群，在服务架构演变过程中单台服务器变为多台服务器，如果依然不能理解的话可以参考下这篇文章大型网站的演进）
  所以这样我们可以知道降低了单台服务器的连接数请求量。

• 主从库原因：
  那么对于5000单台请求量（基于刚才的假设模型），他的请求构成比例又是如何呢？以及如何防止流量并发的场景导致的系统不可用瘫痪呢？数据丢失呢？
  首先我们可以考虑进行数据备份，以及进行流量分析，而一般往往我们就引入了从库：
  一主一从：一个 Master，一个 Slave
  一主多从：一个 Master，多个 Slave
  请求构成比例可以参考我上面的这个图（实际生产环境）：
  可以从图中看到比例read:write=10.73k:26 近似等于 10000:1,平均比例：298.91:2.4=150:1的比例，明显是读取操作大约写入操作，相当于1次写入的时候平均承担了150次请求读取操作。而当流量并发上来的时候更是夸张到1w：1。那么我们能不能把静态的数据读取放到备份数据从库上呢？答案明显是可以的。

• 1、MySQL主从同步的几种策略？以及区别？

• 主从同步机制：
  那么这里还需要考虑的是一个复制数据的同步机制：
  一主一从的情况
  一主多从的情况
  
  根据上图我们来看下他具体是如何实现同步的，我们都知道其实mysql执行的时候是根据binlog日志进行数据执行的。那么我们当然可以根据binlog日志进行最原始的数据二次处理。

• 2、MySQL的主从延迟到底有多大？
• 3、多少的延迟时间我们能接受？
• 4、主从延迟的根本原因是什么？
  实现原理：
  
  主从延时时间：Master 执行成功，到 Slave 执行成功，时间差。
  
  由于从库从主库拷贝日志以及串行执行SQL的特点，在高并发场景下，从库的数据一定会比主库慢一些，是有延时的。所以经常出现，刚写入主库的数据可能是读不到的，要过几十毫秒，甚至几百毫秒才能读取到。
  而且这里还有另外一个问题，就是如果主库突然宕机，然后恰好数据还没同步到从库，那么有些数据可能在从库上是没有的，有些数据可能就丢失了

mysql的两个机制：

• 一个是半同步复制，用来解决主库数据丢失问题；
  semi-sync复制，指的就是主库写入binlog日志之后，就会将强制此时立即将数据同步到从库，从库将日志写入自己本地的relay log之后，接着会返回一个ack给主库，主库接收到至少一个从库的ack之后才会认为写操作完成了

• 一个是并行复制，用来解决主从同步延时问题。
  指的是从库开启多个线程，并行读取relay log中不同库的日志，然后并行重放不同库的日志，这是库级别的并行。

监控主从延迟：
Slave 使用本机当前时间，跟 Master 上 binlog 的时间戳比较
pt-heartbeat、mt-heartbeat
本质：同一条 SQL，Master 上执行结束的时间 vs. Slave 上执行结束的时间。

• 5、当数据量大读写分离只要有写的地方依然会出现延迟导致的数据不一致情况，该如何解决？

• 1、分析mysql日志 看是否慢查询太多
• 2、统计高峰时期的写入语句数量以及平均值
• 3、检查同步时主库和从库的网络数据传输量
• 4、统计服务器运行状态信息
• 5、从探针的角度来考虑问题，方法是在Master上增加一个自增表，这个表仅含有1个的字段。当Master接收到任何数据更新的请求时，均会触发这个触发器，该触发器更新自增表中的记录。如下图所示：
  
  由于Count_table也参与Mysq的主从同步，因此在Master上作的 Update更新也会同步到Slave上。当Client通过Proxy进行数据读取时，Proxy可以先向Master和Slave的 Count_table表发送查询请求，当二者的数据相同时，Proxy可以认定 Master和Slave的数据状态是一致的，然后把select请求发送到Slave服务器上，否则就发送到Master上。如下图所示：
  
  瓶颈思考的角度：sql语句包含大量慢查询，高并发,网络传输问题以及服务器配置

Note：

读写分离不适用的场景不能强行使用：
否则读写分离的主从延迟导致的影响会不止如下几条：

• 异常情况下， HA 无法切换： HA 软件需要检查数据的一致性，延迟时，主备不一致
• 备库 Hang 会引发备份失败：flush tables with read lock 会 900s 超时
• 以 Slave 为基准进行的备份，数据不是最新的，而是延迟的。

这样就会导致的结果读写分离没有意义，主备容灾失效。
那么又回归到了原始开始的场景，如果要使用那么区分自己的业务场景，并细化事务，提升SQL执行速度，优化索引，减少不必要的DML操作，以及定位2/8原则到底是哪些表的数据影响主从延迟大。然后最重要的一点就是有时候往往业务逻辑是引发问题的根本原因，优化业务逻辑是最根本的问题。动态数据变更频繁的必须走实时的读写master主库。否则高并发流量场景下，读写分离带来的损失会更大。

图片部分参考资料来源，资料2