数据库索引原理

我们先了解下 二叉树——>平衡二叉树——>B-树——>B+树 ，一步步深入了解数据库索引底层原理吧。

 

二叉树（Binary Search Trees）

二叉树是每个结点最多有两个子树的树结构。其中的子树一般被称为“左子树”和“右子树”，二叉树常被用于实现二叉查找树和二叉堆。二叉树有以下特点：

1. 每个结点都包含一个元素以及n个子树，这里0≤n≤2。
2. 左子树和右子树是有顺序的，次序不能任意颠倒。并且左子树的值要小于父结点，右子树的值要大于父结点。

假如我们有一个数组： [35, 27, 48, 12, 29, 38, 55] ，顺序插入到树的结构中(或者转换为树的结构)，结果如下：

经通过一系列的插入操作之后，原本无序的一组数已经变成一个有序的结构了，并且这个树满足了上面提到的两个二叉树的特性。

但如果这个数组经过升序排列后再插入到树中，上述数组变为： [12, 27, 29, 35, 38, 48, 55] ，顺序插到树中，结果变为：

因为每次新插入的数据比已存在的结点数据都大，所以每次都会往结点的右边插入，最终导致这棵树退化为一个线性链表，查找效率自然就低了，为了较大发挥二叉树的查找效率，让二叉树不再偏科，保持各科平衡，所以有了平衡二叉树！

 

平衡二叉树（AVL Trees）

平衡二叉树是一种特殊的二叉树，除了满足前面说到的二叉树的两个特性外，还有一个特性：

• 左右两个子树的高度差的绝对值不超过1 (即 -1 ≤ 0 ≤ 1)，并且左右两个子树都是一棵平衡二叉树。

于是，我们按照上面的升序后的数组依次进行插入到树平衡二叉树中： [12, 27, 29, 35, 38, 48, 55] ，看看结果：

  

  

  

这棵树始终满足平衡二叉树的几个特性，也不会退化为线性链表，我们需要查找一个数的时候就能沿着树根一直往下找，这样的查找效率和二分法查找是一样的。

假设树的高度为h，那每一层最多容纳的结点数量为2^(n-1)，那么整棵树最多容纳节点数2^0+2^1+2^2+...+2^(h-1)。

这样计算，100w数据树的高度大概在20左右，那也就是说从100w条数据的平衡二叉树中找一条数据，最坏的情况下需要20次查找。但是我们数据库中的数据基本都是放在磁盘中的，每读取一个二叉树的结点就要操作一次磁盘IO，这样我们找一条数据如果要操作20次磁盘IO，那性能就成了一个很大的问题了。

 

B-Tree

一棵m阶的B-Tree有以下特性：

1. 每个结点最多有m个子结点。
2. 除了根结点和叶子结点外，每个结点最少有 m/2（向上取整）个子结点。
3. 如果根结点不是叶子结点，那根结点至少包含两个子结点。
4. 所有的叶子结点都位于同一层。
5. 每个结点都包含k个元素（关键字），这里 m/2 ≤ k < m ，m/2向下取整。
6. 每个元素（关键字）左结点的值，都小于或等于该元素（关键字）。右结点的值都大于或等于该元素（关键字）。

下面我们以一个 [0, 1, 2, 3, 4, 5, 6, 7] 的数组插入一颗 3阶 的B-Tree为例：

    

    

    

在二叉树中，每个结点只有一个元素。但是在B-Tree中，每个结点都可能包含多个元素，并且非叶子结点在元素的左右都有指向子结点的指针。

以下图为例，如果我们要在下面的B-Tree中找到关键字24，那流程如下：

从这个流程我们能看出，B-Tree的查询效率好像也并不比平衡二叉树高。但是查询所经过的结点数量要少很多，也就意味着查询一条数据需要更少次的磁盘IO操作（查询次数比平衡二叉树少很多次），这对性能的提升是很大的。

下面我们再看看数据库是如何以 B-Tree的数据结构 存储数据的：

普通的B-Tree的结点中，元素就是一个个的数字。但是上图中，我们把元素部分拆分成了key-data的形式，key就是数据的主键，data就是具体的数据。这样我们在找一条数的时候，就沿着根结点往下找就ok了，效率是比较高的。

 

B+Tree

B+Tree是在B-Tree基础上的一种优化，使其更适合实现外存储索引结构。B+Tree与B-Tree的结构很像，但是也有几个自己的特性：

1. 所有的非叶子节点只存储关键字信息。
2. 所有数据（具体数据）都存在叶子结点中。
3. 所有的叶子结点中包含了全部元素的信息。
4. 所有叶子节点之间都有一个链指针。

