• EXPLAIN
    • 语法图
    • 示例
    • MySQL 兼容性
    • 另请参阅

    EXPLAIN

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

    语句 DESC 和 DESCRIBE 是 EXPLAIN 的别名。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)
    10. mysql> CREATE TABLE t1 (id INT NOT NULL PRIMARY KEY auto_increment, c1 INT NOT NULL);
    11. Query OK, 0 rows affected (0.10 sec)
    13. mysql> INSERT INTO t1 (c1) VALUES (1), (2), (3);
    14. Query OK, 3 rows affected (0.02 sec)
    15. Records: 3  Duplicates: 0  Warnings: 0
    17. mysql> EXPLAIN SELECT * FROM t1 WHERE id = 1;
    18. +-------------+-------+------+--------------------+
    19. | id          | count | task | operator info      |
    20. +-------------+-------+------+--------------------+
    21. | Point_Get_1 | 1.00  | root | table:t1, handle:1 |
    22. +-------------+-------+------+--------------------+
    23. 1 row in set (0.00 sec)
    25. mysql> DESC SELECT * FROM t1 WHERE id = 1;
    26. +-------------+-------+------+--------------------+
    27. | id          | count | task | operator info      |
    28. +-------------+-------+------+--------------------+
    29. | Point_Get_1 | 1.00  | root | table:t1, handle:1 |
    30. +-------------+-------+------+--------------------+
    31. 1 row in set (0.00 sec)
    33. mysql> DESCRIBE SELECT * FROM t1 WHERE id = 1;
    34. +-------------+-------+------+--------------------+
    35. | id          | count | task | operator info      |
    36. +-------------+-------+------+--------------------+
    37. | Point_Get_1 | 1.00  | root | table:t1, handle:1 |
    38. +-------------+-------+------+--------------------+
    39. 1 row in set (0.00 sec)
    41. mysql> EXPLAIN INSERT INTO t1 (c1) VALUES (4);
    42. ERROR 1105 (HY000): Unsupported type *core.Insert
    44. mysql> EXPLAIN UPDATE t1 SET c1=5 WHERE c1=3;
    45. +---------------------+----------+------+-------------------------------------------------------------+
    46. | id                  | count    | task | operator info                                               |
    47. +---------------------+----------+------+-------------------------------------------------------------+
    48. | TableReader_6       | 10.00    | root | data:Selection_5                                            |
    49. | └─Selection_5       | 10.00    | cop  | eq(test.t1.c1, 3)                                           |
    50. |   └─TableScan_4     | 10000.00 | cop  | table:t1, range:[-inf,+inf], keep order:false, stats:pseudo |
    51. +---------------------+----------+------+-------------------------------------------------------------+
    52. 3 rows in set (0.00 sec)
    54. mysql> EXPLAIN DELETE FROM t1 WHERE c1=3;
    55. +---------------------+----------+------+-------------------------------------------------------------+
    56. | id                  | count    | task | operator info                                               |
    57. +---------------------+----------+------+-------------------------------------------------------------+
    58. | TableReader_6       | 10.00    | root | data:Selection_5                                            |
    59. | └─Selection_5       | 10.00    | cop  | eq(test.t1.c1, 3)                                           |
    60. |   └─TableScan_4     | 10000.00 | cop  | table:t1, range:[-inf,+inf], keep order:false, stats:pseudo |
    61. +---------------------+----------+------+-------------------------------------------------------------+
    62. 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;
    4. 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;
    5. +--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
    6. | dot contents                                                                                                                                                                                                                                                                                                                                                                                                                                                                               |
    7. +--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
    8. |
    9. digraph HashRightJoin_7 {
    10. subgraph cluster7{
    11. node [style=filled, color=lightgrey]
    12. color=black
    13. label = "root"
    14. "HashRightJoin_7" -> "TableReader_10"
    15. "HashRightJoin_7" -> "TableReader_12"
    16. }
    17. subgraph cluster9{
    18. node [style=filled, color=lightgrey]
    19. color=black
    20. label = "cop"
    21. "Selection_9" -> "TableScan_8"
    22. }
    23. subgraph cluster11{
    24. node [style=filled, color=lightgrey]
    25. color=black
    26. label = "cop"
    27. "TableScan_11"
    28. }
    29. "TableReader_10" -> "Selection_9"
    30. "TableReader_12" -> "TableScan_11"
    31. }
    32.  |
    33. +--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
    34. 1 row in set (0.00 sec)

    如果你的计算机上安装了 dot 程序（在 graphviz 包中），可使用以下方法生成 PNG 文件：

    1. dot xx.dot -T png -O
    3. 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。

    另请参阅

    • Understanding the Query Execution Plan
    • EXPLAIN ANALYZE
    • ANALYZE TABLE
    • TRACE