1.5万字+30张图盘点索引常见的11个知识点(中篇)

史前动物
发布于 2023-9-15 11:05
浏览
0收藏

二级索引

二级索引也被称为非聚簇索引,本身也就是一颗B+树,一个二级索引对应一颗B+树,但是二级索引B+树存储的数据跟聚簇索引不一样。


聚簇索引前面也说了,叶子节点存的就是我们插入到数据库的数据,非叶子节点存的就是数据的主键id和对应的数据页号。


而二级索引叶子节点存的是索引列的数据和对应的主键id,非叶子节点除了索引列的数据和id之外,还会存数据页的页号。


前面提到的数据页,其实真正是叫索引页,因为叶子节点存的是实际表的数据,所以我就叫数据页了,接下来因为真正要讲到索引了,所以我就将二级索引的页称为索引页,你知道是同一个,但是存储的数据不一样就可以了。

单列索引

假设,我们现在对name字段加了一个普通非唯一索引,那么name就是索引列,同时name这个索引也就是单列索引


此时如果往表中插入三条数据,那么name索引的叶子节点存的数据就如下图所示

1.5万字+30张图盘点索引常见的11个知识点(中篇)-鸿蒙开发者社区

mysql会根据name字段的值进行排序,这里我假设张三排在李四前面,当索引列的值相同时,就会根据id排序,所以索引实际上已经根据索引列的值排好序了。

这里肯定有小伙伴疑问,name字段存储的中文也可以排序么?

答案是可以的,并且mysql支持很多种排序规则,我们在建数据库或者是建表的时候等都可以指定排序规则,并且后面文章涉及到的字符串排序都是我随便排的,实际情况可能不一样

1.5万字+30张图盘点索引常见的11个知识点(中篇)-鸿蒙开发者社区

对于单个索引列数据查找也是跟前面说的聚簇索引一样,也会对数据分组,之后可以根据二分查找在单个索引列来查找数据。

当数据不断增多,一个索引页存储不下数据的时候,也会用多个索引页来存储,并且索引页直接也会形成双向链表

1.5万字+30张图盘点索引常见的11个知识点(中篇)-鸿蒙开发者社区

当索引页不断增多是,为了方便在不同索引页中查找数据,也就会抽取一个索引页,除了存页中id,同时也会存储这个id对应的索引列的值

1.5万字+30张图盘点索引常见的11个知识点(中篇)-鸿蒙开发者社区

当数据越来越多越来越多,还会抽取,也会形成三层的一个B+树,这里我就不画了。

联合索引

除了单列索引,联合索引其实也是一样的,只不过索引页存的数据就多了一些索引列

比如,在name和age上建立一个联合索引,此时单个索引页就如图所示

1.5万字+30张图盘点索引常见的11个知识点(中篇)-鸿蒙开发者社区

先以name排序,name相同时再以age排序,如果再有其它列,依次类推,最后再以id排序。

相比于只有name一个字段的索引来说,索引页就多存了一个索引列。

最后形成的B+树简化为如下图

1.5万字+30张图盘点索引常见的11个知识点(中篇)-鸿蒙开发者社区

小结

其实从上面的分析可以看出,聚簇索引和非聚簇索引主要区别有以下几点

  • 聚簇索引的叶子节点存的是所有列的值,非聚簇索引的叶子节点只存了索引列的值和主键id
  • 聚簇索引的数据是按照id排序,非聚簇索引的数据是按照索引列排序
  • 聚簇索引的非叶子节点存的是主键id和页号,非聚簇索引的非叶子节点存的是索引列、主键id、页号

由于后面这个索引树会经常用到,为了你方便比较,所以我根据上面索引树的数据在表中插入了对应的数据,sql在文末

1.5万字+30张图盘点索引常见的11个知识点(中篇)-鸿蒙开发者社区

实际情况下索引B+树可能并不是按照我图中画出来的那样排序,但不耽误理解。

回表

讲完二级索引,接下来讲一讲如何使用二级索引查找数据。

这里假设对name字段创建了一个索引,并且表里就存了上面示例中的几条数据,这里我再把图拿过来

1.5万字+30张图盘点索引常见的11个知识点(中篇)-鸿蒙开发者社区

那么对于下面这条sql应该如何执行?

select * from `user` where name = '赵六';

由于查询条件是​​name = '赵六'​​,所以会走name索引

