mysql索引失效的情况

给students表中插入数据

INSERT INTO students(sname,age,score,time) VALUES('小明',22,100,now());
INSERT INTO students(sname,age,score,time) VALUES('小红',23,80,now());
INSERT INTO students(sname,age,score,time) VALUES( '小绿',24,80, now());
INSERT INTO students(sname,age,score,time)VALUES('小黑',23,70,now());

创建联合索引

alter table students add index idx_sname_age_score(sname,age,score) ;

1破坏最左前缀法则

联合索引从左到右的顺序为sname,age,score，如果where之后的查询语句，破坏索引的顺序，就会出现索引失效。如：

explain select * from students where age = 22 and score = 100;

2在索引列上做任何计算、函数操作，会导致索引失效而转向全表扫描。

如：使用left函数

explain select * from students where left(sname,2) = "小明";

3存储引擎不能使用索引中范围条件右边的列

联合索引sname,age,score，只能使用sname,age

explain select * from students where sname="小明" and age > 22 and score = 100;

4Mysql在使用不等于时无法使用索引会导致全表查询

sname索引无法使用，会进行全表查询

explain select * from students where sname !="小明";

5is null可以使用索引，但是is not null无法使用索引

explain select * from students where sname is null;

explain select * from students where sname is not null;

6like以通配符开头会使索引失效导致全表扫描。

lexplain select * from students where sname like "%明";

7字符串不加单引号或双引号索引会失效。

explain select *from students where sname = 123;

8使用or连接时索引失效

explain select * from students where sname="小明" or age = 22;

参考

链接: 索引优化