关于MySQL B+树索引与哈希索引详解

2024-04-02 19:04:59 179人浏览薄情痞子

摘要

目录索引介绍B+树索引优点缺点哈希索引优点缺点补充：二者区别总结索引介绍索引是一种特殊的数据库结构，被设计用来快速查询数据库表中的特定记录。索引有多种类型，就像字典有拼

索引介绍

索引是一种特殊的数据库结构，被设计用来快速查询数据库表中的特定记录。索引有多种类型，就像字典有拼音查找和偏旁查找一样都是为了提高检索效率。Mysql中最常见的索引类型有B+树索引 和 哈希索引，下面来简单介绍一下这两种索引类型有哪些差别和优劣。

B+树索引

B+树索引是一种多路径的平衡搜索树，具有如下特点：

1.非叶子节点不保存数据，只保存索引值
2.叶子节点保存所有的索引值和数据
3.同级节点通过指针自小而大顺序链接
4.节点内的数据也是自小而大顺序存放
5.叶子节点拥有父节点的所有信息

结构如下图：

优点

由于数据顺序存放，所以无论是区间还是顺序扫描都更快。
非叶子节点不存储数据，因此几乎都能放在内存中，搜索效率更高
单节点中可存储的数据更多，平均扫描I/O请求树更少
平均查询效率稳定（每次查询都从根结点到叶子结点，查询路径长度相同）

缺点

新增数据不是按顺序递增时，索引树需要重新排列，容易造成碎片和页分裂情况。

哈希索引

哈希索引采用一定的哈希算法，把键值换算成新的哈希值，检索时不需要类似B+树那样从根节点到叶子节点逐级查找，只需一次哈希算法即可立刻定位到相应的位置，速度非常快，具有如下特点：

1.哈希索引建立在哈希表的基础上
2.对于每个值，需要先计算出对应的哈希码（Hash Code），不同值的哈希码唯一
3.把哈希码保存在哈希表中，同时哈希表也保存指向对应每行记录的指针

结构如下图：

优点

大量唯一等值查询时，哈希索引效率通常更高。

缺点

哈希索引对于范围查询和模糊匹配查询显得无能为力。
哈希索引不支持排序操作，对于多列联合索引的最左匹配规则也不支持。
哈希索引不支持前缀索引，因为哈希索引始终是使用索引列的全部内容来计算哈希值。
如果存在哈希冲突的情况，也就是不同的索引列有着相同的哈希值，这时候需要遍历链表中所有的行指针进行逐行比对，直到找到所有满足条件的行，效率较低。

补充：二者区别

备注：先说下，在MySQL文档里，实际上是把B+树索引写成了BTREE，例如像下面这样的写法：

CREATE TABLE t(
aid int unsigned not null auto_increment,
userid int unsigned not null default 0,
username varchar(20) not null default ‘',
detail varchar(255) not null default ‘',
primary key(aid),
unique key(uid) USING BTREE,
key (username(12)) USING BTREE — 此处 uname 列只创建了最左12个字符长度的部分索引
)engine=InnoDB;

B+树是一个平衡的多叉树，从根节点到每个叶子节点的高度差值不超过1，而且同层级的节点间有指针相互链接。

在B+树上的常规检索，从根节点到叶子节点的搜索效率基本相当，不会出现大幅波动，而且基于索引的顺序扫描时，也可以利用双向指针快速左右移动，效率非常高。

因此，B+树索引被广泛应用于数据库、文件系统等场景。顺便说一下，xfs文件系统比ext3/ext4效率高很多的原因之一就是，它的文件及目录索引结构全部采用B+树索引，而ext3/ext4的文件目录结构则采用Linked list, hashed B-tree、Extents/Bitmap等索引数据结构，因此在高I/O压力下，其ioPS能力不如xfs。

简单地说，哈希索引就是采用一定的哈希算法，把键值换算成新的哈希值，检索时不需要类似B+树那样从根节点到叶子节点逐级查找，只需一次哈希算法即可立刻定位到相应的位置，速度非常快。

从上面的图来看，B+树索引和哈希索引的明显区别是：

如果是等值查询，那么哈希索引明显有绝对优势，因为只需要经过一次算法即可找到相应的键值；当然了，这个前提是，键值都是唯一的。如果键值不是唯一的，就需要先找到该键所在位置，然后再根据链表往后扫描，直到找到相应的数据；
从示意图中也能看到，如果是范围查询检索，这时候哈希索引就毫无用武之地了，因为原先是有序的键值，经过哈希算法后，有可能变成不连续的了，就没办法再利用索引完成范围查询检索；
同理，哈希索引也没办法利用索引完成排序，以及like ‘xxx%’ 这样的部分模糊查询（这种部分模糊查询，其实本质上也是范围查询）；
哈希索引也不支持多列联合索引的最左匹配规则；
B+树索引的关键字检索效率比较平均，不像B树那样波动幅度大，在有大量重复键值情况下，哈希索引的效率也是极低的，因为存在所谓的哈希碰撞问题。