MySQL :: MySQL 8.4 参考手册 :: 15.8.2 EXPLAIN 语句

版本 8.4

8.0 日语

MySQL 8.4 参考手册 / ... / EXPLAIN 语句

15.8.2 EXPLAIN 语句

{EXPLAIN | DESCRIBE | DESC}
    tbl_name [col_name | wild]

{EXPLAIN | DESCRIBE | DESC}
    [explain_type] [INTO variable]
    {[schema_spec] explainable_stmt | FOR CONNECTION connection_id}

{EXPLAIN | DESCRIBE | DESC} ANALYZE [FORMAT = TREE] [schema_spec] select_statement

explain_type: {
    FORMAT = format_name
}

format_name: {
    TRADITIONAL
  | JSON
  | TREE
}

explainable_stmt: {
    SELECT statement
  | TABLE statement
  | DELETE statement
  | INSERT statement
  | REPLACE statement
  | UPDATE statement
}

schema_spec:
FOR {SCHEMA | DATABASE} schema_name

DESCRIBE 和 EXPLAIN 语句是同义词。在实践中，DESCRIBE 关键字更常用于获取有关表结构的信息，而 EXPLAIN 用于获取查询执行计划（即，解释 MySQL 如何执行查询）。

以下讨论使用 DESCRIBE 和 EXPLAIN 关键字，符合这些用法，但 MySQL 解析器将它们视为完全同义词。

获取表结构信息

DESCRIBE 提供有关表中列的信息

mysql> DESCRIBE City;
+------------+----------+------+-----+---------+----------------+
| Field      | Type     | Null | Key | Default | Extra          |
+------------+----------+------+-----+---------+----------------+
| Id         | int(11)  | NO   | PRI | NULL    | auto_increment |
| Name       | char(35) | NO   |     |         |                |
| Country    | char(3)  | NO   | UNI |         |                |
| District   | char(20) | YES  | MUL |         |                |
| Population | int(11)  | NO   |     | 0       |                |
+------------+----------+------+-----+---------+----------------+

DESCRIBE 是 SHOW COLUMNS 的快捷方式。这些语句还显示视图的信息。SHOW COLUMNS 的描述提供了有关输出列的更多信息。参见第 15.7.7.6 节，“SHOW COLUMNS 语句”。

默认情况下，DESCRIBE 显示有关表中所有列的信息。如果给出 col_name，则它是表中的一列的名称。在这种情况下，该语句只显示有关命名列的信息。如果给出 wild，则它是一个模式字符串。它可以包含 SQL % 和 _ 通配符。在这种情况下，该语句只显示与字符串匹配的名称的列的输出。除非字符串包含空格或其他特殊字符，否则不需要将字符串括在引号中。

DESCRIBE 语句是为了与 Oracle 兼容而提供的。

SHOW CREATE TABLE、SHOW TABLE STATUS 和 SHOW INDEX 语句也提供了有关表的信息。参见第 15.7.7 节，“SHOW 语句”。

explain_format 系统变量对 EXPLAIN 的输出没有影响，当它用于获取有关表列的信息时。

获取执行计划信息

EXPLAIN 语句提供有关 MySQL 如何执行语句的信息

EXPLAIN 与 SELECT、DELETE、INSERT、REPLACE、UPDATE 和 TABLE 语句一起使用。
当 EXPLAIN 与可解释语句一起使用时，MySQL 显示来自优化器有关语句执行计划的信息。也就是说，MySQL 解释它将如何处理语句，包括有关如何连接表以及连接顺序的信息。有关使用 EXPLAIN 获取执行计划信息的信息，请参见第 10.8.2 节，“EXPLAIN 输出格式”。
当 EXPLAIN 与 FOR CONNECTION connection_id 而不是可解释语句一起使用时，它将显示在命名连接中执行的语句的执行计划。参见第 10.8.4 节，“获取命名连接的执行计划信息”。
对于可解释语句，EXPLAIN 生成额外的执行计划信息，可以使用 SHOW WARNINGS 显示。参见第 10.8.3 节，“扩展 EXPLAIN 输出格式”。
EXPLAIN 对于检查涉及分区表的查询很有用。参见第 26.3.5 节，“获取有关分区的信息”。

