索引失效问题之总结

索引失效

create table staffs(
id int primary key auto_increment,
name varchar(20) not null default "aaa" comment "姓名",
age int  not null default 0 comment "年龄",
pos varchar(20) not null default " ass" comment "职位",
add_time timestamp not null default  current_timestamp comment "入职时间"
);

insert into staffs(name,age,pos,add_time) values("Joke",29,"manager",now());
insert into staffs(name,age,pos,add_time) values("Bob",28,"dev",now());
insert into staffs(name,age,pos,add_time) values("Rose",29,"dev",now());

• 最左前缀原则导致索引失效（部分失效）

alter table staffs add index index_name_age_pos(name,age,pos);
--索引失效，跳过了复合索引第一个属性name，全表扫描
explain select *from staffs 
where age=28;
--索引部分失效，跳过了复合索引第二个属性age，索引只对name生效
explain select *from staffs 
where name="Bob"  and pos="dev";
--生效组合name，age，pos
--              name，age
--总之最左原则
--1.复合索引的第一个属性必须存在
--2.复合索引中间属性不能断

• 函数导致索引失效

select *from staffs 
where left(name,4)="Rose" ;

explain select *from staffs
 where left(name,4)="Rose";

• 不能使用范围列右边的列

select *from staffs 
where name="Rose" and age>20 and pos="dev";

explain select *from staffs 
where name="Rose" and age>20 and pos="dev";
--pos列不能使用索引

• 尽量少用*,使用覆盖索引

select age,name,pos 
where name="Rose" and age=29 and pos="dev";

explain select age,name,pos 
where name="Rose" and age=29 and pos="dev";

• mysql在使用不等于（！= 或者<>）时会导致无法使用索引，全表扫描

explain select * from staffs
 where name!="Rose";

• is null 或者is not null

--全表扫描
explain select * from staffs
 where name is  not null;
--null
explain select * from staffs 
where name is  null;

• like会导致全表扫描(左边%和两边%会失效)(右边%是range)

--可以使用覆盖索引解决%%
--下面是全表扫描
 explain select * from staffs 
 where name like "%R_s%";
 
 explain select * from staffs 
 where name like "%R_s";

• 字符串不加引号或者数字加引号，导致索引失效

explain select *from *from staffs 
where age="28";
--假设插入了一个name为“39”的记录
explain select *from *from staffs 
where name=39;

• 少用or

explain select *from staffs 
where name="Rose" or name="Joke";