MySQL EXPLAIN 输出内容的含义

在优化 SQL 语句时，最常用的方式是使用 EXPLAIN 工具获取 SQL 语句的执行计划，之后通过分析，了解 MySQL 数据库将会如何执行对应的 SQL 语句，以便进行相应的语句调整、索引优化等。本文主要记录 EXPLAIN 输出内容的相应解释说明。

环境说明

系统：macOS 10.13.6 (17G65)
- 应用软件：mysql-5.6.40.tar.gz

主要使用 MySQL 官方示例数据库：sakila、employees。

EXPLAIN 基本语法

常用的语法如下：

explain [extended|partition] [format=json] select ...

在 mysql 5.5 版本之前，EXPLAIN 工具只对 SELECT 语句有效。从 mysql 5.6 版本以后，EXPLAIN 工具同时也能用于获取 DELETE、INSERT、REPLACE、UPDATE 语句的执行计划。

以下为 EXPLAIN 工具的常用示例：

explain select * from city where country_id = 103;

+----+-------------+-------+------+-------------------+-------------------+---------+-------+------+-------+
| id | select_type | table | type | possible_keys     | key               | key_len | ref   | rows | Extra |
+----+-------------+-------+------+-------------------+-------------------+---------+-------+------+-------+
|  1 | SIMPLE      | city  | ref  | idx_fk_country_id | idx_fk_country_id | 2       | const |   35 | NULL  |
+----+-------------+-------+------+-------------------+-------------------+---------+-------+------+-------+

使用 EXPLAIN 工具获取 SQL 语句的执行计划时，默认的输出格式为：traditional，当然除此之外，在 mysql 5.6 版本之后，还支持 json 输出格式。

EXPLAIN 字段说明

id

id 字段为数值类型，其表示，在执行一条 SQL 语句时，该 SQL 语句中的每个 SELECT 语句（JOIN 操作本身也属于 SELECT 操作）的执行顺序。

当 id 值相同时，则执行顺序为：从上到下。
当 id 值不同时，则执行顺序为：id 值越大的行对应的 SELECT 语句越先执行。
当 id 值相同时，则可以认为他们是同一分组，同一分组中执行顺序为：从上到下，在所有组中，id 值越大的越先执行。
当 id 值为 NULL 时，则表示这是一个结果集。

在以下执行计划中，id 值为 2 的行记录对应的 SELECT 语句优先执行（即：select 1 from sakila.actor limit 1），之后再执行 select (...) from sakila.film。

explain select (select 1 from sakila.actor limit 1) from sakila.film;

+----+-------------+-------+-------+---------------+---------------------+---------+------+------+-------------+
| id | select_type | table | type  | possible_keys | key                 | key_len | ref  | rows | Extra       |
+----+-------------+-------+-------+---------------+---------------------+---------+------+------+-------------+
|  1 | PRIMARY     | film  | index | NULL          | idx_fk_language_id  | 1       | NULL | 1000 | Using index |
|  2 | SUBQUERY    | actor | index | NULL          | idx_actor_last_name | 137     | NULL |  200 | Using index |
+----+-------------+-------+-------+---------------+---------------------+---------+------+------+-------------+

以下为 id 值都相同的示例，执行顺序为：第一行到最后一行顺序执行。

explain
select straight_join f.film_id
from film as f
    inner join film_actor as fa
        on f.film_id=fa.film_id and  fa.actor_id=1
    inner join actor as a using(actor_id);

+----+-------------+-------+--------+------------------------+--------------------+---------+------------------------+------+-------------+
| id | select_type | table | type   | possible_keys          | key                | key_len | ref                    | rows | Extra       |
+----+-------------+-------+--------+------------------------+--------------------+---------+------------------------+------+-------------+
|  1 | SIMPLE      | f     | index  | PRIMARY                | idx_fk_language_id | 1       | NULL                   | 1000 | Using index |
|  1 | SIMPLE      | fa    | eq_ref | PRIMARY,idx_fk_film_id | PRIMARY            | 4       | const,sakila.f.film_id |    1 | Using index |
|  1 | SIMPLE      | a     | const  | PRIMARY                | PRIMARY            | 2       | const                  |    1 | Using index |
+----+-------------+-------+--------+------------------------+--------------------+---------+------------------------+------+-------------+

以下为 id 值等于 NULL 的示例：

explain select 1 union all select 1;

