分享MySQL 主從延遲與讀寫分離的七種解決方案

前言：

我們都知道網際網路資料有個特性，大部分場景都是 讀多寫少，比如：微博、微信、淘寶電商，按照 二八原則，讀流量佔比甚至能達到 90%。

結合這個特性，我們對底層的資料庫架構也會做相應調整。採用 讀寫分離。

處理過程：

• 使用者端會整合 SDK，每次執行 SQL 時，會判斷是 寫 或 讀 操作。
• 如果是 寫 SQL，請求會發到 主庫。
• 主資料庫執行SQL，事務提交後，會生成 binlog ，並同步給 從庫。
• 從庫 通過 SQL 執行緒回放 binlog ，並在從庫表中生成相應資料。
• 如果是 讀 SQL，請求會通過 負載均衡 策略，挑選一個 從庫 處理使用者請求。

看似非常合理，細想卻不是那麼回事。

主庫 與 從庫 是採用非同步複製資料，如果這兩者之間資料還沒有同步怎麼辦?

主庫剛寫完資料，從庫還沒來得及拉取最新資料，讀 請求就來了，給使用者的感覺，資料丟了?

針對這個問題，今天，我們就來探討下有什麼解決方案?

一、強制走主庫

針對不用的業務訴求，區別性對待。

場景一：

如果是對資料的 實時性 要求不是很高，比如：大V有千萬粉絲，釋出一條微博，粉絲晚幾秒鐘收到這條資訊，並不會有特別大的影響。這時，可以走 從庫。

場景二：

如果對資料的 實時性 要求非常高，比如金融類業務。我們可以在使用者端程式碼標記下，讓查詢強制走主庫。

二、從庫延遲查詢

由於主從庫之間資料同步需要一定的時間間隔，那麼有一種策略是延遲從從庫查詢資料。

比如：

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

在正式的業務查詢時，先執行一個sleep 語句，給從庫預留一定的資料同步緩衝期。

因為是採用一刀切，當面對高並行業務場景時，效能會下降的非常厲害，一般不推薦這個方案。

三、判斷主從是否延遲?決定選主庫還是從庫

之前寫過一篇文章 《京東一面：MySQL 主備延遲有哪些坑?主備切換策略 》。

有講過 什麼是主備延遲?、主備延遲的常見原因?

方案一：

在從庫 執行 命令 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操作時，先判斷從庫是否已經同步最新資料。從而決定是操作主庫，還是操作從庫。

缺點：

無論採用上面哪一種方案，如果主庫的寫操作頻繁不斷，那麼從庫的值永遠跟不上主庫的值，那麼讀流量永遠是打在了主庫上。

1.針對這個問題，有什麼解決方案?

這個問題跟 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從，主節點既負責寫，也負責讀。

通過水平分片，支援資料的橫向擴充套件。由於每個節點都是獨立的伺服器，可以提高整體叢集的吞吐量。

1.轉換到資料庫方面

常見的解決方式，是分庫分表，每次讀寫都是操作主庫的一個分表，從庫只用來做資料備份。當主庫發生故障時，主從切換，保證叢集的高可用性。

到此這篇關於分享MySQL 主從延遲與讀寫分離的七種解決方案的文章就介紹到這了,更多相關MySQL主從延遲讀寫分離解決方案內容請搜尋it145.com以前的文章或繼續瀏覽下面的相關文章希望大家以後多多支援it145.com！