MySQL索引的底层数据结构衍变史

原文链接，本文重新排版优化

1. MySQL为什么要建立索引？

首先，还是那个问题，为什么要出现索引这么概念。 我们都知道，我们在写CRUD代码时，最终都是要和数据库打交道的。而我们思考一下，当数据库的数据越来越多时，我们肯定是需要根据某种规则来进行快速查询的。否则每次查询用O(N)复杂度去遍历所有数据，进行挨个匹配，效率得多低。 那么索引产生的目的就逐渐清晰了，就是帮助我们快速查询的。老规矩，咱得站在Mysql开发者的角度来选择索引底层的数据结构。

2. 可选数据结构

2.1 Hash结构（自适应哈希索引）

哈希表是一种键-值（key-value）存储数据结构。我们只需要输入待查找的值，即key，就可以找到其对应的值即value。哈希的思路很简单。把值放在数组里，用一个哈希函数把key换算成一个确定的位置，然后把value放在数组的这个位置。 不可避免的，多个key值经过哈希函数的换算，会出现同一个值的情况。处理这种情况的一种方法是拉出一个链表。假设，你现在维护着一个身份证信息和姓名的表，需要根据身份证号查找对应的名字，这时对应的哈希索引的示意图如下所示：

图中，User2和User4根据身份证号算出来的值都是N，也就是发生了Hash冲突。但没关系，后面还跟了一个链表。即使发生冲突的数据，我们也仅需要经过较少的遍历，就能拿到我们要的值了。

• 优点：增加新的User时速度会很快，只需要不断地向后追加（具体顺序由散列算法决定）
• 缺点：缺点也很明显，由于散列算法导致数据不是连续的，所以在区间范围查找时，效率就会很低。我们知道数据库的范围查询场景是非常多的。因此这个缺点基本就会把Hash算法给排除了。

2.2 有序数组

有序数组在等值查询和范围查询场景中的性能都非常优秀。（因为有序性的范围查找放弃Hash结构）。还是上面这个身份证号查名字的例子。如果我们是用有序数组来实现的话，示意图如下：

• 优点：这里我们假设身份证号没有重复，这个数组就是按照身份证号递增的顺序保存的。这时候如果你要查ID_card_n2对应的名字，用二分法就可以快速得到，这个时间复杂度是O(log(N))。速度非常快。 这么看来，有序数组来实现排序就是一个完美策略了。但事实是这样么？
• 缺点：如果仅仅看查询效率，有序数组就是最好的数据结构了。但是，在需要更新数据的时候就麻烦了。往中间插入一个记录就必须要挪动后面的数据（一个表的数据轻松几千万），成本太高。 注意，数据库注的使用场景注定是数据量大，修改频繁的场合。因此这个数据结构不合适。
• 那么有序数组适合存储什么样的数据呢？ 所以，有序数组索引只适用于静态存储引擎。比如你要保存的是2017年某个城市所有人口信息。因为已经是过去的时间，数据一般不会改变。

2.3 二叉树结构

二叉树也是课本中的经典数据结构了。还是以身份证号查名字的例子。如果我们用二叉搜索树来实现的话，示意图如下：

二叉搜索树的特点是：每个节点的左儿子小于父节点，右节点大于父节点。这样如果我们要查ID_card_n2的话，按照图中的搜索顺序就是按照UserA -> UserC -> UserF -> User2这个路径得到。这个时间复杂度是O(log(N))。

2.3.1 二叉树的增删（简单举例）

现有一个二叉树：

2.3.1.1 二叉树的增

假设我们要新增一个索引值为6的节点。

• 比较 6 > 4 因此，接右子树
• 比较 6 < 8 ，接左子树
• 比较 6 < 7 ，接左子树
• 比较 6 > 5 ，且 5的右子树为空，将6 作为 5 的右子树（结束）
  
  新增就很简单，只要不停地往下添加元素即可。但我们需要注意的是二叉树每一个节点最多只能有两个子节点。因此我们的二叉树的高度会轻易变得非常高。

2.3.1.2 二叉树的删

二叉树的删除比较复杂，一般需要考虑多种情况

• 删除节点是叶子节点=直接删除
  

• 删除节点是只有左子树（右子树）=将删除节点的值（left，right）替换为该左子树（有节点）的值
  

1. 将D节点的值替换D.left (H节点)的值
2. H左子树不为空，将D.left = H.left,
3. H右子树不为空，D.right = H.right

