MySQL-高可用篇-①读写分离

学习核心

• 读写分离核心（MySQL如何优化数据查询方案？）

  • 什么是读写分离？
  • 什么场景下需要读写分离？
  • 如何进行读写分离？主从复制核心、业务选型

• MySQL提供了哪些复制模式？分别对应的应用场景是什么？

  • 4种复制模式（对应业务场景）
    • 异步复制
    • 半同步复制
    • 多源复制
    • 复制延迟

• 如何避免主从复制延迟？

  • 注意对大事务的处理

    • 大事务处理：优化大事务处理性能（例如SQL优化等）、对大事务进行拆分（拆分为多个小事务后可并行处理）

  • 从机配置：版本升级，配置从机并行复制模式

  • 主从复制延迟监控

    • SHOW SLAVE STATUS可能并不准确
    • 引入心跳表定期更新时间，进而判断主从复制延迟时间

• 读写分离如何在业务中落地

学习资料

• MySQL技术文档学习（实战宝典）- 11、15、16、23

读写分离

读多写少：MySQL如何优化数据查询方案？

案例背景

​ 假设目前在某电商平台就职，公司面临双 11 大促，投入了大量营销费用用于平台推广，这带来了巨大的流量，如果作为订单系统的技术负责人，要怎么应对突如其来的读写流量呢？

​ 这是一个很典型的应用场景，常见的场景回答：通过 Redis 作为 MySQL 的缓存，然后当用户查看“订单中心”时，通过查询订单缓存，帮助 MySQL 抗住大部分的查询请求。

​ 上述缓存的引入是一个优化方向，但实际上深入思考并不完全妥善，因为应用缓存的原则之一是保证缓存命中率足够高，不然很多请求会穿透缓存，最终打到数据库上。然而在“订单中心”这样的场景中，每个用户的订单都不同，除非全量缓存数据库订单信息（又会带来架构的复杂度），不然缓存的命中率依旧很低。所以在这种场景下，缓存只能作为数据库的前置保护机制，但是还会有很多流量打到数据库上，并且随着用户订单不断增多，请求到 MySQL 上的读写流量会越来越多，当单台 MySQL 支撑不了大量的并发请求时，该怎么办？

案例分析

​ 互联网大部分系统的访问流量是读多写少，读写请求量的差距可能达到几个数量级（例如用户在京东上的商品的浏览量肯定远大于下单量），所以要考虑优化数据库来抗住高查询请求，首先要做的就是区分读写流量区，这样才方便针对读流量做单独扩展，这个过程就是流量的“读写分离”。读写分离是提升 MySQL 并发的首选方案，因为当单台 MySQL 无法满足要求时，就只能用多个具有相同数据的 MySQL 实例组成的集群来承担大量的读写请求

​ MySQL 做读写分离的前提，是把 MySQL 集群拆分成**“主 + 从”结构的数据集群**，这样才能实现程序上的读写分离，并且 MySQL 集群的主库、从库的数据是通过主从复制实现数据同步的。

​ 顺着结合”读写分离“概念核心，可以扩展很多衍生问题，例如“MySQL 集群如何实现主从复制？” 换一种问法就是“当提交一个事务到 MySQL 集群后，MySQL 都执行了哪些操作？以该问题为切入点，挖掘对 MySQL 集群主从复制原理的理解，然后再模拟一个业务场景，需设计出解决主从复制问题的架构设计方案。可以设置从以下几点切入：

• 掌握读多写少场景下的架构设计思路，知道缓存不能解决所有问题，**“读写分离”**是提升系统并发能力的重要手段；
• 深入了解数据库的主从复制，掌握其原理、问题，以及解决方案；
• 从实践出发，做到技术的认知抽象，从方法论层面来看设计；

案例解析：读写分离关联核心（主从复制、一主多从、主从复制延迟）

（1）主从复制

​ “MySQL 集群如何实现主从复制”、“当你提交一个事务到 MySQL 集群后，MySQL 集群都执行了哪些操作？” = 》问题核心：MySQL 的主从复制的过程是怎样的？（可参考下述MySQL复制架构进行理解，此处做简单概括）

​ MySQL的主从复制依赖于 binlog ，即记录 MySQL 上的所有变化并以二进制形式保存在磁盘上，复制的过程就是将 binlog 中的数据从主库传输到从库上。这个过程一般是异步的（基础方案：异步复制），即主库上执行事务操作的线程不会等待复制 binlog 的线程同步完成。 MySQL 集群的主从复制过程梳理成 3 个阶段：（Master-主库；Salve-从库）

• 写入 Binlog：Master写 binlog 日志，提交事务，并更新本地存储数据
• 同步 Binlog：把 binlog 复制到所有Salve上，每个Salve把 binlog 写到暂存日志中
• 回放 Binlog：在Salve上回放 binlog，并更新存储数据

​ 主从复制有不同模式，根据不同模式特点理解其模式内容。其过程详情分析如下（不同复制模式在于主从库的依赖性和交互的时间节点不同）：

