MySQL儲存引擎詳解之InnoDB架構

程式設計師必備介面測試偵錯工具：

推薦學習：

目前MySQL8.x版本資料庫已經支援了很多儲存引擎了，但是一般我們常用的就幾種，容易形成思維固化不會輕易採取其他儲存引擎，從而錯失很多優化儲存的功能。因此對現支援的九種資料庫儲存引擎的功能有個清楚的理解是個值得學習的事情。本篇文章將這八種資料庫儲存引擎的功能和作用以及使用場景都講清楚。

此係列文章將被納入我的專欄一文速學SQL各類資料庫操作，基本覆蓋到使用SQL處理日常業務以及常規的查詢建庫分析以及複雜操作方方面面的問題。從基礎的建庫建表逐步入門到處理各類資料庫複雜操作，以及專業的SQL常用函數講解都花費了大量時間和心思創作，如果大家有需要從事資料分析或者資料開發的朋友推薦訂閱專欄，將在第一時間學習到最實用常用的知識。此篇部落格篇幅較長，值得細讀實踐一番，我會將精華部分挑出細講實踐。博主會長期維護博文，有錯誤或者疑惑可以在評論區指出，感謝大家的支援。

一、支援的儲存引擎

進入MySQL的資料庫檢視儲存引擎就可以看到MySQL資料庫所有支援的儲存引擎：

SHOW ENGINES

目前有一個引擎Federated不支援，我們只需要清楚其他八種資料庫儲存就好。

MySQL中常見的資料庫引擎有MyISAM、InnoDB、Memory。那麼我們就先清楚這三種引擎。

二、InnoDB引擎

InnoDB是MySQL的預設引擎，一個支援事務安全的儲存引擎。mysql中資料是儲存在物理磁碟上的，而真正的資料處理又是在記憶體中執行的。由於磁碟的讀寫速度非常慢，如果每次操作都對磁碟進行頻繁讀寫的話，那麼效能就會非常差。

為了上述問題，InnoDB將資料劃分為若干頁，以頁作為磁碟與記憶體互動的基本單位，一般頁的大小為16KB。這樣的話，一次性至少讀取1頁資料到記憶體中或者將1頁資料寫入磁碟。通過減少記憶體與磁碟的互動次數，從而提升效能。

這本質上就是一種典型的快取設計思想，一般快取的設計基本都是從時間維度或者空間維度進行考量的：

• 時間維度：如果一條資料正在在被使用，那麼在接下來一段時間內大概率還會再被使用。可以認為熱點資料快取都屬於這種思路的實現。

• 空間維度：如果一條資料正在在被使用，那麼儲存在它附近的資料大概率也會很快被使用。InnoDB的資料頁和作業系統的頁快取則是這種思路的體現。

下面是官方的InnoDB引擎結構圖，主要分為記憶體結構和磁碟結構兩大部分。

記憶體結構主要包括Buffer Pool、Change Buffer、Adaptive Hash Index和Log Buffer四大元件。

1.Buffer Pool

Buffer Pool由包含資料、索引、insert buffer ,adaptive hash index,lock 資訊及資料字典。緩衝池，簡稱BP。BP以Page頁為單位，預設大小16K，BP的底層採用連結串列資料結構管理Page。在InnoDB存取表記錄和索引時會在Page頁中快取，以後使用可以減少磁碟IO操作，提升效率。

緩衝池簡單來說就是一塊記憶體區域，通過記憶體的速度來彌補磁碟速度較慢對資料庫效能的影響。在資料庫中進行讀取頁的操作，首先將從磁碟讀到的頁存放在緩衝池中，這個過程稱為將頁"FIX"在緩衝池中。下一次再讀取相同的頁時，首先判斷該頁是否在緩衝池中。若在緩衝池中，稱該頁在緩衝池中被命中。直接讀取該頁。否則讀取磁碟上的頁。對於資料庫中頁的修改操作，則首先修改在緩衝池中的頁，然後再以一定的頻率重新整理到磁碟上。這裡需要注意的是，頁從緩衝池重新整理回磁碟的操作並不是每次頁發生更新時觸發，而是通過一種稱為Checkpoint的機制重新整理回磁碟。同樣這也是為了提高資料庫的整體效能。

傳統LUR演演算法

緩衝池是通過LRU(Latest Recent Used，最近最少使用)演演算法來進行管理的，即最頻繁使用的頁在LRU列表的最前段，而最少使用的頁在LRU列表的尾端，當緩衝池不能存放新讀取到的頁時，首先釋放LRU列表尾端的頁：

（1）頁已經在緩衝池裡，那就只做「移至」LRU頭部的動作，而沒有頁被淘汰；

（2）頁不在緩衝池裡，除了做「放入」LRU頭部的動作，還要做「淘汰」LRU尾部頁的動作；

但是InnoDB的LUR演演算法並不是傳統的LUR演演算法。

這裡有兩個問題：

（1）預讀失效；

（2）緩衝池汙染；

我們先了解什麼是預讀；

預讀

