深入理解MySQL索引底层数据结构与算法

不帅的小齐

已于 2023-07-20 22:40:04 修改

阅读量84

点赞数 1

文章标签： mysql 数据库

于 2023-07-19 09:04:47 首次发布

本文链接：https://blog.csdn.net/qq_38104145/article/details/131798558

版权

1.1 B-Tree树

首先我们先理解下什么是B树。

B树是一种自平衡的树，常用于文件系统和数据库系统中。B树的主要目标是保证树的深度尽可能小，以减少磁盘I/O操作。

B树的主要特性如下：

（1）每个节点有多个孩子，孩子的数量介于预定的最小值和最大值之间。

（2）除了根节点外，所有节点的孩子数量至少是最大值的一半（向上取整）。根节点的孩子数量可能少于这个值，但至少是两个，除非整棵树只有一个节点。

（3）所有叶子节点都在同一层，也就是说，从根节点到每个叶子节点的距离都是相等的。

 叶节点具有相同的深度，叶节点的指针为空

 所有索引元素不重复

 节点中的数据索引从左到右递增排列

1.2 B+Tree树

B+Tree是B-Tree的变种，主要有以下的特点：

（1）非叶子节点不存储data，只存储索引(通常为主键)，可以放更多的索引

（2）叶子节点包含所有索引字段

（3）叶子节点用指针连接，提高区间访问的性能

1.3 MySQL的B+Tree树存储

在mysql中，底层数据便是采用B+Tree树进行数据的存储，那么使用它的优点是什么呢？

在计算机中，磁盘的I/O加载到内存中相对来说是耗费时间的，树的深度每加1，就会执行一次磁盘的I/O操作。

在mysql中，给B+Tree的每个树节点设置的内存大小是16KB，假设mysql的某一张表采用bitInt做主键且设置了索引,每个bitInt占用8个字节（Bit)，加上下一个子节点在磁盘中的文件地址,在C语言的底层中，是占用6个字节(Bit)，一共14Bit字节，这样计算可得出一个节点可以存储16KB/14Bit=1170个索引元素，第二层非叶子节点的每个节点也可以存储1170个索引元素，直到第三层的叶子节点，每个节点除了存储索引的8Bit外，还可能存储的是该索引对应的数据地址，也有可能存储的是该索引对应的所有数据（也就是表中某一行的数据），具体根据mysql的引擎来计算，按照最大可能1KB来算，每个叶子节点存储16条数据。那么此时从一个根节点到所有叶子节点的深度为3的B+Tree树来算1170117016=21902400条，也就2100万条数据，也就是说三次的I/O磁盘操作便可以处理2100万条数据，对于查询来说是相当快的。

1.4 MyISAM索引引擎和InnoDB索引引擎

1.4.1 MyISAM索引引擎

mysql在每次创建表时，会指定一个索引引擎，之前常用的是MyISAM引擎，他是非聚合索引引擎，也就是说索引文件和数据文件是分离的。如下图所示，到第三层的叶子节点时，15、18等时索引值，对应的0x07和0x56是该索引对应的该一条数据的文件地址。后续搜索引擎会根据该文件地址去另一个文件查找该条数据。

1.4.2 InnoDB索引引擎

InnoDB索引是聚集性的索引引擎，特点是表数据文件本身就是按B+Tree组织的一个索引结构文件且叶节点包含了完整的数据记录，如下图，这也是为什么在介绍B+Tree树时说第三层的叶子节点存放的大小和索引引擎有关系，像InnoDB叶子节点会存储索引对应的该条所有数据，因此按照每个节点1KB计算。

1.4.3 若索引字段不是主键

在InnoDB索引引擎中，如果索引字段不是主键，则变换为形式类似于非聚集的索引引擎，如下图所示，非主键索引的叶子节点存储的数据不是该行的所有数据了，而是该行数据的主键索引，之后引擎会通过主键索引查询到该索引值对应的所有数据。

1.4.4 关于InnoDB索引引擎的几个问题

（1）为什么建议InnoDB表必须建主键？

