MySQL資料庫中的索引（一）——索引實現原理

標籤：雜湊索引   優秀   複雜   ram   技術分享   points   分享   概念   分離   

今天我們來探討一下資料庫中一個很重要的概念：索引。

MySQL官方對索引的定義為：索引（Index）是協助MySQL高效擷取資料的資料結構，即索引是一種資料結構。

我們知道，資料庫查詢是資料庫的最主要功能之一。我們都希望查詢資料的速度能儘可能的快，因此資料庫系統的設計者會從查詢演算法的角度進行最佳化。最基本的查詢演算法當然是順序尋找（linear search），這種複雜度為O(n)的演算法在資料量很大時顯然是糟糕的，好在電腦科學的發展提供了很多更優秀的尋找演算法，例如二分尋找（binary search）、二叉樹尋找（binary tree search）等。如果稍微分析一下會發現，每種尋找演算法都只能應用於特定的資料結構之上，例如二分尋找要求被檢索資料有序，而二叉樹尋找只能應用於二叉尋找樹上，但是資料本身的組織圖不可能完全滿足各種資料結構（例如，理論上不可能同時將兩列都按順序進行組織），所以，在資料之外，資料庫系統還維護著滿足特定尋找演算法的資料結構，這些資料結構以某種方式引用（指向）資料，這樣就可以在這些資料結構上實現進階尋找演算法。這種資料結構，就是索引。

我們來看一個例子：

展示了一種可能的索引方式。左邊是資料表，一共有兩列七條記錄，最左邊的是資料記錄的物理地址（注意邏輯上相鄰的記錄在磁碟上也並不是一定物理相鄰的）。為了加快Col2的尋找，可以維護一個右邊所示的二叉尋找樹，每個節點分別包含索引索引值和一個指向對應資料記錄物理地址的指標，這樣就可以運用二叉尋找在O(log2n)的複雜度內擷取到相應資料。

雖然這是一個貨真價實的索引，但是實際的資料庫系統幾乎沒有使用二叉尋找樹或其進化品種紅/黑樹狀結構（red-black tree）實現的，因為它們的效率相對於B樹以及雜湊來說特別的低。

接下來介紹的索引的兩種實現方式：B+樹和雜湊。

 

B樹和B+樹

學過資料結構的朋友都知道B樹和B+樹，它們的原理在這就不多闡述，在MySQL資料庫中普遍採用的是B+樹，那為什麼不用B樹呢，我們現在就來探討一下。

一般來說，索引本身也很大，不可能全部儲存在記憶體中，因此索引往往以索引檔案的形式儲存的磁碟上。這樣的話，索引尋找過程中就要產生磁碟I/O消耗，相對於記憶體存取，I/O存取的消耗要高几個數量級，所以評價一個資料結構作為索引的優劣最重要的指標就是在尋找過程中磁碟I/O操作次數的漸進複雜度。換句話說，索引的結構組織要盡量減少尋找過程中磁碟I/O的存取次數。

局部性原理與磁碟預讀

由於儲存介質的特性，磁碟本身存取就比主存慢很多，再加上機械運動耗費，磁碟的存取速度往往是主存的幾百分分之一，因此為了提高效率，要盡量減少磁碟I/O。為了達到這個目的，磁碟往往不是嚴格按需讀取，而是每次都會預讀，即使只需要一個位元組，磁碟也會從這個位置開始，順序向後讀取一定長度的資料放入記憶體。這樣做的理論依據是電腦科學中著名的局部性原理：

當一個資料被用到時，其附近的資料也通常會馬上被使用。

程式運行期間所需要的資料通常比較集中。

由於磁碟順序讀取的效率很高（不需要尋道時間，只需很少的旋轉時間），因此對於具有局部性的程式來說，預讀可以提高I/O效率。

預讀的長度一般為頁（page）的整倍數。頁是電腦管理儲存空間的邏輯塊，硬體及作業系統往往將主存和磁碟儲存區分割為連續的大小相等的塊，每個儲存塊稱為一頁（在許多作業系統中，頁得大小通常為4k），主存和磁碟以頁為單位交換資料。當程式要讀取的資料不在主存中時，會觸發一個缺頁異常，此時系統會向磁碟發出讀盤訊號，磁碟會找到資料的起始位置並向後連續讀取一頁或幾頁載入記憶體中，然後異常返回，程式繼續運行。

B樹和B+樹的效能分析

選擇B+樹而不是B樹的原因主要是因為B+樹的尋找效率比起B樹來說要高得多。

上文說過一般使用磁碟I/O次數評價索引結構的優劣。先從B-Tree分析，根據B-Tree的定義，可知檢索一次最多需要訪問h個節點。資料庫系統的設計者巧妙利用了磁碟預讀原理，將一個節點的大小設為等於一個頁，這樣每個節點只需要一次I/O就可以完全載入。為了達到這個目的，在實際實現B-Tree還需要使用如下技巧：

