mysql索引深入解析

mysql索引的学习点

1.什么是B+tree
2.B+tree在两大引擎下的体现形式
3.理解几大索引原则
推荐一个数据结构可视化的网站：https://www.cs.usfca.edu/~galles/visualization/Algorithms.html

写这篇博客的原因

在参与开发的第一个项目中，有一个特别严重的问题就是mysql的sql优化，sql优化的基础就是索引，我发现我根本没有理解mysql的索引，所以我重新学习了一下索引。

什么是索引

索引是为了加速对表中数据行的检索而创建的一种分散存储的数据结构。

上面是一张数据库表和它的索引，索引是为了什么呢，就是为了快速通过ID来查找数据所在的磁盘地址得到数据并返回。

下面看一下B+tree的演化过程

以上是二叉查找树的结构，这个数据结构会一直往下延伸，这个数据结构不大符合要求，为什么不大符合呢？请往下看：

以上是改进的数据结构，叫平衡二叉树，二叉树的形成过程如何呢，请去上面提供的网站查看
为什么平衡二叉树还是不符合数据库的需求呢，原因如下：
1、它太深了：数据处的深度决定着它的IO操作次数，IO操作耗时大
2、它太小了：每个磁盘快保存的数据量太小了，数据量太小就不会很好的利用操作系统和磁盘的数据交换特性（一般一次交换是4k）
也没有利用好磁盘的IO预读能力（空间局部性原理），从而带来频繁的IO操作，什么叫IO预读能力呢，就是每次加载数据，他都会加载临近的数据，以减少IO的操作次数

上面是B-tree，非常关键，说明一点17、35是什么，这个就是索引的值，比如ID做索引，17、35就是ID的值
多路平衡查找树解决了平衡二叉树什么问题呢？
1、它太深的问题，我们可以提升路(也就是P1、P2、P3)，路数提升了存储的数据也就增多了，同样是三层，多路平衡查找树存储的数据绝对比上面的多，这样就可以减少IO操作了

mysql是采用加强版的平衡查找树，为什么会选择这种呢？先看一下两者的区别：


为什么扫库、表能力更强？（不是非常理解）
（1）上层节点没有保存数据区，所以只需要扫叶子节点就行了
（2）叶子节点还是有序排序的
为什么磁盘读写能力更强
（1）上层节点不保存数据区，可以加载更多的数据，也就有更多的关键字，减少IO操作
为什么排序能力更强
叶子节点本身就有天然的排序功能

索引在不同的存储引擎下面如何展现的呢？

在myisam下不同索引的级别是一样的。

在innodb下的索引是不一样的，innodb非常重视主键，其它索引都是找到主键，然后通过主键索引找到数据的，所以这就是为什么innodb推荐使用自增的主键，因为自增的主键在插入数据的时候主键的索引表的左边是不需要变化的，最右边才需要变化。这也是为什么要做左闭合区间

下面看一下结合mysql，如何建立合适的索引

1.在离散性高选择性越好，btree才能更好的搜索，离散性计算公式：count(distinct col)/count(col)
2.最左匹配原则，就是对索引中关键字进行对比，一定是从左往右，且不可跳过


上面的索引name上建立索引，就是重复索引，在增删改就会性能降低，因为最左匹配原则，name是不可跳过的，所以创建下一种即可。

事例

第一点：对，为什么呢？
数据小，能加载的数据就多，加载的关键字就越多，可以减少IO操作。
第二点：对，一张表索引越多，就会造成增删改需要对索引表进行维护，增加性能的消耗
第三点：9999%这个可以，其它两个不行，不满足最左匹配原则
第四点：对，不等于索引是无法计算的
第五点：对，b+tree天然有序
第六点：对，返回想要的列，可以减少IO操作
第七点：对，最左匹配原则
第八点：对，最左匹配原则
第九点：对，最左匹配原则