MySQL B+樹索引和雜湊索引的區別

標籤：

  導讀

在MySQL裡常用的索引資料結構有B+樹索引和雜湊索引兩種，我們來看下這兩種索引資料結構的區別及其不同的應用建議。

二者區別

備忘：先說下，在MySQL文檔裡，實際上是把B+樹索引寫成了BTREE，例如像下面這樣的寫法：

CREATE TABLE t(
aid int unsigned not null auto_increment,
userid int unsigned not null default 0,
username varchar(20) not null default ‘’,
detail varchar(255) not null default ‘’,
primary key(aid),
unique key(uid) USING BTREE,
key (username(12)) USING BTREE — 此處 uname 列只建立了最左12個字元長度的部分索引
)engine=InnoDB;

一個經典的B+樹索引資料結構見：

(圖片源自網路)

B+樹是一個平衡的多叉樹，從根節點到每個葉子節點的高度差值不超過1，而且同層級的節點間有指標相互連結。

在B+樹上的常規檢索，從根節點到葉子節點的搜尋效率基本相當，不會出現大幅波動，而且基於索引的順序掃描時，也可以利用雙向指標快速左右移動，效率非常高。

因此，B+樹索引被廣泛應用於資料庫、檔案系統等情境。順便說一下，xfs檔案系統比ext3/ext4效率高很多的原因之一就是，它的檔案及目錄索引結構全部採用B+樹索引，而ext3/ext4的檔案目錄結構則採用Linked list, hashed B-tree、Extents/Bitmap等索引資料結構，因此在高I/O壓力下，其IOPS能力不如xfs。

詳細可參見：

https://en.wikipedia.org/wiki/Ext4
https://en.wikipedia.org/wiki/XFS

而雜湊索引的則是這樣的：

(圖片源自網路)

簡單地說，雜湊索引就是採用一定的雜湊演算法，把索引值換算成新的雜湊值，檢索時不需要類似B+樹那樣從根節點到葉子節點逐級尋找，只需一次雜湊演算法即可立刻定位到相應的位置，速度非常快。

從上面的圖來看，B+樹索引和雜湊索引的明顯區別是：

• 如果是等值查詢，那麼雜湊索引明顯有絕對優勢，因為只需要經過一次演算法即可找到相應的索引值；當然了，這個前提是，索引值都是唯一的。如果索引值不是唯一的，就需要先找到該鍵所在位置，然後再根據鏈表往後掃描，直到找到相應的資料；

• 從中也能看到，如果是範圍查詢檢索，這時候雜湊索引就毫無用武之地了，因為原先是有序的索引值，經過雜湊演算法後，有可能變成不連續的了，就沒辦法再利用索引完成範圍查詢檢索；

• 同理，雜湊索引也沒辦法利用索引完成排序，以及like ‘xxx%’ 這樣的部分模糊查詢（這種部分模糊查詢，其實本質上也是範圍查詢）；

• 雜湊索引也不支援多列聯合索引的最左匹配規則；

• B+樹索引的關鍵字檢索效率比較平均，不像B樹那樣波動幅度大，在有大量重複索引值情況下，雜湊索引的效率也是極低的，因為存在所謂的雜湊碰撞問題。

後記

在MySQL中，只有HEAP/MEMORY引擎表才能顯式支援雜湊索引（NDB也支援，但這個不常用），InnoDB引擎的自適應雜湊索引（adaptive hash index）不在此列，因為這不是建立索引時可指定的。

還需要注意到：HEAP/MEMORY引擎表在mysql執行個體重啟後，資料會丟失。

通常，B+樹索引結構適用於絕大多數情境，像下面這種情境用雜湊索引才更有優勢：

在HEAP表中，如果儲存的資料重複度很低（也就是說基數很大），對該列資料以等值查詢為主，沒有範圍查詢、沒有排序的時候，特別適合採用雜湊索引

例如這種SQL：
SELECT … FROM t WHERE C1 = ?; — 僅等值查詢

在大多數情境下，都會有範圍查詢、排序、分組等查詢特徵，用B+樹索引就可以了。

 

MySQL B+樹索引和雜湊索引的區別