面试热点话题：聊聊MySQL索引“B+Tree”的前世今生，(1)

2401_84446610

于 2024-04-20 05:32:11 发布

阅读量647

点赞数 7

分类专栏： 2024年程序员学习文章标签：面试 mysql 职场和发展

本文链接：https://blog.csdn.net/2401_84446610/article/details/137985950

版权

2024年程序员学习专栏收录该内容

20 篇文章 0 订阅

订阅专栏

本文介绍了Java程序员如何有效地提升技能，推荐了《2024年最新Java开发全套学习资料》，涵盖了从基础到进阶的课程，并重点讨论了索引优化，特别是B+树在MySQL中的应用，包括索引的优缺点、最左前缀原则、索引覆盖和回表查询等概念，以及为何MySQL选择B+树而非B树。

摘要由CSDN通过智能技术生成

先自我介绍一下，小编浙江大学毕业，去过华为、字节跳动等大厂，目前阿里P7

深知大多数程序员，想要提升技能，往往是自己摸索成长，但自己不成体系的自学效果低效又漫长，而且极易碰到天花板技术停滞不前！

因此收集整理了一份《2024年最新Java开发全套学习资料》，初衷也很简单，就是希望能够帮助到想自学提升又不知道该从何学起的朋友。

既有适合小白学习的零基础资料，也有适合3年以上经验的小伙伴深入学习提升的进阶课程，涵盖了95%以上Java开发知识点，真正体系化！

由于文件比较多，这里只是将部分目录截图出来，全套包含大厂面经、学习笔记、源码讲义、实战项目、大纲路线、讲解视频，并且后续会持续更新

如果你需要这些资料，可以添加V获取：vip1024b （备注Java）

正文

这些事实或许会颠覆你的一些认知，比如在你读过的其他文章或书中。以上这些都属于“范围查询”，都是不走索引的！

没错，早先5.5以前优化器是不会选择通过索引搜索的，优化器认为这样取出的行多与全表扫描的行，因为还要回表查一次嘛，可能会涉及I/O的行数更多，被优化器放弃。

经过算法（B+Tree）优化后，支持对部分范围类型的扫描（得利与B+Tree数据结构的有序性）。该做法同时也违反了最左前缀原则，导致范围查询后的条件无法用到联合索引，我们在后面详细说明。

二、索引的优缺点

=======================================================================

1、优点

索引大大减小了服务器需要扫描的数据量
索引可以帮助服务器避免排序和临时表
索引可以将随机I/O变成顺序I/O

2、缺点

虽然索引大大提高了查询速度，同时却会降低更新表的速度，如对表进行INSERT、UPDATE和DELETE。因为更新表时，MySQL不仅要保存数据，还要保存索引文件。
建立索引会占用磁盘空间的索引文件。一般情况这个问题不算严重，但如果你在一个大表上创建了多种组合索引，且伴随大量数据量插入，索引文件大小也会快速膨胀。
如果某个数据列包含许多重复的内容，为它建立索引就没有太大的实际效果。
对于非常小的表，大部分情况下简单的全表扫描更高效；

因此应该只为最经常查询和最经常排序的数据列建立索引。（MySQL里同一个数据表里的索引总数限制为16个）

数据库存在的意义之一就是是解决数据存储和快速查找的。那么数据库的数据存在哪？没错，是磁盘，磁盘的优点是啥？便宜！缺点呢？相比内存访问速度慢。

那么你知道MySQL索引主要使用的数据结构么？

B+树！你脱口而出。

那 B+树是什么样的数据结构？MySQL索引又是为什么选择了B+树呢？

其实最终选用 B+树是经历了漫长的演化：

二叉排序树 → 二叉平衡树 → B-Tree（B树） → B+Tree（B+树）

有小伙伴问我“B树跟 B-树有什么区别”？这里普及一下，MySQL数据结构只有B-Tree（B树）和B+Tree（B+树），多只是读法不同罢了，“B-Tree” 一般统称为B树，你叫他B-树也行~~

还有小伙伴提到的红黑树，是编程语言中的存储结构，不是MySQL的；如Java的HashMap就是用的链表加红黑树。

好了，今天就带着大家一起看一下演化成 B+树的过程吧。

三、B+Tree索引的前世今生

==============================================================================

1、二叉排序树

理解B+树之前，简单说一下二叉排序树，对于一个节点，它的左子树的孩子节点值都要小于它本身，它的右子树的孩子节点值都要大于它本身，如果所有节点都满足这个条件，那么它就是二叉排序树。（此处可以串一下二分查找的知识点）

在这里插入图片描述

上图是一颗二叉排序树，你可以尝试利用它的特点，体验查找9的过程：

9比10小，去它的左子树（节点3）查找
9比3大，去节点3的右子树（节点4）查找
9比4大，去节点4的右子树（节点9）查找
节点9与9相等，查找成功

一共比较了4次，那你有没有想过上述结构的优化方式？

