【MySQL笔记】MySQL数据库索引失效的原因解读

Allen_lixl

已于 2023-12-24 20:44:04 修改

阅读量4.6k

点赞数 1

分类专栏：数据库文章标签：数据库 mysql 笔记大数据

于 2023-04-26 13:52:21 首次发布

本文链接：https://blog.csdn.net/liguohuaty/article/details/130382761

版权

数据库专栏收录该内容

27 篇文章 1 订阅

订阅专栏

七、用is null和is not null没注意字段是否允许为空

有时候明明已经建立了索引，但是通过explain发现语句并没有使用上索引，这可能是某些原因导致了我们的索引失效。所以本篇主要介绍的是索引失效的常见原因和如何用好索引，给有需要的小伙伴一个参考。

一、不满足最左前缀原则

为什么联合索引不遵循最左匹配原则就会失效？

原因是，在联合索引的情况下，数据是按照索引第一列排序，第一列数据相同时才会按照第二列排序。也就是说，如果我们想使用联合索引中尽可能多的列，查询条件中的各个列必须是联合索引中从最左边开始连续的列。如果我们仅仅按照第二列搜索，肯定无法走索引。

我们建立了两个索引：idx_code_age_name( code , age , name )联合索引和 idx_height(height)普通索引，下面实例按照这两个来说明：

1.1 第1种情况

where 条件后的字段包含了联合索引的所有索引字段，并且顺序是按照： code 、 age 、name
执行sql如下：
explain  select * from test1 
    where code='001' and age=18  and  name='张飞' ;
可以看出已经走了联合索引idx_code_name_age，并且索引的长度是 188， 188 = 30 * 3 + 2 + 30 * 3 + 2 + 4 ，索引是使用充分的，索引使用效率最佳。

1.2 第2种情况

where 条件后的字段包含了联合索引的所有索引字段，顺序是不按照： code 、 age 、name。
执行sql如下：
explain  select * from test1 
    where code='001' and  name='张飞' and age=18;
可以看出执行结果跟第一种情况一样。
注意：这种情况比较特殊，在查询之前mysql会自动优化顺序

1.3 第3种情况

where 条件后的字段包含了联合索引中的： code 字段
执行sql如下：
explain  select * from test1 
    where code='001';
也走了索引，但是索引长度有所变化，现在变成了 92 ， 92 = 30*3 + 2 ，只用到了一个索引字段code，索引使用不充分。

1.4 第4种情况

where 条件后的字段包含了联合索引中的： age 字段
执行sql如下：
explain  select * from test1 
    where age=18;
从上图中看变成了全表扫描，所有的索引都失效了。

1.5 第5种情况

where 条件后的字段包含了联合索引中的： name 字段
执行sql如下：
explain  select * from test1 
    where  name='张飞';
从上图中看变成了全表扫描，所有的索引都失效了。

1.6 第6种情况

where 条件后的字段包含了联合索引中的： code 和 age 字段
执行sql如下：
explain  select * from test1 
   where code='001' and age=18;
从上图中看出还是走了索引，但是索引长度变成了： 96 ， 96 = 30*3 + 2 + 4 ，只用到了两个索引字段code和age，索引使用也不充分。

1.7 第7种情况

where 条件后的字段包含了联合索引中的： code 和 name 字段
执行sql如下：
explain  select * from test1 
   where code='001' and  name='张飞';
从上图中看出走的索引长度跟第3种情况一样，长度也是 92 。也就是说只用到了一个索引字段 code ，而 age 字段的索引失效了。

1.8 第8种情况

where 条件后的字段包含了联合索引中的： age 和 name 字段
执行sql如下：
explain  select * from test1 
   where age=18  and  name='张飞';
从上图中看出变成了全表扫描，所有的索引都失效了。、

注意：范围查询放最后，指的是联合索引中的范围列放在最后，不是指where条件中的范围列放最后。如果联合索引中的范围列放在最后了，即使where条件中的范围列没放最后也能正常走到索引。

二、范围索引列没有放在最后

