MySQL Explain关键字：语法和输出格式

MySQL Explain关键字：语法和输出格式

语法结构如下：

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

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

{EXPLAIN | DESCRIBE | DESC} ANALYZE [FORMAT = TREE] 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
}

EXPLAIN | DESCRIBE | DESC 这三个关键字是同义词，通常使用DESCRIBE关键字获取表结构信息，而EXPLAIN用于获取查询执行计划。

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

当EXPLAIN与可解释语句一起使用时，MySQL 显示来自优化器的有关语句执行计划的信息。也就是说，MySQL 解释了它将如何处理该语句，包括有关表如何连接以及连接顺序的信息。

EXPLAIN 的输出格式

FORMAT选项可用于选择输出格式。默认的是TRADITIONAL,以表格格式呈现输出。也可以显式的设置为JSON格式显示信息。在 MySQL 8.0.16 及更高版本中， 还可以设置为TREE提供树状输出，比 TRADITIONAL格式更精确地描述查询处理；使用EXPLAIN ANALYZE时，只能以TREE格式显示。

从 MySQL 8.0.32 开始， EXPLAIN默认输出格式由系统变量explain_format的值决定 。

以下是使用TRADITIONAL时，输出为表格格式的内容。

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)

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

mysql> EXPLAIN FORMAT=TREE 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)

以下是JSON格式的输出

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)

参考链接

https://dev.mysql.com/doc/refman/8.0/en/explain.html