MySQL的btree索引和hash索引的区别

Hash

索引检索效率非常高，索引的检索可以一次定位，不像B-Tree

索引需要从根节点到枝节点，最后才能访问到页节点这样多次的IO访问，所以

Hash

索引的查询效率要远高于

B-Tree

索引。但是

Hash

索引本身由于其特殊性也带来了很多限制和弊端，主要有以下这些：

（1）Hash

索引仅仅能满足"=","IN"和"<=>"查询，不能使用范围查询。

由于

Hash

索引比较的是进行

Hash

运算之后的

Hash

值，所以它只能用于等值的过滤，不能用于基于范围的过滤，因为经过相应的

Hash

算法处理之后的

Hash

值的大小关系，并不能保证和Hash运算前完全一样。

（2）Hash

索引无法被用来避免数据的排序 *** 作。

由于

Hash

索引中存放的是经过

Hash

计算之后的

Hash

值，而且Hash值的大小关系并不一定和

Hash

运算前的键值完全一样，所以数据库无法利用索引的数据来避免任何排序运算；

（3）Hash

索引不能利用部分索引键查询。

对于组合索引，Hash

索引在计算

Hash

值的时候是组合索引键合并后再一起计算

Hash

值，而不是单独计算

Hash

值，所以通过组合索引的前面一个或几个索引键进行查询的时候，Hash

索引也无法被利用。

（4）Hash

索引在任何时候都不能避免表扫描。

前面已经知道，Hash

索引是将索引键通过

Hash

运算之后，将

Hash运算结果的

Hash

值和所对应的行指针信息存放于一个

Hash

表中，由于不同索引键存在相同

Hash

值，所以即使取满足某个

Hash

键值的数据的记录条数，也无法从

Hash

索引中直接完成查询，还是要通过访问表中的实际数据进行相应的比较，并得到相应的结果。

（5）Hash

索引遇到大量Hash值相等的情况后性能并不一定就会比B-Tree索引高。

对于选择性比较低的索引键，如果创建

Hash

索引，那么将会存在大量记录指针信息存于同一个

Hash

值相关联。这样要定位某一条记录时就会非常麻烦，会浪费多次表数据的访问，而造成整体性能低下

只有 MEMORY 存储引擎的表才可以选择使用 BTREE 索引或者 HASH 索引，像我们 常用的innodb只支持btree索引 。两种不同类型的索引各有其不同的适用范围。

Hash索引只能用于对等比较，例如=,<=>（相当于=） *** 作符。时间复杂度是O(1)，一次查找便能定位数据，不像BTree索引需要从根节点到枝节点，最后才能访问到页节点这样多次IO访问，所以Hash在 单值查询 下检索效率远高于BTree索引。

但是，事实上我们更多情况是使用btree而不是hash

HASH 索引有一些重要的特征需要在使用的时候特别注意，如下所示。

下面我们可以进行验证：

创建一个city_memory表，其中 country_id字段上加了 HASH索引

插入数据

1、先开看这条等值条件sql

2、那么再来看 大于和小于条件sql

3、那么in这种范围条件呢？

in 条件对于hash来说是支持的，同样btree当然也支持。而且btree索引在使用in条件找数据时相对于hash性能更好，因为rows由4变为2（说明使用btree扫描2行即可找到）证明如下：

4、 BETWEEN .. AND .. 条件呢？

BETWEEN .. AND .. 条件在 不会用到hash索引！再来看看 btree的情况：

BETWEEN .. AND .. 条件能够使用到btree索引。

5、like 条件呢？

为了使用like条件，我们先将country_id类型改为 varchar

我们再来执行：

like条件会让hash索引失效。我们再来看btree下的like怎样：

好的，btree下也支持 like的不带开头%的访问查询

1、先来看hash索引支不支持排序

hash索引果然不能用在排序中，这多么致命呀！产生了 Using filesort文件内排序。性能上是个大坑。

2、同样，我们知道分组是要基于排序的。排序不使用索引，分组当然也不使用索引了。验证如下：

最终不仅没使用到索引，还产生了文件内排序和使用临时表。

当使用 MEMORY 引擎表的时候，如果是默认创建的 HASH索引，就要注意 SQL 语句的编写，确保可以使用上索引，如果索引字段需要 范围查询、排序、分组 就请使用btree索引；

1.添加PRIMARY KEY（主键索引） mysqlALTER TABLE `table_name` ADD PRIMARY KEY ( `column` ) 2.添加UNIQUE(唯一索引) mysqlALTER TABLE `table_name` ADD UNIQUE ( `column` ) 3.添加INDEX(普通索引) mysqlALTER TABLE `table_name` ADD INDEX index_name ( `column` ) 4.添加FULLTEXT(全文索引) mysqlALTER TABLE `table_name` ADD FULLTEXT ( `column`) 5.添加多列索引 mysqlALTER TABLE `table_name` ADD INDEX index_name ( `column1`, `column2`, `column3` )

---