MySQL 中讀寫分離資料延遲

ZhanLi發表於2023-05-09

MySql

MySQL 中讀寫分離可能遇到的問題

MySQL 中讀寫分離可能遇到的問題

前言

MySQL 中讀寫分離是經常用到了的架構了，透過讀寫分離實現橫向擴充套件的能力，寫入和更新操作在源伺服器上進行，從伺服器中進行資料的讀取操作，透過增大從伺服器的個數，能夠極大的增強資料庫的讀取能力。

MySQL 中的高可用架構越已經呈現出越來越複雜的趨勢，但是都是才能夠最基本的一主一從演化而來的，所以這裡來弄明白主從的基本原理。

首先來弄明白主從和主備,以及雙主模式之間的區別

雙主

有兩個主庫，每個主庫都能進行讀寫，並且兩個主庫之間都能進行資料的同步操作。

主從

主從中，資料寫入在主節點中進行，資料讀取在從節點中進行，主庫會同步資料到從庫中。

主備

主備，備庫只是用來進行資料備份，沒有讀寫操作的發生，資料的讀取和寫入發生在主庫中，主庫會同步資料到備庫中。

下面討論的資料延遲，主要是基於主從模式。主從，主備，雙主資料同步原理一樣，這裡不展開分析了。

讀寫分離的架構

常用的讀寫分離有下面兩種實現：

1、客戶端實現讀寫分離；

2、基於中間代理層實現讀寫分離。

基於客戶端實現讀寫分離

客戶端主動做負載均衡，根據 select、insert 進行路由分類，讀請求傳送到讀庫中，寫請求轉發到寫庫中。

這種方式的特點是效能較好，程式碼中直接實現，不需要額外的硬體支援，架構簡單，排查問題更方便。

缺點需要嵌入到程式碼中，需要開發人員去實現，運維無從干預，大型程式碼，實現讀寫分離需要改動的程式碼比較多。

基於中間代理實現讀寫分離

中間代理層實現讀寫分離，在 MySQL 和客戶端之間有一箇中間代理層 proxy，客戶端只連線 proxy，由 proxy 根據請求型別和上下文決定請求的分發路由。

帶 proxy 的架構，對客戶端比較友好。客戶端不需要關注後端細節，連線維護、後端資訊維護等工作，都是由 proxy 完成的。但這樣的話，對後端維護團隊的要求會更高。而且，proxy 也需要有高可用架構。因此，帶 proxy 架構的整體就相對比較複雜。

不過那種部署方式都會遇到讀寫分離主從延遲的問題，因為主從延遲的存在，客戶端剛執行完成一個更新事務，然後馬上發起查詢，如果選擇查詢的是從庫，可能讀取到的狀態是更新之前的狀態。

MySQL 中如何保證主從資料一致

MySQL 資料進行主從同步，主要是透過 binlog 實現的，從庫利用主庫上的binlog進行重播，實現主從同步。

來看下實現原理

在主從複製中，從庫利用主庫上的 binlog 進行重播，實現主從同步，複製的過程中蛀主要使用到了 dump thread，I/O thread，sql thread 這三個執行緒。

IO thread: 在從庫執行 start slave 語句時建立，負責連線主庫，請求 binlog，接收 binlog 並寫入 relay-log；

dump thread：用於主庫同步 binlog 給從庫，負責響應從 IO thread 的請求。主庫會給每個從庫的連線建立一個 dump thread，然後同步 binlog 給從庫；

sql thread：讀取 relay log 執行命令實現從庫資料的更新。

來看下複製的流程：

1、主庫收到更新命令，執行更新操作，生成 binlog;

2、從庫在主從之間建立長連線；

3、主庫 dump_thread 從本地讀取 binlog 傳送剛給從庫；

4、從庫從主庫獲取到 binlog 後儲存到本地，成為 relay log（中繼日誌）；

5、sql_thread 執行緒讀取 relay log 解析、執行命令更新資料。

需要注意的是

一開始建立主從關係的時候，同步是由從庫指定的。比如基於位點的主從關係，從庫說“我要從 binlog 檔案 A 的位置 P ”開始同步，主庫就從這個指定的位置開始往後發。

