Mysql 之索引优化及索引失效

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

2.不在索引上左任何操作(计算,函数，(自动or手动)类型转换),会导致索引失效而转向全表扫描

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

4.尽量使用覆盖索引(只访问索引的查询(索引列和查询列一致)),减少select*

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

6.is null,is not null也无法使用索引

7.like以通配符开头('%abc')mysql索引失效会变成全表扫描

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

9.少用or,用它来连接时会索引失效

 

在上篇mysql索引demo中student表基础上创建index_name_age_sex组合索引(name,age,sex)

 

最佳左前缀法则

如图,在跳过age的时候只使用了部分索引，而且Extra还出现了Using index conidtion意味着虽然查询的时候使用了索引，但还是要进行回表查询数据，而最后一条sql，直接跳过name和age则完全没有使用索引。。。。

不在索引上左任何操作(计算,函数，(自动or手动)类型转换),会导致索引失效而转向全表扫描)

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

根据key可以得知都有使用索引，但根据key_len对比，发现使用了age>23的范围查询，最后面的sex的索引失效了，并且还需要进行回表查询

尽量使用覆盖索引(只访问索引的查询(索引列和查询列一致)),减少select*

 

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

is null,is not null也无法使用索引

通过对比发现，当is null或者 is not null处于第一个索引字段，则根本不会使用索引进行查询，当使用在中间字段或者最好字段的时候，则查询使用索引，但还是要回表查数据，因此，最好不要用is null或者is not null,非要用的话，则放于索引字段顺序的最后一位.

like以通配符开头('%abc')mysql索引失效会变成全表扫描

通过上图得出like 两边都有%,和字段前面有%,索引会失效,%在字段后面索引有效，

但实际业务中,%一般都会是前后使用，这时怎么使索引有效?对比下面2张图

查询的列只要有组合索引的一个字段，也就是使用覆盖索引,索引便有效，但只要有一个查询字段未建索引，索引便会失效.

删除调之前的索引,建立组合索引stuName(stuName,gradename,age)，如下图

  gradeName like'一%年%'的时候使用范围查询，所有索引都有使用，也就说当like的条件%前面是常量的时候,索引有效.

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

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