ClickHouse-使用Explain 查看执行计划

Explain 查看执行计划

在 clickhouse 20.6 版本之前要查看 SQL 语句的执行计划需要设置日志级别为 trace 才能可以看到,并且只能真正执行 sql,在执行日志里面查看。在 20.6 版本引入了原生的执行计划的语法。在 20.6.3 版本成为正式版本的功能。

本文档基于目前较新稳定版 21.7.3.14。

基本语法

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 还可以进行额外的显示设置,如上参数所示。

案例实操

新版本使用 EXPLAIN

可以再安装一个 20.6 以上版本,或者直接在官网的在线 demo,选择高版本进行测试。

官网在线测试链接:https://play.clickhouse.tech/?file=welcome

1)查看 PLAIN

// 简单查询
explain plan select arrayJoin([1,2,3,null,null]);
┌─explain───────────────────────────────────────────────────────────────────┐
│ Expression ((Projection + Before ORDER BY))                               │
│   SettingQuotaAndLimits (Set limits and quota after reading from storage) │
│     ReadFromStorage (SystemOne)                                           │
└───────────────────────────────────────────────────────────────────────────┘


// 复杂 SQL 的执行计划
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 header=1, actions=1, description=1 SELECT number from system.numbers limit 10;
┌─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)                                             │
│     Header: number UInt64                                                 │
│     Limit 10                                                              │
│     Offset 0                                                              │
│       ReadFromStorage (SystemNumbers)                                     │
│       Header: number UInt64                                               │
└───────────────────────────────────────────────────────────────────────────┘

复杂 SQL 的执行计划执行结果：

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-rAjUWh8E-1639105384920)(/home/lxj/.config/Typora/typora-user-images/1639041086092.png)]

2)AST 语法树

EXPLAIN AST SELECT number from system.numbers limit 10;

┌─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                        │
└─────────────────────────────────────────────┘

3)SYNTAX 语法优化

// 先做一次查询
SELECT number = 1 ? 'hello' : (number = 2 ? 'world' : 'test') FROM numbers(10);
┌─if(equals(number, 1), 'hello', if(equals(number, 2), 'world', 'test'))─┐
│ test                                                                   │
│ hello                                                                  │
│ world                                                                  │
│ test                                                                   │
│ test                                                                   │
│ test                                                                   │
│ test                                                                   │
│ test                                                                   │
│ test                                                                   │
│ test                                                                   │
└────────────────────────────────────────────────────────────────────────┘

// 查看语法优化
EXPLAIN SYNTAX SELECT number = 1 ? 'hello' : (number = 2 ? 'world' : 'test') FROM numbers(10);
┌─explain─────────────────────────────────────────────────────────┐
│ SELECT if(number = 1, 'hello', if(number = 2, 'world', 'test')) │
│ FROM numbers(10)                                                │
└─────────────────────────────────────────────────────────────────┘

//开启三元运算符优化
SET optimize_if_chain_to_multiif = 1;

//再次查看语法优化
EXPLAIN SYNTAX SELECT number = 1 ? 'hello' : (number = 2 ? 'world' : 'test') FROM numbers(10);
┌─explain──────────────────────────────────────────────────────────┐
│ SELECT multiIf(number = 1, 'hello', number = 2, 'world', 'test') │
│ FROM numbers(10)                                                 │
└──────────────────────────────────────────────────────────────────┘

4)查看 PIPELINE

EXPLAIN PIPELINE SELECT sum(number) FROM numbers_mt(100000) GROUP BY number % 20;
┌─explain─────────────────────────┐
│ (Expression)                    │
│ ExpressionTransform             │
│   (Aggregating)                 │
│   Resize 2 → 1                  │
│     AggregatingTransform × 2    │
│       (Expression)              │
│       ExpressionTransform × 2   │
│         (SettingQuotaAndLimits) │
│           (ReadFromStorage)     │
│           NumbersMt × 2 0 → 1   │
└─────────────────────────────────┘
// 以上的x2是clickhouse使用的服务器cpu资源

// 打开其他参数
EXPLAIN PIPELINE header=1,graph=1 SELECT sum(number) FROM numbers_mt(10000) GROUP BY number%20;
┌─explain─────────────────────────────────────┐
│ digraph                                     │
│ {                                           │
│   rankdir="LR";                             │
│   { node [shape = box]                      │
│         n2 [label="Limit"];                 │
│         n1 [label="Numbers"];               │
│     subgraph cluster_0 {                    │
│       label ="Expression";                  │
│       style=filled;                         │
│       color=lightgrey;                      │
│       node [style=filled,color=white];      │
│       { rank = same;                        │
│         n5 [label="ExpressionTransform"];   │
│       }                                     │
│     }                                       │
│     subgraph cluster_1 {                    │
│       label ="Expression";                  │
│       style=filled;                         │
│       color=lightgrey;                      │
│       node [style=filled,color=white];      │
│       { rank = same;                        │
│         n3 [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)"];      │
│ }                                           │
└─────────────────────────────────────────────┘

老版本查看执行计划

clickhouse-client -h 主机名 --send_logs_level=trace <<< "sql" > /dev/null

其中,send_logs_level 参数指定日志等级为 trace,<<<将 SQL 语句重定向至 clickhouse-client 进行查询,> /dev/null 将查询结果重定向到空设备吞掉,以便观察日志。

注意:

1、通过将 ClickHouse 的服务日志,设置到 DEBUG 或者 TRACE 级别,才可以变相实现EXPLAIN 查询的作用。

2、需要真正的执行 SQL 查询,CH 才能打印计划日志,所以如果表的数据量很大,最好借助 LIMIT 子句,减小查询返回的数据量。