奇怪！備庫為什么會延遲好幾個小時？

拋出問題：如果備庫執行日志的速度持續低于主庫生成日志的速度，那這個延遲就有可能成了小時級別，而且對于一個壓力持續比較高的主庫來說，備庫很可能永遠都追不上主庫的節奏，那么MySQL有什么應對措施嗎？

并行復制概念引入

首先回顧下MySQL主備一致的流程圖：

談到主備的并行復制能力，我們要關注的是圖中黑色的兩個箭頭，一個箭頭代表了客戶端寫入主庫，另一箭頭代表的是備庫上 sql_thread 執行中轉日志（relay log），如果用箭頭的粗細來代表并行度的話，那么真實情況就如圖 1 所示，第一個箭頭要明顯粗于第二個箭頭，

在主庫上，影響并發度的原因就是各種鎖了，由于 InnoDB 引擎支持行鎖，除了所有并發事務都在更新同一行（熱點行）這種極端場景外，它對業務并發度的支持還是很友好的，所以，你在性能測驗的時候會發現，并發壓測執行緒 32 就比單執行緒時，總體吞吐量高，

而日志在備庫上的執行，就是圖中備庫上 sql_thread 更新資料 (DATA) 的邏輯，如果是用單執行緒的話，就會導致備庫應用日志不夠快，造成主備延遲，

在官方的 5.6 版本之前，MySQL 只支持單執行緒復制，由此在主庫并發高、TPS 高時就會出現嚴重的主備延遲問題，從單執行緒復制到最新版本的多執行緒復制，中間的演化經歷了好幾個版本，接下來，我就跟你說說 MySQL 多執行緒復制的演程序序，

所有的多執行緒復制機制，都是要把圖 1 中只有一個執行緒的 sql_thread，拆成多個執行緒，也就是都符合下面的這個模型：

圖 2 中，coordinator 就是原來的 sql_thread, 不過現在它不再直接更新資料了，只負責讀取中轉日志和分發事務，真正更新日志的，變成了 worker 執行緒，而 work 執行緒的個數，就是由引數 slave_parallel_workers 決定的，根據我的經驗，把這個值設定為執行緒總數的25～50%比較好，畢竟備庫還有可能要提供讀查詢，不能把 CPU 都吃光了，

問題一：事務能不能按照輪詢的方式分發給各個 worker，也就是第一個事務分給 worker_1，第二個事務發給 worker_2 呢？
不行🙅，因為，事務被分發給 worker 以后，不同的 worker 就獨立執行了，但是，由于 CPU 的調度策略，很可能第二個事務最終比第一個事務先執行，而如果這時候剛好這兩個事務更新的是同一行，也就意味著，同一行上的兩個事務，在主庫和備庫上的執行順序相反，會導致主備不一致的問題，

問題二：同一個事務的多個更新陳述句，能不能分給不同的 worker 來執行呢？
不行🙅，舉個例子，一個事務更新了表 t1 和表 t2 中的各一行，如果這兩條更新陳述句被分到不同 worker 的話，雖然最終的結果是主備一致的，但如果表 t1 執行完成的瞬間，備庫上有一個查詢，就會看到這個事務“更新了一半的結果”，破壞了事務邏輯的原子性，

綜上coordinator 在分發的時候，需要滿足以下這兩個基本要求：

• 不能造成更新覆寫，這就要求更新同一行的兩個事務，必須被分發到同一個 worker 中，
• 同一個事務不能被拆開，必須放到同一個 worker 中，

并行復制策略設計

官方 MySQL 5.5 版本及以前是不支持并行復制的，那么線上業務又需要并行復制策略支持的話，你可以自己設計一個，下面從按表分發、按行分發分別來闡述，😎

按表分發策略

按表分發事務的基本思路是，如果兩個事務更新不同的表，它們就可以并行，因為資料是存盤在表里的，所以按表分發，可以保證兩個 worker 不會更新同一行，當然，如果有跨表的事務，還是要把兩張表放在一起考慮的，

每個 worker 執行緒對應一個 hash 表，用于保存當前正在這個 worker 的“執行佇列”里的事務所涉及的表，hash 表的 key 是“庫名. 表名”，value 是一個數字，表示佇列中有多少個事務修改這個表，在有事務分配給 worker 時，事務里面涉及的表會被加到對應的 hash 表中，worker 執行完成后，這個表會被從 hash 表中去掉，

