MySQL中有哪些情况下数据库索引会失效详析

前言

要想分析MySQL查询语句中的相关信息，如是全表查询还是部分查询，就要用到explain.

索引的优点

索引的缺点

一、explain

用法：explain +查询语句。

id：查询语句的序列号，上面图片中只有一个select 语句，所以只会显示一个序列号。如果有嵌套查询，如下

select_type：表示查询类型，有以下几种

　　simple:简单的 select （没有使用 union或子查询）

　　primary:最外层的 select。

　　union:第二层，在select 之后使用了 union。

　　dependent union:union 语句中的第二个select，依赖于外部子查询

　　subquery:子查询中的第一个 select

　　dependent subquery:子查询中的第一个 subquery依赖于外部的子查询

　　derived:派生表 select（from子句中的子查询）

table：查询的表、结果集

type：全称为"join type"，意为连接类型。通俗的讲就是mysql查找引擎找到满足SQL条件的数据的方式。其值为：

　　性能从上到下依次降低。

possible_keys：可能用到的索引

key：使用的索引

ref：ref列显示使用哪个列或常数与key一起从表中选择行。

rows：显示MySQL认为它执行查询时必须检查的行数。多行之间的数据相乘可以估算要处理的行数。

Extra：额外的信息

二、数据库不使用索引的情况

下面举的例子中，GudiNo、StoreId列都有单独的索引。

2.1、like查询已 '%...'开头，以'xxx%'结尾会继续使用索引。

下图中第一句使用的%，没有使用索引，从rows为224147，使用索引rows为1。

2.2 where语句中使用 <>和 !=

2.3 where语句中使用 or，但是没有把or中所有字段加上索引。

这种情况，如果需要使用索引需要将or中所有的字段都加上索引。

2.4 where语句中对字段表达式操作

2.5 where语句中使用Not In

看了别人写的文章，有说“应尽量避免在where 子句中对字段进行null 值判断，否则将导致引擎放弃使用索引而进行全表扫描”，实测没有全表扫描。

"对于多列索引，不是使用的第一部分，则不会使用索引",实测即使多索引，没有使用第一部分，也会命中索引，没有全表扫描。

总结