MySQL 中的 DML 語句執行流程！

碼農老K

工具主管,科技領域創作者

redo log 和 bin log

在DML語句執行的過程中，主要會涉及到兩個日志——，而這兩個日志是數據庫 WAL (Write Ahead Logging，先寫日志再寫磁盤提高效率) 技術的兩大主角。下面我來介紹一下這兩個日志。

redo log(重做日志)

類型：數據頁級別的，記錄的是物理日志 (比如某個數據做了是什麼更改)。作用：確保事務的持久性，防止在數據庫 crash 的時候上有髒頁未寫入磁盤，在重啟 MySQL 的時候會根據 redo log 進行重做。產生時間：在事務開始的時候就會產生，而 redo log 的落盤不是在事務提交的時候，而是在事務執行過程中就會進行 redo log 的寫入。釋放時間：當內存中的髒頁都寫入磁盤了，那麼相應的 redo log 就會被覆蓋。

注意： 這裏為什麼說是覆蓋是因為 redo log 寫日志的特性。redo log 的大小是固定的，所以寫 redo log 是循環的覆蓋寫，你可以理解為，一個環形文件如下圖。

其中，整個環形是兩個文件構成的(文件個數和文件大小你可以自己指定)，兩個文件像連在一起一樣，其中綠色標識的是 check point ，用來表示當前日志被清理到的頭(可以理解為當前有用的 redo log的頭)，而 write position 代表著當前寫入位置(就是當前有用 redo log 的尾)。如果數據庫有更新那麼 write position 就會向前推，如果 write position 要追上 check point 的時候，那麼數據庫就會停下來將 check point 向前推(就是清理，此時就是將內存中的髒頁進行寫入磁盤，對應著上面的釋放時間)。

bin log

bin log 默認是關閉的，需要在配置文件自己設置。

類型：數據行級別的，邏輯日志 (有兩種形式，一種是 statement ，記錄著sql語句，另一種是 row ，記錄著數據行更新前和更新後的內容)。作用：主要用於實現 MySQL 主從複制，數據備份和數據恢複。產生時間：在一個事務提交的時候會被寫入磁盤。釋放時間：是追加寫的，所以不會被覆蓋，無釋放時間。

DML 的執行流程

如果你對 MySQL 的這兩個日志沒有了解過的話，上面的特性是很難理解的，如果結合著 DML 語句執行流程就會好理解一點，比如我現在要在數據庫的表中更新 id = 1 這一行中的 value 字段。

update table set value = value + 1 where id = 1;

這個時候更新的大致流程就是這樣的

首先 MySQL 的 server 層會通過調用執行器去獲取指定數據行苦差事當然交給引擎(這裏是innodb)來做，InnoDB 首先會去查看當前內存中是否存在該數據行，如果存在之間從內存中取出，如果不在那麼會從磁盤中 load 到內存之後再從內存中取出相應數據行。然後將數據行進行更新並將新行寫入內存中(注意此時肯定會產生髒頁，後面會了解到)。之後就會開始寫日志，首先是 redo log的寫入(*此時進入prepare狀態**)。第二個寫 bin log。最後進行事務的提交。

注意：這裏的事務提交不僅僅是簡單的 commit; ，因為這裏只是簡單的 update 語句，自己本身就是一個事務，所以這裏的 commit; 是隱式的。而這裏所說的 commit; 還包含了 redo log 的狀態轉換——從 prepare 到 commit 狀態，這是一個很重要的點，後面我會詳細解釋，你這裏需要記住有這麼一個東西。

到這裏我們來簡單總結一下：

DML語句的執行和兩個日志——redo log、bin log有著很大的關系，因為需要提高數據庫的性能，MySQL 采用了一種 WAL(先寫日志再寫磁盤) 技術，其中就使用到了這兩個日志。主要的流程如下，MySQL會從內存中獲取相應的數據行(如果沒有先從磁盤 load 到內存中)，然後將數據行進行更新並將新行寫入內存後進行redo log的寫入和 bin log 的寫入，在一開始 redo log 是處於 prepare 狀態，只有在 bin log 寫完然後進行事務提交的時候才會處於 commit 狀態。

不僅僅是那麼簡單

這個時候你肯定有幾個疑問。

redo log是如何保證事務的持久性的？(即當事務執行期間發生 crash ，redo log是如何保證 crash-safe 能力)

bin log是如何完成數據恢複和主從複制的？