• 删除节点同时有左子树和右子树
  假设我们需要删除节点8.
  
  这种情况我们需要找到删除节点的继承者（继承者可以是左边最大的7，也可以是右边最小的9）。以9为例：
  
  我们只需要将8移除，并把把9替换到8的位置即可。
• 二叉树挺好的，为什么数据结构不用它
  可以看到，无论是查询效率，还是在增删的影响范围方面，二叉树可以说都非常优秀了。但是MySQL最终选择的数据结构并没有选择他。如果要解释这个问题，我们不得不解释一下二叉树可能带来的另一个问题。

2.3.2 磁盘IO的因素影响

为什么关系型数据库都选择了 B+树，这个和磁盘的特性有着非常大的关系

这就是我们的磁盘，我们数据的读取与增删所谓的落盘操作，就是把值放到这个东西里。我们简化一下，可以这么看：

一个磁盘由大小相同且同轴的圆形盘片组成，磁盘可以转动（各个磁盘必须同步转动）。在磁盘的一侧有磁头支架，磁头支架固定了一组磁头，每个磁头 负责存取一个磁盘的内容。磁头不能转动，但是可以沿磁盘半径方向运动（前后伸缩）。

2.3.2.1 磁盘的组成

盘片被划分成一系列同心环，圆心是盘片中心，每个同心环叫做一个磁道（外围的道长，靠近圆心的道短）， 所有半径相同的磁道组成一个柱面。磁道被沿半径线划分成一个个小的段，每个 段叫做一个扇区，每个扇区是磁盘的最小存储单元也是最小读写单元。现在磁盘扇区一般是 512 个字节~4k 个字节。

2.3.2.2 磁盘定位数据的流程

磁盘上数据必须用一个三维地址唯一标示：柱面号、盘面号、扇区号。 读/写磁盘上某一指定数据需要下面步骤：

1. 首先移动臂根据柱面号使磁头移动到所需要的柱面上，这一过程被称为定位或查找。
2. 所有磁头都定位到磁道上后，这时根据盘面号来确定指定盘面上的具体磁道。
3. 盘面确定以后，盘片开始旋转，将指定块号的磁道段移动至磁头下。 经过上面步骤，指定数据的存储位置就被找到。这时就可以开始读/写操作 了。
   
   

2.3.2.3 磁盘IO的耗时原因分析

可以看见，磁盘读取依靠的是机械运动，分为寻道时间、旋转延迟、传输时间三个部分，这三个部分耗时相加就是一次磁盘 IO 的时间，一般大概 9ms 左 右。

• 寻道时间（seek）是将读写磁头移动至正确的磁道上所需要的时间，这部分时间代价最高；
• 旋转延迟时间（rotation）是磁盘旋转将目标扇区移动到读写磁头下方所需的时间，取决于磁盘转速；
• 数据传输时间（transfer）是完成传输数据所需要的时间，取决于接口的数据传输率，在纳秒级，远小于前两部分消耗时间。 但磁盘读取时间成本是访问内存的几百倍到几万倍之间

2.3.2.4 通过磁盘预读，顺序读提高读数效率

为了提高效率，要尽量减少磁盘 I/O。为了达到这个目的，磁盘往往不是严格按需读取，而是每次都会预读，即使只需要一个字节，磁盘也会从这个位置开始，顺序向后读取一定长度的数据放入内存，这个称之为预读。这样做的理论依据是计算机科学中著名的局部性原理： 也就是说：当一个数据被用到时，其附近的数据也通常会马上被使用。 程序运行期间所需要的数据通常比较集中。 由于磁盘顺序读取的效率很高（不需要寻道时间，只需很少的旋转时间）， 一般来说，磁盘的顺序读的效率是随机读的 40 到 400 倍都有可能，顺序写是随 机写的 10 到 100 倍（SSD 盘则差距要小的多，顺序读写的效率是随机读写的 7 到 10 倍，但是有评测表明机械硬盘的顺序写性能稍优于 SSD。总的来说 Mysql 数据库如果由硬盘由机械的换成 SSD 的，性能会有很大的提升），因此对于具有 局部性的程序来说，预读可以提高 I/O 效率。 都讲到此处了，我们不得不提一下高性能消息中间件Kafka。为什么它的消费速度快呢？因为它是基于磁盘顺序读写和MMAP技术来实现高性能。

2.3.3 二叉树为什么不适合作为Mysql索引的数据结构

