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)