2、AVL树 (自平衡二叉查找树)

在这里插入图片描述

上图是AVL树，节点个数和值均和二叉排序树一摸一样

再来看一下查找9的过程：

9比4大，去它的右子树查找
9比10小，去它的左子树查找
节点9与9相等，查找成功

一共比较了3次，同样的数据量比二叉排序树少了一次，为什么呢？因为AVL树高度要比二叉排序树小，高度越高意味着比较的次数越多；不要小看优化的这一次，假如是200w条数据，比较次数会明显地不同。

你可以想象一下一棵 100 万节点的平衡二叉树，树高 20。一次查询可能需要访问 20 个数据块。在机械硬盘时代，从磁盘随机读一个数据块需要 10 ms 左右的寻址时间。也就是说，对于一个 100 万行的表，如果使用二叉树来存储，单独访问一个行可能需要 20 个 10 ms 的时间，这个查询可真够慢的！

3、B树（Balanced Tree）多路平衡查找树多叉的

B树是一种多路自平衡搜索树，它类似普通的二叉树，但是B书允许每个节点有更多的子节点。B树示意图如下：值得注意的是，B树的非叶子节点和叶子结点的data数据都是分开存储的，那么针对范围查询、排序等常用特性就很不友好了。

在这里插入图片描述

B树的特点：

所有键值分布在整个树中
任何关键字出现且只出现在一个节点中
搜索有可能在非叶子节点结束
在关键字全集内做一次查找，性能逼近二分查找算法

为了提升效率，要尽量减少磁盘I/O的次数。实际过程中，磁盘并不是每次严格按需读取，而是每次都会预读。

磁盘读取完需要的数据后，会按顺序再多读一部分数据到内存中，这样做的理论依据是计算机科学中注明的局部性原理：

由于磁盘顺序读取的效率很高(不需要寻址时间，只需很少的旋转时间)，因此对于具有局部性的程序来说，预读可以提高I/O效率.预读的长度一般为页(page)的整倍数。
MySQL(默认使用InnoDB引擎)，将记录按照页的方式进行管理，每页大小默认为16K(可以修改)。

B-Tree借助计算机磁盘预读机制:

每次新建节点的时候，都是申请一个页的空间，所以每查找一个节点只需要一次I/O;因为实际应用当中，节点深度会很少，所以查找效率很高.

那么最终版的 B+树是如何做的呢？

4、B+ Tree (B+树是B树的变体，也是一种多路搜索树)

在这里插入图片描述

从图中也可以看到，B+树与B树的不同在于：

所有关键字存储在叶子节点，非叶子节点不存储真正的data，从而可以快速定位到叶子结点。
为所有叶子节点增加了一个链指针，意味着所有的值都是按顺序存储的，并且每一个叶子页到根的距离相同，很适合查找范围数据。说明支持范围查询和天然排序。

因此，B+Tree可以对<，<=，=，>，>=，BETWEEN，IN，以及不以通配符开始的LIKE使用索引。且如果用到了该索引，排序功能的消耗大大减少。

B+树的优点：

比较的次数均衡，减少了I/O次数，提高了查找速度，查找也更稳定。

B+树的磁盘读写代价更低
B+树的查询效率更加稳定

要知道的是，你每次创建表，系统会为你自动创建一个基于ID的聚集索引（上述B+树），存储全部数据；你每次增加索引，数据库就会为你创建一个附加索引（上述B+树），索引选取的字段个数就是每个节点存储数据索引的个数，注意该索引并不存储全部数据。

四、为什么MySQL索引选择了 B+树而不是 B树？

==========================================================================================

B+树更适合外部存储(一般指磁盘存储),由于内节点(非叶子节点)不存储data，所以一个节点可以存储更多的内节点，每个节点能索引的范围更大更精确。也就是说使用B+树单次磁盘I/O的信息量相比较B树更大，I/O效率更高。
mysql是关系型数据库，经常会按照区间来访问某个索引列，B+树的叶子节点间按顺序建立了链指针，加强了区间访问性，所以B+树对索引列上的区间范围查询很友好。而B树每个节点的key和data在一起，无法进行区间查找。

五、你应该知道的索引相关知识点

==============================================================================

1、回表查询

比如你创建了name， age索引 name_age_index，查询数据时使用了

select * from table where name =‘陈哈哈’ and age = 26;

由于附加索引中只有name 和 age，因此命中索引后，数据库还必须回去聚集索引中查找其他数据，这就是回表，这也是你背的那条：少用select * 的原因。

2、索引覆盖

结合回表会更好理解，比如上述name_age_index索引，有查询

select name， age from table where name =‘陈哈哈’ and age = 26;

此时select的字段name，age在索引name_age_index中都能获取到，所以不需要回表，满足索引覆盖，直接返回索引中的数据，效率高。是DBA同学优化时的首选优化方式。

3、最左前缀原则