MySQL 마스터-슬레이브 지연 및 읽기-쓰기 분리에 대한 솔루션 요약

이 기사는 마스터-슬레이브 지연 및 읽기-쓰기 분리에 대한 솔루션을 주로 소개하는 mysql에 대한 관련 지식을 제공하고 여러 가지 방법을 살펴보고 요약해 보겠습니다.

추천 학습: mysql 비디오 튜토리얼

우리 모두는 인터넷 데이터에 특성이 있다는 것을 알고 있습니다. 대부분의 시나리오는 Weibo, WeChat, Taobao Business와 같이 더 많이 읽고 덜 씁니다입니다. , 28원칙에 따르면 읽기 트래픽 비율은 90%에 도달할 수도 있습니다读多写少，比如：微博、微信、淘宝电商，按照 二八原则，读流量占比甚至能达到 90%

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

处理过程：

• 客户端会集成 SDK，每次执行 SQL 时，会判断是 写 或 读 操作

• 如果是 写 SQL，请求会发到 主库

• 主数据库执行SQL，事务提交后，会生成 binlog ，并同步给 从库

• 从库 通过 SQL 线程回放 binlog ，并在从库表中生成相应数据

• 如果是 读 SQL，请求会通过 负载均衡 策略，挑选一个 从库 处理用户请求

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

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

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

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

一、强制走主库

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

场景一：

如果是对数据的 实时性 要求不是很高，比如：大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

이 기능과 함께 기본 데이터베이스 아키텍처도 이에 맞게 조정됩니다. 읽기-쓰기 분리를 사용하세요

처리과정 :

• 클라이언트는 SDK를 통합하게 되며, SQL이 실행될 때마다 write 또는 read 작업으로 판단됩니다

• 🎜write SQL인 경우 요청이 다음으로 전송됩니다. 메인 라이브러리🎜
• 🎜마스터 데이터베이스는 트랜잭션이 제출된 후 binlog가 생성되어 슬레이브에 동기화됩니다. library🎜
• 🎜슬레이브 라이브러리는 SQL 스레드를 통해 binlog를 재생하고 슬레이브 라이브러리 테이블에 해당 데이터를 생성합니다🎜
• 🎜읽기 SQL인 경우 요청은 로드 밸런싱 전략을 거치며 슬레이브 라이브러리가 선택됩니다. 사용자 요청을 처리하세요🎜

🎜매우 합리적인 것 같지만 잘 생각해보면 사실이 아닙니다. 그렇다면 무슨 일이 일어나고 있는 걸까요🎜🎜메인 라이브러리 및 슬레이브 라이브러리는 데이터의 비동기 복제를 사용합니다. 둘 사이의 데이터가 아직 동기화되지 않은 경우 어떻게 되나요? 🎜🎜메인 라이브러리는 방금 데이터 쓰기를 마쳤고 슬레이브 라이브러리가 최신 데이터를 가져올 시간이 되기 전에 읽기 요청이 와서 사용자에게 데이터가 손실된 느낌을 줍니다. ? ? ? 🎜🎜이 문제에 대한 대응으로 오늘은 어떤 해결책이 있는지 논의해 볼까요? 🎜🎜1. 기본 데이터베이스 강제 사용🎜🎜사용하지 않는 비즈니스 요구 사항에 대해 다른 비즈니스 요구 사항 처리🎜🎜🎜시나리오 1: 🎜🎜🎜데이터에 대한 실시간 요구 사항이 그다지 높지 않은 경우 예를 들어, 대형 뷔는 수천만 명의 팬을 보유하고 있으며 웨이보 메시지를 게시하고 몇 초 후에 팬이 해당 메시지를 받는다면 그다지 큰 영향을 미치지는 않을 것입니다. 이때 도서관에서로 이동할 수 있습니다. 🎜🎜🎜시나리오 2: 🎜🎜🎜금융 서비스와 같이 데이터의 실시간에 대한 요구 사항이 매우 높은 경우. 쿼리가 클라이언트 코드 태그 아래의 기본 데이터베이스로 이동하도록 강제할 수 있습니다. 🎜🎜2. 슬레이브 데이터베이스의 쿼리 지연🎜🎜마스터 데이터베이스와 슬레이브 데이터베이스 간의 데이터 동기화에는 일정한 시간 간격이 필요하므로 슬레이브 데이터베이스의 데이터 쿼리를 지연시키는 전략이 있습니다. 🎜🎜예: 🎜

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