+----+--------------+------------+------+---------------+------+---------+------+------+-----------------+
| id | select_type  | table      | type | possible_keys | key  | key_len | ref  | rows | Extra           |
+----+--------------+------------+------+---------------+------+---------+------+------+-----------------+
|  1 | PRIMARY      | NULL       | NULL | NULL          | NULL | NULL    | NULL | NULL | No tables used  |
|  2 | UNION        | NULL       | NULL | NULL          | NULL | NULL    | NULL | NULL | No tables used  |
| NULL | UNION RESULT | <union1,2> | ALL  | NULL          | NULL | NULL    | NULL | NULL | Using temporary |
+----+--------------+------------+------+---------------+------+---------+------+------+-----------------+

以下为一个相对复杂的 SQL 语句示例：

explain
select actor_id,
    (select 1
     from film_actor
     where film_actor.actor_id = der_1.actor_id limit 1)
from (
    select actor_id
    from actor limit 5
) as der_1
union all
select film_id,
    (select @var1 from rental limit 1)
from (
    select film_id,
        (select 1 from store limit 1)
    from film limit 5
) as der_2;

+----+----------------------+------------+-------+---------------+---------------------+---------+----------------+-------+-----------------+
| id | select_type          | table      | type  | possible_keys | key                 | key_len | ref            | rows  | Extra           |
+----+----------------------+------------+-------+---------------+---------------------+---------+----------------+-------+-----------------+
|  1 | PRIMARY              | <derived3> | ALL   | NULL          | NULL                | NULL    | NULL           |     5 | NULL            |
|  3 | DERIVED              | actor      | index | NULL          | idx_actor_last_name | 137     | NULL           |   200 | Using index     |
|  2 | DEPENDENT SUBQUERY   | film_actor | ref   | PRIMARY       | PRIMARY             | 2       | der_1.actor_id |     1 | Using index     |
|  4 | UNION                | <derived6> | ALL   | NULL          | NULL                | NULL    | NULL           |     5 | NULL            |
|  6 | DERIVED              | film       | index | NULL          | idx_fk_language_id  | 1       | NULL           |  1000 | Using index     |
|  7 | SUBQUERY             | store      | index | NULL          | idx_unique_manager  | 1       | NULL           |     2 | Using index     |
|  5 | UNCACHEABLE SUBQUERY | rental     | index | NULL          | idx_fk_staff_id     | 1       | NULL           | 16008 | Using index     |
| NULL | UNION RESULT         | <union1,4> | ALL   | NULL          | NULL                | NULL    | NULL           |  NULL | Using temporary |
+----+----------------------+------------+-------+---------------+---------------------+---------+----------------+-------+-----------------+

select_type

类型	说明
SIMPLE	简单查询（无 UNION、SUBQUERY 的 SELECT 语句）
PRIMARY	在复杂查询（包含 UNION、SUBQUERY 的 SELECT 语句）中，最外层的 SELECT 语句
UNION	在联合查询中，UNION 之后的 SELECT 语句
DEPENDENT UNION	在联合查询中，UNION 之后的 SELECT 语句，同时，该 SELECT 语句依赖外层的 SELECT 语句
UNION RESULT	联合查询的结果表
SUBQUERY	不在 FROM 子句中的子查询
DEPENDENT SUBQUERY	相关子查询
DERIVED	派生表（在 FROM 从句中的子查询）
MATERIALIZED	物化表（非相关子查询的一种优化类型）
UNCACHEABLE SUBQUERY	无法缓存的子查询
UNCACHEABLE UNION	无法缓存的联合查询

以下 SQL 语句，涵盖了所有的查询类型：

explain
select actor_id,
    (select 1
     from film_actor
     where film_actor.actor_id = der_1.actor_id limit 1)
from (
    select actor_id
    from actor limit 5
) as der_1
union all
select film_id,
    (select @var1 from rental limit 1)
from (
    select film_id,
        (select 1 from store limit 1)
    from film limit 5
) as der_2;

