MySQL索引和B+Tree底层原理

Blue Protocol

已于 2023-01-17 18:26:28 修改

阅读量574

点赞数 1

分类专栏： MySQL 文章标签：数据库 MySQL 索引

于 2022-10-13 21:01:36 首次发布

本文链接：https://blog.csdn.net/weixin_51921447/article/details/127307511

版权

MySQL 专栏收录该内容

13 篇文章 0 订阅

订阅专栏

说明

说到MySQL数据库，我们一定会说到MySQL的获取数据的高效性，那为什么MySQL在查询的时候它为什么会这么快呢？原因是存在索引。

一、索引的引入

在MySQL中，数据最终是存储在硬盘或者是磁盘当中的。
当我们使用

select * from table where col1 = 9

这个过程会把我们table表中的一条条记录从磁盘中取出来，然后放到内存中，供我们的CPU进行调用，而一条记录从CPU中取出来，放到内存中，供我们CPU进行调用这一个过程就是我们通常所说的一次 IO操作，在这里，我们磁盘的速度是最慢的，如果我们发生的 IO 操作过多，与磁盘打交道的次数过多，那么我们的查询速度就会变得很慢。

二、MySQL为什么使用B+树，而不是使用B树，AVL？

为了去更好得理解这个问题，我们得先从最基础的一个结构二叉搜索树讲起。

二叉搜索树

什么是二叉搜索树，二叉搜索树又称为二叉排序树，它或者是一颗空树，或者是若左右子树都不为空，左子树上所有的节点的值都小于根节点，右子树上所有节点的值都大于根节点的值。它的左右子树也分别是二叉搜索树。

我们先来画一个二叉索引树
分别插入5，4，6，2，1，3，8，7，9
如图：

在这里插入图片描述

当我们先不考虑这数据结构，我们执行SQL

select * from table where col1 = 9

按照我们遍历的做法，按照我们依次寻找的做法，我们需要查询9次才能返回我们想要的一个结果，需要进行9次IO操作，这个时候就很慢。

那此时，假如我们对这个表添加一个二叉搜索树的索引
此时我们从重新根据索引执行我们的SQL，那么我们就只需要查询4次就能获取到我们想要的结果，只进行了4次IO操作。

在这里插入图片描述

但是也没有任何数据库使用二叉搜索树来当他们的数据结构。 因为二叉搜索树还会出现这样的情况：
我们插入数据1,2,3,4,5,6,7,8,9。这样会出现单边树链表的数据特点。此时，查找的时候一定会采用遍历做法，和我们没有使用任何的数据就没有任何区别了，而我们建立索引的目的是帮助MySQL高效获取数据，而我们建立这种数据结构，我们还要开辟空间去进行存储，这个时候，我们既浪费了资源，也没有得到加速效果，所以说，二叉搜索树不可用。而之后又衍生出来一种AVL，AVL平衡二叉搜索树。

在这里插入图片描述

AVL 平衡二叉搜索树

AVL 平衡二叉搜索树要求我们当前平衡二叉搜索树左子树和右子树之间的深度之差的绝对值不超过1，如果超过1，就会触发左旋或者右旋（扭动一下它的小蛮腰），左旋和右旋在AVL里面说白了就是为了维护平衡二叉树相对平衡的概念，通过某个节点来更改我的父子结构，保证高度差不大于1

此时，我们还是通过col1去建立索引，同样通过col1去查询数据，

select * from table where col1 = 9

按照我们遍历的做法，按照我们依次寻找的做法，我们需要查询9次才能返回我们想要的一个结果，需要进行9次IO操作，我们使用二叉搜索树的时候，也要遍历9次。而当我们引入平衡二叉树，引入了左旋和右旋来满足高度之差不大于1之后，我们去查询9这个数据，根据平衡二叉搜索树的结构，我们只需要查询4次。

在这里插入图片描述

但是在平常，也很少用AVL来作为数据存储的结构。
因为
不管是二叉搜索树还是平衡二叉搜索树本质还是二叉树，而二叉树的特点是，一个节点最多分裂出来两个子节点，当我们的数据量不断增加，我们的二叉搜索树或者是平衡二叉树一定会纵向发展，而在平衡二叉搜索树中我们想要查找数据，在树形结构中一定是跟当前数据所在的高度或者是层数有关，而随着我们的数据量不断增加，树的高度也越来越大，如果我们查询的第100层第200层的数据，那么我们查100次？200次？而回到我们之前所说我们磁盘的速度是最慢的，如果我们发生的 IO 操作过多，与磁盘打交道的次数过多，那么我们的查询速度就会变得很慢。

