EXPLAIN

EXPLAIN 语句仅用于显示查询的执行计划,而不执行查询。该语句为 EXPLAIN ANALYZE 语句的补充,后者会执行查询。如果 EXPLAIN 的输出与预期结果不匹配,可考虑在查询的每个表上执行 ANALYZE TABLE,以确保表统计信息是最新的。

语句 DESCDESCRIBEEXPLAIN 的别名。EXPLAIN <tableName> 的替代用法记录在 SHOW [FULL] COLUMNS FROM 下。

TiDB 支持 EXPLAIN [options] FOR CONNECTION connection_id,但与 MySQL 的 EXPLAIN FOR 有一些区别,请参见 EXPLAIN FOR CONNECTION

语法图

ExplainSym
EXPLAINDESCRIBEDESC
ExplainStmt
ExplainSymTableNameColumnNameANALYZEExplainableStmtFORCONNECTIONNUMFORMAT=stringLitExplainFormatTypeFORCONNECTIONNUMExplainableStmt
ExplainableStmt
SelectStmtDeleteFromStmtUpdateStmtInsertIntoStmtReplaceIntoStmtUnionStmt

EXPLAIN 输出格式

目前 TiDB 的 EXPLAIN 会输出 5 列,分别是:idestRowstaskaccess objectoperator info。执行计划中每个算子都由这 5 列属性来描述,EXPLAIN 结果中每一行描述一个算子。每个属性的具体含义如下:

属性名含义
id算子的 ID,是算子在整个执行计划中唯一的标识。在 TiDB 2.1 中,ID 会格式化地显示算子的树状结构。数据从孩子结点流向父亲结点,每个算子的父亲结点有且仅有一个。
estRows算子预计将会输出的数据条数,基于统计信息以及算子的执行逻辑估算而来。在 4.0 之前叫 count。
task算子属于的 task 种类。目前的执行计划分成为两种 task,一种叫 root task,在 tidb-server 上执行,一种叫 cop task,在 TiKV 或者 TiFlash 上并行执行。当前的执行计划在 task 级别的拓扑关系是一个 root task 后面可以跟许多 cop task,root task 使用 cop task 的输出结果作为输入。cop task 中执行的也即是 TiDB 下推到 TiKV 或者 TiFlash 上的任务,每个 cop task 分散在 TiKV 或者 TiFlash 集群中,由多个进程共同执行。
access object算子所访问的数据项信息。包括表 table,表分区 partition 以及使用的索引 index(如果有)。只有直接访问数据的算子才拥有这些信息。
operator info算子的其它信息。各个算子的 operator info 各有不同,可参考下面的示例解读。

示例

EXPLAIN SELECT 1;
+-------------------+---------+------+---------------+---------------+ | 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;
+-------------+---------+------+---------------+---------------+ | id | estRows | task | access object | operator info | +-------------+---------+------+---------------+---------------+ | Point_Get_1 | 1.00 | root | table:t1 | handle:1 | +-------------+---------+------+---------------+---------------+ 1 row in set (0.00 sec)
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)
EXPLAIN INSERT INTO t1 (c1) VALUES (4);
+----------+---------+------+---------------+---------------+ | id | estRows | task | access object | operator info | +----------+---------+------+---------------+---------------+ | Insert_1 | N/A | root | | N/A | +----------+---------+------+---------------+---------------+ 1 row in set (0.00 sec)
EXPLAIN UPDATE t1 SET c1=5 WHERE c1=3;
+---------------------------+---------+-----------+---------------+--------------------------------+ | 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)

EXPLAIN 语句中,你可以使用 FORMAT = "xxx" 语法指定输出内容的格式。目前支持的输出格式如下:

FORMAT作用
未指定未指定 FORMAT 时,默认输出格式为 row
briefEXPLAIN 语句输出结果中的算子 ID 将被简化,较之未指定 FORMAT 时输出结果的算子 ID 更为简化
dotEXPLAIN 语句将输出 DOT 格式的执行计划,可以通过 dot 程序(在 graphviz 包中)生成 PNG 文件
rowEXPLAIN 语句将以表格格式输出结果。更多信息,可参阅 TiDB 执行计划概览
tidb_jsonEXPLAIN 语句将输出 JSON 格式的执行计划,算子信息存放在一个 JSON 数组中
verboseEXPLAIN 语句将以 row 格式输出结果,并在结果中额外包含一个 estCost 列,表示查询的代价估算。关于如何使用这种格式的更多信息,请参阅执行计划管理 (SPM)
plan_cacheEXPLAIN 语句将以 row 格式输出结果,执行计划缓存信息将作为 warning 显示。
  • brief
  • DotGraph
  • JSON

