MySQL EXPLAIN命令

置顶 __如风__

已于 2024-05-25 07:38:35 修改

阅读量149

点赞数 1

分类专栏： MySQL 文章标签： mysql 数据库 sql

于 2023-03-31 08:09:30 首次发布

本文链接：https://blog.youkuaiyun.com/weixin_43701894/article/details/129854673

版权

MySQL 专栏收录该内容

11 篇文章

订阅专栏

EXPLAIN命令用于分析SQL语句的执行计划，包括SELECT,DELETE,INSERT,REPLACE和UPDATE等。它显示了查询如何使用索引，以及预计的行数和扫描方式。文章详细介绍了id、select_type、table、partitions、type、possible_keys、key、key_len、rows和Extra等字段的含义，帮助理解查询优化和性能改进。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

MySQL EXPLAIN命令

https://dev.mysql.com/doc/refman/8.0/en/explain-output.html
EXPLAIN获取SQL语句的执行计划：

可以用于SELECT，DELETE，INSERT，REPLACE和UPDATE语句。在MySQL 8.0.19及更高版本中，它还适用于TABLE语句。

EXPLAIN返回的所有字段

id

查询标识符，SELECT的序列号。当使用union时，可能为null。

mysql> explain select *
    -> from salaries
    -> where emp_no = 10001
    -> union
    -> select *
    -> from salaries
    -> where emp_no = 10002;
+----+--------------+------------+------------+------+---------------+---------+---------+-------+------+----------+-----------------+
| id | select_type  | table      | partitions | type | possible_keys | key     | key_len | ref   | rows | filtered | Extra           |
+----+--------------+------------+------------+------+---------------+---------+---------+-------+------+----------+-----------------+
|  1 | PRIMARY      | salaries   | NULL       | ref  | PRIMARY       | PRIMARY | 4       | const |   17 |   100.00 | NULL            |
|  2 | UNION        | salaries   | NULL       | ref  | PRIMARY       | PRIMARY | 4       | const |    6 |   100.00 | NULL            |
| NULL | UNION RESULT | <union1,2> | NULL       | ALL  | NULL          | NULL    | NULL    | NULL  | NULL |     NULL | Using temporary |
+----+--------------+------------+------------+------+---------------+---------+---------+-------+------+----------+-----------------+

select_type

SIMPLE：简单查询，不使用union或子查询等操作
PRIMARY：查询中最外层的SELECT（存在子查询的外层的表操作为PRIMARY）

mysql> explain
    -> select *
    -> from salaries
    -> where emp_no =
    ->       (select distinct emp_no
    ->        from salaries
    ->        where emp_no = '10001');
+----+-------------+----------+------------+------+---------------+---------+---------+-------+------+----------+-------------+
| id | select_type | table    | partitions | type | possible_keys | key     | key_len | ref   | rows | filtered | Extra       |
+----+-------------+----------+------------+------+---------------+---------+---------+-------+------+----------+-------------+
|  1 | PRIMARY     | salaries | NULL       | ref  | PRIMARY       | PRIMARY | 4       | const |   17 |   100.00 | Using where |
|  2 | SUBQUERY    | salaries | NULL       | ref  | PRIMARY       | PRIMARY | 4       | const |   17 |   100.00 | Using index |
+----+-------------+----------+------------+------+---------------+---------+---------+-------+------+

UNION：UNION中，第二个或更之后的SELECT
DEPENDENT UNION：与UNION相同，但是依赖于外部查询
UNION RESULT：UNION合并结果集
SUBQUERY：子查询
DERIVED：被驱动的SELECT子查询（子查询位于FROM子句）
DEPENDENT SUBQUERY：同SUBQUERY，依赖于外部查询
DEPENDENT DERIVED：同DERIVED，依赖于外部查询
UNCACHEABLE SUBQUERY：无法缓存的子查询结果，必须再次从外部查询获取。
UNCACHEABLE UNION：无法缓存的UNION

table

表名，还可以是以下值

<UnionM，N>：该行指的是具有M和N的ID值的行的联合。
<derivedN>：该行指的是具有N的ID值的行的派生表结果。派生表可以导致例如来自FROM子句中的子查询。
<subqueryN>：该行是指具有n的ID值的行的物化子查询的结果。

mysql> explain
    -> select *
    -> from (
    ->          select *
    ->          from dept_emp
    ->          where emp_no = 10001
    ->          union
    ->          select *
    ->          from dept_emp
    ->          where emp_no = 10002) as e;
+----+--------------+------------+------------+------+---------------+---------+---------+-------+------+----------+-----------------+
| id | select_type  | table      | partitions | type | possible_keys | key     | key_len | ref   | rows | filtered | Extra           |
+----+--------------+------------+------------+------+---------------+---------+---------+-------+------+----------+-----------------+
|  1 | PRIMARY      | <derived2> | NULL       | ALL  | NULL          | NULL    | NULL    | NULL  |    4 |   100.00 | NULL            |
|  2 | DERIVED      | dept_emp   | NULL       | ref  | PRIMARY       | PRIMARY | 4       | const |    1 |   100.00 | NULL            |
|  3 | UNION        | dept_emp   | NULL       | ref  | PRIMARY       | PRIMARY | 4       | const |    1 |   100.00 | NULL            |
| NULL | UNION RESULT | <union2,3> | NULL       | ALL  | NULL          | NULL    | NULL    | NULL  | NULL |     NULL | Using temporary |
+----+--------------+------------+------------+------+---------------+---------+---------+-------+------+----------+-----------------+

