MySQL-B-Tree和哈希索引比较_mysqlb-tree和哈希索引-CSDN博客

本文链接：https://blog.csdn.net/fofcn/article/details/123006450

概述

先来澄清一下：InnoDB从来都不支持用户创建哈希索引，而是通过自适应哈希索引来优化，但是自适应哈希索引用户无法显示的指定。
理解B-Tree和哈希索引数据结构有助于预测不同的查询在不同的存储引擎如何使用这些索引来执行查询，尤其是MEMORY存储引擎可以让你选择B-tree或哈希索引。

B-Tree索引特性
哈希索引特性

B-Tree索引特性

B-Tree可以使用的操作符有：=，>,>=,<,<=,BETWEEN。如果LIKE的参数不以通配符开始，B-Tree索引也可以用于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的值不是常量。

如果col_name有索引那么col_name IS NULL会使用索引。

下面的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;

Any index that does not span all AND levels in the WHERE clause is not used to optimize the query.换句话说，为了使查询能够使用索引，那么索引的前缀必须用到每一个AND分组。
下面的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会尽可能使用索引，因为这样可以更快的查找到记录并返回结果。

哈希索引特性

哈希索引和B-tree有一些不同的特性：

哈希索引只能用于=或<=>操作符（但是很快）。哈希索引无法用于其他比较操作符，比如：返回查询中的<符号。依赖于这种单值查找类型的系统被称为“键-值存储”; 如果使用MySQL是为了键值存储，那么尽可能使用哈希索引。
优化器无法使用哈希索引来加速ORDER BY操作。（哈希索引无法顺序查找下一个实体）
MySQL无法使用哈希索引来估计两个值之间有多少行数据（这是范围优化器来确定使用哪个索引）。如果你修改了MyISAM或InnoDB表为哈希索引的MEMORY表，可能会影响一些查询。
哈希索引只能用于全关键字来搜索行（使用B-tree索引可以最左前缀匹配关键字）