ClickHouse-Explain执行计划
在 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，因为可以查看执行过程
