mysql查询读写延迟_MySQL读写分离后的延迟解决方案
mysql查询读写延迟_MySQL读写分离后的延迟解决⽅案
数据库——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主库。否则⾼并发流量场景下，读写分离带来的损失会更⼤。

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。