每次建立節點時，直接申請一個頁的空間，這樣就保證一個節點物理上也儲存在一個頁裡，加之電腦儲存分配都是按頁對齊的，就實現了一個node只需一次I/O。

B-Tree中一次檢索最多需要h-1次I/O（根節點常駐記憶體），漸進複雜度為O(h)=O(logdN)O(h)=O(logdN)。一般實際應用中，出度d是非常大的數字，通常超過100，因此h非常小（通常不超過3）。

綜上所述，用B-Tree作為索引結構效率是非常高的。

而紅/黑樹狀結構這種結構，h明顯要深的多。由於邏輯上很近的節點（父子）物理上可能很遠，無法利用局部性，所以紅/黑樹狀結構的I/O漸進複雜度也為O(h)，效率明顯比B-Tree差很多。

B+Tree更適合外存索引，原因和內節點出度d有關。從上面分析可以看到，d越大索引的效能越好，而出度的上限取決於節點內key和data的大小：

dmax = floor( pagesize/( keysize+datasize+pointsize ) )

floor表示向下取整。由於B+Tree只在分葉節點才有data域，內節點去掉了data域，因此可以擁有更大的出度，擁有更好的效能。

另外，B+樹中，分葉節點增加了一個指向相鄰葉子節點的指標，極大地提高了區間訪問的效能。

 

在B樹中尋找給定關鍵字的方法是：首先把根結點取來，在根結點所包含的關鍵字K1,…,kj尋找給定的關鍵字（可用順序尋找或二分尋找法），若找到等於給定值的關鍵字，則尋找成功；否則，一定可以確定要查的關鍵字在某個Ki或Ki+1之間，於是取Pi所指的下一層索引節點塊繼續尋找，直到找到，或指標Pi為空白時尋找失敗。

 

B+樹有2個頭指標，一個是樹的根節點，一個是最小關鍵碼的分葉節點。

所以 B+樹有兩種搜尋方法：

一種是按分葉節點自己拉起的鏈表順序搜尋。

一種是從根節點開始搜尋，和B樹類似，不過如果非分葉節點的關鍵碼等於給定值，搜尋並不停止，而是繼續沿右指標，一直查到分葉節點上的關鍵碼。所以無論搜尋是否成功，都將走完樹的所有層。

B+ 型樹狀結構中，資料對象的插入和刪除僅在分葉節點上進行。

 

這兩種處理索引的資料結構的不同之處：
a，B樹中同一索引值不會出現多次，並且它有可能出現在葉結點，也有可能出現在非葉結點中。而B+樹的鍵一定會出現在葉結點中，並且有可能在非葉結點中也有可能重複出現，以維持B+樹的平衡。
b，因為B樹鍵位置不定，且在整個樹結構中只出現一次，雖然可以節省儲存空間，但使得在插入、刪除操作複雜度明顯增加。B+樹相比來說是一種較好的折中。
c，B樹的查詢效率與鍵在樹中的位置有關，最大時間複雜度與B+樹相同(在葉結點的時候)，最小時間複雜度為1(在根結點的時候)。而B+樹的時候覆雜度對某建成的樹是固定的。

MyISAM和InnoDB不同的索引實現

在MySQL中，索引屬於儲存引擎層級的概念，不同儲存引擎對索引的實現方式是不同的，本文主要討論MyISAM和InnoDB兩個儲存引擎的索引實現方式。

MyISAM索引實現

MyISAM引擎使用B+Tree作為索引結構，分葉節點的data域存放的是資料記錄的地址。是MyISAM索引的原理圖：

這裡設表一共有三列，假設我們以Col1為主鍵，則是一個MyISAM表的主索引（Primary key）示意。可以看出MyISAM的索引檔案僅僅儲存資料記錄的地址。在MyISAM中，主索引和輔助索引（Secondary key）在結構上沒有任何區別，只是主索引要求key是唯一的，而輔助索引的key可以重複。如果我們在Col2上建立一個輔助索引，則此索引的結構如所示：

同樣也是一顆B+Tree，data域儲存資料記錄的地址。因此，MyISAM中索引檢索的演算法為首先按照B+Tree搜尋演算法搜尋索引，如果指定的Key存在，則取出其data域的值，然後以data域的值為地址，讀取相應資料記錄。

MyISAM的索引方式也叫做“非聚集”的，之所以這麼稱呼是為了與InnoDB的叢集索引區分。

InnoDB索引實現

雖然InnoDB也使用B+Tree作為索引結構，但具體實現方式卻與MyISAM截然不同。

