侧边栏壁纸
  • 累计撰写 79 篇文章
  • 累计创建 84 个标签
  • 累计收到 7 条评论

目 录CONTENT

文章目录

MySQL学习-索引的基础篇(下)

汤圆学Java
2022-02-07 / 0 评论 / 0 点赞 / 46 阅读 / 2,195 字
温馨提示:
本文最后更新于 2022-02-07,若内容或图片失效,请留言反馈。部分素材来自网络,若不小心影响到您的利益,请联系我们删除。

前言

前面介绍了索引的基础知识,包括索引的模型、分类、重建等等;

本篇我们再延伸一下索引的分类,比如覆盖索引、联合索引;

索引的分类从的方面来说,分为主键索引和非主键索引,像即将介绍的这些索引都属于非主键索引;

目录

  1. 覆盖索引
  2. 联合索引
  3. 最左前缀原则
  4. 索引下推

正文

1. 覆盖索引

定义:如果一条查询语句中,非主键索引覆盖了我们的查询需求,那么这个索引就是覆盖索引

怎么理解呢?

就比如我们执行下面的查询语句:

select id from T where k between 3 and 5;

这里面id主键k是非主键索引,非主键索引中存储的就是主键的值,所以这里的查询需求(id)被覆盖了,我们不需要回表进行二次查询;

简单点来理解就是,如果一条语句只查询主键的值,且用到了非主键索引,那么这个非主键索引就是覆盖索引;

这个覆盖索引的好处就是提升了查询效率;

2. 联合索引

定义:同时将多个字段设置为一个索引,这个索引就是联合索引;

一般的索引都是将一个字段设置为索引;

为啥要弄出一个联合索引呢?

为了提高查询效率,因为联合索引可以查询联合索引的所有字段的值;

表面上看有点类似覆盖索引,都是为了简化查询,不用回表;

例子

比如我们现在有两个字段:身份证号、姓名;然后将这两个字段设置为联合索引,那么我们在通过身份证号查询姓名时,就可以一次查到,不用回表;

如果没有联合索引,那就需要先通过身份证号获取主键ID值,然后再根据主键ID值获取姓名;

那联合索引有没有缺点呢?

有的,缺点跟下面即将介绍的最左前缀原则有关,那就是如果查询条件不是联合索引的第一个字段,那么该联合索引就会失效;

还是上面的身份证号+姓名的例子,如果我们不是通过身份证号查询姓名,而是通过姓名查询身份证号,那么此时联合索引就失效了;

怎么解决这个缺点呢?

就是根据业务需要调整联合索引中字段的顺序,像上面这个业务,根据身份证号查询姓名应该是比较常用的,因为身份证号有唯一性,所以就可以将身份证号字段放到第一位;然后将姓名字段单独再设为一个索引;

怎么来确定联合索引中字段的顺序呢?

简单点来说,就是先设定一个顺序,如果该顺序可以少建立一个其他的索引,那么该顺序就是可以接受的;

3. 最左前缀原则

定义:当查询条件符合联合索引的最左N个字段,或者普通索引(字符串类型)的最左N个字符时,就会用到对应的索引;此时用的原则就是最左前缀原则

比如上面的联合索引 身份证号+姓名,只有一个查询条件身份证号时,也是可以用到该联合索引的;

根据这个原则,我们可以再来回答一下上面的问题:怎么来确定联合索引中字段的顺序?

上面提到了要考虑会不会增加其他索引;

其实还有一个方面是要考虑空间占用问题,下面我们用例子来说明;

例子:

比如有需求为:根据姓名查询年龄,也要根据年龄查询姓名,那此时要怎么建立索引呢?

根据最左前缀原则,他俩的顺序其实怎么建都一样;

但是考虑到空间占用问题,就需要将姓名放到前面,因为姓名字段肯定比年龄字段占用空间大,此时的联合索引为(name, age);

然后再为年龄字段单独建立一个普通索引 (age);

4. 索引下推

定义:根据联合索引中的字段顺序,从前往后挨个筛选,这个就是索引下推

例子:

select * from tuser where name like '张%' and age=10 and ismale=1;

这里假设联合索引为 (name, age),此时的查询过程如下:

  1. 先去联合索引中筛选 name 以"张"开头的用户;
  2. 再在结果集中筛选 age = 10 的用户;
  3. 最后遍历这些结果集,依次取出主键ID的值,再 回表 查询 ismale=1的数据;

这里的前面两步都是直接在联合索引中就可以查到的,因为联合索引保存了联合字段的值;

只有最后一步, ismale 不在联合字段中,才需要回表查询;

这里需要注意的一点是:索引下推是在mysql5.6之后才有的,也就是说mysql5.6之前,联合索引只保存了联合字段中的第一个字段,此时的查询除了第一个字段,后面的字段都需要回表查询

总结

本篇主要介绍了覆盖索引和联合索引,以及相关的一些规则,比如最左前缀原则,索引下推等

  • 覆盖索引:如果要查询的结果字段已经存储在索引中,那么这个索引就是覆盖索引;
  • 联合索引:就是把多个字段联合起来,建立一个索引,这个索引就是联合索引;联合索引需要考虑怎么将多个字段排序,可以少建立其他索引的顺序就是好顺序;
  • 最左前缀原则:比如上面的联合索引 身份证号+姓名,如果只有一个查询条件 身份证号,那么此时就会用到最左前缀原则;普通索引(字符串类型),如果查询条件为字符串的左边N个字符,那么也会用到最左前缀原则
  • 索引下推:还是基于联合索引,当查询条件的多个字段在联合索引中时,这些字段会在联合索引中进行挨个查询,不需要回表;
0

评论区