MySQL索引原理

开篇

Innodb存储引擎实现索引的数据结构是B+树，下面介绍几种数据结构，一步步阐述为什么要使用B+树

二叉查找树

二叉查找树中，左子树的键值总是小于跟的键值，右子树的键值总是大于跟的键值。通过中序遍历得到键值：2、3、5、6、7、8。

是二叉查找树是可以任意构建，如构造如图：

但是这样跟顺序查找就差不多，所以就引用了平衡二叉树的思想，AVL树。

平衡二叉树

定义：符合二叉查找树的定义，其次必须满足任何节点的左右两个子树的高度最大差为1。
平衡二叉树虽然查找速度非常快但是维护一颗平衡二叉树的代价是非常大，通常需要1次或多次左旋和右旋来得到插入或更新后树的平衡性。

B+树

B+树索引的构造类似于二叉树，根据键值快速找到数据。但是B+树中的B不是代表二叉，而是代表平衡。

定义：

(1)根结点只有1个，分支数量范围[2,m]。
(2)除根以外的非叶子结点，每个结点包含分支数范围[[m/2],m]，其中[m/2]表示取大于m/2的最小整数。
(3)所有非叶子节点的关键字数目等于它的分支数量。
(4) 所有叶子节点都在同一层，且关键字数目范围是[[m/2],m]，其中[m/2]表示取大于m/2的最小整数。
(5)所有非叶子节点的关键字可以看成是索引部分，这些索引等于其子树（根结点）中的最大（或最小）关键字
(6)叶子节点包含全部关键字的信息(非叶子节点只包含索引)，且叶子结点中的所有关键字依照大小顺序链接(所以一个B+树通常有两个头指针，一个是指向根节点的root，另一个是指向最小关键字的sqt)。

记录都在叶节点，并且是顺序存放，各个叶节点（页为单位）都是逻辑的连续存放，是一个双向循环链表

为什么要是使用B+树

那么Mysql如何衡量查询效率呢？磁盘IO次数，B-树（B类树）的特定就是每层节点数目非常多，层数很少，目的就是为了就少磁盘IO次数，当查询数据的时候，最好的情况就是很快找到目标索引，然后读取数据，使用B+树就能很好的完成这个目的，但是B-树的每个节点都有data域（指针），这无疑增大了节点大小，说白了增加了磁盘IO次数（磁盘IO一次读出的数据量大小是固定的，单个数据变大，每次读出的就少，IO次数增多，一次IO多耗时啊！），而B+树除了叶子节点其它节点并不存储数据，节点小，磁盘IO次数就少。这是优点之一。
另一个优点是什么，B+树所有的Data域在叶子节点，一般来说都会进行一个优化，就是将所有的叶子节点用指针串起来。这样遍历叶子节点就能获得全部数据，这样就能进行区间访问啦。

聚集索引、非聚集索引

聚集索引与非聚集索引的区别是：叶子节点是否存放一整行记录

聚集索引

InnoDB是索引组织表，聚集索引就是按照每张表的主键构造一颗B+树，所以完整的数据记录都存在聚集索引的叶子节点上
实际数据也只能按照一颗B+树进行排序，因此每张表只能拥有一个聚集索引。

非聚集索引

InnoDB 的辅助索引叶子节点保存主键键值。
当通过辅助索引来寻找数据时，InnoDB存储引擎会遍历辅助索引并通过叶节点主键，然后再通过聚集索引来找到了一个完整的行记录。

两种引擎的差异

MyISAM中查询的时候我们找到叶子节点中保存的地址，然后通过地址我们找到所对应的信息。
InnoDB索引和MyISAM最大的区别是它只有一个数据文件，在InnoDB中，表数据文件本身就是按B+Tree组织的一个索引结构，这棵树的叶节点数据域保存了完整的数据记录。所以我们又把它的主索引叫做聚集索引。而它的辅助索引和MyISAM也会有所不同，它的辅助索引都是将主键作为数据域。所以，这样当我们查找的时候通过辅助索引要先找到主键，然后通过主索引再找到对于的主键，得到信息。
MyISAM的索引文件仅仅保存数据记录的地址。在MyISAM中，主索引和辅助索引（Secondary key）在结构上没有任何区别，只是主索引要求key是唯一的，而辅助索引的key可以重复。如果我们在Col2上建立一个辅助索引，则此索引的结构如下图所示：