8.3.8 比较B树索引和Hash索引

原文:https://dev.mysql.com/doc/refman/5.7/en/index-btree-hash.html

8.3.8 Comparison of B-Tree and Hash Indexes

理解B-tree和hash的数据结构，有助于理解不同的引擎下查询语句是如何在索引上工作的,尤其是同时支持B-tree和hash的MEMORY存储引擎.

B-Tree Index Characteristics

B-tree索引可以使用=, >, >=, <, <=, 或 BETWEEN 操作符。如果不是以通配符开头的且值是常量的LIKE查询，也可以使用B-tree。比如，下面的语句会使用索引：

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 * 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 IS NULL的语句如果col_name列有索引,可以使用索引.

如果一个index没有在所有的and级别使用,此sql将不能使用到该索引.换句话说,一个索引可以被使用,该索引的前缀必须出现在每一个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即使有索引可用时也不使用索引,一种情况是优化器评估使用索引需要访问大量的行(以占表总行数的百分比评估),在这种情况下,较少的寻址会提高查询速度.如果查询语句使用limit,MySQL一定会使用索引,因为这样会更快查询到少量的数据.

Hash Index Characteristics

Hash索引有些不同，具体如下：

• 只能使用= or <=>操作符比较,查找速度是飞快的.不能使用类似于”<”操作符查询某一范围的数据.系统使用键值对存储结构实现这种单值(single-value)查询;在任何能够使用hash索引的地方请尽量使用.

• 对使用了 ORDER BY的查询语句,优化器无法使用哈希索引.

• MySQL不能在哈希索引上评估两个值之间大约数据量.

• 只能按整个字段查询,不能像B-tree一样前缀匹配.