幾年了，作為一個碼農終於把MySQL日記看懂了頭條網

2020-12-11 09:29:40 佚名

一、寫作背景

大家都清楚，日誌是 MySQL數據庫的重要組成部分，記錄著數據庫運行期間各種狀態信息。MySQL日誌主要包括錯誤日誌、查詢日誌、慢查詢日誌、二進制日誌(binlog)和事務日誌(redo log、undo log)幾大類。

其中，二進制日記和事務日記尤為重要，一直被人重視、深入研究；可是事實很殘忍，重視或者說大多數人一般都是瞭解個表面，真正懂得人並不多。真想攻破這兩塊日記必須下血本，而且還不一定能攻破。但是不要緊，為了讓你們省下血本還能順利攻破這兩塊日記，我連續研究幾周MySQL日記，最終肝出了這篇文章。

二、文章指引

文章指導：文章第三節內容切莫跳過，但如果覺得第四、第五、第六和第七節沒意思或者已經有了概念，直接進入文章第八節一舉攻破拿下。

文章方向：理論、原理篇。

三、必要概念字典介紹

基礎不牢地動山搖，還是常規套路，先把必要知識普及/溫習一遍，當後續文章出現疑慮反過來看下這些概念字典，說不定能 “柳暗花明又一村” 呢？

寫了又寫，想了又想，糾結了好久，這部分知識確實有點多，最後還是決定將這些必要概念字典單獨分出一個文章，後續打算用截圖方式引入各個章節中，建議遇到不懂名詞查閱一下字典。

圖1：進階知識部分示意圖

四、認識二進制日記（Binlog）

4.1 Binlog概念

Binlog 是邏輯日記，用於記錄數據庫執行的寫入操作（查詢不記錄）信息，Server層記錄和引擎層無關，並且是以追加方式進行寫入，可以通過參數 max_binlog_size 設置每個Binlog文件的大小，文件大小達到設定值時會生成新的文件來保存日記。

4.2 Binlog 作用

在實際應用中，主要用在兩個場景：主從複製和數據恢復

主從複製場景：在Master主端開啟Binlog，將Binlog發生到各個Slave從端，Slave從端重放Binlog從而達到主從數據一致
數據恢復場景：通過使用 mysqlbinlog 工具來恢復數據

4.3 Binlog 記錄過程及刷盤時機

Binlog何時記錄將在第六點進行介紹，大致記錄過程是先寫Binlog Buffer，然後通過刷盤時機，控制刷入OS Buffer，控制fsync()進行寫入Binlog File日記磁盤的過程。

對於Binlog，MySQL是通過參數sync_binlog參數來控制刷盤時機，取值是0、1和N三種值。0表示由系統自行判斷何時調用sync()寫入磁盤；1表示每次事務commit都要調用fsync()寫入磁盤；N表示每N個事務，才會調用fsync()寫入磁盤。

圖2：內存和磁盤日記結構圖

4.4 Binlog 記錄格式

MySQL5.7.7版本之前默認格式是STATEMENT，版本之後默認是ROW，可以通過參數 binlog-format指定。

五、認識事務日記（Undo log）

5.1 Undo log 概念

Undo log是邏輯日記、回滾日記。比如一條修改+3的邏輯語句，Undo log會記錄對應一條-3的邏輯日記，一條插入語句則會記錄一條刪除語句，這樣發生錯誤時，根據執行Undo log就可以回滾到事務之前的數據狀態。

5.2 Undo log 作用

回滾數據：當程序發生異常錯誤時等，根據執行Undo log就可以回滾到事務之前的數據狀態，保證原子性，要麼成功要麼失敗。
MVCC一致性視圖：通過Undo log找到對應的數據版本號，是保證MVCC視圖的一致性的必要條件。

5.3 Undo log 記錄過程及刷盤時機

刷盤過程及時機類似於Binlog和Redo，可以參考Redo log刷盤時機章節給出的圖片，已經體現出來了。

5.4 Undo log 總結

Undo log日記內容不是很多，重點是回滾和多版本控制MVCC那塊。此外，我記得印象筆記深刻的是長事務會導致日記過多，這個日記就是Undo log。因為長事務存在，導致需要保存很多視圖快照，其實這裡就是涉及到Undo log何時刪除和生成的問題，當時糾結好久，其實很簡單。生成是事務開始後寫Redo log之前生成，當沒有事務需要用到Undo log時就會被刪除。舉個例子，如果事務A一直存活，那麼事務A之後產生的事務B、C...等等就算提交了，也不會被刪除，因為事務A需要用到B、C...事務去找A的版本。所以避免長事務可以減少Undo log日記量，當然還可以提高性能。

