相关文章

数据库系列：MySQL慢查询分析和性能优化
数据库系列：MySQL索引优化总结（综合版）
数据库系列：高并发下的数据字段变更
数据库系列：覆盖索引和规避回表
数据库系列：数据库高可用及无损扩容
数据库系列：使用高区分度索引列提升性能
数据库系列：前缀索引和索引长度的取舍
数据库系列：MySQL引擎MyISAM和InnoDB的比较
数据库系列：InnoDB下实现高并发控制
数据库系列：事务的4种隔离级别
数据库系列：RR和RC下，快照读的区别
数据库系列：MySQL InnoDB锁机制介绍
数据库系列：MySQL不同操作分别用什么锁？
数据库系列：业内主流MySQL数据中间件梳理
数据库系列：巨量数据表的分页性能问题
数据库系列: 主流分库分表中间件介绍(图文总结)
数据裂变，数据库高可用架构设计实践

1 本文索引

1 啥是MySQL主从复制?

主从复制，是指建立一个和主数据库完全一样的数据库环境（称为从数据库），并将主库的操作行为进行复制的过程，那如何保证从库跟主库的数据一致性？ 即将主数据库的DDL和DML的操作日志同步到从数据库上，然后在从数据库上对这些日志进行重新执行，来保证从数据库和主数据库的数据的一致性。

1.1 为什么要做主从复制?

1、在复杂的业务操作中，经常会有操作导致锁行甚至锁表的情况，如果读写不解耦，会很影响运行中的业务，使用主从复制，让主库负责写，从库负责读，职责更单一，性能更优良。
即使主库出现了锁表的情景，通过读从库也可以保证业务的正常运行。

2、保证数据的热备份，主库宕机后能够及时替换主库，保障业务可用性。

3、架构的演进：业务量扩大，I/O访问频率增高，单机无法满足，主从复制可以做多库方案，降低磁盘I/O访问的频率，提高单机的I/O性能。

4、本质上也是分治理念，主从复制、读写分离即是压力分拆的过程。

5、读写比也影响整个拆分方式，读写比越高，主从库比例应越高，才能保证读写的均衡，才能保证较好的运行性能。读写比下的主从分配策略如下：

读写比（大约）	主库	从库
50：50	1	1
66.6：33.3	1 2
80：20	1	4
-- --	-- --	-- --

1.2 为什么会出现主从延迟?

当在从库上启动复制时，首先创建I/O线程连接主库，主库随后创建Binlog Dump线程读取数据库事件并发送给I/O线程，I/O线程获取到事件数据后更新到从库的中继日志Relay Log中去，之后从库上的SQL线程读取中继日志Relay Log中更新的数据库事件并应用，如下图所示：

细化一下有如下几个步骤：
1、MySQL主库在事务提交时把数据变更（insert、delet、update）作为事件日志记录在二进制日志表（binlog）里面。
2、主库上有一个工作线程 binlog dump thread，把binlog的内容发送到从库的中继日志relay log中。
3、从库根据中继日志relay log重做数据变更操作，通过逻辑复制来达到主库和从库的数据一致性。
4、MySQL通过三个线程来完成主从库间的数据复制，其中binlog dump线程跑在主库上，I/O线程和SQL线程跑在从库上。拥有多个从库的主库会为每一个连接到主库的从库创建一个binlog dump线程。

2 主从延迟的原因分析

先理解以下概念，说说啥是主从延时？

主从延时，通常指的是在数据库的主从复制架构中，从服务器（Slave）在接收并应用主服务器（Master）上的数据变更时所经历的时间延迟。具体来说，当主服务器上的数据发生变化后，这些变更需要通过复制机制同步到从服务器，而从服务器处理这些变更并完成数据同步所需的时间就构成了所谓的延时。
直接的后果就是从库中查询到的数据结果和主库中是不一致的，我们经常在查询数据的过程中会查询到旧数据，可能就是这个原因导致的！

MySQL主从复制，读写分离是我们常用的数据库架构，但是在并发量较大、数据变化大的场景下，主从延时会比较严重。
延迟的本质原因是：系统TPS并发较高时，主库产生的DML（也包含一部分DDL）数量超过Slave一个Sql线程所能承受的范围，效率就降低了。
我们看到这个sql thread 是单个线程，所以他在重做RelayLog的时候，能力也是有限的。

还有一些其他原因，比如：

