关于MySQL B+Tree索引的一点理解

关于MySQL B+Tree索引的一点理解
我们都知道索引对于良好的性能非常重要，特别是当表中的数据量越来越大时，索引对性能的影响愈发重要。那么怎么创建一个最优索引以及如何通过优化索引来提高查询性能，这都需要一个前提条件：深入理解索引结构。我们只有知道索引的底层存储结构，才能更好的创建和优化索引。
MySQL是目前主流数据库中的一种，各大互联网公司都在使用，我们今天就来了解下MySQL中的索引结构。MySQL中，索引是在存储引擎层而不是服务器层实现的。所以我们主要讲InnoDB存储引擎的B+Tree索引。
在讲B+Tree之前我们先了解下二叉查找树，平衡二叉树（AVLTree）和平衡多路查找树（B-Tree），B+Tree即是由这些树逐步演化来的。
二叉查找树
二叉查找树又叫二叉排序树或二叉搜索树，首先它是一个二叉树，并且满足一下的特性：
1. 若左子树不为空，则左子树上所有节点的值均小于它的根节点的值
2. 若右子树不为空，则右子树上所有节点的值均大于它的根节点的值
3. 左右子树叶均为二叉查找树
4. 没有键值相等的结点

下图就是一棵二叉查找树：

 

该二叉树在进行节点查找时，最坏情况下也就是遍历到树的叶子节点需要比较3次，平均查找次数为（1+2+2+3+3+3）/ 6 = 2.3次
二叉查找树可以随意的构造，同样是2，3，4，5，6，8这几个数字，也可以按照下图的方式来构造：

 

该树最坏情况下的查找次数为5次，平均查找次数为（1+2+3+4+5+5）/6 = 3.3次。很明显该树的查询效率要比上面的树低。因此若想二叉查找树的效率尽可能高，需要该树是平衡的，即平衡二叉树（AVL树）。
平衡二叉树（AVL树）
平衡二叉树是在符合二叉查找树的前提下，还满足任何节点的两个字树的高度差最大为1。下面的图片，左边的是AVL树，它的任何节点的左右子树高度差<=1；右边的不是AVL树，其根节点的左子树高度为3，而右子树高度为1；

 

如果在AVL树中插入节点或删除节点，可能会导致AVL树失去平衡，这种失去平衡的二叉树可以概括为四种姿态：LL（左左），RR（右右），LR（左右），RL（右左），示意图如下：

 

LL：插入或删除一个节点后，根节点的左孩子的左孩子还有非空节点，导致根节点的左子树比右子树高度高2，AVL树失去平衡。
LR：插入或删除一个节点后，根节点的左孩子的右孩子还有非空节点，导致根节点的左子树比右子树高度高2，AVL树失去平衡。
RL：插入或删除一个节点后，根节点的右孩子的左孩子还有非空节点，导致根节点的右子树比左子树高度高2，AVL树失去平衡。
RR：插入或删除一个节点后，根节点的右孩子的右孩子还有非空节点，导致根节点的右子树比左子树高度高2，AVL树失去平衡。
AVL树失去平衡之后，可以通过旋转使其恢复平衡。具体的旋转方法可自行搜索，这里不做过多介绍。
平衡多路查找树（B-Tree）
B-Tree是为磁盘等外存储设备设计的一种平衡查找树。因此在讲B-Tree之前先了解一下磁盘的相关知识。
系统从磁盘读取数据到内存时是以磁盘块（block）为基本单位的，位于同一磁盘块中的数据会被一次性读取出来，而不是需要什么取什么。
InnoDB存储引擎中有页的概念，页是磁盘管理的最小单位。InnoDB存储引擎中默认每个页的大小为16kb，可通过参数innodb_page_size将页的大小设置为4k，8k，16k，在MySQL中可通过如下命令查看页的大小：
mysql> show variables like 'innodb_page_size';
而系统一个磁盘块的存储空间往往没有这么大，因此InnoDB每次申请磁盘空间时都会是若干地址连续磁盘块来达到页的大小16kb。InnoDB在把磁盘数据读入到磁盘时会以页为基本单位，在范围查询数据时如果一个页中的数据既能满足查询的条件，这将减少磁盘I/O的次数，提高查询效率。
B-Tree结构的数据可以让系统高效的找到数据所在的磁盘块。为了描述B-Tree，首先定义一条记录为一个二元组[key,data]，key为记录的键值，data为一行记录中除主键外的数据。
一棵m阶的B-Tree有如下特性：
1. 每个节点最多有m个孩子
2. 除了根节点和叶子节点外，其他每个节点至少有Ceil(m/2)个孩子
3. 若根节点不是叶子节点，则至少有2个孩子
4. 所有叶子节点都在同一层，且不包含其他关键字信息
5. 每个非终端节点包含n个关键字信息（p0,p1,p2....pn,k1,k2...kn）,n满足：ceil(m/2)-1<=n<=m-1
6. ki(i=1,2....n)为关键字，且关键字升序排序
7. pi(i=1,2....n)为指向子树根节点的指针。p(i-1)指向的子树的所有节点关键字均小于ki，但都大于k(i-1)

