MySQL面试：索引为啥使用B+树而不是B树

索引的本质

数据库索引，就是数据库管理系统(DBMS)中一个排序的数据结构，用以协助快速查询，更新数据库表中数据

• 首先数据是以文件的形式存放在磁盘上面的，每一行数据都有它的磁盘地址。如果没有索引的话，要从500万行数据里面检索一条数据，只能依次遍历这张表的全部数据，直到找到这条数据。

• 但是有了索引之后，只需要在索引里面去检索这条数据就行了，因为它是一种特殊的专门用来快速检索的数据结构，我们找到数据存放的磁盘地址之后，就可以拿到数据了

• 能只有几页的内容，它是按页码来组织的，可以根据拼音或者偏旁部首来查找，只要确定内容对应的页码，就能很快地找到我们想要的内容。

索引存储模型推演

索引是一种数据结构，用来帮助MySQL高效获取数据结构

• 我们知道，数据库查询是数据库的最主要功能之一。我们都希望查询数据的速度能尽可能的快，因此数据库系统的设计者会从查询算法的角度进行优化。
• 最基本的查询算法当然是顺序查找（linear search），这种复杂度为O(n)的算法在数据量很大时显然是糟糕的，好在计算机科学的发展提供了很多更优秀的查找算法，例如二分查找（binary search）、二叉树查找（binary tree search）等。
• 如果稍微分析一下会发现，每种查找算法都只能应用于特定的数据结构之上：
  • 比如二分查找要求被检索数据有序，而二叉树查找只能应用于二叉查找树上，但是数据本身的组织结果不可能完全满足各种数据结果（例如：理论上不可能同时将两列都按照顺序进行组织）。
  • 所以在数据之外，数据库系统还维护这满足特定查找算法的数据结构，这些数据结构以某种方式引用（指向）数据，这样就可以在这些数据结构之上实现高效的查找算法。这种数据结构，就是索引

有序数组（二分查找）

• 二分查找：每一次，我们都把候选数据缩小了一半。如果数据已经排过序的话，这种方式效率比较高。
• 所以第一个，我们可以考虑用有序数组作为索引的数据结构。
• 有序数组的等值查询和比较查询效率非常高，但是更新数据的时候会出现一个问题，可能要挪动大量的数据（改变 index），所以只适合存储静态的数据。
• 为了支持频繁的修改，比如插入数据，我们需要采用链表。链表的话，如果是单链表，它的查找效率还是不够高。
  所以，有没有可以使用二分查找的链表呢？
• 为了解决这个问题，BST（Binary Search Tree）也就是我们所说的二叉查找树诞生了。

二叉查找树（BST Binary Search Tree）

二叉查找树的特点是什么？

• 左子树所有的节点都小于父节点，右子树所有的节点都大于父节点。投影到平面以后，就是一个有序的线性表。

其索引表示：

• 上图展示了一种可能的索引方式。左边是数据表，一共有两列七条记录，最左边的是数据记录的物理地址（注意逻辑上相邻的记录在磁盘上也并不是一定物理相邻的）。
• 为了加快Col2的查找，可以维护一个右边所示的二叉查找树，每个节点分别包含索引键值和一个指向对应数据记录物理地址的指针，这样就可以运用二叉查找在O(log2n)的复杂度内获取到相应数据。

特点：

• 二叉查找树即能够实现快速查找，又能够实现快速插入

• 但是二叉查找树有一个问题：就是它的查找耗时与这颗树的深度有关，在最坏的情况下时间复杂度会退化成O(n)

那么什么时刻是最坏的情况呢？如下

如果我们插入的数据刚好是有序的，2、6、11、13、17、22。

这个时候我们的二叉查找树变成了什么样了呢？

• 它会变成链表（我们把这种树叫做“斜树”），这种情况下不能达到加快检索速度的目的，和顺序查找效率是没有区别的。
  
  造成它倾斜的原因是什么呢？

• 因为左右子树深度差太大，这棵树的左子树根本没有节点——也就是它不够平衡。

