EXPLAIN

EXPLAIN 语句仅用于显示查询的执行计划,而不执行查询。EXPLAIN ANALYZE 可执行查询,补充 EXPLAIN 语句。如果 EXPLAIN 的输出与预期结果不匹配,可考虑在查询的每个表上执行 ANALYZE TABLE

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

语法图

ExplainSym:

ExplainSym

ExplainStmt:

ExplainStmt

ExplainableStmt:

ExplainableStmt

示例

  1. mysql> EXPLAIN SELECT 1;
  2. +-+-+++
  3. | id | count | task | operator info |
  4. +-+-+++
  5. | Projection_3 | 1.00 | root | 1 |
  6. | └─TableDual_4 | 1.00 | root | rows:1 |
  7. +-+-+++
  8. 2 rows in set (0.00 sec)
  9. mysql> CREATE TABLE t1 (id INT NOT NULL PRIMARY KEY auto_increment, c1 INT NOT NULL);
  10. Query OK, 0 rows affected (0.10 sec)
  11. mysql> INSERT INTO t1 (c1) VALUES (1), (2), (3);
  12. Query OK, 3 rows affected (0.02 sec)
  13. Records: 3 Duplicates: 0 Warnings: 0
  14. mysql> EXPLAIN SELECT * FROM t1 WHERE id = 1;
  15. +-+-++--+
  16. | id | count | task | operator info |
  17. +-+-++--+
  18. | Point_Get_1 | 1.00 | root | table:t1, handle:1 |
  19. +-+-++--+
  20. 1 row in set (0.00 sec)
  21. mysql> DESC SELECT * FROM t1 WHERE id = 1;
  22. +-+-++--+
  23. | id | count | task | operator info |
  24. +-+-++--+
  25. | Point_Get_1 | 1.00 | root | table:t1, handle:1 |
  26. +-+-++--+
  27. 1 row in set (0.00 sec)
  28. mysql> DESCRIBE SELECT * FROM t1 WHERE id = 1;
  29. +-+-++--+
  30. | id | count | task | operator info |
  31. +-+-++--+
  32. | Point_Get_1 | 1.00 | root | table:t1, handle:1 |
  33. +-+-++--+
  34. 1 row in set (0.00 sec)
  35. mysql> EXPLAIN INSERT INTO t1 (c1) VALUES (4);
  36. ERROR 1105 (HY000): Unsupported type *core.Insert
  37. mysql> EXPLAIN UPDATE t1 SET c1=5 WHERE c1=3;
  38. ++-++-+
  39. | id | count | task | operator info |
  40. ++-++-+
  41. | TableReader_6 | 10.00 | root | data:Selection_5 |
  42. | └─Selection_5 | 10.00 | cop | eq(test.t1.c1, 3) |
  43. | └─TableScan_4 | 10000.00 | cop | table:t1, range:[-inf,+inf], keep order:false, stats:pseudo |
  44. ++-++-+
  45. 3 rows in set (0.00 sec)
  46. mysql> EXPLAIN DELETE FROM t1 WHERE c1=3;
  47. ++-++-+
  48. | id | count | task | operator info |
  49. ++-++-+
  50. | TableReader_6 | 10.00 | root | data:Selection_5 |
  51. | └─Selection_5 | 10.00 | cop | eq(test.t1.c1, 3) |
  52. | └─TableScan_4 | 10000.00 | cop | table:t1, range:[-inf,+inf], keep order:false, stats:pseudo |
  53. ++-++-+
  54. 3 rows in set (0.00 sec)

如果未指定 FORMAT,或未指定 FORMAT ="row",那么 EXPLAIN 语句将以表格格式输出结果。更多信息,可参阅 Understand the Query Execution Plan

除 MySQL 标准结果格式外,TiDB 还支持 DotGraph。需按照下列所示指定 FORMAT ="dot"

  1. create table t(a bigint, b bigint);
  2. desc format = "dot" select A.a, B.b from t A join t B on A.a > B.b where A.a < 10;
  3. TiDB > desc format = "dot" select A.a, B.b from t A join t B on A.a > B.b where A.a < 10;desc format = "dot" select A.a, B.b from t A join t B on A.a > B.b where A.a < 10;
  4. +--+
  5. | dot contents |
  6. +--+
  7. |
  8. digraph HashRightJoin_7 {
  9. subgraph cluster7{
  10. node [style=filled, color=lightgrey]
  11. color=black
  12. label = "root"
  13. "HashRightJoin_7" -> "TableReader_10"
  14. "HashRightJoin_7" -> "TableReader_12"
  15. }
  16. subgraph cluster9{
  17. node [style=filled, color=lightgrey]
  18. color=black
  19. label = "cop"
  20. "Selection_9" -> "TableScan_8"
  21. }
  22. subgraph cluster11{
  23. node [style=filled, color=lightgrey]
  24. color=black
  25. label = "cop"
  26. "TableScan_11"
  27. }
  28. "TableReader_10" -> "Selection_9"
  29. "TableReader_12" -> "TableScan_11"
  30. }
  31. |
  32. +--+
  33. 1 row in set (0.00 sec)

如果你的计算机上安装了 dot 程序(在 graphviz 包中),可使用以下方法生成 PNG 文件:

  1. dot xx.dot -T png -O
  2. The xx.dot is the result returned by the above statement.

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

Explain Dot

MySQL 兼容性

  • EXPLAIN 的格式和 TiDB 中潜在的执行计划都与 MySQL 有很大不同。
  • TiDB 不像 MySQL 那样支持 EXPLAIN FORMAT = JSON
  • TiDB 目前不支持插入语句的 EXPLAIN

另请参阅