• MySQL 主库在收到客户端提交事务的请求之后，会先写入 binlog，再提交事务，更新存储引擎中的数据，事务提交完成后，返回给客户端“操作成功”的响应
• 从库会创建一个专门的 I/O 线程，连接主库的 log dump 线程，来接收主库的 binlog 日志，再把 binlog 信息写入 relay log 的中继日志里，再返回给主库“复制成功”的响应
• 从库会创建一个用于回放 binlog 的线程，去读 relay log 中继日志，然后回放 binlog 更新存储引擎中的数据，最终实现主从的数据一致性

​ 在完成主从复制之后，就可以在写数据时只写主库，在读数据时只读从库，这样即使写请求会锁表或者锁记录，也不会影响读请求的执行

（2）一主多从

​ “一主多从”的部署方式：在读流量比较大的时候，可以通过部署多个从库共同承担读流量。在垂直电商项目中可以用该方式抵御较高的并发读流量。此外，从库也可以作为一个备库，以避免主库故障导致的数据丢失。

​ 扩展思考：那是不是在大促流量大时，是不是只要多增加几台从库，就可以抗住大促的并发读请求了？

​ 并不是，从库数量增加同样意味着主从复制压力增大。从库数量增加，从库连接上来的 I/O 线程也比较多，主库也要创建同样多的 log dump 线程来处理复制的请求，对主库资源消耗比较高，同时还受限于主库的网络带宽。且一旦主库宕机，如果选用异步复制的模式就会导致数据发生丢失（因为异步复制模式下MySQL并不会主库提交事务的线程并不会等待 binlog 同步到各从库，就返回客户端结果，一旦主库宕机，数据就会发生丢失）

​ 所以在实际使用中，一个主库一般跟 2～3 个从库（1 套数据库，1 主 2 从 1 备主），这就是一主多从的 MySQL 集群结构。

​

​ 那么除此之外，MySQL还有哪些模型？（主从复制的4种模型）

• 异步复制：事务线程完全不等待从库的复制成功响应
• 同步复制：事务线程要等待所有从库的复制成功响应
• 半同步复制：MySQL 5.7 版本之后增加的一种复制方式，介于同步、异步两者之间，事务线程不用等待所有的从库复制成功响应，只要一部分复制成功响应回来就行。比如一主二从的集群，只要数据成功复制到任意一个从库上，主库的事务线程就可以返回给客户端
  • 有损半同步复制：主库事务提交后等待ACK消息
  • 无损半同步复制：主库的 WAIT ACK 发生在事务提交之前
• 多源复制：从库从多个主库同步数据
• 复制延迟：从库延迟同步主库数据

（3）从架构上解决主从复制延迟

​ 场景：主从延迟影响评论读取的实时性：在电商平台，每次用户发布商品评论时，都会先调用评论审核，目的是对用户发布的商品评论进行如言论监控、图片鉴黄等操作。评论在更新完主库后，商品发布模块会异步调用审核模块，并把评论 ID 传递给审核模块，然后再由评论审核模块用评论 ID 查询从库中获取到完整的评论信息。此时如果主从数据库存在延迟，在从库中就会获取不到评论信息，整个流程就会出现异常（主从复制延迟导致的查询异常）。可以通过一些方案来进行补充

• 使用数据冗余（需关注异步调用从库时的调用参数大小，其关系到网络通信）

​ 可以在异步调用审核模块时，不仅仅发送商品 ID，而是发送审核模块需要的所有评论信息，借此避免在从库中重新查询数据（这个方案简单易实现，推荐选择）。但要注意每次调用的参数大小，过大的消息会占用网络带宽和通信时间。

• 使用缓存解决（需注意数据库和缓存不一致的情况）

​ 可以在写入数据主库的同时，把评论数据写到 Redis 缓存里，这样其他线程再获取评论信息时会优先查询缓存，也可以保证数据的一致性。不过这种方式会带来缓存和数据库的一致性问题，比如两个线程同时更新数据，有可能出现数据库和缓存值不一致的异”异常情况“，操作步骤如下：

	线程A	线程B
步骤【1】	更新数据库 100
步骤【2】		更新数据库 200
步骤【3】		更新缓存值 200
步骤【4】	更新缓存值 100

​ 通过缓存解决MySQL主从复制延迟时，可能会出现数据库与缓存值不一致的情况，需结合实际场景应用进行选择

• 直接查询主库（需关注主库查询的访问压力）

​ 该方案在使用时一定要谨慎，要提前明确查询的数据量不大，不然会出现主库写请求锁行，影响读请求的执行，最终对主库造成比较大的压力。

（4）工程代码设计上，如何实现主库和从库的数据访问呢？

​ 当 MySQL 做了主从分离后，对于数据库的使用方式就发生了变化，以前只需要使用一个数据库地址操作数据库，现在却要使用一个主库地址和多个从库地址，并且还要区分写入操作和查询操作。

方案1：代码层实现数据库分析

​ 对于读写分离最简单的做法是：通过代码控制读写，提前把所有数据源配置在工程中，每个数据源对应一个主库或者从库，然后改造代码，在代码逻辑中进行判断，将 SQL 语句发送给某一个指定的数据源来处理。这个方案简单易实现，但是SQL路由规则侵入代码，对于复杂工程而言往往不利于代码维护。

