EXPLAIN ANALYZE

EXPLAIN ANALYZE 语句的工作方式类似于 EXPLAIN,主要区别在于前者实际上会执行语句。这样可以将查询计划中的估计值与执行时所遇到的实际值进行比较。如果估计值与实际值显著不同,那么应考虑在受影响的表上运行 ANALYZE TABLE

语法图

ExplainSym:

ExplainSym

ExplainStmt:

ExplainStmt

ExplainableStmt:

ExplainableStmt

EXPLAIN ANALYZE 输出格式

EXPLAIN 不同,EXPLAIN ANALYZE 会执行对应的 SQL 语句,记录其运行时信息,和执行计划一并返回出来。因此,可以将 EXPLAIN ANALYZE 视为 EXPLAIN 语句的扩展。EXPLAIN ANALYZE 语句的返回结果相比 EXPLAIN,增加了 actRowsexecution infomemorydisk 这几列信息:

属性名 含义
actRows 算子实际输出的数据条数。
execution info 算子的实际执行信息。time 表示从进入算子到离开算子的全部 wall time,包括所有子算子操作的全部执行时间。如果该算子被父算子多次调用 (loops),这个时间就是累积的时间。loops 是当前算子被父算子调用的次数。
memory 算子占用内存空间的大小。
disk 算子占用磁盘空间的大小。

示例

CREATE TABLE t1 (id INT NOT NULL PRIMARY KEY AUTO_INCREMENT, c1 INT NOT NULL);
Query OK, 0 rows affected (0.12 sec)
INSERT INTO t1 (c1) VALUES (1), (2), (3);
Query OK, 3 rows affected (0.02 sec)
Records: 3  Duplicates: 0  Warnings: 0
EXPLAIN ANALYZE SELECT * FROM t1 WHERE id = 1;
+-------------+---------+---------+------+---------------+--------------------------+---------------+--------+------+
| id          | estRows | actRows | task | access object | execution info           | operator info | memory | disk |
+-------------+---------+---------+------+---------------+--------------------------+---------------+--------+------+
| Point_Get_1 | 1.00    | 1       | root | table:t1      | time:177.183µs, loops:2  | handle:1      | N/A    | N/A  |
+-------------+---------+---------+------+---------------+--------------------------+---------------+--------+------+
1 row in set (0.01 sec)
EXPLAIN ANALYZE SELECT * FROM t1;
+-----------------------+----------+---------+-----------+---------------+------------------------------------------------------------------------+--------------------------------+-----------+------+
| id                    | estRows  | actRows | task      | access object | execution info                                                         | operator info                  | memory    | disk |
+-----------------------+----------+---------+-----------+---------------+------------------------------------------------------------------------+--------------------------------+-----------+------+
| TableReader_5         | 10000.00 | 3       | root      |               | time:454.744µs, loops:2, rpc num: 1, rpc time:328.334µs, proc keys:0   | data:TableFullScan_4           | 199 Bytes | N/A  |
| └─TableFullScan_4     | 10000.00 | 3       | cop[tikv] | table:t1      | time:148.227µs, loops:4                                                | keep order:false, stats:pseudo | N/A       | N/A  |
+-----------------------+----------+---------+-----------+---------------+------------------------------------------------------------------------+--------------------------------+-----------+------+
2 rows in set (0.00 sec)

MySQL 兼容性

该语句是 TiDB 对 MySQL 语法的扩展。

另请参阅