+----+----------------------+------------+-------+---------------+---------------------+---------+----------------+-------+-----------------+
| id | select_type          | table      | type  | possible_keys | key                 | key_len | ref            | rows  | Extra           |
+----+----------------------+------------+-------+---------------+---------------------+---------+----------------+-------+-----------------+
|  1 | PRIMARY              | <derived3> | ALL   | NULL          | NULL                | NULL    | NULL           |     5 | NULL            |
|  3 | DERIVED              | actor      | index | NULL          | idx_actor_last_name | 137     | NULL           |   200 | Using index     |
|  2 | DEPENDENT SUBQUERY   | film_actor | ref   | PRIMARY       | PRIMARY             | 2       | der_1.actor_id |     1 | Using index     |
|  4 | UNION                | <derived6> | ALL   | NULL          | NULL                | NULL    | NULL           |     5 | NULL            |
|  6 | DERIVED              | film       | index | NULL          | idx_fk_language_id  | 1       | NULL           |  1000 | Using index     |
|  7 | SUBQUERY             | store      | index | NULL          | idx_unique_manager  | 1       | NULL           |     2 | Using index     |
|  5 | UNCACHEABLE SUBQUERY | rental     | index | NULL          | idx_fk_staff_id     | 1       | NULL           | 16008 | Using index     |
| NULL | UNION RESULT         | <union1,4> | ALL   | NULL          | NULL                | NULL    | NULL           |  NULL | Using temporary |
+----+----------------------+------------+-------+---------------+---------------------+---------+----------------+-------+-----------------+

table

查询访问的表名或表别名。

此外 table 值又有以下几种情况：

表名	说明
`<unionM,N>`	该查询访问的是联合查询结果表，其中 M、N 为执行计划中 id 为 M、N 的行。
`<derivedN>`	该查询访问的是派生表，其中 N 为生成该派生表的 SELECT 语句 id。
`<subqueryN>`	该查询访问的是物化子查询表，其中 N 为物化子查询的 SELECT 语句 id。

partitions

查询涉及到的分区。

type

访问类型。

当指定 EXPLAIN 输出格式为：format=JSON 时，此字段名为：access_type。

以下为所有访问类型的说明：

类型	说明
NULL	不需要访问表
system	表中只有一行记录或空表，只能用于 myisam 和 memory 引擎表
const	当 MySQL 对查询进行优化将其转成常量时，它就会使用这个类型
eq_ref	出现在多表连接查询中，驱动表只有一行记录，同时连接字段都是非空唯一索引
ref	非唯一索引查找（或唯一索引的前缀查找）
fulltext	全文索引查找
ref_or_null	与 ref 类型一致，只是增加了 null 值的对比（测试似乎会被优化成 ref ）
unique_subquery	代替 eq_ref 针对 IN 子查询的优化，IN 子查询返回唯一索引值
index_subquery	与 unique_subquery 相似，但 IN 子查询返回非唯一索引值
range	索引范围扫描，使用 BETWEEN、IN、>、>= 等运算符对索引字段值进行比较
index_merge	使用多个索引匹配查找，最终获取合并结果（交集 AND、并集 OR）
index	全索引扫描
ALL	全表扫描（效率最低的一种访问方式）

NULL

explain select (select film_id from film where film_id = 1);

+----+-------------+-------+-------+---------------+---------+---------+-------+------+----------------+
| id | select_type | table | type  | possible_keys | key     | key_len | ref   | rows | Extra          |
+----+-------------+-------+-------+---------------+---------+---------+-------+------+----------------+
|  1 | PRIMARY     | NULL  | NULL  | NULL          | NULL    | NULL    | NULL  | NULL | No tables used |
|  2 | SUBQUERY    | film  | const | PRIMARY       | PRIMARY | 2       | const |    1 | Using index    |
+----+-------------+-------+-------+---------------+---------+---------+-------+------+----------------+

system

explain select * from mysql.func;

+----+-------------+-------+--------+---------------+------+---------+------+------+---------------------+
| id | select_type | table | type   | possible_keys | key  | key_len | ref  | rows | Extra               |
+----+-------------+-------+--------+---------------+------+---------+------+------+---------------------+
|  1 | SIMPLE      | func  | system | NULL          | NULL | NULL    | NULL |    0 | const row not found |
+----+-------------+-------+--------+---------------+------+---------+------+------+---------------------+

const

explain select film_id from film where film_id = 1;

+----+-------------+-------+-------+---------------+---------+---------+-------+------+-------------+
| id | select_type | table | type  | possible_keys | key     | key_len | ref   | rows | Extra       |
+----+-------------+-------+-------+---------------+---------+---------+-------+------+-------------+
|  1 | SIMPLE      | film  | const | PRIMARY       | PRIMARY | 2       | const |    1 | Using index |
+----+-------------+-------+-------+---------------+---------+---------+-------+------+-------------+

eq_ref

explain select city.city, country.country from city join country using(country_id) where city.city_id in (13,17,19,20,21);

