BTREE這種Mysql預設的索引方式，具有普遍的適用性

標籤：函數   分割   hash表   使用方式   索引值對   完成   where   document   sql   

文章轉自 52145477

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預設的索引方式，具有普遍的適用性

（2）由於FULLTEXT對中文支援不是很好，在沒有外掛程式的情況下，最好不要使用。其實，一些小的部落格應用，只需要在資料擷取時，為其建立關鍵字列表，通過關鍵字索引，也是一個不錯的方法，至少我是經常這麼做的。

（3）對於一些搜尋引擎層級的應用來說，FULLTEXT同樣不是一個好的處理方法，Mysql的全文索引建立的檔案還是比較大的，而且效率不是很高，即便是使用了中文分詞外掛程式，對中文分詞支援也只是一般。真要碰到這種問題，Apache的Lucene或許是你的選擇。

（4）正是因為hash表在處理較小資料量時具有無可比擬的素的優勢，所以hash索引很適合做緩衝（記憶體資料庫）。如mysql資料庫的記憶體版本Memsql，使用量很廣泛的緩衝工具Mencached，NoSql資料庫redis等，都使用了hash索引這種形式。當然，不想學習這些東西的話Mysql的MEMORY引擎也是可以滿足這種需求的。

BTREE這種Mysql預設的索引方式，具有普遍的適用性