• 所以，我们有没有左右子树深度相差不是那么大，更加平衡的树呢？

• 这个就是平衡二叉树，叫做 Balanced binary search trees，或者 AVL 树（AVL 是发明这个数据结构的人的名字）。

平衡二叉树(AVL树)

平衡二叉树的定义：左右子树深度差绝对值不能超过 1。

是什么意思呢？比如左子树的深度是 2，右子树的深度只能是 1 或者 3。

这个时候我们再按顺序插入 1、2、3、4、5、6，一定是这样，不会变成一棵“斜树”。

那它的平衡是怎么做到的呢？怎么保证左右子树的深度差不能超过 1 呢？

插入 1、2、3。

我们注意看：当我们插入了 1、2 之后，如果按照二叉查找树的定义，3 肯定是要在2 的右边的，这个时候根节点 1 的右节点深度会变成 2，但是左节点的深度是 0，因为它没有子节点，所以就会违反平衡二叉树的定义。

那应该怎么办呢？因为它是右节点下面接一个右节点，右-右型，所以这个时候我们要把 2 提上去，这个操作叫做左旋

同样的，如果我们插入 7、6、5，这个时候会变成左左型，就会发生右旋操作，把 6提上去。

所以为了保持平衡，AVL 树在插入和更新数据的时候执行了一系列的计算和调整的操作。

平衡的问题我们解决了，那么平衡二叉树作为索引怎么查询数据呢？

在平衡二叉树中，一个节点，它的大小是一个固定的单位，作为索引它应该存储怎么内容呢？

它应该存储三块内容：

• 第一个是索引的键值。比如我们在id上面创建了一个索引，我在用where id =1 的条件查询的时候就会找到索引里面的 id 的这个键值。
• 第二个是数据的磁盘地址，因为索引的作用就是去查找数据的存放的地址
• 第三个，因为是二叉树，它必须还要有左子节点和右子节点的引用，这样我们才能找到下一个节点。比如大于 26 的时候，走右边，到下一个树的节点，继续判断。
  
  如果是这样存储数据的话，我们来看一下会有什么问题。

缺陷

首先，索引的数据，是放在硬盘上的。查看数据和索引的大小：

select
CONCAT(ROUND(SUM(DATA_LENGTH/1024/1024),2),'MB') AS data_len,
CONCAT(ROUND(SUM(INDEX_LENGTH/1024/1024),2),'MB') as index_len
from information_schema.TABLES
where table_schema='gupao' and table_name='user_innodb';

• 当我们用树来存储索引的时候，访问一个节点就要跟磁盘发生一次IO。
• InnoDB操作磁盘的最小的单位是一页（或者叫一个磁盘块），大小是16K（16384字节）。那么，一个树的节点就是16K的大小。
• 如果我们用一个节点只存一个键值+数据+引用，比如整形的字段，可能就用了十几个或者几十个字节，它远远达不到16K的容量，所以访问一个树节点，进行一次IO的时候，浪费了大量的空间
• 所以如果每个节点存储的数据太少，从因中找到我们需要的数据，就要访问更多的节点，意味着跟磁盘交互次数就会过多。
• 如果是机械硬盘时代，每次从磁盘读取数据需要10ms左右的寻址时间，交互次数越多，消耗的时间越多

比如上面这张图，我们一张表里面有 6 条数据，当我们查询 id=37 的时候，要查询两个子节点，就需要跟磁盘交互 3 次，如果我们有几百万的数据呢？这个时间更加难以 估计。

所以我们的解决方案是什么呢？

• 第一个就是让每个节点存储更多的数据
• 第二个，节点上的关键字的数量越多，我们的指针树也越多，也就是意味着可以有更多的分叉。 因为分叉数越多，树的深度就会减少（根节点是 0）。 这样，我们的树是不是从原来的高瘦高瘦的样子，变成了矮胖矮胖的样子？ 这个时候，我们的树就不再是二叉了，而是多叉，或者叫做多路。