方案2：独立部署的代理中间件

​ 例如MyCat，这一类中间件部署在独立的服务器上，一般使用标准的 MySQL 通信协议，可以代理多个数据库。

​ 优点：隔离底层数据库与上层应用的访问复杂度，比较适合有独立运维团队的公司选型；

​ 缺点：所有的 SQL 语句都要跨两次网络传输，有一定的性能损耗，且运维中间件是一个专业且复杂的工作，需要一定的技术沉淀；

（5）MySQL数据复制的抽象

​ 将MySQL数据复制技术抽象成一个更高层次的理论体系。以Raft 协议为例，其内部是通过日志复制同步的方式来实现共识的，例如在领导者选举成功后，它就会开始接收客户端的请求，此时每一个客户端请求都将被解析成一条指令日志，然后并行地向其他节点发起通知，要求其他节点复制这个日志条目，并最终在各个节点中回放日志，实现共识

运行机制

​ 如果客户端将要执行的命令发送给集群中的一台服务器，那么这台服务器就会以日志的方式记录这条命令，然后将命令发送给集群内其他的服务，并记录在其他服务器的日志文件中，注意，只要保证各个服务器上的日志是相同的，并且各服务器都能以相同的顺序执行相同的命令的话，那么集群中的每个节点的执行结果也都会是一样的。

​ 这种数据共识的机制就叫复制状态机，目的是通过日志复制和回放的方式来实现集群中所有节点内的状态一致性。

​ 其实 MySQL 中的主从复制，通过 binlog 操作日志来实现从主库到从库的数据复制的，就是应用了这种复制状态机的机制，所以这种方式不是 MySQL 特有的。除了 Raft 协议以外，在 Redis Cluster 中也用到了 backlog 来实现主从节点的数据复制，其方式和 MySQL 一模一样。可以理解为几乎所有的存储系统或数据库，基本都用了这样一套方法来解决数据复制和备份恢复等问题。

复制模式

​ 在前面的模块学习中，介绍了 MySQL 架构中的表结构设计、索引设计。对业务开发场景来说，掌握这些内容就能很好地面向业务逻辑进行编码工作了。但是业务需要上线，所以除了表和索引的结构设计之外，还要做好高可用的设计。因为在真实的生产环境下，如果发生物理硬件故障，没有搭建高可用架构，会导致业务完全不可用。而这在海量并发访问的互联网业务中完全不敢想象，所以除了业务架构，还要做好可用性的架构设计。基于此，需要进一步掌握MySQL 高可用架构中最基础、最为核心的内容：MySQL 复制（Replication）

1.MySQL复制架构

​ 数据库复制本质上就是数据同步。MySQL 数据库是基于二进制日志（binary log）进行数据增量同步，而二进制日志记录了所有对于 MySQL 数据库的修改操作。

​ 在默认 ROW 格式二进制日志中，一条 SQL 操作影响的记录会被全部记录下来，比如一条 SQL语句更新了三行记录，在二进制日志中会记录被修改的这三条记录的前项（before image）和后项（after image）。MySQL中使用SHOW BINLOG EVENTS 查看某个二进制日志文件的内容。

# 1.查看binlog日志目录
SHOW VARIABLES LIKE 'log_bin'; # 如果未开启显示OFF，如果开启了则显式指定的binlog目录
SHOW VARIABLES LIKE 'datadir'; # 查看binlog存放路径（/opt/homebrew/var/mysql/）

# 2.开启binlog（需要修改MySQL配置文件：my.ini或者my.cnf）:mac 中通过 which mysql 查看mysql安装目录
# mac 中查看加载my.cnf的默认路径
mysql --verbose --help | grep my.cnf
-- output：/etc/my.cnf /etc/mysql/my.cnf /opt/homebrew/etc/my.cnf ~/.my.cnf

# 查看到/opt/homebrew/etc/my.cnf文件才存在（使用brew安装的mysql服务），修改配置添加下述参数
[mysqld]
# log_bin
-- log-bin = mysql-bin #开 启binlog -- 指定为mysql-bin，存放在默认安装路径，显示log_bin状态为ON
log-bin = mysql-bin # 开启binlog，可以指定binlog的存放目录
binlog-format = ROW # 选择row模式
server_id = 1       # 配置mysql replication需要定义，不能和canal的slaveId重复

# 重启mysql后通过客户端查看bin_log是否正常启动
brew services stop  mysql@5.7 / brew services start mysql@5.7
brew services restart  mysql@5.7

​ 查看MAC下的binlog存放路径（/opt/homebrew/var/mysql/），该目录下有一个文件【hostname-bin.index】文件

​ 默认的binlog日志格式是ROW，基于ROW格式：一条SQL操作影响的记录会被全部记录下来，比如一条 SQL语句更新了三行记录，在二进制日志中会记录被修改的这三条记录的前项（before image）和后项（after image）

binlog记录了什么？

案例准备（t_student）

