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：额外的信息

Distinct:MySQL发现第1个匹配行后,停止为当前的行组合搜索更多的行。
Not exists:MySQL能够对查询进行LEFT JOIN优化,发现1个匹配LEFT JOIN标准的行后,不再为前面的的行组合在该表内检查更多的行。
range checked for each record (index map: #):MySQL没有发现好的可以使用的索引,但发现如果来自前面的表的列值已知,可能部分索引可以使用。
Using filesort:MySQL需要额外的一次传递,以找出如何按排序顺序检索行。
Using index:从只使用索引树中的信息而不需要进一步搜索读取实际的行来检索表中的列信息。
Using temporary:为了解决查询,MySQL需要创建一个临时表来容纳结果。
Using where:WHERE 子句用于限制哪一个行匹配下一个表或发送到客户。
Using sort_union(...), Using union(...), Using intersect(...):这些函数说明如何为index_merge联接类型合并索引扫描。
Using index for group-by:类似于访问表的Using index方式,Using index for group-by表示MySQL发现了一个索引,可以用来查询GROUP BY或DISTINCT查询的所有列,而不要额外搜索硬盘访问实际的表。

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

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

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

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