mysql 索引失效分析

大神总结的索引失效一句话理解后就不用看我下面的文章了，这就是

“模型数空运最快”

模：模糊查询like  %在前面索引失效

型：数据类型有变化索引失效

数：索引列使用了函数索引失效

空：索引列有空值索引失效

运：索引列有运算索引失效

最：复合索引不符合最左边原则索引失效

快：

      如果优化器认为使用全表比使用索引快，那就不会使用索引，说白了就是基于优化器或者统计信息，这个就是所谓的百分比，这里最重要的一点是使用索引一般情况下是回表，也就是发生随机IO,这个随着回表的量的大小变大，一般OLTP都是在表下查询少量数据情况，所以还是使用索引的情况会好

mysql 索引失效，mysql优化分析如下：

1、联合索引(多个字段组成的索引)----->最佳左前缀法则：查询从索引的最左前列开始并且不跳过索引中的列，中间有跳过的值，则其后面的索引会失效

 例如： 字段a，b，c按此顺序组成联合索引，

查询语句：

① select * from x where b=1 and c=1；

②  select * from x where c=1；

③  select * from x where c=2；

④  select * from x where a=1 and c=2；

⑤  select * from x where a=1 and b = 1 and c=2；

⑥ select * from x where a=1 and b >1 and c=2；

则①②③条查询语句都不走索引，因为字段a在创建索引排在第一个，如果没有他，联合索引不起效，切记跟创建索引的顺序有关

第④条语句，a字段走索引，c字段不走索引，因为a和c字段中间的b字段断开了，这b后面的字段索引都会失效

第5条语句，符合所有条件，所以a，b，c字段全都走索引

第6条语句， a，b字段走索引，c不走索引，主要是存储引擎不能使用索引中范围条件右边的列

 此情此景我想吟诗一首：火车跑的快，全靠头来带，中间数据变，后面止不前

2、范围查询( '<','>', 'like', 'in', 'or'，'时间查询' )和联合索引中全值匹配

   ① select * from x where a>1 and b = 1 and c=2；

   ② select * from x where a like 'tan%' and b = 1 and c=2；

   ③ select * from x where a in ('t1','t2') and b > 1 and c=2； 

   ④ select * from x where a = 't1' or a='t2' and b = 1 and c=2；

   ⑤ select id,a,b,c from x where a = 't1' or a='t2' and b = 1 and c=2；

      如上情况，第①②③④条语句都不一定走索引，要根据字段a后面的条件，产生的结果集在整个x表里的占比情况，查询的结果集较大，而且查询出来后需要回表查询，效率不高，还没有进行全表扫描快，则不会走索引;如果结果集很少则会走索引，并且后面的字段范围查询不受影响，也依旧遵从最佳左前缀法则。

     第⑤条语句走索引，原因是全值匹配。首先回忆一下mysql的索引树，针对二级索引，我们查询的结果在联合索引的索引树上都有，因此直接遍历二级索引树就可获取到结果，无需回表操作，因此mysql选择使用二级索引。  

    也就是说，某些场景我们是可以使用覆盖索引（即遍历索引树就可以取到返回的结果字段）来进行优化的。

      

其实这里用到了索引下推，什么是索引下推？

       对于联合索引(a,b,c)，按照最左前缀原则，sql语句 select * from x where a like 'tan%' and b = 1 and c=2; 按道理说应该只能用到a字段，在a字段值不确认情况下，b和c字段是不走索引的，但是实际情况是走索引的(也有可能不走，上面已经提过原因，我们这里假设筛选后的结果集数据占比很小)。

       在Mysql5.6以前，是先走a索引，找到结果集，然后在回表，通过比较b和c字段确认查找的记录。

      在Mysql5.6以后，先走a索引确定a的值，之后遍历二级索引树，根据b和c字段在过滤一遍，然后拿到结果集（过滤后的结果集会少很多）再去回表，这样提高回表时的效率。这就是索引下推。
 

注意：通过前面2点我们可以清楚的明白，联合索引，最主要是判断首字段是否走索引，以及什么时候回表查询

3、索引列上做了操作（计算、函数、自动或手动类型转换等表达式），会导致索引失效而转向全表扫描

4、mysql在使用不等于（!= 或 <>）

     这种情况跟上面第二种范围查询一样，走不走索引要看不等式后的条件，产生的结果集是否占总数据大小情况，只是这种查询大多数情况都是占比很大，所以一般不会走索引

5、is null，is not null也无法使用索引

6、like以通配符开头（'%abc..'）也会导致索引失效

　　通过覆盖索引可以解决like '%字符串%'索引失效的问题

　　例：假设以name，age字段建索引

create index idx_user_nameAge on tb_user(name,age);

查询字段只要有一个和覆盖索引沾边就行

但如果有超过索引的部分，索引就用不上了，所以用select * 就不能使用覆盖索引

7、字符串不加单引号，该字段以后的索引失效

8、少用or，用它来连接时会索引失效

9、少数据值的列也不应该增加索引，只有两种情况，且平均分布，加了索引反而降低速度

10、range的包含范围有一定的阈值，超过会进行全文扫描(例如时间索引)

这是我总结的mysql索引失效情况，有不对或者需要补充的希望大家指正