前言

上一篇文章講了 《一條查詢語句是如何執行的》，應該很多人都注意到我在前綴 MySQL 相關後面加了個（一）

為啥講的是更新而不是刪除和插入呢？

Because，更新的複雜度要比插入和刪除要高，如果已經理解了更新的一整套流程，插入和刪除的流程對於你來說也是信手拈來（好像不太恰當，暫時想不出更好的詞就士但啦），所以這裡就只討論更新的，插入和刪除的流程大同小異，不再做討論。

使用哪個數據庫存儲引擎？

目前市面上流行的還是 MySQL5.7 ，而且大部分系統用的是分佈式微服務的架構，考慮併發事務的執行，這裡選擇講解 innodb 引擎中的語句更新流程。

從哪些方面講？

更新的流程相對比較複雜，涉及到數據庫 innodb 的事務，所以這裡會先從數據庫的磁盤結構+內存結構對其內在機理進行剖析，之後通過講解 redo log + undo log ，數據庫端的事務日誌以及 binlog，服務層面的日誌，還有數據庫的二階段提交保證事務的 ACID（暫不展開），來讓大家對整個流程有一個比較全面的瞭解。

innodb 的磁盤和內存結構

按照我的思路啊，我們先來看官網提供的這張圖：

從圖中我們可以看到：

• 左邊是 innodb 的內存結構，其中包含自適應的 hash 索引（adaptive hash index），Buffer Pool，Change Buffer，最下面是 Log Buffer；

• 在內存數據刷到磁盤中間有操作系統的緩存；

• 右邊是 innodb 的磁盤結構，包含系統表空間，獨佔表空間，共享表空間，臨時表空間，redo log 和 undo log，用虛線表示的是邏輯存在而非物理存在；

接下來我會挑重點來介紹這些組件。

Buffer Pool

這個是 innodb 的緩衝池空間，保存的是數據頁（data page）和索引頁（index page），在修改數據的時候，數據不會直接寫入到磁盤中，而會先寫入 Buffer Pool（如果頁數據在的話則修改 Buffer Pool），再由內存空間刷入到磁盤空間。

我們可以通過如下命令查看關於 innodb 的 Buffer Pool 的參數，有想詳細瞭解的小夥伴請移步 MySQL 官網：

show variables like '%innodb_buffer_pool%';

show status like '%innodb_buffer_pool%';

有幾個點我們需要明確一下：

1. 緩衝池(buffer pool)是一種常見的降低磁盤訪問的機制；

使用 Buffer Pool，可以避免每次查詢數據都跟磁盤進行 IO，磁盤讀寫，並不是按需讀取，而是按頁讀取，一次至少讀一頁數據（1M / 64page=16K），如果未來要讀取的數據就在頁中，就能夠省去後續的磁盤IO，提高效率，所以說Buffer Pool 是一種減少磁盤 IO 的機制。

2. 緩衝池以頁(page)為單位緩存數據；

3.緩衝池使用的內存淘汰算法是LRU，memcache，OS，InnoDB都使用了這種算法；

InnoDB對普通LRU(鏈表實現)進行了優化：將緩衝池分為老生代和新生代，入緩衝池的頁，優先進入老生代，頁被訪問，才進入新生代，以解決預讀失效的問題頁被訪問，且在老生代停留時間超過配置閾值的，才進入新生代，以解決批量數據訪問，大量熱數據淘汰的問題

這裡再明確幾個概念：

• 髒頁：當緩衝池數據和磁盤不一致的時候；
• 刷髒：把髒頁的數據寫入磁盤中的時候叫刷髒；
• 磁盤和緩衝區都是以頁為單位，而關於頁的大小：

Change Buffer

• 更新數據頁時，數據頁已經加載到緩衝池中的時候，可以直接更新；
• 那如果更新數據頁時，如果數據頁存在於磁盤，則需要將數據頁先讀取到內存，再修改內存，不在內存區域的話至少得發生一次 IO，這樣不就資源浪費了？

這個時候 change buffer 就出現了，如果要更新的數據不是唯一索引，並且沒有數據重複的情況，則不需要確保數據是否會重複。我們將要修改的記錄寫入到 change buffer 中，再通過 change buffer 一次性同步到磁盤中 ，減少 IO，5.5 之後叫 change buffer，以前叫 insert buffer （只支持 insert），這樣可以減少 IO 次數，提升修改效率。

