一篇文章彻底搞明白Mysql索引底层数据结构与算法、B+树、存储引擎实现、联合索引（深度剖析）

---

---

前言

索引是帮助MySQL高效获取数据的排好序的数据结构

---

一、Mysql为什么需要索引？

首先需要明白我们的表数据都是写在磁盘、硬盘上的；
每次表插入数据，不一定是连续存放的，比如A表第一条数据插入之后，过了一会才插入第二条数据，但是由于磁盘写数据是一个磁道一个磁道进行写入的，可能在你写入第一条数据之后，有其他程序把这个磁盘写满了，所以下一条数据可能写到了其他地方，所以这就造成了我们一张表的数据可能是随机分布也可能是连续存储的。

日常我们使用MYSQL 一个select可能查询到5条数据，假如这5条数据，分别都在5个不同的磁盘上，意味着我们要进行5次IO，所以为了减少IO交互（IO减少了，性能就提高了），基于此索引诞生了。

---

二、索引数据结构-优劣分析

2.1、二叉树

特性

1、二叉树有根节点，左子树，右子树组成；
2、左边的元素是小于父元素的，右边的元素是大于他的父元素的
3、树节点，都是从根节点开始查找；

缺点

比如ID是递增的，因为二叉树的特性，依次插入1-7，就成了一个链表形式，那这时候比如要查找6这个元素，起码要找6次，遇到这种情况效率是相对比较低下的；

---

2.2、红黑树

红黑树又被称为特殊的二叉平衡树，节点的特性和二叉树一致，只不过会自动做平衡。

特性

1、当一边比另外一边高度高的比较多的时候，会自动做平衡；
2、非叶子节点最多只允许存在2个子节点存在，大于2则做平衡；
3、每个非叶子节点的分布，都是左边小于当前值，右边大于当前值

如果还是ID递增的案例，依次插入1-7，如图

缺点
数据量大的场景，效率可能直线下滑。

假如数据量有500W，树的高度会很高很高，如果我**要查找元素，位于底端叶子节点**，我们一般情况都是从根节点开始查找，
要查找很多次，才能查找到，效率就会非常低；

PS：
上面2个树都有一个本质问题，查找效率和树的高度是相关的，所以控制树的高度，效率就会快很多；

---

2.3、Hash

特性

1、查找时，对索引的key进行一次hash计算就可以定位出数据存储的位置，然后去这个链表遍历到匹配的，
就可以拿到索引的磁盘文件地址，可能一次磁盘IO就拿到数据了。效率非常快

2、很多时候Hash索引要比B+ 树索引更高效

缺点

1、仅能满足 “=”，“IN”，不支持范围查询，所以范围查询时，不好定位，只能全表扫描，用不到索引。
2、hash冲突问题

---

2.4、B树（多叉树）

特性：

1、一个节点可以有多个元素，减少去磁盘寻址次数，提高效率；
2、节点中的数据索引从左到右递增排列；
3、所有索引元素不重复
4、叶节点具有相同的深度（所有叶子节点都是在同一列，按从左到右排序），叶节点的指针为空
5、每个节点不仅存放元素地址，还存放元素值

为了控制树的高度，在一开始划分空间的时候，划分的大一点，这样就可以横向去放索引，横向放的越多，树的高度就越小(也就是B树)；

既然知道了树的高度决定了查询效率，所以想尽可能的横向放更多的元素。

缺点

1、B树每个元素不仅存放元素地址，还存放元素的data,这就造成了节点横向放的元素相对有限，因为每一个节点的空间是有限的。
2、B树的查找性能不稳定，查找根节点和查找叶子结点的性能是完全不同的，B+树因为所有data都只存储在叶子结点，所以查找性能稳定

---

2.5、B+Tree

B树的进阶版本， Mysql采用的就是B+树作为底层数据结构。
mysql表数据都储存在磁盘上，一般情况下，mysql的安装目录下的data目录下，一个数据文件就是一个数据库实例，这个实例下存放的就是数据库表。

特性

1、非叶子节点不存储data，只存储索引(冗余)，可以放更多的索引	
2、叶子节点包含所有索引字段	
3、叶子节点用指针连接，提高区间访问的性能（存储这个节点在磁盘上的位置，会存储相邻节点的位置,对于范围查找，如> ，in等很有帮助）
4、节点中的数据索引从左到右递增排列，排好序的

