索引使用规则及索引失效情况

Richard丷

已于 2022-12-05 23:09:42 修改

阅读量3.3k

点赞数 3

分类专栏： Mysql 文章标签： mysql

于 2022-12-05 23:06:58 首次发布

本文链接：https://blog.csdn.net/Maringss/article/details/128193744

版权

Mysql 专栏收录该内容

8 篇文章 0 订阅

订阅专栏

索引使用规则

- 联合索引
- 索引失效情况

联合索引

1.最左前缀法则
如果索引使用了多列（联合索引），要遵守最左前缀法则。最左前缀法则指的是查询从索引的最左列开始，并且不跳过索引中的列，如果跳跃某一列，索引将会部分失效（后面字段的索引失效）。其中对于最左的列必须存在，否则索引将全部失效。

在这里插入图片描述
以tb_user为例，tb_user表所创建的索引如上图所示，在tb_user表中 ,id 为主键索引,phone,name为普通索引，还有一个联合索引，联合索引涉及到3个字段，顺序分别为profession，age，status。
最左原则就是在查询时，最左边的列，也就是profession必须存在，否则索引全部失效。而且中间不能跳过某一列，否则该列后面的字段将失效。用以下几组案例来演示:

EXPLAIN SELECT * FROM tb_user WHERE profession = '软件工程' and age = 31 and status = '0';

在这里插入图片描述

EXPLAIN SELECT * FROM tb_user WHERE profession = '软件工程' and age = 31 ;

在这里插入图片描述

EXPLAIN SELECT * FROM tb_user WHERE profession = '软件工程' ;

在这里插入图片描述
以上3组测试中，只要联合索引最左边的字段 profession存在，索引就会生效，只不过索引的长度不同，通过以上3组测试可以得出，profession字段索引长度36，age长度为2，status长度也为2.

EXPLAIN SELECT * FROM tb_user WHERE age = 31 and status  ='0' ;

在这里插入图片描述

EXPLAIN SELECT * FROM tb_user WHERE status  ='0' ;

在这里插入图片描述
通过上面2组测试看到索引并未生效，原因就是不满足最左前缀法则，联合索引最左边的列profession不存在。

EXPLAIN SELECT * FROM tb_user WHERE profession ='软件工程' and   status  ='0' ;

在这里插入图片描述
通过上面这组测试看到profession存在时索引生效，但是在查询时，跳过了age这个列，索引后面的列索引是不会使用的，也就是索引部分失效，索引索引的字段为36。

2.范围查询
在联合索引中，出现范围查询（>,<),范围右侧索引的列索引就失效。

EXPLAIN SELECT * FROM tb_user WHERE profession ='软件工程' and age >30  and   status  ='0' ;

在这里插入图片描述
当联合索引使用了范围查询(<,>),索引会生效，但是索引长度为38，就说明范围查询最右边的status字段是没有左索引的。当范围查询使用<= 或者 >=时，则右侧索引的列索引不会失效。

EXPLAIN SELECT * FROM tb_user WHERE profession ='软件工程' and age >=0  and   status  ='0' ;

在这里插入图片描述

索引失效情况

**1.不要在索引列上进行运算操作，索引将失效
在tb_user表中，其中phone字段也是一个索引。

当phone字段进行等值匹配时，索引生效。

EXPLAIN SELECT * FROM tb_user WHERE phone ='17799990017'

在这里插入图片描述

当phone字段进行函数运算操作之后，索引失效。

EXPLAIN SELECT * FROM tb_user WHERE SUBSTRING(phone ,10,2) = 15;

在这里插入图片描述
2.字符串类型字段使用时，不加引号，索引将失效

EXPLAIN SELECT * FROM tb_user WHERE phone = 17799990017

在这里插入图片描述
3.模糊查询
如果只是尾部模糊匹配，索引不会失效。如果是头部使用模糊匹配，索引失效。

尾部模糊匹配，索引不失效

EXPLAIN SELECT * FROM tb_user WHERE phone like '17111%'

在这里插入图片描述

头部模糊匹配，索引失效

EXPLAIN SELECT * FROM tb_user WHERE phone like '%17'

在这里插入图片描述
4.or连接条件
用or分割开的条件，如果or前的条件的列有索引，而后面的列中没有索引，那么涉及的索引都不会被用到。

EXPLAIN SELECT * FROM tb_user WHERE  id =10 or age = 22;

在这里插入图片描述
由于age没有索引，所以即使id有索引，索引也会失效。

CREATE INDEX idx_user_age on tb_user(age);

我们可以对age字段建立索引，建立索引后再次执行以上SQL语句，结果如下:
在这里插入图片描述
最终发现，当or连接的条件中，左右两侧都有索引时，索引才会生效。
5.不等于(!= 或者<>)索引失效

EXPLAIN SELECT * FROM tb_user WHERE phone <> '123' ;

在这里插入图片描述

EXPLAIN SELECT * FROM tb_user WHERE phone != '123' ;

在这里插入图片描述

6.数据分布影响
如果Mysql评估使用索引比全表更慢，则不使用索引。

EXPLAIN SELECT * FROM tb_user WHERE  phone > '17799990015' ;

在这里插入图片描述

EXPLAIN SELECT * FROM tb_user WHERE  phone > '17799990020' ;

在这里插入图片描述
通过以上2组SQL测试得到只是phone查询的值不同，最终的执行计划也完全不一样。这是为啥？
就是因为Mysql在查询时，会评估使用索引的效率与走全表扫描的效率，如果走全表扫描更快，则会放弃索引，走全表扫描。因为索引是用来索引少量数据的，如果通过索引查询返回大批量的数据则还不如走全表扫描来的快，此时索引就会失效。

我们在看看 is null 与 is not null 操作是否走索引。

EXPLAIN SELECT * FROM tb_user WHERE  profession is null ;

在这里插入图片描述

EXPLAIN SELECT * FROM tb_user WHERE  profession is not  null ;

在这里插入图片描述
接下来，我们做一个操作profession字段值全部更新为null。

update  tb_user set profession = null ;

然后在执行以上2条sql，查看其执行计划
在这里插入图片描述

最终看到一模一样的SQL语句，先后执行了两次，结果查看执行计划是不一样的，为什么会出现这种情况呢？还是因为数据库的数据分布有关系。查询Mysql会评估，走索引快还是全表扫描快，如果全表扫描更快，则会放弃索引走全表扫描。因此,is null 、is not null是否走索引，得具体情况具体分析，并不是固定的。