因为是想告诉InnoDB利用某个不重复的字段值确定B+Tree树的根节点，如果没有建立主键，则他会寻找一个没有重复的字段来确定根节点索引值，如果没有找到，则会在内部自己生成一个字段用来做根节点索引值。

（2）为什么主键推荐用Int整型？

因为整型在遍历树中的较索引大小时更加容易，更加迅速方便，而我们日常开发中会用UUID做为表的主键和唯一索引，UUID在遍历树中的索引值比较大小时用的ASCII码比较，这样挨个比较相对来说是比较消耗性能的，因此最好还是用Int整型做主键比较好，不过如今大多硬件设施不错，往往忽视这方面的设置。

（3）使用整型的主键，为什么推荐使用自增？

首先我们先按照1，2，3，4的顺序插入一个节点最大元素数为3的B+Tree数，下面图中展示的是未插入5，6，7之前的结构图：

下面我们再来看下如果按照自增的顺序插入B+Tree，之后的树结构如下,可以看出一共有4个节点，根节点2个元素：

下面我们来看下如果在4之后，没有按照递增顺序，而是按照7，5，6的顺序插入树中的结果如下图所示，我们可以看出，一共会有5个节点，比正常循序多一个节点，且根节点3个元素：

故设置递增的好处便是会更大限度的利用节点的空间，减少树索引结构的冗余。

1.5 Hash索引

我们在创建数据表的索引时，除了可选择BTree索引外，还可以选择Hash索引，如下图所示，在创建表时可供选择

如下图所示，假如我们给name加上Hash索引，mysql会将存储的name值进行hash运算时，放入一个链表中（0~6）对应的位置，若此时查找name=‘Alice’的数据，拿到0x56的数据地址后，便可将该name值对应的一行数据查找出来，如果在精确查找时，有时一次的I/O磁盘操作便可以查找到我们所需要的，时间更快。

那么问题来了，为什么我们在日常的研发中不用Hash索引而用BTree呢？

因为采用Hash索引有下面两个不足的问题，主要是第一个：

（1）仅能满足 “=”，“IN”，不支持范围查询。

（2）hash冲突问题

我们重点来描述下第一个问题：

Hash索引归根是一个无序的链表，如果想查找一个具体精确的值，是非常快的，如果想查询name>'Jim'的所有数据，Hash便不能使用，失去了作用，只能去一个一个的遍历表中数据的内容挨个比较。但是在我们的日常使用中，范围查询是经常遇到的，那为什么B+Tree结构没有这个问题或者说他的优化比Hash索引要好呢？因为B+Tree结构的索引值在树中都是事先排好顺序的，范围是确定的。如下图所示，如果我想查找name>'Jim'的，只需要找到name='Jim'，之后把后面的索引对应的数据不用对比全部捞出来即可。

1.6 联合索引

1.6.1 什么是联合索引

联合索引就是将一张表中的多个字段组合在一起共同设置一个索引，如下图所示我们将name,id和position三个字段共同确定一个索引项。

1.6.2 联合索引在B+Tree树中是如何排序的

BTree树索引的排序遵循我们在创建索引时的先后字段顺序，例如我们创建索引时是name、age、position的排列顺序，那么他在比较大小时，先会按照name字段进行排序，如果遇到name字段的值相同，再去按照age字段的值排序，以此类推。

1.6.3 哪些查询会走联合索引

下面会有三条查询语句,其中第一条会遵循联合索引查询，第二条和第三条不会走索引查询。

explain select * from employee where name='Bill' and age=31;

explain select * from employee where age=30 and position='dev';

explain select * from employee where position='manager;

为什么第一条可以，其余的不行呢？因为联合索引遵循最左前缀原则，即要想使用联合索引，在查询时必须使用第一个查询字段，即name。那么为什么要遵循最左前缀原则？因为上面讲过BTree是排好序的，如果是联合索引，则首先排序第一个字段，观察上图可以看出，如果只查询age或者age+position,那么在查询时会比较age的大小，而上图中的age有可能时无序的，因此无法走索引。

创作不易，好心人留个小攒攒吧！！！！！