MySQL主從延遲、讀寫分離的解決方法總結

這篇文章為大家帶來了關於mysql的相關知識，其中主要介紹了關於主從延遲和讀寫分離的解決方法，下面一起來看一下總結了幾個方法，希望對大家有幫助。

推薦學習：mysql影片教學

#我們都知道網路資料有個特性，大部分場景都是讀多寫少，例如：微博、微信、淘寶電商，按照二八原則，讀流量佔比甚至能達到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#o##比較 

Retrieved_Gtid_Set

##比較 Retrieved_Gtid_Set#o

比較 

Retrieved_Gtid_Set###########比較 ###Retrieved_Gtid_Set#o#########比較 ###Retrieved_Gtid_Set#o#########比較 ###Retrieved_Gtid_Set############################C ##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中文網其他相關文章！