mysql 索引在什么情况下失效

• 使用 like 模糊匹配 左边有% 如 EXPLAIN select * from uic_user where user_id like "%bb%"

  尽量使用 select * from uic_user where user_id like "qb%" ，这种方式不失效

• 索引列上 使用了 函数 或者计算 如 select * from uic_user where length (user_id ) = 7

• 联合索引不满足 最左前缀原则 如创建索引 KEY idx_code_age_name (code,age,name)

  联合索引有效sql:

  select * from user where code='101' ； select * from user where code='101' and name ="bb" ;

  结论：code字段是索引字段的第一个 ，在where 子句中存在就走联合索引

  联合索引失效 sql :

  select * from user where age=21 and name='bb'; select * from user where name='bb' ;

• 在《阿里巴巴开发手册》中明确说过，查询sql中禁止使用select * , select code,name from uic_user where name="bb"

  从图中执行结果不难看出，该sql语句这次走了全索引扫描，比全表扫描效率更高。其实这里用到了：覆盖索引。

  如果select语句中的查询列，都是索引列，那么这些列被称为覆盖索引。这种情况下，查询的相关字段都能走索引，索引查询效率相对来说更高一些。

  而使用select *查询所有列的数据，大概率会查询非索引列的数据，非索引列不会走索引，查询效率非常低。

• 索引字段类型不匹配 ，如code 索引是varchar , 查询类型使用的是整型

  如果 code 索引是整型 ，查询类型是“1” 字符串 ，这种情况可以走索引，做了字符串1 隐式转换 为整型

• 使用or 关键字查询 非索引字段 如 select * from user where code='101' or name ="bb" ;

• 左连接或者右连接查询字段编码格式不一样

• 范围查询 not in 和 not exsists 使索引失效

  假如我们想查出height在某些范围之内的用户，这时sql语句可以这样写：

  explain select * from user where height in (173,174,175,176);
  执行结果：

  

  从图中可以看出，sql语句中用in关键字是走了索引的。

  有时候使用in关键字时性能不好，这时就能用exists关键字优化sql了，该关键字能达到in关键字相同的效果：

  explain select * from user  t1 where  exists (select 1 from user t2 where t2.height=173 and t1.id=t2.id)

  执行结果：

  从图中可以看出，用exists关键字同样走了索引。

• 上面演示的两个例子是正向的范围，即在某些范围之内。

  那么反向的范围，即不在某些范围之内，能走索引不？

  话不多说，先看看使用not in的情况：

  explain select * from user where height not in (173,174,175,176);
  执行结果：

•  

• 你没看错，索引失效了。

  看如果现在需求改了：想查一下id不等于1、2、3的用户有哪些，这时sql语句可以改成这样：

  explain select * from user where id  not in (173,174,175,176);
  
  执行结果：

  

  你可能会惊奇的发现，主键字段中使用not in关键字查询数据范围，任然可以走索引。而普通索引字段使用了not in关键字查询数据范围，索引会失效。

  除此之外，如果sql语句中使用not exists时，索引也会失效。具体sql语句如下：

  explain select * from user  t1 where  not exists (select 1 from user t2 where t2.height=173 and t1.id=t2.id)
  
  执行结果：

    从图中看出sql语句中使用not exists关键后，t1表走了全表扫描，并没有走索引。