上面 B-Tree 的图变成 B+Tree ，结果如下：

比较上面的 B-Tree 和 B+Tree 两幅图：

• 非叶子结点上已经只有key信息了，满足上面第1点特性！
• 所有叶子结点下面都有一个data区域，满足上面第2点特性！
• 非叶子结点的数据在叶子结点上都能找到，如根结点的元素4、8在最底层的叶子结点上也能找到，满足上面第3点特性！
• 注意图中叶子结点之间的箭头，满足满足上面第4点特性！

 

B-Tree or B+Tree？

操作系统从磁盘读取数据到内存是以磁盘块（block）为基本单位的，位于同一个磁盘块中的数据会被一次性读取出来，而不是需要什么取什么。即使只需要一个字节，磁盘也会从这个位置开始，顺序向后读取一定长度的数据放入内存。当一个数据被用到时，其附近的数据也通常会被马上使用。

预读的长度一般为页（page）的整倍数。页是计算机管理存储器的逻辑块，硬件及操作系统往往将主存和磁盘存储区分割为连续的大小相等的块，每个存储块称为一页（在许多操作系统中，页得大小通常为4k）。

如何选择 B-Tree 和 B+Tree ？ 它们的优劣是什么？

1. B-Tree因为非叶子结点也能保存具体数据，所以在查找某个关键字的时候找到即可返回。而B+Tree所有的数据都在叶子结点，每次查找都要得到叶子结点（即查到叶子结点）。所以在同样高度的B-Tree和B+Tree中，B-Tree查找某个关键字的效率更高。
2. 由于B+Tree所有的数据都在叶子结点，并且结点之间有指针连接，在找大于某个关键字或者小于某个关键字的数据的时候，B+Tree只需要找到该关键字然后沿着链表遍历就可以了，而B-Tree还需要遍历该关键字结点的根结点去搜索。（如上图的B-Tree和B+Tree，要查询大于6的数据，B-Tree需要遍历关键字6结点的根结点，即存储关键字4和8这个结点；而B+Tree只需在6叶子结点往下遍历链表就行了）
3. 由于B-Tree的每个结点（这里的结点可以理解为一个数据页）都存储主键+实际数据，而B+Tree非叶子结点只存储关键字信息，而每个页的大小是有限的，所以同一页能存储的B-Tree的数据会比B+Tree存储的更少。这样同样总量的数据，B-Tree的深度会更大，增大查询时的磁盘I/O次数，进而影响查询效率。

 

innodb引擎数据存储

在InnoDB存储引擎中，也有页的概念，默认每个页的大小为16K，也就是每次读取数据时都是读取4*4k的大小。

在某个页内插入新行时，为了不减少数据的移动，通常是插入到当前行的后面或者是已删除行留下来的空间，所以在某一个页内的数据并不是完全有序的。但是为了为了数据访问顺序性，在每个记录中都有一个指向下一条记录的指针，以此构成了一条单向有序链表。

这里我们按顺序排列，由于数据比较少，一个页就能容下，所以只有一个根结点，主键和数据也都是保存在根结点（左边的数字代表主键，右边名字、性别代表具体的数据）。假设我们写入10条数据之后，Page1满了，有个叫“秦寿生”的朋友来了，但是Page1已经放不下数据了，这时候就需要进行页分裂，产生一个新的Page。如下图：

在Innodb内流程如下：

1. 产生新的Page2，然后将Page1的内容复制到Page2。
2. 产生新的Page3，“秦寿生”的数据放入Page3。
3. 原来的Page1依然作为根结点，但是变成了一个不存放数据只存放索引的页，并且有两个子结点Page2、Page3。
4.  

这里有两个问题需要注意的是：

1、为什么要复制Page1为Page2而不是创建一个新的页作为根结点，这样就少了一步复制的开销了？

答：如果是重新创建根结点，那根结点存储的物理地址可能经常会变，不利于查找。并且在innodb中根结点是会预读到内存中的，所以结点的物理地址固定会比较好！

2、原来Page1有10条数据，在插入第11条数据的时候进行裂变，根据前面对B-Tree、B+Tree特性的了解，那这至少是一颗11阶的树，裂变之后每个结点的元素至少为11/2=5个，那是不是应该页裂变之后主键1-5的数据还是在原来的页，主键6-11的数据会放到新的页，根结点存放主键6？

答：如果是这样的话新的页空间利用率只有50%，并且会导致频繁的页分裂。所以innodb对这一点做了优化，新的数据放入新创建的页，不移动原有页面的任何记录。

 