而主從複製關係搭建完成以後，是主庫來決定“要發資料給從庫”的。只有主庫有新的日誌，就會傳送給從庫。

binlog 有三種格式 statement，row 和 mixed。

1、Statement(Statement-Based Replication,SBR)：每一條會修改資料的 SQL 都會記錄在 binlog 中，裡面記錄的是執行的 SQL;

Statement 模式只記錄執行的 SQL，不需要記錄每一行資料的變化，因此極大的減少了 binlog 的日誌量，避免了大量的 IO 操作，提升了系統的效能。

正是由於 Statement 模式只記錄 SQL，而如果一些 SQL 中包含了函式，那麼可能會出現執行結果不一致的情況。

比如說 uuid() 函式，每次執行的時候都會生成一個隨機字串，在 master 中記錄了 uuid，當同步到 slave 之後，再次執行，就獲取到另外一個結果了。

所以使用 Statement 格式會出現一些資料一致性問題。

2、Row(Row-Based Replication,RBR)：不記錄 SQL 語句上下文資訊，僅僅只需要記錄某一條記錄被修改成什麼樣子;

Row 格式的日誌內容會非常清楚的記錄下每一行資料修改的細節，這樣就不會出現 Statement 中存在的那種資料無法被正常複製的情況。

比如一個修改，滿足條件的資料有 100 行，則會把這 100 行資料詳細記錄在 binlog 中。當然此時，binlog 檔案的內容要比第一種多很多。

不過 Row 格式也有一個很大的問題，那就是日誌量太大了，特別是批次 update、整表 delete、alter 表等操作，由於要記錄每一行資料的變化，此時會產生大量的日誌，大量的日誌也會帶來 IO 效能問題。

3、Mixed(Mixed-Based Replication,MBR)：Statement 和 Row 的混合體。

因為有些 statement 格式的 binlog 可能會導致主從不一致，所以要使用 row 格式。

但 row 格式的缺點是，很佔空間。比如你用一個 delete 語句刪掉10萬行資料，用 statement 的話就是一個SQL語句被記錄到binlog中，佔用幾十個位元組的空間。但如果用 row 格式的 binlog，就要把這10萬條記錄都寫到 binlog 中。這樣做，不僅會佔用更大的空間，同時寫 binlog 也要耗費IO資源，影響執行速度。所以，MySQL就取了個折中方案，也就是有了 mixed 格式的 binlog。

mixed 格式的意思是，MySQL 自己會判斷這條SQL語句是否可能引起主從不一致，如果有可能，就用 row 格式，否則就用 statement 格式。也就是說，mixed 格式可以利用 statment 格式的優點，同時又避免了資料不一致的風險。

不過現在越來越多的場景會把把 MySQL 的 binlog 格式設定成 row，例如下面的場景資料恢復。

這裡從 delete、insert 和 update 這三種 SQL 語句的角度，來看看資料恢復的問題。

1、delete：如果執行的是 delete 操作，需要恢復資料。row 格式的 binlog 也會把被刪掉的行的整行資訊儲存起來。所以，如果在執行完一條 delete 語句以後，發現刪錯資料了，可以直接把 binlog 中記錄的 delete 語句轉成 insert，把被錯刪的資料插入回去就可以恢復了；

2、insert：如果執行的是 insert 操作，需要恢復資料。row 格式下，insert 語句的 binlog 裡會記錄所有的欄位資訊，這些資訊可以用來精確定位剛剛被插入的那一行。這時，可以直接把 insert 語句轉成 delete 語句，刪除掉這被誤插入的一行資料就可以了；

3、update：如果執行的是 update 操作，需要進行資料恢復。binlog裡面會記錄修改前整行的資料和修改後的整行資料。所以，如果誤執行了 update 語句的話，只需要把這個 event 前後的兩行資訊對調一下，再去資料庫裡面執行，就能恢復這個更新操作了。

迴圈複製問題

上面 binlog 的原理，我們可以知道 MySQL 中主從資料同步就是透過 binlog 來保持資料一致的。

不過，雙 M 結構可能存在迴圈複製的問題。

什麼是雙 M 架構，雙 M 結構和 M-S 結構，其實區別只是多了一條線，即：節點 A 和 B 之間總是互為主從關係。這樣在切換的時候就不用再修改主從關係。

