【MySQL】B-Tree索引和Hash索引比较

了解B-Tree索引和Hash索引的数据结构有助于预测不同查询在不同的存储引擎的执行情况，特别是对于允许您选择B-Tree索引或Hash索引的Memory存储引擎。

B-Tree索引的特点

B-Tree索引可用于使用=, >, >=, <, <=,或者 BETWEEN 等运算符的表达式中的列比较。并且在like表达是开头不是通配符时，也可以用于like比较。比如下面的查询就用到了索引

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

在第一个查询中like条件相当于**‘Patrick’ <= key_col < ‘Patricl’，在第二个查询中like条件相当于’Pat’ <= key_col < ‘Pau’**
下面的查询就不能使用索引

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

第一个查询中的like条件是通配符开头的，第二个查询中的like条件是一个变量。所以不能使用索引。
当使用**like ‘%String%’**这样的查询条件时，如果String的个长度大于3，MySQL会使用Turbo Boyer-Moore 算法来初始化字符串的模式，然后用此模式来更快地执行查询。
当查询使用 col_name IS NULL这样的查询条件，当col_name被索引的时候也是可以使用索引的。
当使用OR拼接查询条件时，索引必须在or的各个部分中都存在才可以用来优化查询。
例如下面的查询就用到了索引

select * from table WHERE index_part1=1 AND index_part2=2 AND other_column=3
    /* 使用索引 index = 1 OR index = 2 */
select * from table WHERE index=1 OR A=10 AND index=2
    /* 使用索引index "index_part1='hello'" */
select * from table WHERE index_part1='hello' AND index_part3=5
    /* 可以使用index1但是不能使用index2或者index3 */
select * from table WHERE index1=1 AND index2=2 OR index1=3 AND index3=3;

下面的查询不能使用索引

/* 不符合最左前缀原则 */
select * from table WHERE index_part2=1 AND index_part3=2
/* 索引不是在or的每一部分都存在 */
select * from table WHERE index=1 OR A=10
/* 没有完整的索引  */
select * from table  WHERE index_part1=1 OR index_part2=10

有些时候MySQL并不会使用索引，即使有索引是可用的。发生这种情况的一种情况是，优化器估计使用索引将需要MySQL访问表中非常大比例的行。（在这种情况下，表扫描要更快因为它需要较少的搜索。）但是如果这样的查询使用LIMIT来仅检索某些行，那么MySQL一定会使用索引，因为这样可以更快的在结果中找到需要返回的行。

Hash索引特点

Hash索引与上面讨论的有以下几点不同

• 仅用于使用=或者<=>运算符的等值比较（但是速度非常快）。不能用于像**>**这样的范围查询。依赖于这种类型的单值查找的系统被称为“键值存储”; 要将MySQL用于此类应用程序，请尽可能使用哈希索引。
• 优化器不能使用Hash索引来加速order by 操作。(此类索引不能用于按顺序搜索下一个条目)
• MySQL无法确定两个值之间有多少行（查询优化器用此数据来决定使用哪种索引）。如果将MyISAM或InnoDB表更改为哈希索引的MEMORY表，则可能会影响某些查询。
• 只有整个键可以用来搜索行。（B-Tree索引任何最左边的前缀都可以用来搜索行）