索引失效

一、建表SQL

create table if not exists staffs(id INT PRIMARY KEY AUTO_INCREMENT, NAME VARCHAR(24) NOT NULL COMMENT '姓名', age INT NOT NULL DEFAULT 0 COMMENT '年龄', pos VARCHAR(20) NOT NULL COMMENT '职位', add_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '入职时间')CHARSET utf8 COMMENT '员工记录表';
insert into staffs(name,age,pos,add_time) values('z3',22,'manager',NOW());  
insert into staffs(name,age,pos,add_time) values('July',23,'dev',NOW());
insert into staffs(name,age,pos,add_time) values('2000',23,'dev',NOW());
alter table staffs add index idx_staffs_nameAgePos(name,age,pos);

二、索引失效的情况

 

2.1 全值匹配我最爱

全值匹配指的是我要查询的语句的字段和顺序恰好和建立的索引的字段和顺序一致，否则索引失效。

✈ 总结：当索引是按照name,age,pos顺序建立的时候，如果查询条件不是以name开头，就会导致索引失效。总结一句话就是：带头大哥不能死！

2.2最佳左前缀法则

如果索引了多列，要遵守最左前缀法则，指的是查询从索引的最左前列开始并且不跳过索引中的列。

✈ 总结：当索引是按照name,age,pos顺序建立的时候，如果查询条件是以name开头，但是没有按顺序，就会导致后面的索引失效。总结一句话就是：中间兄弟不能断！

explain select * from staffs where name = 'july';

 

能用到索引

 

 explain select * from staffs where name = 'july' and age = 23;

explain select * from staffs where name = 'july' and age = 23 and pos = 'dev';

但是，以下情况没有索引...

原因？

最佳左前缀法则：如果索引了多列，要遵循最左前缀法则，指的是从索引的最左前列开始并且不跳过索引中的列

如果把索引的3个字段中间拆掉呢？也用到了索引，不过只是部分

explain select * from staffs where name = "july" and pos = 'dev';

2.3不在索引列上做任何操作

这里的任何操作包括计算、函数、（自动or手动）类型转换，会导致索引失效而转向全表扫描。总结一句话就是：索引列上不操作！

explain select * from staffs where name = "july";

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

left是一个函数，就是字符串截取的作用。原来的name加上left(name,1)之后，索引就失效了

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

explain select *from staffs where name = 'july' and age = 23 and pos = 'dev';

如果把age=23改为age>23

2.5 尽量使用覆盖索引

尽量使用索引的查询即索引列和查询列一致，减少select *

explain select *from staffs where name = 'july' and age > 23 and pos = 'dev';

explain select name,age,pos from staffs where name = 'july' and age > 23 and pos = 'dev';

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

用上索引

explain select * from staffs where name = 'july';  

用不上索引

explain select * from staffs where name != 'july';

用不上索引

explain select * from staffs where name <> 'july';

2.7 is null,is not null也可能导致索引失效

explain select * from staffs where name is null;

全null，Impossible WHERE因为该字段本来不为空。没有用到索引

explain select * from staffs where name is not null;

没有用到索引

2.8like以通配符开头（’%abc…’）索引失效会变成全表扫描

 explain select *from staffs where name like '%July';

全表扫描

explain select *from staffs where name like '%July%';

explain select *from staffs where name like 'July%';

type变为range了，说明通配符只有写在右边才不会失效

问题：解决like '%字符串%'时索引不被使用的方法? 使用覆盖索引

先建表

create table `tbl_user`( `id` INT(11) NOT NULL AUTO_INCREMENT, `NAME` VARCHAR(20) DEFAULT NULL, `age` INT(11) DEFAULT NULL, email VARCHAR(20) DEFAULT NULL, PRIMARY KEY(`id`) )ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET = utf8;
insert into tbl_user(name, age, email) values('1aa1',21,'b@163.com');
insert into tbl_user(name, age, email) values('2aa2',222,'a@163.com');
insert into tbl_user(name, age, email) values('3aa3','265','c@163.com');
insert into tbl_user(name, age, email) values('4aa4',21,'c@163.com');
insert into tbl_user(name, age, email) values('aa',121,'e@163.com'); 

after create index

create index idx_user_nameAge on tbl_user(name,age);

 

explain select name from tbl_user where name like '%aa%';

以下两种情况是失效的

explain select * from tbl_user where name like '%aa%';

多了个email的字段，超过索引字段的范围索引失效

explain select id,name,age,email from tbl_user where name like '%aa%';

2.9 字符串不加单引号索引失效

不加单引号，会引起数据类型的转换而导致全表扫描

如果要查name为2000的数据？

正确的方法：

select * from staffs where name ='2000';

错误的方法也能查出来：

select * from staffs where name =2000;

2.10 少用or，用它来连接时会索引失效

3. 总结

3.1 小练