MySQL的InnoDB索引詳細分析

MySQL的InnoDB索引詳細分析

摘要：

本篇介紹下MySQL的InnoDB索引相關知識，從各種樹到索引原理到儲存的細節。

InnoDB是MySQL的預設儲存引擎(MySQL5.5.5之前是MyISAM，文檔)。本著高效學習的目的，本篇以介紹InnoDB為主，少量涉及MyISAM作為對比。

這篇文章是我在學習過程中總結完成的，內容主要來自書本和部落格(參考文獻會給出)，過程中加入了一些自己的理解，描述不準確的地方煩請指出。

1 各種樹形結構

本來不打算從二叉搜尋樹開始，因為網上已經有太多相關文章，但是考慮到清晰的圖示對理解問題有很大協助，也為了保證文章完整性，最後還是加上了這部分。

先看看幾種樹形結構：

1 搜尋二叉樹：每個節點有兩個子節點，資料量的增大必然導致高度的快速增加，顯然這個不適合作為大量資料存放區的基礎結構。

2 B樹：一棵m階B樹是一棵平衡的m路搜尋樹。最重要的性質是每個非根節點所包含的關鍵字個數 j 滿足：┌m/2┐ - 1

3 B+樹：一棵m階B樹是一棵平衡的m路搜尋樹。最重要的性質是每個非根節點所包含的關鍵字個數 j 滿足：┌m/2┐ - 1

4 B*樹：一棵m階B樹是一棵平衡的m路搜尋樹。最重要的兩個性質是1每個非根節點所包含的關鍵字個數 j 滿足：┌m2/3┐ - 1

B/B+/B*三種樹有相似的操作，比如檢索/插入/刪除節點。這裡只重點關注插入節點的情況，且只分析他們在當前節點已滿情況下的插入操作，因為這個動作稍微複雜且能充分體現幾種樹的差異。與之對比的是檢索節點比較容易實現，而刪除節點只要完成與插入相反的過程即可（在實際應用中刪除並不是插入的完全逆操作，往往只刪除資料而保留下空間為後續使用）。

先看B樹的分裂，的紅色值即為每次新插入的節點。每當一個節點滿後，就需要發生分裂（分裂是一個遞迴過程，參考下面7的插入導致了兩層分裂），由於B樹的非葉子節點同樣儲存了索引值，所以已滿節點分裂後的值將分布在三個地方：1原節點，2原節點的父節點，3原節點的建立兄弟節點（參考5，7的插入過程）。分裂有可能導致樹的高度增加（參考3，7的插入過程），也可能不影響樹的高度（參考5，6的插入過程）。

B+樹的分裂：當一個結點滿時，分配一個新的結點，並將原結點中1/2的資料複製到新結點，最後在父結點中增加新結點的指標；B+樹的分裂隻影響原結點和父結點，而不會影響兄弟結點，所以它不需要指向兄弟節點的指標。

B*樹的分裂：當一個結點滿時，如果它的下一個兄弟結點未滿，那麼將一部分資料移到兄弟結點中，再在原結點插入關鍵字，最後修改父結點中兄弟結點的關鍵字（因為兄弟結點的關鍵字範圍改變了）。如果兄弟也滿了，則在原結點與兄弟結點之間增加新結點，並各複製1/3的資料到新結點，最後在父結點增加新結點的指標。可以看到B*樹的分裂非常巧妙，因為B*樹要保證分裂後的節點還要2/3滿，如果採用B+樹的方法，只是簡單的將已滿的節點一分為二，會導致每個節點只有1/2滿，這不滿足B*樹的要求了。所以B*樹採取的策略是在本節點滿後，繼續插入兄弟節點（這也是為什麼B*樹需要在非葉子節點加一個兄弟間的鏈表），直到把兄弟節點也塞滿，然後拉上兄弟節點一起湊份子，自己和兄弟節點各出資1/3成立新節點，這樣的結果是3個節點剛好是2/3滿，達到B*樹的要求，皆大歡喜。

B+樹適合作為資料庫的基礎結構，完全是因為電腦的記憶體-機械硬碟兩層儲存結構。記憶體可以完成快速的隨機訪問（隨機訪問即給出任意一個地址，要求返回這個地址儲存的資料）但是容量較小。而硬碟的隨機訪問要經過機械動作（1磁頭移動 2碟片轉動），訪問效率比記憶體低幾個數量級，但是硬碟容量較大。典型的資料庫容量大大超過可用記憶體大小，這就決定了在B+樹中檢索一條資料很可能要藉助幾次磁碟IO操作來完成。如所示：通常向下讀取一個節點的動作可能會是一次磁碟IO操作，不過非分葉節點通常會在初始階段載入記憶體以加快訪問速度。同時為提高在節點間橫向遍曆速度，真實資料庫中可能會將圖中藍色的CPU計算/記憶體讀取最佳化成二叉搜尋樹（InnoDB中的page directory機制）。

真實資料庫中的B+樹應該是非常扁平的，可以通過向表中順序插入足夠資料的方式來驗證InnoDB中的B+樹到底有多扁平。我們通過如的CREATE語句建立一個只有簡單欄位的測試表，然後不斷添加資料來填充這個表。通過的統計資料（來源見參考文獻1）可以分析出幾個直觀的結論，這幾個結論宏觀的展現了資料庫裡B+樹的尺度。

MySQL InnoDB儲存引擎鎖機制實驗

InnoDB儲存引擎的啟動、關閉與恢複

MySQL InnoDB獨立資料表空間的配置

MySQL Server 層和 InnoDB 引擎層 體繫結構圖

InnoDB 死結案例解析

MySQL Innodb獨立資料表空間的配置

1 每個葉子節點儲存了468行資料，每個非葉子節點儲存了大約1200個索引值，這是一棵平衡的1200路搜尋樹！

2 對於一個22.1G容量的表，也只需要高度為3的B+樹就能儲存了，這個容量大概能滿足很多應用的需要了。如果把高度增大到4，則B+樹的儲存容量立刻增大到25.9T之巨！

3 對於一個22.1G容量的表，B+樹的高度是3，如果要把非分葉節點全部載入到記憶體也只需要少於18.8M的記憶體（如何得出的這個結論？因為對於高度為2的樹，1203個葉子節點也只需要18.8M空間，而22.1G從良表的高度是3，非分葉節點1204個。同時我們假設葉子節點的尺寸是大於非分葉節點的，因為葉子節點儲存了行資料而非分葉節點只有鍵和少量資料。），只使用如此少的記憶體就可以保證只需要一次磁碟IO操作就檢索出所需的資料，效率是非常之高的。

更多詳情見請繼續閱讀下一頁的精彩內容：  

1 2 3 4 下一頁