组合索引的使用规则到底有哪些?有什么注意点?什么情况下索引会生效、失效?


索引的使用规则到底有哪些?有什么注意点?什么情况下索引会生效、失效?


前言: 5月初面试了字节跳动抖音电商实习岗,一上来,面试官就问了组合索引的问题,并且给出了很多题用于判断哪些情况下组合索引会生效,当时答得很差,只记之前在网上看了下什么范围查找会失效,面试下来才知道错回答了很多,于此记录本篇博客。本篇博客主要针对B+树的组合索引,同时只是给出结论和简单解释,至于为什么生效,为什么失效,之后有时间了会给出图文解释。

一、索引生效的查询规则

首先对索引的生效规则给出结论:包括

  • 全值查询
  • 匹配最左前缀
  • 匹配列前缀
  • 匹配范围值
  • 精确匹配某一列并范围匹配另一列
  • 只访问索引的查询

现在对上面的规则给出解释,为了简单说明其中的道理,假设现在有一张user表,字段为id ,name, address,birthday四个字段。同时为name, address,birthday这三个字段建立组合索引(组合索引就是为多个列建立一个索引)。在这样的索引建立顺序下,对下面类型的查询该索引会生效

  • 全值查询:全值查询是指和索引列中所有列进行匹配。对面上面的索引,我们可以查询姓名为柳浪春、出生地址为酒厂、生日为1998-11-03的人

  • 匹配最左前缀:上面的索引可以查询姓名为牛栏山的人,也可以查询姓名为江小白,出生地位为重庆的人。即使用这个索引的前1项或者前n-1项(当使用当前n项的时候,就成了等值查询)


    题外话:对于这个最左前缀原则,也就是面试官爱问的问题,简单来说一个记忆方式:对于组合索引,直接从左到右地按索引的建立顺序选择索引列,都可以生效,可以选择一个列,也可以多个列,一定一定要按照索引的建立顺序,中间不能跳过任何一个列,中间不能出现范围查询,结尾可以出现范围查询也可以是等值查询。


  • 匹配列前缀:即可以匹配某一列的值的开头部分。里面前面的索引可以匹配姓名中以zhao开头的人。这里使用了索引的第一列。

  • 匹配范围值:例如上面的索引可以查找名字为Bob到Mary之间的人,这时候只用了组合索引的第一列(当时我直接回答了,只要出现了> 、< 、between and这种范围查询,索引就一定失效)

  • 精确匹配某一列或几列并范围匹配另一列:前面的索引可以查询姓名为Kap,地址在America和California之间的人。或者查询姓名为Joe、地址在New York、生日在1998-11-03到2020-11-03之间的人。注:其实这种方式可以归属于最左前缀查询,但是在《高性能MySQL》中是将这条单独列出来的

  • 只访问索引的查询:即只需要访问索引,但是不需要访问数据行

    (这个之前忘了哪个公司的面试官也问到了,但是当时采用另一种方式问的我----->非主键索引的情况,是不是一定会进行回表操作?当时没回答出来)如果不知道回表的同学可以自己查一下。

    对于这种情况,考虑一种特殊情况:如果索引的叶子节点已经包括了我们想要的数据了,那是不是我们就不用再次回表去查询数据了?这样我们可以引申出一个概念:

    覆盖索引:一个索引覆盖了(包含了)所有需要查询的字段值。

    对面上面那个问题:考虑几种场景:

    1. select name, address, birthday from user where name = ‘lisi’ and address = ‘重庆’

      对于这样一个sql语句,查询的条件符合原则精确匹配某一列或几列并范围匹配另一列,同时查询到的列都被我们组合索引(name, address, birthday)包括,这时候就称为只访问索引的查询。

    2. select name, address, birthday from user

      对于这样一个sql语句,查询的条件符合原则全值查询,同时查询到的列都被我们组合索引(name, address, birthday)包括,这时候也称为只访问索引的查询。

    3. 根据一名用户的name去查询id 和 name字段,这时候叶子节点一定包含了name和id,同时我们就没有必要拿着id进行回表操作去查询其他列信息了(针对于InnoDB的聚簇索引,因为InnoDB的二级索引的叶子节点保存了行的主键值)。

    二、索引的限制(索引的失效情景)

    1. 如果不按照索引的最左列开始查询,那么就无法使用索引。还是采用上面的user表和索引,例如是查询address等于北京的人员消息,那么是无法查询到的。(跳过了最左列,也就是name列)

    2. 跳过索引的列。比如没有跳过最左列,但是跳过了address列,而去查询名为bob,出生日期为2004-04-12的人员信息。

    3. 在使用索引的中途出现范围查询,也就是说没有违反1和2两个条件,但是在查询中(不是查询末尾),出现了某个索引列的范围查询(> 、<、between and 、in、like ‘%列名’)。例如:

      select * from user where name = ‘bob’ and address in (‘sichuan’, ‘shanxi’, ‘beijing’) and birthday = ‘2014-08-01’。在这个sql语句中,address字段出现在了查询中,而且使用了范围查询,因此这个查询实际生效的只能是前两列,也就是name和address,而birthday不会走索引(以后有时间会讲为什么)

    三、总结

    ​ 本篇博客记录了索引的使用规则和索引的失效情景。但是没用介绍底层的原理,其实原理也非常简单,未来有时间会进行图文说明,一看,也就清楚了。


    参考:

    《高性能MySQL》第3版

  • 5
    点赞
  • 16
    收藏
    觉得还不错? 一键收藏
  • 0
    评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值