上面redo log的 prepare 和 commit 兩個狀態的存在意義是什麼？

為什麼要存在兩個日志，只要一個不行嗎？

為什麼 WAL 技術能提高數據庫性能？

下面我來慢慢回答這些問題。

bin log是如何完成數據恢複和主從複制的

首先最簡單的是第二個，，看了上面的介紹大家應該也知道了，bin log有這麼幾個特性。

追加寫，不會像 redo log 那樣被覆蓋。記錄了完整的邏輯日志，可以利用它進行快速的數據恢複。

所以，當我們要進行數據恢複的時候可以 使用 bin log 為基礎備份出一個和原庫一樣的備庫。當我們要進行主從複制的時候，可以使用 bin log 進行 主從庫的同步。

redo log是如何保證事務的持久性的

提醒一下，我這裏使用的是 “雙一配置”(即innodb_flush_log_at_trx_commit = 1 和 sync_binlog = 1 )。sync_binlog = 1的意思是 在事務每次提交的時候都會進行 bin log的持久化。而 innodb_flush_log_at_trx_commit = 1 的意思是事務提交的時候都將 redo log 持久化到磁盤。

所以這裏的雙一就是在每次事務提交的時候都會進行 redo log 和 bin log 的持久化，這兩個參數的其他配置可以去參考其他文章，這裏不做過多涉及。

這就不得不提到兩階段提交了，這時候還會牽扯到上面的另一個問題redo log的 prepare 和 commit 兩個狀態的存在意義是什麼？。

兩階段提交是為了在數據庫發生 crash 之後重啟恢複能夠保證事務完整性。比如這個時候我們正在進行上面的 update 語句，然後此時數據庫宕掉了。為了你好理解我在將上面的流程圖拿過來。

你會發現，我這裏標注了三個時刻，就是我們宕機事務可能會執行到的時刻。

首先我先將規則寫在前面，你們可以對照著去理解。

如果redo log裏面的事務是完整的，也就是已經有了commit標識，則直接提交。如果redo log裏面的事務只有完整的prepare，則判斷對應的事務binlog是否存在並完整，如果存在並完整則繼續提交事務，如果不是那麼回滾事務。

我們來看一下上面幾個時刻。

時刻A：顯而易見，此時日志都沒寫，東西都在內存中，重啟肯定會回滾(就當什麼事都沒發生)。時刻B：此時redo log 已經寫盤，但是只是處於 prepare 狀態，如果這時候發生 crash ，那麼 bin log還沒寫 and redo log 還處於 prepare 狀態，此時事務會回滾。時刻C：此時 bin log 已經寫盤，redo log 已寫盤並處於 prepare 狀態，此時事務會根據 redo log 和 bin log 繼續提交。

為什麼會使用兩階段提交呢？

我們可以用反證法。

如果redo log在前 bin log在後，在redo log寫完之後宕機，那麼重啟之後主庫可以根據 redo log 進行數據恢複，但是這時候因為 bin log 是沒有寫的，所以如果使用 bin log 進行備份，那麼備庫會少了這一個事務。

如果bin log在前 redo log在後，在bin log寫完之後宕機，那麼就會導致後面使用 bin log做備份的時候多出這個事務。

所以如果不使用 兩階段提交 ，那麼就會出現 bin log備份出來的數據庫和原庫的數據不一致。

所以redo log 結合著上面的兩階段提交就解決了，crash-safe 能力和 原庫備庫一致性。

為什麼要存在兩個日志，只要一個不行嗎

你可能會想，如果不引入兩個日志就沒有必要進行 兩階段提交 了，這樣豈不是快哉？！

我們可以繼續利用反證法去證明。

如果我們只有 redo log，你知道 redo log 大小是固定且是可以被覆蓋的，所以如果用來做數據備份是不可以的，因為它僅僅會記錄當前內存中數據頁的情況。而且 redo log是 innodb 層面的，它不是 數據庫層面的，如果當你使用的另外一個數據庫不是 以 innodb 作為存儲引擎的話，是根本進行不了同步的。

如果我們只有 bin log，我們知道bin log是數據行級別且記錄的是邏輯日志，所以是沒有“數據頁恢複”的功能的。

所以，這裏我們還是需要使用 redo log 和 bin log。

redo log的 prepare 和 commit 兩個狀態的存在意義是什麼

這裏我們還得引出一個點，我們上面提到了 redo log 的落盤是在事務執行過程中。那麼，redo log究竟具體在什麼時候會進行日志的持久化呢？

