MySQL索引失效的场景，什么情况下会造成MySQL索引失效

短命女 2022-10-30 06:26 40阅读 0赞

**目录**

一、准备工作，准备sql

二、索引是什么、使用explain关键字来解析sql使用索引情况

三、索引的使用与失效场景

1.使用全部复合索引（索引正常使用）

2.最佳左前缀法则

3.在索引列上做任何操作（计算、函数、自动或手动类型转换），会导致索引失效而转向全表扫描。

4.不能使用索引中范围条件右边的列。（范围条件之后的索引全失效，但是范围条件之前的和范围条件的还是用到索引的。）

5.尽量使用覆合索引（只访问索引的查询（索引列和查询列一致）），减少select \*。

6.mysql在使用不等于（!= 或者 <>）的时候无法使用索引会导致全表扫描。（8.0似乎优化了，不等于是范围了，待验证）

7.is null、is not null也无法使用索引（这里不知道为什么is null还是走了索引。。有明白的小伙伴可以解释一下）。

8.like以通配符开头（'%abc...'）会导致索引失效。

9.少用or，用它来连接时会索引失效。

10.关于in关键字

四、总结

--------------------

# 一、准备工作，准备sql #

这里我使用的mysql版本是 5.5.40。

CREATE TABLE `student`  (
      `id` int(11) NOT NULL AUTO_INCREMENT,
      `name` varchar(20),
      `age` int(11),
      `address` varchar(25),
      `crtdate` timestamp,
      PRIMARY KEY (`id`) USING BTREE
    );
    
    insert into student(name,age,address,crtdate) values('张三', 15, '山东青岛', now());
    insert into student(name,age,address,crtdate) values('李四', 17, '山东济南', now());
    insert into student(name,age,address,crtdate) values('王五', 18, '山东潍坊', now());
    
    -- 创建复合索引
    alter table student add index idx_stu_nameAgeAddr(name,age,address);

# 二、索引是什么、使用explain关键字来解析sql使用索引情况 #

explain如何使用，请阅读这篇文章：

[MySQL高级-索引是个什么东西？explain到底怎么用-MySQL查询优化大全][MySQL_-_explain_-MySQL]

# 三、索引的使用与失效场景 #

## 1.使用全部复合索引（索引正常使用） ##

之前创建了name、age、address三个字段的复合索引，查询条件根据这三个字段，是可以直接使用索引的。

-- sql贴在这里了
    explain select * from student where name='张三' and age=15 and address = '山东青岛';

![20210218100948943.png][]

## 2.最佳左前缀法则 ##

**如果索引了多列，要遵循最左前缀法则。指的是查询从索引的最左前列开始并且不跳过索引中的列。**

-- sql贴在这里了
    explain select * from student where name='张三' and age=15;
    explain select * from student where name='张三';
    explain select * from student where age=15 and address = '山东青岛';
    explain select * from student where name='张三' and address = '山东青岛';

以下这两种查询都是没问题的，因为遵守最佳左前缀法则，条件字段从左到右，从第一个索引字段name开始并且也不跳过索引中间的列：

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0FfYXJ0X3hpYW5n_size_16_color_FFFFFF_t_70][]

以下会造成索引完全失效，因为没有使用复合索引的第一列name：

![20210218101536505.png][]

以下会造成索引部分失效，第一个条件name是走索引的，但是address因为跳过了age所以address这个字段索引失效：

![20210218101619121.png][]

## 3.在索引列上做任何操作（计算、函数、自动或手动类型转换），会导致索引失效而转向全表扫描。 ##

-- sql贴这里了
    explain select * from student where left(name,1)='张';

![2021021810251242.png][]

索引列上使用了表达式，如where substr(name, 1, 3)='张小四'、where num = num +1，表达式是一大忌讳，再简单的表达式mysql也会造成索引失效。

有时数据量不是大到严重影响速度时，一般可以先查出来，比如先查询所有订单记录的数据，再在程序中筛选。

**字符串不加单引号索引失效：（做了隐式的类型转换）**

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0FfYXJ0X3hpYW5n_size_16_color_FFFFFF_t_70 1][]

## 4.不能使用索引中范围条件右边的列。（范围条件之后的索引全失效，但是范围条件之前的和范围条件的还是用到索引的。） ##

-- sql贴这里了
    explain select * from student where name='张三';
    explain select * from student where name='张三' and age=15;
    explain select * from student where name='张三' and age=15 and address = '山东青岛';
    explain select * from student where name='张三' and age>15 and address = '山东青岛';

很显然，最后一条sql，只有name、age走了索引，但是address失效了。

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0FfYXJ0X3hpYW5n_size_16_color_FFFFFF_t_70 2][]

## 5.尽量使用覆合索引（只访问索引的查询（索引列和查询列一致）），减少select \*。 ##