假設在圖中的情況下，coordinator 從中轉日志中讀入一個新事務 T，這個事務修改的行涉及到表 t1 和 t3，
現在我們用事務 T 的分配流程，來看一下分配規則，

• 由于事務 T 中涉及修改表 t1，而 worker_1 佇列中有事務在修改表 t1，事務 T 和佇列中的某個事務要修改同一個表的資料，這種情況我們說事務 T 和 worker_1 是沖突的，
• 按照這個邏輯，順序判斷事務 T 和每個 worker 佇列的沖突關系，會發現事務 T 跟 worker_2 也沖突，事務 T 跟多于一個 worker 沖突，coordinator 執行緒就進入等待，
• 每個 worker 繼續執行，同時修改 hash_table，假設 hash_table_2 里面涉及到修改表 t3 的事務先執行完成，就會從 hash_table_2 中把 db1.t3 這一項去掉，
• 這樣 coordinator 會發現跟事務 T 沖突的 worker 只有 worker_1 了，因此就把它分配給 worker_1，

coordinator 繼續讀下一個中轉日志，繼續分配事務，

每個事務在分發的時候，跟所有 worker 的沖突關系包括以下三種情況：

• 如果跟所有 worker 都不沖突，coordinator 執行緒就會把這個事務分配給最空閑的 woker;
• 如果跟多于一個 worker 沖突，coordinator 執行緒就進入等待狀態，直到和這個事務存在沖突關系的 worker 只剩下 1 個；
• 如果只跟一個 worker 沖突，coordinator 執行緒就會把這個事務分配給這個存在沖突關系的 worker，

這個按表分發的方案，在多個表負載均勻的場景里應用效果很好，但是，如果碰到熱點表，比如所有的更新事務都會涉及到某一個表的時候，所有事務都會被分配到同一個 worker 中，就變成單執行緒復制了，

按行分發策略

要解決熱點表的并行復制問題，就需要一個按行并行復制的方案，按行復制的核心思路是：如果兩個事務沒有更新相同的行，它們在備庫上可以并行執行，顯然，這個模式要求 binlog 格式必須是 row，

我們判斷一個事務 T 和 worker 是否沖突，用的就規則就不是“修改同一個表”，而是“修改同一行”，
按行復制和按表復制的資料結構差不多，也是為每個 worker，分配一個 hash 表，只是要實作按行分發，這時候的 key，就必須是“庫名 + 表名 + 唯一鍵的值”，
但是，這個“唯一鍵”只有主鍵 id 還是不夠的，我們還需要考慮下面這種場景，表 t1 中除了主鍵，還有唯一索引 a：

CREATE TABLE `t1` (
 `id` int(11) NOT NULL,
 `a` int(11) DEFAULT NULL,
 `b` int(11) DEFAULT NULL,
 PRIMARY KEY (`id`),
 UNIQUE KEY `a` (`a`)
) ENGINE=InnoDB;
insert into t1 values(1,1,1),(2,2,2),(3,3,3),(4,4,4),(5,5,5);

這兩個事務要更新的行的主鍵值不同，但是如果它們被分到不同的 worker，就有可能 session B 的陳述句先執行，這時候 id=1 的行的 a 的值還是 1，就會報唯一鍵沖突，

因此，基于行的策略，事務 hash 表中還需要考慮唯一鍵，即 key 應該是“庫名 + 表名 + 索引 a 的名字 +a 的值”，

在上面這個例子中，我要在表 t1 上執行 update t1 set a=1 where id=2 陳述句，在 binlog 里面記錄了整行的資料修改前各個欄位的值，和修改后各個欄位的值，

因此，coordinator 在決議這個陳述句的 binlog 的時候，這個事務的 hash 表就有三個項:

• key=hash_func(db1+t1+“PRIMARY”+2), value=2; 這里 value=2 是因為修改前后的行 id 值不變，出現了兩次，
• key=hash_func(db1+t1+“a”+2), value=1，表示會影響到這個表 a=2 的行，
• key=hash_func(db1+t1+“a”+1), value=1，表示會影響到這個表 a=1 的行，

相比于按表并行分發策略，按行并行策略在決定執行緒分發的時候，需要消耗更多的計算資源，你可能也發現了，這兩個方案其實都有一些約束條件：

