MySql引擎比较

• 在介绍MySql索引以及底层数据结构之前想先对比一下MySql的几种存储引擎

基础数据结构介绍

基础数据类型介绍：
1. AVL树（二叉平衡搜索树，所有节点左右子树高度差不超过1）
2. 红黑树（最深子树高度不会超过最浅子树高度的2倍）
3. B树
4. B+树
5. Hash
最好的演示网站：https://www.cs.usfca.edu/~galles/visualization/Algorithms.html

MySql数据结构的选择

首先来分析一下几种基础数据类型

1. Hash表的索引格式

Hash表的索引格式首先需要计算Hash值，然后用mod方法确定值存储的位置，如果该位置已经有值，那就是Hash冲突，需要在该值下面的链表尾添加数据

缺点：

1、利用hash存储的话需要将所有的数据文件添加到内存，比较耗费内存空间
2、如果所有的查询都是等值查询，那么hash确实很快，但是在企业或者实际工作环境中范围查找的数据更多，而不是等值查询，因此hash就不太适合了

2. 二叉树和红黑树的索引

二叉树比较简单，介绍一下红黑树特点

红黑树特点：
1. 每个节点非红即黑
2、根节点是黑色
3、每个叶节点（叶节点即树尾端NULL指针或NULL节点）都是黑的
4、如图所示，如果一个节点是红的，那么它的两个儿子都是黑的
5、对于任意节点而言，其到叶子节点树NULL指针的每条路径都包含相同数目的黑节点
6、每条路径都包含相同的黑节点
7、红黑树确保没有一条路径会比其他路径长出两倍

1、二叉树每个节点的左子树上的值都小于该节点，右子树的值都大于该节点，如果频繁插入和删除节点，最后二叉树就会退化为一个拍好序的链表，而影响查找
2、AVL严格平衡二叉树，节点删除和插入时都需要通过旋转来保持二叉树的平衡性，所有叶子节点的高度差不超过1，AVL保持了良好的查找特性，但是插入和删除的旋转操作复杂且耗时，所以AVL树只适合大量查找，但是修改不频繁的场景
3、红黑树是一种简化后的二叉树，它的要求没有那么严格，最深子树高度不会超过最浅子树高度的2倍

缺点：

无论是二叉树还是红黑树，都会因为树的深度过深而造成io次数变多，影响数据读取的效率

3. B树索引格式

B树特点：
1、所有键值分布在整颗树中
2、搜索有可能在非叶子结点结束，在关键字全集内做一次查找,性能逼近二分查找
3、每个节点最多拥有m个子树
4、根节点至少有2个子树
5、分支节点至少拥有m/2颗子树（除根节点和叶子节点外都是分支节点）
6、所有叶子节点都在同一层、每个节点最多可以有m-1个key，并且以升序排列

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

查找关键字过程：
1、根据根节点找到磁盘块 1，读入内存。【磁盘 I/O 操作第 1 次】
2、比较关键字 28 在区间（16,34），找到磁盘块 1 的指针 P2。
3、根据 P2 指针找到磁盘块 3，读入内存。【磁盘 I/O 操作第 2 次】
4、比较关键字 28 在区间（25,31），找到磁盘块 3 的指针 P2。
5、根据 P2 指针找到磁盘块 8，读入内存。【磁盘 I/O 操作第 3 次】
6、在磁盘块 8 中的关键字列表中找到关键字 28。 

缺点：

1、每个节点都有key，同时也包含data，而每个页存储空间是有限的，如果data比较大的话会导致每个节点存储的key数量变小
2、当存储的数据量很大的时候会导致深度较大，增大查询时磁盘io次数，进而影响查询性能

4. MySql索引数据结构--B+Tree

B+Tree是在BTree的基础之上做的一种优化，变化如下：
1、B+Tree每个Key右边的子节点是大于等于该节点的值，这样就可以把Data都存在叶子节点
2、B+Tree每个节点可以包含更多的节点，这个做的原因有两个，第一个原因是为了降低树的高度，第二个原因是将
数据范围变为多个区间，区间越多，数据检索越快
3、非叶子节点存储key，叶子节点存储key和数据
4、叶子节点两两指针相互连接（符合磁盘的预读特性），顺序查询性能更高

注意：

在B+Tree上有两个头指针，一个指向根节点，另一个指向关键字最小的叶子节点，而且所有叶子节点（即数据节点）之间是一种链式环结构。因此可以对 B+Tree 进行两种查找运算：一种是对于主键的范围查找和分页查找，另一种是从根节点开始，进行随机查找。

InnoDB的B+Tree索引

1、InnoDB是通过B+Tree结构对主键创建索引，然后叶子节点中存储记录，如果没有主键，那么会选择唯一键，如果没有唯一键，那么会生成一个6位的row_id来作为主键（主键索引不允许为空，唯一索引允许为空）
2、如果创建索引的键是其他字段，那么在叶子节点中存储的是该记录的主键，然后再通过主键索引找到对应的记录,叫做回表

索引中的常见技术名词

1. 回表

对于普通列的索引，其B+树的叶子节点存储的不是整行的值，而是主键的值，先找到普通索引值，然后再通过主键的B+树查找到整行的值，这个过程称为回表

2. 覆盖索引

如果查询所需要的值就只有主键值，那么通过普通列索引搜索B+树最后得到的就是所需值，不需要回表，这个就称为覆盖索引

3. 最左匹配

where条件中的字段值必须按照索引顺序，不能跳过某个值
例如：name age是索引
select * from emp where name=？（索引生效）
select * from emp where age=？（索引不生效）

4. 索引下推

索引在最初查询时已经经过筛选，不需要在Server再做计算