可以看到，通过我们刚才学习到的知识，我们知道每一个索引代表的就是一次查询。因此，图中无论我们要访问红色节点，还是黄色节点，都需要经过两次IO。每次查询的内容都分布在磁盘的不同位置，因此每次磁盘IO都要做一次长途旅行，要经过磁盘疯狂旋转到对应的扇区后才能访问数据。毫无疑问，如果这样的IO操作过多，就好比我们的五一5天假期想去哪个地方旅游，结果4天半的时间全部花在跑路上了。这样肯定是极大影响执行效率的。因此，机智的Mysql研发人员就想到了以二叉树作为基础，以继续优化磁盘IO的次数为优化方向而努力衍生出变种树。

2.3.4 二叉树的异化过程

2.3.4.1 普通的二叉树

我们考虑一种比较极端的情况。当我们在运气非常不好的时候，二叉树的样子可能会是这样的。（当根节点是00001，而每次插入的节点都比上一次插入的大。那么这课树就很容易长成一根竹竿）。

这种情况肯定不会减少IO，反而把IO的次数拉到了最大。因此果断排除。

2.3.4.2 平衡二叉树

平衡二叉树在二叉树的基础上增加了左旋右旋的概念。当我们依旧以上面的案例新增元素时，平衡二叉树就会通过左/右旋的方式，将二叉树分布的更加均匀。
此时，平衡二叉树的查询时间复杂度时O（logN），性能已经足够高了，难道B-树可以比它更快？ 其实，从算法逻辑上来讲，二叉树查找速度和比较次数都是最小的。OK，这已经比普通二叉树前进了一大步了。那么在平衡二叉树上我们如何继续减少IO呢？

回到我们之前分析IO次数的这张图中。 如果我们要查ID_card_n2的话，按照图中的搜索顺序就是按照UserA -> UserC -> UserF -> User2这个路径得到。 从这个例子中例子中，我们不难总结出：磁盘IO的次数是4。索引数的高度也是4。所以最坏的情况下，磁盘IO次数等于索引树的高度。因此，如果我们如果需要优化我们的查询算法，那么优化树的高度就成了我们不可回避的问题。 因此，出现了B-树。B树的优化目标就是把我们原本"瘦高"的树结构变得"矮胖"。这就是B-树的特征之一。

2.3.4.3 B-树的诞生，减少IO的一大步 (B树就是B-树)

一个m阶的B-树具有如下几个特征。首先很多新手，看到这个B-树会念成B减树。这可太不专业了。实际上这个就念B树。 下面来介绍一下：

B-树（Balance Tree），一个m阶的B-树具有如下几个特征：

• B树的阶数（M）一般都比较大（通常大于100）
• 每个结点最多有M-1个key，并且以升序排列；
• 每个结点最多能有M个子结点；
• 根结点至少有两个子结点；

我们以一个3阶（m=3），k表示当前阶的B-树为例，来看看B-树的具体结构。树中的具体元素和刚才的二叉树是一样的。我们下面直接写出已经生成好的B-树的例子：

在这棵已经生成好的树中，咱们重点来看一下左边的（2，6）节点。该节点有两个元素2和6。又有三个孩子1，（3，5），8。其中1小于元素2，（3，5）在元素（2，6）之间，8大于（3，5）。恰好符合刚刚列出的几条特性。（当我们的M越大，我们每个节点中存放的key才会越多，每个节点的子节点也会越多。因此数据量较大的场景，M值也会较大）。

2.3.4.3.1 B-树查询流程

假设我们现在要查询5，流程如下：

2.3.4.3.1.1 B-树在平衡二叉树上的基础上做了什么

• 压缩
  我们可以发现，B树的核心概念就是把多个数据压缩到同一片磁盘扇区中。因此每次访问到一个磁盘扇区时，我们会对该磁盘扇区中查到的数据和内存中的数据进行比较。 B-树在查询的比较次数上来说其实不比二叉树找的树少。尤其是单一节点中的元素数量很多时，反而比较更加频繁。 但是，相比于磁盘IO的速度，内存中比较耗时几乎可以忽略不计，所以只要树的高度足够低，IO次数足够少，就可以提升查找性能。 相比之下节点内部元素多一些也没有关系。仅仅是多了几次内存交互。只要是不超过磁盘页的大小即可。这就是B-树的优势之一。
• 分叉
  显然，二叉树，每个节点最多有两个子节点。而B-树突破了这一限制。因此一个三层B-树就能存放原本平衡二叉树的千倍万倍的数据量。