+----+-------------+---------+--------+---------------------------+---------+---------+------------------------+------+-------------+
| id | select_type | table   | type   | possible_keys             | key     | key_len | ref                    | rows | Extra       |
+----+-------------+---------+--------+---------------------------+---------+---------+------------------------+------+-------------+
|  1 | SIMPLE      | city    | range  | PRIMARY,idx_fk_country_id | PRIMARY | 2       | NULL                   |    5 | Using where |
|  1 | SIMPLE      | country | eq_ref | PRIMARY                   | PRIMARY | 2       | sakila.city.country_id |    1 | NULL        |
+----+-------------+---------+--------+---------------------------+---------+---------+------------------------+------+-------------+

explain select * from city where country_id=103;

+----+-------------+-------+------+-------------------+-------------------+---------+-------+------+-------+
| id | select_type | table | type | possible_keys     | key               | key_len | ref   | rows | Extra |
+----+-------------+-------+------+-------------------+-------------------+---------+-------+------+-------+
|  1 | SIMPLE      | city  | ref  | idx_fk_country_id | idx_fk_country_id | 2       | const |   35 | NULL  |
+----+-------------+-------+------+-------------------+-------------------+---------+-------+------+-------+

fulltext

alter table film add column title_ft_test varchar(255) null after title;
update film set title_ft_test = title where film_id > 0;
alter table film add fulltext index `idx_ft_title_ft_test` (`title_ft_test`);

explain select * from film where match(title_ft_test) against('AC');

+----+-------------+-------+----------+----------------------+----------------------+---------+------+------+-------------+
| id | select_type | table | type     | possible_keys        | key                  | key_len | ref  | rows | Extra       |
+----+-------------+-------+----------+----------------------+----------------------+---------+------+------+-------------+
|  1 | SIMPLE      | film  | fulltext | idx_ft_title_ft_test | idx_ft_title_ft_test | 0       | NULL |    1 | Using where |
+----+-------------+-------+----------+----------------------+----------------------+---------+------+------+-------------+

ref_or_null
unique_subquery

value IN (SELECT primary_key FROM single_table WHERE some_expr)

index_subquery

value IN (SELECT key_column FROM single_table WHERE some_expr)

range

explain select * from city where country_id > 100;

+----+-------------+-------+-------+-------------------+-------------------+---------+------+------+-----------------------+
| id | select_type | table | type  | possible_keys     | key               | key_len | ref  | rows | Extra                 |
+----+-------------+-------+-------+-------------------+-------------------+---------+------+------+-----------------------+
|  1 | SIMPLE      | city  | range | idx_fk_country_id | idx_fk_country_id | 2       | NULL |   67 | Using index condition |
+----+-------------+-------+-------+-------------------+-------------------+---------+------+------+-----------------------+

index_merge

explain select * from film_actor where film_id = 3 or actor_id = 3;

+----+-------------+------------+-------------+------------------------+------------------------+---------+------+------+--------------------------------------------------+
| id | select_type | table      | type        | possible_keys          | key                    | key_len | ref  | rows | Extra                                            |
+----+-------------+------------+-------------+------------------------+------------------------+---------+------+------+--------------------------------------------------+
|  1 | SIMPLE      | film_actor | index_merge | PRIMARY,idx_fk_film_id | idx_fk_film_id,PRIMARY | 2,2     | NULL |   27 | Using union(idx_fk_film_id,PRIMARY); Using where |
+----+-------------+------------+-------------+------------------------+------------------------+---------+------+------+--------------------------------------------------+

index

explain select * from film order by language_id desc limit 10;

+----+-------------+-------+-------+---------------+--------------------+---------+------+------+-------+
| id | select_type | table | type  | possible_keys | key                | key_len | ref  | rows | Extra |
+----+-------------+-------+-------+---------------+--------------------+---------+------+------+-------+
|  1 | SIMPLE      | film  | index | NULL          | idx_fk_language_id | 1       | NULL |   10 | NULL  |
+----+-------------+-------+-------+---------------+--------------------+---------+------+------+-------+

explain select * from film order by rental_rate < 10;

+----+-------------+-------+------+---------------+------+---------+------+------+----------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra          |
+----+-------------+-------+------+---------------+------+---------+------+------+----------------+
|  1 | SIMPLE      | film  | ALL  | NULL          | NULL | NULL    | NULL | 1000 | Using filesort |
+----+-------------+-------+------+---------------+------+---------+------+------+----------------+

possible_keys

可能会使用到的索引。

explain select * from film_actor where film_id = 3 or actor_id = 3;

