【MYSQL】MYSQL 的学习教程（七）之 索引为什么要用 B+ 树

1. 树

树跟数组、链表、堆栈一样，是一种数据结构。它由有限个节点，组成具有层次关系的集合。因为它看起来像一棵树，所以得其名。一颗普通的树如下:

关于树的概念：

• 结点的度：一个结点含有的子结点个数称为该结点的度
• 树的度：一棵树中，最大结点的度称为树的度；
• 父结点：若一个结点含有子结点，则这个结点称为其子结点的父结点；
• 深度：对于任意结点n,n的深度为从根到n的唯一路径长，根结点的深度为0；
• 高度：对于任意结点n,n的高度为从n到一片树叶的最长路径长，所有树叶的高度为0；

2. 树的种类

按照有序性，可以分为有序树和无序树：

• 无序树：树中任意节点的子结点之间没有顺序关系
• 有序树：树中任意节点的子结点之间有顺序关系

按照节点包含子树个数，可以分为 B 树和二叉树，二叉树可以分为以下几种：

• 二叉树：每个节点最多含有两个子树的树称为二叉树；
• 满二叉树：叶节点除外的所有节点均含有两个子树的树被称为满二叉树；

• 完全二叉树：如果一颗二叉树除去最后一层节点为满二叉树，且最后一层的结点依次从左到右分布

• 二叉查找树/二叉排序树（为了提高查找效率）：首先它是一颗二叉树，若左子树不空，则左子树上所有结点的值均小于它的根结点的值；若右子树不空，则右子树上所有结点的值均大于它的根结点的值；左、右子树也分别为二叉排序树；

在最好的情况下，二叉排序树的查找效率比较高，是 O(logn)，其访问性能近似于折半查找；

最差时候会是 O(n)，比如插入的元素是有序的，生成的二叉排序树就是一个链表，这种情况下，需要遍历全部元素才行

• 平衡二叉树（AVL：为了保证树不至于太倾斜，尽量保证两边平衡）：是一种二叉排序树。一 棵空树或它的左右两个子树的高度差的绝对值不超过 1，并且左右两个子树都是一棵平衡二叉树。它有几种实现方式：红黑树、AVL树
  • AVL 树：本质上还是一棵二叉搜索树，每个结点的左右子树的高度之差的绝对值（平衡因子）最多为1
    
  • 红黑树，是一种二叉搜索树，但在每个结点上增加一个存储位表示结点的颜色，可以是Red或Black。 通过任何一条从根到叶子的路径上各个结点着色方式的限制，红黑树确保没有一条路径会比其他路径长出俩倍，因而是接近平衡的。因而，红黑树是相对接近平衡的二叉树，并不是一个完美平衡二叉查找树

• 霍夫曼树：带权路径最短的二叉树

一文带你搞定【二叉树】

3. B- 树、B+ 树简介

先看几个概念：

• 阶数：一个节点最多有多少个孩子节点。（一般用字母m表示）
• 关键字：节点上的数值就是关键字
• 度：一个节点拥有的子节点的数量。

3.1 B- 树 简介

B- 树（B 树）是一种平衡的多叉树，它比较适用于对外查找

1. 一颗 m 阶的 B- 树，有以下特征：
2. 根结点至少有两个子女；
3. 每个非根节点所包含的关键字个数 j 满足：⌈m/2⌉ - 1 <= j <= m - 1.(⌈⌉表示向上取整)
4. 有k个关键字(关键字按递增次序排列)的非叶结点恰好有k+1个孩子。
5. 所有的叶子结点都位于同一层
6. 每个节点既保存索引，又保存数据

3.2 B+ 树简介

B+ 树是 B- 树的变体，也是一颗多路搜索树

一棵 m 阶的 B+ 树主要有这些特点：

1. 每个结点至多有m个子女;
2. 非根节点关键值个数范围：m/2 <= k <= m-1
3. 相邻叶子节点是通过指针连起来的，并且是关键字大小排序的。
4. 只有叶子节点保存数据
5. 增加了相邻接点的指向指针

B+ 树和 B- 树的主要区别如下：