DROP TABLE IF EXISTS `t_student`;
CREATE TABLE `t_student` (
  `number` int(11) NOT NULL AUTO_INCREMENT COMMENT '学号',
  `name` varchar(5) DEFAULT NULL COMMENT '姓名',
  `major` varchar(30) DEFAULT NULL COMMENT '专业',
  PRIMARY KEY (`number`)
) ENGINE=InnoDB AUTO_INCREMENT=20240104 DEFAULT CHARSET=utf8 COMMENT='学生信息表';

INSERT INTO `t_student` (`number`, `name`, `major`) VALUES (20240101, '小红', '软件学院');
INSERT INTO `t_student` (`number`, `name`, `major`) VALUES (20240102, '小白', '计算机科学与技术1');
INSERT INTO `t_student` (`number`, `name`, `major`) VALUES (20240103, '小赵', '计算机科学与技术2');
INSERT INTO `t_student` (`number`, `name`, `major`) VALUES (20240104, '中钱', '计算机科学与技术3');
INSERT INTO `t_student` (`number`, `name`, `major`) VALUES (20240105, '中孙', '计算机科学与技术1');
INSERT INTO `t_student` (`number`, `name`, `major`) VALUES (20240106, '中李', '计算机科学与技术2');
INSERT INTO `t_student` (`number`, `name`, `major`) VALUES (20240107, '大伍', '计算机科学与技术3');
INSERT INTO `t_student` (`number`, `name`, `major`) VALUES (20240108, '大吴', '计算机科学与技术1');
INSERT INTO `t_student` (`number`, `name`, `major`) VALUES (20240109, '大黄', '计算机科学与技术2');
INSERT INTO `t_student` (`number`, `name`, `major`) VALUES (20240110, '大白', '计算机科学与技术3');

删除数据

# 1.删除数据
delete from t_student where name like '小_';
# 2.查看二进制文件内容
SHOW BINLOG EVENTS; # 查看二进制文件内容
SHOW BINLOG EVENTS IN 'mysql-bin.000001'; # 查看某个二进制文件内容

​ 可通过mysql自带的mysqlbin指令解析二进制日志，观察到更详细的记录信息：

cd /opt/homebrew/var/mysql/
mysqlbinlog -vv mysql-bin.000001

​ 从binlog日志中可以跟踪到被删除记录的完整信息，包括每个列的属性、类型、是否允许为null值等

新增数据

INSERT INTO `t_student` (`number`, `name`, `major`) VALUES (20240111, '张三', '信息学院');
INSERT INTO `t_student` (`number`, `name`, `major`) VALUES (20240112, '李四', '信息学院');

修改数据

update t_student set major = '计算机科学与技术1' where number = '20240112';

​ binlog日志中记录了被修改记录的修改前后的完整信息

MySQL复制架构

（1）MySQL复制概念核心

​ 基于二进制日志的基础上，MySQL 数据库就可以通过数据复制技术实现数据同步。而数据复制的本质就是把一台 MySQL 数据库上的变更同步到另一台 MySQL 数据库上。参考图示分析当前 MySQL 数据库的复制架构：

​ 在MySQL复制中，一台是数据库的角色是 Master（也叫 Primary），剩下的服务器角色是 Slave（也叫 Standby）：

• Master 服务器会把数据变更产生的二进制日志通过 Dump 线程发送给 Slave 服务器；
• Slave 服务器中的 I/O 线程负责接受二进制日志，并保存为中继日志；
• SQL/Worker 线程负责并行执行中继日志，即在 Slave 服务器上回放 Master 产生的日志；

​ master发送日志=》salve接收并保存日志=》在salve上回放日志

得益于二进制日志，MySQL 的复制相比其他数据库，如 Oracle、PostgreSQL 等，非常灵活，用户可以根据自己的需要构建所需要的复制拓扑结构，比如：

（2）MySQL复制配置

搭建 MySQL 复制实现非常简单，基本步骤如下：

• 【1】创建复制所需的账号和权限；

• 【2】从 Master 服务器拷贝一份数据，可以使用逻辑备份工具 mysqldump、mysqlpump，或物理备份工具 Clone Plugin；

• 【3】通过命令 CHANGE MASTER TO 搭建复制关系；

• 【4】通过命令 SHOW SLAVE STATUS 观察复制状态；

虽然 MySQL 复制原理和实施非常简单，但在配置时却容易出错，务必在配置文件中设置如下配置：

gtid_mode = on
enforce_gtid_consistency = 1
binlog_gtid_simple_recovery = 1
relay_log_recovery = ON
master_info_repository = TABLE 
relay_log_info_repository = TABLE

​ 上述设置都是用于保证 crash safe，即无论 Master 还是 Slave 宕机，当它们恢复后，连上主机后，主从数据依然一致，不会产生任何不一致的问题。

​ 可能经常会遇到一些问题：例如MySQL会存在主从数据不一致的情况，请确认上述参数都已配置，否则任何的不一致都不是 MySQL 的问题，而是使用 MySQL 的打开方式不对。

（3）MySQL复制类型及应用选项