業務邏輯在節點 A 中更新了一條語句，然後把生成的 binlog 傳送給節點 B，節點 B 同樣也會生成 binlog 。

如果節點 A 同時又是節點 B 的從庫，節點 B 同樣也會傳遞 binlog 到節點 A，節點 A 會執行節點 B 傳過來的 binlog。這樣，節點 A 和節點 B 會不斷的迴圈這個更新語句，這就是迴圈複製。

如何解決呢？

1、規定兩個庫的 server id 必須不同，如果相同，則它們之間不能設定為主從關係；

2、一個從庫接到 binlog 並在重放的過程中，生成與原 binlog 的 server id 相同的新的 binlog；

3、每個庫在收到從自己的主庫發過來的日誌後，先判斷 server id，如果跟自己的相同，表示這個日誌是自己生成的，就直接丟棄這個日誌。

這樣當設定了雙 M 結構，日誌的執行流就會變成這樣：

1、從節點 A 更新的事務，binlog 裡面記的都是 A 的 server id；

2、傳到節點 B 執行一次以後，節點 B 生成的 binlog 的 server id 也是 A 的 server id；

3、再傳回給節點 A，A 判斷到這個 server id 與自己的相同，就不會再處理這個日誌。所以，死迴圈在這裡就斷掉了。

主從同步延遲

主從同步延遲，就是同一個事務，在從庫執行完成的時間和主庫執行完成的時間之間的差值。

1、主庫 A 執行完成一個事務，並且寫入到 binlog ，記錄這個時間為 T1；

2、傳遞資料給從庫，從庫接收這個 binlog，接收完成的時間記為 T2;

3、從庫 B 執行完成這個接收的事務，這個時間記為 T3。

主從延遲的時間就是 T3-T1 之間的時間差。

透過 show slave status 命令能到 seconds_behind_master 這個值就表示當前從庫延遲了多少秒。

seconds_behind_master 的計算方式：

1、每個事務的 binlog 都有一個時間欄位，用於記錄主庫寫入的時間；

2、從庫取出當前正在執行的事務的時間欄位的值，計算他與當前系統時間的差值，就能得到 seconds_behind_master。

簡單的講 seconds_behind_master 就是上面 T3 -T1 的時間差值。

如果主從機器的時間設定的不一致，會不會導致主從延遲的不準確？

答案是不會的，從庫連線到主庫，會透過 SELECT UNIX_TIMESTAMP()函式來獲取當前主庫的時間，如果這時候發現主庫系統時間與自己的不一致，從庫在執行 seconds_behind_master 計算的時候會主動扣減掉這差值。

主從同步延遲的原因

主從同步延遲可能存在的原因：

1、從庫的效能比主庫所在的機器效能較差；

從庫的效能比較查，如果從庫的複製能力，低於主庫，那麼在主庫寫入壓力很大的情況下，就會造成從庫長時間資料延遲的情況出現。

2、從庫的壓力大；

大量查詢放在從庫上，可能會導致從庫上耗費了大量的 CPU 資源，進而影響了同步速度，造成主從延遲。

3、大事務的執行；

有事務產生的時候，主庫必須要等待事務完成之後才能寫入到 binlog，假定執行的事務是一個非常大的資料插入，這些資料傳輸到從庫，從庫同步這些資料也需要一定的時間，就會導致從節點出現資料延遲。

4、主庫異常發生主從或主從切換切換。

發生主庫切換的時候，可能會出現資料的不一致，主從切換會有下面兩種策略：

可靠性優先策略：

1、首先判斷下從庫的 seconds_behind_master ，如果小於某個可以容忍的值，就進行下一步，否則持續重試這一步；

2、把主庫 A 改成只讀狀態，設定 readonly 為 true;

3、判斷從庫 B 的 seconds_behind_master，直到這個值變成 0 為止；

4、把從庫 B 改成可讀寫狀態，設定 readonly 為 false，從庫 B 變成新的主庫;

5、更新業務請求會切換到到從庫 B。

這個切換的過程中是存在不可用時間的，在步驟 2 之後，主庫 A 和從庫 B 都處於 readonly 狀態，這時候系統處於不可寫狀態，知道從庫庫 B readonly 狀態變成 false，這時候才能正常的接收寫請求。