FORMAT 选项可用于选择输出格式。 TRADITIONAL 以表格格式呈现输出。如果不存在 FORMAT 选项，则为默认值。 JSON 格式以 JSON 格式显示信息。 TREE 提供树状输出，并提供比 TRADITIONAL 格式更精确的查询处理描述；它是唯一显示散列连接使用情况的格式（参见第 10.2.1.4 节，“散列连接优化”），并且始终用于 EXPLAIN ANALYZE。

在 MySQL 8.4 中，EXPLAIN 使用的默认输出格式（即，当它没有 FORMAT 选项时）由 explain_format 系统变量的值决定。此变量的精确效果将在本节后面描述。

MySQL 8.4 支持 EXPLAIN FORMAT=JSON 的附加 INTO 选项，它允许将 JSON 格式的输出保存到用户变量中，如下所示

mysql> EXPLAIN FORMAT=JSON INTO @myselect 
    ->     SELECT name FROM a WHERE id = 2;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT @myselect\G
*************************** 1. row ***************************
@myex: {
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "1.00"
    },
    "table": {
      "table_name": "a",
      "access_type": "const",
      "possible_keys": [
        "PRIMARY"
      ],
      "key": "PRIMARY",
      "used_key_parts": [
        "id"
      ],
      "key_length": "4",
      "ref": [
        "const"
      ],
      "rows_examined_per_scan": 1,
      "rows_produced_per_join": 1,
      "filtered": "100.00",
      "cost_info": {
        "read_cost": "0.00",
        "eval_cost": "0.10",
        "prefix_cost": "0.00",
        "data_read_per_join": "408"
      },
      "used_columns": [
        "id",
        "name"
      ]
    }
  }
}
1 row in set (0.00 sec)

这适用于任何可解释语句（SELECT、TABLE、INSERT、UPDATE、REPLACE 或 DELETE）。这里显示了使用 UPDATE 和 DELETE 语句的示例

mysql> EXPLAIN FORMAT=JSON INTO @myupdate 
    ->   UPDATE a SET name2 = "garcia" WHERE id = 3;              
Query OK, 0 rows affected (0.00 sec)

mysql> EXPLAIN FORMAT=JSON INTO @mydelete 
    ->     DELETE FROM a WHERE name1 LIKE '%e%';
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT @myupdate, @mydelete\G
*************************** 1. row ***************************
@myupdate: {
  "query_block": {
    "select_id": 1,
    "table": {
      "update": true,
      "table_name": "a",
      "access_type": "range",
      "possible_keys": [
        "PRIMARY"
      ],
      "key": "PRIMARY",
      "used_key_parts": [
        "id"
      ],
      "key_length": "4",
      "ref": [
        "const"
      ],
      "rows_examined_per_scan": 1,
      "filtered": "100.00",
      "attached_condition": "(`db`.`a`.`id` = 3)"
    }
  }
}
@mydelete: {
  "query_block": {
    "select_id": 1,
    "table": {
      "delete": true,
      "table_name": "a",
      "access_type": "ALL",
      "rows_examined_per_scan": 2,
      "filtered": "100.00",
      "attached_condition": "(`db`.`a`.`name1` like '%e%')"
    }
  }
}
1 row in set (0.00 sec)

您可以使用 MySQL JSON 函数与该值进行操作，就像您对任何其他 JSON 值一样，例如以下使用 JSON_EXTRACT() 的示例

mysql> SELECT JSON_EXTRACT(@myselect, "$.query_block.table.key");
+----------------------------------------------------+
| JSON_EXTRACT(@myselect, "$.query_block.table.key") |
+----------------------------------------------------+
| "PRIMARY"                                          |
+----------------------------------------------------+
1 row in set (0.01 sec)

mysql> SELECT JSON_EXTRACT(@myupdate, "$.query_block.table.access_type") AS U_acc,
    ->        JSON_EXTRACT(@mydelete, "$.query_block.table.access_type") AS D_acc;
