DESCRIBE 提供表列信息：DESCRIBE provides information about the columns in a table:

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 is a shortcut for SHOW COLUMNS. These statements also display information for views. The description for SHOW COLUMNS provides more information about the output columns. See Section 15.7.7.6, “SHOW COLUMNS Statement”.

By default, DESCRIBE displays information about all columns in the table. col_name, if given, is the name of a column in the table. In this case, the statement displays information only for the named column. wild, if given, is a pattern string. It can contain the SQL %以及_ wildcard characters. In this case, the statement displays output only for the columns with names matching the string. There is no need to enclose the string within quotation marks unless it contains spaces or other special characters.By default, DESCRIBE displays information about all columns in the table. col_name, if given, is the name of a column in the table. In this case, the statement displays information only for the named column. wild, if given, is a pattern string. It can contain the SQL % and _ wildcard characters. In this case, the statement displays output only for the columns with names matching the string. There is no need to enclose the string within quotation marks unless it contains spaces or other special characters.

为兼容 Oracle，提供了DESCRIBE 语句。The DESCRIBE statement is provided for compatibility with Oracle.

SHOW CREATE TABLE“， SHOW TABLE STATUS， SHOW INDEX 语句也提供表的信息。参见 第 15.7.7 节，“SHOW 语句”。The SHOW CREATE TABLE, SHOW TABLE STATUS, and SHOW INDEX statements also provide information about tables. See Section 15.7.7, “SHOW Statements”.

explain_format系统变量在用于获取表列信息时，对 EXPLAIN。The explain_format system variable has no effect on the output of EXPLAIN when used to obtain information about table columns.

EXPLAIN 语句提供有关 MySQL 如何执行语句的信息：The EXPLAIN statement provides information about how MySQL executes statements:

EXPLAIN 需要执行该解释语句所需的权限。此外，EXPLAIN 还需要 SHOW VIEW 权限。EXPLAIN ... FOR CONNECTION 还需要 PROCESS 权限，如果指定的连接属于不同用户。EXPLAIN requires the same privileges required to execute the explained statement. Additionally, EXPLAIN also requires the SHOW VIEW privilege for any explained view. EXPLAIN ... FOR CONNECTION also requires the PROCESS privilege if the specified connection belongs to a different user.

explain_format 系统变量决定了 EXPLAIN 输出的格式，用于显示查询执行计划时。该变量可以取 FORMAT，并添加 DEFAULT，作为 TRADITIONAL词。以下示例使用了来自countryThe explain_format system variable determines the format of the output from EXPLAIN when used to display a query execution plan. This variable can take any of the values used with the FORMAT option, with the addition of DEFAULT as a synonym for TRADITIONAL. The following example uses the country table from the world database which can be obtained from MySQL: Other Downloads:

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

检查explain_format，我们看到它为默认值，且EXPLAIN（没有 FORMAToption）因此使用传统的表格输出：Checking the value of explain_format, we see that it has the default value, and that EXPLAIN (with no FORMAT option) therefore uses the traditional tabular output:

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_formatTREE然后重复运行相同的EXPLAIN语句，输出采用树状格式：If we set the value of explain_format to TREE, then rerun the same EXPLAIN statement, the output uses the tree-like format:

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选项覆盖了该设置。使用EXPLAINAs stated previously, the FORMAT option overrides this setting. Executing the same EXPLAIN statement using FORMAT=JSON instead of FORMAT=TREE shows that this is the case:

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_formatTRADITIONAL或者，您也可以将其设置为DEFAULT效果相同，如此处所示：To return the default output of EXPLAIN to the tabular format, set explain_format to TRADITIONAL. Alternatively, you can set it to DEFAULT, which has the same effect, as shown here:

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 优化器的未来版本兼容。您可以通过将服务器系统变量的值设置为explain_json_format_version切换到版本 2 格式，如下面针对前一个示例中使用的相同2语句所示：EXPLAIN使用版本 2 格式后，您可以通过将MySQL 8.4 supports two versions of the JSON output format. Version 1 is the linear format always used in MySQL 8.2 and earlier; this remains the default in MySQL 8.4, and is used in the examples already shown in this section. Version 2 of the JSON output format is based on access paths, and is intended to provide compatibility with future versions of the MySQL Optimizer. You can switch to the Version 2 format by setting the value of the explain_json_format_version server system variable to 2, as shown here for the same EXPLAIN statement used in the previous example:

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' <= Code <= 'A????????')"
      ],
      "covering": false,
      "operation": "Index range scan on country using PRIMARY over ('A' <= Code < '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
}                                                                              |
+------------------------------------------------------------------------------+

重新设置为EXPLAIN FORMAT=JSON（默认值）来使所有后续explain_json_format_version语句的 JSON 输出返回到版本 1 格式。1通过After using the Version 2 format, you can cause the JSON output from all subsequent EXPLAIN FORMAT=JSON statements to revert to the Version 1 format by setting explain_json_format_version back to 1 (the default).

explain_json_format_version决定了所有使用 JSON 输出格式的 EXPLAIN 语句所采用的版本，是因为给定的 EXPLAIN 语句包含显式的 FORMAT=JSON 选项，还是因为 explain_format系统变量设置为 JSON 格式。The value of explain_json_format_version determines the version of the JSON output format employed by all EXPLAIN statements which use it, whether the JSON format is used because a given EXPLAIN statement includes an explicit FORMAT=JSON option, or because the JSON format is used automatically due to the explain_format system variable being set to JSON.

