mysql索引原理以及查询优化理解

mysql作为开源的数据库在很多持久化数据存储的场景中被广泛的使用，接下来记录一下自己对mysql数据存储和查询理解。

---

一.索引

为了优化数据的存储和读取查询mysql设计了索引，有人说过数据结构+算法=程序，那么其实这个索引就是一种数据结构+加上算法而设计来稳定有效的关联数据，下面就先分析一下mysql索引的设计原理。
1.数据结构角度
1.B-TREE mysql早期版本使用

2.B+TREE mysql一般使用这个数据结构,mysql一般是三层的B+TREE

3.hash tree 不做解释
4.FULLTEXT索引（InnoDB引擎5.7以后支持）

B+Tree的定义
B+Tree是B树的变种，有着比B树更高的查询性能，来看下m阶B+Tree特征：

1、有m个子树的节点包含有m个元素（B-Tree中是m-1）

2、根节点和分支节点中不保存数据，只用于索引，所有数据都保存在叶子节点中。

3、所有分支节点和根节点都同时存在于子节点中，在子节点元素中是最大或者最小的元素。

4、叶子节点会包含所有的关键字，以及指向数据记录的指针，并且叶子节点本身是根据关键字的大小从小到大顺序链接。

B+TREE的优势

1.更好的稳定性 从上面两张图片可以看出，B+TREE相较于B-TREE在中间节点是不存储数据data的而是存储在第三层上的叶子节点上，所以B+TREE当数据检索时，总是最终到第三层叶子节点上才能拿到数据，而B-TREE也许在第二层或第一层的中间节点中就拿到了数据，所以B+TREE增加了检索数据的稳定性，因为他总是检索到第三层的叶子节点上。
2.更好的查询优势 从B+TREE的定义中可以知道，叶子节点存储数据的方式是以键值顺序排序的链表存储的，所以如果检索是以键值的一个范围【a,b】查询的时候，他只需要根据a,b两个节点数据查询就能获取到中间的所有节点数据。而B-TREE没有这个特性，当查询【a,b】范围数据时，他每拿到一个数据时都要再次回到根节点再次查询接下来的数据。
图示：
B-TREE查找：
[外链图片转存失败(img-gezThsFK-1565923996089)(https://i.imgur.com/s3DgTiY.png)]
B+TREE查找：
[外链图片转存失败(img-AVq23PVA-1565923996089)(https://i.imgur.com/20A0xmK.png)]

2.逻辑角度
即我们一般说到的索引
1、主键索引
2、单列索引
3、多列索引
4、唯一索引

2.物理存储角度
1.聚簇索引 InnoDB使用的是聚簇索引,叶子节点存储的就是行数据
2.非聚簇索引 MyISM使用的是非聚簇索引,叶子节点存储的是行数据的物理地址

****** InnoDB数据文件其实就是聚簇索引文件，InnoDB总会存在唯一的一个聚簇索引,InnoDB创建聚簇索引的顺序规则为：
1、单调递增的主键索引
2、非空的唯一值索引
3、上面都没有的话，mysql内部自动创建一个6字节的索引
设置主键之前自己手动的先添加上唯一索引，然后再设置主键，这种情况会建立以这个唯一索引的聚簇索引存储
在上述规则下，无论如何都会创建聚簇索引。

****** 除聚簇索引之外我们建立的其他索引会以辅助索引(二级索引)存储，辅助索引较聚簇索引的话他是可以多个的。
辅助索引叶子节点的存储是索引的key值和这行的主键值，当二级索引检索时，他会拿到这个索引的key值和这行数据的主键，如果要获取这行的其他数据，第二步需要拿着这个主键值去聚簇索引中查找。当我们查询的行的值就是辅助索引的key的时候称为覆盖索引，这时候并不需要到聚簇索引中查找其他的值，效率会有所小提高。