# EXPLAIN

显示语句的执行计划。

EXPLAIN [AST | SYNTAX | QUERY TREE | PLAN | PIPELINE | ESTIMATE | TABLE OVERRIDE] [setting = value, ...]
    [
      SELECT ... |
      tableFunction(...) [COLUMNS (...)] [ORDER BY ...] [PARTITION BY ...] [PRIMARY KEY] [SAMPLE BY ...] [TTL ...]
    ]
    [FORMAT ...]

EXPLAIN 类型

AST — 抽象语法树。
SYNTAX — 经过 AST 级别优化后的查询文本。
QUERY TREE — 经过查询树级别优化后的查询树。
PLAN — 查询执行计划。
PIPELINE — 查询执行流程。

# EXPLAIN AST

显示查询的 AST。支持所有类型的查询，不仅限于 SELECT。

示例：

EXPLAIN AST SELECT 1;

SelectWithUnionQuery (children 1)
 ExpressionList (children 1)
  SelectQuery (children 1)
   ExpressionList (children 1)
    Literal UInt64_1

EXPLAIN AST ALTER TABLE t1 DELETE WHERE date = today();

  explain
  AlterQuery  t1 (children 1)
   ExpressionList (children 1)
    AlterCommand 27 (children 1)
     Function equals (children 1)
      ExpressionList (children 2)
       Identifier date
       Function today (children 1)
        ExpressionList

# EXPLAIN SYNTAX

返回经过语法优化后的查询。

示例：

EXPLAIN SYNTAX SELECT * FROM system.numbers AS a, system.numbers AS b, system.numbers AS c;

SELECT
    `--a.number` AS `a.number`,
    `--b.number` AS `b.number`,
    number AS `c.number`
FROM
(
    SELECT
        number AS `--a.number`,
        b.number AS `--b.number`
    FROM system.numbers AS a
    CROSS JOIN system.numbers AS b
) AS `--.s`
CROSS JOIN system.numbers AS c

# EXPLAIN QUERY TREE

设置：

run_passes — 在显示查询树之前运行所有查询树优化步骤。默认值：1。
dump_passes — 在显示查询树之前显示使用的优化步骤信息。默认值：0。
passes — 指定要运行的优化步骤数量。如果设置为 -1，则运行所有步骤。默认值：-1。

示例：

EXPLAIN QUERY TREE SELECT id, value FROM test_table;

QUERY id: 0
  PROJECTION COLUMNS
    id UInt64
    value String
  PROJECTION
    LIST id: 1, nodes: 2
      COLUMN id: 2, column_name: id, result_type: UInt64, source_id: 3
      COLUMN id: 4, column_name: value, result_type: String, source_id: 3
  JOIN TREE
    TABLE id: 3, table_name: default.test_table

# EXPLAIN PLAN

显示查询计划步骤。

设置：

header — 打印步骤的输出标题。默认值：0。
description — 打印步骤描述。默认值：1。
indexes — 显示使用的索引，每个应用的索引的过滤部分数量和过滤的粒度数量。默认值：0。仅适用于 MergeTree 表。
actions — 打印有关步骤操作的详细信息。默认值：0。
json — 以 JSON 格式打印查询计划步骤。默认值：0。建议使用 TSVRaw 格式以避免不必要的转义。

示例：

EXPLAIN SELECT sum(number) FROM numbers(10) GROUP BY number % 4;

Union
  Expression (Projection)
  Expression (Before ORDER BY and SELECT)
    Aggregating
      Expression (Before GROUP BY)
        SettingQuotaAndLimits (Set limits and quota after reading from storage)
          ReadFromStorage (SystemNumbers)

当 json = 1 时，查询计划以 JSON 格式表示。每个节点都是一个字典，始终具有 Node Type 和 Plans 两个键。Node Type 是一个字符串，表示步骤名称。Plans 是一个包含子步骤描述的数组。根据节点类型和设置，可能会添加其他可选键。

示例：

EXPLAIN json = 1, description = 0 SELECT 1 UNION ALL SELECT 2 FORMAT TSVRaw;

[
  {
    "Plan": {
      "Node Type": "Union",
      "Plans": [
        {
          "Node Type": "Expression",
          "Plans": [
            {
              "Node Type": "SettingQuotaAndLimits",
              "Plans": [
                {
                  "Node Type": "ReadFromStorage"
                }
              ]
            }
          ]
        },
        {
          "Node Type": "Expression",
          "Plans": [
            {
              "Node Type": "SettingQuotaAndLimits",
              "Plans": [
                {
                  "Node Type": "ReadFromStorage"
                }
              ]
            }
          ]
        }
      ]
    }
  }
]

# EXPLAIN PIPELINE

设置：

header — 打印每个输出端口的标题。默认值：0。
graph — 以 DOT 图形描述语言打印图形。默认值：0。
compact — 如果启用了 graph 设置，则以紧凑模式打印图形。默认值：1。

示例：

EXPLAIN PIPELINE SELECT sum(number) FROM numbers_mt(100000) GROUP BY number % 4;

(Union)
(Expression)
ExpressionTransform
  (Expression)
  ExpressionTransform
    (Aggregating)
    Resize 2 → 1
      AggregatingTransform × 2
        (Expression)
        ExpressionTransform × 2
          (SettingQuotaAndLimits)
            (ReadFromStorage)
            NumbersMt × 2 0 → 1