mysql 索引失效的几种情况

索引的类型

针对InnoDB
聚簇索引 ：主键索引，b+树叶子节点指向的是真实数据；
非聚簇索引：非主键索引，b+树叶子节点存储的是聚簇索引和非聚簇索引值；

全值索引 最佳左前缀法则失效

最佳左前缀法则：如果索引了多列，要遵守最左前缀法则。指的是查询从索引的最左前列开始并且不跳过索引中的列。
但常量查询时，mysql会对搜索条件顺序进行优化
index（a,b,c）
where a= “a” and c= “c” and b = “b” 可以使用索引
where a= “a” order b,c 可以用到索引
where a= “a” order c,b 无法使用
where a= “a” order b,c 可以使用索引
where a= “a” and b = “b” order c,b 可以使用索引，因为b此时已经为常量；
where b=“b” 或者 where b=“b” and c=“c” 或者 where c =“c” ，无法使用索引，因为跳过了a列；
group by 基本上都需要进行排序，会有临时表产生。

不在索引列上做任何操作

（计算、函数、自动or手动类型转换），会导致索引失效而转向全表扫描。

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

在范围条件之后索引全失效

尽量使用覆盖索引

mysql在使用不等于的时候无法使用索引，会导致全表扫描

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

like以通配符开头mysql索引时效会变成全表扫描

加在右边是范围查询；加在左边是全表扫描；对于必须使用两边百分号的数据，使用覆盖索引解决，查询字段与索引字段个数顺序一致；
index（a,b,c）
where a= “a” and b like"b%" and c=“c” ,可以使用索引，使用到a,b,c
where a= “a” and b like"%b" and c=“c” ,可以使用索引，只使用到a
where a= “a” and b like"%b%" and c=“c” ,可以使用索引，只使用到a
where a= “a” and b like"b%b%" and c=“c” ,可以使用索引，使用到a,b,c

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

使用了mysql隐式的类型转换

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

左右连接查询索引建立方式

左连接，加右表外键索引；因为是由左连接特性决定的，left join 条件用于确定如何从右表搜索行，左边一定有，而右边是我们的关键点。
右连接，加左表外键索引；右连接相反同上。

一般性建议

对于单建索引，尽量选择针对当前query过滤性更好的索引
在选择组合索引的时候，当前query中过滤性最好的字段在索引字段顺序中，位置越靠前越好
在选择组合索引的时候，尽量选择可以包含当前query中的where字句中更多字段的索引；
尽可能通过分析统计信息和调整query的写法来达到选择合适索引的目的