索引失效的几种情况和原因剖析

在网上看到很多关于索引失效的情况，但是看了感觉都讲的不过明确，或者只告诉你这么查询会导致索引失效但是却没有告诉你索引失效的原因，这里做一些详细的说明。
一般我们常见的出现索引失效的有这么几个原因
1，使用or关键字（但是并不是所有带or的查询都会失效，如果有两个字段，两个字段都有索引就不会失效，会走两个索引）
2，使用like关键字（但是并不是所有like查询都会失效，只有在查询时字段最左侧加%和左右侧都加%才会导致索引失效）
3，组合索引（如果查询的字段在组合索引中不是最左侧的字段，那么该组合索引是不会生效的。即左前缀原则）
4, 索引列有运算符!=
5，索引列使用了函数
6，索引列使用is null 或者 is not null不一定失效
7，索引列使用>或者<不一定失效

不走索引的原因是因为mysql中innodb搜索引擎的数据结构是B+树，索引查询b+树查询是通过二分法进行查询的，这就要求叶子节点上的数据必须是有序的，不然会走全表查询。

在这个组合索引中，第一个字段是按照字母排序的，是有序的，这是如果我们根据name字段去查询的话,那么数据库是会走索引的。
在第一个字段都相等的情况下，第二个字段是才会是有序的
所以如果只有根据第一个索引字段查询出来有序排列以后，后面的其他索引字段才可以进行二分法查询。才会走索引，这就是左前缀原则。

在检查索引失效的时候我们用到的是explain查看sql查询的执行计划，如下图：

对于没使用过explain执行计划的朋友，我在这里对这些字段做一些简单的说明。
select_type: 主要是用于区别普通查询、联合查询、子查询等的复杂查询。

table：显示这一行的数据是关于哪张表的

type：这是重要的列，显示连接使用了何种类型。从最好到最差的连接类型为system，const，eq_ref，ref，fulltext，ref_or_null，index_merge，unique_subquery，index_subquery，range，index，ALL
type意味着类型，这里的type官方全称是“join type”，意思是“连接类型”,这样很容易给人一种错觉觉得必须需要俩个表以上才有连接类型。事实上这里的连接类型并非字面那样的狭隘，它更确切的说是一种数据库引擎查找表的一种方式，在《高性能mysql》一书中作者更是觉得称呼它为访问类型更贴切一些。mysql5.7中type的类型达到了14种之多，这里只记录和理解最重要且经常遇见的六种类型，它们分别是all,index,range,ref,eq_ref，const。从左到右，它们的效率依次是增强的。撇开sql的具体应用环境以及其他因素，你应当尽量优化你的sql语句，使它的type尽量靠左，但实际运用中还是要综合考虑各个方面的。接下来，为了演示和重现这几种连接类型，我新建了一个数据测试表，以方面更好的理解这五种类型。

possible_keys：显示可能应用在这张表中的索引。如果为空，没有可能的索引。可以为相关的域从where语句中选择一个合适的语句

key： 实际使用的索引。如果为null，则没有使用索引。很少的情况下，mysql会选择优化不足的索引。这种情况下，可以在select语句中使用use index（indexname）来强制使用一个索引或者用ignore index（indexname）来强制mysql忽略索引

key_len：使用的索引的长度。在不损失精确性的情况下，长度越短越好

ref：显示索引的哪一列被使用了，如果可能的话，是一个常数

rows：mysql认为必须检查的用来返回请求数据的行数

extra：关于mysql如何解析查询的额外信息。但这里可以看到的坏的例子是using temporary和using filesort。
useing filesort 说明mysql会对数据使用一个外部的索引排序，而不是按照表内的索引顺序进行读取。MySQL中无法利用索引完成的排序操作称为“文件排序”。
using temporary使用了用临时表保存中间结果，MySQL在对查询结果排序时使用临时表。常见于排序order by和分组查询group by。