索引使用规则以及注意失效的场景

目录

一、联合索引遵循最左前缀法则

1.1 执行带着唯一索引和联合索引情况:

1.2 执行不带最左侧字段情况:

1.3 执行跳过中间列情况：

1.4 使用联合索引查找是否跟建立索引的顺序有关？

二、范围查询

三、避免某些操作导致索引失效

3.1 索引列运算

3.2 对于字段为字符类型不加引号

四、使用like模糊查询失效

五、or连接条件

六、数据分布影响

七、尽量使用覆盖索引

---

一、联合索引遵循最左前缀法则

如果索引了多列（联合索引），要遵守最左前缀法则。最左前缀法则指的是查询从索引的最左列开始， 并且不跳过索引中的列。如果跳跃某一列，索引将会部分失效(后面的字段索引失效)

表结构如下：

在username,password,name三个字段建立联合索引 ，同时上面建表时设置了唯一索引username

create index index_username_password_name on emp (username,password,name);

对于最左前缀法则指的是，查询时，最左边的列，也就是username必须存在，否则索引全部失效。 而且中间不能跳过某一列，否则该列后面的字段索引将失效

1.1 执行带着唯一索引和联合索引情况:

explain select * from emp where username='jinyong' and password='123456' and name='金庸';

能看到出现可能使用的索引有俩个，username唯一索引和联合索引index_username_password_name，走的是username唯一索引

删掉唯一索引，来看看联合索引的长度为多少

alter table emp drop constraint username

在俩个索引同时使用的情况下，肯定是走最短的那个 

1.2 执行不带最左侧字段情况:

explain select * from emp where password='123456' and name='金庸';

通过key,和key_len能看到的是，索引失效了 ,不满足最左前缀法则，要求最左侧的列必须存在

1.3 执行跳过中间列情况：

不跳过的长度是255

发现执行的索引长度变短了，为82，所以中间一部分的索引失效了

1.4 使用联合索引查找是否跟建立索引的顺序有关？

执行以下sql:

explain select * from emp where  password='123456' and name='金庸' and username='jinyong';

可以看到，是完全满足最左前缀法则的，联合索引是生效的

注意 ： 最左前缀法则中指的最左边的列，是指在查询时，联合索引的最左边的字段(即是 第一个字段)必须存在，与我们编写SQL时，条件编写的先后顺序无关

二、范围查询

联合索引中，出现范围查询(>,<)，范围查询右侧的列索引失效

当使用<,>时，情况如下：

图来源黑马MySQL教程

教程中推测出profession字段索引长度为47、age 字段索引长度为2、status字段索引长度为5

能够从上方看出，status字段没有走索引

 图来源黑马MySQL教程

当使用<=,>=的时候，这个时候是会去走联合索引的，因此对于范围查询我们尽可能的去使用<=,>=

三、避免某些操作导致索引失效

3.1 索引列运算

索引列上进行运算操作， 索引将会失效

执行下述sql:

explain select * from emp where substring(username,10,2) = 'ji';

在username该索引上使用函数进行运算操作，能够看到key_len失效

3.2 对于字段为字符类型不加引号

执行以下sql:

explain select * from emp where  password=123456 and name='金庸' and username='jinyong';

explain select * from emp where  password='123456' and name='金庸' and username='jinyong';

 执行的结果是一样的，rows都为1，但是对于字符类型的数据字段，不加引号，索引就失效了

四、使用like模糊查询失效

如果仅仅是尾部模糊匹配，索引不会失效。如果是头部模糊匹配，索引失效

图来源黑马MySQL教程

profession是联合索引的最左侧索引，在like模糊查询中，在关键字后面加%，索引可以生效。而如果在关键字 前面加了%，索引将会失效

五、or连接条件

用or分割开的条件， 如果or前的条件中的列有索引，而后面的列中没有索引，那么涉及的索引都不会 被用到

图来源黑马MySQL教程

由于age没有索引，id、phone有索引的情况下，索引也会失效，所以需要针对于age也要建立索引

图来源黑马MySQL教程 

上图为建完索引后的执行情况，所以只有左右俩侧都有索引时，索引才会生效

六、数据分布影响

一模一样的SQL语句，先后执行了两次，只是传入的字段值不同，结果查询计划是不一样的，这种现象是和数据库的数据分布有关系，如果MySQL认为我们使用索引反而比全表慢，那么就不使用索引。

1：根据phone的查询范围不同。

 

图来源黑马MySQL教程 

根据上图俩条sql语句explain语句执行计划分析，同样的查询字段，只是因为范围不一样，而导致使用索引就不一样，这就是数据分布的影响，MySQL会去评估使用索引快还是全表快。

七、尽量使用覆盖索引

在编写sql语句时，我们应该尽量使用覆盖索引，减少select * 操作，因为select *很容易造成回表查询，这样会导致查询时间缓慢。

我们来看一张表的结构设计以及所建立的索引字段：

 图来源黑马MySQL教程 

假设现在我们执行了如下几条sql:

1：根据主键id去查询数据：

select * from tb_user where id = 2;

id作为主键，即聚集索引，主键聚集索引，叶子节点挂载的是row（行数据），所以即使我们使用的是select *，是可以一次性直接返回数据的。

2：根据非聚集索引name字段查询：

selet id,name from tb_user where name = 'Arm';

二级索引name(非聚集索引)叶子节点挂载的是主键的id值，在查询name,id时能够一次性返回 ，覆盖到了索引。

3：根据name查询，查询结果中有其他的非索引字段：

selet id,name,gender from tb_user where name = 'Arm';

在name的二级索引中，不包含gender，所以，需要两次索引扫描，也就是需要回表查询。