随着数据的不断写入，如下图：

每次新增数据，都是将一个页写满，然后新创建一个页继续写，这里其实是有个隐含条件的，那就是主键自增！主键自增写入时新插入的数据不会影响到原有页，这样做插入的效率高，且页的利用率也高！但是如果主键是无序的或者随机的，那每次的插入可能会导致原有页频繁的分裂，影响插入效率，同时降低页的利用率！这也是为什么在innodb中建议设置主键自增的原因！

在innodb中，如果一个表没有主键，那默认会找建了唯一索引的列，如果也没有，则会生成一个隐形的字段作为主键！

如果这个用户表频繁的插入和删除，那会导致数据页产生碎片，页的空间利用率低，还会导致树变的“虚高”，降低查询效率！这可以通过索引重建来消除碎片提高查询效率！

 

innodb引擎数据查找

数据插入了的查找流程：

1. 找到数据所在的页。这个查找过程就跟前面说到的B+Tree的搜索过程是一样的，从根结点开始查找一直到叶子结点。
2. 在页内找具体的数据。读取第1步找到的叶子结点数据到内存中，然后通过分块查找的方法找到具体的数据。

这跟我们在新华字典中找某个汉字是一样的，先通过字典的索引定位到该汉字拼音所在的页，然后到指定的页找到具体的汉字。innodb中定位到页后用了哪种策略快速查找某个主键呢？这我们就需要从页结构开始了解。

• 左边蓝色区域称为Page Directory，这块区域由多个槽（slot）组成，是一个稀疏索引结构，即一个槽中可能属于多个记录，最少属于4条记录，最多属于8条记录。槽内的数据是有序存放的，所以当我们寻找一条数据的时候可以先在槽中通过二分法查找到一个大致的位置。
• 右边区域为数据区域，每一个数据页中都包含多条行数据。注意看图中最上面和最下面的两条特殊的行记录Infimum和Supremum，这是两个虚拟的行记录。在没有其他用户数据的时候Infimum的下一条记录的指针指向Supremum，当有用户数据的时候，Infimum的下一条记录的指针指向当前页中最小的用户记录，当前页中最大的用户记录的下一条记录的指针指向Supremum，至此整个页内的所有行记录形成一个单向链表。
• 行记录被Page Directory逻辑的分成了多个块，块与块之间是有序的，也就是说“4”这个槽指向的数据块内最大的行记录的主键都要比“8”这个槽指向的数据块内最小的行记录的主键要小。但是块内部的行记录不一定有序。
• 每个行记录的都有一个nowned的区域（图中粉红色区域），nowned标识这个这个块有多少条数据，伪记录Infimum的nowned值总是1，记录Supremum的nowned的取值范围为[1,8]，其他用户记录nowned的取值范围[4,8]，并且只有每个块中最大的那条记录的nowned才会有值，其他的用户记录的n_owned为0。

所以当我们要找主键为6的记录时，先通过二分法在稀疏索引中找到对应的槽，也就是Page Directory中“8”这个槽，“8”这个槽指向的是该数据块中最大的记录，而数据是单向链表结构，无法逆向查找，所以需要找到上一个槽即“4”这个槽，然后通过“4”这个槽中最大的用户记录的指针沿着链表顺序查找到目标记录。

 

聚集索引 & 非聚集索引

前面关于数据存储的都是演示的聚集索引的实现，如果上面的用户表需要以“用户名字”建立一个非聚集索引，如下图

非聚集索引的存储结构与前面是一样的，不同的是在叶子结点的数据部分存的不再是具体的数据，而是数据的聚集索引的key。也就是说，聚簇索引的叶子节点就是数据节点，而非聚簇索引的叶子节点仍然是索引节点，只不过有指向对应数据块的指针。所以通过非聚集索引查找的过程是先找到该索引key对应的聚集索引的key，然后再拿聚集索引的key到主键索引树上查找对应的数据，这个过程称为回表！

 

innodb与MyISAM两种存储引擎对比

我们看看MyISAM主键索引的存储结构，如下图：

1. 主键索引树的叶子结点的数据区域没有存放实际的数据，存放的是数据记录的地址。
2. 数据的存储不是按主键顺序存放的，按写入的顺序存放。

结论：innodb引擎数据在物理上是按主键顺序存放，而MyISAM引擎数据在物理上按插入的顺序存放。并且MyISAM的叶子结点不存放数据，所以非聚集索引的存储结构与聚集索引类似，在使用非聚集索引查找数据的时候通过非聚集索引树就能直接找到数据的地址了，不需要回表，这比innodb的搜索效率会更高。