MySQL日志篇（一）undo log、redo log、binlog 有什麼用？

我們知道了一條查詢語句經曆的過程，這屬于「讀」一條記錄的過程，如下圖：

那麼，執行一條 update 語句，期間發生了什麼？，比如這一條 update 語句：

UPDATE t_user SET name = 'xiaolin' WHERE id = 1;
           

查詢語句的那一套流程，更新語句也是同樣會走一遍：

• 用戶端先通過連接配接器建立連接配接，連接配接器自會判斷使用者身份；

• 因為這是一條 update 語句，是以不需要經過查詢緩存，但是表上有更新語句，是會把整個表的查詢緩存清空的，是以說查詢緩存很雞肋，在 MySQL 8.0 就被移除這個功能了；

• 解析器會通過詞法分析識别出關鍵字 update，表名等等，建構出文法樹，接着還會做文法分析，判斷輸入的語句是否符合 MySQL 文法；

• 預處理器會判斷表和字段是否存在；

• 優化器确定執行計劃，因為 where 條件中的 id 是主鍵索引，是以決定要使用 id 這個索引；

• 執行器負責具體執行，找到這一行，然後更新。

不過，更新語句的流程會涉及到 undo log（復原日志）、redo log（重做日志） 、binlog （歸檔日志）這三種日志：

• undo log（復原日志）：是 Innodb 存儲引擎層生成的日志，實作了事務中的原子性，主要用于事務復原和 MVCC。

• redo log（重做日志）：是 Innodb 存儲引擎層生成的日志，實作了事務中的持久性，主要用于掉電等故障恢複；

• binlog （歸檔日志）：是 Server 層生成的日志，主要用于資料備份和主從複制；

是以這次就帶着這個問題，看看這三種日志是怎麼工作的。

1、為什麼需要 undo log？

我們在執行執行一條“增删改”語句的時候，雖然沒有輸入 begin 開啟事務和 commit 送出事務，但是 MySQL 會隐式開啟事務來執行“增删改”語句的，執行完就自動送出事務的，這樣就保證了執行完“增删改”語句後，我們可以及時在資料庫表看到“增删改”的結果了。

執行一條語句是否自動送出事務，是由 autocommit 參數決定的，預設是開啟。是以，執行一條 update 語句也是會使用事務的。

那麼，考慮一個問題。一個事務在執行過程中，在還沒有送出事務之前，如果MySQL 發生了崩潰，要怎麼復原到事務之前的資料呢？

如果我們每次在事務執行過程中，都記錄下復原時需要的資訊到一個日志裡，那麼在事務執行中途發生了 MySQL 崩潰後，就不用擔心無法復原到事務之前的資料，我們可以通過這個日志復原到事務之前的資料。

實作這一機制就是 undo log（復原日志），它保證了事務的 ACID 特性 (opens new window) 中的原子性（Atomicity）。

undo log 是一種用于撤銷回退的日志。在事務沒送出之前，MySQL 會先将記錄更新前的資料到 undo log 日志檔案裡面，當事務復原時，可以利用 undo log 來進行復原。如下圖：

每當 InnoDB 引擎對一條記錄進行操作（修改、删除、新增）時，要把復原時需要的資訊都記錄到 undo log 裡，比如：

• 在插入一條記錄時，要把這條記錄的主鍵值記下來，這樣之後復原時隻需要把這個主鍵值對應的記錄删掉就好了；

• 在删除一條記錄時，要把這條記錄中的内容都記下來，這樣之後復原時再把由這些内容組成的記錄插入到表中就好了；

• 在更新一條記錄時，要把被更新的列的舊值記下來，這樣之後復原時再把這些列更新為舊值就好了。

在發生復原時，就讀取 undo log 裡的資料，然後做原先相反操作。比如當 delete 一條記錄時，undo log 中會把記錄中的内容都記下來，然後執行復原操作的時候，就讀取 undo log 裡的資料，然後進行 insert 操作。

不同的操作，需要記錄的内容也是不同的，是以不同類型的操作（修改、删除、新增）産生的 undo log 的格式也是不同的，具體的每一個操作的 undo log 的格式我就不詳細介紹了，感興趣的可以自己去查查。

一條記錄的每一次更新操作産生的 undo log 格式都有一個 roll_pointer 指針和一個 trx_id 事務id：

• 通過 trx_id 可以知道該記錄是被哪個事務修改的；

• 通過 roll_pointer 指針可以将這些 undo log 串成一個連結清單，這個連結清單就被稱為版本鍊；

版本鍊如下圖：

另外，undo log 還有一個作用，通過 ReadView + undo log 實作 MVCC（多版本并發控制）。

對于「讀送出」和「可重複讀」隔離級别的事務來說，它們的快照讀（普通 select 語句）是通過 Read View + undo log 來實作的，它們的差別在于建立 Read View 的時機不同：

• 「讀送出」隔離級别是在每個 select 都會生成一個新的 Read View，也意味着，事務期間的多次讀取同一條資料，前後兩次讀的資料可能會出現不一緻，因為可能這期間另外一個事務修改了該記錄，并送出了事務。

• 「可重複讀」隔離級别是啟動事務時生成一個 Read View，然後整個事務期間都在用這個 Read View，這樣就保證了在事務期間讀到的資料都是事務啟動前的記錄。

這兩個隔離級别實作是通過「事務的 Read View 裡的字段」和「記錄中的兩個隐藏列（trx_id 和 roll_pointer）」的比對，如果不滿足可見行，就會順着 undo log 版本鍊裡找到滿足其可見性的記錄，進而控制并發事務通路同一個記錄時的行為，這就叫 MVCC（多版本并發控制）。

是以，undo log 兩大作用：

• 實作事務復原，保障事務的原子性。事務處理過程中，如果出現了錯誤或者使用者執行了 ROLLBACK 語句，MySQL 可以利用 undo log 中的曆史資料将資料恢複到事務開始之前的狀态。

• 實作 MVCC（多版本并發控制）關鍵因素之一。MVCC 是通過 ReadView + undo log 實作的。undo log 為每條記錄儲存多份曆史資料，MySQL 在執行快照讀（普通 select 語句）的時候，會根據事務的 Read View 裡的資訊，順着 undo log 的版本鍊找到滿足其可見性的記錄。