+---------+-------+
| U_acc   | D_acc |
+---------+-------+
| "range" | "ALL" |
+---------+-------+
1 row in set (0.00 sec)

另请参见第 14.17 节，“JSON 函数”。

尝试在没有明确包含 FORMAT=JSON 的情况下使用 INTO 子句会导致 EXPLAIN 被拒绝，并出现 ER_EXPLAIN_INTO_IMPLICIT_FORMAT_NOT_SUPPORTED 错误。无论 explain_format 系统变量的当前值是什么，都是如此。

INTO 子句不支持 EXPLAIN ANALYZE；它也不支持 FOR CONNECTION。

重要

如果由于任何原因，要分析的语句被拒绝，则不会更新用户变量。

MySQL 8.4 支持 FOR SCHEMA 子句，它会导致 EXPLAIN 的行为就像要分析的语句已在命名数据库中执行一样；FOR DATABASE 支持作为同义词。这里显示了一个简单的使用示例

mysql> USE b;
Database changed
mysql> CREATE SCHEMA s1;
Query OK, 1 row affected (0.01 sec)

mysql> CREATE SCHEMA s2;
Query OK, 1 row affected (0.01 sec)

mysql> USE s1;
Database changed
mysql> CREATE TABLE t (c1 INT NOT NULL AUTO_INCREMENT PRIMARY KEY, c2 INT NOT NULL);
Query OK, 0 rows affected (0.04 sec)

mysql> USE s2;
Database changed
mysql> CREATE TABLE t (c1 INT NOT NULL AUTO_INCREMENT PRIMARY KEY, c2 INT NOT NULL, KEY i1 (c2));
Query OK, 0 rows affected (0.04 sec)

mysql> USE b;
Database changed
mysql> EXPLAIN FORMAT=TREE FOR SCHEMA s1 SELECT * FROM t WHERE c2 > 50\G
*************************** 1. row ***************************
EXPLAIN: -> Filter: (t.c2 > 50)  (cost=0.35 rows=1)
    -> Table scan on t  (cost=0.35 rows=1)

1 row in set (0.00 sec)

mysql> EXPLAIN FORMAT=TREE FOR SCHEMA s2 SELECT * FROM t WHERE c2 > 50\G
*************************** 1. row ***************************
EXPLAIN: -> Filter: (t.c2 > 50)  (cost=0.35 rows=1)
    -> Covering index scan on t using i1  (cost=0.35 rows=1)

1 row in set (0.00 sec)

如果数据库不存在，则语句将被拒绝，并出现 ER_BAD_DB_ERROR 错误。如果用户没有运行语句所需的权限，则语句将被拒绝，并出现 ER_DBACCESS_DENIED_ERROR 错误。

FOR SCHEMA 不兼容 FOR CONNECTION。

EXPLAIN 需要与执行解释语句所需的相同权限。此外，EXPLAIN 还需要对任何解释的视图使用 SHOW VIEW 权限。如果指定的连接属于不同的用户，则 EXPLAIN ... FOR CONNECTION 还需要 PROCESS 权限。

explain_format 系统变量决定 EXPLAIN 用于显示查询执行计划时的输出格式。此变量可以采用与 FORMAT 选项一起使用的任何值，以及 DEFAULT 作为 TRADITIONAL 的同义词。以下示例使用来自 world 数据库的 country 表，该数据库可以从 MySQL：其他下载获取

mysql> USE world; # Make world the current database
Database changed

检查 explain_format 的值，我们看到它具有默认值，并且 EXPLAIN（没有 FORMAT 选项）因此使用传统的表格输出

mysql> SELECT @@explain_format;
+------------------+
| @@explain_format |
+------------------+
| TRADITIONAL      |
+------------------+
1 row in set (0.00 sec)

mysql> EXPLAIN SELECT Name FROM country WHERE Code Like 'A%';
+----+-------------+---------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
| id | select_type | table   | partitions | type  | possible_keys | key     | key_len | ref  | rows | filtered | Extra       |
+----+-------------+---------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | country | NULL       | range | PRIMARY       | PRIMARY | 12      | NULL |   17 |   100.00 | Using where |
+----+-------------+---------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

