mysql索引失效的几种情况

为了验证在哪些情况下会导致索引失效，这里我们借助了一个分析关键字 explain来进行分析。explain的用法是在执行SQL语句之前加上这个关键字即可。如下所示。

explain ｓｅｌｅｃｔ * FROM `sys_oper_log`

并且其查询结果列如下

id：选择标识符，id越大优先级越高，越先被执行。select_type：表示查询的类型table：输出结构的表partitions:匹配分区type:表示表的连接类型possible_keys:表示查询的时候可能用到的索引key：表示实际使用索引key_len:索引字段的长度ref:列与索引的比较rows:大概估计的执行行数filtered:按照条件过滤表的百分比Extra:执行情况描述说明

其中比较重要的字段就是type，type有以下的一些值

all:表示全表扫描index:表示索引遍历range:表示范围查找index_subquery：在子查询中使用了refunique_subquery:在子查询中使用了eq_refref_or_null 对null进行了索引优化的reffulltext 使用全文索引ref 使用了非唯一性索引查找eq_ref：在join中使用了主键或者是唯一键关联const：将一个主键放到了where的后面作为查询条件。索引失效情况一：非最左匹配

所谓的最左匹配是指以最左边的起点字进行查询可以使用联合索引，否则就不能使用联合索引。

这里我们假设使用索引的字段顺序是A+B+C，如果是最左匹配那么如下的一些索引都会被使用到到

A+B+CA+BA+C

但是对于B+C的操作则不能使用到联合索引，这就是最左匹配原则。

索引失效情况二：错误的使用了模糊查询

我们常用的模糊查询的匹配方式有如下几种

like ‘张%‘like ‘%张‘like ‘%张三%‘

这三种情况只有第一种情况会用到索引，其他的则不会使用到索引

索引失效情况三：进行了列运算

如果在执行SQL的过程中使用索引列使用了运算，那么就会导致索引失效。

例如在执行使用id查询的时候执行了如下的操作。

ｓｅｌｅｃｔ * operation_log where id+1=2索引失效情况四：使用了函数

在进行查询的时候，如果查询列使用了任意的MySQL函数都会导致索引失效，例如，在有些判断中使用了count函数。

索引失效情况五：进行了类型转换

如果在查询的时候对索引列进行了类型的转换那么也不会使用索引，例如我们将一个string类型的数据转换成了一个int类型的数据，则不会触发索引。

索引失效情况六：使用 is not null

当在一个查询中出现了 is not null 的时候也会导致使用的索引失效的情况发生。例如在我们查询某些数据的时候会使用到 is not null 来判断数据字段是否为空。