京东二面：MySQL 主从延迟、读写分离 7 种解决方案！

迈不过友情╰ 2023-09-30 12:16 1阅读 0赞

我们都知道互联网数据有个特性，大部分场景都是 `读多写少`，比如：微博、微信、淘宝电商，按照 `二八原则`，读流量占比甚至能达到 90%

结合这个特性，我们对底层的数据库架构也会做相应调整。采用 `读写分离`

![7cf4f33d7a91bb9d1ad2789321632f2e.png][]

**处理过程：**

*  客户端会集成 SDK，每次执行 SQL 时，会判断是 `写` 或 `读` 操作
 *  如果是 `写` SQL，请求会发到 `主库`
 *  主数据库执行SQL，事务提交后，会生成 `binlog` ，并同步给 `从库`
 *  `从库` 通过 SQL 线程回放 `binlog` ，并在从库表中生成相应数据
 *  如果是 `读` SQL，请求会通过 `负载均衡` 策略，挑选一个 `从库` 处理用户请求

看似非常合理，细想却不是那么回事

`主库` 与 `从库` 是采用异步复制数据，如果这两者之间数据还没有同步怎么办？

主库刚写完数据，从库还没来得及拉取最新数据，`读` 请求就来了，给用户的感觉，`数据丢了？？？`

![eded9fff9d17593a08ba08ae95ed9ceb.png][]

针对这个问题，今天，我们就来探讨下有什么解决方案？

####  ####

#### 一、强制走主库 ####

针对不用的业务诉求，区别性对待

**场景一：**

如果是对数据的 `实时性` 要求不是很高，比如：大V有千万粉丝，发布一条微博，粉丝晚几秒钟收到这条信息，并不会有特别大的影响。这时，可以走 `从库`。

**场景二：**

如果对数据的 `实时性` 要求非常高，比如金融类业务。我们可以在客户端代码标记下，让查询强制走主库。

#### 二、从库延迟查询 ####

由于主从库之间数据同步需要一定的时间间隔，那么有一种策略是延迟从从库查询数据。

比如：

select sleep(1)
    select * from order where order_id=11111;

在正式的业务查询时，先执行一个sleep 语句，给从库预留一定的数据同步缓冲期。

因为是采用一刀切，当面对高并发业务场景时，性能会下降的非常厉害，一般不推荐这个方案。

#### 三、判断主从是否延迟？决定选主库还是从库 ####

**方案一：**

在从库 执行 命令 `show slave status`

查看 `seconds_behind_master` 的值，单位为秒，如果为 0，表示主备库之间无延迟

**方案二：**

比较主从库的文件点位

还是执行 `show slave status`，响应结果里有截个关键参数

*  Master\_Log\_File 读到的主库最新文件
 *  Read\_Master\_Log\_Pos 读到的主库最新文件的坐标位置
 *  Relay\_Master\_Log\_File 从库执行到的最新文件
 *  Exec\_Master\_Log\_Pos 从库执行到的最新文件的坐标位置

两两比较，上面的参数是否相等

**方案三：**

比较 GTID 集合

*  Auto\_Position=1 主从之间使用 GTID 协议
 *  Retrieved\_Gtid\_Set 从库收到的所有binlog日志的 GTID 集合
 *  Executed\_Gtid\_Set 从库已经执行完成的 GTID 集合

比较 `Retrieved_Gtid_Set` 和 `Executed_Gtid_Set` 的值是否相等

在执行业务SQL操作时，先判断从库是否已经同步最新数据。从而决定是操作主库，还是操作从库。

**缺点：**

无论采用上面哪一种方案，如果主库的写操作频繁不断，那么从库的值永远跟不上主库的值，那么读流量永远是打在了主库上。

**针对这个问题，有什么解决方案？**

这个问题跟 MQ消息队列 既要求高吞吐量又要保证顺序是一样的，从全局来看确实无解，但是缩小范围就容易多了，我们可以保证一个分区内的消息有序。

回到 `主从库` 之间的数据同步问题，从库查询哪条记录，我们只要保证之前对应的写binglog已经同步完数据即可，可以不用管主从库的所有的事务binlog 是否同步。

问题是不是一下简单多了

![46caecd0a46f3c1ffb73d4eb6ab14c0c.png][]

#### 四、从库节点判断主库位点 ####

在从库执行下面命令，返回是一个正整数 M，表示从库从参数节点开始执行了多少个事务

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

*  file 和 pos 表示主库上的文件名和位置
 *  timeout 可选， 表示这个函数最多等待 N 秒

**缺点：**

`master_pos_wait` 返回结果无法与具体操作的数据行做关联，所以每次接收`读请求`时，从库还是无法确认是否已经同步数据，方案实用性不高。