其次，我们从磁盘中拿取数据，加载到内存中。我们会有基本代为，InnoDB的一页是16Kb。而我们一条记录的内容占据的数据量差不多是10几byte，而InnoDB每次去磁盘拿数据的时候，是以页为单位（一页是16Kb），所以在这么大的空间中真正有用的数据只占一点点，这就造成了IO浪费我们本来与磁盘打交道的次数又多，而我们的IO浪费又大。

所以AVL平衡二叉树不适合作为我们MySQL索引的数据结构。

总结一下AVL：
1、平衡二叉树本质还是二叉树，随着数据量增加，会纵向发展，而高度会越来越大，在树形结构中我们查找数据与树的高度有关，会导致我们查询数据过多，查询数据过多IO次数也会过多，IO次数过多，就快不起来。
2、一条记录的内容占据的数据量差不多是10几byte，而InnoDB每次去磁盘拿数据的时候，是以页为单位（一页是16Kb），所以在这么大的空间中真正有用的数据只占一点点，IO浪费大。

所以AVL也被淘汰了

B-Tree（B树）

为什么我们要说B-Tree，因为B+树是B树的变种，而B树是所有关系型数据库的鼻祖。

什么是B-Tree?什么B树？
B-Tree又称为多路搜索树，多叉平衡查找树。
我们原来AVL中一个节点上面只有一个关键字，而B-Tree将一个节点上面的内容将数据由一个关键字变成多个关键字（一个节点中最多关键字=Degree-1）。这个Degree也是B-Tree中的路的个数

我们原理AVL中我们允许左子树和右子树之差是1，而在我们的B-Tree上要求同类型的节点都在同一水平线上。

B-Tree还会根据我们当前节点的内容，将数据区间划分为若干个开区间。如5,20 会划分为
（负无穷，5）
（5,20）
（20，正无穷）

在这里插入图片描述

而为什么要使用B-Tree？

因为在B-Tree里面节点的关键字可以是N个，所以我们可以往节点中去填充数据，填充到16Kb的大小，因为我们填充了很多内容，然后根据节点里面的关键字，我们还可以分为多路和多叉，所以我们的B-Tree的树形结构，不单单可以纵向发展，而且还可以随着我们的关键字变多，进行横向发展，本来我们二叉树是纵向发展的，现在我们应该纵向发展的树变成了横向发展，那么这个时候我们树的高度自然就变少了。所以使用查询次数变少，IO次数变少。
而又因为我们可以往节点中填充数关键到16Kb大小，所以我们IO浪费的问题也解决了。

那问题又来了，为什么MySQL中InnoDB底层使用的是B+Tree，而不是B-Tree？

B+Tree（B+树）

我们先来看B-Tree和B+Tree的区别。
在B-Tree中，节点中最多可存放关键字个数=Degree(路)-1.，而在我们的B+Tree中，节点汇总关键字个数与路数的比值为1:1（节点关键字个数：路数 = 1:1）。
2、在我们的B-Tree层面，每个节点里面的内容包含关键字、数据区和子节点的指引用针。而B+Tree中，只有叶子节点有数据区，这个数据区我们可以认为我们可以从这里拿到我们数据的值。B+Tree把所有数据区的内容都存放到了叶子节点，也就是将非叶子节点中的数据区都存放到叶子节点中。
3、数据区划分也有所区别，如1,28,66
[1,28)
[28,66)
[66,无穷大)

在这里插入图片描述

我们现在来看看B+Tree的优点：
1、基于索引的扫表操作更快。比如我们执行一条语句 select * from user；如果在我们的B-Tree中，由于数据区存放在每一个节点中，所以我们需要去扫整个数据结构，而B+Tree的数据区存放到了叶子节点，所以我们只需要扫叶子节点就行了。
2、基于索引的排序更加优秀。我们执行排序select * from user order by id;如果在我们的B-Tree中，因为节点在磁盘上的顺序可能不同，一些快一些慢，当这些数据加载到内存一定会做一个二次排序的操作，而在B+Tree里叶子节点中的数据区中，一个节点的末尾关键字指向相邻节点的头关键字，并且整个叶子节点数据区中的数据是天然有序的，数据从磁盘拿到内存不用进行二次排序。
3、IO吞吐能力更强。B+Tree中将所有非叶子节点的数据区都存放到叶子节点中，B+Tree中非叶子节点包含关键字+指针，而B-Tree中一个节点包括关键字+数据区+指针。InnoDB每次去磁盘拿数据的时候，是以页为单位，这样减少了数据区的数据量，有用的数据量变多了，IO吞吐能力更强。
4、B+Tree访问次数更加稳定。 在B-Tree中，由于树的高度不同，所以访问B-Tree的次数是不定的。而在我们的B+Tree中，无论我们要访问哪一个关键字节点，我们一定要走到叶子节点数据区，拿到数据才能返回。所以B+Tree访问次数更加稳定。