​ MySQL 复制可以分为以下几种类型：

​ 默认的复制是异步复制，需要了解一下每种复制类型，以及它们在业务中的选型，以便在业务做正确的选型

异步复制

​ 在异步复制（async replication）中，Master 不用关心 Slave 是否接收到二进制日志，所以 Master 与 Slave 没有任何的依赖关系。可以认为 Master 和 Slave 是分别独自工作的两台服务器，数据最终会通过二进制日志达到一致。异步复制的性能最好，因为它对数据库本身几乎没有任何开销，除非主从延迟非常大，Dump Thread 需要读取大量二进制日志文件。

​ 如果业务对于数据一致性要求不高，当发生故障时，能容忍数据的丢失，甚至大量的丢失，推荐用异步复制，这样性能最好（比如像微博这样的业务，虽然它对性能的要求极高，但对于数据丢失，通常可以容忍）。但往往核心业务系统最关心的就是数据安全，比如监控业务、告警系统

半同步复制

​ 半同步复制要求 Master 事务提交过程中，至少有 N 个 Slave 接收到二进制日志，这样就能保证当 Master 发生宕机，至少有 N 台 Slave 服务器中的数据是完整的。

​ 半同步复制并不是 MySQL 内置的功能，而是要安装半同步插件，并启用半同步复制功能，设置 N 个 Slave 接受二进制日志成功，比如：

plugin-load="rpl_semi_sync_master=semisync_master.so;rpl_semi_sync_slave=semisync_slave.so"
rpl-semi-sync-master-enabled = 1
rpl-semi-sync-slave-enabled = 1
rpl_semi_sync_master_wait_no_slave = 1

上面的配置中：

• 第 1 行要求数据库启动时安装半同步插件；
• 第 2、3 行表示分别启用半同步 Master 和半同步 Slave 插件；
• 第 4 行表示半同步复制过程中，提交的事务必须至少有一个 Slave 接收到二进制日志;

​ 在半同步复制中，有损半同步复制是 MySQL 5.7 版本前的半同步复制机制，这种半同步复制在Master 发生宕机时，Slave 会丢失最后一批提交的数据，若这时 Slave 提升（Failover）为Master，可能会发生已经提交的事情不见了，发生了回滚的情况。

有损半同步复制/无损半同步复制原理如下图所示：

​ 有损半同步是在 Master 事务提交后，即步骤 4 后，等待 Slave 返回 ACK，表示至少有 Slave 接收到了二进制日志，如果这时二进制日志还未发送到 Slave，Master 就发生宕机，则此时 Slave 就会丢失 Master 已经提交的数据

​ MySQL 5.7 的无损半同步复制解决了这个问题，无损半同步复制 WAIT ACK 发生在事务提交之前，这样即便 Slave 没有收到二进制日志，但是 Master 宕机了，由于最后一个事务还没有提交，所以本身这个数据对外也不可见，不存在丢失的问题

​ 所以，对于任何有数据一致性要求的业务，如电商的核心订单业务、银行、保险、证券等与资金密切相关的业务，务必使用无损半同步复制。这样数据才是安全的、有保障的、即使发生宕机，从机也有一份完整的数据

多源复制

​ 无论是异步复制还是半同步复制，都是 1 个 Master 对应 N 个 Slave。其实 MySQL 也支持 N 个 Master 对应 1 个 Slave，这种架构就称之为多源复制。多源复制允许在不同 MySQL 实例上的数据同步到 1 台 MySQL 实例上，方便在 1 台 Slave 服务器上进行一些统计查询，如常见的 OLAP 业务查询。参考下述图示，主库分别为库存库、订单库、供应商库，通过多源复制将这些主库内容同步到一台从库（MySQL实例上），在MySQL8.0版本中提供了复杂的SQL能力以支持对业务进行深度的数据分析和挖掘。

延迟复制

​ 前面介绍的复制架构，Slave 在接收二进制日志后会尽可能快地回放日志，这样是为了避免主从之间出现延迟。而延迟复制却允许Slave 延迟回放接收到的二进制日志，为了避免主服务器上的误操作，马上又同步到了从服务器，导致数据完全丢失。

​ 可以通过以下命令设置延迟复制：

CHANGE MASTER TO master_delay = 3600 # 人为设置了 Slave 落后 Master 服务器1个小时

​ 延迟复制在数据库的备份架构设计中非常常见，比如可以设置一个延迟一天的延迟备机，这样本质上说，用户可以有 1 份 24 小时前的快照。那么当线上发生误操作，如 DROP TABLE、DROP DATABASE 这样灾难性的命令时，用户有一个 24 小时前的快照，数据可以快速恢复。对金融行业来说，延迟复制是备份设计中，必须考虑的一个架构部分。

各项复制模式总结对比

