EXPLAIN ANALYZE

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

语法图

ExplainSym:

ExplainSym

ExplainStmt:

ExplainStmt

ExplainableStmt:

ExplainableStmt

示例

  1. mysql> CREATE TABLE t1 (id INT NOT NULL PRIMARY KEY auto_increment, c1 INT NOT NULL);
  2. Query OK, 0 rows affected (0.12 sec)
  4. mysql> INSERT INTO t1 (c1) VALUES (1), (2), (3);
  5. Query OK, 3 rows affected (0.02 sec)
  6. Records: 3  Duplicates: 0  Warnings: 0
  8. mysql> EXPLAIN ANALYZE SELECT * FROM t1 WHERE id = 1;
  9. +-------------+-------+------+--------------------+---------------------------+
  10. | id          | count | task | operator info      | execution info            |
  11. +-------------+-------+------+--------------------+---------------------------+
  12. | Point_Get_1 | 1.00  | root | table:t1, handle:1 | time:0ns, loops:0, rows:0 |
  13. +-------------+-------+------+--------------------+---------------------------+
  14. 1 row in set (0.01 sec)
  16. mysql> EXPLAIN ANALYZE SELECT * FROM t1;
  17. +-------------------+----------+------+-------------------------------------------------------------+----------------------------------+
  18. | id                | count    | task | operator info                                               | execution info                   |
  19. +-------------------+----------+------+-------------------------------------------------------------+----------------------------------+
  20. | TableReader_5     | 10000.00 | root | data:TableScan_4                                            | time:931.759µs, loops:2, rows:3  |
  21. | └─TableScan_4     | 10000.00 | cop  | table:t1, range:[-inf,+inf], keep order:false, stats:pseudo | time:0s, loops:0, rows:3         |
  22. +-------------------+----------+------+-------------------------------------------------------------+----------------------------------+
  23. 2 rows in set (0.00 sec)

MySQL 兼容性

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

另请参阅