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

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