为什么不用红黑树

• 只有两路
• 不够平衡

红黑树一般只放在内存里面使用，比如jav中的treemap

多路平衡查找树（B Tree）（分裂、合并）

Balanced Tree 这个就是我们的多路平衡查找树，叫做 B Tree（B 代表平衡）。

跟AVL树一样，B树在枝节点存储键值、数据地址、节点引用。

它有一个特点：分叉数（路数）永远比关键字数多 1。比如我们画的这棵树，每个节 点存储两个关键字，那么就会有三个指针指向三个子节点。

B-Tree的特性：

• 叶节点具有相同的深度，左子树跟右子树的深度一致
• 叶节点的指针为空
• 节点中的数据key从左到右递增排列

在这里需要说明下的是，BTree的结构里每个节点包含了索引值和表记录（数据值）的信息

我们可以按照Map集合这样理解：key=索引，value=表记录，如下图所示：

由于B-Tree的特性，在B-Tree中按key检索数据的算法非常直观：首先从根节点进行二分查找，如果找到则返回对应节点的data，否则对相应区间的指针指向的节点递归进行查找，直到找到节点或找到null指针，前者查找成功，后者查找失败。B-Tree上查找算法的伪代码如下：

BTree_Search(node, key) {
    if(node == null) return null;
    foreach(node.key)
    {
        if(node.key[i] == key) return node.data[i];
            if(node.key[i] > key) return BTree_Search(point[i]->node);
    }
    return BTree_Search(point[i+1]->node);
}
data = BTree_Search(root, my_key);

比如我们要在这张表里面查找 15。

• 因为 15 小于 17，走左边。
• 因为 15 大于 12，走右边。
• 在磁盘块 7 里面就找到了 15，只用了 3 次 IO。

这个是不是比 AVL 树效率更高呢？

那 B Tree 又是怎么实现一个节点存储多个关键字，还保持平衡的呢？跟 AVL 树有什 么区别？

• 比如 Max Degree（路数）是 3 的时候，我们插入数据 1、2、3，在插入 3 的时候， 本来应该在第一个磁盘块，但是如果一个节点有三个关键字的时候，意味着有 4 个指针， 子节点会变成 4 路，所以这个时候必须进行分裂。把中间的数据 2 提上去，把 1 和 3 变 成 2 的子节点。
• 如果删除节点，会有相反的合并的操作。 注意这里是分裂和合并，跟 AVL 树的左旋和右旋是不一样的。 我们继续插入 4 和 5，B Tree 又会出现分裂和合并的操作。
  

也就是说，由于插入删除新的数据记录会破坏B-Tree的性质，因此在插入删除时，需要对树进行一个分裂、合并、转移等操作以保持B-Tree性质

从这里我们也可以看到，在更新索引的时候会有大量的索引的结构的调整，所以解释了为什么我们不要在频繁更新的列上建索引，或者为什么不更新主键。节点的分裂和合并，其实就是InnoDB页的分裂和合并

B+Tree（加强版多路平衡查找树）

B-Tree有许多变种，其中最常见的是B+Tree，例如MySQL就普遍使用B+Tree实现其索引结构。

• 与B-Tree相比，B+Tree有以下不同点：
• 每个节点的指针上限为2d而不是2d+1。
• 内节点不存储data，只存储key；叶子节点不存储指针。

由于并不是所有节点都具有相同的域，因此B+Tree中叶节点和内节点一般大小不同。这点与B-Tree不同，虽然B-Tree中不同节点存放的key和指针可能数量不一致，但是每个节点的域和上限是一致的，所以在实现中B-Tree往往对每个节点申请同等大小的空间。

带有顺序访问指针的B+Tree

一般在数据库系统或文件系统中使用的B+Tree结构都在经典B+Tree的基础上进行了优化，增加了顺序访问指针。

如上图所示，