1. B 树的每个节点包含键和对应的值，而 B+ 树的非叶子节点只包含键，所有的值都存储在叶子节点中（其所有叶子结点的数据组合起来就是完整的数据）
2. 由于 B+ 树的叶子节点形成了有序链表，范围查询和顺序遍历操作更加高效。在 B 树中，为了找到满足范围查询的键，需要进行额外的搜索操作
3. 在 B 树中，叶子节点存储了所有的键和对应的值，而在 B+ 树中，叶子节点只存储键和相关值的引用（或指针）。这使得B+树的叶子节点可以容纳更多的键，提高了存储空间的利用率
4. B+树查询速度更稳定：B+所有关键字数据地址都存在叶子节点上，所以每次查找的次数都相同，所以查询速度要比B树更稳定（B树在找到具体的数值以后，则结束，而B+树则需要通过索引找到叶子结点中的数据才结束，也就是说B+树的搜索过程中走了一条从根结点到叶子结点的路径）
5. B+树遍历整棵树只需要遍历所有的叶子节点即可，而不需要像B树一样需要对每一层进行遍历，这有利于数据库做全表扫描
6. B- 树中任何一个关键字出现且只出现在一个结点中，而 B+ 树可以出现多次

3.3 B+ 树的查找

因为 B+ 树的数据都是在叶子节点上的，非叶子节点只是指针索引的作用，因此，查找过程需要搜索到叶子节点上。以这颗 B+ 树为例吧：

3.3.1 B+ 树单值查询

假设我们要查的值为 32

第一次磁盘 I/O，查找磁盘块 1，即根节点（36,43），因为 32 小于36，因此访问根节点的左边第一个孩子节点

第二次磁盘 I/O, 查找磁盘块 2，即根节点的第一个孩子节点，获得区间 （28,32），遍历即可得 32

3.3.2 B+ 树范围查询

假设我们要查找区间 [32,40] 区间的值

第一步先访问根节点，发现区间的左端点 32 小于 36,则访问根节点的第一个左子树（28,32）

第二步访问节点（28,32），找到 32，于是开始遍历链表，把 [32,40] 区间值找出来，这也是 B+ 树比 B- 树高效的地方

4、索引为什么使用 B 树/B+ 树？

红黑树等数据结构也可以用来实现索引，但是文件系统及数据库系统普遍采用 B-/+Tree 作为索引结构。
一般来说，索引本身也很大，不可能全部存储在内存中，因此，索引往往以索引文件的形式存储的磁盘上。这样的话，索引查找过程中就要产生磁盘 I/O 消耗，相对于内存存取， I/O 存取的消耗要高几个数量级，所以评价一个数据结构作为索引的优劣最重要的指标就是在查找过程中磁盘 I/O 操作次数的渐进复杂度。换句话说，索引的结构组织要尽量减少查找过程中磁盘 I/O 的存取次数。

4.1、磁盘存取原理

首先我们来看看磁盘的物理结构：

硬盘内部主要部件为磁盘盘片、传动磁臂、读写磁头和转轴，数据主要写入磁盘的盘片上的，盘片又是由若干个扇区构成的，数据写入读取都是以扇区为基本单位的，另外以盘片中心为圆心，把盘片分成若干个同心圆，那每一个划分圆的“线条”，就称为磁道

那么数据是如何读取与写入的呢，主要有三步：

1. 寻道：既然数据是保存在扇区上的，那我首先我们需要知道它到底是在哪个扇区上吧，这就需要先让磁头移动到扇区所在的磁道上，我们把它称为寻道时间，平均寻道时间一般在 3-15ms
2. 旋转延迟: 磁盘移动到扇区所在的磁盘上时，此时的磁头对准的还不一定我们想要的数据对应的扇区，所以需要等待盘片旋转片刻，等到我们想要的数据对应的扇区落到磁头下，旋转延迟取决于磁盘转速，通常用磁盘旋转一周所需时间的 1/2 表示。比如：7200rpm 的磁盘平均旋转延迟大约为 60*1000/7200/2 = 4.17ms，而转速为 15000rpm 的磁盘其平均旋转延迟为 2ms
3. 数据传输：经过前面的两步，磁头终于开始读写数据了，目前 IDE/ATA 能达到 133MB/s，SATA II 可达到300MB/s 的接口数据传输率，数据传输时间通常远小于前两部分消耗时间，可忽略不计

注意：数据传输中的忽略不计是有前提的，即是需要读取连续相邻的扇区，也就是我们常说的顺序 IO，磁盘顺序 IO 的读写速度可以媲美甚至超越内存的随机 IO，所以这部分时间可以忽略不计；但如果要读取的数据是分布在不同的扇区的话，也就变成了随机 IO，随机 IO 毫无疑问增大了寻道时间和旋转延迟，性能是非常堪忧的（典型代表就是上文提到的 回表时大量 id 分布在不同的页上，造成了大量的随机 IO）

