MySql索引的底层数据结构

索引的应用场景

1、什么是索引

       首先我们看一张表，这个表有两个列Col1和Col2，我们要查Col2 = 77的那条数据，这时候我们会写一条SQL：

SELECT * FROM my_table WHERE col2 = '77'

       这样从表“my_table”中可以获得“col2”为“77”的数据记录。

       我们在查询的过程中都希望查询速度能尽可能的快，如果正常情况就下是顺序查找，遍历my_table这张表，看Col2是否等于77，然而这种复杂度为O(n)的算法在数据量很大的情况下是很糟糕的。所以，在数据之外，数据库系统还维护着满足特定查找算法的数据结构，这些数据结构以某种方式引用（指向）数据，这样就可以在这些数据结构上实现高级查找算法。这种数据结构，就是索引。还是用这个表我们看一个例子。

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

       因此，总结一下索引的定义，就是索引是帮助MySQL高效获取数据的排好序的数据结构。

2、索引的应用场景

      根据索引的定义，我们可以思考一下它的实际应用场景：

      1、根据某个值精确快速查找。

      2、根据区间值的上下限来快速查找此区间的数据。

      3、索引值需要排好序，并支持快速顺序查找和逆序查找。

      接下来我们思考哪些数据结构满足这样的条件。

索引的数据结构

1、二叉树（Binary Search Trees）

       二叉树是每个结点最多有两个子树的树结构。通常子树被称作“左子树”（left subtree）和“右子树”（right subtree）。二叉树常被用于实现二叉查找树和二叉堆。二叉树有如下特性：

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

       光看概念有点枯燥，假设我们现在有这样一组数[35 27 48 12 29 38 55]，顺序的插入到一个二叉树的结构中，步骤如下：

       1）插入35为根节点

      

        2）插入27，由于比35小，所以为35的左子树

       

        3）插入48，由于比35大，所以为35的右子树

        4）插入12，由于比35小所以在35的左边，但是35的子节点数已经超过2个，所以要往下找27，成为27的左子树

    

         5）插入29，依然比35小，但是比27大，所以成为27的右子树

         

         6）插入38，由于比35大所以在35的右边，但是35的节点已经超过2个，所以找到48，因为比48小，所以成为48的左子树

         

        7）插入55，因为比35大，也比48大，所以成为48的右子树

            

       经过所有的插入，一个二叉树就完成了。

       但是二叉树也存在问题，就是当待插入的数组升序排列时，比如之前的数组[35 27 48 12 29 38 55]变成了[12 27 29 35 38 48 55]，会出现单边插入的问题，我们根据规则再插一次，会发现二叉树变成了这个形状：

                

       这时候二叉树完完全全就变成了一个链表，为了规避这样的一个问题，于是有了平衡二叉树。

2、平衡二叉树(AVL Trees)

       平衡二叉树是一种特殊的二叉树，所以他也满足前面说到的二叉树的两个特性，同时还有一个特性：

       它的左右两个子树的高度差的绝对值不超过1，并且左右两个子树都是一棵平衡二叉树。

       我们看一下它是如何规避这样的问题的，还是之前的问题数组[12 27 29 35 38 48 55]，我们根据新的规则来做一次插入：

       1）插入12，为根节点

      

       2）插入27，因为比12大，所以成为12的右子树

      

        3）插入29，由于比12和27大，所以成为27的右子树，但是这样根节点左子树的高度是0，右子树的高度是2，左右子树高度差的绝对值大于1，不满足平衡二叉树的特性，因此将树左旋保持平衡。

      

        4）插入35，因为比27和29大，所以成为29的右子树

       

       5）插入38，由于比35大，所以为35的右子树，这样29的左子树的高度为0，右子树的高度为2，左右子树的高度差的绝对值超过1，不满足平衡二叉树的特性，所以需要将以29为根节点的树左旋以保持平衡。

       6）插入48，由于比38大，所以为38的右子树，这样根节点左子树的高度为1，右子树的高度为3，左右子树高度差绝对值大于1，不满足平衡二叉树的特性，所以需要将以27为根节点的树左旋以保证平衡。

     

       7）插入55，由于比48大，所以为48的右子树，这样38的这个节点左子树的高度为1，右子树的高度高度为3，左右子树高度差绝对值大于1，不满足平衡二叉树特性，所以需要以38为根节点的树左旋以保持平衡。

      

       这样插入数据就结束了，很显然平衡二叉树不会因为排序的原因而退化成一个链表，完美的解决了这个问题 。根据特性，一颗平衡二叉树的节点的个数是由它的高度决定的，假设树的高度为h，那每一层最多容纳的结点数量为2^(n-1)，整棵树最多容纳节点数为2^0+2^1+2^2+...+2^(h-1)。这样计算，100w数据树的高度大概在20左右，那也就是说从有着100w条数据的平衡二叉树中找一个数据，最坏的情况下需要20次查找。然而数据库的数据是放到磁盘中的，每次的查找需要消耗一次I/0，这个时候I/O就占到了性能的关键因素，有没有可能让I/O的次数变少一些呢？