2.3.4.3.2 B-树插入流程

那么B-树插入新节点的流程是怎样的呢？B-树插入新节点的过程比较复杂。而且分成很多种情 况。在此举一个最典型的例子。

假如我们要插入的值是4。 自顶向下查找4的节点位置，发现4应当插入到节点元素3，5之间。

节点3，5已经是两个元素节点，无法再增加了（每个结点最多有M-1个key）。父节点2，6也是 两元素节点，也无法增加。根节点9是单元素节点，可以升级为两元素节点。于是拆分节点3，5与2，6。让根节点9升级为两元素节点4，9。节点6独立为根节点的第二个子节点。

我们可以看到，仅仅是插入了一个元素，让整个B树的那么多节点都发生了连锁改变。但也正因 如此，让B-树能够始终维持多路平衡。这也是B-树的一大优势：自平衡。

2.3.4.3.3 B-树删除流程

下面我们再说说B-树的删除过程。同样举一个典型的例子。

删除元素11： 自顶向下查找元素11的节点位置。

删除11节点后，12节点只有一个子节点。不符合B树规范。因此找出12，13，15三个节点的中位 数13，取代节点12。从而节点12自身下移称为第一个孩子。（这个过程称为左旋）。

以上就是B-树的插入和删除，也就是学习B-树最绕的部分，但我们看到本质和平衡二叉树一样，都是通过各种旋转来维持树的平衡，同时B树除了维持平衡外，也同时兼顾维持树的高度。我们这里不深入探讨其他流程，主要理解其思想。

2.3.4.3.4 总结

我们学习B-树之后，我们忍不住拍案叫好！MySQL开发人员果然是大神。研究出这么高明的"变异树"来解决Mysql的IO效率问题。 不，博主打断一下。B-树并不是我们MySQL底层最终选择的数据结构。 什么！还有比B-树更强的数据结构么？ B-树主要应用于文件系统以及部分数据库索引，比如著名的非关系型数据库MongoDB。 而大部分关系型数据库，比如Mysql，则使用B+树作为索引。 在B-树的基础上更进一步！

2.3.4.4 B+树的诞生

B+树是基于B-树的一种变体，有着比B-树更高的查询性能。

什么是阶数：从字面意思上看很容易误导，让人误以为阶数是层数，其实不然。 阶数代表每一个节点可以拥有的子节点个数。

一个m阶的B+树具有如下几个特征，B+树和B-树有一些共同点，但是B+树也具备一些新的特性。

• 有M个子树的中间节点最多包含有M个元素(B树中是M-1个元素)，每个元素不保存数据，只用来索引，所有数据都保存在叶子节点；（让树变得更加矮胖）
• 所有的叶子节点数据构成一个有序列表，按照key排序遍历所有的数据；
• 所有的中间节点元素都同时存在于子节点，在子节点元素中是最大（或最小）元素。

看概念可能还是不太好懂（不过当你真正理解B+树时，应该觉得这些概念都理所当然。可以以这个为评判标准来看看你是否真正理解了B+树），我们可以用具体的例子来看一下B+树的结构。

猛地一看，这个树非常奇怪。不但节点之间包含了重复元素，而且叶子节点还用指针连在一起。 这些正是B+树的几个特点。首先，每个父节点的元素都出现在子节点中。是子节点的最大或最小元素。

在上面这棵树中，根节点元素8是子节点（2，5，8）的最大元素，也是叶子节点（6，8）的最大元素。 根节点元素15是子节点（11，15）的最大元素，也是叶子节点（13，15）的最大元素。 需要注意的是，根节点的最大元素（这里是15），也就等同于整个B+树的最大元素。以后无论插入删除多少元素，始终要保持最大元素在根节点中。 至于叶子节点都带有指向下一个节点的指针，形成了有序链表。

什么是卫星数据（索引所在行的数据）

所谓卫星数据，指的是索引元素所指向的数据记录，比如数据库中的某一行。

在B-树中，无论中间节点还是叶子节点都带有卫星数据。
B-树中的卫星数据（Satellite Information）

2.3.4.4.1 B+树的查询（和B-树差不多）

在单元素查询的时候，B+树会自动向下逐层查找节点，最终找到匹配的叶子节点。比如我么要查找的是元素3。


查询流程看起来和B-树差不多。但依旧存在两点不同。