4.2、局部性原理与磁盘预读

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

由于磁盘顺序读取的效率很高（不需要寻道时间，只需很少的旋转时间），因此对于具有局部性的程序来说，预读可以提高 I/O 效率。预读的长度一般为页 （ page ） 的整倍数。页是计算机管理存储器的逻辑块，硬件及操作系统往往将主存和磁盘存储区分割为连续的大小相等的块，每个存储块称为一页 （在许多操作系统中，页得大小通常为 4k ） ，主存和磁盘以页为单位交换数据。当程序要读取的数据不在主存中时，会触发一个缺页异常，此时系统会向磁盘发出读盘信号，磁盘会找到数据的起始位置并向后连续读取一页或几页载入内存中，然后异常返回，程序继续运行。

InnoDB 中的数据页默认是 16 KB，而操作系统页是 4 KB，可以指定在读取的起始地址连续读取 4 个操作系统页。所以，读取一个数据页就是一次 IO

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

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

为了达到这个目的，在实际实现 B-Tree 还需要使用如下技巧：每次新建节点时，直接申请一个页的空间，这样就保证一个节点物理上也存储在一个页里，加之计算机存储分配都是按页对齐的，就实现了一个 node 只需一次 I/O 。B-Tree 中一次检索最多需要 h-1 次 I/O（根节点常驻内存），渐进复杂度为 O(h) = O(log_dN) 。一般实际应用中，出度d是非常大的数字，通常超过 100 ，因此 h 非常小（通常不超过 3 ）。综上所述，用 B-Tree 作为索引结构效率是非常高的。而红黑树这种结构， h 明显要深的多。由于逻辑上很近的节点（父子）物理上可能很远，无法利用局部性，所以红黑树的 I/O 渐进复杂度也为 O(h) ，效率明显比 B-Tree 差很多。

上面还说过， B+Tree 更适合外存索引，原因和内节点出度 d 有关。从上面分析可以看到， d 越大，索引的性能越好，而出度的上限取决于节点内 key 和 data 的大小：d_{max} = floor(pagesize / (keysize + datasize + pointsize)) 。floor 表示向下取整。由于 B+Tree 内节点去掉了 data 域，因此可以拥有更大的出度，拥有更好的性能

5. B+ 树经典面试题

5.1 InnoDB一棵 B+ 树可以存放多少行数据？

这个问题的简单回答是：约 2千万 行

• 在计算机中，磁盘存储数据最小单元是扇区，一个扇区的大小是 512 B
• 文件系统中，最小单位是块，一个块大小就是 4 KB
• InnoDB存储引擎最小储存单元是页，一页大小就是 16 KB

因为 B+ 树叶子存的是数据，内部节点存的是键值 + 指针。索引组织表通过非叶子节点的二分查找法以及指针确定数据在哪个页中，进而再去数据页中找到需要的数据；

假设 B+ 树的高度为 2 的话，即有一个根结点和若干个叶子结点。这棵 B+ 树的存放总记录数为 = 根结点指针数 * 单个叶子节点记录行数

• 如果一行记录的数据大小为 1KB，那么单个叶子节点（一页）可以存的记录数 = 16K / 1K = 16
• 非叶子节点内存放多少指针呢？我们假设主键 ID 为 bigint 类型，长度为 8 字节，而指针大小在 InnoD B源码中设置为 6 B，所以就是 8+6 =14 B，16K / 14B = 16 * 1024B / 14B = 1170

因此，一棵高度为 2 的 B+ 树，能存放 1170 * 16=18720 条这样的数据记录。

同理一棵高度为 3 的 B+ 树，能存放 1170 *1170 *16 =21902400，也就是说，可以存放两千万左右的记录。B+ 树高度一般为1-3层，已经满足千万级别的数据存储

5.2 为什么索引结构默认使用 B+ 树，而不是 B-Tree，Hash 哈希，二叉树，红黑树？

• Hash哈希，只适合等值查询，不适合范围查询。
• 一般二叉树，可能会特殊化为一个链表，相当于全表扫描。
• 红黑树，是一种特化的平衡二叉树，MySQL 数据量很大的时候，索引的体积也会很大，内存放不下的而从磁盘读取，树的层次太高的话，读取磁盘的次数就多了。
• B-Tree，叶子节点和非叶子节点都保存数据，相同的数据量，B+ 树更矮壮，也是就说，相同的数据量，B+ 树数据结构，查询磁盘的次数会更少