步驟 3 判斷 seconds_behind_master 為 0，這個操作是最耗時的，透過步驟 1 中的提前判斷，可以確保 seconds_behind_master 的值足夠小，能夠減少步驟 3 的等待時間。

可用性優先策略：

如果把步驟4、5調整到最開始執行，不等主庫的資料同步，直接把連線切到從庫 B，讓從庫 B 可以直接讀寫，這樣系統就幾乎沒有不可用時間了。

這種策略能最大可能保障服務的可用性，但是會出現資料不一致的情況，因為寫請求直接切換到從庫 B 中，也就是設定 B 為新的主庫，因為 B 庫中還沒有同步到最新的資料，變成主庫之後，這部分資料就丟失了。

主從延遲如何處理

面對主從延遲，有下面幾種應對方案：

1、強制走主庫方案；

2、sleep方案；

3、判斷主從無延遲方案；

4、配合 semi-sync 方案；

5、等主庫位點方案；

6、等 GTID 方案。

強制走主庫方案

強制走主庫方案是實質上就是將查詢進行分類，將不能容忍同步延遲的查詢直接在主庫中進行。

1、對於必須要拿到最新結果的請求，強制將其發到主庫上。

2、對於可以讀到舊資料的請求，才將其發到從庫上。

這種方式有點投機的意思，如果所有的查詢都不允許有延遲的出現，也就意味所有的查詢，都會在主庫中出現。這樣就相當於放棄讀寫分離，所有的讀寫壓力都在主庫，等同於放棄了擴充套件性。

Sleep 方案

主庫更新完成，從庫在讀取資料之前，首先 sleep 一會。具體的方案就是，類似於執行一條 select sleep(1) 命令。

這個方案的假設是，大多數情況下主從延遲在1秒之內，做一個sleep可以有很大機率拿到最新的資料。

這種方式不是一個靠譜的方案

1、如果一個查詢請求本來 0.5 秒就可以在從庫上拿到正確結果，也會等 1 秒；

2、如果延遲超過1秒，還是會出現過期讀。

判斷主從無延遲方案

可以透過主從是否有延遲，沒有延遲就在從庫中執行查詢操作，有下面三種方法。

1、判斷 seconds_behind_master；

每次查詢之前首先判斷 seconds_behind_master 的值是否等於 0 。如果不等於 0 表示還有延遲，直到等於 0 才進行查詢操作。

2、對比位點確保主從無延遲；

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 這兩組值完全相同，就表示接收到的日誌已經同步完成。

3、對比 GTID 集合確保主從無延遲：

Auto_Position=1 ，表示這對主從關係使用了 GTID 協議；
Retrieved_Gtid_Set，是從庫收到的所有日誌的 GTID 集合；
Executed_Gtid_Set，是從庫所有已經執行完成的GTID集合。

如果 Retrieved_Gtid_Set 和 Executed_Gtid_Set 這兩個集合相同就表示從庫接收的日誌已經同步完成。

什麼是 GTID ？

GTID 的全稱是 Global Transaction Identifier，也就是全域性事務 ID，是一個事務在提交的時候生成的，是這個事務的唯一標識。

它由兩部分組成，格式是：

GTID=server_uuid:gno

server_uuid 是一個例項第一次啟動時自動生成的，是一個全域性唯一的值；
gno 是一個整數，初始值是1，每次提交事務的時候分配給這個事務，並加1。

在 GTID 模式下，每一個事務都會跟一個 GTID 一一對應。GTID 有兩種生成方式，透過 session 變數 gtid_next 的值來決定：

1、如果 gtid_next=automatic，代表使用預設值。這時，MySQL 就會把 server_uuid:gno 分配給這個事務。

a、記錄 binlog 的時候，先記錄一行 SET @@SESSION.GTID_NEXT=‘server_uuid:gno’;

b、把這個 GTID 加入本例項的 GTID 集合。

2、如果 gtid_next 是一個指定的GTID的值，比如透過 set gtid_next='current_gtid’ 指定為 current_gtid，那麼就有兩種可能：

a、如果 current_gtid 已經存在於例項的GTID集合中，接下來執行的這個事務會直接被系統忽略；