TIP

很多人疑問 undo log 是如何刷盤（持久化到磁盤）的？

undo log 和資料頁的刷盤政策是一樣的，都需要通過 redo log 保證持久化。

buffer pool 中有 undo 頁，對 undo 頁的修改也都會記錄到 redo log。redo log 會每秒刷盤，送出事務時也會刷盤，資料頁和 undo 頁都是靠這個機制保證持久化的。

2、為什麼需要 Buffer Pool？

MySQL 的資料都是存在磁盤中的，那麼我們要更新一條記錄的時候，得先要從磁盤讀取該記錄，然後在記憶體中修改這條記錄。那修改完這條記錄是選擇直接寫回到磁盤，還是選擇緩存起來呢？

當然是緩存起來好，這樣下次有查詢語句命中了這條記錄，直接讀取緩存中的記錄，就不需要從磁盤擷取資料了。

為此，Innodb 存儲引擎設計了一個緩沖池（Buffer Pool），來提高資料庫的讀寫性能。

有了 Buffer Poo 後：

• 當讀取資料時，如果資料存在于 Buffer Pool 中，用戶端就會直接讀取 Buffer Pool 中的資料，否則再去磁盤中讀取。

• 當修改資料時，如果資料存在于 Buffer Pool 中，那直接修改 Buffer Pool 中資料所在的頁，然後将其頁設定為髒頁（該頁的記憶體資料和磁盤上的資料已經不一緻），為了減少磁盤I/O，不會立即将髒頁寫入磁盤，後續由背景線程選擇一個合适的時機将髒頁寫入到磁盤。

Buffer Pool 緩存什麼？

InnoDB 會把存儲的資料劃分為若幹個「頁」，以頁作為磁盤和記憶體互動的基本機關，一個頁的預設大小為 16KB。是以，Buffer Pool 同樣需要按「頁」來劃分。

在 MySQL 啟動的時候，InnoDB 會為 Buffer Pool 申請一片連續的記憶體空間，然後按照預設的16KB的大小劃分出一個個的頁， Buffer Pool 中的頁就叫做緩存頁。此時這些緩存頁都是空閑的，之後随着程式的運作，才會有磁盤上的頁被緩存到 Buffer Pool 中。

是以，MySQL 剛啟動的時候，你會觀察到使用的虛拟記憶體空間很大，而使用到的實體記憶體空間卻很小，這是因為隻有這些虛拟記憶體被通路後，作業系統才會觸發缺頁中斷，申請實體記憶體，接着将虛拟位址和實體位址建立映射關系。

Buffer Pool 除了緩存「索引頁」和「資料頁」，還包括了 Undo 頁，插入緩存、自适應哈希索引、鎖資訊等等。

Undo 頁是記錄什麼？

開啟事務後，InnoDB 層更新記錄前，首先要記錄相應的 undo log，如果是更新操作，需要把被更新的列的舊值記下來，也就是要生成一條 undo log，undo log 會寫入 Buffer Pool 中的 Undo 頁面。

查詢一條記錄，就隻需要緩沖一條記錄嗎？

不是的。

當我們查詢一條記錄時，InnoDB 是會把整個頁的資料加載到 Buffer Pool 中，将頁加載到 Buffer Pool 後，再通過頁裡的「頁目錄」去定位到某條具體的記錄。

3、為什麼需要 redo log ？

Buffer Pool 是提高了讀寫效率沒錯，但是問題來了，Buffer Pool 是基于記憶體的，而記憶體總是不可靠，萬一斷電重新開機，還沒來得及落盤的髒頁資料就會丢失。

為了防止斷電導緻資料丢失的問題，當有一條記錄需要更新的時候，InnoDB 引擎就會先更新記憶體（同時标記為髒頁），然後将本次對這個頁的修改以 redo log 的形式記錄下來，這個時候更新就算完成了。

後續，InnoDB 引擎會在适當的時候，由背景線程将緩存在 Buffer Pool 的髒頁重新整理到磁盤裡，這就是 WAL （Write-Ahead Logging）技術。

WAL 技術指的是， MySQL 的寫操作并不是立刻寫到磁盤上，而是先寫日志，然後在合适的時間再寫到磁盤上。

過程如下圖：

什麼是 redo log？

redo log 是實體日志，記錄了某個資料頁做了什麼修改，比如對 XXX 表空間中的 YYY 資料頁 ZZZ 偏移量的地方做了AAA 更新，每當執行一個事務就會産生這樣的一條或者多條實體日志。

在事務送出時，隻要先将 redo log 持久化到磁盤即可，可以不需要等到将緩存在 Buffer Pool 裡的髒頁資料持久化到磁盤。

當系統崩潰時，雖然髒頁資料沒有持久化，但是 redo log 已經持久化，接着 MySQL 重新開機後，可以根據 redo log 的内容，将所有資料恢複到最新的狀态。

被修改 Undo 頁面，需要記錄對應 redo log 嗎？

需要的。

開啟事務後，InnoDB 層更新記錄前，首先要記錄相應的 undo log，如果是更新操作，需要把被更新的列的舊值記下來，也就是要生成一條 undo log，undo log 會寫入 Buffer Pool 中的 Undo 頁面。

不過，在記憶體修改該 Undo 頁面後，需要記錄對應的 redo log。

redo log 和 undo log 差別在哪？

這兩種日志是屬于 InnoDB 存儲引擎的日志，它們的差別在于：

• redo log 記錄了此次事務「完成後」的資料狀态，記錄的是更新之後的值；

• undo log 記錄了此次事務「開始前」的資料狀态，記錄的是更新之前的值；

事務送出之前發生了崩潰，重新開機後會通過 undo log 復原事務，事務送出之後發生了崩潰，重新開機後會通過 redo log 恢複事務，如下圖：

是以有了 redo log，再通過 WAL 技術，InnoDB 就可以保證即使資料庫發生異常重新開機，之前已送出的記錄都不會丢失，這個能力稱為 crash-safe（崩潰恢複）。可以看出來， redo log 保證了事務四大特性中的持久性。

redo log 要寫到磁盤，資料也要寫磁盤，為什麼要多此一舉？

寫入 redo log 的方式使用了追加操作， 是以磁盤操作是順序寫，而寫入資料需要先找到寫入位置，然後才寫到磁盤，是以磁盤操作是随機寫。