如果我们将 explain_format 的值设置为 TREE，然后重新运行相同的 EXPLAIN 语句，则输出将使用树状格式

mysql> SET @@explain_format=TREE;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT @@explain_format;
+------------------+
| @@explain_format |
+------------------+
| TREE             |
+------------------+
1 row in set (0.00 sec)

mysql> EXPLAIN SELECT Name FROM country WHERE Code LIKE 'A%';
+--------------------------------------------------------------------------------------------------------------+
| EXPLAIN                                                                                                      |
+--------------------------------------------------------------------------------------------------------------+
| -> Filter: (country.`Code` like 'A%')  (cost=3.67 rows=17)
    -> Index range scan on country using PRIMARY over ('A' <= Code <= 'A????????')  (cost=3.67 rows=17)  |
+--------------------------------------------------------------------------------------------------------------+
1 row in set, 1 warning (0.00 sec)

如前所述，FORMAT 选项会覆盖此设置。使用 FORMAT=JSON 而不是 FORMAT=TREE 执行相同的 EXPLAIN 语句表明情况确实如此

mysql> EXPLAIN FORMAT=JSON SELECT Name FROM country WHERE Code LIKE 'A%';
+------------------------------------------------------------------------------+
| EXPLAIN                                                                      |
+------------------------------------------------------------------------------+
| {
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "3.67"
    },
    "table": {
      "table_name": "country",
      "access_type": "range",
      "possible_keys": [
        "PRIMARY"
      ],
      "key": "PRIMARY",
      "used_key_parts": [
        "Code"
      ],
      "key_length": "12",
      "rows_examined_per_scan": 17,
      "rows_produced_per_join": 17,
      "filtered": "100.00",
      "cost_info": {
        "read_cost": "1.97",
        "eval_cost": "1.70",
        "prefix_cost": "3.67",
        "data_read_per_join": "16K"
      },
      "used_columns": [
        "Code",
        "Name"
      ],
      "attached_condition": "(`world`.`country`.`Code` like 'A%')"
    }
  }
}                                                                              |
+------------------------------------------------------------------------------+
1 row in set, 1 warning (0.00 sec)

要将 EXPLAIN 的默认输出返回到表格格式，请将 explain_format 设置为 TRADITIONAL。或者，您可以将其设置为 DEFAULT，这具有相同的效果，如下所示

mysql> SET @@explain_format=DEFAULT;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT @@explain_format;
+------------------+
| @@explain_format |
+------------------+
| TRADITIONAL      |
+------------------+
1 row in set (0.00 sec)

MySQL 8.4 支持 JSON 输出格式的两个版本。版本 1 是在 MySQL 8.2 及更早版本中始终使用的线性格式；这在 MySQL 8.4 中仍然是默认值，并且在本文档中已经显示的示例中使用。JSON 输出格式的版本 2 基于访问路径，旨在与未来版本的 MySQL Optimizer 保持兼容。您可以通过将 explain_json_format_version 服务器系统变量的值设置为 2 来切换到版本 2 格式，如下所示，对于与前面示例中使用的相同 EXPLAIN 语句

mysql> SELECT @@explain_json_format_version;
+-------------------------------+
| @@explain_json_format_version |
+-------------------------------+
|                             1 |
+-------------------------------+
1 row in set (0.00 sec)

mysql> SET @@explain_json_format_version = 2;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT @@explain_json_format_version;
+-------------------------------+
| @@explain_json_format_version |
+-------------------------------+
|                             2 |
+-------------------------------+
1 row in set (0.00 sec)