b、如果 current_gtid 沒有存在於例項的 GTID 集合中，就將這個 current_gtid 分配給接下來要執行的事務，也就是說系統不需要給這個事務生成新的 GTID，因此 gno 也不用加1。

一個 current_gtid 只能給一個事務使用。這個事務提交後，如果要執行下一個事務，就要執行set 命令，把 gtid_next 設定成另外一個 gtid 或者 automatic。

每個 MySQL 例項都維護了一個 GTID 集合，用來對應“這個例項執行過的所有事務”。

明白了 GTID 的概念，再來看下基於 GTID 的主從複製的用法。

在 GTID 模式下，從庫 C 要設定為主從庫 B 的從庫的語法如下：

我們把現在這個時刻，例項 B 的 GTID 集合記為set_b，例項 C 的 GTID 集合記為 set_c。接下來，我們就看看現在的主從切換邏輯。

我們在例項 C 上執行 start slave 命令，取 binlog 的邏輯是這樣的：

1、例項 C 指定主庫 B，基於主從協議建立連線。

2、例項 C 把 set_c 發給主庫 B。

3、例項 B 算出 set_b 與 set_c 的差集，也就是所有存在於 set_b，但是不存在於 set_c 的 GITD 的集合，判斷 B 本地是否包含了這個差集需要的所有 binlog 事務。

a、如果不包含，表示 B 已經把例項 C 需要的 binlog 給刪掉了，直接返回錯誤；

b、如果確認全部包含，B 從自己的 binlog 檔案裡面，找出第一個不在 set_c 的事務，發給 C；

之後就從這個事務開始，往後讀檔案，按順序取 binlog 發給 C 去執行。

這個邏輯裡面包含了一個設計思想：在基於 GTID 的主從關係裡，系統認為只要建立主從關係，就必須保證主庫發給從庫的日誌是完整的。因此，如果例項 C 需要的日誌已經不存在，B 就拒絕把日誌發給 C。

這跟基於位點的主從協議不同。基於位點的協議，是由從庫決定的，從庫指定哪個位點，主庫就發哪個位點，不做日誌的完整性判斷。

我們再來看看引入 GTID 後，一主多從的切換場景下，主從切換是如何實現的。

由於不需要找位點了，所以從庫 C、D 只需要分別執行 change master 命令指向例項 B 即可。

其實，嚴謹地說，主從切換不是不需要找位點了，而是找位點這個工作，在例項 B 內部就已經自動完成了。但由於這個工作是自動的，所以對HA系統的開發人員來說，非常友好。

之後這個系統就由新主庫 B 寫入，主庫 B 的自己生成的 binlog 中的 GTID 集合格式是：server_uuid_of_B:1-M。

GTID 主從同步設定時，主庫 A 發現需同步的 GTID 日誌有刪掉的，那麼 A 就會報錯。

解決辦法：

從庫 C 在啟動同步前需要設定 gtid_purged，指定 GTID 同步的起點，使用備份搭建從庫時需要這樣設定。

如果在從庫上執行了單獨的操作，導致主庫上缺少 GTID，那麼可以在主庫上模擬一個與從庫 C 上 GTID 一樣的空事務，這樣主從同步就不會報錯了。

配合semi-sync

MySQL 有三種同步模式，分別是：

1、非同步複製：MySQL 中預設的複製是非同步的，主庫在執行完客戶端提交的事務後會立即將結果返回給客戶端，並不關心從庫是否已經接收並且處理。存在問題就是，如果主庫的日誌沒有及時同步到從庫，然後主庫當機了，這時候執行故障轉移，在從庫衝選主，可能會存在選出的主庫中資料不完整；

2、全同步複製：指當主庫執行完一個事務，並且等到所有從庫也執行完成這個事務的時候，主庫在提交事務，並且返回資料給客戶端。因為要等待所有從庫都同步到主庫中的資料才返回資料，所以能夠保證主從資料的一致性，但是資料庫的效能必然受到影響；

3、半同步複製：是介於全同步和全非同步同步的一種，主庫至少需要等待一個從庫接收並寫入到 Relay Log 檔案即可，主庫不需要等待所有從庫給主庫返回 ACK。主庫收到 ACK ，標識這個事務完成，返回資料給客戶端。

