MySQL -- 讀寫分離
Proxy
對比
- 客戶端直連
- 少了一層
Proxy
轉發,查詢效能稍微好一點 - 整體架構簡單,排查問題方便
- 需要了解 後端部署細節 ,在出現主從切換、庫遷移時,客戶端有感知,需要調整資料庫連線資訊
- 一般伴隨著一個負責管理後端的元件,例如
ZooKeeper
- 一般伴隨著一個負責管理後端的元件,例如
- 少了一層
- Proxy – 發展趨勢
- 對客戶端友好,客戶端不需要關注後端細節,但後端維護成本較高
-
Proxy
也需要 高可用 架構,帶Proxy的整體架構相對複雜
過期讀
由於 主從延遲 ,主庫上執行完一個更新事務後,立馬在從庫上執行查詢,有可能讀到剛剛的事務更新之前的狀態
解決方案
強制走主庫
- 將查詢請求做 分類
- 必須要拿到最新結果的請求,強制將其傳送到主庫上
- 可以讀到舊資料的請求,將其發到從庫上
- 如果完全不能接受過期讀,例如金融類業務,相當於放棄讀寫分離,所有的讀寫壓力都在主庫上
SLEEP
- 主庫更新後,讀從庫之前先
SLEEP
一下,類似於SELECT SLEEP(1)
- 基於的假設:大多數主從延時在1秒內
- 賣家釋出商品後,用
Ajax
直接把客戶端輸入的內容作為“新的商品”顯示在頁面上,而非真正的做資料庫查詢- 等賣家再次重新整理頁面,其實主從已經同步完成了,也達到了
SLEEP
的效果
- 等賣家再次重新整理頁面,其實主從已經同步完成了,也達到了
-
SLEEP
方案解決了類似場景下的過期讀問題,但存在 不精確 的問題- 如果主從延時只有0.5秒,也會等到1秒
- 如果主從延遲超過了1秒,依然會出現 過期讀 的問題
判斷主從無延遲
-
SLOW SLAVE STATUS
.Seconds_Behind_Master
- 每次在從庫執行查詢請求前,先判斷
Seconds_Behind_Master
是否等於0
-
Seconds_Behind_Master=0
才能執行查詢請求 -
Seconds_Behind_Master
的精度為 秒 ,如果需要更高精度,可以考慮對比 位點 和GTID
- 每次在從庫執行查詢請求前,先判斷
- 位點
-
Master_Log_File
和Read_Master_Log_Pos
,表示 讀到的主庫的最新位點 -
Relay_Master_Log_File
和Exec_Master_Log_Pos
,表示 從庫執行的最新位點 -
Master_Log_File=Relay_Master_Log_File
和Read_Master_Log_Pos=Exec_Master_Log_Pos
- 表示接收到的日誌已經 同步完成
-
-
GTID
-
Auto_Position=1
,表示 主從關係 使用了GTID
協議 -
Retrieved_Gtid_Set
,表示從庫 收到 的所有日誌的GTID
集合 -
Executed_Gtid_Set
,表示從庫所有 已經執行完成 的GTID
集合
-
mysql> SHOW SLAVE STATUS\G; *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Log_File: master-bin.000003 Read_Master_Log_Pos: 484 Relay_Log_File: relay-bin.000003 Relay_Log_Pos: 699 Relay_Master_Log_File: master-bin.000003 Exec_Master_Log_Pos: 484 Seconds_Behind_Master: 0 Master_UUID: b0bda503-3cf1-11e9-8c3a-0242ac110002 Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates Retrieved_Gtid_Set: b0bda503-3cf1-11e9-8c3a-0242ac110002:1-6 Executed_Gtid_Set: b0bda503-3cf1-11e9-8c3a-0242ac110002:1-6,ba0b2f12-3cf1-11e9-9c40-0242ac110003:1-5 Auto_Position: 1
不精確
-
binlog
在主從之間的狀態- 主庫執行完成,寫入
binlog
,反饋給客戶端 -
binlog
被主庫傳送到從庫,從庫收到 - 從庫執行
binlog
(應用relaylog
)
- 主庫執行完成,寫入
- 上面判斷的 主從無延遲 : 從庫收到的日誌都執行完成了
- 並沒有考慮這部分日誌:客戶端已經收到提交確認,但從庫還未收到
- 主庫上執行完成了3個事務:
trx1
、trx2
和trx3
-
trx1
和trx2
已經傳到從庫,並且已經執行完成了 -
trx3
在主庫執行完成後,並且已經回覆給客戶端,但還未傳到從庫中 - 如果此時在從庫上執行查詢請求,按上面的邏輯,從庫已經 沒有同步延遲 了,但還是查不到
trx3
的變更,出現了 過期讀
SEMI-SYNC
SEMI-SYNC設計
- 事務提交到時候,主庫把
binlog
發給從庫 - 從庫收到
binlog
後,發回給主庫一個ACK
,表示收到了 - 主庫收到這個
ACK
以後,才能給客戶端返回事務完成的確認
小結
- 啟用了
SEMI-SYNC
- 所有給客戶端傳送過確認的事務,都確保了 某一個從庫 已經收到了這個日誌
-
SEMI-SYNC
+位點的方案,只針對 一主一從 的場景是成立的- 在 一主多從 的場景裡,主庫只要等到一個從庫的
ACK
,就開始給客戶端返回確認 - 如果對剛剛響應了
ACK
的從庫執行查詢請求(+判斷 主從無延遲 ),能夠確保讀到最新的資料,否則可能是 過期讀
- 在 一主多從 的場景裡,主庫只要等到一個從庫的
- 如果在業務高峰期,主庫的位點或者GTID集合更新很快,從庫可能一直跟不上主庫,導致從庫遲遲無法響應查詢請求
- 在出現 持續延遲 的情況下,可能會出現 過度等待 (判斷 主從無延遲 )的情況
等主庫位點
SELECT MASTER_POS_WAIT(file, pos[, timeout]);
- 在 從庫 上執行
- 引數
file
和pos
指的是 主庫 上的檔名和位置 -
timeout
單位為秒 - 返回正整數,表示從 命令開始執行 ,到應用完
file
和pos
,總共 執行了多少事務- 如果在執行期間,從庫的同步執行緒發生異常,返回NULL
- 如果等待超過
timeout
秒,返回-1
- 如果剛開始執行的時候,發現 已經執行 過這個位置,返回
0
樣例
先在 MySQL A
執行 trx1
,然後在 MySQL B
執行查詢請求
- 事務
trx1
更新完成後,馬上執行SHOW MASTER STATUS
,得到當前主庫執行到的File
和Position
- 選擇一個從庫執行查詢語句
- 在該從庫上先執行
SELECT MASTER_POS_WAIT(File, Position, 1)
- 如果返回值
>=0
,則直接在這個從庫上執行查詢語句 - 否則,在主庫上執行查詢語句
- 一種退化機制,針對 主從延時不可控 的場景
-- MySQL A mysql> SHOW MASTER STATUS; +-------------------+----------+--------------+------------------+------------------------------------------+ | File| Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set| +-------------------+----------+--------------+------------------+------------------------------------------+ | master-bin.000003 |643 ||| b0bda503-3cf1-11e9-8c3a-0242ac110002:1-7 | +-------------------+----------+--------------+------------------+------------------------------------------+ -- MySQL B mysql> SELECT MASTER_POS_WAIT('master-bin.000003',643,1); +--------------------------------------------+ | MASTER_POS_WAIT('master-bin.000003',643,1) | +--------------------------------------------+ |0 | +--------------------------------------------+
等GTID
-- 等待,直到這個庫執行的事務中包含傳入的gtid_set,返回0 -- 超時返回1 SELECT WAIT_FOR_EXECUTED_GTID_SET(gtid_set, 1);
樣例
- 從MySQL 5.7.6開始,允許在執行完 更新類事務 後,把這個事務的
GTID
返回給客戶端 - 事務
trx1
更新完成後,從返回結果中直接獲取trx1
的GTID
,記為gtid1
- 選擇一個從庫執行查詢語句
- 在該從庫上先執行
SELECT WAIT_FOR_EXECUTED_GTID_SET(gtid1, 1)
- 如果返回
0
,則直接在這個從庫上執行查詢語句 - 否則,在主庫上執行查詢語句
-- MySQL A mysql> SHOW MASTER STATUS; +-------------------+----------+--------------+------------------+------------------------------------------+ | File| Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set| +-------------------+----------+--------------+------------------+------------------------------------------+ | master-bin.000003 |643 ||| b0bda503-3cf1-11e9-8c3a-0242ac110002:1-7 | +-------------------+----------+--------------+------------------+------------------------------------------+ -- MySQL B mysql> SELECT WAIT_FOR_EXECUTED_GTID_SET('b0bda503-3cf1-11e9-8c3a-0242ac110002:1-7',1); +--------------------------------------------------------------------------+ | WAIT_FOR_EXECUTED_GTID_SET('b0bda503-3cf1-11e9-8c3a-0242ac110002:1-7',1) | +--------------------------------------------------------------------------+ |0 | +--------------------------------------------------------------------------+
參考資料
《MySQL實戰45講》
轉載請註明出處:http://zhongmingmao.me/2019/03/02/mysql-read-write-separation/
訪問原文「MySQL -- 讀寫分離」獲取最佳閱讀體驗並參與討論