mysql> EXPLAIN FORMAT=JSON SELECT Name FROM country WHERE Code LIKE 'A%';
+------------------------------------------------------------------------------+
| EXPLAIN                                                                      |
+------------------------------------------------------------------------------+
| {
  "query": "/* select#1 */ select `world`.`country`.`Name` AS `Name` from `world`.`country` where (`world`.`country`.`Code` like 'A%')",
  "inputs": [
    {
      "ranges": [
        "('A' &lt;= Code &lt;= 'A????????')"
      ],
      "covering": false,
      "operation": "Index range scan on country using PRIMARY over ('A' &lt;= Code &lt;= 'A????????')",
      "index_name": "PRIMARY",
      "table_name": "country",
      "access_type": "index",
      "estimated_rows": 17.0,
      "index_access_type": "index_range_scan",
      "estimated_total_cost": 3.668778400708174
    }
  ],
  "condition": "(country.`Code` like 'A%')",
  "operation": "Filter: (country.`Code` like 'A%')",
  "access_type": "filter",
  "estimated_rows": 17.0,
  "estimated_total_cost": 3.668778400708174
}                                                                              |
+------------------------------------------------------------------------------+
1 row in set, 1 warning (0.00 sec)

在使用版本 2 格式后，您可以通过将 explain_json_format_version 恢复到 1（默认值）来使所有后续 EXPLAIN FORMAT=JSON 语句的 JSON 输出恢复到版本 1 格式。

explain_json_format_version 的值决定了所有使用它的 EXPLAIN 语句使用的 JSON 输出格式的版本，无论 JSON 格式是由于给定的 EXPLAIN 语句包含显式 FORMAT=JSON 选项而使用，还是由于 explain_format 系统变量被设置为 JSON 而自动使用。

借助 EXPLAIN，您可以看到您应该在哪些位置为表添加索引，以便通过使用索引查找行来使语句更快地执行。您还可以使用 EXPLAIN 检查优化器是否以最佳顺序连接表。为了提示优化器使用与 SELECT 语句中表名顺序相对应的连接顺序，请在语句开头使用 SELECT STRAIGHT_JOIN，而不是仅使用 SELECT。（参见第 15.2.13 节，“SELECT 语句”。）

优化器跟踪有时可能会提供补充 EXPLAIN 的信息。但是，优化器跟踪格式和内容可能会在不同版本之间发生变化。有关详细信息，请参见 MySQL 内部：跟踪优化器。

如果您遇到索引没有被使用的情况，而您认为它们应该被使用，请运行 ANALYZE TABLE 来更新表统计信息，例如键的基数，这些信息可能会影响优化器做出的选择。参见第 15.7.3.1 节，“ANALYZE TABLE 语句”。

注意

MySQL Workbench 具有“可视化解释”功能，可以提供对 EXPLAIN 输出的可视化表示。参见教程：使用 Explain 提高查询性能。

使用 EXPLAIN ANALYZE 获取信息

EXPLAIN ANALYZE 运行一个语句并生成 EXPLAIN 输出，以及关于优化器预期如何与实际执行匹配的计时和额外的基于迭代器的信息。对于每个迭代器，提供以下信息：

估计执行成本
(一些迭代器没有被成本模型考虑在内，因此未包含在估计中。)
估计返回的行数
返回第一行的时间
执行此迭代器（包括子迭代器，但不包括父迭代器）所花费的时间（毫秒）。
(当存在多个循环时，此数字显示每次循环的平均时间。)
迭代器返回的行数
循环次数

查询执行信息使用 TREE 输出格式显示，其中节点代表迭代器。 EXPLAIN ANALYZE 始终使用 TREE 输出格式。这可以使用 FORMAT=TREE 显式指定，但 TREE 以外的格式不受支持。

EXPLAIN ANALYZE 可用于 SELECT 语句、多表 UPDATE 和 DELETE 语句以及 TABLE 语句。

您可以使用 KILL QUERY 或 CTRL-C 终止此语句。

EXPLAIN ANALYZE 不能与 FOR CONNECTION 一起使用。

示例输出

mysql> EXPLAIN ANALYZE SELECT * FROM t1 JOIN t2 ON (t1.c1 = t2.c2)\G
*************************** 1. row ***************************
EXPLAIN: -> Inner hash join (t2.c2 = t1.c1)  (cost=4.70 rows=6)
(actual time=0.032..0.035 rows=6 loops=1)
    -> Table scan on t2  (cost=0.06 rows=6)
(actual time=0.003..0.005 rows=6 loops=1)
    -> Hash
        -> Table scan on t1  (cost=0.85 rows=6)