+----+-------------+------------+-------------+------------------------+------------------------+---------+------+------+--------------------------------------------------+
| id | select_type | table      | type        | possible_keys          | key                    | key_len | ref  | rows | Extra                                            |
+----+-------------+------------+-------------+------------------------+------------------------+---------+------+------+--------------------------------------------------+
|  1 | SIMPLE      | film_actor | index_merge | PRIMARY,idx_fk_film_id | idx_fk_film_id,PRIMARY | 2,2     | NULL |   27 | Using union(idx_fk_film_id,PRIMARY); Using where |
+----+-------------+------------+-------------+------------------------+------------------------+---------+------+------+--------------------------------------------------+

key

实际使用到的索引。

explain select * from film_actor where film_id = 3 or actor_id = 3;

+----+-------------+------------+-------------+------------------------+------------------------+---------+------+------+--------------------------------------------------+
| id | select_type | table      | type        | possible_keys          | key                    | key_len | ref  | rows | Extra                                            |
+----+-------------+------------+-------------+------------------------+------------------------+---------+------+------+--------------------------------------------------+
|  1 | SIMPLE      | film_actor | index_merge | PRIMARY,idx_fk_film_id | idx_fk_film_id,PRIMARY | 2,2     | NULL |   27 | Using union(idx_fk_film_id,PRIMARY); Using where |
+----+-------------+------------+-------------+------------------------+------------------------+---------+------+------+--------------------------------------------------+

key_len

正常情况下，等于索引字段的长度（单位：字节）。
字符串类型同时需要考虑字符集因素。
若字段允许为 NULL，则长度再 +1。
变长字段类型（VARCHAR），则长度再 +2。
key_len 仅计算利用索引完成数据过滤时的索引长度（不包括用于 GROUP BY/ORDER BY 的索引长度）。

索引字段长度的计算与数据类型存储空间大小计算不同，具体参考：MySQL 数据类型存储空间占用说明

ref

引用什么值来进行索引查找。当引用的值为常量时，则 ref 的值为 const。

同时注意，仅当 type 字段值为 ref、ref_eq、ref_or_null 时，ref 字段才会有值。

explain select * from city where country_id = 103;

+----+-------------+-------+------+-------------------+-------------------+---------+-------+------+-------+
| id | select_type | table | type | possible_keys     | key               | key_len | ref   | rows | Extra |
+----+-------------+-------+------+-------------------+-------------------+---------+-------+------+-------+
|  1 | SIMPLE      | city  | ref  | idx_fk_country_id | idx_fk_country_id | 2       | const |   35 | NULL  |
+----+-------------+-------+------+-------------------+-------------------+---------+-------+------+-------+

rows

预计查询需要扫描的行数（不区分：回表扫描、索引扫描）。

explain select * from film where film_id > 50;

+----+-------------+-------+-------+---------------+---------+---------+------+------+-------------+
| id | select_type | table | type  | possible_keys | key     | key_len | ref  | rows | Extra       |
+----+-------------+-------+-------+---------------+---------+---------+------+------+-------------+
|  1 | SIMPLE      | film  | range | PRIMARY       | PRIMARY | 2       | NULL |  500 | Using where |
+----+-------------+-------+-------+---------------+---------+---------+------+------+-------------+

Extra

补充说明。

以下为常见 Extra 字段值说明：

字段值	说明
Using index	利用覆盖索引。即：无需回表，直接通过索引就查找到需要的数据。
Using index condition	通过索引查找到对应的主键后，再使用主键回表读取行记录。
Using filesort	无法利用现有索引进行排序，需要额外排序（仅当排序缓冲区内存大小不足时，才真正使用文件排序）。
Using MRR	多范围读取优化。预先读取一定范围的索引，然后排序 rowid，之后回表读取行记录，尽可能将随机 I/O 优化成顺序 I/O。
Using temporary	需要用临时表存储结果，通常是因为 group by/order by 字段没有索引导致的。
Using where	回表，根据 where 从句的条件过滤行记录。

更多 Extra 字段值说明，请参考：MySQL explain-extra-information。

MySQL EXPLAIN 输出内容的含义

环境说明

EXPLAIN 基本语法

EXPLAIN 字段说明

id

select_type

table

partitions

type

possible_keys

key

key_len

ref

rows

Extra

参考资料

添加评论

最近发布

MySQL EXPLAIN 输出内容的含义

环境说明

EXPLAIN 基本语法

EXPLAIN 字段说明

id

select_type

table

partitions

type

possible_keys

key

key_len

ref

rows

Extra

参考资料

添加评论

标签云

最近发布