磁碟讀寫，並不是按需讀取，而是按頁讀取，一次至少讀一頁資料（一般是4K），如果未來要讀取的資料就在頁中，就能夠省去後續的磁碟IO，提高效率。資料存取，通常都遵循「集中讀寫」的原則，使用一些資料，大概率會使用附近的資料，這就是所謂的「區域性性原理」，它表明提前載入是有效的，確實能夠減少磁碟IO。

預讀失效

由於預讀(Read-Ahead)，提前把頁放入了緩衝池，但最終MySQL並沒有從頁中讀取資料，稱為預讀失效。

要優化預讀失效，思路是：

（1）讓預讀失敗的頁，停留在緩衝池LRU裡的時間儘可能短；

（2）讓真正被讀取的頁，才挪到緩衝池LRU的頭部；

以保證，真正被讀取的熱資料留在緩衝池裡的時間儘可能長。

具體方法是：

（1）將LRU分為兩個部分：

新生代(new sublist)

老生代(old sublist)

（2）新老生代收尾相連，即：新生代的尾(tail)連線著老生代的頭(head)；

（3）新頁（例如被預讀的頁）加入緩衝池時，只加入到老生代頭部：

如果資料真正被讀取（預讀成功），才會加入到新生代的頭部

如果資料沒有被讀取，則會比新生代裡的「熱資料頁」更早被淘汰出緩衝池

新老生代改進版LRU仍然解決不了緩衝池汙染的問題。

2.Log Buffer

Log Buffer用來快取重做紀錄檔。

InnoDB有兩個非常重要的紀錄檔：undo log、redo log

（1）通過undo log可以看到資料較早版本，實現MVCC，或回滾事務等功能。

（2）通過redo log用來保證事務永續性。

redo紀錄檔緩衝區是記憶體儲存區域，用於儲存要寫入磁碟上的紀錄檔檔案的資料。紀錄檔緩衝區大小由innodb_log_buffer_size 變數定義，預設大小為16MB。

紀錄檔緩衝區的內容定期重新整理到磁碟。較大的紀錄檔緩衝區可以執行大型事務，而無需在事務提交之前將重做紀錄檔資料寫入磁碟。因此，如果有更新，插入或刪除許多行的事務，則增加紀錄檔緩衝區的大小可以節省磁碟I/O。

innodb_flush_log_at_trx_commit ：控制如何將紀錄檔緩衝區的內容寫入並重新整理到磁碟。

innodb_flush_log_at_timeout ：控制紀錄檔重新整理頻率。

如果磁碟I/O導致效能問題，則需要觀察事務,例如涉及許多BLOB條目的事務。只要InnoDB紀錄檔緩衝區已滿，便會將其重新整理到磁碟，因此增加緩衝區大小可以減少I/O。

紀錄檔檔案的預設數量為兩個: ib_logfile0 和 ib_logfile1 。

紀錄檔具有固定大小，預設大小取決於MySQL版本。

3.Adaptive Hash Index

Adaptive Hash Index自適應hash索引是一種鍵值對的儲存結構，儲存的是熱點頁所在的記錄。InnoDB儲存引擎會自動根據存取的頻率和模式 來為某些頁建立雜湊索引。

上面的圖就是區分B+樹索引和自適應hash索引的區別。 通過引數innodb_adaptive_hash_index來禁用或啟動此特性，預設為開啟。

4.Change Buffer

Change Buffer：MySQL中資料分為記憶體和磁碟兩個部分；在buffer pool中快取熱的資料頁和索引頁，減少磁碟讀；通過change buffer就是為了緩解磁碟寫的一種手段。

當需要更新一個資料頁時，如果資料頁在記憶體中就直接更新。如果資料頁不在記憶體中。在不影響資料一致性的前下，InooDB 會將這些更新操作快取在 change buffer 中，這樣就不需要從磁碟中讀入這個資料頁了。在下次查詢需要存取這個資料頁的時候，將資料頁讀入記憶體，然後執行 change buffer 中與這個頁有關的操作。通過這種方式就能保證這個資料邏輯的正確性。

雖然名字叫作 change buffer，實際上它是可以持久化的資料。也就是說，change buffer 在記憶體中有拷貝，也會被寫入到磁碟上（ibdata）。

 將 change buffer 中的操作合併到原資料頁，得到最新結果的過程稱為 merge。以下情況會觸發merge：

• 存取這個資料頁；

• 後臺master執行緒會定期 merge；

• 資料庫緩衝池不夠用時；

• 資料庫正常關閉時；

• redo log寫滿時；

change buffer就是在非唯一普通索引頁不在buffer pool中時，對頁進行了寫操作的情況下，先將記錄變更緩衝，等未來資料被讀取時，再將 change buffer 中的操作merge到原資料頁的技術。在MySQL5.5之前，叫插入緩衝(insert buffer)，只針對insert做了優化；現在對delete和update也有效，叫做寫緩衝(change buffer)。

推薦學習：

以上就是MySQL儲存引擎詳解之InnoDB架構的詳細內容，更多請關注TW511.COM其它相關文章！