where 条件后的字段 age 用了大于等于，具体sql如下：
EXPLAIN  select * from test1 
where  code='001' and age>18  and  name='张飞' ;
从上图中看出索引长度变成： 96 ， 96 = 30*3 + 2 + 4 ，只用到了两个索引字段 code 和age ，而 name 字段的索引失效了。

如果范围查询的语句放到最后：
EXPLAIN  select * from test1 
where code='001' and  name='张飞' and age>18 ;
索引长度还是： 96

这是一个非常经典的错误
范围查询放最后是指创建联合索引的字段顺序，现在的顺序是：

调整一下把索引字段name和age的顺序调整一下：

再执行上面的sql，结果：

从上图中看出索引长度变成： 188 ，索引使用充分了。

回过头再执行刚开始的那条sql：
EXPLAIN  select * from test1 
where  code='001' and age>18  and  name='张飞';
索引长度也是： 188 。

注意：范围查询放最后，指的是联合索引中的范围列放在最后，不是指where条件中的范围列放最后。如果联合索引中的范围列放在最后了，即使where条件中的范围列没放最后也能正常走到索引。

三、使用了select*

使用select * 。
EXPLAIN  select * from test1
可以看出走了全表扫描。

那么如果查询的是索引列：
EXPLAIN  select code,age,name  from test1
可以看出这种情况走了全索引扫描，比全表扫描效率更高。

四、索引列上有计算

执行sql如下：
explain  select * from test1 
where  height+1 =7;
以看出变成全表扫描了，由此可见在索引列上有计算，索引会失效。

五、索引列上使用了函数

如果在索引列加某个函数，具体sql如下：
explain  select * from test1 
where   SUBSTR(height,1,1)=8;
从上图中可以看出变成全表扫描了，由此可见在索引列上加了函数，索引也会失效。

六、字符类型没加引号

explain  select * from test1 
where  name = 123;
可以看出走的全表扫描，索引失效了。
name 字段是字符类型，而等于号右边的是数字类型，类型不匹配导致索引丢失。

所以在使用字符类型字段做判断时，一定要加上单引号。

类型不匹配导致索引丢失问题，是我们平时工作中非常容易忽视的问题，一定要引起足够的重视。

七、用is null和is not null没注意字段是否允许为空

如果字段不允许为空，则is null 和 is not null这两种情况索引都会失效。
如果字段允许为空，则is null走 ref 类型的索引，而is not null走 range 类型的索引。

八、like查询左边有%

like ‘%a’ 索引失效
like ‘a%’ 走range类型索引
like ‘%a%’ 索引失效

九、使用or关键字时没注意

在 WHERE 子句中，如果在 OR 前的条件列是索引列，而在 OR 后的条件列不是索引列，那么索引会失效。

十、创建索引的建议

优先使用唯一索引，能够快速定位
为常用查询字段建索引
为排序、分组和联合查询字段建索引
一张表的索引数量不超过5个
表数据量少，可以不用建索引
尽量使用占用空间小的字段建索引
用idx_或unx_等前缀命名索引，方面查找
删除没用的索引，因为它会占一定空间

十一、EXPLAIN列的解释

table：显示这一行的数据是关于哪张表的
type：这是重要的列，显示连接使用了何种类型。从最好到最差的连接类型为const、eq_reg、ref、range、 indexhe和ALL
possible_keys：显示可能应用在这张表中的索引。如果为空，没有可能的索引。可以为相关的域从WHERE语句中选择一个合适的语句
key：实际使用的索引。如果为NULL，则没有使用索引。很少的情况下，MYSQL会选择优化不足的索引。这种情况下，可以在SELECT语句中使用USE INDEX(indexname)来强制使用一个索引或者用IGNORE INDEX(indexname)来强制MYSQL忽略索引
key_len：使用的索引的长度。在不损失精确性的情况下，长度越短越好
ref：显示索引的哪一列被使用了，如果可能的话，是一个常数
rows：MYSQL认为必须检查的用来返回请求数据的行数
Extra：关于MYSQL如何解析查询的额外信息。坏的例子是Using temporary和Using filesort，意思MYSQL根本不能使用索引，结果是检索会很慢