• 在B+Tree的每个叶子节点增加一个指向相邻叶子节点的指针，就形成了带有顺序访问指针的B+Tree。
• 做这个优化的目的是为了提高区间访问的性能，例如上图中如果要查询key为从18到49的所有数据记录，当找到18后，只需顺着节点和指针顺序遍历就可以一次性访问到所有数据节点，极大提到了区间查询效率。

小结：

• B+树是一个平衡的多叉树，从根节点到每个叶子节点的高度差不超过1，而且同层级的节点之间由指针相互链接
• 在B+树上的常规检索，从根节点到叶子节点的查找效率基本相当，不会出现大幅波动，而且基于索引的顺序扫描时，也可以利用双向指针快速只有移动，效率非常高
• 因此，B+树索引被广泛应用于数据库、文件系统等场景

MySQL中的B+Tree

MySQL 中的 B+Tree 有几个特点：

• 它的关键字的数量是和路数相等的
• B+Tree的根节点和枝节点中都不存储数据，只有叶子节点才存储数据。搜索到关键字不会直接返回，会到最后一层的叶子节点。比如搜索 id=28，虽然在第一 层直接命中了，但是全部的数据在叶子节点上面，所以我还要继续往下搜索，一直到叶 子节点。

举个例子：假设一条记录是 1K，一个叶子节点（一页）可以存储 16 条记录。非叶 子节点可以存储多少个指针？ 假设索引字段是 bigint 类型，长度为 8 字节。指针大小在 InnoDB 源码中设置为 6 字节，这样一共 14 字节。非叶子节点（一页）可以存储 16384/14=1170 个这样的 单元（键值+指针），代表有 1170 个指针。

树 深 度 为 2 的 时 候 ， 有 1170^2 个 叶 子 节 点 ， 可 以 存 储 的 数 据 为 1170117016=21902400。

在查找数据时一次页的查找代表一次 IO，也就是说，一张 2000 万左右的表，查询 数据最多需要访问 3 次磁盘。所以在InnoDB中B+树深度一般为1~3层，它就能满足千万级的数据存储

• B+Tree的每个叶子节点增加了一个指向相邻叶子节点的指针，它的最后一个数据会指向下一个叶子节点的第一个数据，形成了一个有序链表的结构。
• 它是根据左闭右开的区间 [ )来检索数据。

我们来看下B+Tree的数据查找过程

• 比如我们要查找28，在根节点就找到了键值，但是因为它不是叶子节点，所以会继续往下找，28是[28,66)的左闭右开的区间的临界值，所以会走中间的子节点，然后继续查找，，它又是[28,34)的左闭右开的区间的临界值，所以会走左边的子节点，最后 在叶子节点上找到了需要的数据。
• 第二个，如果是范围查询，比如要查询从 22 到 60 的数据，当找到 22 之后，只 需要顺着节点和指针顺序遍历就可以一次性访问到所有的数据节点，这样就极大地提高了区间查询效率（不需要返回上层父节点重复遍历查找）。

B-Tree和B+Tree

目前大部分数据库系统及文件系统都采用B-Tree或其变种B+Tree作为索引结构，在本文的下一节会结合存储器原理及计算机存取原理讨论为什么B-Tree和B+Tree在被如此广泛用于索引，这一节先单纯从数据结构角度描述它们。

为什么使用B-Tree（B+Tree）

上文说过，红黑树等数据结构也可以用来实现索引，但是文件系统及数据库系统普遍采用B-/+Tree作为索引结构，这一节将结合计算机组成原理相关知识讨论B-/+Tree作为索引的理论基础。

一般来说，索引本身也很大，不可能全部存储在内存中，因此索引往往以索引文件的形式存储的磁盘上。这样的话，索引查找过程中就要产生磁盘I/O消耗，相对于内存存取，I/O存取的消耗要高几个数量级，所以评价一个数据结构作为索引的优劣最重要的指标就是在查找过程中磁盘I/O操作次数的渐进复杂度。换句话说，索引的结构组织要尽量减少查找过程中磁盘I/O的存取次数。下面先介绍内存和磁盘存取原理，然后再结合这些原理分析B-/+Tree作为索引的效率。

