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，因为可以查看执行过程

posted on 2022-04-20 21:59 嘣嘣嚓阅读(459) 评论(0) 编辑收藏举报

刷新页面返回顶部

登录后才能查看或发表评论，立即登录或者逛逛博客园首页

相关博文：

· ClickHouse-语法优化规则

· ClickHouse-SQL语法

· ClickHouse 执行计划查看

· ClickHouse高级

· MySQL执行计划解读

阅读排行：
· 分享4款.NET开源、免费、实用的商城系统
· 全程不用写代码，我用AI程序员写了一个飞机大战
· MongoDB 8.0这个新功能碉堡了，比商业数据库还牛
· 白话解读 Dapr 1.15：你的「微服务管家」又秀新绝活了
· 记一次.NET内存居高不下排查解决与启示

历史上的今天：
2020-04-20 Kafka-处理请求（生产请求、获取请求）

嘣嘣嚓