MySQL系列之一條更新SQL的生命歷程

聽說MySQL能恢復到半個月內任意一秒的狀態

要從一條更新語句說起，如果將ID=2這一行的值+1，SQL語句可以這樣寫：

mysql> update T set c=c+1 where ID=2;
複製程式碼

執行一條更新語句同樣會走一遍查詢語句的流程:

• 連線資料庫
• 清空該表涉及到的快取
• 分析器通過詞法和語法解析出這是一條更新語句，並確定涉及到表與欄位
• 優化器決定使用"ID"這個索引
• 執行器找到這一行資料，然後進行更新操作
  與查詢過程不同的是，更新涉及到兩個日誌模組：redo log（重做日誌）和bin log（歸檔日誌）

臨時記錄：redo log

酒店掌櫃有一個賬本和一個小黑板，來做賒賬的記錄。有以下兩種方案：

1. 每一筆賬都開啟賬本做記錄，當有人還賬時，找到對應的賒賬記錄，修改記錄的狀態
2. 先在黑板上記錄本次要做的操作，打烊後按照黑板上的記錄向賬本上進行核算

當生意紅火，顧客絡繹不絕時，第一種方案效率實在是低下，掌櫃的一定按照第二種方案來記賬。
同樣的，MySQL如果每次更新操作都要寫入磁碟，在磁碟中找到對應記錄，然後更新，這個過程的IO成本、查詢成本都太高了。
為了解決和這個問題，MySQL就使用了類似於黑板-賬本模式來提高效率。這一模式即為WAL技術，全程為Write-Ahead Logging，關鍵點:先寫日誌，再寫磁碟，也就是前文中先寫黑板，再寫賬本。

具體步驟如下：
當有記錄需要更新，innoDB先把記錄寫入redo log中，並更新記憶體，這是更新操作就算結束了。innoDB引擎會在適當的時候講操作記錄更新到磁碟裡，這一動作一般是系統比較閒的時候做的。
redo log的大小是固定的，共有4個檔案組成，每個大小為1G。邏輯上可以將4個檔案理解為環形，從頭開始寫，寫到末尾又重新開始新的一輪,如下圖所示

write pos為當前記錄位置，check point為當前擦除點的位置，當記錄更新時，check point會隨著檔案的記錄向後移動。擦除後未寫入的位置可以記錄新的操作。當write pos追上了check point，則需要停下來寫入動作，將redo log內容寫入磁碟,然後清除check point向後移動。
有了redo log，innoDB可以知曉每一次操作，保證當資料庫發生異常重啟時，之前的能夠根據redo log恢復之前的記錄，這種能力叫做"crash-safe"。

歸檔日誌：bin log

redo log與bin log日誌的區別：

1. redo log 是屬於innoDB引擎所有，bin log是server提供的，所有引擎都可以使用
2. redo log 屬於物理日誌，記錄"在某個資料頁做了什麼修改"，bin log記錄的是該語句邏輯日誌"將ID=2的這一行c的值+1" ^[1]
3. redo log日誌檔案是迴圈使用的，空間有使用完的時刻，bin log是追加記錄的，不會覆蓋之前的記錄

也就是說，server搭配其他引擎是沒有redo log的，因此也就沒有了crash-safe能力

更新具體流程

基於對兩個日誌檔案的瞭解，再次深入瞭解更新的流程

1. 執行器先通過引擎使用樹搜尋找到ID=2這一行，如果該記錄所在的資料頁本身就在記憶體中，則直接返回執行器，否則先從磁碟讀入記憶體，然後返回
2. 執行器拿到資料後將c的值加一，然後通過引擎的寫入介面將修改後的資料寫入
3. 引擎j將新資料更新到記憶體中，然後在redo log中記錄此次修改，這時redo log中該記錄的狀態置為prepare，並告知執行器已經更新完成，隨時可以提交事務
4. 執行器生成此次操作的bin log，將bin log寫入磁碟中
5. 執行器呼叫引擎的提交事務介面，引擎將剛剛寫入的redo log置為commit狀態，更新結束

下圖是《MySQL實戰》提供的流程圖：

淺色代表在innoDB中執行，深色在server中執行

兩階段提交

