【MySQL精通之路】优化-B树索引与哈希索引

了解B树和Hash散列数据结构有助于预测不同查询在索引中使用这些数据结构的不同存储引擎上的执行情况，特别是对于允许您选择B树或散列索引的MEMORY存储引擎。

1.B+树索引特征

B树索引可用于使用=、>、>=、<、<=或BETWEEN运算符的表达式中的列比较。

如果LIKE的参数是不以通配符开头的常量字符串（最左前缀匹配），则索引也可用于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%"，字符串长度超过三个字符，MySQL使用Turbo Boyer-Moore算法初始化字符串的模式，然后使用此模式更快地执行搜索。

如果对col_name进行了索引，则使用col_name IS NULL的搜索将使用索引。

博主PS：

这是经常遇见的面试题：加了索引后，IS NULL还会走索引么？

所以官网文档在这里做了回答。会！

任何不跨越WHERE子句中所有AND级别的索引都不会用于优化查询。换句话说，为了能够使用索引，必须在每个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无论如何都会使用索引，因为它可以更快地找到结果中返回的几行。

2.哈希索引特征

哈希索引的特性与刚才讨论的有些不同：

它们仅用于使用=或<=>运算符的相等性比较（但速度非常快）。它们不用于查找值范围的比较运算符，如<。依赖于这种类型的单值查找的系统被称为“键值存储”；

要将MySQL用于此类应用程序，请尽可能使用哈希索引。

优化器不能使用哈希索引来加快ORDER BY操作的速度。

（这种类型的索引不能用于按顺序搜索下一个条目。）

MySQL无法大致确定两个值之间有多少行（这是范围优化器用来决定使用哪个索引的）。

如果将MyISAM或InnoDB表更改为哈希索引的MEMORY表，这可能会影响某些查询。

使用键的所有内容才能用于搜索一行。（对于B-树索引，键的任何最左边的前缀内容都可以用来查找行。）

博主PS：

意思Hash索引必须用到完整的key，但是B+树可以只用一部分。

此部分来自官网的内容，对于B+索引优化的内容不完善，后期博主再补充。