MySQL索引可能失效之or、is null、is not null、不等于(!=,＜＞)、联合索引

年纪轻轻的程序员

已于 2024-04-28 13:45:43 修改

阅读量3.5k

点赞数 1

CC 4.0 BY-SA版权

文章标签：数据库 java

于 2023-08-23 11:32:59 首次发布

本文链接：https://blog.csdn.net/qq_40391091/article/details/132447774

本文详细介绍了MySQL索引失效的多种情况，如主键与非主键字段在不同查询条件下索引是否失效，不同版本MySQL的差异等。还探讨了单个字段和联合索引在各种查询语句中的表现，以及联合索引的最左匹配原则和范围查询放置规则。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

5、给主键name字段、card字段加上索引，

select * from user where name !='123';

select * from user where name not in (7,8);

select * from user where age !='18' ;

select * from user where age not in (7,8);

答案： name字段为主键，索引不失效(range索引) ；age字段不是主键，即使age字段设索引也会失效。另外mysql5.7和5.8不同的版本效果不一样，5.7中sql执行结果是全表扫描，而5.8中使用了 range 类型索引。5.7中如果想使用索引将where条件后的不等于用大于和小于代替。

6、给单个name字段、单个card字段加上索引，查询name为非空的语句（card同样）

select * from user where name is not null

答案：会走索引

7、给单个name字段、单个card字段加上索引，查询name为非空或card为非空的语句

select * from user where name is not null or card is not null;

答案：不会走索引。它两用or连接起来，索引就失效了。很多时候，也是因为数据量问题，导致了MySQL优化器放弃走索引。同时，平时我们用explain分析SQL的时候，如果type=range,要注意一下哈，因为这个可能因为数据量问题，导致索引无效。

8、给单个name字段加上索引，

select * from user where name is not null索引是否失效？

答案：如果name字段不允许为空， is null 和 is not null这两种情况索引都会失效（索引一般加在非空列！）

如果name字段允许为空，则is null走 ref 类型的索引，而is not null走 range 类型的索引（如果返回数据量过大，也会索引失效）

补充：

导致索引失效而全表扫描的通常是因为一次查询中回表数量太多。mysql计算认为使用索引的时间成本高于全表扫描，于是mysql宁可全表扫描也不愿意使用索引。使用索引的时间成本高于全表扫描的临界值（10%~30% 左右）。而is null、is not null、!=这些判断条件经常会出现在这些回表范围很大的场景，然后被人误解为是这些判断条件导致的索引失效。

1、如果 A,B 两列都有索引，那么

select * from Table where A=a or B=b;

会走索引吗？

答案：会，因为 A,B都有索引；

2、如果 A,B有索引，但是C没有索引；

select * from Table where A=a or B=b or C =c；

会走索引吗？

答案：不会走，因为C是or的形式，且没有索引

3、如果建了A B联合索引，但是查询的时候是B A，会走索引吗？

select * from Table where B=b and A=a ；

答案：会走索引，mysql会自动优化；

4、如果建了A B联合索引，也建了 B,A 联合索引，但是查询的时候是B A，会走那个索引吗？

select * from Table where B=b and A=a ；

答案：不一定走 BA 索引，走那个索引，需要mysql会把sql优化，看 A列的数据过滤多，还是B的过滤多；如果 A的列数据能过滤更多数据，那么会走AB,如果B的列能过滤更多数据，则走BA；

9、联合索引不满足最左匹配原则相关

建立一个联合索引：idx_code_age_name( code , age , name )

#where 条件后的字段包含了联合索引的所有索引字段，并且顺序是按照： code 、 age 、name
explain  select * from test1 where code='001' and age=18  and  name='张飞'
#可以看出已经走了联合索引idx_code_name_age,索引是使用充分的，索引使用效率最佳

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

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

#where 条件后的字段包含了联合索引中的： age 字段 或者 name 字段
explain  select * from test1 where age=18
explain  select * from test1 where name='张飞';
#全表扫描，所有的索引都失效了

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

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

#where条件后的字段包含了联合索引中的：age 和 name 字段
explain  select * from test1 where age=18  and  name='张飞';
#全表扫描，所有的索引都失效了

10、联合索引的范围查询放到最后

例如联合索引 name,age,height 其中 age需要范围查询（14-18）岁的学生，这时候，要把age放到联合索引列的最后（联合索引中的age列放在最后，不是指where条件中的age放最后。如果联合索引中的范围列放在最后了，即使where条件中的范围列没放最后也能正常走到索引）

摘抄于：

https://www.cnblogs.com/aspirant/p/16166851.html

MySQL 索引失效详解_is not null 索引失效_王林-wlin的博客-CSDN博客