B-Tree索引和Hash索引的對比

對於 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 索引，任何該鍵的左前綴都可用以查找記錄。