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;
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、!=这些判断条件经常会出现在这些回表范围很大的场景,然后被人误解为是这些判断条件导致的索引失效。
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文章来源:https://www.toymoban.com/news/detail-669635.html
MySQL 索引失效详解_is not null 索引失效_王林-wlin的博客-CSDN博客文章来源地址https://www.toymoban.com/news/detail-669635.html
到了这里,关于MySQL索引可能失效之or、is null、is not null、不等于(!=,<>)、联合索引的文章就介绍完了。如果您还想了解更多内容,请在右上角搜索TOY模板网以前的文章或继续浏览下面的相关文章,希望大家以后多多支持TOY模板网!