六、認識事務日記（Redo log）

6.1 Redo log 概念

Redo log 是重做日記，屬於InnoDB引擎的日記。是物理日記，日記記錄的內容的是數據頁的更改，這個頁 “做了什麼改動”。如：add xx記錄 to Page1，向數據頁Page1增加一個記錄。

6.2 Redo log 作用

前滾操作：具備crash-safe能力，提供斷電重啟時解決事務丟失數據問題。
提高性能：先寫Redo log記錄更新。當等到有空閒線程、內存不足、Redo log滿了時 “刷髒
”。寫Redo log是順序寫入，刷髒是隨機寫，
節省的是隨機寫磁盤的 IO 消耗（轉成順序寫），所以性能得到提升。此技術稱為WAL技術：Write-Ahead Logging，它的關鍵點就是先寫日記磁盤
，再寫數據磁盤
。

6.3 Redo log 的兩階段提交

更新內存後引擎層寫Redo log將狀態改成prepare為預提交第一階段，Server層寫Binlog，將狀態改成commit為提交第二階段。兩階段提交可以確保Binlog和Redo log數據一致性。

6.4 Redo log 容災恢復過程

MySQL的處理過程如下

判斷redo log是否完整，如果判斷是完整（commit）的，直接用Redo log恢復
如果redo log只是預提交prepare但不是commit狀態，這個時候就會去判斷binlog是否完整，如果完整就提交Redo log，用Redo log恢復，不完整就回滾事務，丟棄數據。

只有在redo log狀態為prepare時，才會去檢查binlog是否存在，否則只校驗redo log是否是 commit就可以啦。怎麼檢查binlog：一個完整事物binlog結尾有固定的格式。

6.5 Redo log 刷盤時機

Undo log的刷盤時機和Redo log差不多，但是對於Undo log我沒找到對應的刷盤參數設計，所以不再提。Redo log每次先寫入Redo Log Buffer中，然後通過刷盤時機控制刷入OS Buffer時間和刷入日記磁盤的時間。

圖3：內存和磁盤日記結構圖

在Undo Log中，MySQL是通過參數innodb_flush_log_at_trx_commit來控制刷盤時機，取值是0、1和2三種值。0表示事務提交後，每秒寫入OS Buffer並調用fsync()寫入日記磁盤中；1表示每次事務提交會寫入OS Buffer並調用fsync()將日記寫入日記磁盤中。2表示事務每次提交寫入到OS Buffer，每秒調用fsync()寫入日記磁盤。可見參數為1是最安全的，同時也是默認值。

圖4：Redo log刷盤時機參數對應操作圖

6.6 Redo log 存儲方式

圖5：Redo log File環形存儲結構圖

上圖是日記磁盤的Redo log環形設計圖（從頭寫，寫到結束又從頭開始寫~循環）。write pos和check point是兩個指針，write pos指針指向當前日記文件寫入的位置，check point指針指向當前要擦除的開始位置。圖中綠色部分是可以寫入Redo log地方，每次寫入，write pos指針會順時針推進，當然基本不會與check point指針重合，因為MySQL有這種機制去實現，每次觸發檢查點checkpoint，check point會指針向前推進，這個過程就是需要進行刷日記和數據磁盤，記錄相應的LSN，引出難點LSN。

6.7 Redo Log 檢查點

啥時候會觸發檢查點checkpoint，網上找了點資料：啥時候數據庫會觸發檢查點checkpoint

圖6：檢查點觸發時機

Checkpoint發生的時間、條件及髒頁

的選擇等都非常複雜。而Checkpoint所做的事情無外乎是將緩衝池中的髒頁刷回到磁盤，不同之處在於每次刷新多少頁到磁盤，每次從哪裡取髒頁，以及什麼時間觸發Checkpoint。這些本文不會去研究。

6.8 Redo Log LSN

LSN這個概念，比較複雜，我介紹完你們不一定懂！LSN稱為日誌的邏輯序列號(log sequence number)，在innodb存儲引擎中，lsn佔用8個字節。LSN的值會隨著日誌的寫入而逐漸增大。可以簡單理解SLN就是記錄從開始到現在已經產生了多少字節的Redo log值。

存儲方式兩個指針又是通過LSN計算得到指向位置，因為LSN記錄的是文件的大小字節，當超過文件大小時，需要用取模計算出這兩個指針位置，取模使得寫入就會從頭開始寫，這樣使得兩個指針在一個文件中，一直落在循環位置，你追我趕的過程。這就是Redo log 環形邏輯思想設計實現。

