[原理解析] MySQL insert 語句的磁盤寫入之旅

作者及簡介：

黃 炎

王 悅，愛可生研發團隊成員，負責數據庫管理平臺相關項目的開發和故障排查，好奇 MySQL 技術原理及各類數據庫實現方案。

一條 insert 語句在寫入磁盤的過程中到底涉及了哪些文件？順序又是如何的？

下面我們用兩張圖和大家一起解析 insert 語句的磁盤寫入之旅。

圖 1：事務提交前的日誌文件寫入

旅途過程：

1. 首先 insert 進入 server 層後，會進行一些必要的檢查，檢查的過程中並不會涉及到磁盤的寫入。
2. 檢查沒有問題之後，便進入引擎層開始正式的提交。我們知道 InnoDB 會將數據頁緩存至內存中的 buffer pool，所以 insert 語句到了這裡並不需要立刻將數據寫入磁盤文件中，只需要修改 buffer pool 當中對應的數據頁就可以了。

• buffer pool 中的數據頁刷盤並不需要在事務提交前完成，其中的交互過程我們會在下一張圖中分解。

1. 但僅僅寫入內存的 buffer pool 並不能保證數據的持久化，如果 MySQL 宕機重啟了，需要保證 insert 的數據不會丟失。redo log 因此而生，當 innodb_flush_log_at_trx_commit=1 時，每次事務提交都會觸發一次 redo log 刷盤。（redo log 是順序寫入，相比直接修改數據文件，redo 的磁盤寫入效率更加高效）
2. 如果開啟了 binlog 日誌，我們還需將事務邏輯數據寫入 binlog 文件，且為了保證複製安全，建議使用 sync_binlog=1 ，也就是每次事務提交時，都要將 binlog 日誌的變更刷入磁盤。

綜上（在 InnoDB buffer pool 足夠大且上述的兩個參數設置為雙一時），insert 語句成功提交時，真正發生磁盤數據寫入的，並不是 MySQL 的數據文件，而是 redo log 和 binlog 文件。

然而，InnoDB buffer pool 不可能無限大，redo log 也需要定期輪換，很難容下所有的數據，下面我們就來看看 buffer pool 與磁盤數據文件的交互方式。

名詞背景說明

double write 背景

InnoDB buffer pool 一頁髒頁大小為 16 KB，如果只寫了前 4KB 時發生宕機，那這個髒頁就發生了寫失敗，會造成數據丟失。為了避免這一問題，InnoDB 使用了 double write 機制（InnoDB 將 double write 的數據存於共享表空間中）。在寫入數據文件之前，先將髒頁寫入 double write 中，當然這裡的寫入都是需要刷盤的。有人會問 redo log 不是也能恢復數據頁嗎？為什麼還需要 double write？這是因為 redo log 中記錄的是頁的偏移量，比如在頁偏移量為 800 的地方寫入數據 xxx，而如果頁本身已經發生損壞，應用 redo log 也無濟於事。

insert buffer 背景

InnoDB 的數據是根據聚集索引排列的，通常業務在插入數據時是按照主鍵遞增的，所以插入聚集索引一般是順序磁盤寫入。但是不可能每張表都只有聚集索引，當存在非聚集索引時，對於非聚集索引的變更就可能不是順序的，會拖慢整體的插入性能。為了解決這一問題，InnoDB 使用了 insert buffer 機制，將對於非聚集索引的變更先放入 insert buffer ，儘量合併一些數據頁後再寫入實際的非聚集索引中去。

圖 2：事務提交後的數據文件寫入

旅途過程：

1. 當 buffer pool 中的數據頁達到一定量的髒頁或 InnoDB 的 IO 壓力較小 時，都會觸發髒頁的刷盤操作。

2. 當開啟 double write 時，InnoDB 刷髒頁時首先會複製一份刷入 double write，在這個過程中，由於double write的頁是連續的，對磁盤的寫入也是順序操作，性能消耗不大。

3. 無論是否經過 double write，髒頁最終還是需要刷入表空間的數據文件。刷入完成後才能釋放 buffer pool 當中的空間。

4. insert buffer 也是 buffer pool 中的一部分，當 buffer pool 空間不足需要交換出部分髒頁時，有可能將 insert buffer 的數據頁換出，刷入共享表空間中的 insert buffer 數據文件中。

5. 當 innodb_stats_persistent=ON 時，SQL 語句所涉及到的 InnoDB 統計信息也會被刷盤到 innodb_table_stats 和 innodb_index_stats 這兩張系統表中，這樣就不用每次再實時計算了。

6. 有一些情況下可以不經過 double write 直接刷盤

• a. 關閉 double write
• b. 不需要 double write 保障，如 drop table 等操作

彙總兩張圖，一條 insert 語句的所有涉及到的數據在磁盤上會依次寫入 redo log，binlog，(double write，insert buffer) 共享表空間，最後在自己的用戶表空間落定為安。

閱讀更多 愛可生 的文章

關鍵字: 內存 SQL 寫入