索引失效总结

建表语句

CREATE TABLE staffs(
id INT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(24)NOT NULL DEFAULT’’ COMMENT’姓名’,
age INT NOT NULL DEFAULT 0 COMMENT’年龄’,
pos VARCHAR(20) NOT NULL DEFAULT’’ 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());

创建复合索引

索引失效案例分析

1. 全值匹配我最爱

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’;

explain select * from staffs where age=23 and pos=‘dev’;

explain select * from staffs where name=‘july’ and pos=‘dev’;

带头大哥不能‘死’，中间兄弟不能断

2. 最佳最前缀法则

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

3. 不在索引列上做任何操作（计算，函数、（自动or手动）类型转换），会导致索引失效而转向全表扫描

explain select * from staffs where left(name,4)=‘July’;

索引列上少操作

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

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

范围之后全失效

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’;

6. mysql在使用不等于（！=或者<>）的时候无法使用索引会导致范围扫描

explain select * from staffs where name!=‘july’;

7. is null，is not null也无法使用索引

explain select * from staffs where name is not null;

8. like以通配符（‘%abc…’）mysql索引失效会变成全表扫描的操作

explain select * from staffs where name like ‘%July%’;

explain select * from staffs where name like ‘%July’;

explain select * from staffs where name like ‘July%’;

如何解决like '%字段%'索引不被使用的方法？
------可以使用覆盖索引

9. 字符串不加单引号索引失效

explain select * from staffs where name=‘2000’;

explain select * from staffs where name=2000;

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

explain select * from staffs where name=‘July’ or name=‘z3’;

小总结

优化总结口诀

全值匹配我最爱，最左前缀要遵守；
带头大哥不能死，中间兄弟不能断；
索引列上少计算，范围之后全失效；
LIKE百分写最右，覆盖索引不写*；
不等空值还有or，索引失效要少用；