复制类型	概念核心	业务场景
异步复制	Master、Salve分别独自工作，不相互依赖	业务对于数据一致性要求不高，当发生故障时，能容忍数据的丢失
半同步复制	有损半同步复制：Master事务提交后等待Slave返回ACK 无损半同步复制：Master的WAIT ACK发生在事务提交前	对于任何有数据一致性要求的业务，如电商的核心订单业务、银行、保险、证券等与资金密切相关的业务，务必使用无损半同步复制 以确保数据安全，即便发生宕机，从机也有一份完整的数据
多源复制	允许Slave从多台Master服务器上同步数据	常见的 OLAP 业务查询（从多台主库同步数据，从库进行业务分析提供查询）
延迟复制	允许Slave 延迟回放接收到的二进制日志，进而避免主服务器上的误操作	常用作设置延迟备机，当线上发生误操作时，数据可以快速恢复

2.主从复制延迟问题

逻辑日志的优缺点

​ MySQL 复制基于的二进制日志 binlog 是一种逻辑日志，其写入的是每个事务中已变更的每条记录的前项、后项。有了每条记录的变化内容，用户可以方便地通过分析 MySQL 的二进制日志内容，准时地将 MySQL 中的数据同步到异构的数据平台，如 HBase、ES、Hive 等大数据平台。

​ 逻辑日志简单易懂，方便数据之间的同步，但它的缺点是：事务不能太大，否则会导致二进制日志非常大，一个大事务的提交会非常慢，进而导致主从复制延迟

​ 假设有个 DELETE 删除操作，删除当月数据，由于数据量可能有 1 亿条记录，可能会产生 100G 的二进制日志，则这条 SQL 在提交时需要等待 100G 的二进制日志写入磁盘，如果二进制日志磁盘每秒写入速度为 100M/秒，至少要等待 1000 秒才能完成这个事务的提交。

​ 因此，在MySQL中要对大事务特别对待：主要是用于控制二进制日志的大小

• 操作转化：设计时，把 DELETE 删除操作转化为 DROP TABLE/PARTITION 操作；

  • 即在设计时把流水或日志类的表按时间分表或者分区，基于此在删除时二进制日志内容就是一条 DROP TABLE/PARITION 的 SQL，进而提升binlog写入速度

• 大事化小：业务设计时，把大事务拆成小事务（进而可借助多线程并发操作，进一步提升删除效率）

  • 如果设计时没有进行分表或分区，可以将进行拆分：例如将一个大的delete操作拆分为每次删除1000条记录的小操作

  DELETE FROM ...
  WHEREE time between ... and ...
  LIMIT 1000;
  

大事务问题导致的主从复制延迟问题

​ 大事务的产生：一来是业务需求，二来可能是主服务上没有创建索引（SQL存在优化空间），导致一个简单的操作时间变得非常长。

​ 主从复制问题：一个大事务在主服务器上运行了 30 分钟，那么在从服务器上也需要运行 30 分钟。在从机回放这个大事务的过程中，主从服务器之间的数据就产生了延迟；

​ 除了上述场景中对大事务的特殊处理可以避免主从复制延迟，还可以通过设置复制回放相关的配置参数来实现主从复制延迟的优化

主从复制延迟优化

​ 要彻底避免 MySQL 主从复制延迟，数据库版本至少要升级到 5.7，因为之前的MySQL 版本从机回放二进制都是单线程的（5.6 是基于库级别的单线程）。

从 MySQL 5.7 版本开始，MySQL 支持了从机多线程回放二进制日志的方式，通常把它叫作“并行复制”，官方文档中称为“Multi-Threaded Slave（MTS）”。

MySQL 的从机并行复制有两种模式。

• COMMIT ORDER： 主机怎么并行，从机就怎么并行
• WRITESET： 基于每个事务，只要事务更新的记录不冲突，就可以并行

​ COMMIT ORDER 模式的从机并行复制，从机完全根据主服务的并行度进行回放。理论上来说，主从延迟极小。但如果主服务器上并行度非常小，事务并不小，比如单线程每次插入 1000 条记录，则从机单线程回放，也会存在一些复制延迟的情况。

​ 而 WRITESET 模式是基于每个事务并行，如果事务间更新的记录不冲突，就可以并行。还是以“单线程每次插入 1000 条记录”为例，如果插入的记录没有冲突，比如唯一索引冲突，**那么虽然主机是单线程，但从机可以是多线程并行回放！**所以在 WRITESET 模式下，主从复制几乎没有延迟。

启用 WRITESET 复制模式的配置：

binlog_transaction_dependency_tracking = WRITESET
transaction_write_set_extraction = XXHASH64
slave-parallel-type = LOGICAL_CLOCK
slave-parallel-workers = 16

​ 因为主从复制延迟会影响到后续高可用的切换，以及读写分离的架构设计，所以在真实的业务中，要对主从复制延迟进行监控。

主从复制延迟监控

（1）SHOW SLAVE STATUS

​ 通过命令 SHOW SLAVE STATUS，其中的 Seconds_Behind_Master 可以查看复制延迟。但是，Seconds_Behind_Master 不准确！用于严格判断主从延迟的问题并不合适， 有这样三个原因。

