MySQL：InnoDB儲存引擎的B+樹索引演算法

標籤：blog   http   io   使用   ar   strong   資料   sp   2014   

很早之前，就從學校的圖書館借了MySQL技術內幕，InnoDB儲存引擎這本書，但一直草草閱讀，做的筆記也有些淩亂，趁著現在大四了，課程稍微少了一點，整理一下筆記，按照專題寫一些，加深一下印象，不枉讀了一遍書。與此同時，也加深一下對MySQL的瞭解，認識了原理，對最佳化的原則才有把握，對問題的分析才有源頭。

關於B+樹資料結構①InnoDB儲存引擎支援兩種常見的索引。

一種是B+樹，一種是雜湊。B+樹中的B代表的意思不是二叉（binary），而是平衡（balance），因為B+樹最早是從平衡二叉樹演化來的，但是B+樹又不是一個平衡二叉樹。

同時，B+樹索引並不能找到一個給定索引值的具體行。B+樹索引只能找到的是被尋找資料行所在的頁。然後資料庫通過把頁讀入記憶體，再在記憶體中進行尋找，最後得到尋找的資料。

先從二分尋找法說起：

    二分尋找法的基本思想是，將記錄排序（假如從小到大排序），然後採用跳躍式的方式進行尋找，以有序數列的中點位置為比較對象，如果要找的元素小於該中點元素，那麼尋找左半部分，如果要找的元素大於該中點元素，那麼久找右半部分。比如一組排好序的數：5 10 19 22 30 55 59 60 90， 如果我要尋找60這個數字，那麼先找30，發現30小於60，那麼找30右半部分的中點59，發現59還是小了，那麼找59右邊的數，從而找到了60，這樣通過不斷二分把尋找需要的時間以指數級進行下降，演算法效率到了Logn層級。

 

再說一下平衡二叉樹：

    

 

        這是一幅二叉尋找樹，左子樹的值總是小於根的值，右子樹的值總是大於根的索引值，因此可以通過中序遍曆（以遞迴的方式按照左中右的順序來訪問子樹），因此遍曆以後得到的輸出是9、17、28、35、39、56、65、87。這樣，如果要尋找索引值為28的記錄，先找到根，然後發現根大於28，找左子樹，發現左子樹的根17小於28，再找下一層右子樹，然後找到28。通過了3次尋找找到了需要找的節點。但是如果二叉樹節點分布非常不均勻，就像第二張圖那樣，那麼如果要尋找39這個節點的話，尋找效率和順序尋找就差不多了，最差的結果就是尋找65，那麼二叉搜尋樹就會完全退化成線性表。因此如果想要最大效能地構造一個二叉尋找樹，需要這顆二叉尋找樹是平衡的，平衡二叉樹對於尋找的效能是比較高的，但是不是最高的，只是接近最高的效能。要達到最好的效能，需要建立一顆最優二叉樹，但是最優二叉樹的建立和維護需要大量的操作，因此用平衡 二叉樹就比較好。同時，平衡二叉樹多用於記憶體結構對象中，因此維護他的開銷相對較小。

②為什麼使用B+樹呢？

雖然二叉尋找樹和平衡二叉樹都能夠實現較快的資料尋找，但是，由於資料庫的內容是存在於磁碟上，而磁碟IO與記憶體IO相比，比記憶體IO慢了10^5～10^6倍，為了減少磁碟IO，提高檢索速度，因而才用了B+樹這種資料結構。換言之，B+樹就是為磁碟或其他直接存取輔助裝置而設計的一種平衡二叉樹。

③什麼是B+樹，其特性是什麼

B+樹的概念還是過於複雜，直接比較合適，來一張維基百科上的：

從上面可以看出，所有記錄的節點都在頁節點中，並且是順序存放的，如果我們從最左邊的節點開始遍曆，可以得到的所有索引值的順序是：1、2、3、4、5、6、7。

在B+樹中，所有記錄節點都是按照索引值的大小順序存放在同一層的分葉節點中，各個葉子節點通過指標進行串連。由於一個節點中存放了多條的資料，那麼檢索的時候，進行的磁碟IO次數將會少掉很多。

在B+樹插入的時候，為了保持平衡，對於新插入的索引值可能需要做大量的拆分頁操作，而B+樹主要用於磁碟，因此頁的拆分意味著磁碟操作，因此應該在可能的情況下盡量減少頁的拆分。因此，B+樹提供了旋轉的功能。至於旋轉和刪除等內容，過於複雜，這篇筆記先不做記錄。只是瞭解使用B+樹的原因以及B+樹的特性。

關於索引

InnoDB儲存引擎使用叢集索引，實際的資料行和相關索引值儲存在一塊。因而，在InnoDB中要使用索引訪問資料始終需要兩次尋找，而不是一次。因為索引葉子節點中儲存的不是行的物理位置，而是主鍵的值。即：二次索引-->主鍵-->資料的葉子-->通過資料葉位元組點中的page directory找到資料行。

因為每一張InnoDB的表都會有一個主鍵索引，但是如果沒有顯式指定怎麼辦？如果沒有手工去指定主鍵索引的話，那麼，InnoDB引擎會指派一個unique的列作為主鍵，如果沒有unique的欄位的話，那麼便會自動產生一個隱含的列作為主鍵。

所以，在在InnoDB的設計中，應該儘可能的使用一個與業務無關auto_increment的自增主鍵，而不要去使用uuid之類的隨機（無序）的聚集鍵。同時，由於所有的索引都使用主鍵的索引，如果主鍵索引過長，也會使輔助索引相應的變大。

叢集索引的儲存並不是物理上的連續，而是邏輯上連續的。一方面，頁通過雙向鏈表串連，頁按照主鍵的順序排列；另一方面，每個頁中的記錄也是通過雙向鏈表進行維護，實體儲存體上可以同樣不按照主鍵儲存。

對於目前的MySQL來說，所有的對於索引的添加或者刪除操作，MySQL資料庫都是要先建立一張新的暫存資料表，然後再把資料匯入暫存資料表，再刪除原來的表，然後再把暫存資料表命名為原來的表。所以，如果一張表中資料太多的話，那麼後期添加刪除索引需要花費很長的時間，因而最好在資料庫設計初期便設計好索引。

還有，雖然InnoDB儲存引擎從版本innoDB Plugin開始，支援一種稱為快速索引建立的方法，但是這種方法只限定於輔助索引，對於主鍵的建立和刪除還是需要重建一張表。

 

更好的資料：

[1]敲代碼的張揚的《MySQL索引背後的資料結構和演算法》

[2]《MySQL技術內幕：InnoDB儲存引擎》

MySQL：InnoDB儲存引擎的B+樹索引演算法