• 最後把 Change Buffer 記錄到數據頁的操作叫做 merge。什麼時候發生 merge？ 有幾種情況：在訪問這個數據頁的時候，或者通過後臺線程、或者數據庫 shut down、 redo log 寫滿時觸發。

我們也可以用下面的命令看看 change_buffer 的參數：

show variables like '%change_buffer%';

注意：max_size 不是指 change buffer 的大小，而是指 change buffer 佔整個 buffer pool 的比例，默認為 25%。如果數據庫大部分索引都是非唯一索引，並且業務是寫多讀少，這種情況下可以適當調大 change buffer的比例 。

Log Buffer + Redo Log

看到這裡，相信很多人還是會有很多問號：

• 既然是用內存空間來做緩衝區，那如果數據庫服務器宕機了，那未同步完的數據不都完了？

其實很多小夥伴應該之前有接觸過或者聽說過 innodb 是支持事務的，而它支持事務的方式是 redo log + undo log 作為事務日誌，但是對 redo log 可能沒有進行一個比較深入的瞭解或者說並不知道它實際上是以一個什麼樣的角色存在。

好了不賣關子了，瞭解過 redis 的小夥伴應該都知道 redis 也是基於內存的，它做異常恢復的方式是通過持久化到磁盤的方式，那我們MySQL 的 innodb 引擎也是通過類似的方式，在剛才官網的結構圖中我們看到磁盤空間中有 redo log，而 redo log 就是通過buffer 區的另外一塊區域 log buffer （專門用來保存寫入日誌文件的數據，默認 16M，可以節省磁盤 IO）進行同步的，這個能力叫crash safe。redo log 在 innodb 中保證了事務的持久性。

redo log 不是記錄數據頁更新之後的狀態，而是記錄這個頁做了什麼改動，屬於物理日誌。

這種日誌和磁盤配合的整個過程，其實就是 MySQL 裡的 WAL 技 術（Write-Ahead Logging），它的關鍵點就是先寫日誌，再寫磁盤。

• 那已經有Buffer pool 作為緩存了，為啥還要多此一舉再多一個 log buffer呢？

我想問一下大家 kafka 快的原因有哪些知道不？其中有一點就是多這個 log buffer 的原因。沒錯，這位同學答對了！就是順序 IO 和隨機 IO 的區別，隨機 IO 是刷盤的操作，相對而言順序 IO 效率就高多了，因此先把修改寫入日誌，可以延遲刷盤時機，進而提升系統吞吐。當然，buffer pool 還是作為刷入磁盤的主角。

• 那 log buffer 什麼時候才會觸發刷數據到 redo log 的操作呢？

我們來看下 log buffer 的參數，大小默認是 16M：

show variables like '%log_buffer%';

redo log 既然是存在於磁盤空間，那它是可以找到對應的文件的，在 mysql 目錄中對應的文件名如下，默認是兩個大小恆為 48M 的 ib_logfile 文件：

既然大小是恆定的，那它肯定是會有刪除數據的操作的，

write pos 是當前記錄的位置，一邊寫一邊後移，寫到第 3 號文件末尾後就回到 0 號文件開頭。checkpoint 是當前要擦除的位置，也是往後推移並且循環的，擦除記錄前要把記錄更新到數據文件。

write pos 和 checkpoint 之間的是還空著的部分，可以用來記錄新的操作。如果 write pos 跟 checkpoint 重合了，表示已經沒有空間，這時候不能再執行新的更新，得停下來先刪除一些記錄，把 checkpoint 往前移一下。

那到底什麼時候刷盤呢？

我的回答是跟事務相關，先看這條命令：

show variables like '%log_at_trx_commit%';

默認值是 1，

默認是提交事務就寫入 log buffer，值為 0 時事務提交後不會刷盤，值為 2 時由操作系統提交。

值含義

• 0（延遲寫）log buffer 將每秒一次地寫入 log file 中，並且 log file 的 flush 操作同時進行。 該模式下，在事務提交的時候，不會主動觸發寫入磁盤的操作。
• 1（默認，實時 寫，實時刷）每次事務提交時 MySQL 都會把 log buffer 的數據寫入 log file，並且刷到磁盤 中去。
• 2（實時寫，延 遲刷）每次事務提交時 MySQL 都會把 log buffer 的數據寫入 log file。但是 flush 操 作並不會同時進行。該模式下，MySQL 會每秒執行一次 flush 操作。

• 標題上沒見到 undo log，為什麼不說說 undo log？