磁盤的「順序寫 」比「随機寫」 高效的多，是以 redo log 寫入磁盤的開銷更小。

針對「順序寫」為什麼比「随機寫」更快這個問題，可以比喻為你有一個本子，按照順序一頁一頁寫肯定比寫一個字都要找到對應頁寫快得多。

可以說這是 WAL 技術的另外一個優點：MySQL 的寫操作從磁盤的「随機寫」變成了「順序寫」，提升語句的執行性能。這是因為 MySQL 的寫操作并不是立刻更新到磁盤上，而是先記錄在日志上，然後在合适的時間再更新到磁盤上 。

至此， 針對為什麼需要 redo log 這個問題我們有兩個答案：

• 實作事務的持久性，讓 MySQL 有 crash-safe 的能力，能夠保證 MySQL 在任何時間段突然崩潰，重新開機後之前已送出的記錄都不會丢失；

• 将寫操作從「随機寫」變成了「順序寫」，提升 MySQL 寫入磁盤的性能。

産生的 redo log 是直接寫入磁盤的嗎？

不是的。

實際上， 執行一個事務的過程中，産生的 redo log 也不是直接寫入磁盤的，因為這樣會産生大量的 I/O 操作，而且磁盤的運作速度遠慢于記憶體。

是以，redo log 也有自己的緩存—— redo log buffer，每當産生一條 redo log 時，會先寫入到 redo log buffer，後續在持久化到磁盤如下圖：

redo log buffer 預設大小 16 MB，可以通過 innodb_log_Buffer_size 參數動态的調整大小，增大它的大小可以讓 MySQL 處理「大事務」是不必寫入磁盤，進而提升寫 IO 性能。

redo log 什麼時候刷盤？

緩存在 redo log buffer 裡的 redo log 還是在記憶體中，它什麼時候重新整理到磁盤？

主要有下面幾個時機：

• MySQL 正常關閉時；

• 當 redo log buffer 中記錄的寫入量大于 redo log buffer 記憶體空間的一半時，會觸發落盤；

• InnoDB 的背景線程每隔 1 秒，将 redo log buffer 持久化到磁盤。

• 每次事務送出時都将緩存在 redo log buffer 裡的 redo log 直接持久化到磁盤（這個政策可由 innodb_flush_log_at_trx_commit 參數控制，下面會說）。

innodb_flush_log_at_trx_commit 參數控制的是什麼？

單獨執行一個更新語句的時候，InnoDB 引擎會自己啟動一個事務，在執行更新語句的過程中，生成的 redo log 先寫入到 redo log buffer 中，然後等事務送出的時候，再将緩存在 redo log buffer 中的 redo log 按組的方式「順序寫」到磁盤。

上面這種 redo log 刷盤時機是在事務送出的時候，這個預設的行為。

除此之外，InnoDB 還提供了另外兩種政策，由參數 innodb_flush_log_at_trx_commit 參數控制，可取的值有：0、1、2，預設值為 1，這三個值分别代表的政策如下：

• 當設定該參數為 0 時，表示每次事務送出時 ，還是将 redo log 留在 redo log buffer 中 ，該模式下在事務送出時不會主動觸發寫入磁盤的操作。

• 當設定該參數為 1 時，表示每次事務送出時，都将緩存在 redo log buffer 裡的 redo log 直接持久化到磁盤，這樣可以保證 MySQL 異常重新開機之後資料不會丢失。

• 當設定該參數為 2 時，表示每次事務送出時，都隻是緩存在 redo log buffer 裡的 redo log 寫到 redo log 檔案，注意寫入到「 redo log 檔案」并不意味着寫入到了磁盤，因為作業系統的檔案系統中有個 Page Cache，Page Cache 是專門用來緩存檔案資料的，是以寫入「 redo log檔案」意味着寫入到了作業系統的檔案緩存。

我畫了一個圖，友善大家了解：

innodb_flush_log_at_trx_commit 為 0 和 2 的時候，什麼時候才将 redo log 寫入磁盤？

InnoDB 的背景線程每隔 1 秒：

• 針對參數 0 ：會把緩存在 redo log buffer 中的 redo log ，通過調用 write() 寫到作業系統的 Page Cache，然後調用 fsync() 持久化到磁盤。是以參數為 0 的政策，MySQL 程序的崩潰會導緻上一秒鐘所有事務資料的丢失;

• 針對參數 2 ：調用 fsync，将緩存在作業系統中 Page Cache 裡的 redo log 持久化到磁盤。是以參數為 2 的政策，較取值為 0 情況下更安全，因為 MySQL 程序的崩潰并不會丢失資料，隻有在作業系統崩潰或者系統斷電的情況下，上一秒鐘所有事務資料才可能丢失。

加入了背景現線程後，innodb_flush_log_at_trx_commit 的刷盤時機如下圖：

這三個參數的應用場景是什麼？

這三個參數的資料安全性和寫入性能的比較如下：

• 資料安全性：參數 1 > 參數 2 > 參數 0

• 寫入性能：參數 0 > 參數 2> 參數 1

是以，資料安全性和寫入性能是熊掌不可得兼的，要不追求資料安全性，犧牲性能；要不追求性能，犧牲資料安全性。

• 在一些對資料安全性要求比較高的場景中，顯然 innodb_flush_log_at_trx_commit 參數需要設定為 1。

• 在一些可以容忍資料庫崩潰時丢失 1s 資料的場景中，我們可以将該值設定為 0，這樣可以明顯地減少日志同步到磁盤的 I/O 操作。

• 安全性和性能折中的方案就是參數 2，雖然參數 2 沒有參數 0 的性能高，但是資料安全性方面比參數 0 強，因為參數 2 隻要作業系統不當機，即使資料庫崩潰了，也不會丢失資料，同時性能友善比參數 1 高。

redo log 檔案寫滿了怎麼辦？

