Mysql B+树索引算法原理

最新推荐文章于 2025-07-27 19:54:35 发布

原创最新推荐文章于 2025-07-27 19:54:35 发布 · 4.7k 阅读

9 ·

CC 4.0 BY-SA版权

文章标签：

#mysql #算法

MySQL 专栏收录该内容

32 篇文章

订阅专栏

一、Mysql中的索引

在Mysql中的常见的索引为B+树和Hash，其中Hash为散列存放的形式不利于范围查询，而 B+树采用树结构，并且叶子节点和非叶子节点存放不同信息，不仅利于范围查询，对单个搜素也有非常高的效率。

另一方面，添加到数据库中的数据是存放在硬盘中的，每次读取查询要做磁盘io操作效率是非常低的，当然数据库底层已经使用缓存池技术来降低磁盘的操作了，但磁盘的io操作还是不可避免的，使用索引之所以可以提高效率很大一方面是减少了很多不必要的磁盘io操作。

在给数据库指定索引算法时，大家使用最多的应该还是B+树算法吧，那 B+树是怎么演变来的呢？为什么可以提高效率呢？

二、都有哪些搜索算法

Hash表等值查询效率比较高、但是不支持范围查询
二叉树时间复杂度log2n 缺点：有可能产生不平衡类似于链表的结构时间复杂度为o(n)
平衡二叉树avl/红黑树使用旋转和变色方式保证平衡它是一棵空树或它的左右两个子树的高度差的绝对值不超过1
缺点：如果存放几百条数据的情况下，树高度越高，查询效率会越慢
B-Tree树
叶子节点具有相同的深度、叶子节点指针是为空，所有索引的元素不能够重复叶子节点的数据从左到右递增排列。
B+树
1.非叶子节点不存储data，只存放索引
2.叶子节点包含所有索引字段
3.叶子节点用指针连接，提高区间访问的性能

三、B+树算法的演变

1. 二叉树作为索引算法带来的弊端

在这里插入图片描述

假如现在新增的一条数剧，首先根据主键ID在第一级节点上判断是否大于当前的值，如果大于就判断右分支下一节点是否有数据，如果第二级有数据，则与第二级对比大小，如果大于当前第二级的数据且第二级右分支没有子节点，则该数据放在当前第二级的右分支位置。以此类推后续新增的数据其实就是找到自己在当前数据下的存放位置。

当根据主键ID查询时，会先判和第一级数据的大小，如果大于则左边分支的数据直接略过，去右分支的第二级比较大小，以此来提高效率。比如查询主键ID为5的数据只需要做两次节点判断即两次磁盘操作即可取到结果。

但根据这种算法，对于顺序新增的数据会出现左右分支的不平衡性问题，比如下面的情况。

在这里插入图片描述
当在这种情况下，要查询主键ID为4的数据则要做4次节点判断，即4次磁盘操作，相当于全表查询，并没有提高效率。

2. 平衡二叉树

二叉树在解决数据分配不平衡的场景可以提高效率，但对于顺序添加的数据，并没有提高效率，而平衡二叉树使用旋转的方式解决二叉树分支不平衡性问题，平衡二叉树保持分支两边级数差别不能等大于1，否则就旋转交换位置使左右级数差别在1以内。

比如，添加主键ID为1、2、3的三条数据，当添加1、2时和二叉树规则一样，但当添加3时，按二叉树的规则应当放在2的右分支，但此时整体相当于，左分支1级，右分支3级，左右分支差别大于了1，此时触发旋转将2调制第一级的位置，左分支为1，右分支为2，此时左分支2级右分支2级达到左右分支平衡。
在这里插入图片描述

同理当4添加进来时，会放在3的右分支，此时整体左分支为2级，右分支为3级，左右分支差别没有大于1，不会触发旋转。
在这里插入图片描述
但当5添加进来时，整体左分支1级，右边有3级左右差别大于1，触发旋转，将4提到第二级，3和5在4的左右分支。此时整体左分支2级，右分支3级差距不大于1。

从上面的过程可以看出，平衡二叉树解决了二叉树左右分支不平衡的问题，但随着数据的增多，层级数也会升高，如果查询的数据比较靠下面，则节点判断的次数还是比较多，还是不能降低磁盘的操作。

3. B-Tree树

从上面的看以看出，二叉树和平衡二叉树，都只是一个节点放一个数据，在每次做节点判断时都要做一次磁盘操作取出一个值判断下面是继续左分支还是右分支，这样的弊端就是当数据非常多的时候，树的层级也会越来越高，磁盘操作会越来越多，同样会降低效率，那有没有一种数据量大的时候，层级也不会堆积的太高呢，B树便可以解决这一问题，B树将原本一个节点放一个数据的情况换成可一个节点可以存放多个数据，比如设定一个节点最大存放3个子节点，那么如果存放顺序的1，2，3这三个信息则节点图为：

在这里插入图片描述
因为我们设定一个节点最大可以存放三个子节点，所以1、2、3都放在同一级的一个大节点即可，在读取数据时，可以将一个大节点的数据都读进内存中进行判断，效率要比每次读取磁盘要高的多，在mysql中读取硬盘是根据页数据读的，默认一个读取16k一页的大小。

那在节点中再添加一个主键ID为4的一条数据呢呢，现在的节点数就为4个节点了，因为我们设定的最大子节点树为3，一个大节点已经无法存放这么多子节点了。如果此时拆分为两个节点，这两个节点不可能是同一级的，只能是一级节点和二级节点，但左右分支数会不平衡，如果拆分成三个呢，比如下面这种情况：
在这里插入图片描述
如果此时再添加主键ID为5的一条数据，节点图为：

再添加主键ID为6的数据：

看到这里应该可以看出规律了，假如现在要查询主键ID为5的数据，第一次磁盘操作读取第一级的数据，判断出5>4的则直接再读取第二级的第三个节点中的数据，即可找到ID为5的数据，一共就做了两次磁盘操作。

4. B+树

上面B树已经解决了二叉树，和平衡二叉树的弊端问题，但为什么又出现B+树呢，原因在于，二叉树、平衡二叉树、B树，他们再每个子节点都存放了主键ID和数据data，在上面也有提到，mysql一次从磁盘读取的为16k一页的大小，假如一个主键id占用8个字节，一条数据一共占50个字节，则16k 只能读取 1024*16/（50+8） = 282 个数据，假如有100万条数据假如是全表查询则要读取将近 3546 次磁盘操作才可以读取完所有的数据，对于数据库动则几百上千万的数据，磁盘操作的次数终会成为性能的瓶颈。

那么B+树是怎么优化的呢，B+树将B树的一个节点同时存放主键和数据的形式改为，叶子节点和非叶子节点，其中非叶子节点不存储data，只存放主键和指向数据的指针，而叶子节点存放的data是指向数据文件对应行的数据。叶子节点和非叶子节点采用指针连接，提高区间访问的性能。

在这里插入图片描述
在这里我们再计算下，假设主键占用8个字节，指针占6个字节，那么mysql 一个数据页16k 就可以查询 1024*16/（8+6）=1170 条数据，再假如全表查询100万条数据的话差不多做854次磁盘操作就可以了，当然这里没算缓冲池的数据，比B树的3546 提高了4倍多的效率，而且在B树中我们只把数据当做50个字节来算的，在项目中一条数据何止50个字节呀，所以B+树提高的绝非四五倍的效率。