mysql的索引分類B+和hash詳解

MySQL目前主要有以下幾種索引類型：FULLTEXT，HASH，BTREE，RTREE。

那麼，這幾種索引有什麼功能和性能上的不同呢？

FULLTEXT

即為全文索引，目前隻有MyISAM引擎支援。其可以在CREATE TABLE ，ALTER TABLE ，CREATE INDEX 使用，不過目前隻有 CHAR、VARCHAR ，TEXT 列上可以建立全文索引。

值得一提的是，在資料量較大時候，現将資料放入一個沒有全局索引的表中，然後再用CREATE INDEX建立FULLTEXT索引，要比先為一張表建立FULLTEXT然後再将資料寫入的速度快很多。

全文索引并不是和MyISAM一起誕生的，它的出現是為了解決WHERE name LIKE “%word%"這類針對文本的模糊查詢效率較低的問題。在沒有全文索引之前，這樣一個查詢語句是要進行周遊資料表操作的，

可見，在資料量較大時是極其的耗時的，如果沒有異步IO處理，程序将被挾持，很浪費時間，當然這裡不對異步IO作進一步講解，想了解的童鞋，自行谷哥。

全文索引的使用方法并不複雜：

建立ALTER TABLE table ADD INDEX

FULLINDEX

USING FULLTEXT(

cname1

[,cname2…]);

使用SELECT * FROM table WHERE MATCH(cname1[,cname2…]) AGAINST ('word' MODE );

其中， MODE為搜尋方式（IN BOOLEAN MODE ，IN NATURAL LANGUAGE MODE ，IN NATURAL LANGUAGE MODE WITH QUERY EXPANSION / WITH QUERY EXPANSION）。

關于這三種搜尋方式，在這裡也不多做交代，簡單地說，就是，布爾模式，允許word裡含一些特殊字元用于标記一些具體的要求，如+表示一定要有，-表示一定沒有，*表示通用比對符，是不是想起了正則，類似吧；

自然語言模式，就是簡單的單詞比對；含表達式的自然語言模式，就是先用自然語言模式處理，對傳回的結果，再進行表達式比對。

對搜尋引擎稍微有點了解的同學，肯定知道分詞這個概念，FULLTEXT索引也是按照分詞原理建立索引的。西文中，大部分為字母文字，分詞可以很友善的按照空格進行分割。

但很明顯，中文不能按照這種方式進行分詞。那又怎麼辦呢？這個向大家介紹一個mysql的中文分詞插件Mysqlcft，有了它，就可以對中文進行分詞，想了解的同學請移步Mysqlcft，當然還有其他的分詞插件可以使用。

HASH

Hash這個詞，可以說，自打我們開始碼的那一天起，就開始不停地見到和使用到了。其實，hash就是一種（key=>value）形式的鍵值對，如數學中的函數映射，允許多個key對應相同的value，但不允許一個key對應多個value。

正是由于這個特性，hash很适合做索引，為某一列或幾列建立hash索引，就會利用這一列或幾列的值通過一定的算法計算出一個hash值，對應一行或幾行資料（這裡在概念上和函數映射有差別，不要混淆）。

在Java語言中，每個類都有自己的hashcode()方法，沒有顯示定義的都繼承自object類，該方法使得每一個對象都是唯一的，在進行對象間equal比較，和序列化傳輸中起到了很重要的作用。

hash的生成方法有很多種，足可以保證hash碼的唯一性，例如在MongoDB中，每一個document都有系統為其生成的唯一的objectID（包含時間戳，主機散列值，程序PID，和自增ID）也是一種hash的表現。

由于hash索引可以一次定位，不需要像樹形索引那樣逐層查找,是以具有極高的效率。那為什麼還需要其他的樹形索引呢？

在這裡就不自己總結了。引用下園子裡其他大神的文章：來自 14的路 的MySQL的btree索引和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 值相關聯。這樣要定位某一條記錄時就會非常麻煩，會浪費多次表資料的通路，而造成整體性能低下。

我稍作補充，講一下HASH索引的過程，順便解釋下上面的第4,5條：

當我們為某一列或某幾列建立hash索引時（目前就隻有MEMORY引擎顯式地支援這種索引），會在硬碟上生成類似如下的檔案：

hash值 存儲位址

1db54bc745a1 77#45b5

4bca452157d4 76#4556,77#45cc…

…

hash值即為通過特定算法由指定列資料計算出來，磁盤位址即為所在資料行存儲在硬碟上的位址（也有可能是其他存儲位址，其實MEMORY會将hash表導入記憶體）。

這樣，當我們進行WHERE age = 18 時，會将18通過相同的算法計算出一個hash值==>在hash表中找到對應的儲存位址==>根據存儲位址取得資料。

是以，每次查詢時都要周遊hash表，直到找到對應的hash值，如（4），資料量大了之後，hash表也會變得龐大起來，性能下降，周遊耗時增加，如（5）。

BTREE

BTREE索引就是一種将索引值按一定的算法，存入一個樹形的資料結構中，相信學過資料結構的童鞋都對當初學習二叉樹這種資料結構的經曆記憶猶新，反正我當時為了軟考可是被這玩意兒好好地折騰了一番，不過那次考試好像沒怎麼考這個。

如二叉樹一樣，每次查詢都是從樹的入口root開始，依次周遊node，擷取leaf。

BTREE在MyISAM裡的形式和Innodb稍有不同

在 Innodb裡，有兩種形态：一是primary key形态，其leaf node裡存放的是資料，而且不僅存放了索引鍵的資料，還存放了其他字段的資料。二是secondary index，其leaf node和普通的BTREE差不多，隻是還存放了指向主鍵的資訊.

而在MyISAM裡，主鍵和其他的并沒有太大差別。不過和Innodb不太一樣的地方是在MyISAM裡，leaf node裡存放的不是主鍵的資訊，而是指向資料檔案裡的對應資料行的資訊.

RTREE

RTREE在mysql很少使用，僅支援geometry資料類型，支援該類型的存儲引擎隻有MyISAM、BDb、InnoDb、NDb、Archive幾種。

相對于BTREE，RTREE的優勢在于範圍查找.

各種索引的使用情況

1. 對于BTREE這種Mysql預設的索引類型，具有普遍的适用性