從上圖可以看出，redo log是分兩個階段來提交的,這是為了保持兩個日誌邏輯上一致 如果不用兩階段提交會發生什麼呢 利用反證法來看下：
假設初始ID=2的資料行，c的值為0，現在要執行c+1的操作。

1. 先記錄redo log 後記錄bin log 如果剛記錄完redo log，還沒有記錄bin log時，c的值已經記錄變為1，這時MySQL服務崩潰重啟，根據crash-safe機制，可以用redo log來恢復資料庫，恢復後的資料中c的值為1。由於bin log中沒有記錄這一變化，以後備份bin log時，c的值還是0。如果有一天需要從bin log回覆一臺備用資料庫，由於bin log少了一次更新，則最後恢復出來的c值仍然為0，與原庫中值不符合
2. 先記錄bin log 後記錄redo log 寫完bin log就發生crash，還沒來得及寫入redo log，崩潰恢復後這個事務是無效的，因此c的值還是0，但是bin log中已經記錄了"將c的值+1"的日誌，所以用bin log恢復出來的資料多出來一個事務，使得c的值為1，與原庫中資料不符。
3. 兩階段提交 記錄過bin log回過頭提交commit（可參見評論區知識點） 更新redo log後，還沒有記錄bin log時崩潰，這時redo log的狀態還是prepare，事務並沒有提交，而且bin log中沒有記錄，因此由於crash-safe機制，並不會恢復該記錄，c的值仍然為0，由於bin log中沒有記錄，以後從bin log恢復資料時，c的值在此操作中並沒有記錄變化，因此還是0，與原庫中資料一致；另一種情況：更新redo log，也更新了bin log，下一步執行器呼叫commit介面前崩潰，這時雖然redo log中狀態為prepare，但是從bin log中查到有記錄，所以還是會從redo log中恢復c=1，後面直接從bin log恢復出新的資料庫時，因為已經記錄c的值+1，所以與原庫中的值相同

總結如下：

兩種方式確定記錄完整：

1. redo log狀態為 commit
2. redo log狀態為prepare並且bin log記錄完整 （提交commit之前）

總結

這節主要學習了兩個日誌檔案的用法 redo log 用於保證 crash-safe 能力，bin log用於恢復資料的完整性

• innodb_flush_log_at_trx_commit 這個引數設定成 1 的時候，表示每次事務的 redo log 都直接持久化到磁碟。這樣可以保證 MySQL 異常重啟之後資料不丟失。
• sync_binlog 這個引數設定成 1 的時候，表示每次事務的 binlog 都持久化到磁碟。這樣可以保證 MySQL 異常重啟之後 binlog 不丟失。

---

評論區知識點：

• binlog沒有被用來做崩潰恢復,binlog是可以關的，你如果有許可權，可以"set sql_log_bin=0"關掉本執行緒的binlog日誌。 所以只依賴binlog來恢復就靠不住的

• @高枕 同學的評論簡單精煉的表達了兩階段提交機制下的工作狀態：
  記錄日誌共有三個過程：

  1. prepare階段
  2. 寫binlog
  3. commit
  • 當在2之前崩潰時
    重啟恢復：後發現沒有commit，回滾。備份恢復：沒有binlog。備份與原庫一致
  • 當在3之前崩潰時
    重啟恢復：雖沒有commit，但滿足prepare和binlog完整，所以重啟後會自動commit。備份：有binlog. 備份與原庫一致

• 來自@黃金的太陽

  • 問：
  1. redo log本身也是檔案，記錄檔案的過程其實也是寫磁碟，那和文中提到的離線寫磁碟操作有何區別？
  2. 響應一次SQL我理解是要同時操作兩個日誌檔案？也就是寫磁碟兩次？
  • 作者回復：

  1. 寫redo log是順序寫，不用去“找位置”，而更新資料需要找位置，因此redo log寫的速度更快

  2. 其實是3次（redolog兩次 binlog 1次）。不過在併發更新的時候會合並寫

本文中含有極客時間《MySQL實戰》的圖和部分原文，如有侵權，請聯絡我會立刻刪除
第一節：ACID之I：事務隔離

---

1. redo log不是記錄資料頁“更新之後的狀態”，而是記錄這個頁 “做了什麼改動”。
   bin log有兩種模式，statement 格式的話是記sql語句; row格式會記錄行的內容，記兩條，更新前和更新後都有。 ↩︎