整个过程大致分为以下几个步骤:

  • 从最上面那层索引页开始二分查找,我们图中就是索引页113,如果索引页113上面还有一层,就从上面一层二分查找
  • 在索引页113查找到​​赵六​​​在​​王五​​​和​​刘七​​​之间,之后到​​王五​​​对应的索引页111上去查找​​赵六​
  • 在索引页111找到​​赵六​​的第一条记录,也就是id=4的那条
  • 由于是​​select *​​,还要查其它字段,此时就会根据id=4到聚簇索引中查找其它字段数据,这个查找过程前面说了很多次了,这个根据id=4到聚簇索引中查找数据的过程就被称为回表
  • 由于是非唯一索引,所以​​赵六​​​这个值可能会有重复,所以接着就会在索引页111顺着链表继续遍历,如果name还是​​赵六​​​,那么还会根据id值进行回表,如此重复,一直这么遍历,直至name不再等于​​赵六​​为止,对于图示,其实就是两条数据

从上面的二级索引的查找数据过程分析,就明白了回表的意思,就是先从二级索引根据查询条件字段值查找对应的主键id,之后根据id再到聚簇索引查找其它字段的值。

覆盖索引

上一节说当执行​​select * from user where name = '赵六';​​​这条sql的时候,会先从索引页中查出来​​name = '赵六';​​对应的主键id,之后再回表,到聚簇索引中查询其它字段的值。

那么当执行下面这条sql,又会怎样呢?

select id from `user` where name = '赵六';

这次查询字段从​​select *​​​变成​​select id​​,查询条件不变,所以也会走name索引

所以还是跟前面一样了,先从索引页中查出来​​name = '赵六';​​对应的主键id之后,惊讶的发现,sql中需要查询字段的id值已经查到了,那次此时压根就不需要回表了,已经查到id了,还回什么表。

而这种需要查询的字段都在索引列中的情况就被称为覆盖索引,索引列覆盖了查询字段的意思。

当使用覆盖索引时会减少回表的次数,这样查询速度更快,性能更高。

所以,在日常开发中,尽量不要select * ,需要什么查什么,如果出现覆盖索引的情况,查询会快很多。

索引下推

假设现在对表建立了一个name和age的联合索引,为了方便理解,我把前面的图再拿过来

1.5万字+30张图盘点索引常见的11个知识点(中篇)-鸿蒙开发者社区

接下来要执行如下的sql

select * from `user` where name > '王五' and age > 22;

在MySQL5.6(不包括5.6)之前,整个sql大致执行步骤如下:

  • 先根据二分查找,定位到​​name > '王五'​​的第一条数据,也就是id=4的那个赵六
  • 之后就会根据id=4进行回表操作,到聚簇索引中查找id=4其它字段的数据,然后判断数据中的age是否大于22,是的话就说明是我们需要查找的数据,否则就不是
  • 之后顺着链表,继续遍历,然后找到一条记录就回一次表,然后判断age,如此反复下去,直至结束

所以对于图上所示,整个搜索过程会经历5次回表操作,两个赵六,两个刘七,一个王九,最后符合条件的也就是id=6的赵六那条数据,其余age不符和。

虽然这么执行没什么问题,但是不知有没有发现其实没必要进行那么多次回表,因为光从上面的索引图示就可以看出,符合​​name > '王五' and age > 22​​的数据就id=6的赵六那条数据

所以在MySQL5.6之后,对上面的​​age > 22​​判断逻辑进行了优化

前面还是一样,定位查找到id=4的那个赵六,之后就不回表来判断age了,因为索引列有age的值了,那么直接根据索引中age判断是否大于22,如果大于的话,再回表查询剩余的字段数据(因为是​​select *​​),然后再顺序链表遍历,直至结束

所以这样优化之后,回表次数就成1了,相比于前面的5次,大大减少了回表的次数。

而这个优化,就被称为索引下推,就是为了减少回表的次数。

之所以这个优化叫索引下推,其实是跟判断​​age > 22​​逻辑执行的地方有关,这里就不过多赘述了。

文章转载自公众号:三友的java日记

标签
已于2023-9-15 11:05:50修改
收藏
回复
举报
回复
    相关推荐
    这个用户很懒,还没有个人简介
    帖子
    视频
    声望
    粉丝
    社区精华内容

    目录