上面提到LSN比較複雜，是因為它有很多個值，輸入命令"show engine innodb status;"，可以看到四個的lsn記錄

圖7：LSN值列表

為了方便識別，我都為它們重新命名，如下。名詞記不住，後面無法繼續深入

內存日記：redo log buffer lsn；磁盤日記：redo log file lsn；

一般關係為：redo log buffer lsn >= redo log file lsn，如果刷盤時機為1，則redo log buffer lsn = redo log file lsn。

內存數據頁：data buffer lsn；數據磁盤數據頁：data disk lsn；

一般關係為data buffer lsn > data disk lsn，如果已經刷入數據磁盤，則data buffer lsn = data disk lsn。

檢查點：chckpoint lsn；

後面提到檢查點刷盤，數據刷盤和日記刷盤（如果有日記刷盤：則說明我假設的日記刷盤的時機設置值不為1，為1是同步的，即始終redo log buffer lsn = redo log file lsn，不會由檢查點觸發刷日記磁盤）。

都說Redo log是環形記錄，那麼怎麼記錄的？下面結合LSN給出記錄過程虛構圖，可以對比6.6 Redo log 存儲方式圖

相關知識：日記磁盤 + redo log file lsn + checkpoint lsn + 雙指針（write pos、check point）

1-8按時間順序發生。1點是假設最初的狀態；2、3點寫日記磁盤；4點是觸發了檢查點checkpoint，進行刷盤，checkpoint lsn=1開始，刷盤結束並更新checkpoint lsn=512。在5點、6點已經刷過了一循環內存、二循環內存，從頭開始寫入log，兩個指針指向回到了頭部。第7點也是一個觸發checkpoint的過程。9點是假設沒有更新，最後達到平衡的結果，即內存中數據頁和日記都完成了刷盤。

圖8：Redo Log File存儲過程

整個流程：

在某些情況下，觸發checkpoint，觸發數據頁和日誌頁刷盤，此時將內存中的髒數據---"數據髒頁"和"日誌髒數據" 分別刷到數據磁盤和日記磁盤中，而且兩者刷盤速度不一樣。checkpoint會保護機制，當數據刷盤速度超過日誌刷盤時，將會暫時停止數據刷盤，等待日誌刷盤進度超過數據刷盤。

刷盤時，對於數據磁盤，全部都是在內存中，此時每次刷一個數據頁到內存更新數據頁也更新了data disk lsn為data buffer lsn（在更新內存數據頁時，會更新data buffer lsn）。

對於日記磁盤，除了要記錄checkpoint lsn的值為檢查點 checkpoint的值（必須在結束時 直接記錄一個值，速度很快），這裡是針對日記刷盤時機不是1（1是同步緩存刷日記刷盤）時，並且日記還沒刷到日記磁盤需要觸發將緩存中日記提前刷到日記磁盤中，此時會將

redo buffer log刷到redo log file中也更新了redo log file lsn為redo log buffer lsn 。

模擬檢查點觸發前後，整個流程變化，一個數據頁和日記，數據變化及lsn從179-180的變化圖（刷盤時機不為1）

6.9 Redo log 容災恢復過程與LSN

結合6.4 Redo log 容災恢復過程和6.8的LSN知識，再次細化6.4的Redo log恢復過程

重啟innodb時，Redo log完不完整，採用6.4知識過程。用Redo log恢復，啟動數據庫時，InnoDB會掃描數據磁盤的數據頁data disk lsn和日誌磁盤中的checkpoint lsn。兩者相等則從checkpoint lsn點開始恢復，恢復過程是利用 redo log到buffer pool，直到checkpoint lsn等於redo log file lsn，則恢復完成。

如果checkpoint lsn 小於 data disk lsn，說明在檢查點觸發後還沒結束刷盤時數據庫宕機了。因為checkpoint lsn最新值是在數據刷盤結束後才記錄的，檢查點之後有一部分數據已經刷入數據磁盤，這個時候數據磁盤已經寫入部分的部分恢復將不會重做，直接跳到沒有恢復的lsn值開始恢復。

七、瞭解 ChangeBuffer

7.1 為啥提到ChangeBuffer

為啥本文我會提到ChangeBuffer呢，其實很多時候會將ChangeBuffer和Redo log搞混，兩者都是巧用內存，減少磁盤IO，為了不弄混我覺得有必要專門對這個進行一個講解。

7.2 ChangeBuffer概念及作用

下面是我對ChangeBuffer的簡單介紹