第一個重大區別是InnoDB的資料檔案本身就是索引檔案。從上文知道，MyISAM索引檔案和資料檔案是分離的，索引檔案僅儲存資料記錄的地址。而在InnoDB中，表資料檔案本身就是按B+Tree組織的一個索引結構，這棵樹的分葉節點data域儲存了完整的資料記錄。這個索引的key是資料表的主鍵，因此InnoDB表資料檔案本身就是主索引。

是InnoDB主索引（同時也是資料檔案）的，可以看到分葉節點包含了完整的資料記錄。這種索引叫做叢集索引。因為InnoDB的資料檔案本身要按主鍵聚集，所以InnoDB要求表必須有主鍵（MyISAM可以沒有），如果沒有顯式指定，則MySQL系統會自動選擇一個可以唯一標識資料記錄的列作為主鍵，如果不存在這種列，則MySQL自動為InnoDB表產生一個隱含欄位作為主鍵，這個欄位長度為6個位元組，類型為長整形。

第二個與MyISAM索引的不同是InnoDB的輔助索引data域儲存相應記錄主鍵的值而不是地址。換句話說，InnoDB的所有輔助索引都引用主鍵作為data域。例如，圖11為定義在Col3上的一個輔助索引：

這裡以英文字元的ASCII碼作為比較準則。叢集索引這種實現方式使得按主鍵的搜尋十分高效，但是輔助索引搜尋需要檢索兩遍索引：首先檢索輔助索引獲得主鍵，然後用主鍵到主索引中檢索獲得記錄。

瞭解不同儲存引擎的索引實現方式對於正確使用和最佳化索引都非常有協助，例如知道了InnoDB的索引實現後，就很容易明白為什麼不建議使用過長的欄位作為主鍵，因為所有輔助索引都引用主索引，過長的主索引會令輔助索引變得過大。再例如，用非單調的欄位作為主鍵在InnoDB中不是個好主意，因為InnoDB資料檔案本身是一顆B+Tree，非單調的主鍵會造成在插入新記錄時資料檔案為了維持B+Tree的特性而頻繁的分裂調整，十分低效，而使用自增欄位作為主鍵則是一個很好的選擇。

 

雜湊索引

雜湊索引，顧名思義，就是根據索引的索引值計算出響應的hash值，然後根據hash表中的地址來定位元據。

Hash 索引結構的特殊性，其檢索效率非常高，索引的檢索可以一次定位，不像B-Tree 索引需要從根節點到枝節點，最後才能訪問到頁節點這樣多次的IO訪問，所以 Hash 索引的查詢效率要遠高於 B-Tree 索引。
可能很多人又有疑問了，既然 Hash 索引的效率要比 B-Tree 高很多，為什麼大家不都用 Hash 索引而還要使用 B-Tree 索引呢？任何事物都是有兩面性的，Hash 索引也一樣，雖然 Hash 索引效率高，但是 Hash 索引本身由於其特殊性也帶來了很多限制和弊端，主要有以下這些。

（1）Hash 索引僅僅能滿足"=","IN"和"<=>"查詢，不能使用範圍查詢。
     由於 Hash 索引比較的是進行 Hash 運算之後的 Hash 值，所以它只能用於等值的過濾，不能用於基於範圍的過濾，因為經過相應的 Hash 演算法處理之後的 Hash 值的大小關係，並不能保證和Hash運算前完全一樣。

（2）Hash 索引無法被用來避免資料的排序操作。
     由於 Hash 索引中存放的是經過 Hash 計算之後的 Hash 值，而且Hash值的大小關係並不一定和 Hash 運算前的索引值完全一樣，所以資料庫無法利用索引的資料來避免任何排序運算。

（3）Hash 索引不能利用部分索引鍵查詢。
     對於複合式索引，Hash 索引在計算 Hash 值的時候是複合式索引鍵合并後再一起計算 Hash 值，而不是單獨計算 Hash 值，所以通過複合式索引的前面一個或幾個索引鍵進行查詢的時候，Hash 索引也無法被利用。

（4）Hash 索引在任何時候都不能避免表掃描。
     前面已經知道，Hash 索引是將索引鍵通過 Hash 運算之後，將 Hash運算結果的 Hash 值和所對應的行指標資訊存放於一個 Hash 表中，由於不同索引鍵存在相同 Hash 值，所以即使取滿足某個 Hash 索引值的資料的記錄條數，也無法從 Hash 索引中直接完成查詢，還是要通過訪問表中的實際資料進行相應的比較，並得到相應的結果。

（5）Hash 索引遇到大量Hash值相等的情況後效能並不一定就會比B-Tree索引高。
     對於選擇性比較低的索引鍵，如果建立 Hash 索引，那麼將會存在大量記錄指標資訊存於同一個 Hash 值相關聯。這樣要定位某一條記錄時就會非常麻煩，會浪費多次表資料的訪問，而造成整體效能低下。

MySQL資料庫中的索引（一）——索引實現原理