缺点

索引会有重复

---

三、B+树解析

3.1、B+树的构建

当前这个节点插满后，会把节点的第一个元素（最小的），提上去作为冗余索引，这是这个B+树的构建原则；

---

3.2、B+树如何查找数据的？过程是什么样的？

比如查询一个字段col =18；

以上图为例，进行了3次查找

1、从根节点开始查找，会把根节点整个节点的元素(这里指的是第一层，也就是顶层节点元素)，会全部把他们放到内存里面去。
2、然后用这个18去内存里面做比对，mysql会用折半查找/二分查找算法，可以快速定位到18，他在15-56之间，这个15-56之间存储的是15这个数据页，在磁盘文件上面的地址。
3、然后把15这个数据页的元素，也加载到内存，然后继续用二分查找，然后定位到18应该位于15-20这个区间位置。
4、然后这个里面下一节点的位置，然后也放到内存，然后在内存里比对，最终找到了18，然后把18所在的磁盘文件地址拿出来，去磁盘上找这个元素；

PS：
每次把找到的元素放到磁盘上，就是一次IO操作，IO操作相比在内存里比对而言，比对是非常快的，耗时长的主要是IO操作；

---

3.3、mysql默认page大小

可以看到默认大小是16384（16k），官方没有提供修改的参数，但是可以通过其他手段进行修改。

SHOW GLOBAL STATUS like 'Innodb_page_size';

3.4、MYSQL用B+树是如何解决在千万级数据里很快查找到想要的数据的？

还是以上面那个图为例；

mysql他是把根/页节点的大小差不多默认设置为16K，
假如用bigint做主键，bigInt占用字节为8个字节，然后每出现一个索引节点，他旁边就会出现下一个叶节点，叶节点占6个字节（C语言给存放内存地址的占6个字节），16KB/(8+6)=1170个元素，叶子节点可能会特殊点，他有一个dada元素，这个data元素可能会有点大，他可能是15所在的文件地址，也可以是15的其他列；
一般来说1行记录1KB差不多来算（1KB一行，16K就是16所以），假如B+树全部被撑满，1170117016=2190W数据，他的树的高度只有3，只需要经过3次就可以找到我们想要的元素；

对于高版本mysql来说，**他会把根页节点常驻在内存里，
甚至有可能把所有的非叶子节点都放到内存里面去**，这样速度就会特别特别快了，
这样查找数据可能费时间的就是把叶子节点ROOT出来的时间（这里我理解为把叶子节点放到内存里，比对）

所以这就是为什么Mysql采用B+树作为索引的原因！

---

3.5、MYSQL为什么在B树和B+树之间，选择了B+树？

还是拿这个B树图举例说明

1、按前面得到的结论，B树一个元素最大差不多为1KB，一个叶节点才16KB，如果要想存放2000W数据，可以想象，B树的高度会非常高！
2、B+树的高度取决于非叶子节点能存放多少元素决定的，放的越多，树高度越小，这个是最关键的！
3、B树相邻元素之间没有指针，如果进行范围查找，如图，要找49后面的元素，还要再次从上面56-77区间查找下来，而B+树有指针，可以少很多不必要的查找。

---

3.6、B+树插入过程模拟

这里依次插入1-7模拟ID自增场景
假设最大根节点可以存放4个节点场景；

1、依次插入1-4，插入到4的时候，达到了最大值，他就会分裂成下图：

2、然后接着插入5

3、然后插入6，插入6的时候3456这一个节点，等于最大值了，所以这里会继续分裂

PS：

这里有一个点需要注意，原本右下角达到了4个元素，认为他会分裂，但是 为什么0003和0004也被分裂出去了？
个人理解，这是因为B+树包含很多小的二叉平衡树，为了保持二端的平衡，可以提高搜索效率。

---

3.7、B树和B+树区别

B树

非叶子节点也存放了data：
由于非叶子节点也存放了data，那可以存放索引的空间就会相对变小，就会导致树的高度变高，从而影响搜索效率；
叶子节点之间没有指针：
当范围查找时，如where name > 20，需要一个个区间里去查找，每次都要从根节点从头开始找起；

B+树