• 其计算规则是（当前回放二进制时间 - 二进制日志中的时间），如果 I/O 线程有延迟，那么 Second_Behind_Master 为 0，这时可能已经落后非常多了，例如存在有大事务的情况下；
• 对于级联复制，最下游的从服务器延迟是不准确的，因为它只表示和上一级主服务器之间的延迟；
• 若主从时区不一样，那么 second_behind_master 也不准确；

总的来说，线上业务通过 Seconds_Begind_Master 值观察主从复制延迟并不准确，需要额外引入一张表，才能真正监控主从的复制延迟情况。

（2）心跳表

​ 想要实时准确地监控主从复制延迟，可以在主服务器上引入一张心跳表 heartbeat，用于定期更新时间（比如每 3 秒一次）。于主从复制机制，主机上写入的时间会被复制到从机，这时对于主从复制延迟的判断可以根据如下规则：

主从延迟 = 从机当前时间 - 表 heartbeat 中的时间

这可以很好解决上述 Seconds_Behind_Master 值存在的问题。表 heartbeat 和定期更新时间可以根据类似的设计：

# 创建DBA数据库，在其下创建心跳表heartbeat用于记录当前时间
USE DBA;
CREATE TABLE heartbeat (
  server-uuid VARCHAR(36) PRIMARY KEY,
  ts TIMESTAMP(6) NOT NULL
);
# REPLACE 语句用于定期更新当前时间，并存入到表 heartbeat，表 heartbeat 在正常运行情况下只有一条记录
REPLACE INTO heartbeat(@@server_uuid, NOW())

​ 定期执行 REPLACE 语句可以使用定期的脚本调度程序，也可以使用 MySQL自带的事件调度器（event scheduler），如：

CREATE EVENT e_heartbeat
ON SCHEDULE
    EVERY 3 SECOND
DO
BEGIN
    REPLACE INTO DBA.heartbeat VALUES (@@server_uuid,NOW())
END

​ 基于上述内容完成对主从复制的配置，并对复制延迟进行监控，这时可以进一步设计读写分离的业务架构

3.读写分离设计

​ 读写分离设计：把对数据库的读写请求分布到不同的数据库服务器上。对于写入操作只能请求主服务器，而对读取操作则可以将读取请求分布到不同的从服务器上。以此有效降低主服务器的负载，提升从服务器资源利用率，从而进一步提升整体业务的性能。常见的业务读写分离的架构设计如下图所示

​ 引入Load Balance负载均衡组件，Server对于数据库的请求不需要关心后面有多少个从机，只需要访问Load Balance服务器的IP或者域名即可。通过配置 Load Balance 服务，还能将读取请求平均或按照权重平均分布到不同的从服务器，可以根据架构的需要做灵活的设计。

读写分离设计的兜底

​ 读写分离设计的前提是从机不能落后主机很多，最好是能准实时数据同步，务必一定要开始并行复制，并确保线上已经将大事务拆成小事务

​ 若是一些报表类的查询，只要不影响最终结果，业务是能够容忍一些延迟的。但无论如何，一定要在线上数据库环境中做好主从复制延迟的监控。如果真的由于一些不可预知的情况发生，比如一个初级 DBA 在主机上做了一个大事务操作，导致主从延迟发生，如何做好读写分离设计的兜底呢？

​ 解决方案：可以通过设置较小比例的读取请求访问主库，其他从库按照实际配置承担相应的比例。

​ 如果发生严重的主从复制情况，可以设置从机权重为 0，将主机权重设置为 100%，进而避免突发的异常导致数据延迟而影响业务

4.读写分离如何在业务中落地

读写分离适用场景（读多写少）

读写分离场景适用于读多写少场景，以支撑高并发读，分担主库压力

​ 互联网大部分业务场景都是读多写少的，对于电商等典型业务，读和写的请求对比可能差了不止一个数量级。为了不让数据库的读成为业务瓶颈，同时也为了保证写库的成功率，一般会采用读写分离的技术来保证。

​ 读写分离：分离读库和写库操作

• 从 CRUD 的角度理解：主数据库处理新增、修改、删除等事务性操作，而从数据库处理 SELECT 查询操作
• 具体的实现上：可以有一主一从（一个主库配置一个从库）；也可以一主多从（一个主库配置多个从库），读操作通过多个从库进行以支撑更高的读并发压力

​ 读写分离的实现是把访问的压力从主库转移到从库，特别在单机数据库无法支撑并发读写，并且业务请求大部分为读操作的情况下。如果业务特点是写多读少，比如一些需要动态更新的业务场景，应用读写分离就不合适了，由于 MySQL InnoDB 等关系型数据库对事务的支持，使得写性能不会太高，一般会选择更高性能的 NoSQL 等存储来实现。

读写分离原理（基于主从复制架构实现）

（1）binlog日志

​ MySQL InnoDB 引擎的主从复制，是通过二进制日志 binlog 来实现。除了数据查询语句 select 以外，binlog 日志记录了其他各类数据写入操作，包括 DDL 和 DML 语句。binlog 有三种格式：Statement、Row 及 Mixed