#### 五、比较 GTID ####

执行下面查询命令

*  阻塞等待，直到从库执行的事务中包含 gtid\_set，返回 0
 *  超时，返回 1

select wait_for_executed_gtid_set(gtid_set, 1);

> MySQL 5.7.6 版本开始，允许在执行完更新类事务后，把这个事务的 GTID 返回给客户端。具体操作，将参数`session_track_gtids` 设置为`OWN_GTID`，调用 API 接口`mysql_session_track_get_first` 返回结果解析出 GTID

**处理流程：**

*  发起 `写` SQL 操作，在主库成功执行后，返回这个事务的 GTID
 *  发起 `读` SQL 操作时，先在从库执行 `select wait_for_executed_gtid_set (gtid_set, 1)`
 *  如果返回 0，表示已经从库已经同步了数据，可以在从库执行 `查询` 操作
 *  否则，在主库执行 `查询` 操作

**缺点：**

跟上面的 `master_pos_wait` 类似，如果 `写操作` 与 `读操作` 没有上下文关联，那么 GTID 无法传递 。方案实用性不高。

#### 六、引入缓存中间件 ####

![a88270c94c72697103162abee571dc50.png][]

高并发系统，缓存作为性能优化利器，应用广泛。我们可以考虑引入缓存作为`缓冲介质`

**处理过程：**

*  客户端 `写` SQL ，操作主库
 *  同步将缓存中的数据删除
 *  当客户端读数据时，优先从缓存加载
 *  如果 缓存中没有，会强制查询主库预热数据

**缺点：**

K-V 存储，适用一些简单的查询条件场景。如果复杂的查询，还是要查询从库。

#### 七、数据分片 ####

![ea6b76a4117c99c4bbd82b1dc6146bec.png][]

参考 Redis Cluster 模式， 集群网络拓扑通常是 3主 3从，主节点既负责写，也负责读。

通过水平分片，支持数据的横向扩展。由于每个节点都是独立的服务器，可以提高整体集群的吞吐量。

转换到数据库方面

常见的解决方式，是分库分表，每次`读写`都是操作主库的一个分表，从库只用来做数据备份。当主库发生故障时，主从切换，保证集群的高可用性。

![ce86c37ffc042c5a9eb12e0a48ef684c.gif][]

往期推荐

![e2bb75f85cd0a45f6f6f380fb7ffd1bd.png][]

Spring官方推荐的@Transactional还能导致生产事故？

![f4380a64afc6dd5572848b75352fc093.png][]

Objects.equals有坑

![4854e75a4c0cd3e66ae8e04193e9fb1f.png][]

为什么创建线程池一定要用ThreadPoolExecutor？

![8b361da28a43d10cf5fb1997fa01d1d9.gif][]

[7cf4f33d7a91bb9d1ad2789321632f2e.png]: https://img-blog.csdnimg.cn/img_convert/7cf4f33d7a91bb9d1ad2789321632f2e.png
[eded9fff9d17593a08ba08ae95ed9ceb.png]: https://img-blog.csdnimg.cn/img_convert/eded9fff9d17593a08ba08ae95ed9ceb.png
[46caecd0a46f3c1ffb73d4eb6ab14c0c.png]: https://img-blog.csdnimg.cn/img_convert/46caecd0a46f3c1ffb73d4eb6ab14c0c.png
[a88270c94c72697103162abee571dc50.png]: https://img-blog.csdnimg.cn/img_convert/a88270c94c72697103162abee571dc50.png
[ea6b76a4117c99c4bbd82b1dc6146bec.png]: https://img-blog.csdnimg.cn/img_convert/ea6b76a4117c99c4bbd82b1dc6146bec.png
[ce86c37ffc042c5a9eb12e0a48ef684c.gif]: https://img-blog.csdnimg.cn/img_convert/ce86c37ffc042c5a9eb12e0a48ef684c.gif
[e2bb75f85cd0a45f6f6f380fb7ffd1bd.png]: https://img-blog.csdnimg.cn/img_convert/e2bb75f85cd0a45f6f6f380fb7ffd1bd.png
[f4380a64afc6dd5572848b75352fc093.png]: https://img-blog.csdnimg.cn/img_convert/f4380a64afc6dd5572848b75352fc093.png
[4854e75a4c0cd3e66ae8e04193e9fb1f.png]: https://img-blog.csdnimg.cn/img_convert/4854e75a4c0cd3e66ae8e04193e9fb1f.png
[8b361da28a43d10cf5fb1997fa01d1d9.gif]: https://img-blog.csdnimg.cn/img_convert/8b361da28a43d10cf5fb1997fa01d1d9.gif