預設情況下， InnoDB 存儲引擎有 1 個重做日志檔案組( redo log Group），「重做日志檔案組」由有 2 個 redo log 檔案組成，這兩個 redo 日志的檔案名叫 ：ib_logfile0 和 ib_logfile1 。

在重做日志組中，每個 redo log File 的大小是固定且一緻的，假設每個 redo log File 設定的上限是 1 GB，那麼總共就可以記錄 2GB 的操作。

重做日志檔案組是以循環寫的方式工作的，從頭開始寫，寫到末尾就又回到開頭，相當于一個環形。

是以 InnoDB 存儲引擎會先寫 ib_logfile0 檔案，當 ib_logfile0 檔案被寫滿的時候，會切換至 ib_logfile1 檔案，當 ib_logfile1 檔案也被寫滿時，會切換回 ib_logfile0 檔案。

我們知道 redo log 是為了防止 Buffer Pool 中的髒頁丢失而設計的，那麼如果随着系統運作，Buffer Pool 的髒頁重新整理到了磁盤中，那麼 redo log 對應的記錄也就沒用了，這時候我們擦除這些舊記錄，以騰出空間記錄新的更新操作。

redo log 是循環寫的方式，相當于一個環形，InnoDB 用 write pos 表示 redo log 目前記錄寫到的位置，用 checkpoint 表示目前要擦除的位置，如下圖：

圖中的：

• write pos 和 checkpoint 的移動都是順時針方向；

• write pos ～ checkpoint 之間的部分（圖中的紅色部分），用來記錄新的更新操作；

• check point ～ write pos 之間的部分（圖中藍色部分）：待落盤的髒資料頁記錄；

如果 write pos 追上了 checkpoint，就意味着 redo log 檔案滿了，這時 MySQL 不能再執行新的更新操作，也就是說 MySQL 會被阻塞（是以是以針對并發量大的系統，适當設定 redo log 的檔案大小非常重要），此時會停下來将 Buffer Pool 中的髒頁重新整理到磁盤中，然後标記 redo log 哪些記錄可以被擦除，接着對舊的 redo log 記錄進行擦除，等擦除完舊記錄騰出了空間，checkpoint 就會往後移動（圖中順時針），然後 MySQL 恢複正常運作，繼續執行新的更新操作。

是以，一次 checkpoint 的過程就是髒頁重新整理到磁盤中變成幹淨頁，然後标記 redo log 哪些記錄可以被覆寫的過程。

4、為什麼需要 binlog ？

前面介紹的 undo log 和 redo log 這兩個日志都是 Innodb 存儲引擎生成的。

MySQL 在完成一條更新操作後，Server 層還會生成一條 binlog，等之後事務送出的時候，會将該事物執行過程中産生的所有 binlog 統一寫入 binlog 檔案。

binlog 檔案是記錄了所有資料庫表結構變更和表資料修改的日志，不會記錄查詢類的操作，比如 SELECT 和 SHOW 操作。

redo log 和 binlog 有什麼差別？

這兩個日志有四個差別。

1、适用對象不同：

• binlog 是 MySQL 的 Server 層實作的日志，所有存儲引擎都可以使用；

• redo log 是 Innodb 存儲引擎實作的日志；

2、檔案格式不同：

• binlog 有 3 種格式類型，分别是 STATEMENT（預設格式）、ROW、 MIXED，差別如下：

• STATEMENT：每一條修改資料的 SQL 都會被記錄到 binlog 中（相當于記錄了邏輯操作，是以針對這種格式， binlog 可以稱為邏輯日志），主從複制中 slave 端再根據 SQL 語句重制。但 STATEMENT 有動态函數的問題，比如你用了 uuid 或者 now 這些函數，你在主庫上執行的結果并不是你在從庫執行的結果，這種随時在變的函數會導緻複制的資料不一緻；

• ROW：記錄行資料最終被修改成什麼樣了（這種格式的日志，就不能稱為邏輯日志了），不會出現 STATEMENT 下動态函數的問題。但 ROW 的缺點是每行資料的變化結果都會被記錄，比如執行批量 update 語句，更新多少行資料就會産生多少條記錄，使 binlog 檔案過大，而在 STATEMENT 格式下隻會記錄一個 update 語句而已；

• MIXED：包含了 STATEMENT 和 ROW 模式，它會根據不同的情況自動使用 ROW 模式和 STATEMENT 模式；

• redo log 是實體日志，記錄的是在某個資料頁做了什麼修改，比如對 XXX 表空間中的 YYY 資料頁 ZZZ 偏移量的地方做了AAA 更新；

3、寫入方式不同：

• binlog 是追加寫，寫滿一個檔案，就建立一個新的檔案繼續寫，不會覆寫以前的日志，儲存的是全量的日志。

• redo log 是循環寫，日志空間大小是固定，全部寫滿就從頭開始，儲存未被刷入磁盤的髒頁日志。

4、用途不同：

• binlog 用于備份恢複、主從複制；

• redo log 用于掉電等故障恢複。

如果不小心整個資料庫的資料被删除了，能使用 redo log 檔案恢複資料嗎？

不可以使用 redo log 檔案恢複，隻能使用 binlog 檔案恢複。

因為 redo log 檔案是循環寫，是會邊寫邊擦除日志的，隻記錄未被刷入磁盤的資料的實體日志，已經刷入磁盤的資料都會從 redo log 檔案裡擦除。

binlog 檔案儲存的是全量的日志，也就是儲存了所有資料變更的情況，理論上隻要記錄在 binlog 上的資料，都可以恢複，是以如果不小心整個資料庫的資料被删除了，得用 binlog 檔案恢複資料。

主從複制是怎麼實作？

MySQL 的主從複制依賴于 binlog ，也就是記錄 MySQL 上的所有變化并以二進制形式儲存在磁盤上。複制的過程就是将 binlog 中的資料從主庫傳輸到從庫上。

這個過程一般是異步的，也就是主庫上執行事務操作的線程不會等待複制 binlog 的線程同步完成。

MySQL 叢集的主從複制過程梳理成 3 個階段：

• 寫入 Binlog：主庫寫 binlog 日志，送出事務，并更新本地存儲資料。

• 同步 Binlog：把 binlog 複制到所有從庫上，每個從庫把 binlog 寫到暫存日志中。

• 回放 Binlog：回放 binlog，并更新存儲引擎中的資料。

具體詳細過程如下：

• MySQL 主庫在收到用戶端送出事務的請求之後，會先寫入 binlog，再送出事務，更新存儲引擎中的資料，事務送出完成後，傳回給用戶端“操作成功”的響應。

• 從庫會建立一個專門的 I/O 線程，連接配接主庫的 log dump 線程，來接收主庫的 binlog 日志，再把 binlog 資訊寫入 relay log 的中繼日志裡，再傳回給主庫“複制成功”的響應。

• 從庫會建立一個用于回放 binlog 的線程，去讀 relay log 中繼日志，然後回放 binlog 更新存儲引擎中的資料，最終實作主從的資料一緻性。

在完成主從複制之後，你就可以在寫資料時隻寫主庫，在讀資料時隻讀從庫，這樣即使寫請求會鎖表或者鎖記錄，也不會影響讀請求的執行。

從庫是不是越多越好？

不是的。

因為從庫數量增加，從庫連接配接上來的 I/O 線程也比較多，主庫也要建立同樣多的 log dump 線程來處理複制的請求，對主庫資源消耗比較高，同時還受限于主庫的網絡帶寬。

是以在實際使用中，一個主庫一般跟 2～3 個從庫（1 套資料庫，1 主 2 從 1 備主），這就是一主多從的 MySQL 叢集結構。

MySQL 主從複制還有哪些模型？

主要有三種：

• 同步複制：MySQL 主庫送出事務的線程要等待所有從庫的複制成功響應，才傳回用戶端結果。這種方式在實際項目中，基本上沒法用，原因有兩個：一是性能很差，因為要複制到所有節點才傳回響應；二是可用性也很差，主庫和所有從庫任何一個資料庫出問題，都會影響業務。

• 異步複制（預設模型）：MySQL 主庫送出事務的線程并不會等待 binlog 同步到各從庫，就傳回用戶端結果。這種模式一旦主庫當機，資料就會發生丢失。

• 半同步複制：MySQL 5.7 版本之後增加的一種複制方式，介于兩者之間，事務線程不用等待所有的從庫複制成功響應，隻要一部分複制成功響應回來就行，比如一主二從的叢集，隻要資料成功複制到任意一個從庫上，主庫的事務線程就可以傳回給用戶端。這種半同步複制的方式，兼顧了異步複制和同步複制的優點，即使出現主庫當機，至少還有一個從庫有最新的資料，不存在資料丢失的風險。

binlog 什麼時候刷盤？

事務執行過程中，先把日志寫到 binlog cache（Server 層的 cache），事務送出的時候，再把 binlog cache 寫到 binlog 檔案中。

一個事務的 binlog 是不能被拆開的，是以無論這個事務有多大（比如有很多條語句），也要保證一次性寫入。這是因為一個線程隻能同時有一個事務在執行的設定，是以每當執行一個 begin/start transaction 的時候，就會預設送出上一個事務，這樣如果一個事務的 binlog 被拆開的時候，在備庫執行就會被當做多個事務分段自行，這樣破壞了原子性，是有問題的。

MySQL 給每個線程配置設定了一片記憶體用于緩沖 binlog ，該記憶體叫 binlog cache，參數 binlog_cache_size 用于控制單個線程内 binlog cache 所占記憶體的大小。如果超過了這個參數規定的大小，就要暫存到磁盤。

什麼時候 binlog cache 會寫到 binlog 檔案？

在事務送出的時候，執行器把 binlog cache 裡的完整事務寫入到 binlog 檔案中，并清空 binlog cache。如下圖：

雖然每個線程有自己 binlog cache，但是最終都寫到同一個 binlog 檔案：

• 圖中的 write，指的就是指把日志寫入到 binlog 檔案，但是并沒有把資料持久化到磁盤，因為資料還緩存在檔案系統的 page cache 裡，write 的寫入速度還是比較快的，因為不涉及磁盤 I/O。

• 圖中的 fsync，才是将資料持久化到磁盤的操作，這裡就會涉及磁盤 I/O，是以頻繁的 fsync 會導緻磁盤的 I/O 升高。

MySQL提供一個 sync_binlog 參數來控制資料庫的 binlog 刷到磁盤上的頻率：

• sync_binlog = 0 的時候，表示每次送出事務都隻 write，不 fsync，後續交由作業系統決定何時将資料持久化到磁盤；

• sync_binlog = 1 的時候，表示每次送出事務都會 write，然後馬上執行 fsync；

• sync_binlog =N(N>1) 的時候，表示每次送出事務都 write，但累積 N 個事務後才 fsync。

在MySQL中系統預設的設定是 sync_binlog = 0，也就是不做任何強制性的磁盤重新整理指令，這時候的性能是最好的，但是風險也是最大的。因為一旦主機發生異常重新開機，還沒持久化到磁盤的資料就會丢失。

而當 sync_binlog 設定為 1 的時候，是最安全但是性能損耗最大的設定。因為當設定為 1 的時候，即使主機發生異常重新開機，最多丢失一個事務的 binlog，而已經持久化到磁盤的資料就不會有影響，不過就是對寫入性能影響太大。

如果能容少量事務的 binlog 日志丢失的風險，為了提高寫入的性能，一般會 sync_binlog 設定為 100~1000 中的某個數值。

三個日志講完了，至此我們可以先小結下，update 語句的執行過程。

當優化器分析出成本最小的執行計劃後，執行器就按照執行計劃開始進行更新操作。

具體更新一條記錄 UPDATE t_user SET name = 'xiaolin' WHERE id = 1; 的流程如下:

1. 執行器負責具體執行，會調用存儲引擎的接口，通過主鍵索引樹搜尋擷取 id = 1 這一行記錄：

• 如果 id=1 這一行所在的資料頁本來就在 buffer pool 中，就直接傳回給執行器更新；

• 如果記錄不在 buffer pool，将資料頁從磁盤讀入到 buffer pool，傳回記錄給執行器。

1. 執行器得到聚簇索引記錄後，會看一下更新前的記錄和更新後的記錄是否一樣：

• 如果一樣的話就不進行後續更新流程；

• 如果不一樣的話就把更新前的記錄和更新後的記錄都當作參數傳給 InnoDB 層，讓 InnoDB 真正的執行更新記錄的操作；

1. 開啟事務， InnoDB 層更新記錄前，首先要記錄相應的 undo log，因為這是更新操作，需要把被更新的列的舊值記下來，也就是要生成一條 undo log，undo log 會寫入 Buffer Pool 中的 Undo 頁面，不過在記憶體修改該 Undo 頁面後，需要記錄對應的 redo log。

1. InnoDB 層開始更新記錄，會先更新記憶體（同時标記為髒頁），然後将記錄寫到 redo log 裡面，這個時候更新就算完成了。為了減少磁盤I/O，不會立即将髒頁寫入磁盤，後續由背景線程選擇一個合适的時機将髒頁寫入到磁盤。這就是 WAL 技術，MySQL 的寫操作并不是立刻寫到磁盤上，而是先寫 redo 日志，然後在合适的時間再将修改的行資料寫到磁盤上。

1. 至此，一條記錄更新完了。

1. 在一條更新語句執行完成後，然後開始記錄該語句對應的 binlog，此時記錄的 binlog 會被儲存到 binlog cache，并沒有重新整理到硬碟上的 binlog 檔案，在事務送出時才會統一将該事務運作過程中的所有 binlog 重新整理到硬碟。

1. 事務送出，剩下的就是「兩階段送出」的事情了，接下來就講這個。

5、為什麼需要兩階段送出？

事務送出後，redo log 和 binlog 都要持久化到磁盤，但是這兩個是獨立的邏輯，可能出現半成功的狀态，這樣就造成兩份日志之間的邏輯不一緻。

舉個例子，假設 id = 1 這行資料的字段 name 的值原本是 'jay'，然後執行 UPDATE t_user SET name = 'xiaolin' WHERE id = 1; 如果在持久化 redo log 和 binlog 兩個日志的過程中，出現了半成功狀态，那麼就有兩種情況：

• 如果在将 redo log 刷入到磁盤之後， MySQL 突然當機了，而 binlog 還沒有來得及寫入。MySQL 重新開機後，通過 redo log 能将 Buffer Pool 中 id = 1 這行資料的 name 字段恢複到新值 xiaolin，但是 binlog 裡面沒有記錄這條更新語句，在主從架構中，binlog 會被複制到從庫，由于 binlog 丢失了這條更新語句，從庫的這一行 name 字段是舊值 jay，與主庫的值不一緻性；

• 如果在将 binlog 刷入到磁盤之後， MySQL 突然當機了，而 redo log 還沒有來得及寫入。由于 redo log 還沒寫，崩潰恢複以後這個事務無效，是以 id = 1 這行資料的 name 字段還是舊值 jay，而 binlog 裡面記錄了這條更新語句，在主從架構中，binlog 會被複制到從庫，從庫執行了這條更新語句，那麼這一行 name 字段是新值 xiaolin，與主庫的值不一緻性；

可以看到，在持久化 redo log 和 binlog 這兩份日志的時候，如果出現半成功的狀态，就會造成主從環境的資料不一緻性。這是因為 redo log 影響主庫的資料，binlog 影響從庫的資料，是以 redo log 和 binlog 必須保持一緻才能保證主從資料一緻。

MySQL 為了避免出現兩份日志之間的邏輯不一緻的問題，使用了「兩階段送出」來解決，兩階段送出其實是分布式事務一緻性協定，它可以保證多個邏輯操作要不全部成功，要不全部失敗，不會出現半成功的狀态。

兩階段送出把單個事務的送出拆分成了 2 個階段，分别是「準備（Prepare）階段」和「送出（Commit）階段」，每個階段都由協調者（Coordinator）和參與者（Participant）共同完成。注意，不要把送出（Commit）階段和 commit 語句混淆了，commit 語句執行的時候，會包含送出（Commit）階段。

兩階段送出的過程是怎樣的？

在 MySQL 的 InnoDB 存儲引擎中，開啟 binlog 的情況下，MySQL 會同時維護 binlog 日志與 InnoDB 的 redo log，為了保證這兩個日志的一緻性，MySQL 使用了内部 XA 事務，内部 XA 事務由 binlog 作為協調者，存儲引擎是參與者。

當用戶端執行 commit 語句或者在自動送出的情況下，MySQL 内部開啟一個 XA 事務，分兩階段來完成 XA 事務的送出，如下圖：

從圖中可看出，事務的送出過程有兩個階段，就是将 redo log 的寫入拆成了兩個步驟：prepare 和 commit，中間再穿插寫入binlog，具體如下：

• prepare 階段：将 XID（内部 XA 事務的 ID） 寫入到 redo log，同時将 redo log 對應的事務狀态設定為 prepare，然後将 redo log 持久化到磁盤（innodb_flush_log_at_trx_commit = 1 的作用）；

• commit 階段：把 XID 寫入到 binlog，然後将 binlog 持久化到磁盤（sync_binlog = 1 的作用），接着調用引擎的送出事務接口，将 redo log 狀态設定為 commit，此時該狀态并不需要持久化到磁盤，隻需要 write 到檔案系統的 page cache 中就夠了，因為隻要 binlog 寫磁盤成功，就算 redo log 的狀态還是 prepare 也沒有關系，一樣會被認為事務已經執行成功；

異常重新開機會出現什麼現象？

我們來看看在兩階段送出的不同時刻，MySQL 異常重新開機會出現什麼現象？下圖中有時刻 A 和時刻 B 都有可能發生崩潰：

不管是時刻 A（redo log 已經寫入磁盤， binlog 還沒寫入磁盤），還是時刻 B （redo log 和 binlog 都已經寫入磁盤，還沒寫入 commit 辨別）崩潰，此時的 redo log 都處于 prepare 狀态。

在 MySQL 重新開機後會按順序掃描 redo log 檔案，碰到處于 prepare 狀态的 redo log，就拿着 redo log 中的 XID 去 binlog 檢視是否存在此 XID：

• 如果 binlog 中沒有目前内部 XA 事務的 XID，說明 redolog 完成刷盤，但是 binlog 還沒有刷盤，則復原事務。對應時刻 A 崩潰恢複的情況。

• 如果 binlog 中有目前内部 XA 事務的 XID，說明 redolog 和 binlog 都已經完成了刷盤，則送出事務。對應時刻 B 崩潰恢複的情況。

可以看到，對于處于 prepare 階段的 redo log，即可以送出事務，也可以復原事務，這取決于是否能在 binlog 中查找到與 redo log 相同的 XID，如果有就送出事務，如果沒有就復原事務。這樣就可以保證 redo log 和 binlog 這兩份日志的一緻性了。

是以說，兩階段送出是以 binlog 寫成功為事務送出成功的辨別，因為 binlog 寫成功了，就意味着能在 binlog 中查找到與 redo log 相同的 XID。

處于 prepare 階段的 redo log 加上完整 binlog，重新開機就送出事務，MySQL 為什麼要這麼設計?

binlog 已經寫入了，之後就會被從庫（或者用這個 binlog 恢複出來的庫）使用。

是以，在主庫上也要送出這個事務。采用這個政策，主庫和備庫的資料就保證了一緻性。

事務沒送出的時候，redo log 會被持久化到磁盤嗎？

會的。

事務執行中間過程的 redo log 也是直接寫在 redo log buffer 中的，這些緩存在 redo log buffer 裡的 redo log 也會被「背景線程」每隔一秒一起持久化到磁盤。

也就是說，事務沒送出的時候，redo log 也是可能被持久化到磁盤的。

有的同學可能會問，如果 mysql 崩潰了，還沒送出事務的 redo log 已經被持久化磁盤了，mysql 重新開機後，資料不就不一緻了？

放心，這種情況 mysql 重新開機會進行復原操作，因為事務沒送出的時候，binlog 是還沒持久化到磁盤的。

是以， redo log 可以在事務沒送出之前持久化到磁盤，但是 binlog 必須在事務送出之後，才可以持久化到磁盤。

6、兩階段送出有什麼問題？

兩階段送出雖然保證了兩個日志檔案的資料一緻性，但是性能很差，主要有兩個方面的影響：

• 磁盤 I/O 次數高：對于“雙1”配置，每個事務送出都會進行兩次 fsync（刷盤），一次是 redo log 刷盤，另一次是 binlog 刷盤。

• 鎖競争激烈：兩階段送出雖然能夠保證「單事務」兩個日志的内容一緻，但在「多事務」的情況下，卻不能保證兩者的送出順序一緻，是以，在兩階段送出的流程基礎上，還需要加一個鎖來保證送出的原子性，進而保證多事務的情況下，兩個日志的送出順序一緻。

為什麼兩階段送出的磁盤 I/O 次數會很高？

binlog 和 redo log 在記憶體中都對應的緩存空間，binlog 會緩存在 binlog cache，redo log 會緩存在 redo log buffer，它們持久化到磁盤的時機分别由下面這兩個參數控制。一般我們為了避免日志丢失的風險，會将這兩個參數設定為 1：

• 當 sync_binlog = 1 的時候，表示每次送出事務都會将 binlog cache 裡的 binlog 直接持久到磁盤；

• 當 innodb_flush_log_at_trx_commit = 1 時，表示每次事務送出時，都将緩存在 redo log buffer 裡的 redo log 直接持久化到磁盤；

可以看到，如果 sync_binlog 和 當 innodb_flush_log_at_trx_commit 都設定為 1，那麼在每個事務送出過程中， 都會至少調用 2 次刷盤操作，一次是 redo log 刷盤，一次是 binlog 落盤，是以這會成為性能瓶頸。

為什麼鎖競争激烈？

在早期的 MySQL 版本中，通過使用 prepare_commit_mutex 鎖來保證事務送出的順序，在一個事務擷取到鎖時才能進入 prepare 階段，一直到 commit 階段結束才能釋放鎖，下個事務才可以繼續進行 prepare 操作。

通過加鎖雖然完美地解決了順序一緻性的問題，但在并發量較大的時候，就會導緻對鎖的争用，性能不佳。

組送出

MySQL 引入了 binlog 組送出（group commit）機制，當有多個事務送出的時候，會将多個 binlog 刷盤操作合并成一個，進而減少磁盤 I/O 的次數，如果說 10 個事務依次排隊刷盤的時間成本是 10，那麼将這 10 個事務一次性一起刷盤的時間成本則近似于 1。

引入了組送出機制後，prepare 階段不變，隻針對 commit 階段，将 commit 階段拆分為三個過程：

• flush 階段：多個事務按進入的順序将 binlog 從 cache 寫入檔案（不刷盤）；

• sync 階段：對 binlog 檔案做 fsync 操作（多個事務的 binlog 合并一次刷盤）；

• commit 階段：各個事務按順序做 InnoDB commit 操作；

上面的每個階段都有一個隊列，每個階段有鎖進行保護，是以保證了事務寫入的順序，第一個進入隊列的事務會成為 leader，leader上司所在隊列的所有事務，全權負責整隊的操作，完成後通知隊内其他事務操作結束。

對每個階段引入了隊列後，鎖就隻針對每個隊列進行保護，不再鎖住送出事務的整個過程，可以看的出來，鎖粒度減小了，這樣就使得多個階段可以并發執行，進而提升效率。

有 binlog 組送出，那有 redo log 組送出嗎？

這個要看 MySQL 版本，MySQL 5.6 沒有 redo log 組送出，MySQL 5.7 有 redo log 組送出。

在 MySQL 5.6 的組送出邏輯中，每個事務各自執行 prepare 階段，也就是各自将 redo log 刷盤，這樣就沒辦法對 redo log 進行組送出。

是以在 MySQL 5.7 版本中，做了個改進，在 prepare 階段不再讓事務各自執行 redo log 刷盤操作，而是推遲到組送出的 flush 階段，也就是說 prepare 階段融合在了 flush 階段。

這個優化是将 redo log 的刷盤延遲到了 flush 階段之中，sync 階段之前。通過延遲寫 redo log 的方式，為 redolog 做了一次組寫入，這樣 binlog 和 redo log 都進行了優化。

接下來介紹每個階段的過程，注意下面的過程針對的是“雙 1” 配置（sync_binlog 和 innodb_flush_log_at_trx_commit 都配置為 1）。

flush 階段

第一個事務會成為 flush 階段的 Leader，此時後面到來的事務都是 Follower ：

接着，擷取隊列中的事務組，由綠色事務組的 Leader 對 rodo log 做一次 write + fsync，即一次将同組事務的 redolog 刷盤：

完成了 prepare 階段後，将綠色這一組事務執行過程中産生的 binlog 寫入 binlog 檔案（調用 write，不會調用 fsync，是以不會刷盤，binlog 緩存在作業系統的檔案系統中）。

從上面這個過程，可以知道 flush 階段隊列的作用是用于支撐 redo log 的組送出。

如果在這一步完成後資料庫崩潰，由于 binlog 中沒有該組事務的記錄，是以 MySQL 會在重新開機後復原該組事務。

sync 階段

綠色這一組事務的 binlog 寫入到 binlog 檔案後，并不會馬上執行刷盤的操作，而是會等待一段時間，這個等待的時長由 Binlog_group_commit_sync_delay 參數控制，目的是為了組合更多事務的 binlog，然後再一起刷盤（此時隊列可能沒滿，後面會有新的事務過來）如下過程：

不過，在等待的過程中，如果事務的數量提前達到了 Binlog_group_commit_sync_no_delay_count 參數設定的值，就不用繼續等待了，就馬上将 binlog 刷盤，如下圖：

從上面的過程，可以知道 sync 階段隊列的作用是用于支援 binlog 的組送出。

如果想提升 binlog 組送出的效果，可以通過設定下面這兩個參數來實作：

• binlog_group_commit_sync_delay= N，表示在等待 N 微妙後，直接調用 fsync，将處于檔案系統中 page cache 中的 binlog 刷盤，也就是将「 binlog 檔案」持久化到磁盤。

• binlog_group_commit_sync_no_delay_count = N，表示如果隊列中的事務數達到 N 個，就忽視binlog_group_commit_sync_delay 的設定，直接調用 fsync，将處于檔案系統中 page cache 中的 binlog 刷盤。

如果在這一步完成後資料庫崩潰，由于 binlog 中已經有了事務記錄，MySQL會在重新開機後通過 redo log 刷盤的資料繼續進行事務的送出。

commit 階段

最後進入 commit 階段，調用引擎的送出事務接口，将 redo log 狀态設定為 commit。

commit 階段隊列的作用是承接 sync 階段的事務，完成最後的引擎送出，使得 sync 可以盡早的處理下一組事務，最大化組送出的效率。

7、MySQL 磁盤 I/O 很高，有什麼優化的方法？

現在我們知道事務在送出的時候，需要将 binlog 和 redo log 持久化到磁盤，那麼如果出現 MySQL 磁盤 I/O 很高的現象，我們可以通過控制以下參數，來 “延遲” binlog 和 redo log 刷盤的時機，進而降低磁盤 I/O 的頻率：

• 設定組送出的兩個參數： binlog_group_commit_sync_delay 和 binlog_group_commit_sync_no_delay_count 參數，延遲 binlog 刷盤的時機，進而減少 binlog 的刷盤次數。這個方法是基于“額外的故意等待”來實作的，是以可能會增加語句的響應時間，但即使 MySQL 程序中途挂了，也沒有丢失資料的風險，因為 binlog 早被寫入到 page cache 了，隻要系統沒有當機，緩存在 page cache 裡的 binlog 就會被持久化到磁盤。

• 将 sync_binlog 設定為大于 1 的值（比較常見是 100~1000），表示每次送出事務都 write，但累積 N 個事務後才 fsync，相當于延遲了 binlog 刷盤的時機。但是這樣做的風險是，主機掉電時會丢 N 個事務的 binlog 日志。

• 将 innodb_flush_log_at_trx_commit 設定為 2。表示每次事務送出時，都隻是緩存在 redo log buffer 裡的 redo log 寫到 redo log 檔案，注意寫入到「 redo log 檔案」并不意味着寫入到了磁盤，因為作業系統的檔案系統中有個 Page Cache，專門用來緩存檔案資料的，是以寫入「 redo log檔案」意味着寫入到了作業系統的檔案緩存，然後交由作業系統控制持久化到磁盤的時機。但是這樣做的風險是，主機掉電的時候會丢資料。

8、總結

具體更新一條記錄 UPDATE t_user SET name = 'xiaolin' WHERE id = 1; 的流程如下:

1. 執行器負責具體執行，會調用存儲引擎的接口，通過主鍵索引樹搜尋擷取 id = 1 這一行記錄：

• 如果 id=1 這一行所在的資料頁本來就在 buffer pool 中，就直接傳回給執行器更新；

• 如果記錄不在 buffer pool，将資料頁從磁盤讀入到 buffer pool，傳回記錄給執行器。

1. 執行器得到聚簇索引記錄後，會看一下更新前的記錄和更新後的記錄是否一樣：

• 如果一樣的話就不進行後續更新流程；

• 如果不一樣的話就把更新前的記錄和更新後的記錄都當作參數傳給 InnoDB 層，讓 InnoDB 真正的執行更新記錄的操作；

1. 開啟事務， InnoDB 層更新記錄前，首先要記錄相應的 undo log，因為這是更新操作，需要把被更新的列的舊值記下來，也就是要生成一條 undo log，undo log 會寫入 Buffer Pool 中的 Undo 頁面，不過在記憶體修改該 Undo 頁面後，需要記錄對應的 redo log。

1. InnoDB 層開始更新記錄，會先更新記憶體（同時标記為髒頁），然後将記錄寫到 redo log 裡面，這個時候更新就算完成了。為了減少磁盤I/O，不會立即将髒頁寫入磁盤，後續由背景線程選擇一個合适的時機将髒頁寫入到磁盤。這就是 WAL 技術，MySQL 的寫操作并不是立刻寫到磁盤上，而是先寫 redo 日志，然後在合适的時間再将修改的行資料寫到磁盤上。

1. 至此，一條記錄更新完了。

1. 在一條更新語句執行完成後，然後開始記錄該語句對應的 binlog，此時記錄的 binlog 會被儲存到 binlog cache，并沒有重新整理到硬碟上的 binlog 檔案，在事務送出時才會統一将該事務運作過程中的所有 binlog 重新整理到硬碟。

1. 事務送出（為了友善說明，這裡不說組送出的過程，隻說兩階段送出）：

• prepare 階段：将 redo log 對應的事務狀态設定為 prepare，然後将 redo log 重新整理到硬碟；

• commit 階段：将 binlog 重新整理到磁盤，接着調用引擎的送出事務接口，将 redo log 狀态設定為 commit（将事務設定為 commit 狀态後，刷入到磁盤 redo log 檔案）；

1. 至此，一條更新語句執行完成。