而上面这些，就是B+Tree相比于B-Tree更适用于MySQL的地方。

B+Tree衍生问题

为什么建议主键ID是递增的，和B+Tree有什么关系？

MySQL建议我们的主键ID是递增的，因为InnoDB底层的B+Tree中叶子节点的数据区的数据是天然有序的，而如果主键ID是递增的话，那么我们就只需要往数据区后面的数据追加就行了，而如果我们的主键不是递增的，我们就需要将数据插入到有序的数据区中，这样可能会出现大量移位，开销很大。

为什么主键不建议使用UUID？
1、首先我们知道，UUID是varchar类型的，而varchar类型占据的存储空间较大，这样会弱化B+Tree的IO吞吐能力。
2、MySQL建议我们的主键ID是递增的，因为InnoDB底层的B+Tree中叶子节点的数据区的数据是天然有序的，而如果主键ID是递增的话，那么我们就只需要往数据区后面的数据追加就行了，而如果我们的主键不是递增的，我们就需要将数据插入到有序的数据区中，这样可能会出现大量移位，开销很大。而我们很多人在用UUID的时候用的是version4的版本，version4的版本是随机数的版本，如果是随机数的话，那么就无法保证后面生成的一定比前面生成的大，所以可能不是一个追加操作而是插入操作，开销较大。

拓展基于B+Tree索引结构的落地，请查看我的另一篇文章：MySQL 5.5版本的两个执行引擎

三、什么是索引

既然索引能让查询速度变快，那么到底什么是索引呢？

官方介绍索引是帮助MySQL高效获取数据的数据结构。更通俗的说，数据库索引好比是一本书的目录，通过索引，我们能够加快数据库的查询速度。
一般来说索引本身也很大，不可能全部存储在内存中，因此索引往往是存储在磁盘上的文件中（可能存储在单独的索引文件中，也可能和数据一起存储在数据文件中）。
我们通常说的索引，包括普通索引、主键索引、全文索引、空间索引等等，如果没有特别的说明，默认都是使用B+树结构组织（多路搜索树，并不一定是二叉的）的索引。

四、索引的优缺点

索引的优点

使用索引的意义就是通过缩小表中需要查询的记录的数目从而加快搜索速度。所以使用索引可以提高数据的检索速度，减低数据库IO成本。
索引之所以查得快，是因为事先将数据排好序，若该字段正好需要排序，则正好降低了排序的成本。所以使用索引能降低数据排序的成本，降低CPU的消耗。

索引的缺点

索引实际上也是一张表，记录了主机和索引字段，一般以索引文件的形式存储在磁盘上。所以使用索引占用了存储空间。
如果表的数据发生了变化，对应的所有也需要一起变更，从而降低了更新速度。否则索引指向的物理数据可能不对，这也是索引失效的原因之一。

请添加图片描述

五、如何选择索引？

比如在一个表中有姓名、年龄、性别、区号。我们如何选索引？哪列做为索引字段最好？
在选择索引的时候，我们的要选择数据重复性小的，离散性强，我们选择性就最强，也就是要选择我们数据重复性小的字段来作为索引。

计算索引的时候有一个公式：count distince col : count col
也就是当前列不重复的值：当前列的所有值

如果当前列能够维持离散性的比值大概是 1/10，或者大于它，那么这个列就是非常优秀的索引了~

六、什么时候不用索引

经常增删改的列不要建立索引。
有大量重复的列不要建立索引。
表记录太少不要建立索引。

七、MySQL的索引类型

MySQL中的索引类型有哪些？

普通索引：普通索引是MySQL中基本的索引类型，没有什么限制，允许在定义索引的列中插入重复值和空值。
主键索引：主键索引列中的值必须是唯一的，不允许有空值。
唯一索引：索引列中的值必须唯一，但是允许为空值。
全文索引：只能在文本类型char、varchar或者text类型的字段上创建全文索引。字段长度比较大时，如果创建普通索引，在进行like模糊查询时效率较低，这是可以创建全文索引。
空间索引：MySQL在5.7之后的版本支持了空间索引，而且支持openGIS集合数据模型。MySQL在空间索引这方面遵循openGIS集合数据模型的规则。
前缀索引：在文本类型如char、varchar或者是text列上创建索引时，可以指定索引列的长度，但是数值类型不能指定。

按照索引列类型分类：可分为单列索引和组合索引