13. 索引失效分析

1. 全值匹配

# 条件与索引一一对应
explain select * from tuser where name = 'zhaoyun' and age = 1 and sex = '1';

2. 最左前缀

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

# 错误案例，没有最左列
explain select * from tuser where age = 23;

# 中间索引断（带头索引生效，其他索引失效）
explain select * from tuser where name = 'aa' and sex = '1';
explain select * from tuser where name = 'aa' and sex = '1' and age = 23;
explain select * from tuser where name = 'aa' and sex = 1 and age = 23;

## 比较key_len,第二条sql使用到的索引最多

3. 不要在索引上做计算

不要进行这些操作：计算、函数、自动/手动类型转换，不然会导致索引失效而转向全表扫描

explain select * from tuser where loginname='1';

# type = all,索引失效
explain select * from tuser where left(loginname,1)='1';

4.范围条件右边的列失效

不能继续使用索引中范围条件（bettween、<、>、in等）右边的列

explain select * from tuser where name='asd' and age>20 and sex='1';

5.尽量使用覆盖索引

尽量使用覆盖索引（只查询索引的列），也就是索引列和查询列一致，减少select *

explain select * from tuser ; 

explain select name,loginname from tuser ;

explain select name,age,sex from tuser ; 

explain select loginname from tuser ; 

6.索引字段上不要使用不等

索引字段上使用（！= 或者 < >）判断时，会导致索引失效而转向全表扫描

7.主键索引字段上不可以判断null

主键字段上不可以使用 null

索引字段上使用 is null / is not null 判断时，可使用索引

explain select * from tuser where name is null; 

explain select * from tuser where name is not null;

explain select * from tuser where name is not null; 

explain select * from tuser where loginname is not null; 

# 没有使用索引
explain select * from tuser where id is not null;

8.索引字段使用like不以通配符开头

索引字段使用like以通配符开头（‘%字符串’）时，会导致索引失效而转向全表扫描

explain select * from tuser where name like 'a%';

# 索引失效
explain select * from tuser where name like '%a';

由结果可知，like以通配符结束相当于范围查找，索引不会失效。与范围条件（bettween、<、>、in等）不同的是： 不会导致右边的索引失效。

问题：解决like ‘%字符串%’时，索引失效问题的方法？

使用覆盖索引可以解决。

explain select name , age,sex from tuser where name like '%a%'; 

9.索引字段字符串要加单引号

索引字段是字符串，但查询时不加单引号，会导致索引失效而转向全表扫描

explain select * from tuser where name=123;

10.索引字段不要使用or

索引字段使用 or 时，会导致索引失效而转向全表扫描

explain select * from tuser where name='asd' or age=23;