MySQL索引底层数据结构与算法

索引

索引：是帮助MySQL高效获取数据的排好序的数据结构；
理论上，可以用来作为索引数据结构的由：

1. 二叉树
2. 红黑树
3. Hash表
4. B-Tree
5. B+Tree

二叉树

二叉树的样子：

特点：

• 每个父结点最多只有两个子节点；
• 父节点的左子节点值小于父节点的值；
• 父节点的右子节点值大于父节点的值；

缺点：
对于数据库记录自增情况，会退化为一个链表，如下图；

那么遍历6结点时，就会进行6次磁盘IO，非常耗性能；
数据量大时，树的层数太深，导致IO次数变多；

红黑树

符合二叉查找树、平衡二叉树的特性。
红黑树的特性：
1）每个结点是红色或者黑色；
2）根节点是黑结点；
3）每个叶子结点都是黑色结点；
4）一个节点是红色的、则它的子节点必须是黑色的；
5）任意一结点到每个叶子结点的路径所包含的黑结点数量是一致的。

红黑树具有平衡二叉树的特性，当左子树与右子树的层数超过2时，会发生平衡操作，使左子树与右子树的层数小于等于1；

红黑树能自平衡，靠三种操作：左旋、右旋和变色。


变色：结点的颜色由红变黑或由黑变红。需要变色的情况：插入数据出现，父子结点红红相连且父父节点的另一个子结点（隔壁）也是红色，需要变色，父父节点的2个红色子结点变黑色，父父节点变为红色。

通过左旋，右旋，就可以防止二叉树退化为链表的情况。
如果有6个结点，查找任意一个结点，做多只需要3次IO就可以找到值。

B-Tree

B树是多叉树，每个结点可包含多个值。
形状如下图所示：
B树的特点（不存在两个重复值的结点）：
1. 叶节点具有相同的深度，叶节点的指针为空；
2. 所有节点不重复
3. 结点中的数据索引从左到右增增排序；
4. 叶节点都存放着索引的数据值data；

B树与红黑树的主要区别：
B树每个结点可以存放多个索引结点，而红黑树与二叉树只可以存放一个索引结点；这就导致了B树的层数远小于红黑树，减少IO次数；

B+Tree

B+树在B树的基础上进行了优化，形状如下图：

B+树特点：
1）非叶子结点不存放数据，只存储冗余索引，可以放更多的索引结点。
2）叶子结点包含了所有索引字段，只有在叶子结点中，才会存放索引结点数据值data;
3）叶子结点使用指针连接，提高区间访问性能；

B树与B+树的区别

1. B+树只有在叶子结点才存储索引数据，非叶子结点不存储索引数据，B树非叶子结点可以存储索引数据；
2. B+树叶子结点中包括了所有的索引以及所有数据，B树只出现非子节点没有出现过的索引数据。
3. B+树叶子结点使用指针相连，而B树不存在指针相连；

进而衍生了MySQL为什么使用B+树的原因

MySQL采用B+树作为索引数据结构的原因

1. 树的高度
   由于B+树非叶子结点不存放索引数据data,这就导致了B+树的每个结点可以存放更多的索引关键字，这样就导致了B+树高度小于B树，一次性读入内存中的需要查找的关键字也就越多。相对来说IO读写次数也就降低了。
2. 检索速率
   由于B+树只有在叶子结点才存放所有具体数据值，所有每一次检索都需要从根节点搜索到叶子结点，而B树的非叶子结点存放了具体的数据，所以遍历可能一次就找到，也可能遍历很多次才找到。
3. 范围查询
   由于B+树叶子结点存在相连的指针，当需要范围查询时，把结点指针指向的下一个结点获取出来即可，而B树没有指针相连，需要进行全表扫描。

Hash表

结构如下图所示：

特点：

1. 对索引的key进行一次hash计算可以定位数据存储的位置；
2. Hash索引要比B+树更高效
3. 仅能满足“=”，“in”等值查询，不支持范围查询；
4. 存在哈希冲突的问题；

B+树与Hash表

MySQL索引底层数据结构只有两种，一种是B+树，另一种是Hash表。

