一條更新SQL的內部執行及日誌模組

語言: CN / TW / HK
時間 2022-05-21 17:43:00

一條更新SQL的內部執行

學習MySQL實戰45講，非常推薦學

還是老圖：

上文複習

在執行查詢語句的時候，會執行聯結器(總要連上才能搞事情)，然後去查詢快取(MySQL8+刪除了)，有資料返回，沒資料 進行分析器-優化器-執行器-執行引擎 流程並且其特點是 如果該表上有更新，都會把快取結果清空 ；

MySQL整體來看可以分為兩塊：一塊是Server層，它主要做的是MySQL功能層面的事情；還有一塊是引擎層，負責儲存相關的具體事宜；

update T set c=c+1 where ID=2;

流程：

  1. 聯結器：連線資料庫
  2. 分析器：分析詞法和語法解析知道這是更新語句
  3. 優化器：決定使用ID進行索引
  4. 執行器：具體執行，找到改行然後更新

更新對查詢來說，涉及到了兩個重要的 日誌模組 , redo log（重做日誌）InnoDB特有的，binlog（歸檔日誌）Server中存在的 ，只要持續學習資料庫方面，這兩個是繞不過的。

redo log（重做日誌）

在InnoDB中redo log的大小是固定的，可以配置為一組4個檔案，每個檔案的大小是1GB，那麼這塊redo log總共就可以記錄4GB的操作(可以自己設定)。

MySQL中的問題：

如果每一次的更新操作都需要寫進磁碟，然後磁碟也要找到對應的那條記錄，然後再更新，整個過程IO成本、查詢成本都很高

解決問題：

WAL技術： Write-Ahead Logging

  1. 寫日誌
  2. 寫磁碟

執行流程：

為什麼redo log具有crash-safe的能力

為什麼 redo log 具有 crash-safe 的能力，是 binlog 無法替代的？

redo log的被動刷盤機制

明確定義：

write：刷盤

fsync：持久化到磁碟

write(刷盤)指的是MySQL從buffer pool中將內容寫到系統的page cache中，並沒有持久化到系統磁碟上。這個速度其實是很快的。

fsync指的是從系統的cache中將資料持久化到系統磁碟上。這個速度可以認為比較慢，而且也是IOPS升高的真正原因。

redo log是物理日誌，記錄的是“在某個資料頁上做了什麼修改”，內部結構是基於頁的，記錄了這個頁的欄位值變化，只要crash後讀取redo log進行重放就可以恢復資料。（因為redo log是迴圈寫的，如果滿了InnoDB就會執行 真正寫盤 ）。

好處是不用每一次操作都實時把資料寫盤，就算crash後也可以通過redo log重放恢復，所以能夠實現快速響應SQL語句。

binlog（歸檔日誌）

MySQL剛開始是使用的自帶引擎MyISAM,但其沒有crash-safe,binlog日誌只能用於歸檔，5.5版本之後引入InnoDB,通過redo log來實現crash-safe能力。

redo log與binlog的區別

  1. redo log 是 InnoDB 引擎特有的；binlog 是 MySQL 的 Server 層實現的，所有引擎都可以使用。
  2. redo log 是 物理日誌 ， 記錄的是在某個資料頁上做了什麼修改 ；binlog 是 邏輯日誌 ， 記錄的是這個語句的原始邏輯 ，比如“給 ID=2 這一行的 c 欄位加 1 ”。
  3. redo log 是 迴圈寫的 ，空間固定會用完；binlog 是 可以追加寫入的 。“追加寫”是指 binlog 檔案寫到一定大小後會切換到下一個，並不會覆蓋以前的日誌

update內部執行流程：

流程圖：圖中 淺色框 表示是在 InnoDB 內部執行的， 深色框 表示是在執行器中執行的

其中redo log分為兩個階段提交：這是為了讓兩份日誌之間的邏輯一致

  1. redo log prepare階段
  2. commit階段

好處：保證redo log和binlog都能同時失敗或者成功。

論證兩階段提交的必要性：

如果不採用兩階段提交，那麼redo log和binlog是單獨的邏輯，由此引出下面兩種提交方式：

  1. 先寫redo log,後寫binlog
  2. 先寫binlog,後寫redo log

還是以update為例：

update T set c=c+1 where ID=2;

先寫redo log,後寫binlog

在redo log寫完，binlog沒寫完的時候，MySQL 程序異常重啟，因為其實先寫入記憶體的， 所以MySQL崩潰後仍然能把資料恢復

根據備份恢復後c的值為1.但由於binlog沒有寫完就崩潰了，這時候binlog就沒有記錄這條語句的操作，使用binlog恢復的時候就會少一次更新，c的值為0，這就與原庫的值不同了。

先寫binlog,後寫redo log與上面邏輯相同都會造成恢復的資料與原庫值不同；

可以看到，如果不使用“兩階段提交”，那麼資料庫的狀態就有可能和用它的日誌恢復出來的庫的狀態不一致

但是當按照上述流程圖來說，在進行兩階段提交的時候，即A、B時刻出現crash,是怎麼處理的。

流程如下：

B時刻發生crash後對應的是2(a)的情況，崩潰恢復過程中事務會被提交。

由此引出的很多問題，感興趣的朋友可以看下 MySQL實戰45講答疑篇第一章

備份的流程：

  1. 找到最近的一次全量備份，從這個備份恢復到臨時庫；
  2. 從備份的時間點開始，將備份的 binlog 依次取出來，重放到中午誤刪表之前的那個時刻。

備份恢復的資料的場景：

  1. 誤操作後需要恢復資料
  2. 要擴容的時候，也需要再多搭建一些備庫來增加系統的讀能力的時候，用全量備份加上應用binlog 來實現的。如果這個“不一致”就會導致你的線上出現 主從資料庫不一致 的情況
「」
「」