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的时候称为覆盖索引,这时候并不需要到聚簇索引中查找其他的值,效率会有所小提高。