MySQL的B+树索引

欢乐海豚

已于 2023-07-25 12:22:15 修改

阅读量282

点赞数

分类专栏： mysql 文章标签： mysql 数据库

于 2023-07-24 23:57:39 首次发布

本文链接：https://blog.csdn.net/m0_37689903/article/details/131883647

版权

mysql 专栏收录该内容

3 篇文章 0 订阅

订阅专栏

索引（Index）是帮助MySQL高效获取数据的数据结构。

1 索引存储的位置

1）MyISAM

在MyISAM储存引擎中，数据和索引文件是分开储存的，Myisam 的存储文件有三个，后缀名分别是 .frm、.MYD、MYI，其中 .frm 是表的定义文件，.MYD 是数据文件，.MYI 是索引文件。

2）InnoDB

在InnoDB中，数据和索引文件是合起来储存的，InnoDB 的存储文件有两个，后缀名分别是 .frm 和 .idb，其中 .frm 是表的定义文件，而 idb 是数据文件。

2 B+树索引原理

有一张students表，表数据如图1所示，其中id为主键

create table students(
id int primary key,
class int,
age int ,
name varchar(20),
key (age)
);

在这里插入图片描述
图1 students数据
InnoDB中，数据结构是采用B+树。索引是以页为单位存储在磁盘中的，每页大小为16kB。students表的主键索引如图2 所示，在页1中，红色部分表示key值，蓝色表示指向下一个页的指针，在B+树的非叶子节点中是不存储数据的，数据只存储在叶子节点中。因为数据量很大时，每个页的大小固定是16KB，这样可以存储更多的键值，从而使整个树的高度变低，在查询数据时，可以减少磁盘IO。B+树中各个页之间是通过双向链表连接的，叶子节点中的数据是通过单向链表连接的。下图为了画图方便，叶子节点每页只画了一个数据，实际肯定远不止1条。

在这里插入图片描述
图2 id主键索引

3 聚簇索引和非聚簇索引

在MySQL中，B+树索引按照存储方式的不同分为聚集索引和非聚集索引。

聚集索引（聚簇索引）：以innodb作为存储引擎的表，表中的数据都会有一个主键，即使你不创建主键，系统也会帮你创建一个隐式的主键。这是因为innodb是把数据存放在B+树中的，而B+树的键值就是主键，在B+树的叶子节点中，存储了表中所有的数据。这种以主键作为B+树索引的键值而构建的B+树索引，我们称之为聚集索引。如上图的students表的主键索引就是聚除索引。
非聚集索引（非聚簇索引）：以主键以外的列值作为键值构建的B+树索引，我们称之为非聚集索引。非聚集索引与聚集索引的区别在于非聚集索引的叶子节点不存储表中的数据，而是存储该列对应的主键，想要查找数据我们还需要根据主键再去聚集索引中进行查找，这个再根据聚集索引查找数据的过程，我们称为回表。

如下面sql语句，因为age为普通索引，非主键索引都是非聚簇索引。在age的索引列表中，叶子节点存储的是age（索引）和主键（id），所以会先查找age=8的id（1和9），然后在主键索引列表中，查询id为1和9对应的值，这个操作称为回表。

select * from student where age = 8

4 最左前缀原则

-- 创建联合索引
create index  idx_class_age on students(class,age);

-- sql查询1
select * from students where class = 2 and age = 9;

-- 删除age索引
drop index age on students;

--  sql查询2
select * from students where age = 9;

建立一个联合索idx_class_age,索引在叶子节点的存储顺序如图3所示，首先会根据class进行排序，然后对于同样值的class再根据age进行排序，当执行sql查询1的时候，会快速定位到第一个class为2的记录，然后继续遍历直到不满足条件为止，接着在class=2的记录中查找age=9的记录，这就是最左前缀原则。删除age索引，执行sal查询2的时候，这个时候由于跳过了联合索引中的class，如下图所示，age列是无序的，这就破坏了索引的有序性，导致不能使用联合索引。

在这里插入图片描述
图3

5 范围之后查询失效原因

select * from class > 1 and age = 1;

在执行上面sql时，如图3所示，由于在class = 1和class = 2 时，age列是无序的，破坏了索引的有序性，所以导致范围查询失效。

6 索引下推

select * from students where class = 2 and age = 9;

上面sql中，在在MySQL 5.6之前，没有索引下推的优化。如图4 无索引下推执行流程，首先会在联合索引inx_class_age列表中查询class=2的记录，然后3条记录依次回表去主键索引中查询满足age=9的记录；在MySQL5.6以后，有了索引下推的优化，执行流程如图5所示，直接会在联合索引inx_class_age列表中查找满足class=2并且age=9的记录，然后再回表去主键列表找到相应id的全部数据，相对于无索引流程省略了回表的次数。
在这里插入图片描述
图4 无索引下推执行流程

在这里插入图片描述
图5 有索引下推执行流程

7 索引结构的选择

为什么索引结构默认使用B-Tree，而不是hash，二叉树，红黑树呢

1）Hash

Hash索引仅能满足（=），(<>)和IN查询。如果进行范围查询，哈希值的索引，时间复杂度会退化为O(n);而树型的“有序”特性，依然能够保持O(log2N)的高效率
Hash索引还有一个缺陷，数据的存储是没有顺序的，在ORDER BY 的情况下，使用Hash索引还需要对数据重新排序
对于联合索引的情况，Hash值是将联合索引合并在一起来计算的，无法对单独的一个键或者几个索引键进行查询
对于等值查询来说，通常Hash索引的效率更高，不过也存在一种情况，就是‘索引列的重复值如果很多；遇到Hash冲突时，需要遍历桶中的行指针来进行比较，找到的关键字，非常耗时。所以Hash索引通常不会用到重复值多的列上，比如列为性别、年龄的情况等