• 要能夠從 binlog 里面決議出表名、主鍵值和唯一索引的值，也就是說，主庫的 binlog 格式必須是 row；
• 表必須有主鍵；
• 不能有外鍵，表上如果有外鍵，級聯更新的行不會記錄在 binlog 中，這樣沖突檢測就不準確，

對比按表分發和按行分發這兩個方案的話，按行分發策略的并行度更高，不過，如果是要操作很多行的大事務的話，按行分發的策略有兩個問題：

• 耗費記憶體，比如一個陳述句要洗掉 100 萬行資料，這時候 hash 表就要記錄 100 萬個項，
• 耗費 CPU，決議 binlog，然后計算 hash 值，對于大事務，這個成本還是很高的，

所以，我在實作這個策略的時候會設定一個閾值，單個事務如果超過設定的行數閾值（比如，如果單個事務更新的行數超過 10 萬行），就暫時退化為單執行緒模式，退化程序的邏輯大概是這樣的：

• coordinator 暫時先 hold 住這個事務；
• 等待所有 worker 都執行完成，變成空佇列；
• coordinator 直接執行這個事務；
• 恢復并行模式，

MySQL 5.6 版本的并行復制策略

官方 MySQL5.6 版本，支持了并行復制，只是支持的粒度是按庫并行，用于決定分發策略的 hash 表里，key 就是資料庫名，
這個策略的并行效果，取決于壓力模型，如果在主庫上有多個 DB，并且各個 DB 的壓力均衡，使用這個策略的效果會很好，

相比于按表和按行分發，這個策略有兩個優勢：

• 構造 hash 值的時候很快，只需要庫名；而且一個實體上 DB 數也不會很多，不會出現需要構造 100 萬個項這種情況，
• 不要求 binlog 的格式，因為 statement 格式的 binlog 也可以很容易拿到庫名，

如果你的主庫上的表都放在同一個 DB 里面，這個策略就沒有效果了；或者如果不同 DB 的熱點不同，比如一個是業務邏輯庫，一個是系統配置庫，那也起不到并行的效果，

MariaDB 的并行復制策略

MariaDB 的并行復制策略利用了組提交的概念：

• 能夠在同一組里提交的事務，一定不會修改同一行；
• 主庫上可以并行執行的事務，備庫上也一定是可以并行執行的，

在實作上，MariaDB 是這么做的：

• 在一組里面一起提交的事務，有一個相同的 commit_id，下一組就是 commit_id+1；
• commit_id 直接寫到 binlog 里面；
• 傳到備庫應用的時候，相同 commit_id 的事務分發到多個 worker 執行；
• 這一組全部執行完成后，coordinator 再去取下一批，

這個策略有一個問題，它并沒有實作“真正的模擬主庫并發度”這個目標，在主庫上，一組事務在 commit 的時候，下一組事務是同時處于“執行中”狀態的，

假設了三組事務在主庫的執行情況，你可以看到在 trx1、trx2 和 trx3 提交的時候，trx4、trx5 和 trx6 是在執行的，這樣，在第一組事務提交完成的時候，下一組事務很快就會進入 commit 狀態，

按照 MariaDB 的并行復制策略，備庫上的執行效果如圖 6 所示，

在備庫上執行的時候，要等第一組事務完全執行完成后，第二組事務才能開始執行，這樣系統的吞吐量就不夠，
另外，這個方案很容易被大事務拖后腿，假設 trx2 是一個超大事務，那么在備庫應用的時候，trx1 和 trx3 執行完成后，就只能等 trx2 完全執行完成，下一組才能開始執行，這段時間，只有一個 worker 執行緒在作業，是對資源的浪費，

MySQL 5.7 的并行復制策略

在 MariaDB 并行復制實作之后，官方的 MySQL5.7 版本也提供了類似的功能，由引數 slave-parallel-type 來控制并行復制策略：

• 配置為 DATABASE，表示使用 MySQL 5.6 版本的按庫并行策略；
• 配置為 LOGICAL_CLOCK，表示的就是類似 MariaDB 的策略，不過，MySQL 5.7 這個策略，針對并行度做了優化，這個優化的思路也很有趣兒，

考慮一個問題：同時處于“執行狀態”的所有事務，是不是可以并行？
不能，因為，這里面可能有由于鎖沖突而處于鎖等待狀態的事務，如果這些事務在備庫上被分配到不同的 worker，就會出現備庫跟主庫不一致的情況，而上面提到的 MariaDB 這個策略的核心，是“所有處于 commit”狀態的事務可以并行，事務處于 commit 狀態，表示已經通過了鎖沖突的檢驗了，