根据以上的特性，我们来构造一个3阶的B-Tree，如下图：

 

B-Tree中每个节点根据实际情况可以包含大量的关键字信息。每个节点占用一个盘块的磁盘空间，一个节点上有两个升序排序的关键字和三个指向子树根节点的指针，指针存储的是子节点所在磁盘块的地址。两个关键词划分成的三个范围域对应三个指针指向的子树的数据的范围域。以根节点为例，关键字为17和35，P1指针指向的子树的数据范围为小于17，P2指针指向的子树的数据范围为17~35，P3指针指向的子树的数据范围为大于35。
查找关键字28的过程：
1. 将根节点所在的磁盘块1读入内存。【磁盘I/O操作第1次】
2. 比较关键字28在15～35之间，找到磁盘块1的指针p2
3. 根据p2指针找到磁盘块3，读入内存。【磁盘I/O操作第2次】
4. 比较关键字28在21～29之间，找到磁盘块3的指针p2
5. 根据p2指针找到磁盘块7，读入内存。【磁盘I/O操作第3次】
6. 在磁盘块7中的关键字列表找到关键字28

分析上面过程，发现需要3次磁盘I/O操作，和3次内存查找操作。由于内存中的关键字是一个有序表结构，可以利用二分法查找提高效率。而3次磁盘I/O操作是影响整个B-Tree查找效率的决定因素。B-Tree相对于AVLTree缩减了节点个数，使每次磁盘I/O取到内存的数据都发挥了作用，从而提高了查询效率。
B+Tree
B+Tree是在B-Tree基础上的一种优化，使其更适合实现外存储索引结构。
从上一节中的B-Tree结构图中可以看到每个节点中不仅包含数据的key值，还有data值。而每一个页的存储空间是有限的，如果data数据较大时将会导致每个节点（即一个页）能存储的key的数量很小，当存储的数据量很大时同样会导致B-Tree的深度较大，增大查询时的磁盘I/O次数，进而影响查询效率。在B+Tree中，所有数据记录节点都是按照键值大小顺序存放在同一层的叶子节点上，而非叶子节点上只存储key值信息，这样可以大大加大每个节点存储的key值数量，降低B+Tree的高度。
B+Tree相对于B-Tree有以下几点优化：
1. 非叶子节点只存储键值信息
2. 所有叶子节点之间都有一个链指针
3. 数据记录都存放在叶子节点中

将上一节的B-Tree优化，变成B+Tree后其结构如下图：

 

通常在B+Tree上有两个头指针，一个指向根节点，另一个指向关键字最小的叶子节点，而且所有叶子节点（即数据节点）之间是一种链式环结构。因此可以对B+Tree进行两种查找运算：一种是对于主键的范围查找和分页查找，另一种是从根节点开始，进行随机查找。
由于图中空间有限，无法真正的模拟出表中千万级数据量的场景，我们做一个推算：
InnoDB存储引擎中页的大小为16KB，一般表的主键类型为INT（占用4个字节）或BIGINT（占用8个字节），指针类型也一般为4或8个字节，也就是说一个页（B+Tree中的一个节点）中大概存储16KB/(8B+8B)=1K个键值（因为是估值，为方便计算，这里的K取值为〖10〗^3）。也就是说一个深度为3的B+Tree索引可以维护10^3 * 10^3 * 10^3 = 10亿条记录。
实际情况中每个节点可能不能填充满，因此在数据库中，B+Tree的高度一般都在2~4层。MySQL的InnoDB存储引擎在设计时是将根节点常驻内存的，也就是说查找某一键值的行记录时最多只需要1~3次磁盘I/O操作。
数据库中的B+Tree索引可以分为聚簇索引（clustered index）和辅助索引（secondary index）。上面的B+Tree示例图在数据库中的实现即为聚簇索引，聚簇索引的B+Tree中的叶子节点存放的是整张表的行记录数据。辅助索引与聚簇索引的区别在于辅助索引的叶子节点并不包含行记录的全部数据，而是存储相应行数据的聚簇索引键，即主键。当通过辅助索引来查询数据时，InnoDB存储引擎会遍历辅助索引找到主键，然后再通过主键在聚簇索引中找到完整的行记录数据。