也就是說對於更新的操作，如果用到了ChangeBuffer，更新的數據所在的數據頁如果不在內存中，將不用去數據磁盤將數據頁讀到內存，而是將這一次操作記錄在ChangeBuffer中，ChangeBuffer 主要節省的則是隨機讀磁盤的 IO 消耗，下次讀取查詢等讀取數據頁時用上ChangeBuffer中的記錄即可。其實也是一種巧用內存的思想。

7.3 ChangeBuffer與Redo log區別

Redo log 主要節省的是隨機寫磁盤的 IO 消耗（轉成順序寫），而 ChangeBuffer 主要節省的則是隨機讀磁盤的 IO 消耗。

這句話怎麼理解，看下面：

Redo log 與 ChangeBuffer(含磁盤持久化) 這2個機制，不同之處在於優化了整個變更流程的不同階段。

先不考慮Redo log、ChangeBuffer機制，簡化抽象一個更新(insert、update、delete)流程：

從磁盤讀取待變更的行所在的數據頁，讀入內存頁中
對內存頁中的行，執行變更操作
將變更後的數據頁，寫入至數據磁盤中

其中，流程中的步驟1涉及隨機讀磁盤IO；步驟3涉及隨機寫磁盤IO；剛好對應ChangeBuffer和Redo log。

對那句話的理解答案：

ChangeBuffer機制，優化了步驟1——避免了隨機讀磁盤IO ，將不在內存中的數據頁的操作寫入ChangeBuffer中，而不是將數據頁從磁盤讀入內存頁中
Redo log機制，優化了步驟3——避免了隨機寫磁盤IO，將隨機寫磁盤，優化為了順序寫磁盤(寫Redo log，確保crash-safe)

7.4 有沒有用到ChangeBuffer對於Redo log的區別

Redo log機制，為了保證crash-safe，一直都會用到。有無用到ChangeBuffer機制，對於redo log這步的區別在於—— 用到了ChangeBuffer機制時，在Redo log中記錄的本次變更，是記錄new change buffer item相關的信息，而不是直接的記錄物理頁的變更(文章中第八節都有體現這一過程)。在我們mysql innodb中， ChangeBuffer機制不是一直會被應用到，僅當待操作的數據頁當前不在內存中，需要先讀磁盤加載數據頁時，ChangeBuffer才有用武之地。

7.5 ChangeBuffer的merge過程

除了訪問這個數據也會觸發 merge 外，系統有後臺線程會定期 merge。在數據庫正常關閉（shutdown）的過程中，也會執行 merge 操作。

merge過程做三步

從磁盤讀入數據頁到內存（老版本的數據頁）；
從 change buffer 裡找出這個數據頁的 change buffer 記錄 (可能有多個），依次應用，得到新版數據頁；
寫 redo log。這個 redo log 包含了數據的變更和 change buffer 的變更。

八、日記大連貫U-R-B，一舉攻破拿下

前面分別講的是Binlog、Undo log和Redo log，下面將他們都串聯起來，在一些流程體現全部日記。

同樣，以一些最經典的更新語句例子展開說明。

8.1 製造演示數據

測試語句：插入語句+查詢語句，a字段是普通索引

<code>insert into ta(a,b) values(2,5),(7, 5)2、select * from t where a in (2, 7)/<code>

假設原來的數據如下圖，數據頁page1在內存中，page2不在。插入的數據(2，5)落在page1，數據(7,5)落在page2中。

8.2 假設沒有日記和ChangeBuffer 示範

先不考慮所有日記及ChangeBuffer機制，簡化抽象一個更新insert流程

從磁盤讀取待變更的行所在的數據頁，讀入內存頁中

對內存頁中的行，執行變更操作
將變更後的數據頁，寫入至數據磁盤中

8.3 考慮所有日記和ChangeBuffer 示範--現有Innodb流程

過程是兩階段提交-----日記刷盤------數據刷盤（涉及Redo log lsn 和 ChangeBuffer的內容）

8.3.1 兩階段提交過程

數據(2,5)所在頁page1在內存中直接更新內存；數據(7,5)所在頁page2不在內存中，記錄change buffer（具有唯一性的索引或者沒有使用change buffer的操作是將磁盤中的數據頁讀入內存中並做更新）。
寫undo日記。先寫緩存，後面根據刷盤參數決定何時刷入磁盤，後面的redo/Binlog都一樣。日記刷盤在每一個日記中基本已經提到，它和設置的參數有關，具體刷盤可以參考上文4.3和6.5章節，下文不會再展開介紹。
寫redo日記（先記在內存中的更新，然後記錄在內存中的change buffer的改變）
日記狀態改成prepare階段。
寫Binlog日記。
提交事務，日記狀態改成commit階段。