主存存取原理

目前计算机使用的主存基本都是随机读写存储器（RAM），现代RAM的结构和存取原理比较复杂，这里本文抛却具体差别，抽象出一个十分简单的存取模型来说明RAM的工作原理。

从抽象角度看，主存是一系列的存储单元组成的矩阵，每个存储单元存储固定大小的数据。每个存储单元有唯一的地址，现代主存的编址规则比较复杂，这里将其简化成一个二维地址：通过一个行地址和一个列地址可以唯一定位到一个存储单元。图5展示了一个4 x 4的主存模型。

主存的存取过程如下：

• 当系统需要读取主存时，则将地址信号放到地址总线上传给主存，主存读到地址信号后，解析信号并定位到指定存储单元，然后将此存储单元数据放到数据总线上，供其它部件读取。

• 写主存的过程类似，系统将要写入单元地址和数据分别放在地址总线和数据总线上，主存读取两个总线的内容，做相应的写操作。

这里可以看出，主存存取的时间仅与存取次数呈线性关系，因为不存在机械操作，两次存取的数据的“距离”不会对时间有任何影响，例如，先取A0再取A1和先取A0再取D3的时间消耗是一样的。

磁盘存取原理

上文说过，索引一般以文件形式存储在磁盘上，索引检索需要磁盘I/O操作。与主存不同，磁盘I/O存在机械运动耗费，因此磁盘I/O的时间消耗是巨大的。

图6是磁盘的整体结构示意图。

一个磁盘由大小相同且同轴的圆形盘片组成，磁盘可以转动（各个磁盘必须同步转动）。在磁盘的一侧有磁头支架，磁头支架固定了一组磁头，每个磁头负责存取一个磁盘的内容。磁头不能转动，但是可以沿磁盘半径方向运动（实际是斜切向运动），每个磁头同一时刻也必须是同轴的，即从正上方向下看，所有磁头任何时候都是重叠的（不过目前已经有多磁头独立技术，可不受此限制）。

图7是磁盘结构的示意图。

盘片被划分成一系列同心环，圆心是盘片中心，每个同心环叫做一个磁道，所有半径相同的磁道组成一个柱面。磁道被沿半径线划分成一个个小的段，每个段叫做一个扇区，每个扇区是磁盘的最小存储单元。为了简单起见，我们下面假设磁盘只有一个盘片和一个磁头。

当需要从磁盘读取数据时，系统会将数据逻辑地址传给磁盘，磁盘的控制电路按照寻址逻辑将逻辑地址翻译成物理地址，即确定要读的数据在哪个磁道，哪个扇区。为了读取这个扇区的数据，需要将磁头放到这个扇区上方，为了实现这一点，磁头需要移动对准相应磁道，这个过程叫做寻道，所耗费时间叫做寻道时间，然后磁盘旋转将目标扇区旋转到磁头下，这个过程耗费的时间叫做旋转时间。

局部性原理与磁盘预读

由于存储介质的特性，磁盘本身存取就比主存慢很多，再加上机械运动耗费，磁盘的存取速度往往是主存的几百分分之一，因此为了提高效率，要尽量减少磁盘I/O。为了达到这个目的，磁盘往往不是严格按需读取，而是每次都会预读，即使只需要一个字节，磁盘也会从这个位置开始，顺序向后读取一定长度的数据放入内存。这样做的理论依据是计算机科学中著名的局部性原理：

当一个数据被用到时，其附近的数据也通常会马上被使用。

程序运行期间所需要的数据通常比较集中。

由于磁盘顺序读取的效率很高（不需要寻道时间，只需很少的旋转时间），因此对于具有局部性的程序来说，预读可以提高I/O效率。