不用等到 commit 階段，只要能夠到達 redo log prepare 階段，就表示事務已經通過鎖沖突的檢驗了，

因此，MySQL 5.7 并行復制策略的思想是：

• 同時處于 prepare 狀態的事務，在備庫執行時是可以并行的；

• 處于 prepare 狀態的事務，與處于 commit 狀態的事務之間，在備庫執行時也是可以并行的，

• binlog_group_commit_sync_delay 引數，表示延遲多少微秒后才呼叫 fsync;

• binlog_group_commit_sync_no_delay_count 引數，表示累積多少次以后才呼叫 fsync，

這兩個引數是用于故意拉長 binlog 從 write 到 fsync 的時間，以此減少 binlog 的寫盤次數，在 MySQL 5.7 的并行復制策略里，它們可以用來制造更多的“同時處于 prepare 階段的事務”，這樣就增加了備庫復制的并行度，

MySQL 5.7.22 的并行復制策略

MySQL 5.7.22 版本里，MySQL 增加了一個新的并行復制策略，基于 WRITESET 的并行復制，

新增了一個引數 binlog-transaction-dependency-tracking，用來控制是否啟用這個新策略，這個引數的可選值有以下三種，

• COMMIT_ORDER，表示的就是前面介紹的，根據同時進入 prepare 和 commit 來判斷是否可以并行的策略，
• WRITESET，表示的是對于事務涉及更新的每一行，計算出這一行的 hash 值，組成集合 writeset，如果兩個事務沒有操作相同的行，也就是說它們的 writeset 沒有交集，就可以并行，
• WRITESET_SESSION，是在 WRITESET 的基礎上多了一個約束，即在主庫上同一個執行緒先后執行的兩個事務，在備庫執行的時候，要保證相同的先后順序，

當然為了唯一標識，這個 hash 值是通過“庫名 + 表名 + 索引名 + 值”計算出來的，如果一個表上除了有主鍵索引外，還有其他唯一索引，那么對于每個唯一索引，insert 陳述句對應的 writeset 就要多增加一個 hash 值，

這跟我們前面介紹的基于 MySQL 5.5 版本的按行分發的策略是差不多的，不過，MySQL 官方的這個實作還是有很大的優勢：

• writeset 是在主庫生成后直接寫入到 binlog 里面的，這樣在備庫執行的時候，不需要決議 binlog 內容（event 里的行資料），節省了很多計算量；
• 不需要把整個事務的 binlog 都掃一遍才能決定分發到哪個 worker，更省記憶體；
• 由于備庫的分發策略不依賴于 binlog 內容，所以 binlog 是 statement 格式也是可以的，

因此，MySQL 5.7.22 的并行復制策略在通用性上還是有保證的，
當然，對于“表上沒主鍵”和“外鍵約束”的場景，WRITESET 策略也是沒法并行的，也會暫時退化為單執行緒模型，

給出一道思考題:假設一個 MySQL 5.7.22 版本的主庫，單執行緒插入了很多資料，過了 3 個小時后，我們要給這個主庫搭建一個相同版本的備庫，這時候，你為了更快地讓備庫追上主庫，要開并行復制，

在 binlog-transaction-dependency-tracking 引數的 COMMIT_ORDER、WRITESET 和 WRITE_SESSION 這三個取值中，你會選擇哪一個呢？你選擇的原因是什么？如果設定另外兩個引數，你認為會出現什么現象呢？

這個問題的答案：引數設定為 WRITESET，

由于主庫是單執行緒壓力模式，所以每個事務的 commit_id 都不同，那么設定為 COMMIT_ORDER 模式的話，從庫也只能單執行緒執行，
由于 WRITESET_SESSION 模式要求在備庫應用日志的時候，同一個執行緒的日志必須與主庫上執行的先后順序相同，也會導致主庫單執行緒壓力模式下退化成單執行緒復制，

所以，應該將 binlog-transaction-dependency-tracking 設定為 WRITESET，

總結：
本章節是對資料庫大佬“丁奇”的學習總結！這是本人認為的一個比較不錯的學習資料，特對內容進行總結，方便各位進行學習！
覺得有用的客官可以點贊、關注下！各位的點贊+關注就是我持續更新的動力源泉! 🙏