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

本文链接：https://blog.csdn.net/qq_41899026/article/details/117654236

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

前言： 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版