B-Tree 索引和 Hash 索引的對比，b-treehash

B-Tree 索引和 Hash 索引的對比，b-treehash

對於 B-tree 和 hash 資料結構的理解能夠有助於預測不同儲存引擎下使用不同索引的查詢效能的差異，尤其是那些允許你選擇 B-tree 或者 hash 索引的記憶體儲存引擎。

B-Tree 索引的特點B-tree 索引可以用於使用 =, >, >=, <, <= 或者 BETWEEN 運算子的列比較。如果 LIKE 的參數是一個沒有以萬用字元起始的常量字串的話也可以使用這種索引。比如，以下 SELECT 語句就使用索引：

SELECT * FROM tbl_name WHERE key_col LIKE 'Patrick%';SELECT * FROM tbl_name WHERE key_col LIKE 'Pat%_ck%';

在第一個句子中，只會考慮 'Patrick' <= key_col < 'Patricl' 的記錄。第二句中，則只會考慮 'Pat' <= key_col < 'Pau' 的記錄。
以下 SELECT 語句不使用索引：

SELECT * FROM tbl_name WHERE key_col LIKE '%Patrick%';SELECT * FROM tbl_name WHERE key_col LIKE other_col;

第一句裡面，LIKE 的值起始於一個萬用字元。在第二句裡，LIKE 的值不是一個常量。
如果你這樣使用： ... LIKE '%string%'，其中的 string 不大於三個字元，MySql 將使用 Turbo Boyer-Moore 演算法來對該字串運算式進行初始化，並使用這種運算式來讓查詢更加迅速。
如果 col_name 列建立了索引，那麼一個使用了 col_name IS NULL 的查詢是可以使用該索引的。
任何沒有涵蓋 WHERE 從句中所有 AND 層級的條件的索引將不會被使用。換句話講，要想使用索引，該索引的前置列必須在每一個 AND 組合中使用到。
以下 WHERE 從句使用索引：

... WHERE index_part1=1 AND index_part2=2 AND other_column=3    /* index = 1 OR index = 2 */... WHERE index=1 OR A=10 AND index=2    /* optimized like "index_part1='hello'" */... WHERE index_part1='hello' AND index_part3=5    /* Can use index on index1 but not on index2 or index3 */... WHERE index1=1 AND index2=2 OR index1=3 AND index3=3;

這些 WHERE 從句不使用索引：

    /* index_part1 is not used */... WHERE index_part2=1 AND index_part3=2    /*  Index is not used in both parts of the WHERE clause  */... WHERE index=1 OR A=10    /* No index spans all rows  */... WHERE index_part1=1 OR index_part2=10

有時，即使有索引可以使用，MySql 也不使用任何索引。發生這種情況的情境之一就是最佳化器估算出使用該索引將要求 MySql 去訪問這張表的絕大部分記錄。這種情況下，一個表掃描可能更快，因為它要求更少量的查詢。但是，如果這樣的一個查詢使用了 LIMIT 來檢索只是少量的記錄時，MySql 還是會使用索引，因為它能夠更快地找到這點記錄並將其返回。

Hash 索引的特點Hash 索引有著與剛才所討論特點的相比截然不同的特點：

• Hash 索引只能夠用於使用 = 或者 <=> 運算子的相等比較(但是速度更快)。Hash 索引不能夠用於諸如 < 等用於尋找一個範圍值的比較子。依賴於這種單值尋找的系統被稱為 "鍵-值儲存"；對於這種系統，儘可能地使用 hash 索引。
• 最佳化器不能夠使用 hash 索引來加速 ORDER BY 操作。這種類型的索引不能夠用於按照順序尋找下一個條目。
• MySql 無法使用 hash 索引估計兩個值之間有多少行(這種情況由範圍最佳化器來決定使用哪個索引)。如果你將一張 MyISAM 或 InnoDB 錶轉換成一個 hash 索引的記憶體表時，一些查詢可能會受此影響。
• 尋找某行記錄必須進行全鍵匹配。而 B-tree 索引，任何該鍵的左首碼都可用以尋找記錄。

原文連結：http://dev.mysql.com/doc/refman/5.5/en/index-btree-hash.html。