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