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 索引。不论什么该键的左前缀都可用以查找记录。

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