我们都知道，Extra中出现using index是好现象，对查询效率会增强。

而且有范围查询时，不用\*的时候，key\_len会更小，这意味着会增加查询效率。

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0FfYXJ0X3hpYW5n_size_16_color_FFFFFF_t_70 3][]

## 6.mysql在使用不等于（!= 或者 <>）的时候无法使用索引会导致全表扫描。（8.0似乎优化了，不等于是范围了，待验证） ##

-- sql贴这里了
    explain select * from student where name='张三';
    explain select * from student where name!='张三';
    explain select * from student where name<>'张三';

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0FfYXJ0X3hpYW5n_size_16_color_FFFFFF_t_70 4][]

## 7.is null、is not null也无法使用索引（这里不知道为什么is null还是走了索引。。有明白的小伙伴可以解释一下）。 ##

-- sql贴这里了
    explain select * from student where name='张三';
    explain select * from student where name is null;
    explain select * from student where name is not null;

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0FfYXJ0X3hpYW5n_size_16_color_FFFFFF_t_70 5][]

## 8.like以通配符开头（'%abc...'）会导致索引失效。 ##

like以通配符开头会导致索引失效，但是通配符放在后面索引就会正常使用。

-- sql在这里
    explain select * from student where name='张三';
    explain select * from student where name like '%张';
    explain select * from student where name like '张%';
    explain select * from student where name like '%张%';

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0FfYXJ0X3hpYW5n_size_16_color_FFFFFF_t_70 6][]

但是，这种情况like前面使用通配符，也是会走索引的：（select查询的字段刚好是复合索引的字段，所以为什么在查询的时候不建议使用\*）

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0FfYXJ0X3hpYW5n_size_16_color_FFFFFF_t_70 7][]

## 9.少用or，用它来连接时会索引失效。 ##

explain select * from student where name='张三' or age=15;

![20210218115747109.png][]

## 10.关于in关键字 ##

in关键字在mysql中是个很神奇的存在

-- 主键
    explain select * from student where id in (1,2);
    explain select * from student where id in (1,2,3,4,5,6,7,8,9);
    -- 复合索引
    explain select * from student where name in ('李四');
    explain select * from student where name in ('李四', '王五');

我们可以看到，in使用关键字少的时候，会走索引的，但是内容较多，就会造成索引失效

# ![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0FfYXJ0X3hpYW5n_size_16_color_FFFFFF_t_70 8][]四、总结 #

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0FfYXJ0X3hpYW5n_size_16_color_FFFFFF_t_70 9][]

[MySQL_-_explain_-MySQL]: https://blog.csdn.net/A_art_xiang/article/details/113643377
[20210218100948943.png]: /images/20221024/fadb271c600f4e11b832d478efbc0a24.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0FfYXJ0X3hpYW5n_size_16_color_FFFFFF_t_70]: /images/20221024/e6a553b7f5a4487b8d6ad2638fe9f80c.png
[20210218101536505.png]: /images/20221024/cd6c05a50efb4c11bbf4468211b49e80.png
[20210218101619121.png]: /images/20221024/3aafef2c71d74fe78cd6ba41f6c4d786.png
[2021021810251242.png]: /images/20221024/8e5e2634a24d4ac88a48b189eb757b1b.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0FfYXJ0X3hpYW5n_size_16_color_FFFFFF_t_70 1]: /images/20221024/8e6872280a264e869b0e04dc9d6e883a.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0FfYXJ0X3hpYW5n_size_16_color_FFFFFF_t_70 2]: /images/20221024/479c48ed6ee847258e53a85ee5365c21.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0FfYXJ0X3hpYW5n_size_16_color_FFFFFF_t_70 3]: /images/20221024/409e82ec3cd84348a077f8fd3cd02bd6.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0FfYXJ0X3hpYW5n_size_16_color_FFFFFF_t_70 4]: /images/20221024/262f18b427c2478583fecb3fa70434e3.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0FfYXJ0X3hpYW5n_size_16_color_FFFFFF_t_70 5]: /images/20221024/c0d12e39e58648f8a8a27422870492e3.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0FfYXJ0X3hpYW5n_size_16_color_FFFFFF_t_70 6]: /images/20221024/f836c7d4327441c3a476f06788b00194.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0FfYXJ0X3hpYW5n_size_16_color_FFFFFF_t_70 7]: /images/20221024/f92ac6ebbf9c4d7fb5695e01493bf92b.png
[20210218115747109.png]: /images/20221024/f765b589b0894aa4980164283f096e34.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0FfYXJ0X3hpYW5n_size_16_color_FFFFFF_t_70 8]: /images/20221024/3a9dd42fb006442bbcecc0e1d9531f18.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0FfYXJ0X3hpYW5n_size_16_color_FFFFFF_t_70 9]: /images/20221024/95679045389545369ec4f03993eafa23.png