InnoDB——详细说明索引中B+树的操作和原理

本内容针对Mysql5.x；

索引是应用程序设计和开发的一个重要方面。
若索引太多，应用程序的性能可能会收到影响。
而索引太少，对查询性能又会产生影响。

索引的注意事项：

1. 如果知道数据的使用，从一开始就应该在需要处添加索引，而不是事后才想起添加。
2. 添加索引需要通过监控大量的SQL语句进而从中找到问题。
3. 当然索引也并不是越多越好，索引会占用相当大小的磁盘空间。

InnoDB存储索引概述

InnoDB存储引擎支持下面几种常见的索引：

• B+树索引
• 全文索引
• 哈希索引

同时InnoDB存储引擎的哈希索引是自适应哈希：InnoDB存储引擎会根据表的使用情况自动为表生成哈希索引，无法人为进行干预。
B+树索引就是传统意义上的索引，这是目前关系型数据库系统中查找最为常用和最为有效的索引。

有一个需要注意的问题：B+树索引并不能找到一个给定键值的具体行。

B+树索引能找到的只是被查询数据行所在的页。然后数据库通过把页读入内存，再在内存中进行查找，最后得到要查找的数据。

B+树

在介绍B+树索引之前，我们先介绍与之密切相关的一些算法与数据结构；

为什么不用平衡二叉树（AVL）

平衡二叉树的查找性能是比较高的，但不是最高的，最好的性能需要建立一颗最优二叉树。
但是最优二叉树的建立和维护需要大量的操作，因此，用户一般只需建立一颗平衡二叉树即可。

平衡二叉树的查询速度的确很快，但是维护一颗平衡二叉树的代价是非常大的。通常来说，需要1次或多次左旋和右旋来得到插入或更新后树的平衡性。
因此对一颗平衡树的维护是有一定开销的，不过平衡二叉树多用于内存结构对象中，因此维护的开销相对较小。

什么是B+树

B+树由B树和索引顺序访问方法（ISAM，这也是MyISAM引擎最初参考的数据结构）演化而来。
实际使用过程中几乎已经没有使用B树的情况了。

B+树的定义十分复杂，这里做一个简单的介绍：

B+树是为磁盘或其他直接存储辅助设备设计的一种平衡二叉树。在B+树中，所有记录点都是按键值的大小顺序放在同一层的叶子结点上，由各叶子节点指针进行连接。

叶子结点可以找到所有数据。

下面看一个B+树，其高度为2，每页可存放4条记录，扇出（fan out，子树？）为5。
所有记录都在叶子节点上，并且是顺序存放的。

B+树的插入操作

B+树的插入必须保证插入后叶子节点中的记录依旧有序，同时需要考虑插入到B+树的三种情况，每种情况都可能会导致不同的插入算法。

初始状态

Leaf Page满，Index Page未满

Leaf Page满，Index Page满

最后插入95，需要做两次拆分

可以看出，不管怎么变化，B+树总是会保持平衡。但是为了保持平衡对于新插入的键值可能需要做大量的拆分页（split）操作。
因为B+树结构主要用于磁盘，页的拆分意味着磁盘的操作，所以应该在可能的情况下尽量减少页的拆分操作。

页插入优化（树旋转操作）

由于拆页非常影响性能，B+同样提供了类似平衡二叉树的旋转（Rotatioin）功能。
旋转发生在Leaf Page已经满，但是其的左右兄弟节点没有满的情况下。
这时B+树并不会急于去做拆分页的操作，而是将记录移到所在页的兄弟节点上。通常情况下，左兄弟会先被检查用来做旋转操作。
我们再用上面初始状态的树来讨论，若插入键值70，其实B+树并不会急于去拆分叶子节点，而是去做旋转：

这样的话，采用旋转操作使B+树减少了一次页的拆分操作，同时高度还是2。

B+树的删除操作

B+树使用填充因子（fill factor）来控制树的删除变化，50%是填充因子可设的最小值。

B+树的删除操作同样必须保证删除后叶子节点中的记录依然有序，同插入一样，B+树的删除操作同样需要考虑下吗三种情况，与插入不同的是，删除根据填充因子的变化来衡量。

此处图片略去…

B+树索引

前面讨论的都是B+树的数据结构及其一般操作。

B+树索引的本质就是B+树在数据库中的实现

但是，B+索引在数据库中有一个特点是**高扇出性，**因此在数据库中B+树的高度一般在2～4层。
也就是说查找某一键值的行记录时最多只需要2～4次I/O

数据库中的B+树索引可以分为聚集索引（clustered index）和辅助索引（secondary index）。
但是不管是聚集索引还是辅助索引，内部都是B+树，即高度平衡的，叶子节点存放着所有的数据。
聚集索引与辅助索引不同的是，叶子节点存放的是否是一整行的信息。

聚集索引

聚集索引是按照每张表的主键构造一颗B+树，同时叶子节点中存放的即为整张表的行记录数据，
也将聚集索引的叶子节点称为数据页。

聚集索引的这个特效决定了索引组织表中数据也是索引的一部分。
同B+树数据结构一样，每个数据页都通过一个双向链表来进行连接。
由于实际的数据页只能按照一颗B+树进行排序，因此每张表只能拥有一个聚集索引。
因此，大多数情况下，查询优化器倾向于采用聚集索引。因为聚集索引能够在B+树索引的叶子节点上直接找到数据。
同时，由于定义了数据的逻辑顺序，聚集索引能够特别快地访问针对范围值的查询**。**查询优化器能够快速发现某一段范围的数据页需要扫描。

聚集索引的结构

聚集索引中包含数据页（叶子节点）与非数据页（非叶子节点），同时数据页上存放的是完整的每行的记录。
而在非数据页的索引页中，存放的仅仅是键值及指向数据页的偏移量，而不是一个完整的行记录。
因此这颗聚集索引树的构造大致如下图所示：

聚集索引的存储并不是物理上连续的，而是逻辑上连续的，否则维护成本会非常高。
有两个特点：

1. 数据页会通过双向链表连接，页按照主键的顺序排序
2. 每个页中的记录也是通过双向链表进行维护的，物理存储上可以同样不按照主键存储。

同时，对于主键的排序查找和范围查找，聚集索引的速度都非常的快。

辅助索引

辅助索引（Secondary Index，也称为非聚集索引，叶子节点并不包含行记录的全部数据。

叶子节点中存储索引键值，除此之外，每个叶子节点中的索引行还包含了一个书签（bookmark）。
该书签用来告诉InnoDB存储引擎哪里可以找到与索引相对应的行数据。

由于InnoDB存储引擎是索引组织表，因此InnoDB存储引擎的辅助索引的书签就是相应行数据的聚集索引键。

辅助索引并不影响数据在聚集索引中的组织，因此每张表上可以有多个辅助索引。

如何定位数据

1. InnoDB存储引擎会遍历辅助索引并通过叶级别的指针获得指向主键索引的主键
2. 通过主键索引来找到一个完整的行记录

那么如果对于一颗高度为3的辅助索引，同时聚集索引树的高度同样为3，那么一共需要6次逻辑IO访问才能得到最终的一个数据页。

参考文章