redo log 和 undo log 是一對，它們組合到一起就是 innodb 的事務日誌，但是 undo log 是關於事務提交回滾的日誌，這次主要討論流程，這裡就簡單提一下。

undo log（撤銷日誌或回滾日誌）記錄了事務發生之前的數據狀態（不包括 select）。 如果修改數據時出現異常，可以用 undo log 來實現回滾操作（保持原子性）。

在執行 undo 的時候，僅僅是將數據從邏輯上恢復至事務之前的狀態，而不是從物 理頁面上操作實現的，屬於邏輯格式的日誌。

undo Log 的數據默認在系統表空間 ibdata1 文件中，因為共享表空間不會自動收 縮，也可以單獨創建一個 undo 表空間。（結構圖可以看出）

binlog

用過 binlog 的夥伴應該知道binlog 有這幾個功能：

• 記錄DDL 和 DML 的邏輯日誌
• 主從複製 （slave 拿到 master 的 binlog 再執行）
• 數據恢復

MySQL 在 server 層面引入了 binlog, 它可以記錄所有引擎中的修改操作，因而可以對所有的引擎使用複製功能，然而這種情況會導致redo log與binlog的一致性問題。在 MySQL 中是通過內部 XA 機制解決這種一致性的問題。

我們先來看看 binlog 怎麼配置，在 my.cnf 中配置 binlog：

<code>vi /etc/my.cnf
log-bin=mysql-bin #添加這一行就ok
binlog-format=ROW #選擇row模式
server_id=1 #配置mysql replaction需要定義，不能和canal的slaveId重複/<code>

業內目前推薦使用的是 row 模式，準確性高，雖然說文件大，但是現在有 SSD 和萬兆光纖網絡，這些磁盤 IO 和網絡 IO 都是可以接受的。

在 innodb 裡其實又可以分為兩部分，一部分在緩存中，一部分在磁盤上。這裡業內有一個詞叫做刷盤，就是指將緩存中的日誌刷到磁盤上。跟刷盤有關的參數有兩個：

sync_binlog 和binlog_cache_size。

這兩個參數作用如下：

<code>binlog_cache_size: 二進制日誌緩存部分的大小，默認值32k
sync_binlog=[N]: 表示每多少個事務寫入緩衝，刷一次盤,默認值為0/<code>

要注意兩點：

• binlog_cache_size設過大，會造成內存浪費。binlog_cache_size設置過小，會頻繁將緩衝日誌寫入臨時文件。
• sync_binlog=0:表示刷新binlog時間點由操作系統自身來決定，操作系統自身會每隔一段時間就會刷新緩存數據到磁盤，這個性能最好。sync_binlog=1，代表每次事務提交時就會刷新binlog到磁盤，對性能有一定的影響。sync_binlog=N,代表每N個事務提交會進行一次binlog刷新。

• 另外，這裡存在一個一致性問題，sync_binlog=N，數據庫在操作系統宕機的時候，可能數據並沒有同步到磁盤，於是再次重啟數據庫，會帶來數據丟失問題。

MySQL 的 binlog 是多文件存儲，定位一個 LogEvent 需要通過 binlog filename + binlog position，進行定位。

二階段提交

二階段提交時指當一個事務跨越多個節點時，為了保證事務的 ACID特性，需要引入一個作為協調者的組件來統一掌控所有節點(稱作參與者)的操作結果並最終指示這些節點是否要把操作結果進行真正的提交(比如將更新後的數據寫入磁盤等等)。

因此，二階段提交的算法思路可以概括為：參與者將操作成敗通知協調者，再由協調者根據所有參與者的反饋情報決定各參與者是否要提交操作還是中止操作。

第一階段：

• InnoDB prepare， write/sync redo log；
• binlog不作任何操作；

第二階段：包含兩步:

• write/sync Binlog；
• InnoDB commit (commit in memory)；

總結

寫得是有點累啊，不過有大家的支持我覺得就是值得的，也是我更新的動力，不對的地方希望大家幫忙指正，覺得寫得還可以的話麻煩大家幫我點個贊哈哈哈。

最後我們還是以一張圖來將流程描述清楚：

我們記住幾個重點即可：

1. 先記錄到內存，再寫日誌文件；
2. 記錄 redo log 分為兩個階段；
3. 存儲引擎和 Server 記錄不同的日誌；
4. 先記錄 redo，再記錄 binlog。

原文鏈接：https://blog.csdn.net/weixin_42669785/article/details/104114754

閱讀更多 架構師之路 的文章

關鍵字: 算法 語句 寫入