MySQL InnoDB 存儲引擎原理淺析

版權說明： 本文章版權歸本人及博客園共同所有，轉載請標明原文出處 ( https://www.cnblogs.com/mikevictor07/p/12013507.html )，以下內容爲個人理解，僅供參考。

前言：

本文主要基於 MySQL 5.6 以後版本編寫，多數知識來着書籍《MySQL 技術內幕 ++InnoDB 存儲引擎》，本文章僅記錄個人認爲比較重要的部分，有興趣的可以花點時間讀原書。

今年的多數學習知識只寫在筆記裏，較爲零散，最近稍有時間整理出來，分享進步。  

一、MySQL 體系結構

主要包含以下幾部分：

1、管理服務於工具組件。

2、連接池與鑑權。

3、SQL 接口。

4、查詢分析器。

5、優化器組件。

6、緩存與緩衝區。

7、各式的插件式存儲引擎。

8、物理文件。

其中存儲引擎是基於表，而非數據庫。

二、InnoDB 體系結構

InnoDB 引擎包含幾個重要部分:

1、後臺進程：

    1.1 Master Thread：核心線程，負責緩衝池的數據異步入盤，包括髒頁刷新、合併插入緩衝、undo 頁回收等。

    1.2 IO Thread：包括 read thread 和 writer thread，使用 show variables like '%innodb_%io_thread%'; 查看。

    1.3 Purge Thread：回收事務提交後不再需要的 undo log，通過 show variables like '%innodb_purge_threads%'; 查看。

    1.4 Page clear thread：髒頁的刷新操作，從 master thread 分離出來。

2、內存池

    2.1 緩衝池

InnoDB 將記錄按頁的形式進行管理，對於頁的修改先修改緩衝池中的頁，後以一定頻率進行刷新到磁盤中（checkpoint）。在數據庫的頁讀取操作時，將也緩存到緩衝池中，下一次如讀取相同的頁，則無需從磁盤中加載。緩存池大小通過 innodb_buffer_pool_size 配置。

從上圖來看，主要包括索引頁、數據頁、undo 頁、insert buffer、adaptive hash index、數據字典等，其中索引頁和數據頁佔用多數內存。

    配置 innodb_pool_buffer_instances 將緩衝池分割爲多個實例，減少內部競爭 (比如鎖)。

    2.2 LRU list、free list、flush list

    默認的緩衝頁大小是 16KB，使用 LRU 算法進行管理，新從磁盤加載的頁默認加到 LRU 列表的 midpoint 處（尾端算起 37% 位置處）。通過 show engine innodb status 輸出如下（部分）：

Buffer pool size   512  【緩衝池內存 512*16K】

Free buffers       256

Database pages     256  【LRU 列表佔用頁】

Old database pages 0

Modified db pages  0

Pending reads      0

Pending writes: LRU 0, flush list 0, single page 0

Pages made young 0, not young 0

0.00 youngs/s, 0.00 non-youngs/s

Pages read 255, created 40, written 67

0.16 reads/s, 0.06 creates/s, 0.37 writes/s

Buffer pool hit rate 943 / 1000 【緩衝池命中率大於 95% 則良好】, young-making rate 0 / 1000 not 0 / 1000

LRU len: 256, unzip_LRU len: 0 【LRU 列表中的頁可被壓縮分爲 1K/2K/4K/8K 之類的頁】

LRU 列表中的頁被修改後變爲 dirty page，此時緩衝池中的頁和磁盤不一致，通過 checkpoint 刷回磁盤，其中 Flush list 即爲 dirty page 列表。

    2.3 Redo log buffer

    InnoDB 將重做日誌首先刷入緩衝區中，後續以每秒一次刷新到日誌文件中，通過 show variables like 'innodb_log_buffer_size'; 查看，需要保證 mysql 每秒事務量應該小於此大小，通常可以配置 8-32MB。以下情況會刷新緩衝區到磁盤的重做日誌文件中：

    1、Master thread 每秒刷新。

    2、每個事務提交。

    3、緩衝區空間小於 1/2（如果緩衝區過小則導致頻繁的磁盤刷新，降低性能）。

    2**.4 innodb****_****additonal****_mem_****pool****_****size**

如果申請了很大的 buffer pool，此參數應該相應增加，存儲了 LRU、鎖等信息。

3、checkpoint

    每次執行 update、delete 等語句更改記錄時，緩衝池中的頁與磁盤不一致，但是緩衝池的頁不能頻繁刷新到磁盤中（頻率過大性能低），因此增加了 write ahead log 策略，當事務提交時先寫重做日誌，再修改內存頁。當發生宕機時通過重做日誌來恢復。checkpint 解決以下問題：

    （1）減少重做日誌大小，縮減數據恢復時間。

    （2）緩衝池不夠用時將髒頁刷回磁盤。

    （3）重做日誌不可用時將髒頁刷回磁盤（如寫滿）。

 show variables like 'innodb_max_dirty_pages_pct'; (默認 75%) 來控制 inndodb 強制進行 checkpoint。

若每個重做日誌大小爲 1G，定了了兩個總共 2G，則：

    asyn_water_mark = 75 % * 重做日誌總大小。

**    syn_water_mark = 90 % * 重做日誌總大小。**

    （1）當 checkpoint_age < asyn_water_mark 時則不需要刷新髒頁回盤。

    （2）當 syn_water_mark <checkpoint_age < syn_water_mark 時觸發 ASYNC FLUSH。

（3）當 checkpoint_age>syn_water_mark 觸發 sync flush，此情況很少發生，一般出現在大量 load data 或 bulk insert 時。

4、InnoDB 關鍵特性

關鍵特性包括：

(1) Insert buffer.

(2) double write.

(3) adaptive hash index.

(4) Async IO.

(5)Flush neighbor page.

4.1  Insert buffer

若插入按照聚集索引 primary key 插入，頁中的行記錄按照 primary 存放，一般情況下不需要讀取另一個頁記錄，插入速度很快（如果使用 UUID 或者指定的 ID 插入而非自增類型則可能導致非連續插入導致性能下降，由 B + 樹特性決定）。如果按照非聚集索引插入就很有可能存在大量的離散插入，insert buffer 對於非聚集索引的插入和更新操作進行一定頻率的合併操作，再 merge 到真正的索引頁中。使用 insert buffer 需滿足條件：

    （1）索引爲輔助索引。

    （2）索引非唯一。（唯一索引需要從查找索引頁中的唯一性，可能導致離散讀取）

4.2 Double write

Doubel write 保證了頁的可靠性，Redo log 是記錄對頁 (16K) 的物理操作，若 innodb 將頁寫回表時寫了一部分 (如 4K) 出現宕機，則物理頁將會損壞無法通過 redolog 恢復。所以在 apply 重做日誌前，將緩衝池中的髒頁通過 memcpy 到 doublewrite buffer 中，再將 doublewrite buffer 頁分兩次每次 1MB 刷入共享表空間的磁盤文件中（磁盤連續，開銷較小），完成 doublewrite buffer 的頁寫入後再寫入各個表空間的表中。

當寫入頁時發生系統崩潰，恢復過程中，innodb 從共享表空間的 doublewrite 找到該頁的副本，並將其恢復到表空間文件中，再 apply 重做日誌。

4.3 Adaptive hash index

    Innodb 根據訪問頻率對熱點頁建立哈希索引，AHI 的要求是對頁面的訪問模式必須一樣，如連續使用 where a='xxx' 訪問了 100 次。建立熱點哈希後讀取速度可能能提升兩倍，輔助索引連接性能提升 5 倍。

通過 show engine innodb status\G; 查看 hash searches/s, 表示使用自適應哈希，對於範圍查找則不能使用。

4.4 Async IO

    用戶執行一次掃描如果需要查詢多個索引頁，可能會執行多個 IO 操作，AIO 可同時發起多個 IO 請求，系統自動將這些 IO 請求合併（如請求數據頁 [1，2]、[2,3] 則可合併爲從 1 開始連續掃描 3 個頁）提高讀取性能。

4.5 刷新臨近頁

InnoDB 提供刷新臨近頁功能：當刷新一髒頁時，同時檢測所在區 (extent) 的所有頁，如果有髒頁則一併刷新，好處則是通過 AIO 特性合併寫 IO 請求，缺點則是有些頁不怎麼髒也好被刷新，而且頻繁的更改那些不怎麼髒的頁又很快變成髒頁，造成頻繁刷新。對於固態磁盤則考慮關閉此功能（將 innodb_flush_neighbors 設置爲 0）。

5、InnoDB 的啓動、關閉與恢復

5.1 innodb_fast_shutdown

該值影響數據庫正常關閉時的行爲，取值可以爲 0/1/2（默認爲 1）：

【爲 0 時】：關閉過程中需要完成所有的 full purge 好 merge insert buffer，並將所有的髒頁刷新回磁盤，這個過程可能需要一定的時間，如果是升級 InnoDB 則必須將此參數調整爲 0 再關閉數據庫。

【爲 1 時 (默認)】：不需要 full purge 和 merge insert buffer，但會將緩衝池中的髒頁寫回磁盤。

【爲 2 時】：不需要 full purge 和 merge insert buffer，也不會將緩衝池中的髒頁寫回磁盤，而是將日誌寫入日誌文件中，後續啓動時 recovery。

5.2 innodb_force_recovery

參數 innodb_force_recovery 直接影響 InnoDB 的恢復情況。

默認值爲 0：進行所有的恢復操作，當不能進行有效恢復（如數據頁 corrupt）則將錯誤寫入錯誤日誌中。

某些情況下不需要完整的恢復造成，則可定製恢復策略，有以下幾種：

• 1（SRV_FORCE_IGNORE_CORRUPT）：忽略檢查到的 corrupt 頁。
• 2（SRV_FORCE_NO_BACKGROUND）：阻止 Master Thread 線程運行，如果 master thread 需要進行 full purge 操作，這樣會導致 crash。
• 3（SRV_FORACE_NO_TRX_UNDO）: 不進行事務的回滾操作。
• 4（SRV_FORCE_NO_IBUF_MERGE）: 不進行插入緩衝區的合併操作。
• 5（SRV_FORCE_NO_UNDO_LOG_SCAN）: 不查看 undo log，這樣未提交的事務被視爲已提交。
• 6（SRV_FORCE_NO_LOG_REDO）: 不進行 redo 操作。

在設置了 innodb_force_recovery 大於 0 後可對錶進行 select/create/drop 操作，但不能進行 insert update 和 delete 等 DML。如有大事務未提交，並且發生了宕機，恢復過程緩慢，不需要進行事務回滾則將參數設置爲 3 以加快啓動過程。

三、文件

3.1 二進制日誌

    二進制日誌記錄 MySQL 的變更操作 (不包含查詢)，如果數據的影響行數爲 0 也會記錄。主要用於數據的恢復、複製、審計等場景。通過 log-bin 參數配置 binlog 的文件名。影響二進制日誌記錄的行爲有：

(1) max_binglog_size

(2) binlog_cache_size

(3) sync_binlog

(4) binlog-to-db

(5) binlog-ignore-db

(6) log-slave-update

(7) binlog_format

max_binglog_size 指定單個日誌文件最大值，超過則產生新文件，默認爲 1G。

binlog_cache_size 默認爲 32K，記錄未提交的事務，當提交事務後會寫入二進制日誌文件中，該參數是基於會話的，不宜設置過大，通過以下命令檢查是否 cache 不夠導致使用到了磁盤 (binlog_cache_disk_use)，單位爲次數：

$ show variables like 'binlog_cache_size';

$ show global status like 'binlog_cache%'; （該命令顯示的單位爲次數）

如果顯示的 binlog_cache_disk_use 次數較多，則考慮要增加 binlog_cache_size 大小。

sync_binlog 表示每寫多少次緩衝就同步到磁盤，通過設置參數爲 1 則代表同步的方式寫磁盤，但即使將該參數設置爲 1，還有一種異常場景：假設事務發出 commit 前，由於 sync_binlog 設置爲 1 會立即寫盤，但實際上還沒提交事務就宕機，下次重啓前由於沒有 commit 動作事務將會被回滾，但二進制日誌記錄了該事務又不能被回滾，該異常場景通過設置 innodb_support_xa 爲 1 來解決，保證了二進制日誌與 InnoDB 存儲贏錢數據文件的同步。

3.2 InnoDB 存儲引擎文件

3.2.1 表空間文件

    默認共享表空間爲 ibatat1，可通過設定 innodb_data_file_path=/db/ibdata1:2000M; /dir2/db/ibdata2:2000M:autoextend 指定多個共享表空間文件 (用於均衡磁盤負載)，通過設置 autoextend 用完自動增長，該文件不會縮小（即使刪除記錄），只能通過導出數據後，再刪除該文件後重啓再導入才能縮小此文件佔用的空間。

一般情況下開啓參數 innodb_file_per_table=ON 來開個獨立表空間，每個表都有自己的表空間，以：表名. idb 命名，在清空表會後自動釋放此單獨的表空間。

獨立的表空間僅存儲該表的數據、索引、插入緩衝 BITMAP 等信息，其餘的信息還是放在默認表空間中。

3.2.2 重做日誌文件 (Redo log file)

    MySQL 默認初始化 ib_logfile0、ib_logfile1 兩個重做日誌文件，一個用完切換到另一個，影響參數如下：

(1) innodb_log_file_size : 每個 redo log 文件大小。

innodb_log_files_in_group : 文件組中的文件數量，默認爲 2.

innodb_mirrored_log_groups : 鏡像文件組數量，默認爲 1，如果磁盤已做高可用陣列，則用默認的 1 即可，不再需要再做日誌鏡像。

innodb_log_group_home_dir : 日誌文件路徑，默認在數據文件路徑下。

Redo log 設置不易過大，多大則重啓需要恢復時間很長，也不宜過小，過小則導致頻繁發生 async checkpoint，需要刷髒頁回磁盤，影響性能。一般的應用設置爲 1G 即可。

InnoDB 中重做日誌是記錄每個 page 的物理更改情況，而二進制文件是僅在事務提交前提交 (即只寫磁盤一次)，在事務進行過程中，卻不斷有 redo entry 寫入到重做日誌文件中。兩者是由差別的。

參數 innodb_flush_log_at_trx_commit 影響重做日誌的刷寫動作，有以下值：

【0】事務提交時並不寫，而是等待主線程每秒刷寫一次。

【1】默認值，表示執行事務 commit 時同步寫到磁盤，提供最大的安全性，也是最慢的方式。

【2】異步寫磁盤，先寫到系統緩存，交給系統寫到磁盤。

四、表

    表空間由 segment、extend、page 組成，其中 page 是 InnoDB 磁盤管理的最小單位 (默認大小爲 16K)。如下圖：

如果啓用了 innodb_file_per_table 參數，每張表的表空間只存放數據、所以和插入緩衝 bitmap 頁，其他的數據如 undo 信息、插入緩衝、double write buffer 等還是存放在共享表空間中。

4.1 Segment (段)

常見的 segment 有數據段、索引段、回滾段等， 數據段爲 B + 樹的葉子節點 (Leaf node segment)、索引段爲 B + 樹的非葉子節點 (Non-leaf node segment)。如下圖：

4.2 Extend (區)

    每個區大小固定爲 1MB，爲保證區中 page 的連續性通常 InnoDB 會一次從磁盤中申請 4-5 個區。在默認 page 的大小爲 16KB 的情況下，一個區則由 64 個連續的 page。

    InnoDB 1.2.x 版本增加參數 innodb_page_size 參數指定 page 的大小，但區的大小不會改變。 當啓用了 innodb_file_per_table 參數後創建的表大小默認是 96KB，而不是立即是 1MB，是由於每個段開始先使用 32 個頁大小的 fragment page(碎片頁) 來存放數據，對於一些小表可節省磁盤空間。

4.3 Page (頁)

    每個 page 默認大小爲 16K， InnoDB 1.2.x 版本增加參數 innodb_page_size 參數指定 page 的大小，設置完成後表中所有 page 大小都固定，除非重新 dump 再 imports 數據，否則不能再修改 page 大小。page 類型有：

(1) B-tree node - 數據頁

(2) undo log page

(3) system page

(4) transaction system page
(5) insert buffer bitmap

(6) insert buffer free list

(7) uncompressed BLOB page

(8) compressed BLOB page

本文由 Readfog 進行 AMP 轉碼，版權歸原作者所有。
來源：https://www.cnblogs.com/mikevictor07/p/12013507.html