圖解MySQL | [原理解析] MySQL insert 語句的磁盤寫入之旅
- 2020 年 4 月 11 日
- 筆記
作者及簡介：
黃 炎，愛可生首席技術官；
王 悅，愛可生研發團隊成員，負責數據庫管理平台相關項目的開發和故障排查，好奇 MySQL 技術原理及各類數據庫實現方案。
本文來源：轉載自公眾號-圖解 MySQL
*愛可生開源社區出品，原創內容未經授權不得隨意使用，轉載請聯繫小編並註明來源。
一條 insert 語句在寫入磁盤的過程中到底涉及了哪些文件？順序又是如何的？
下面我們用兩張圖和大家一起解析 insert 語句的磁盤寫入之旅。
圖 1：事務提交前的日誌文件寫入
旅途過程：
- 首先 insert 進入 server 層後，會進行一些必要的檢查，檢查的過程中並不會涉及到磁盤的寫入。
- 檢查沒有問題之後，便進入引擎層開始正式的提交。我們知道 InnoDB 會將數據頁緩存至內存中的 buffer pool，所以 insert 語句到了這裡並不需要立刻將數據寫入磁盤文件中，只需要修改 buffer pool 當中對應的數據頁就可以了。
- buffer pool 中的數據頁刷盤並不需要在事務提交前完成，其中的交互過程我們會在下一張圖中分解。
- 但僅僅寫入內存的 buffer pool 並不能保證數據的持久化，如果 MySQL 宕機重啟了，需要保證 insert 的數據不會丟失。redo log 因此而生，當 innodb_flush_log_at_trx_commit=1 時，每次事務提交都會觸發一次 redo log 刷盤。（redo log 是順序寫入，相比直接修改數據文件，redo 的磁盤寫入效率更加高效）
- 如果開啟了 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：事務提交後的數據文件寫入
旅途過程：
- 當 buffer pool 中的數據頁達到一定量的臟頁或 InnoDB 的 IO 壓力較小 時，都會觸發臟頁的刷盤操作。
- 當開啟 double write 時，InnoDB 刷臟頁時首先會複製一份刷入 double write，在這個過程中，由於double write的頁是連續的，對磁盤的寫入也是順序操作，性能消耗不大。
- 無論是否經過 double write，臟頁最終還是需要刷入表空間的數據文件。刷入完成後才能釋放 buffer pool 當中的空間。
- insert buffer 也是 buffer pool 中的一部分，當 buffer pool 空間不足需要交換出部分臟頁時，有可能將 insert buffer 的數據頁換出，刷入共享表空間中的 insert buffer 數據文件中。
- 當 innodb_stats_persistent=ON 時，SQL 語句所涉及到的 InnoDB 統計信息也會被刷盤到 innodb_table_stats 和 innodb_index_stats 這兩張系統表中，這樣就不用每次再實時計算了。
- 有一些情況下可以不經過 double write 直接刷盤
- 關閉 double write
- 不需要 double write 保障，如 drop table 等操作
匯總兩張圖，一條 insert 語句的所有涉及到的數據在磁盤上會依次寫入 redo log，binlog，(double write，insert buffer) 共享表空間，最後在自己的用戶表空間落定為安。