🎜정식 비즈니스 쿼리에서는 먼저 sleep 문을 실행하여 슬레이브 데이터베이스에 대한 특정 데이터 동기화 버퍼 기간을 예약합니다. 🎜🎜모든 경우에 적용되는 솔루션이므로 동시성이 높은 비즈니스 시나리오에 직면하면 성능이 크게 저하됩니다. 이 솔루션은 일반적으로 권장되지 않습니다. 🎜🎜3. 마스터와 슬레이브가 지연되는지 확인하세요. 마스터 라이브러리를 선택할지 슬레이브 라이브러리를 선택할지 결정합니다🎜🎜🎜옵션 1: 🎜🎜🎜슬레이브 라이브러리에서 showslave status🎜🎜 명령을 실행하여 seconds_behind_master의 값을 확인합니다. code>, 단위는 초이며, 0이면 마스터 데이터베이스와 슬레이브 데이터베이스 사이에 지연이 없음을 의미합니다🎜🎜🎜옵션 2: 🎜🎜🎜마스터 데이터베이스와 슬레이브 데이터베이스의 파일 포인트를 비교🎜🎜또는 <code>showslave status, 응답 결과에 키가 있습니다 Parameter🎜

• 🎜Master_Log_File 메인 라이브러리에서 읽은 최신 파일🎜
• 🎜Read_Master_Log_Pos 메인 라이브러리에서 읽은 최신 파일🎜
• 🎜Relay_Master_Log_File 라이브러리에서 실행된 최신 파일🎜
• 🎜Exec_Master_Log_Pos 라이브러리에서 실행된 최신 파일의 좌표 위치🎜

🎜두 매개변수를 비교하여 위의 매개변수가 동일한지 확인하세요🎜🎜🎜옵션 3:🎜 🎜🎜GTID 세트 비교🎜

• 🎜Auto_Position=1 마스터와 슬레이브 간에 GTID 프로토콜을 사용하세요🎜
• 🎜Retrieved_Gtid_Set 라이브러리에서 받은 모든 binlog 로그의 GTID 세트🎜
🎜Executed_Gtid_Set 라이브러리에서 실행된 GTID 세트🎜

🎜여부를 비교하세요 Retrieved_Gtid_Set과 Executed_Gtid_Set의 값이 동일합니다🎜🎜비즈니스 SQL 실행 시 먼저 슬레이브 데이터베이스가 최신 데이터를 동기화했는지 확인합니다. 마스터 데이터베이스를 운영할지, 슬레이브 데이터베이스를 운영할지 결정합니다. 🎜🎜🎜단점: 🎜🎜🎜위 솔루션 중 어떤 솔루션을 채택하더라도 메인 라이브러리에 쓰기 작업이 자주 발생하면 슬레이브 라이브러리의 값이 메인 라이브러리의 값을 따라잡지 못하고 읽기 트래픽이 늘어나게 됩니다. 항상 메인 라이브러리를 방문하세요. 🎜

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

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

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

问题是不是一下简单多了

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

在从库执行下面命令，返回是一个正整数 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 无法传递 。方案实用性不高。

六、引入缓存中间件

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

处理过程：

• 客户端 写 SQL ，操作主库

• 同步将缓存中的数据删除

• 当客户端读数据时，优先从缓存加载

• 如果 缓存中没有，会强制查询主库预热数据

缺点：

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

七、数据分片

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

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

转换到数据库方面

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

推荐学习：mysql视频教程

위 내용은 MySQL 마스터-슬레이브 지연 및 읽기-쓰기 분리에 대한 솔루션 요약의 상세 내용입니다. 자세한 내용은 PHP 중국어 웹사이트의 기타 관련 기사를 참조하세요!