使用 explain 查看 MySQL 中 SQL 语句的执行情况

Question

再 SQL 优化中，我们经常需要去找那些查询比较耗时的语句，找到以后我们还要对其进行优化，些时我们常常用到 explain 这个命令来查看一个这些 SQL 语句的执行情况，查看该 SQL 语句有没有使用上了索引，有没有做全表扫描，这都可以通过 explain 命令来查看。所以我们深入了解 MySQL 的基于开销的优化器，还可以获得很多可能被优化器考虑到的访问策略的细节，以及当运行 SQL 语句时哪种策略预计会被优化器采用。

使用方法

在 select 语句前加上 explain 就可以了,如

各个属性的含义

id

select 查询的序列号；

select_type

select 查询的类型，主要是区别普通查询和联合查询、子查询之类的复杂查询；

table

输出的行所引用的表；

type

联合查询所使用的类型，type 显示的是访问类型，是较为重要的一个指标，结果值从好到坏依次是：

system > const > eq_ref > ref > fulltext > ref_or_null > index_merge
> unique_subquery > index_subquery > range > index > ALL

一般来说，得保证查询至少达到range级别，最好能达到 ref

possible_keys

指出MySQL能使用哪个索引在该表中找到行。如果是空的，没有相关的索引。这时要提高性能，可通过检验 WHERE 子句，看是否引用某些字段，或者检查字段不是适合索引；

key

显示MySQL实际决定使用的键。如果没有索引被选择，键是 NULL；

key_len

显示MySQL决定使用的键长度。如果键是 NULL，长度就是 NULL。文档提示特别注意这个值可以得出一个多重主键里 mysql 实际使用了哪一部分；

ref

显示哪个字段或常数与 key 一起被使用；

rows

这个数表示 mysql 要遍历多少数据才能找到，在 innodb 上是不准确的；

Extra

• 如果是Only index，这意味着信息只用索引树中的信息检索出的，这比扫描整个表要快。
• 如果是where used，就是使用上了where限制。
• 如果是impossible where 表示用不着where，一般就是没查出来啥。
• 如果此信息显示 Using filesort 或者 Using temporary 的话会很吃力，WHERE和ORDER BY的索引经常无法兼顾，如果按照 WHERE 来确定索引，那么在 ORDER BY 时，就必然会引起 Using filesort，这就要看是先过滤再排序划算，还是先排序再过滤划算。

常见的名词解释

Using filesort

MySQL需要额外的一次传递，以找出如何按排序顺序检索行。

Using index

从只使用索引树中的信息而不需要进一步搜索读取实际的行来检索表中的列信息。

Using temporary

为了解决查询，MySQL 需要创建一个临时表来容纳结果。

ref

对于每个来自于前面的表的行组合，所有有匹配索引值的行将从这张表中读取

ALL

完全没有索引的情况，性能非常地差劲。

index

与 ALL 相同，除了只有索引树被扫描。这通常比 ALL 快，因为索引文件通常比数据文件小。

SIMPLE

简单 SELECT，不使用 UNION 或子查询

explain 手册查询

id

SELECT 识别符。这是SELECT的查询序列号

select_type

SELECT 类型，可以为以下任何一种：

• SIMPLE：简单SELECT(不使用UNION或子查询)
• PRIMARY：最外面的SELECT
• UNION：UNION中的第二个或后面的SELECT语句
• DEPENDENT UNION：UNION中的第二个或后面的SELECT语句,取决于外面的查询
• UNION RESULT：UNION 的结果
• SUBQUERY：子查询中的第一个SELECT
• DEPENDENT SUBQUERY：子查询中的第一个SELECT,取决于外面的查询
• DERIVED：导出表的SELECT(FROM子句的子查询)

table

输出的行所引用的表

type

联接类型。下面给出各种联接类型,按照从最佳类型到最坏类型进行排序：

• system：表仅有一行(=系统表)。这是const联接类型的一个特例。
• const：表最多有一个匹配行,它将在查询开始时被读取。因为仅有一行,在这行的列值可被优化器剩余部分认为是常数。const表很快,因为它们只读取一次!
• eq_ref：对于每个来自于前面的表的行组合,从该表中读取一行。这可能是最好的联接类型,除了const类型。
• ref：对于每个来自于前面的表的行组合,所有有匹配索引值的行将从这张表中读取。
• ref_or_null：该联接类型如同ref,但是添加了MySQL可以专门搜索包含NULL值的行。
• index_merge：该联接类型表示使用了索引合并优化方法。
• unique_subquery：该类型替换了下面形式的IN子查询的ref： value IN (SELECT primary_key FROM single_table WHERE some_expr) unique_subquery是一个索引查找函数,可以完全替换子查询,效率更高。
• index_subquery：该联接类型类似于unique_subquery。可以替换IN子查询,但只适合下列形式的子查询中的非唯一索引： value IN (SELECT key_column FROM single_table WHERE some_expr)
• range：只检索给定范围的行,使用一个索引来选择行。
• index：该联接类型与ALL相同,除了只有索引树被扫描。这通常比ALL快,因为索引文件通常比数据文件小。
• ALL：对于每个来自于先前的表的行组合,进行完整的表扫描。

possible_keys

指出MySQL能使用哪个索引在该表中找到行

• key 显示MySQL实际决定使用的键(索引)。如果没有选择索引,键是NULL。
• key_len 显示MySQL决定使用的键长度。如果键是NULL,则长度为NULL。
• ref 显示使用哪个列或常数与key一起从表中选择行。
• rows 显示MySQL认为它执行查询时必须检查的行数。多行之间的数据相乘可以估算要处理的行数。
• filtered 显示了通过条件过滤出的行数的百分比估计值。

Extra

该列包含 MySQL 解决查询的详细信息

• 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查询的所有列,而不要额外搜索硬盘访问实际的表。