3、B-Tree

       B树有如下特征：

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

       B-Tree中的每个节点根据实际情况可以包含大量的关键字信息和分支，如下图所示为一个3阶的B-Tree：

       每个节点占用一个盘块的磁盘空间，一个节点上有两个升序排序的关键字和三个指向子树根节点的指针，指针存储的是子节点所在磁盘块的地址。两个关键词划分成的三个范围域对应三个指针指向的子树的数据的范围域。以根节点为例，关键字为17和35，P1指针指向的子树的数据范围为小于17，P2指针指向的子树的数据范围为17~35，P3指针指向的子树的数据范围为大于35。

       模拟查找关键字29的过程：

1. 根据根节点找到磁盘块1，读入内存。【磁盘I/O操作第1次】
2. 比较关键字29在区间（17,35），找到磁盘块1的指针P2。
3. 根据P2指针找到磁盘块3，读入内存。【磁盘I/O操作第2次】
4. 比较关键字29在区间（26,30），找到磁盘块3的指针P2。
5. 根据P2指针找到磁盘块8，读入内存。【磁盘I/O操作第3次】
6. 在磁盘块8中的关键字列表中找到关键字29。

       分析上面过程，发现需要3次磁盘I/O操作，和3次内存查找操作。由于内存中的关键字是一个有序表结构，可以利用二分法查找提高效率。而3次磁盘I/O操作是影响整个B-Tree查找效率的决定因素。B-Tree相对于AVLTree缩减了节点个数，使每次磁盘I/O取到内存的数据都发挥了作用，从而提高了查询效率。 

4、B+Tree

       从B-Tree结构图中可以看到每个节点中不仅包含数据的key值，还有data值。而每一个页的存储空间是有限的，如果data数据较大时将会导致每个节点（即一个页）能存储的key的数量很小，当存储的数据量很大时同样会导致B-Tree的深度较大，增大查询时的磁盘I/O次数，进而影响查询效率。在B+Tree中，所有数据记录节点都是按照键值大小顺序存放在同一层的叶子节点上，而非叶子节点上只存储key值信息，这样可以大大加大每个节点存储的key值数量，降低B+Tree的高度。

     B+Tree相对于B-Tree有几点不同：

1. 有k个子树的中间节点包含有k个元素（B树中是k-1个元素），每个元素不保存数据，只用来索引，所有数据都保存在叶子节点。
2. 所有的叶子节点中包含了全部元素的信息，及指向含这些元素记录的指针，且叶子节点本身依据关键字的大小自小而大顺序链接。
3. 所有的中间节点元素都同时存在于子节点，在子节点元素中是最大（或最小）元素。

       针对B-Tree优化，由于B+Tree的非叶子节点只存储键值信息，假设每个磁盘块能存储4个键值及指针信息，则变成B+Tree后其结构如下图所示：

       B+树设计成这个样子有什么好处呢？首先在单行查询和范围查询方面，数据量相同的情况下B+树的阶数更小，这样可以带来更少的I/O。其次B-树的性能并不稳定，最好情况是找到根节点，最差情况是找到叶子节点，而B+树每次都是叶子节点，相对于B-树来说查询性能更稳定；而且所有的叶子节点形成有序链表，更适合范围查询。