Mysql索引失效案例分析

新建表staffs的索引为：idx_nameAgePos；
1、尽量使用全值匹配：即索引包含哪些字段，就使用哪些字段查询；

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

2、最佳左前缀法则：查询从索引的最左前列开始，且不跳过索引中的列：

// 部分匹配
select * from staffs where name = 'July';

// 部分匹配
select * from staffs where name = 'July' and pos = 'dev';

// 索引失效
select * from staffs where pos = 'dev';

// 索引失效
select * from staffs where age = 23 and pos = 'dev';

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

// 索引匹配
explain select * from staffs where name = 'July';

// 索引失效（left为截取字符串函数，mysql自带函数，类似于java的subString）
explain select * from staffs where left(name, 4) = 'July';

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

explain select * from staffs where name = 'July' and age = 25 and pos = 'manager';

explain select * from staffs where name = 'July' and age > 25 and pos = 'manager';

5、尽量使用覆盖索引（只访问索引的查询（索引列和查询列一致）），减少select *；

6、在使用不等于（!=或<>)的时候无法使用索引会导致全表扫描；
7、is null、is not null也无法使用索引；
新建表user的索引为：idx_user_nameAge；
8、like以通配符开头（‘%abc…’），mysql索引失效会变成全表扫描操作；

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

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

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

Q：如何解决like’%字符串%'时索引不被使用？
A：使用索引全覆盖（索引字段与查询字段一致）。
9、字符串不加单引号会导致索引失效（涉及隐性转换）；

explain select * from user where name = '2000';

explain select * from user where name = 2000;

10、少用or，用它连接时会导致索引失效。

explain select * from user where name = '2000' or name = '1aa1';

总结：
假设index(a,b,c)

where语句	索引是否被使用
where a = 3	是（使用到a）
where a = 3 and b = 5	是（使用到a,b，但c未被使用）
where a = 3 and a = 5 and c = 4	是（使用到a,b,c）
where b = 3 或者 where b = 3 and c = 4 或者 where c = 4	否
where a = 3 and c = 5	是（使用到a，缺少b，c未被使用）
where a = 3 and b > 5 and c = 5	是（使用到a,b， c在范围之后，未被使用）
where a = 3 and b like ‘kk%’ and c = 4	是（使用到a,b，c在范围之后，未被使用）

优化总结口诀
全值匹配我最爱，最左前缀要遵守；
带头大哥不能死，中间兄弟不能断；
索引列上少计算，范围之后全失效；
like百分写最右，覆盖索引不写星；
不等控制还有or，索引失效要少用。