EXPLAIN 中指定 FORMAT = "brief" 时,示例如下:

EXPLAIN FORMAT = "brief" DELETE FROM t1 WHERE c1=3;
+-------------------------+---------+-----------+---------------+--------------------------------+ | id | estRows | task | access object | operator info | +-------------------------+---------+-----------+---------------+--------------------------------+ | Delete | N/A | root | | N/A | | └─TableReader | 0.00 | root | | data:Selection | | └─Selection | 0.00 | cop[tikv] | | eq(test.t1.c1, 3) | | └─TableFullScan | 3.00 | cop[tikv] | table:t1 | keep order:false, stats:pseudo | +-------------------------+---------+-----------+---------------+--------------------------------+ 4 rows in set (0.001 sec)

除 MySQL 标准结果格式外,TiDB 还支持 DotGraph,需要在 EXPLAIN 中指定 FORMAT = "dot",示例如下:

create table t(a bigint, b bigint); explain format = "dot" select A.a, B.b from t A join t B on A.a > B.b where A.a < 10;
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | dot contents | +-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | digraph Projection_8 { subgraph cluster8{ node [style=filled, color=lightgrey] 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 程序,可使用以下方法生成 PNG 文件:

dot xx.dot -T png -O

The xx.dot is the result returned by the above statement.

如果你的计算机上未安装 dot 程序,可将结果复制到本网站以获取树形图:

Explain Dot

EXPLAIN 中指定 FORMAT = "tidb_json" 时,示例如下:

CREATE TABLE t(id int primary key, a int, b int, key(a)); EXPLAIN FORMAT = "tidb_json" SELECT id FROM t WHERE a = 1;
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | TiDB_JSON | +------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | [ { "id": "Projection_4", "estRows": "10.00", "taskType": "root", "operatorInfo": "test.t.id", "subOperators": [ { "id": "IndexReader_6", "estRows": "10.00", "taskType": "root", "operatorInfo": "index:IndexRangeScan_5", "subOperators": [ { "id": "IndexRangeScan_5", "estRows": "10.00", "taskType": "cop[tikv]", "accessObject": "table:t, index:a(a)", "operatorInfo": "range:[1,1], keep order:false, stats:pseudo" } ] } ] } ] | +------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.01 sec)

在输出结果中,idestRowstaskTypeaccessObjectoperatorInfo 与默认格式输出的列含义相同。subOperators 是存放子节点的数组,子节点的字段及含义与父节点均相同。若某字段缺失,则说明该字段为空。

MySQL 兼容性

  • EXPLAIN 的格式和 TiDB 中潜在的执行计划都与 MySQL 有很大不同。
  • TiDB 不支持 FORMAT=JSONFORMAT=TREE 选项。
  • TiDB 支持的 FORMAT=tidb_json 是对当前默认 EXPLAIN 格式的 JSON 编码,与 MySQL 的 FORMAT=JSON 结果的格式、字段信息都不同。

EXPLAIN FOR CONNECTION

EXPLAIN FOR CONNECTION 用于获得一个连接中当前正在执行 SQL 的执行计划或者是最后执行 SQL 的执行计划,其输出格式与 EXPLAIN 完全一致。但 TiDB 中的实现与 MySQL 不同,除了输出格式之外,还有以下区别:

  • 如果连接处于睡眠状态,MySQL 返回为空,而 TiDB 返回的是最后执行的查询计划。
  • 如果获取当前会话连接的执行计划,MySQL 会报错,而 TiDB 会正常返回。
  • MySQL 的文档中指出,MySQL 要求登录用户与被查询的连接相同,或者拥有 PROCESS 权限,而 TiDB 则要求登录用户与被查询的连接相同,或者拥有 SUPER 权限。

另请参阅