在 clickhouse 20.6 版本之前要查看 SQL 语句的执行计划需要设置日志级别为 trace 才能可以看到，并且只能真正执行 sql，在执行日志里面查看。在 20.6 版本引入了原生的执行计划的语法。在 20.6.3 版本成为正式版本的功能。
1.基本语法
EXPLAIN [AST | SYNTAX | PLAN | PIPELINE] [setting = value, ...]
SELECT ... [FORMAT ...]
➢ PLAN：用于查看执行计划，默认值。
header 打印计划中各个步骤的 head 说明，默认关闭，默认值 0;
description 打印计划中各个步骤的描述，默认开启，默认值 1；
actions 打印计划中各个步骤的详细信息，默认关闭，默认值 0。
➢ AST ：用于查看语法树;
➢ SYNTAX：用于优化语法;
➢ PIPELINE：用于查看 PIPELINE 计划。
header 打印计划中各个步骤的 head 说明，默认关闭;
graph 用 DOT 图形语言描述管道图，默认关闭，需要查看相关的图形需要配合graphviz 查看；
actions 如果开启了 graph，紧凑打印打，默认开启。
注：PLAN 和 PIPELINE 还可以进行额外的显示设置，如上参数所示。
2使用 EXPLAIN
1)查看 PLAN
简单查询
superset-BI :) explain plan select arrayJoin([1,2,3,null,null]);
EXPLAIN
SELECT arrayJoin([1, 2, 3, NULL, NULL])
Query id: b90df0c0-f8d8-4530-a1e3-921a3b1b04af
┌─explain───────────────────────────────────────────────────────────────────┐
│ Expression ((Projection + Before ORDER BY)) │
│ SettingQuotaAndLimits (Set limits and quota after reading from storage) │
│ ReadFromStorage (SystemOne) │
└───────────────────────────────────────────────────────────────────────────┘
3 rows in set. Elapsed: 0.001 sec.
复杂 SQL 的执行计划
superset-BI :) explain select database,table,count(1) cnt from system.parts where
database in ('datasets','system') group by database,table order by
database,cnt desc limit 2 by database;
EXPLAIN
SELECT
database,
table,
count(1) AS cnt
FROM system.parts
WHERE database IN ('datasets', 'system')
GROUP BY
database,
table
ORDER BY
database ASC,
cnt DESC
LIMIT 2 BY database
Query id: f7d9ef19-2c00-4816-a64b-53815af836a2
┌─explain─────────────────────────────────────────────────────────────────────────────────┐
│ Expression (Projection) │
│ LimitBy │
│ Expression (Before LIMIT BY) │
│ Sorting (Sorting for ORDER BY) │
│ Expression (Before ORDER BY) │
│ Aggregating │
│ Expression (Before GROUP BY) │
│ Filter (WHERE) │
│ SettingQuotaAndLimits (Set limits and quota after reading from storage) │
│ ReadFromStorage (SystemParts) │
└─────────────────────────────────────────────────────────────────────────────────────────┘
10 rows in set. Elapsed: 0.003 sec.
打开全部的参数的执行计划
superset-BI :) EXPLAIN header=1, actions=1,description=1 SELECT number from
system.numbers limit 10;
EXPLAIN header = 1, actions = 1, description = 1
SELECT number
FROM system.numbers
LIMIT 10
Query id: d8fab6c8-d8b3-467e-8ef1-1b3591806d1c
┌─explain───────────────────────────────────────────────────────────────────┐
│ Expression ((Projection + Before ORDER BY)) │
│ Header: number UInt64 │
│ Actions: INPUT :: 0 -> number UInt64 : 0 │
│ Positions: 0 │
│ SettingQuotaAndLimits (Set limits and quota after reading from storage) │
│ Header: number UInt64 │
│ Limit (preliminary LIMIT (without OFFSET)) │
│ Header: number UInt64 │
│ Limit 10 │
│ Offset 0 │
│ ReadFromStorage (SystemNumbers) │
│ Header: number UInt64 │
└───────────────────────────────────────────────────────────────────────────┘
12 rows in set. Elapsed: 0.001 sec. 2)AST 语法树
superset-BI :) EXPLAIN AST SELECT number from system.numbers limit 10;
EXPLAIN AST
SELECT number
FROM system.numbers
LIMIT 10
Query id: 06c3eef4-9f67-4464-8cf3-e5e38f3d0ea3
┌─explain─────────────────────────────────────┐
│ SelectWithUnionQuery (children 1) │
│ ExpressionList (children 1) │
│ SelectQuery (children 3) │
│ ExpressionList (children 1) │
│ Identifier number │
│ TablesInSelectQuery (children 1) │
│ TablesInSelectQueryElement (children 1) │
│ TableExpression (children 1) │
│ TableIdentifier system.numbers │
│ Literal UInt64_10 │
└─────────────────────────────────────────────┘
10 rows in set. Elapsed: 0.001 sec. 3)SYNTAX 语法优化
//先做一次查询
superset-BI :) SELECT number = 1 ? 'hello' : (number = 2 ? 'world' : 'fengzhen') FROM numbers(10);
SELECT if(number = 1, 'hello', if(number = 2, 'world', 'fengzhen'))
FROM numbers(10)
Query id: 7351b9ef-830e-43df-b6f4-030d6407bc30
┌─if(equals(number, 1), 'hello', if(equals(number, 2), 'world', 'fengzhen'))─┐
│ fengzhen │
│ hello │
│ world │
│ fengzhen │
│ fengzhen │
│ fengzhen │
│ fengzhen │
│ fengzhen │
│ fengzhen │
│ fengzhen │
└────────────────────────────────────────────────────────────────────────────┘
10 rows in set. Elapsed: 0.002 sec.
查看语法优化
EXPLAIN SYNTAX SELECT number = 1 ? 'hello' : (number = 2 ? 'world' :'fengzhen') FROM numbers(10);
superset-BI :) EXPLAIN SYNTAX SELECT number = 1 ? 'hello' : (number = 2 ? 'world' :'fengzhen') FROM numbers(10);
EXPLAIN SYNTAX
SELECT if(number = 1, 'hello', if(number = 2, 'world', 'fengzhen'))
FROM numbers(10)
Query id: 93840505-4caa-47a4-8c6f-83f98e470fab
┌─explain─────────────────────────────────────────────────────────────┐
│ SELECT if(number = 1, 'hello', if(number = 2, 'world', 'fengzhen')) │
│ FROM numbers(10) │
└─────────────────────────────────────────────────────────────────────┘
2 rows in set. Elapsed: 0.002 sec.
开启三元运算符优化
SET optimize_if_chain_to_multiif = 1;
再次查看语法优化
superset-BI :) EXPLAIN SYNTAX SELECT number = 1 ? 'hello' : (number = 2 ? 'world' :
'fengzhen') FROM numbers(10);
EXPLAIN SYNTAX
SELECT if(number = 1, 'hello', if(number = 2, 'world', 'fengzhen'))
FROM numbers(10)
Query id: 7413fe4e-8af1-4720-9716-051278ce1e5c
┌─explain──────────────────────────────────────────────────────────────┐
│ SELECT multiIf(number = 1, 'hello', number = 2, 'world', 'fengzhen') │
│ FROM numbers(10) │
└──────────────────────────────────────────────────────────────────────┘
2 rows in set. Elapsed: 0.002 sec.
返回优化后的语句
superset-BI :) SELECT multiIf(number = 1, 'hello', number = 2, 'world', 'fengzhen') FROM numbers(10);
SELECT multiIf(number = 1, 'hello', number = 2, 'world', 'fengzhen')
FROM numbers(10)
Query id: d9af6deb-83fc-44ed-a0f3-b4cde9934605
┌─multiIf(equals(number, 1), 'hello', equals(number, 2), 'world', 'fengzhen')─┐
│ fengzhen │
│ hello │
│ world │
│ fengzhen │
│ fengzhen │
│ fengzhen │
│ fengzhen │
│ fengzhen │
│ fengzhen │
│ fengzhen │
└─────────────────────────────────────────────────────────────────────────────┘
10 rows in set. Elapsed: 0.002 sec. 4)查看 PIPELINE(执行过程)
date1002 :) EXPLAIN PIPELINE SELECT sum(number) FROM numbers_mt(100000) GROUP BY number % 20;
EXPLAIN PIPELINE
SELECT sum(number)
FROM numbers_mt(100000)
GROUP BY number % 20
Query id: b4b35074-d585-488c-9bf0-cd4065f4c007
┌─explain─────────────────────────┐
│ (Expression) │
│ ExpressionTransform │
│ (Aggregating) │
│ Resize 4 → 1 │
│ AggregatingTransform × 4 │
│ (Expression) │
│ ExpressionTransform × 4 │
│ (SettingQuotaAndLimits) │
│ (ReadFromStorage) │
│ NumbersMt × 4 0 → 1 │
└─────────────────────────────────┘
10 rows in set. Elapsed: 0.001 sec.
AggregatingTransform × 4:代表聚合4次，说明是四个线程执行任务，默认是16线程，如果小于16线程，则占满
打开其他参数
date1002 :) EXPLAIN PIPELINE header=1,graph=1 SELECT sum(number) FROM numbers_mt(10000) GROUP BY number%20;
EXPLAIN PIPELINE header = 1, graph = 1
SELECT sum(number)
FROM numbers_mt(10000)
GROUP BY number % 20
Query id: 3df2d884-0cb9-4f3d-8d9e-7353e0378338
┌─explain─────────────────────────────────────┐
│ digraph │
│ { │
│ rankdir="LR"; │
│ { node [shape = rect] │
│ n2 [label="Limit"]; │
│ n1 [label="Numbers"]; │
│ subgraph cluster_0 { │
│ label ="Expression"; │
│ style=filled; │
│ color=lightgrey; │
│ node [style=filled,color=white]; │
│ { rank = same; │
│ n3 [label="ExpressionTransform"]; │
│ } │
│ } │
│ subgraph cluster_1 { │
│ label ="Expression"; │
│ style=filled; │
│ color=lightgrey; │
│ node [style=filled,color=white]; │
│ { rank = same; │
│ n5 [label="ExpressionTransform"]; │
│ } │
│ } │
│ subgraph cluster_2 { │
│ label ="Aggregating"; │
│ style=filled; │
│ color=lightgrey; │
│ node [style=filled,color=white]; │
│ { rank = same; │
│ n4 [label="AggregatingTransform"]; │
│ } │
│ } │
│ } │
│ n2 -> n3 [label=" │
│ number UInt64 UInt64(size = 0)"]; │
│ n1 -> n2 [label=" │
│ number UInt64 UInt64(size = 0)"]; │
│ n3 -> n4 [label=" │
│ number UInt64 UInt64(size = 0) │
│ modulo(number, 20) UInt8 UInt8(size = 0)"]; │
│ n4 -> n5 [label=" │
│ modulo(number, 20) UInt8 UInt8(size = 0) │
│ sum(number) UInt64 UInt64(size = 0)"]; │
│ } │
└─────────────────────────────────────────────┘
45 rows in set. Elapsed: 0.001 sec.
上述比较有用的是：
syntax，因为可以优化语法
pipline，因为可以查看执行过程