业务中，查询只使用到了等值查询，那么可以使用Hash表，这样只需要一次IO，就可以查找到数据，大大提高检索效率；

MyISAM索引实现

特点（非聚集）：
MyISAM的索引文件和数据文件是分开的；

检索数据过程：
当检索时，从索引的根节点检索到叶子结点叶子结点包含了数据记录存放的地址，然后再根据该地址去数据文件里查找数据。

InnoDB索引实现（聚集/聚簇）

形状如下；

特点：

1. 表数据文件就是按B+Tree组织的一个索引结构文件
2. 聚集索引的叶子结点包含了完整的数据记录；
3. 二级索引的叶子结点只存储主键值；

PS：主键索引就是聚簇索引；

检索数据过程

第一种：
如果是主键索引检索数据，根据主键ID值，从根节点检索到叶子结点，然后直接从叶子结点里面将数据获取出来；
第二种：
如果是从辅助索引检索，从辅助索引B+树根节点检索到叶子结点，辅助索引的叶子结点存放的索引记录与主键编号；
拿到该主键编号后，再去检索主键索引的B+树，重复第一种过程，拿到数据记录，

从辅助索引里拿到主键ID编号再检索主键聚簇索引的过程称为回表；

为什么建议InnoDB表必须建主键，并且推荐使用整型的自增主键？

• 必须设置主键原因：
  当我们数据库表不建主键时，MySQL会根据我们每一列的值，如果该列的值大部分不一样，不重复率越高，使用该列作为主键的优先级就越大，如果实在没有这种列，MySQL会使用隐藏列rowid作为主键，建立一颗主键聚簇索引B+树；

• 这个过程会耗费性能，检索每一列值不重复率，想想就知道。能我们做的事情为什么交给MySQL来做…

• 推荐使用整型的自增主键：
  整型主键：我实习的公司数据库表使用的是32位UUID字符串作为主键，这种UUID在数据量达到几百万时，检索效率明显低于整型主键。

• 原因：
  1）整型主键（int,bigint）,占用4字节或者8字节，明显小于UUID，每个树节点存放的索引结点更多，将树节点读入内存时，可以读取更多的结点。而且字符串是逐位比较的，明显慢于数字比较。
  2）自增主键：
  如果不使用自增，由于B+树具有平衡树的特性，自增是把数据放入链表的尾部，非自增可能把元素插在中间，发生平衡，分页操作的概率会更大，平衡与分页的操作又是比较耗时的，在并发量大的时候，降低系统性能；所以建议自增，这样发生分裂和平衡的概率比较小。

聚簇索引的好处：

• 按照聚簇索引排列顺序，查询显示一定范围数据的时候，由于数据都是紧密相连，数据库不用从多个数据块中提取数据，所以节省了大量的io操作。
• 聚簇索引的限制：
  对于mysql数据库目前只有innodb数据引擎支持聚簇索引，而Myisam并不支持聚簇索引。

复合索引

有些人就好奇了，那么复合索引长什么样？如下图所示：

树的排序根据复合索引列从左到右，依次排序，如果第一列多个数据相等，就会比较第二列的多个数据进行排序，如果第二列的多个值相等，那么就会比较第三列的多个数据，再进行排序。

同样的复合索引是二级索引，如果没有索引覆盖的情况，也会发生回表操作；

B+树索引结点的总量

存储所有的数量计算：
第一层：假设单个索引字节大小为8B，而指向索引区间结点（就是白色格子）大小为6B. 每层的索引的大小为16kB，计算可得16KB / 14B =1170 ,即第一层可以存放1170个索引。
假设排列的第一层：1 -> 1170 ->2340->3510->…每次增加1170个。最大限度。
第二层：即白色格子指向的下一层的字节点。也能存放1170元素。
第三层存放数据，假设数据大小为1KB, 那么第二层每个白色格子指向的区间最多只有 16KB/1KB=16；将每个白色格子铺满。
总个数为：1170 * 1179 * 16 大约为两千万

这也就意味着：单表检索千万条数据，只需要3次磁盘IO就可以完成，速度非常快。