金三银四精选java面试题-Mysql中九种索引失效场景分析

MySQL中九种索引失效场景分析

表数据：

CREATE TABLE `t1` (
  a int primary key,
  b int ,
  c int ,
  d int ,
  e varchar(20)
) ENGINE=InnoDB ;


insert into t1 values(4,3,1,1,'d');
insert into t1 values(1,1,1,1,'a');
insert into t1 values(8,8,8,8,'h');
insert into t1 values(2,2,2,2,'b');
insert into t1 values(5,2,3,5,'e');
insert into t1 values(3,3,2,2,'c');
insert into t1 values(7,4,5,5,'g');
insert into t1 values(6,6,4,4,'f');

索引情况：

a字段是主键，对应主键索引，bcd三个字段组成一个联合索引，e字段一个索引

1. 不符合最左匹配原则

去掉b=1的条件就不符合最左匹配原则了，导致所有失效

2. 不正确的Like查询

不用like能走索引：

正常使用like：

不正确使用like：

3. 对索引列进行了计算或使用了函数

4. 索引列进行了类型转换

e字段的类型是vachar，下面这个sql需要把e字段中的字符转换成数字，会导致索引失效

5. <>不等于导致索引失效

b=1可以走索引，b<>1就不能走索引

6. order by导致索引失效

就算利用索引，但是由于是select * 所以需要回表，而且回表成本比较高，所以不会走索引。

如果是select b就需要回表了，就会选择走索引

7. 使用or导致索引失效

8. select * 导致索引失效

9. 范围查询数据量过多导致索引失效

新增一些数据：

insert into t1 values(10,3,1,1,'d');
insert into t1 values(20,1,1,1,'a');
insert into t1 values(15,8,8,8,'h');
insert into t1 values(18,2,2,2,'b');
insert into t1 values(14,2,3,5,'e');
insert into t1 values(13,3,2,2,'c');
insert into t1 values(17,4,5,5,'g');
insert into t1 values(22,6,4,4,'f');