1. 首先：B+树中间节点没有卫星数据，所以同样大小的磁盘可以容纳更多的节点元素。这就意味着，数据量相同的情况下，B+树的结构比B-树更加"矮胖"，因此，查询时IO次数也更少。
2. 其次，B+树的查询必须最终查找到叶子节点，而B-树只要找到匹配元素即可，无论匹配元素处于中间节点还是叶子节点。 因此，B-树的查找性能并不确定（最好的情况是只查根节点，最坏的情况是查到叶子节点）。而B+树的每一次查找都是稳定的。下面我们来看看范围查询。

2.3.4.4.2 B-树与B+树范围查询的对比

2.3.4.4.2.1 B-树如何做范围查询呢？ 只能依靠繁琐的中序遍历。

比如我们要查询范围为3到11的元素：

2.3.4.4.2.2 B+树如何做范围查询呢？

需要注意：上图中的磁盘页1，2，3依旧需要进行磁盘IO。 如果每次从头节点自上而下的方式查找，假设树的高度为3，就需要经过3次磁盘IO。我们的链表的方式，我们获取下一个磁盘页只需要一次磁盘IO。从而减少IO次数。

2.3.4.4.2.3 对比结果

通过对比，B+树在处理范围查询时，直接对叶子节点进行链表遍历的效率远远大于B-树的中序遍历。

优势有以下四点：

1. 非叶子节点仅存放索引节点可以容纳更多节点元素，从而再次压缩树的高度。
2. 范围查询只需要经过一次自上至下的遍历到叶子节点，之后通过链表遍历，减少磁盘IO。
3. 查询性能稳定（始终为M）。
4. 范围查询简便， 至于B+树的插入与删除操作，过程与B-树类似。此处就不再描述了。

至此，B+树成功被我们选为了MySQL的底层索引数据结构。

2.3.4.5 问题：B+树比B树能多存多少数据

我们在B+树的概念中，有一个概念是这么描述的：有M个子树的中间节点最多包含有M个元素(B树中是M-1个元素)。那是不是说，我们的B+树中，通过仅保存索引而不保存值这个优化，也仅仅能比B树多存储一个数据呢？ 非也非也。

我们换个角度思考，这个话也可以这么说。B+树的非叶子节点存储的数据能存储的子节点数，比B-数还要多一个。那么B+树一个数据页中存储的指针数据能比B树多多少呢？

我们来做一个简单的计算。

2.3.4.5.1 B-树存储的数据量

假设B-树中的一个节点最多能存储16k大小的数据。而数据库表中的每一行数据+索引的平均大小为1k，那么一个内存页差不多就能存储16个元素，并且该节点可以拥有最多17个子节点。

2.3.4.5.2 B+树的内存页存储的数据量

对于 B+树而言，只有叶子节点存放数据，非叶子节点存放的是只是索引信息和下一层节点的指针信息。一个非叶子节点能存放多少指针？ 其实这也很好算，我们假设主键 ID 为 常用的 bigint 类型，长度为 8 字 节，而指针大小在 InnoDB 源码中设置为 6 字节，这样一共 14 字节，我们一个页中能存放多少这样的单元，其实就代表有多少指针，即 16384/14=1170 个。这样我们一个节点下就可以最多拥有1170个子节点。是不是比B-树多了将近100倍？这还仅仅是一个节点对数据量的影响。

2.3.4.5.3 从数据量的角度来看为什么选择B+树，而不是B-树

根据同样的原理我们可以算出一个高度为 3 的 B+ 树可以存放： 1170*1170*16=21902400 条这样的记录。 所以在 InnoDB 中 B+ 树高度一般为 3 层，就能满足千万级的数据存储。 而 B 树和 B+树的最大区别就是，B 树不管叶子节点还是非叶子节点，都会保存数据，这样导致在非叶子节点中能保存的指针数量变少（有些资料也称为扇出），指针少的情况下要保存大量数据，只能增加树的高度，导致 IO 操作变多， 查询性能变低

3. B+树数据结构的总结

3.1 B+树的特征

• 非叶子节点只存储key，不存储value。只在叶子节点存储数据；
• 所有的叶子节点数据构成一个有序列表，按照key排序遍历所有的数据；
• 所有的中间节点元素都同时存在于子节点，在子节点元素中是最大（或最小）元素。

3.2 B+树的优势

• 单一的节点存储更多的元素，使得树更加矮胖，从而使查询的IO次数更少。
• 所有的查询都要查找到叶子节点，查询性能稳定。
• 所有的叶子节点形成有序链表，便于范围查询。

Over,Enjoy!