当前位置: 首页 > 工具软件 > clickhouse-go > 使用案例 >

ClickHouse-Explain执行计划

公孙宸
2023-12-01
 在 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,因为可以查看执行过程
 类似资料: