MySQL索引优化（二）索引失效
本文主要说的是索引失效问题
创建表
create table if not exists 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 '入职时间'
)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);
1、左前缀法则
先看一个正常的
explain select * from staffs where name = 'July' and age = 23 and pos = 'dev';
结果如下
由ref下的三个const可知，三个索引都被使用到了，key_len的长度也可以体现。说明这个是正常的sql查询。
1）带头索引不能少
name字段的索引是带头索引，这里不使用name字段的索引。
explain select * from staffs where age = 23 and pos = 'dev';
结果如下
可知type类型变成了最糟糕的ALL，且key_len的长度为null，说明三个索引都没有用到。
2）中间索引不能丢
age字段的索引为中间索引，所以使用age字段的索引。
explain select * from staffs where name = 'July' and pos = 'dev';
结果如下
由ref下的一个const可知，索引只使用到了一个，也就是带头索引。
3）结论
综上，如果可以全值匹配就全值匹配（条件字段和索引一样），要遵守左前缀法则，查询从索引的最左前列开始并且不跳过索引中的列。
2、 不在索引列上做任何操作
这里的操作包括计算、函数、类型转换，会导致索引失效而转向全表扫描。
select * from staffs where name = 'July';
select * from staffs where left(name, 4) = 'July';
执行这两条sql都可以得到想要的结果。
执行
explain select * from staffs where left(name, 4) = 'July';
结果如下
会发现索引失效了。
3、 少用范围查询
执行
explain select * from staffs where name = 'July' and age = 23;
explain select * from staffs where name = 'July' and age > 23;
结果如下
type类型由ref降低为range。
4、减少使用select *
尽量使用覆盖索引（只访问索引的查询（索引列和查询列一致）），减少select *
explain select * from staffs where name = 'July' and age = 23;
只取想要的数据。
explain select name, age, pos from staffs where name = 'July' and age = 23;
结果如下
Extra出现了Using index，表明性能更优了。
5、不等于
使用不等于（!=或者<>）的时候无法使用索引会导致全表扫描。
explain select * from staffs where name = 'July' and age != 23;
type变为ALL，索引失效了。
6、is null，is not null
is null，is not null也无法使用索引。
explain select * from staffs where name = 'July' and age is null;
创建字段的时候，尽量避免空值null。
6、like和通配符
like以通配符开头（%abc）索引也会失效，变成全表扫描。
分别执行
explain select * from staffs where name = 'July';
explain select * from staffs where name like 'July';
explain select * from staffs where name like '%July';
结果如下
可以看见第三条sql的索引失效。所以like的时候，百分号加右边。
如何解决like ‘%字符串%’ 时索引不会被使用？
使用覆盖索引即可解决（只要查询的字段是索引字段即可）。
可以看到type变为了index，性能比ALL好了不少。
7、 字符串不加单引号
字符串不加单引号索引失效。
explain select * from staffs where name = 2000;
结果如下
type变成了ALL，这里也说明了MySQL底层会自动进行类型转换，隐性自动类型转换，也是我们需要避免的。
字符串不加单引号还会导致一个很严重的问题，就是行锁会升级为表锁。
8、 少用or
or也会导致索引失效。
explain select * from staffs where name = 'July' or name = 'z3';
可见索引失效了。