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 SELECT sum(number) FROM numbers(10) UNION ALL SELECT sum(number) FROM numbers(10) ORDER BY sum(number) ASC FORMAT TSV;
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)
Expression (Projection)
MergingSorted (Merge sorted streams for ORDER BY)
MergeSorting (Merge sorted blocks for ORDER BY)
PartialSorting (Sort each block for ORDER BY)
Expression (Before ORDER BY and SELECT)
Aggregating
Expression (Before GROUP BY)
SettingQuotaAndLimits (Set limits and quota after reading from storage)
ReadFromStorage (SystemNumbers)
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"
}
]
}
]
}
]
}
}
]
如果 description = 1，则 Description 键将添加到步骤中。
{
"Node Type": "ReadFromStorage",
"Description": "SystemOne"
}
如果 header = 1，则 Header 键将作为列数组添加到步骤中。
示例
EXPLAIN json = 1, description = 0, header = 1 SELECT 1, 2 + dummy;
[
{
"Plan": {
"Node Type": "Expression",
"Header": [
{
"Name": "1",
"Type": "UInt8"
},
{
"Name": "plus(2, dummy)",
"Type": "UInt16"
}
],
"Plans": [
{
"Node Type": "SettingQuotaAndLimits",
"Header": [
{
"Name": "dummy",
"Type": "UInt8"
}
],
"Plans": [
{
"Node Type": "ReadFromStorage",
"Header": [
{
"Name": "dummy",
"Type": "UInt8"
}
]
}
]
}
]
}
}
]
如果 indexes = 1，则会添加 Indexes 键。它包含已使用索引的数组。每个索引都用 JSON 描述，包含 Type 键（字符串 MinMax、Partition、PrimaryKey 或 Skip）以及可选键
Name— 索引名称（目前仅用于Skip索引）。Keys— 索引使用的列数组。Condition— 使用的条件。Description— 索引描述（目前仅用于Skip索引）。Parts— 应用索引之前/之后的部件数量。Granules— 应用索引之前/之后的颗粒数量。
示例
"Node Type": "ReadFromMergeTree",
"Indexes": [
{
"Type": "MinMax",
"Keys": ["y"],
"Condition": "(y in [1, +inf))",
"Parts": 5/4,
"Granules": 12/11
},
{
"Type": "Partition",
"Keys": ["y", "bitAnd(z, 3)"],
"Condition": "and((bitAnd(z, 3) not in [1, 1]), and((y in [1, +inf)), (bitAnd(z, 3) not in [1, 1])))",
"Parts": 4/3,
"Granules": 11/10
},
{
"Type": "PrimaryKey",
"Keys": ["x", "y"],
"Condition": "and((x in [11, +inf)), (y in [1, +inf)))",
"Parts": 3/2,
"Granules": 10/6
},
{
"Type": "Skip",
"Name": "t_minmax",
"Description": "minmax GRANULARITY 2",
"Parts": 2/1,
"Granules": 6/2
},
{
"Type": "Skip",
"Name": "t_set",
"Description": "set GRANULARITY 2",
"": 1/1,
"Granules": 2/1
}
]
如果 actions = 1，则添加的键将取决于步骤类型。
示例
EXPLAIN json = 1, actions = 1, description = 0 SELECT 1 FORMAT TSVRaw;
[
{
"Plan": {
"Node Type": "Expression",
"Expression": {
"Inputs": [],
"Actions": [
{
"Node Type": "Column",
"Result Type": "UInt8",
"Result Type": "Column",
"Column": "Const(UInt8)",
"Arguments": [],
"Removed Arguments": [],
"Result": 0
}
],
"Outputs": [
{
"Name": "1",
"Type": "UInt8"
}
],
"Positions": [0],
"Project Input": true
},
"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)
NumbersRange × 2 0 → 1
EXPLAIN ESTIMATE
显示要从表中读取的行数、标记数和部件数的估计值，以便处理查询。适用于 MergeTree 系列中的表。
示例
创建表
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) │
└─────────────────────────────────────────────────────────┘
注意
验证并不完整，因此成功的查询不能保证覆盖不会导致问题。