深入理解Mysql索引底层数据结构与算法

整理下mysql索引底层数据结果和算的的学习笔记

1、为什么索引能够帮助mysql高效获取数据？

• 我们都知道mysql数据是以文件的形式存储在磁盘上的。

• 那磁盘是有一圈一圈的磁道组成的

• 磁头移动到不同磁道，磁盘旋转，这样就可以读取到数据

• 磁盘存取原理 1.寻道时间(速度慢，费时) 2.旋转时间(速度较快)

• cpu读取数据都是从内存去读，内存去磁盘读取数据，内存读取磁盘数据大小都是一页的大小单位，一页=10kb

总结：

1. 通过上面的一些知识，我们知道当磁头移动到另一个磁道读取诗句就是我们常说的一次I/O操作，但是我们知道mysql数据是分布到不同的磁道上的，每次读取数据都要把所有磁道读取一遍，那我们进行I/O次数就很多了，查询效率就很低

2. 那索引就是把索引字段数据的地址保存起来，来帮助mysql直接定位到哪个磁道的哪个扇区，这样就减少I/O操作了，自然查询效率就提高了

2、数据结构那么多，mysql索引为什么要用B+Tree数据结构，而不是其他呢 ？

常见的数据结构

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

a.二叉树

二叉树是n(n>=0)个结点的有限集合，该集合或者为空集（称为空二叉树），或者由一个根结点和两棵互不相交的、分别称为根结点的左子树和右子树组成。

不使用原因：会出现极端情况，一个节点只有一度，就是只有一个子节点，那读取树的一层就是一次I/O，那性能也不好

b.红黑树

红黑树即为平衡二叉树的一种

不使用原因：极端情况下，一个节点有2个子节点，那就出现一层只有2个节点的情况，这种性能也不好

c.Hash

不使用原因：Hash是把索引数据进行Hash算法对应一个地址，我们会发现这个好像性能很好啊，直接找到，但是我们想想，它能满足我们日常开发大部分情况吗？比如通过大于或者小于去筛选数据，所以说也不合适，当然mysql还是提供了Hash索引，毕竟有些场合还是用起来也不错

d.B Tree

1.度(Degree)-节点的数据存储个数 2.叶节点具有相同的深度 3.叶节点的指针为空 4.节点中的数据key从左到右递增排列

不使用原因：虽然解决了每一层的节点数的极端情况下，但是我们会发现每个节点存储了索引和数据，那一层能存储的数据太多也不好,毕竟内存能读取的数据大小就是10kb

e.B+Tree

1.B+Tree(B-Tree变种) 2.非叶子节点不存储data，只存储key，可以增大度 3.叶子节点不存储指针 4.顺序访问指针，提高区间访问的性能

使用原因：设计有几个方面
1.非叶子节点不存储data，只存储key，可以增大度
2.叶子节点不存储指针
3.顺序访问指针，提高区间访问的性能

一个m阶的B+树具有如下几个特征：

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

 

上面的这颗树中，得出结论：

根节点元素8是子节点2,5,8 的最大元素，也是叶子节点6,8 的最大元素；
根节点元素15是子节点11,15 的最大元素，也是叶子节点13,15 的最大元素；
根节点的最大元素也就是整个B+树的最大元素，以后无论插入删除多少元素，始终要保持最大的元素在根节点当中。
由于父节点的元素都出现在子节点中，因此所有的叶子节点包含了全部元素信息，并且每一个叶子节点都带有指向下一个节点的指针，形成了一个有序链表。

 

 

B+Tree索引的性能分析

• 一般使用磁盘I/O次数评价索引结构的优劣

• 预读：磁盘一般会顺序向后读取一定长度的数据(页的整数倍)放入内存

• 局部性原理：当一个数据被用到时，其附近的数据也通常会马上被使用

• B+Tree节点的大小设为等于一个页，每次新建节点直接申请一个页的空间，这样就保证一个节点物理上也存储在一个页里，就实现了一个节点的载入只需一次I/O

• B+Tree的度d一般会超过100，因此h非常小(一般为3到5之间)

 不同的存储引擎有不同的索引实现

 1.MyISAM索引实现(非聚集) 2.InnoDB索引实现(聚集)

.MyISAM索引实现(非聚集)

--->MyISAM索引文件和数据文件是分离的

b.InnoDB索引实现(聚集)

1.数据文件本身就是索引文件 2.表数据文件本身就是按B+Tree组织的一个索引结构文件 3.聚集索引-叶节点包含了完整的数据记录 4.为什么InnoDB表必须有主键，并且推荐使用整型的自增主键？ 5.为什么非主键索引结构叶子节点存储的是主键值？(一致性和节省存储空间)

联合索引结构

---->联合索引的底层存储结构长什么样？

联合索引(col1, col2,col3)也是一棵B+Tree，其非叶子节点存储的是第一个关键字的索引，而叶节点存储的则是三个关键字col1、col2、col3三个关键字的数据，且按照col1、col2、col3的顺序进行排序。

注：对应地址指的是数据记录的地址。

如图，联合索引(年龄, 姓氏,名字)，叶节点上data域存储的是三个关键字的数据。且是按照年龄、姓氏、名字的顺序排列的。

因此，如果执行的是：
select * from STUDENT where 姓氏='李' and 名字='安';
或者
select * from STUDENT where 名字='安';
那么当执行查询的时候，是无法使用这个联合索引的。因为联合索引中是先根据年龄进行排序的。如果年龄没有先确定，直接对姓氏和名字进行查询的话，就相当于乱序查询一样，因此索引无法生效。因此查询是全表查询。

如果执行的是：
select * from STUDENT where 年龄=1 and 姓氏='李';
那么当执行查询的时候，索引是能生效的，从图中很直观的看出，age=1的是第一个叶子节点的前6条记录，在age=1的前提下，姓氏=’李’的是前3条。因此最终查询出来的是这三条，从而能获取到对应记录的地址。
如果执行的是：
select * from STUDENT where 年龄=1 and 姓氏='黄' and 名字='安';
那么索引也是生效的。

而如果执行的是：
select * from STUDENT where 年龄=1 and 名字='安';
那么，索引年龄部分能生效，名字部分不能生效。也就是说索引部分生效。

因此我对联合索引结构的理解就是B+Tree是按照第一个关键字进行索引，然后在叶子节点上按照第一个关键字、第二个关键字、第三个关键字…进行排序

 

索引最左前缀原理

而之所以会有最左原则，是因为联合索引的B+Tree是按照第一个关键字进行索引排列的