借助 EXPLAIN，您可以查看应在哪些表上添加索引，以便语句通过使用索引查找行时执行得更快。您还可以使用 EXPLAIN 来检查优化器是否以最佳顺序连接表。要向优化器提示使用与 SELECT 语句中表的命名顺序相对应的连接顺序，请在语句开头使用 SELECT STRAIGHT_JOIN，而不仅仅是 SELECT。（参见 第 15.2.13 节，“SELECT 语句”。）With the help of EXPLAIN, you can see where you should add indexes to tables so that the statement executes faster by using indexes to find rows. You can also use EXPLAIN to check whether the optimizer joins the tables in an optimal order. To give a hint to the optimizer to use a join order corresponding to the order in which the tables are named in a SELECT statement, begin the statement with SELECT STRAIGHT_JOIN rather than just SELECT. (See Section 15.2.13, “SELECT Statement”.)

优化器跟踪有时可能提供与 EXPLAIN 补充的信息。然而，优化器跟踪的格式和内容可能会随着版本的不同而发生变化。有关详细信息，请参见 第 10.15 节，“跟踪优化器”。The optimizer trace may sometimes provide information complementary to that of EXPLAIN. However, the optimizer trace format and content are subject to change between versions. For details, see Section 10.15, “Tracing the Optimizer”.

如果您遇到索引未被使用的问题，而您认为它们应该被使用，请运行 ANALYZE TABLE 来更新表统计信息，例如键的基数，这些信息可能会影响优化器的选择。请参见 第 15.7.3.1 节，“ANALYZE TABLE 语句”。If you have a problem with indexes not being used when you believe that they should be, run ANALYZE TABLE to update table statistics, such as cardinality of keys, that can affect the choices the optimizer makes. See Section 15.7.3.1, “ANALYZE TABLE Statement”.

EXPLAIN ANALYZE 执行一个语句，并生成 EXPLAIN 输出，同时提供有关优化器预期与实际执行如何匹配的时间和其他基于迭代器的信息。对于每个迭代器，提供以下信息：EXPLAIN ANALYZE runs a statement and produces EXPLAIN output along with timing and additional, iterator-based, information about how the optimizer's expectations matched the actual execution. For each iterator, the following information is provided:

查询执行信息以 TREE 输出格式显示，其中节点表示迭代器。EXPLAIN ANALYZE 总是使用 TREE 输出格式。可以通过 FORMAT=TREE 显式指定；不支持 TREE 以外的格式。The query execution information is displayed using the TREE output format, in which nodes represent iterators. EXPLAIN ANALYZE always uses the TREE output format. This can optionally be specified explicitly using FORMAT=TREE; formats other than TREE remain unsupported.

EXPLAIN ANALYZE 可用于 SELECT 语句、多表 UPDATE 和 DELETE 语句，以及 TABLE 语句。EXPLAIN ANALYZE can be used with SELECT statements, multi-table UPDATE and DELETE statements, and TABLE statements.

您可以使用 KILL QUERY 或 CTRL-C 终止此语句。You can terminate this statement using KILL QUERY or CTRL-C.

EXPLAIN ANALYZE 不能与 FOR CONNECTION 一起使用。EXPLAIN ANALYZE cannot be used with FOR CONNECTION.

示例输出：Example output:

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=3.5 rows=5) 
(actual time=0.121..0.131 rows=1 loops=1) 
    -> Table scan on t2  (cost=0.07 rows=5) 
(actual time=0.0126..0.0221 rows=5 loops=1) 
    -> Hash
        -> Table scan on t1  (cost=0.75 rows=5) 
(actual time=0.0372..0.0534 rows=5 loops=1)

mysql> EXPLAIN ANALYZE SELECT * FROM t3 WHERE i > 8\G
*************************** 1. row ***************************
EXPLAIN: -> Filter: (t3.i > 8)  (cost=0.75 rows=1.67) 
(actual time=0.0168..0.0182 rows=1 loops=1)
    -> Table scan on t3  (cost=0.75 rows=5) 
(actual time=0.015..0.0167 rows=5 loops=1)

mysql> EXPLAIN ANALYZE FORMAT=TREE SELECT * FROM t3 WHERE pk > 17\G
*************************** 1. row ***************************
EXPLAIN: -> Filter: (t3.pk > 17)  (cost=0.91 rows=2) 
(actual time=0.0334..0.042 rows=2 loops=1)
    -> Index range scan on t3 using PRIMARY over (17 < pk)  
(cost=0.91 rows=2) (actual time=0.0306..0.0384 rows=2 loops=1)

示例输出中使用的表是通过此处显示的语句创建的：The tables used in the example output were created by the statements shown here:

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
);

此语句输出中显示的 actual time 的值以毫秒为单位。Values shown for actual time in the output of this statement are expressed in milliseconds.

explain_format 对 EXPLAIN ANALYZE 有以下影响：explain_format has the following effects on EXPLAIN ANALYZE:

我们在这里展示了第二点描述的行为，重复使用了前一个示例中的最后一个 EXPLAIN ANALYZE 语句：We illustrate the behavior described in the second point here, re-using the last EXPLAIN ANALYZE statement from the previous example:

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)

使用 FORMAT=TRADITIONAL 或 FORMAT=JSON 搭配 EXPLAIN ANALYZE 总是会触发错误，无论 explain_format。Using FORMAT=TRADITIONAL or FORMAT=JSON with EXPLAIN ANALYZE always raises an error, regardless of the value of explain_format.

在 MySQL 8.4 中，输出EXPLAIN ANALYZE和EXPLAIN FORMAT=TREE按照以下规则进行格式化：In MySQL 8.4, numbers in the output of EXPLAIN ANALYZE and EXPLAIN FORMAT=TREE are formatted according to the following rules: