Mysql索引知识
1、索引的数据结构
可以作为数据结构的数据类型有哪些?
- Hash 优点:做等值匹配时可以快速获取目标地址。缺点:不可以做范围查找
- 二叉树 优点:递归二分查找法,可以缩小查询时间。缺点:在特定情况下可以让所有节点的L or R没有数据使得数据结构成为一种类似链表的结构。
- AVL树:优点:插入数据时会对数据进行旋转操作,使得数据结构一直为相对平衡的二叉树。缺点:树的高度比较高,对操作系统页浪费较大(4K对齐),与硬盘的交互次数太多,IO消耗比较大。
- B树 优点:多路平衡操作树,树的高度比较小,可以做到对操作系统页的充分利用,减小与硬盘的交互次数,IO交互比较少。缺点:各个节点都存在数据。每次查询的耗费时间相差比较大。
- Mysql自定义 B+树:与B树相比父节点不存数据(左闭合),所有的数据都保存在叶子节点,有利于排序,查找的效率趋于稳定(每次查询的时间消耗差不多)
2、Mysql索引的落地实现
mysql的驱动是插拔式设计,索引的实现又驱动去做,在不同的驱动中索引的实现会有不同。比如常见的INODB和MyISAM驱动两者的索引实现就略有不同。
MyISAM驱动时索引实现会生成一个索引文件和一个数据文件,在索引文件中索引的子节点存放在数据区的指针。当有主键索引和辅助索引时在索引文件中会生成主键索引的B+tree和辅助索引的B+tree
(两个树的结构一样,两种B+树是同级别的关系,不存在主从关系,一条sql是可以使用到两种索引的index merge)。
INODB驱动时索引实现看不到index文件,因为它的实现是一种聚集索引的实现,将数据与主键索引放在一起进行实现(inodb有且只有一个聚集索引,当表中没有设置主键时会默认rowid为主键索引)数据结构还是B+tree,辅助索引的实现也是B+tree与主键索引的B+tree不同的是,叶子节点存放的是主键id存主键id的原因是为了实现主键索引与辅助索引的解耦。(当通过辅助索引列进行查找数据时,是先查到主键id,然后再在主键索引tree查找数据,在没有出现覆盖索引的情况,会出现回表查询的操作)。
联合索引:多例组合在一起的索引(使用原则:带头大哥不能死,中间兄弟不能断。最左前置原则,最长用列>离线度高>最小空间。要知道详情请留言)
覆盖索引:要找的数据正好就是索引列的数据,减少回表查询效率提高一倍多。(因此sql 不建议写select * 就是为了利用覆盖索引)
3、sql的评判
三星索引:这个一搜太多了,我就不写了。我得睡觉去了。
欢迎大家留言吐槽,激励我找出更多的真相