MySQL 中預設的複製是非同步的，所以主庫和從庫的同步會存在一定的延遲，更重要的是非同步複製還可能引起資料的丟失。全同步複製的效能又太差了，所以從 MySQL 5.5 開始，MySQL 以外掛的形式支援 semi-sync 半同步複製。

因為本同步複製不需要等待所有的從庫同步成功，這時候在從庫中的查詢，就會面臨兩種情況：

1、如果查詢是落在這個響應了 ack 的從庫上，是能夠確保讀到最新資料；

2、如果是查詢落到其他從庫上，它們可能還沒有收到最新的日誌，就會產生過期讀的問題。

等主庫位點方案

理解等主庫位點方案之前，首先來看下下面的這條命令的含義

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

看下這條命令的幾個引數：

1、這條命令是在從庫中執行的；

2、引數 file 和 pos 指的是主庫上的檔名和位置；

3、timeout 可選，設定為正整數 N 表示這個函式最多等待 N 秒。

這條命令的正常返回是一個正整數 M ，表示從命令開始執行，到應用完 file 和 pos 表示的 binlog 位置，成功執行的事務個數。

如果從庫執行這個命令，表示從庫同步完 file 和 pos 表示的 binlog 位置，同步的事務數量，正常返回 M 表示從庫資料在 timeout 時間內相對於主庫已經沒有延遲了。

除了正常返回，也會有其它的執行返回資訊

1、如果執行期間從庫同步發生異常，就返回 NULL;

2、如果等待超過 N 秒，聚返回 -1；

3、如果剛開始執行的時候，就發現已經執行過這個位置了，就返回 0。

弄明白了這條命令的作用，我們再來看下等主庫位點方案的具體執行流程，如果我們在主庫中執行一個資料更新，然後在從庫中查詢，執行的過程就是:

1、更新事務執行完成，馬上執行 show master status 得到當前主庫執行到的 File 和 Position；

2、選定一個從庫執行查詢；

3、在從庫上執行 select master_pos_wait(File, Position, 1)；

4、如果返回值是 >=0 的正整數，則在這個從庫執行查詢語句；

5、否則到主庫執行查詢。

如果從庫的延遲都超過了的等待的 timeout 時間，那麼所有的請求，最總還是會落到主庫中，查詢的壓力還是會爬到主庫中。

等 GTID 方案

如果開啟了 GTID 模式，對應的也有等待 GTID 的方案。

MySQL 中同樣提供了等待同步的命令

 select wait_for_executed_gtid_set(gtid_set, 1);

執行邏輯：

1、從庫在執行命令的時候攜帶事務的 gtid，如果返回 0 表示該事務已經正常同步到從庫中，可以在從庫中執行查詢；

2、超時測返回 1。

在前面等位點的方案中，我們執行完事務後，還要主動去主庫執行 show master status。而 MySQL 5.7.6 版本開始，允許在執行完更新類事務後，把這個事務的GTID返回給客戶端，這樣等 GTID 的方案就可以減少一次查詢。

等 GTID 方案的流程就是

1、主庫中執行完成一個事務，從返回包直接獲取這個事務的 GTID，記為 gtid1；

2、選定一個從庫執行查詢語句；

3、從庫中執行 select wait_for_executed_gtid_set(gtid1, 1)；

4、如果返回值是 0，就在從庫中執行查詢；

5、返回值不是 0，表示該事務還沒有同步到從庫中，就需要到主庫中執行查詢了。

和等主庫位點方案的方案一樣，等待超時就需要向主庫中發起查詢了。

總結

1、MySQL 中讀寫分離是經常用到了的架構了，透過讀寫分離實現橫向擴充套件的能力，寫入和更新操作在源伺服器上進行，從伺服器中進行資料的讀取操作，透過增大從伺服器的個數，能夠極大的增強資料庫的讀取能力；

2、MySQL 資料進行主從同步，主要是透過 binlog 實現的，從庫利用主庫上的binlog進行重播，實現主從同步；

3、資料同步會存在一個問題及時同步延遲；

4、主從同步延遲可能存在的原因：

1、從庫的效能比主庫所在的機器效能較差；
2、從庫的壓力大；
3、大事務的執行；
4、主庫異常發生主從或主從切換切換。

5、主從延遲應該如何處理？