EXPLAIN
EXPLAIN
语句仅用于显示查询的执行计划,而不执行查询。EXPLAIN ANALYZE
可执行查询,补充 EXPLAIN
语句。如果 EXPLAIN
的输出与预期结果不匹配,可考虑在查询的每个表上执行 ANALYZE TABLE
。
语句 DESC
和 DESCRIBE
是 EXPLAIN
的别名。EXPLAIN <tableName>
的替代用法记录在 下。
TiDB 支持 EXPLAIN [options] FOR CONNECTION connection_id
,但与 MySQL 的 EXPLAIN FOR
有一些区别,请参见 EXPLAIN FOR CONNECTION
。
ExplainSym:
ExplainableStmt:
EXPLAIN 输出格式
目前 TiDB 的 EXPLAIN
会输出 5 列,分别是:id
,estRows
,task
,access object
, operator info
。执行计划中每个算子都由这 5 列属性来描述,EXPLAIN
结果中每一行描述一个算子。每个属性的具体含义如下:
+-------------------+---------+------+---------------+---------------+
| id | estRows | task | access object | operator info |
+-------------------+---------+------+---------------+---------------+
| Projection_3 | 1.00 | root | | 1->Column#1 |
| └─TableDual_4 | 1.00 | root | | rows:1 |
+-------------------+---------+------+---------------+---------------+
2 rows in set (0.00 sec)
CREATE TABLE t1 (id INT NOT NULL PRIMARY KEY AUTO_INCREMENT, c1 INT NOT NULL);
Query OK, 0 rows affected (0.10 sec)
INSERT INTO t1 (c1) VALUES (1), (2), (3);
Query OK, 3 rows affected (0.02 sec)
Records: 3 Duplicates: 0 Warnings: 0
EXPLAIN SELECT * FROM t1 WHERE id = 1;
DESC SELECT * FROM t1 WHERE id = 1;
+-------------+---------+------+---------------+---------------+
| id | estRows | task | access object | operator info |
+-------------+---------+------+---------------+---------------+
| Point_Get_1 | 1.00 | root | table:t1 | handle:1 |
1 row in set (0.00 sec)
DESCRIBE SELECT * FROM t1 WHERE id = 1;
+-------------+---------+------+---------------+---------------+
| id | estRows | task | access object | operator info |
+-------------+---------+------+---------------+---------------+
| Point_Get_1 | 1.00 | root | table:t1 | handle:1 |
+-------------+---------+------+---------------+---------------+
1 row in set (0.00 sec)
+----------+---------+------+---------------+---------------+
| id | estRows | task | access object | operator info |
+----------+---------+------+---------------+---------------+
| Insert_1 | N/A | root | | N/A |
+----------+---------+------+---------------+---------------+
1 row in set (0.00 sec)
+---------------------------+---------+-----------+---------------+--------------------------------+
| id | estRows | task | access object | operator info |
+---------------------------+---------+-----------+---------------+--------------------------------+
| Update_4 | N/A | root | | N/A |
| └─TableReader_8 | 0.00 | root | | data:Selection_7 |
| └─Selection_7 | 0.00 | cop[tikv] | | eq(test.t1.c1, 3) |
| └─TableFullScan_6 | 3.00 | cop[tikv] | table:t1 | keep order:false, stats:pseudo |
+---------------------------+---------+-----------+---------------+--------------------------------+
4 rows in set (0.00 sec)
EXPLAIN DELETE FROM t1 WHERE c1=3;
+---------------------------+---------+-----------+---------------+--------------------------------+
| id | estRows | task | access object | operator info |
+---------------------------+---------+-----------+---------------+--------------------------------+
| Delete_4 | N/A | root | | N/A |
| └─TableReader_8 | 0.00 | root | | data:Selection_7 |
| └─Selection_7 | 0.00 | cop[tikv] | | eq(test.t1.c1, 3) |
| └─TableFullScan_6 | 3.00 | cop[tikv] | table:t1 | keep order:false, stats:pseudo |
+---------------------------+---------+-----------+---------------+--------------------------------+
4 rows in set (0.01 sec)
如果未指定 FORMAT
,或未指定 FORMAT ="row"
,那么 EXPLAIN
语句将以表格格式输出结果。更多信息,可参阅 。
create table t(a bigint, b bigint);
desc format = "dot" select A.a, B.b from t A join t B on A.a > B.b where A.a < 10;
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|
digraph Projection_8 {
subgraph cluster8{
color=black
label = "root"
"Projection_8" -> "HashJoin_9"
"HashJoin_9" -> "TableReader_13"
"HashJoin_9" -> "Selection_14"
"Selection_14" -> "TableReader_17"
}
subgraph cluster12{
node [style=filled, color=lightgrey]
color=black
label = "cop"
"Selection_12" -> "TableFullScan_11"
}
subgraph cluster16{
node [style=filled, color=lightgrey]
color=black
label = "cop"
"Selection_16" -> "TableFullScan_15"
}
"TableReader_13" -> "Selection_12"
"TableReader_17" -> "Selection_16"
}
|
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
如果你的计算机上安装了 dot
程序(在 graphviz
包中),可使用以下方法生成 PNG 文件:
dot xx.dot -T png -O
The xx.dot
is the result returned by the above statement.
如果你的计算机上未安装 dot
程序,可将结果复制到 本网站 以获取树形图:
MySQL 兼容性
EXPLAIN
的格式和 TiDB 中潜在的执行计划都与 MySQL 有很大不同。- TiDB 不像 MySQL 那样支持
EXPLAIN FORMAT = JSON
。 - TiDB 目前不支持插入语句的
EXPLAIN
。
EXPLAIN FOR CONNECTION
用于获得一个连接中最后执行的查询的执行计划,其输出格式与 EXPLAIN
完全一致。但 TiDB 中的实现与 MySQL 不同,除了输出格式之外,还有以下区别:
- MySQL 返回的是正在执行的查询计划,而 TiDB 返回的是最后执行的查询计划。
- MySQL 的文档中指出,MySQL 要求登录用户与被查询的连接相同,或者拥有 权限,而 TiDB 则要求登录用户与被查询的连接相同,或者拥有
SUPER
权限。