• 主服务器负载过高 ：当主服务器需要处理大量的数据变更时，可能会产生较高的负载，导致数据变更的生成和传输速度变慢，从而增加从服务器的同步延迟。
• 从服务器负载过高 ：从服务器在接收并应用主服务器上的数据变更时，如果自身的负载过高，可能会导致处理速度变慢，从而产生同步延迟。
• 网络延迟 ：主从服务器之间的网络连接不稳定或带宽不足，也可能导致数据变更的传输速度变慢，从而增加同步延迟。
• 机器性能差异 ：主从服务器的硬件配置不同，例如CPU、内存、磁盘等性能差异，也可能导致同步延迟的产生。
• MySQL配置不合理 ：例如，如果主服务器的二进制日志（binlog）设置过大，或者从服务器的中继日志（relay log）配置不合理，都可能导致处理速度变慢，从而产生同步延迟。
• 从库大量的大型的 query 语句产生了锁等待

3 主从延时优化方案

3.1 最优的系统配置

优化系统配置（系统级、链接层、存储引擎层），让数据库处在最优状态：最大连接数、允许错误数、允许超时时间、pool_size、log_size等，保证内存、CPU、存储空间的扩容（硬件部分）。

倒金字塔法则 告诉我们，这一块往往是被忽略的，但是又是必不可少的。

如果MySQL部署在linux系统上，可以适当调整操作系统的参数来优化MySQL性能，下面是对Linux内核参数进行适当调整。

 1 # TIME_WAIT超时时间，默认是60s
 2 net.ipv4.tcp_fin_timeout = 30 
 3 # 增加tcp支持的队列数，加大队列长度可容纳更多的等待连接
 4 net.ipv4.tcp_max_syn_backlog = 65535
 5 # 减少断开连接时 ，资源回收
 6 net.ipv4.tcp_max_tw_buckets = 8000
 7 net.ipv4.tcp_tw_reuse = 1
 8 net.ipv4.tcp_tw_recycle = 1
 9 net.ipv4.tcp_fin_timeout = 10
10 # 打开文件的限制
11 *soft nofile 65535
12 *hard nofile 65535 

MySQL5.5+版本之后，默认存储引擎为InnoDB，我们这边列出部分可能影响数据库性能的参数。
公共参数默认值：

1 max_connections = 151
2 # 同时处理最大连接数，建议设置最大连接数是上限连接数的80%左右,一般默认值为151，可以做适当调整。
3 sort_buffer_size = 2M
4 # 查询排序时缓冲区大小，只对order by和group by起作用，建议增大为16M
5 open_files_limit = 1024 
6 # 打开文件数限制，如果show global status like 'open_files'查看的值等于或者大于open_files_limit值时，程序会无法连接数据库或卡死

InnoDB参数默认值：

 1 innodb_buffer_pool_size = 128M
 2 # 索引和数据缓冲区大小，建议设置物理内存的70%左右（这个前提是这个服务器只用做Mysql数据库服务器）
 3 innodb_buffer_pool_instances = 1    
 4 # 缓冲池实例个数，推荐设置4个或8个
 5 innodb_flush_log_at_trx_commit = 1  
 6 # 关键参数，0代表大约每秒写入到日志并同步到磁盘，数据库故障会丢失1秒左右事务数据。1为每执行一条SQL后写入到日志并同步到磁盘，I/O开销大，执行完SQL要等待日志读写，效率低。2代表只把日志写入到系统缓存区，再每秒同步到磁盘，效率很高，如果服务器故障，才会丢失事务数据。对数据安全性要求不是很高的推荐设置2，性能高，修改后效果明显。
 7 sync_binlog=1
 8 
 9 innodb_file_per_table = ON  
10 # 是否共享表空间，5.7+版本默认ON，共享表空间idbdata文件不断增大，影响一定的I/O性能。建议开启独立表空间模式，每个表的索引和数据都存在自己独立的表空间中，可以实现单表在不同数据库中移动。
11 innodb_log_buffer_size = 8M  
12 # 日志缓冲区大小，由于日志最长每秒钟刷新一次，所以一般不用超过16M 

3.2 数据库层做合理分治

数据库分区是永恒的话题，主从延迟一定程度上是单台数据库主服务操作过于频繁，使得 单线程的SQL thread 疲于应付 。可以适当的从功能上对数据库进行拆分，分担压力。

数据库拆分可以参考我的这篇文章《 分库分表 》，这边就不赘述。

3.3 从库同步完成后响应

