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]
AST — 抽象语法树SYNTAX — 经过 AST-level 优化后的查询文本。QUERY TREE — 优化后的查询树。PLAN — 查询计划PIPELINE — 查询执行流水线转储查询的抽象语法树（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 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
配置
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
查询计划步骤。
设置：
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 } } ] } ] } ] } }
设置：
header — 为每个输出端口打印标题。默认值：0。graph — 以DOT图描述语言打印图。默认值：0。compact — 如果启用了graph设置，则以紧凑模式打印图。默认值：1。Example:
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 ------------------------------------------
显示在处理查询时估计从表中读取的rows、marks和parts。
示例
创建一张表:
CREATE TABLE ttt (i Int64) ENGINE = CnchMergeTree() 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 │ └──────────┴───────┴───────┴──────┴───────┘
显示通过表函数访问的表结构上表重写的结果。同时进行一些验证，如果重写可能导致某种失败，则抛出异常
示例
假设你有一个远程的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) │ └─────────────────────────────────────────────────────────┘
注意：验证并不完整，因此查询成功并不能保证重写不会导致问题。