具體有三種

redo log buffer占用的空間要達到 innodb_log_buffer_size一半的時候，會有後台線程主動將日志寫盤。注意，由於這個事務並沒有提交，所以這個寫盤動作只是write，而沒有調用fsync，也就是只留在了文件系統的page cache。後台線程會做每秒的輪詢將 redo log buffer write到文件系統的page cache並調用 fsync 進行寫盤。並行的事務提交的時候，順帶將這個事務的redo log buffer持久化到磁盤。

這裏我們提到了兩個很關鍵的詞：redo log buffer 和 page cache。

那麼這個redo log buffer 和 page cache 又是幹什麼的呢？這裏我們需要將一下 redo log 的三種形態。如下圖

你可以想一下，一個事務會有多個 DML 語句，而每次 DML 語句都進行寫盤會進行大量的系統調用導致資源浪費和時間浪費，所以每次 DML 語句的時候只是會將 日志先緩存到內存中的 redo log buffer 中去，而最終調用 commit 的時候會將 redo log buffer 中的內容寫入磁盤。

而 page cache 的存在是為了加快 fsync 系統調用的速度，我們知道每次事務 commit 的時候都會進行兩次 fsync 調用(雙一配置)，而主要的 redo log 一般會提早進行 write 到文件系統緩沖中，所以這樣會加快寫盤速度。

在這裏，我放了一張加入“緩存”的DML更新流程的圖。

其中 bin log cache你也可以理解為緩存，而且因為bin log是邏輯日志，所以一個事務的bin log是不能被拆開的，所以我們的 bin log cache 是存放在每個線程的空間裏的，相互獨立。如下圖

注意：redo log在最後只是 write 進行了寫入文件系統的 page cache 中是因為這個時候已經可以保證 crash-safe能力了，就不需要再額外進行寫盤操作了，如果不理解可以結合上面的兩階段提交規則去理解。

所以這裏 redo log 的兩種狀態其實也是兩階段提交的重要組成部分，我們可以知道，在bin log未寫盤之前 redo log會先進行寫盤，但是這次寫盤的狀態還只是 prepare 狀態，只有在bin log 寫完之後 才會最終將狀態變為 commit，並且這裏不再進行寫盤操作，而是通過後面進行寫盤的時候順便寫入。

為什麼 WAL 技術能提高數據庫性能

我們這個時候可能還會有一個疑惑，在“雙一配置”下，每次事務的提交都需要進行兩次 fsync 系統調用，這樣對於數據庫的壓力會是很大的。

我們知道 WAL 技術能提高數據庫性能的一個原因是——日志文件是順序寫的，磁盤的順序寫要比隨機寫快很多。但是對於每次事務進行兩次系統調用這點，WAL 有沒有做什麼優化呢？

答案是有的，試想一下，如果存在多個事務並發的情況下，此時會出現多個事務的 redo log buffer都已經寫好，這時候 InnoDB 會使用 LSN(log sequence number)日志邏輯序列號，LSN 是單調遞增的，用來對應 redo log的寫入點，每次寫入 length 長度的 redo log，LSN 就會增加 length。

比如此時有，三個並發事務trx1，trx2，trx3。我們可以看到 trx1 是第一個到達的，而 trx1 要進行寫盤的時候已經有三個事務在隊列中了，所以此時 trx1 去寫盤的時候帶的 LSN 就會變成 200，那麼此時進行寫盤，就會將trx1，trx2，trx3都寫入磁盤中了，這裏僅進行了一次系統調用。

所以這裏 WAL 技術會對一些需要系統調用寫盤的地方進行一些優化，盡量減少IO。對於這個問題就可以總結為兩點：

通過日志的順序寫提高磁盤效率通過組提交減少系統調用

總結

這裏我們主要介紹了在 MySQL 中 一條 DML 語句是如何執行的，redo log 、bin log又是如何和 DML 語句、事務聯系在一起的。其中還介紹了 redo log的三種形態和兩種提交狀態，bin log的線程cache，LSN組提交實現等。

總的來說就是 MySQL 在進行 DML 語句的時候會先寫日志緩存(為了事務多個 DML 語句而不多次進行寫盤操作)，等到事務提交的時候會進行日志的真正落盤(“雙一配置”)，其中還使用了兩階段提交加上redo log的兩種提交狀態來實現 crash-safe能力 和 redo log，bin log 的同步。