我的书签
添加书签
移除书签EXPLAIN
语句 DESC 和 DESCRIBE 是 EXPLAIN 的别名。EXPLAIN <tableName> 的替代用法记录在 SHOW [FULL] COLUMNS FROM 下。
TiDB 支持 EXPLAIN [options] FOR CONNECTION connection_id，但与 MySQL 的 EXPLAIN FOR 有一些区别，请参见 。
ExplainSym
ExplainStmt
ExplainableStmt
EXPLAIN 输出格式
注意
使用 MySQL 客户端连接到 TiDB 时，为避免输出结果在终端中换行，可先执行 pager less -S 命令。执行命令后，新的 EXPLAIN 的输出结果不再换行，可按右箭头 → 键水平滚动阅读输出结果。
注意
在执行计划返回结果中，自 v6.4.0 版本起，特定算子（即 IndexJoin 和 Apply 算子的 Probe 端所有子节点）的 estRows 字段意义与 v6.4.0 之前的有所不同。细节请参考 TiDB 执行计划概览。
目前 TiDB 的 EXPLAIN 会输出 5 列，分别是：id，estRows，task，access object，operator info。执行计划中每个算子都由这 5 列属性来描述，EXPLAIN 结果中每一行描述一个算子。每个属性的具体含义如下：
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;
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 |+---------------------------+---------+-----------+---------------+--------------------------------+| └─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 |
row | EXPLAIN 语句将以表格格式输出结果。更多信息，可参阅 TiDB 执行计划概览 |
brief | EXPLAIN 语句输出结果中的算子 ID 将被简化，较之未指定 FORMAT 时输出结果的算子 ID 更为简化 |
dot | EXPLAIN 语句将输出 DOT 格式的执行计划，可以通过 dot 程序（在 graphviz 包中）生成 PNG 文件 |
tidb_json | EXPLAIN 语句将输出 JSON 格式的执行计划，算子信息存放在一个 JSON 数组中 |
- 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=blacklabel = "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=blacklabel = "cop""Selection_12" -> "TableFullScan_11"}subgraph cluster16{node [style=filled, color=lightgrey]label = "cop""Selection_16" -> "TableFullScan_15"}"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 中指定 FORMAT = "tidb_json" 时，示例如下：
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+| 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)
MySQL 兼容性
EXPLAIN的格式和 TiDB 中潜在的执行计划都与 MySQL 有很大不同。- TiDB 不支持
FORMAT=JSON或FORMAT=TREE选项。 - TiDB 支持的
FORMAT=tidb_json是对当前默认EXPLAIN格式的 JSON 编码，与 MySQL 的FORMAT=JSON结果的格式、字段信息都不同。
EXPLAIN FOR CONNECTION 用于获得一个连接中当前正在执行 SQL 的执行计划或者是最后执行 SQL 的执行计划，其输出格式与 EXPLAIN 完全一致。但 TiDB 中的实现与 MySQL 不同，除了输出格式之外，还有以下区别：
- 如果连接处于睡眠状态，MySQL 返回为空，而 TiDB 返回的是最后执行的查询计划。
- 如果获取当前会话连接的执行计划，MySQL 会报错，而 TiDB 会正常返回。
- MySQL 的文档中指出，MySQL 要求登录用户与被查询的连接相同，或者拥有
PROCESS权限，而 TiDB 则要求登录用户与被查询的连接相同，或者拥有 权限。