非叶子节点不存放data：
好处是可以横向的存放更多索引，从而有效控制树的高度，提高搜索效率；
B+树的高度，是由非叶子节点，能横向存放多少个索引元素决定的，存储同样的数据，他的高度就越小；
叶子节点直接存在双向指针：
当范围查找时，无需从根节点一个个开始找，直接通过指针，找到相邻的数据；

补充

都存放到了叶子节点上了，意味着叶子节点，存放着这张表的所有索引；
冗余索引：取每一页的第一个元素，第一个元素其实是处于索引里的中间元素

---

四、Mysql存储引擎

指的是数据库表的存储引擎；

4.1、这里有个概念聚集和非聚集索引

聚集索引：叶子节点包含了完整的数据记录（innodb存储引擎），就叫聚集索引；
非聚集索引：叶节点不包含完整数据（MyISAM储存引擎），存储的是磁盘文件地址，索引和数据分开存储的叫非聚集索引；

---

4.2、MyISAM存储引擎索引实现

MyISAM存储引擎结构图

MyISAM会有3个文件，MYD/MYI/frm

frm文件：**数据表结构**
MYD文件：**数据**
MYI文件：**索引**

走索引的情况下取数据流程：

1、mysql找到匹配的索引后，先去MYI（索引）文件里，找到这个索引下存放的磁盘文件地址；
2、拿到磁盘文件地址后，根据这个地址去MYD（数据）文件里去定位具体的数据行，拿到所有数据；

PS：所以MyISAM索引文件和数据文件是分离的，是(非聚集索引)

---

4.3、InnoDB存储引擎索引实现

InnoDB存储引擎结构图

---

4.3.1、主键索引

可以看到叶子结点储存的是索引和实际的所有值。

---

4.3.2、二级索引（普通索引）

如下图
Alice这个值（他是一个name字段）被作为一个普通索引，他存储的是实际是上图主键的18这个值。
下面这个sql是要查询name为Alice的所有值，这种情况会进行一个回表查询。

select * from table where name = 'Alice'

---

4.3.3、回表查询（主键所有和二级索引的区别）

通过下图可以看到他的叶子结点，下面储存的data只是一个主键值，并没有其他值？那是如何获取到所有值的呢？他通过主键id去回表在去查询一次，就获取到了最终值。

如果是这种情况，则不需要进行回表操作，因为他一次查询就已经获取到了id。

select id from table where name = 'Alice'

4.3.4、为什么二级索引不存放所有数据？

这里主要牵扯到2个问题
1、节省储存空间（主键索引和二级索引都储存，会有大量的冗余数据）
2、一致性问题（试想，如果主键索引成功了，二级没成功，就出现了一致性问题，虽然也可以通过其他方式解决，但是就增加了复杂度）

---

InnoDB有2个文件，frm,ibd

frm文件：数据表结构
ibd文件：数据+索引

ibd文件表数据文件本身就是按B+Tree组织的一个索引结构文件，所以他是聚集索引

---

---

4.4、innodb和MyISAM存储引擎的最大区别

MyISAM储存索引和数据，是单独的文件分开储存
innodb储存索引和数据，是同一个文件存储

聚集索引和非聚集索引哪个查找效率高？
其实从结构上就可以看出来，明显聚集更快，因为非聚集还需要在根据地址去找。

---

五、联合索引（复合索引）

多个字段组成成一个索引，要想索引生效，必须按照创建索引的顺序才行。

索引是一个排好序的数据结构。

在彻底搞明白联合索引之前需要知道一个概念最左前缀原则

5.1、最左前缀原则

即按照创建联合索引的先后顺序维护在底层的数据结构里的，所以要想联合索引生效必须按顺序来。

// 1、可生效
select * from table where name =‘Bill’ and age=32
// 2、不会生效
select * from table where age=30

案例1、先匹配name=Bill时，找到后，就不会在匹配后面的数据，只需要在name=Bill的基础上，去进行第二个字段age=32的比较，以此类推，后面是第二、第三字段也是同逻辑。

如果第三个字段都想等就排在一起
（联合主键是不可能3个全部相等，如果非联合主键，可以最终通过主键回表，去比较）；

案例2、由于他略过了name,导致不是一个排好序的情况下，他只会进行全表扫描。

---