美文网首页
mysql btree和hash索引对比

mysql btree和hash索引对比

作者: 尹楷楷 | 来源:发表于2020-04-04 17:44 被阅读0次

莫等闲、白了少年头,空悲切

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

HASH 索引的优势

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

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

既然hash索引的查找那么高效,为什么 不都使用hash索引?

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

1、只用于使用 =<=> 操作符的等式比较,当然还有in这种范围条件(因为IN可以看做是多个等值比较)。
2、HASH 索引不能用来排序。优化器不能使用 HASH 索引来加速 ORDER BY 操作。
3、HASH 索引不能用来分组。优化器不能使用 HASH 索引来加速 GROUP BY 操作。
4、hash索引计算后的结果,是随机的,如果是在磁盘上安置数据。以主键为id为例,那么随着id的增长,id对应的行在磁盘上随机放置。查的时候虽然快,但是取的话也慢
5、必须回行,就是说通过索引拿到数据位置,必须回表中取数据
6、无法使用前缀索引,hash('helloword')和hash('hello')两者的关系仍为随机

下面我们可以进行验证:

实验准备

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

CREATE TABLE `test`.`Untitled`  (
  `city_id` smallint(5) UNSIGNED NOT NULL AUTO_INCREMENT,
  `city` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `country_id` smallint(5) UNSIGNED NOT NULL,
  `last_update` timestamp(0) NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP(0),
  PRIMARY KEY (`city_id`) USING HASH,
  INDEX `idx_fk_country_id`(`country_id`) USING HASH
) ENGINE = MEMORY AUTO_INCREMENT = 6 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci ROW_FORMAT = Fixed STORAGE MEMORY;

插入数据

INSERT INTO `test`.`city_memory`(`city_id`, `city`, `country_id`, `last_update`) VALUES (1, '广州', 1, '2020-04-04 16:17:52');
INSERT INTO `test`.`city_memory`(`city_id`, `city`, `country_id`, `last_update`) VALUES (2, '深圳', 2, '2020-04-04 16:34:49');
INSERT INTO `test`.`city_memory`(`city_id`, `city`, `country_id`, `last_update`) VALUES (3, '上海', 3, '2020-04-04 16:35:15');
INSERT INTO `test`.`city_memory`(`city_id`, `city`, `country_id`, `last_update`) VALUES (4, '北京', 4, '2020-04-04 16:35:23');
INSERT INTO `test`.`city_memory`(`city_id`, `city`, `country_id`, `last_update`) VALUES (5, '杭州', 5, '2020-04-04 16:35:35');

查询计划分析where条件

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

 explain SELECT * FROM city_memory WHERE country_id =  1
 explain SELECT * FROM city_memory WHERE country_id <=>  1
image.png
可见使用 =<=>这种确定条件是能够用上hash索引的。

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

 explain SELECT * FROM city_memory WHERE country_id  > 1
 explain SELECT * FROM city_memory WHERE country_id  < 1
image.png
对于 > 和 < 这种范围条件不能用到hash索引。而 btree是能用到的,只是说之后的查询条件无法用到索引。证明如下:
 show INDEX FROM city_memory
 explain SELECT * FROM city_memory WHERE country_id  > 1
image.png

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

show INDEX FROM city_memory
explain SELECT * FROM city_memory WHERE country_id in (1,2)
image.png

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


image.png

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


image.png

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


image.png

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

5、like 条件呢?
为了使用like条件,我们先将country_id类型改为 varchar

ALTER TABLE `test`.`city_memory` 
MODIFY COLUMN `country_id` varchar(5) NOT NULL AFTER `city`

我们再来执行:

show INDEX FROM city_memory
explain SELECT * FROM city_memory WHERE country_id  like '1%'
image.png

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

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


image.png
查询计划分析排序和分组

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


image.png

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

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


image.png

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

总结

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

相关文章

  • MySQL面试题 | 附答案解析(四)

    7. 索引算法有哪些? 索引算法有 BTree算法和Hash算法 BTree算法 BTree是最常用的mysql数...

  • mysql btree和hash索引对比

    莫等闲、白了少年头,空悲切 只有 MEMORY 存储引擎的表才可以选择使用 BTREE 索引或者 HASH 索引,...

  • Mysql索引与锁

    本文以Mysql5.7为例测试。 1:mysql索引方法 Mysql的索引方法分为btree索引和hash索引。 ...

  • 索引

    MySQL索引原理及慢查询优化 索引的储存分类: BTREE索引和HASH索引。MyISAM 和 InnoDB 存...

  • MySQL索引

    一、 MySQL: 索引以B树格式保存 Memory存储引擎可以选择Hash或BTree索引,Hash索引只能用于...

  • 索引

    1、mysql索引类型:normal、unique、full text 索引方式:btree、hash等等 2、什...

  • MySQL的数据库索引优化

    1.Btree索引和Hash索引 MySQL支持的索引类型: B-tree索引的特点: B-tree索引以B+树的...

  • MySQL 索引类型

    MySQL目前主要有以下几种索引类型:FULLTEXT,HASH,BTREE,RTREE。 一、FULLTEXT ...

  • Hash索引和BTree索引区别

    Hash仅支持=、>、>=、<、<=、between。BTree可以支持like模糊查询 索引是帮助mysql获取...

  • 8 mysql Hash索引和BTree索引区别

    Hash仅支持=、>、>=、<、<=、between。BTree可以支持like模糊查询 索引是帮助mysql获取...

网友评论

      本文标题:mysql btree和hash索引对比

      本文链接:https://www.haomeiwen.com/subject/euujphtx.html