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

前言

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

索引的优点

• 大大减少了服务器需要扫描的数据量
• 可以帮助服务器避免排序或减少使用临时表排序
• 索引可以随机I/O变为顺序I/O
  

索引的缺点

• 需要占用磁盘空间，因此冗余低效的索引将占用大量的磁盘空间
• 降低DML性能，对于数据的任意增删改都需要调整对应的索引，甚至出现索引分裂
• 索引会产生相应的碎片，产生维护开销

一、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条件的数据的方式。其值为：

• system：系统表，表中只有一行数据
• const：读常量，最多只会有一条记录匹配，由于是常量，实际上只须要读一次。
• eq_ref：最多只会有一条匹配结果，一般是通过主键或唯一键索引来访问。
• ref：对于每个来自于前面的表的行组合,所有有匹配索引值的行将从这张表中读取
• fulltext：进行全文索引检索。
• ref_or_null：与ref的唯一区别就是在使用索引引用的查询之外再增加一个空值的查询。
• index_merge：查询中同时使用两个（或更多）索引，然后对索引结果进行合并，再读取表数据。
• unique_subquery：子查询中的返回结果字段组合是主键或者唯一约束。
• index_subquery：子查询中的返回结果字段组合是一个索引（或索引组合），但不是一个主键或唯一索引。
• rang：索引范围扫描。
• index：全索引扫描。
• all：全表扫描。

　　性能从上到下依次降低。

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。

2.2 where语句中使用 >和 !=

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

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

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

2.5 where语句中使用Not In

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

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

总结

以上就是这篇文章的全部内容了，希望本文的内容对大家的学习或者工作具有一定的参考学习价值，如果有疑问大家可以留言交流，谢谢大家对脚本之家的支持。