语法

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 SELECT sum(total_rows) FROM system.tables UNION ALL SELECT sum(total_bytes) FROM system.tables ORDER BY sum(total_rows) ASC;

Projection Est. ? rows
│     Expressions: sum(total_rows):=`expr#sum(total_rows)_2`
└─ Union Est. ? rows
   │     OutputToInputs: expr#sum(total_rows)_2 = [expr#sum(total_rows),expr#sum(total_bytes)]
   ├─ Aggregating Est. ? rows
   │  │     Group by: {}
   │  │     Aggregates: expr#sum(total_rows):=AggNull(sum)(total_rows)
   │  └─ TableScan system.tables Est. ? rows
   │           Outputs: [total_rows]
   └─ Aggregating Est. ? rows
      │     Group by: {}
      │     Aggregates: expr#sum(total_bytes):=AggNull(sum)(total_bytes)
      └─ TableScan system.tables Est. ? rows
               Outputs: [total_bytes]

EXPLAIN 类型

EXPLAIN 支持以下类型：

• AST — 抽象语法树。
• SYNTAX — 经过 AST-level 优化后的查询文本。
• 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.databases AS a, system.tables AS b;

SELECT
    name,
    engine,
    data_path,
    metadata_path,
    uuid,
    database,
    b.name,
    b.uuid,
    b.engine,
    is_temporary,
    data_paths,
    b.metadata_path,
    metadata_modification_time,
    dependencies_database,
    dependencies_table,
    create_table_query,
    engine_full,
    as_select,
    partition_key,
    sorting_key,
    primary_key,
    sampling_key,
    storage_policy,
    total_rows,
    total_bytes,
    lifetime_rows,
    lifetime_bytes,
    comment,
    has_own_data
FROM system.databases AS a
CROSS JOIN system.tables AS b

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

查询计划步骤。

配置

• json — 以 JSON 格式将查询计划步骤作为一行打印。默认值：0。建议使用 TSVRaw 格式以避免不必要的转义。

示例

EXPLAIN SELECT count(total_rows) FROM system.tables GROUP BY total_rows % 4

Projection Est. ? rows
│     Expressions: count(total_rows):=`expr#count(total_rows)`
└─ Gather Exchange Est. ? rows
   └─ Projection Est. ? rows
      │     Expressions: [expr#count(total_rows)]
      └─ MergingAggregated Est. ? rows
         └─ Repartition Exchange Est. ? rows
            │     Partition by: {expr#modulo(total_rows, 4)}
            └─ Aggregating Est. ? rows
               │     Group by: {expr#modulo(total_rows, 4)}
               │     Aggregates: expr#count(total_rows):=AggNull(count)(total_rows)
               └─ Projection Est. ? rows
                  │     Expressions: [total_rows], expr#modulo(total_rows, 4):=total_rows % 4
                  └─ TableScan system.tables Est. ? rows
                           Outputs: [total_rows]

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

EXPLAIN json = 1 SELECT 1 UNION ALL SELECT 2 FORMAT TSVRaw;

explain
{
  ""total_cost"" : 0.44400000661611555,
  ""cpu_cost_value"" : 0.6000000089406967,
  ""net_cost_value"" : 0,
  ""men_cost_value"" : 0,
  ""plan"" : {
    ""NodeId"" : 62,
    ""NodeType"" : ""Projection"",
    ""Expressions"" : [
      ""1:=`expr#1_1`""
    ],
    ""Statistic"" : {
      ""RowCount"" : 2
    },
    ""Children"" : [
      {
        ""NodeId"" : 61,
        ""NodeType"" : ""Union"",
        ""OutputToInputs"" : [
          ""expr#1_1 = [expr#1,expr#2]""
        ],
        ""Statistic"" : {
          ""RowCount"" : 2
        },
        ""Children"" : [
          {
            ""NodeId"" : 58,
            ""NodeType"" : ""Projection"",
            ""Expressions"" : [
              ""expr#1:=1""
            ],
            ""Statistic"" : {
              ""RowCount"" : 1
            },
            ""Children"" : [
              {
                ""NodeId"" : 57,
                ""NodeType"" : ""Values"",
                ""Statistic"" : {
                  ""RowCount"" : 1
                }
              }
            ]
          },
          {
            ""NodeId"" : 60,
            ""NodeType"" : ""Projection"",
            ""Expressions"" : [
              ""expr#2:=2""
            ],
            ""Statistic"" : {
              ""RowCount"" : 1
            },
            ""Children"" : [
              {
                ""NodeId"" : 59,
                ""NodeType"" : ""Values"",
                ""Statistic"" : {
                  ""RowCount"" : 1
                }
              }
            ]
          }
        ]
      }
    ]
  }
}

EXPLAIN PIPELINE

配置

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

示例

EXPLAIN PIPELINE SELECT count(total_rows) FROM system.tables GROUP BY total_rows % 4

Segment[ 2 ] :
(Aggregating)
AggregatingTransform
  (Projection)
  ExpressionTransform
    (TableScan) # 
    Tables 0 → 1

------------------------------------------

Segment[ 1 ] :
(Projection)
ExpressionTransform × 8
  (MergingAggregated)
  Resize 1 → 8
    MergingAggregatedTransform
      Resize 16 → 1
        (RemoteExchangeSource)
        Source segment_id : [ 2 ]
        DeserializeBufTransform × 16
          Resize 1 → 16
            ExchangeSource 0 → 1

------------------------------------------

Segment[ 0 ] :
(Projection)
ExpressionTransform × 16
  (RemoteExchangeSource)
  Source segment_id : [ 1 ]
  DeserializeBufTransform × 16
    Resize 1 → 16
      ExchangeSource 0 → 1

------------------------------------------

EXPLAIN ESTIMATE

显示在处理查询时估计从表中读取的 rows、marks和parts。

示例

创建一张表：

CREATE TABLE ttt (i Int64) ENGINE = MergeTree() ORDER BY i SETTINGS index_granularity = 16, write_final_mark = 0;
INSERT INTO ttt SELECT number FROM numbers(128);
OPTIMIZE TABLE ttt;

查询：

EXPLAIN ESTIMATE SELECT * FROM ttt;

结果：

┌─database─┬─table─┬─parts─┬─rows─┬─marks─┐
│ default  │ ttt   │     1 │  128 │     8 │
└──────────┴───────┴───────┴──────┴───────┘

EXPLAIN TABLE OVERRIDE

显示通过表函数访问的表结构上表重写的结果。同时进行一些验证，如果重写可能导致某种失败，则抛出异常。

示例

假设你有一个远程的 MySQL 表如下：

CREATE TABLE db.tbl (
    id INT PRIMARY KEY,
    created DATETIME DEFAULT now()
)

EXPLAIN TABLE OVERRIDE mysql('127.0.0.1:3306', 'db', 'tbl', 'root', 'clickhouse')
PARTITION BY toYYYYMM(assumeNotNull(created))

查询结果：

┌─explain─────────────────────────────────────────────────┐
│ PARTITION BY uses columns: created Nullable(DateTime) │
└─────────────────────────────────────────────────────────┘