假如你的业务时间允许，你可以在写入主库的时候，确保数据都同步到从库了之后才返回这条数据写入成功，当然如果有多个从库，你也必须确保每个从库都写入成功。当然， 这个方案对性能和时间的消耗是极大的，会直接降低你的系统吞吐量，不推荐。

3.4 适当引入缓存

可以引入redis或者其他nosql数据库来存储我们经常会产生主从延迟的业务数据。当我在写入数据库的同时，我们再写入一份到redis中。

读取数据的时候，我们可以先去查看redis中是否有这个数据，如果有我们就可以直接从redis中读取这个数据。当数据真正同步到数据库中的时候，再从redis中把数据删除。如下图：

这边还需注意两点，很重要哟，面试必问：

1、虽然一定程度上缓解延迟的问题，但如果遇到高并发的情况，对Redis的频繁删除也不合理，所以需要结合场景综合考虑，比如定期删除缓存。

2、高并发情况下可能存在slave还没同步，又有新的值写进来了，这时候Master --> Slave 还在排队中，但是Cache已经被更新了。所以如果对Redis进行删除，可能会误删除最新的缓存值，导致读取到的数据是旧的。

如上图情况，对一个值分别更新 1，2，3，主从同步按照顺序进行，刚同步完1，Cache就更新到3了，这时候如果把Cache删除了，读请求就会走到从库去读，读到了1，数据就会出现短暂不一致了。

所以这个地方也需要注意，可以同时将唯一键（比如主键）也做保存，删除之前做一个判断，避免误删。或者干脆不实时删除缓存，低峰值期再来处理。

3.5 多线程重放RelayLog

MySQL使用单线程重放RelayLog，那能不能在这上面做解法呢，比如使用多线程并行重放RelayLog，就可以缩短时间。但是这个对数据一致性是个考验。

需要考虑如何分割RelayLog， 才能够让多个数据库实例，多个线程并行重放RelayLog ，不会出现不一致。比如RelayLog包含这三条语句给学生授予学分的记录，你就不知道结果会变成什么。可能是806甚至是721。

1 update t_score set score = 721 where stu_code=374532;
2 update t_score set score = 806 where stu_code=374532;
3 update t_score set score = 899 where stu_code=374532;

解法就是：

相同库表上的写操作，用相同的线程来重放RelayLog；不同库表上的写操作，可以并发用多个线程并发来重放RelayLog。

设计一个哈希算法，hash(db-name) % thread-num，表名称hash之后再模上线程数，就能很轻易做到，同一个库表上的写操作，被同一个重放线程串行执行，做到提效的目的。

这其实也是一种分治的思维，类似上面直接对数据库进行拆分。

3.6 少量读业务直连主库

业务量不多的情况下，不做主从分离.既然主从延迟是由于从库同步写库不及时引起的，那我们也可以在有主从延迟的地方改变读库方式，由原来的读从库改为读主库。当然这也会增加代码的一些逻辑复杂性。
这边需要注意的是，直接读主库的业务量不宜多，而且是读实时一致性有刚性需求的业务才这么做。否则背离读写分离的目的。

3.7 适当的限流、降级

任何的服务器都是有吞吐量的限制的，没有任何一个方案可以无限制的承载用户的大量流量。所以我们必须估算好我们的服务器能够承载的流量上限是多少。
达到这个上限之后，就要采取缓存，限流，降级的这三大杀招来应对我们的流量。这也是应对主从延迟的根本处理办法。

3.8 高版本MySQL支持多线程复制

MySQL 5.6版本开始支持多线程复制（也称为并行复制），MySQL5.7之后，提供了基于GTID并行复制的能力。在5.6这个版本中，默认是单线程复制，但是可以通过配置参数slave_parallel_workers来启用多线程复制。
要启用多线程复制，请按照以下步骤操作：

1. 确保你的MySQL版本是5.6+

2. 修改多线程配置
修改MySQL的my.cnf（或my.ini）配置文件，在从服务器上设置slave_parallel_workers参数为期望的工作线程数，例如：

[mysqld]
slave_parallel_workers = 8

3. 重启MySQL服务以使配置生效。

4. 确认多线程复制是否已经启用：

SHOW VARIABLES LIKE 'slave_parallel_workers';

如果返回值大于0，则表示多线程复制已经开启，并且可以使用指定数量的线程来应用日志事件。

4 总结

上面提到了多种方案都是可以讨论，每个方法都有弊端和优势，根据实际情况进行选型。