(actual time=0.018..0.022 rows=6 loops=1)

mysql> EXPLAIN ANALYZE SELECT * FROM t3 WHERE i > 8\G
*************************** 1. row ***************************
EXPLAIN: -> Filter: (t3.i > 8)  (cost=1.75 rows=5)
(actual time=0.019..0.021 rows=6 loops=1)
    -> Table scan on t3  (cost=1.75 rows=15)
(actual time=0.017..0.019 rows=15 loops=1)

mysql> EXPLAIN ANALYZE SELECT * FROM t3 WHERE pk > 17\G
*************************** 1. row ***************************
EXPLAIN: -> Filter: (t3.pk > 17)  (cost=1.26 rows=5)
(actual time=0.013..0.016 rows=5 loops=1)
    -> Index range scan on t3 using PRIMARY  (cost=1.26 rows=5)
(actual time=0.012..0.014 rows=5 loops=1)

示例输出中使用的表是通过此处显示的语句创建的。

CREATE TABLE t1 (
    c1 INTEGER DEFAULT NULL,
    c2 INTEGER DEFAULT NULL
);

CREATE TABLE t2 (
    c1 INTEGER DEFAULT NULL,
    c2 INTEGER DEFAULT NULL
);

CREATE TABLE t3 (
    pk INTEGER NOT NULL PRIMARY KEY,
    i INTEGER DEFAULT NULL
);

此语句输出中显示的 实际时间 值以毫秒为单位。

explain_format 对 EXPLAIN ANALYZE 具有以下影响：

如果此变量的值为 TRADITIONAL 或 TREE（或同义词 DEFAULT），则 EXPLAIN ANALYZE 使用 TREE 格式。这确保了此语句继续默认使用 TREE 格式，就像在引入 explain_format 之前一样。
如果 explain_format 的值为 JSON，则 EXPLAIN ANALYZE 会返回错误，除非 FORMAT=TREE 作为语句的一部分指定。这是因为 EXPLAIN ANALYZE 只支持 TREE 输出格式。

我们在此演示第二点中描述的行为，重新使用上一示例中的最后一个 EXPLAIN ANALYZE 语句。

mysql> SET @@explain_format=JSON;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT @@explain_format;
+------------------+
| @@explain_format |
+------------------+
| JSON             |
+------------------+
1 row in set (0.00 sec)

mysql> EXPLAIN ANALYZE SELECT * FROM t3 WHERE pk > 17\G
ERROR 1235 (42000): This version of MySQL doesn't yet support 'EXPLAIN ANALYZE with JSON format'

mysql> EXPLAIN ANALYZE FORMAT=TRADITIONAL SELECT * FROM t3 WHERE pk > 17\G
ERROR 1235 (42000): This version of MySQL doesn't yet support 'EXPLAIN ANALYZE with TRADITIONAL format'

mysql> EXPLAIN ANALYZE FORMAT=TREE SELECT * FROM t3 WHERE pk > 17\G
*************************** 1. row ***************************
EXPLAIN: -> Filter: (t3.pk > 17)  (cost=1.26 rows=5)
(actual time=0.013..0.016 rows=5 loops=1)
    -> Index range scan on t3 using PRIMARY  (cost=1.26 rows=5)
(actual time=0.012..0.014 rows=5 loops=1)

在 EXPLAIN ANALYZE 中使用 FORMAT=TRADITIONAL 或 FORMAT=JSON 始终会导致错误，无论 explain_format 的值是什么。

在 MySQL 8.4 中，EXPLAIN ANALYZE 和 EXPLAIN FORMAT=TREE 输出中的数字将根据以下规则进行格式化：

范围为 0.001-999999.5 的数字将以十进制数形式打印。
小于 1000 的十进制数具有三位有效数字；其余的具有四位、五位或六位有效数字。
超出 0.001-999999.5 范围的数字将以工程格式打印。此类值的示例包括 1.23e+9 和 934e-6。
不打印尾随零。例如，我们打印 2.3 而不是 2.30，以及 1.2e+6 而不是 1.20e+6。
小于 1e-12 的数字将打印为 0。

PREV HOME UP NEXT