• Statement 格式（基于 SQL 语句的复制）：记录的是原SQL语句，binlog 会记录每一条修改数据的 SQL 操作，从库拿到后在本地进行回放即可

  • 缺陷：如果SQL语句中使用函数例如now()则可能出现主从复制不一致的情况（主从库执行同样的SQL语句却拿到不一样的结果，进而导致主从复制不一致）
  • 优点：binlog存储的是原SQL语句，占用空间比较小

• Row 格式（基于行信息复制）：Row 格式以行为维度，记录每一行数据修改的细节，不记录执行 SQL 语句的上下文相关的信息，仅记录行数据的修改。

  • 缺陷：存储占用空间可能会很多，假设有一个批量更新操作，会以行记录的形式来保存二进制文件，这样可能会产生大量的日志内容
  • 优点：能够记录数据的完整信息，确保主从库数据的一致性

• Mixed 格式（混合模式复制）：Mixed 格式，就是 Statement 与 Row 的结合，在这种方式下，不同的 SQL 操作会区别对待。比如一般的数据操作使用 row 格式保存，有些表结构的变更语句，使用 statement 来记录

（2）主从复制过程

• 写入binlog：主库将变更写入 binlog 日志，从库连接到主库之后，主库会创建一个log dump 线程，用于发送 bin log 的内容
• 同步binlog：从库开启同步以后，会创建一个 IO 线程用来连接主库，请求主库中更新的 bin log，I/O 线程接收到主库 binlog dump 进程发来的更新之后，保存在本地 relay 日志中
• 回放binlog：从库中有一个 SQL 线程负责读取 relay log 中的内容，同步到数据库存储中，即在自己本地进行回放，最终保证主从数据的一致性

读写分离场景问题

（1）主从复制下的延时问题

​ 由于主库和从库是两个不同的数据源，主从复制过程会存在一个延时（当主库有数据写入之后，同时写入 binlog 日志文件中，然后从库通过 binlog 文件同步数据，由于需要额外执行日志同步和写入操作，这期间会有一定时间的延迟）。特别是在高并发场景下，刚写入主库的数据是不能马上在从库读取的，要等待几十毫秒或者上百毫秒以后才可以。

​ 在某些对一致性要求较高的业务场景中，这种主从导致的延迟会引起一些业务问题，比如订单支付，付款已经完成，主库数据更新了，从库还没有，这时候去从库读数据，会出现订单未支付的情况，在业务中是不能接受的。

为了解决主从同步延迟的问题，通常有以下几个方法。

• 敏感业务强制读主库（数据冗余）

​ 在开发中有部分业务需要写库后实时读数据，这一类操作通常可以通过强制读主库来解决。例如如果涉及到调用，则在业务逻辑处理时多冗余一些数据传递（注意数据传递的大小对网络性能的影响）来规避对从库的查询

• 关键业务不进行读写分离

​ 对一致性不敏感的业务，比如电商中的订单评论、个人信息等可以进行读写分离，对一致性要求比较高的业务，比如金融支付，不进行读写分离，避免延迟导致的问题

• 缓存

​ 一些场景中可以将数据更新到缓存中，读取的时候优先从缓存中读取，但要注意并发场景中可能会出现数据库和缓存中的数据不一致的情况

（2）主从复制如何避免丢数据

​ 这个概念主要从主从复制的4种模式的业务选择进行切入。主从复制丢数据的场景指的是：假设假设在数据库主从同步时，主库宕机，并且数据还没有同步到从库，就会出现数据丢失和不一致的情况。（虽然这是一种很极端的情况，但是MySQL还是将其纳入考虑范围，并提供了相应的方案选择）。MySQL 数据库主从复制有异步复制、半同步复制和全同步复制的方式

• 异步复制（主从之间无依赖）

​ 异步复制模式下，主库在接受并处理客户端的写入请求时，直接返回执行结果，不关心从库同步是否成功，这样就会存在上面说的问题，主库崩溃以后，可能有部分操作没有同步到从库，出现数据丢失问题。

• 半同步复制（半同步有损复制、半同步无损复制）
  • 半同步有损复制：WAIT ACK 发生在COMMIT之后，一旦主机在COMMIT一个事务后宕机，无法正常接收ACK信号，从库则无法正常回放这个事务，就会导致数据丢失而引发数据不一致
  • 半同步无损复制：WAIT ACK 发生在COMMIT之前，计算主机在任意时间节点宕机，从库不会处理这个事务，这个事务也还没有COMMIT，数据对外是不可见的，也就没有数据丢失一说

​ 在半同步复制模式下，主库需要等待至少一个从库完成同步之后，才完成写操作。主库在执行完客户端提交的事务后，从库将日志写入自己本地的 relay log 之后，会返回一个响应结果给主库，主库确认从库已经同步完成，才会结束本次写操作。相对于异步复制，半同步复制提高了数据的安全性，避免了主库崩溃出现的数据丢失，但是同时也增加了主库写操作的耗时。

• 全同步复制

​ 全同步复制指的是在多从库的情况下，当主库执行完一个事务，需要等待所有的从库都同步完成以后，才完成本次写操作。全同步复制需要等待所有从库执行完对应的事务，所以整体性能是最差的。

​