索引失效原因

索引失效原因

一.准备工作

创建student表，id是主键

创建复合索引

create index idx_name_age on student(name,age);

查看索引

show index from student;

二.索引失效原因

1.全值匹配我最爱

explain select * from student where name = ‘张三’ and age = 1;

2.最佳左前缀法则，带头大哥不能死，中间兄弟不能少。

explain select * from student where age = 1;

3.不要在索引列上做任何操作

explain select * from student where left(name,1) = ‘张’ and age = 1;

4.范围条件后列上索引失效

explain select * from student where age > 1 and name = ‘王五’;

查找级别是范围，name上的索引失效。

5.尽量使用覆盖索引减少使用select *

explain select * from student where name = ‘zhangsan’;

explain select name from student where name = ‘zhangsan’;

6.使用不等于（！= 或者<>）不能使用索引

explain select * from student where name != ‘张三’;

7.使用 is null 或者 is not null 也不能使用索引

explain select * from student where name is not null;

8.like 已通配符开头（%abc）导致索引失效 （解决方法：使用覆盖索引）

explain select * from student where name like ‘%张%’;

想用的话解决方法，使用覆盖索引

explain select name from student where name like ‘%张%’;

9.少用or，用它来连接索引会失效

explain select * from student where name = ‘张三’ or age = 2;