partitions

查询时匹配到的分区信息，对于非分区表值为NULL，当查询的是分区表时，partitions显示分区表命中的分区情况。

type

查询使用了何种类型，它在 SQL优化中是一个非常重要的指标。
https://dev.mysql.com/doc/refman/8.0/en/explain-output.html#explain-join-types

const：单表操作的时候，查询使用了主键或者唯一索引。
system：当表仅有一行记录时（系统表），这是const的特殊情况。
eq_ref：多表关联查询的时候，主键和唯一索引作为关联条件。
连表时，因为确定只有一行，当匹配到一行后，立即跳出外层循环

-- key_columns表示主键或唯一索引
SELECT * FROM ref_table,other_table
  WHERE ref_table.key_column=other_table.column;

ref
查找条件列使用了索引而且不为主键和唯一索引。虽然使用了索引，但该索引列的值并不唯一，这样即使使用索引查找到了第一条数据，仍然不能停止，要在目标值附近进行小范围扫描。但它的好处是不需要扫全表，因为索引是有序的，即便有重复值，也是在一个非常小的范围内做扫描。多表与单表都适用
ref_or_null：类似 ref，会额外搜索包含NULL值的行。
fulltext：全文索引
index_merge：查询使用了多个索引，索引合并优化，索引合并不适用于全文索引。

mysql> explain
    -> select *
    -> from employees
    -> where birth_date = '1953-09-02'
    ->    or emp_no = 10001;
+----+-------------+-----------+------------+-------------+---------------------+---------------------+---------+------+------+----------+-----------------------------------------------+
| id | select_type | table     | partitions | type        | possible_keys       | key                 | key_len | ref  | rows | filtered | Extra                                         |
+----+-------------+-----------+------------+-------------+---------------------+---------------------+---------+------+------+----------+-----------------------------------------------+
|  1 | SIMPLE      | employees | NULL       | index_merge | PRIMARY,index_birth | index_birth,PRIMARY | 3,4     | NULL |   64 |   100.00 | Using union(index_birth,PRIMARY); Using where |
+----+-------------+-----------+------------+-------------+---------------------+---------------------+---------+------+------+----------+-----------------------------------------------+

unique_subquery：一个索引查找函数，完全替换子查询以提高效率

value IN (SELECT primary_key FROM single_table WHERE some_expr)

index_subquery：类似unique_subquery，但适用于非唯一索引

value IN (SELECT key_column FROM single_table WHERE some_expr)

range：范围索引扫描，当使用=, <>, >, >=, <, <=, IS NULL, <=>, BETWEEN, LIKE, 或 IN()运算符中的任何一个将键列与常量进行比较时，可能为range
index：按索引树扫描所有行，走索引覆盖、按索引顺序排序时为该类型
all：全表扫描，查询没有用到索引，性能最差

possible_keys

此次查询中可能选用的索引。但这个索引并不定一会是最终查询数据时所被用到的索引。

key

此次查询中确切使用到的索引。

key_len

表示查询用到的索引长度（字节数），原则上长度越短越好。

单列索引，那么需要将整个索引长度算进去；多列索引，不是所有列都能用到，需要计算查询中实际用到的列。

注意：key_len只计算where条件中用到的索引长度，而排序和分组即便是用到了索引，也不会计算到key_len中。

a、一般地，key_len 等于索引列类型字节长度，例如int类型为4-bytes，bigint为8-bytes；

b、如果是字符串类型，还需要同时考虑字符集因素，例如：CHAR(30) UTF8则key_len至少是90-bytes；

c、若该列类型定义时允许NULL，其key_len还需要再加 1-bytes；

d、若该列类型为变长类型，例如 VARCHAR（TEXT\BLOB不允许整列创建索引，如果创建部分索引，也被视为动态列类型），其key_len还需要再加 2-bytes;

rows

估算要找到所需的记录，需要读取的行数。评估SQL 性能的一个比较重要的数据，mysql需要扫描的行数，很直观的显示 SQL 性能的好坏，一般情况下 rows 值越小越好。

filtered

存储引擎返回的数据在经过过滤后，剩下满足条件的记录数量的比例。

Extra

这个字段可能的情况比较多，这里只写常见的，具体可参照MySQL官方文档

Using index：覆盖索引，只查索引索引就拿到了数据
Using index condition
索引下推（index condition pushdown，ICP），先使用where条件过滤索引，过滤完索引后找到所有符合索引条件的数据行，随后用 WHERE 子句中的其他条件去过滤这些数据行。
https://dev.mysql.com/doc/refman/8.0/en/index-condition-pushdown-optimization.html
Using Where
查询时未找到可用的索引，或者不能通过索引确定数据行，进而通过where条件过滤获取所需数据，但要注意的是并不是所有带where语句的查询都会显示Using where