预读的长度一般为页（page）的整倍数。页是计算机管理存储器的逻辑块，硬件及操作系统往往将主存和磁盘存储区分割为连续的大小相等的块，每个存储块称为一页（在许多操作系统中，页得大小通常为4k），主存和磁盘以页为单位交换数据。当程序要读取的数据不在主存中时，会触发一个缺页异常，此时系统会向磁盘发出读盘信号，磁盘会找到数据的起始位置并向后连续读取一页或几页载入内存中，然后异常返回，程序继续运行。

B-/+Tree索引的性能分析

上文说过一般使用磁盘I/O次数评价索引结构的优劣。先从B-Tree分析，

根据B-Tree的定义，可知检索一次最多需要访问h个节点。数据库系统的设计者巧妙利用了磁盘预读原理，将一个节点的大小设为等于一个页，这样每个节点只需要一次I/O就可以完全载入。为了达到这个目的，在实际实现B-Tree还需要使用如下技巧：

每次新建节点时，直接申请一个页的空间，这样就保证一个节点物理上也存储在一个页里，加之计算机存储分配都是按页对齐的，就实现了一个node只需一次I/O。

B-Tree中一次检索最多需要h-1次I/O（根节点常驻内存），渐进复杂度为$O(h)=O(lo{g}_{d}N)$。一般实际应用中，出度d是非常大的数字，通常超过100，因此h非常小（通常不超过3）【B-Tree的度越大，h越小】。

综上所述，用B-Tree作为索引结构效率是非常高的。

而红黑树这种结构，h明显要深的多。由于逻辑上很近的节点（父子）物理上可能很远，无法利用局部性，所以红黑树的I/O渐进复杂度也为O(h)，效率明显比B-Tree差很多。

上文还说过，B+Tree更适合外存索引，原因和内节点出度d有关。从上面分析可以看到，d越大索引的性能越好，而出度的上限取决于节点内key和data的大小：

floor表示向下取整。由于B+Tree内节点去掉了data域，因此可以拥有更大的出度，拥有更好的性能。

问题

谈谈你对B+树的理解

• B+树是基于B树和叶子节点顺序访问指针进行实现的，它具有B树的平衡性，并且通过该顺序访问指针来提高区间查询的性能
• 在B+树中，一个节点中的key从左到右非递减排列。如果某个指针的左右相邻key分别是key i和key i+1，而且不为NULL，则该指针指向节点的所有key大于等于key i而且小于key i+1
• 进行查找操作时，首先在根节点进行二分查找，找到一个key所在的指针，然后递归的在指针所指向的节点进行查找，知道找到叶子节点，然后在叶子节点上进行二分查找，找到key所对应的data
• 插入、删除操作会破坏平衡树的平衡性，因此在插入删除之后，需要对数进行一个分裂、合并、旋转等操作来维护平衡性

为什么 InnoDB 存储引擎选用 B+ 树而不是 B 树？

用B+树不用B树考虑的是IO对性能的影响，B树的每个节点都存储数据，而B+树只有叶子节点才存储数据，所以在查找相同量的情况下，B树的高度更高、IO更频繁。

• 它是Btree的变种，B Tree能解决的问题，它都能解决（B Tree 解决的两大问题 是什么？【每个节点存储更多的关键字；路数更多】）
• 排序能力更强
  • 因为叶子节点上有下一个数据区的指针，数据形成了链表
• 扫库，扫表能力更强
  • 比如如果我们要对表进行全表扫描，只需要遍历叶子节点就可以了，不需要遍历整颗B+Tree拿到所有的数据
• B+Tree的磁盘读写能力相对于B Tree来说更强
  • 空间利用率更好：根节点和枝节点不保存数据区，所以一个节点可以保存更多的关键字，一次磁盘加载的关键字更多
  • IO交互次数更少。相对的，IO读写次数也就降低了。可减少I/O次数，磁盘读写代价更低。
• 效率更加稳定
  • B+Tree永远是在叶子节点拿数据，所以IO次数是稳定的
  • B树搜索有可能会在非叶子结点结束，越靠近根节点的记录查找时间越短，只要找到关键字即可确定记录的存在，其性能等价于在关键字全集内做一次二分查找。