前往小程序,Get更优阅读体验!
立即前往
首页
学习
活动
专区
工具
TVP
发布
社区首页 >专栏 >Clickhouse如何分析sql查询计划完整指南

Clickhouse如何分析sql查询计划完整指南

作者头像
公众号-利志分享
发布2022-04-25 09:35:28
2K0
发布2022-04-25 09:35:28
举报
文章被收录于专栏:利志分享

ClickHouse20.6之前目前并没有直接提供EXPLAIN查询,但是借助后台的服务日志,能变相实现该功能。

下面我们来看个例子:

代码语言:javascript
复制
clickhouse-client --send_logs_level=trace <<< 'select * from mytest.ReplacingMergeTreeTest final'

执行结果如下:

代码语言:javascript
复制
[VM-16-6-centos] 2022.03.12 20:33:04.821144 [ 31454 ] {40c14902-c2d2-45da-817e-a15458d1ab71} <Debug> executeQuery: (from [::1]:48088) select * from mytest.ReplacingMergeTreeTest final 
[VM-16-6-centos] 2022.03.12 20:33:04.821466 [ 31454 ] {40c14902-c2d2-45da-817e-a15458d1ab71} <Trace> ContextAccess (default): Access granted: SELECT(id, age, name) ON mytest.ReplacingMergeTreeTest
[VM-16-6-centos] 2022.03.12 20:33:04.821533 [ 31454 ] {40c14902-c2d2-45da-817e-a15458d1ab71} <Trace> InterpreterSelectQuery: FetchColumns -> Complete
[VM-16-6-centos] 2022.03.12 20:33:04.821693 [ 31454 ] {40c14902-c2d2-45da-817e-a15458d1ab71} <Debug> mytest.ReplacingMergeTreeTest (3e79c511-efc8-4d21-a33c-6b9c2b55bced) (SelectExecutor): Key condition: unknown
[VM-16-6-centos] 2022.03.12 20:33:04.821765 [ 31454 ] {40c14902-c2d2-45da-817e-a15458d1ab71} <Debug> mytest.ReplacingMergeTreeTest (3e79c511-efc8-4d21-a33c-6b9c2b55bced) (SelectExecutor): MinMax index condition: unknown
[VM-16-6-centos] 2022.03.12 20:33:04.822054 [ 31454 ] {40c14902-c2d2-45da-817e-a15458d1ab71} <Debug> mytest.ReplacingMergeTreeTest (3e79c511-efc8-4d21-a33c-6b9c2b55bced) (SelectExecutor): Selected 6/6 parts by partition key, 6 parts by primary key, 6/6 marks by primary key, 6 marks to read from 6 ranges
[VM-16-6-centos] 2022.03.12 20:33:04.822157 [ 31454 ] {40c14902-c2d2-45da-817e-a15458d1ab71} <Trace> MergeTreeInOrderSelectProcessor: Reading 1 ranges in order from part 1_1_1_0, approx. 1 rows starting from 0
[VM-16-6-centos] 2022.03.12 20:33:04.822205 [ 31454 ] {40c14902-c2d2-45da-817e-a15458d1ab71} <Trace> MergeTreeInOrderSelectProcessor: Reading 1 ranges in order from part 1_2_2_0, approx. 1 rows starting from 0
[VM-16-6-centos] 2022.03.12 20:33:04.822274 [ 31454 ] {40c14902-c2d2-45da-817e-a15458d1ab71} <Trace> MergeTreeInOrderSelectProcessor: Reading 1 ranges in order from part 2_3_3_0, approx. 1 rows starting from 0
[VM-16-6-centos] 2022.03.12 20:33:04.822344 [ 31454 ] {40c14902-c2d2-45da-817e-a15458d1ab71} <Trace> MergeTreeInOrderSelectProcessor: Reading 1 ranges in order from part 3_4_4_0, approx. 1 rows starting from 0
[VM-16-6-centos] 2022.03.12 20:33:04.822381 [ 31454 ] {40c14902-c2d2-45da-817e-a15458d1ab71} <Trace> MergeTreeInOrderSelectProcessor: Reading 1 ranges in order from part 4_5_5_0, approx. 1 rows starting from 0
[VM-16-6-centos] 2022.03.12 20:33:04.822431 [ 31454 ] {40c14902-c2d2-45da-817e-a15458d1ab71} <Trace> MergeTreeInOrderSelectProcessor: Reading 1 ranges in order from part 5_6_6_0, approx. 1 rows starting from 0
1       25      张三
4       25      张三2
5       22      李四3
2       25      张三1
3       22      李四1
[VM-16-6-centos] 2022.03.12 20:33:04.824003 [ 31454 ] {40c14902-c2d2-45da-817e-a15458d1ab71} <Information> executeQuery: Read 6 rows, 106.00 B in 0.002814838 sec., 2131 rows/sec., 36.77 KiB/sec.
[VM-16-6-centos] 2022.03.12 20:33:04.824383 [ 31454 ] {40c14902-c2d2-45da-817e-a15458d1ab71} <Debug> MemoryTracker: Peak memory usage (for query): 188.84 KiB.

现在我们分析一下,从上述日志中能够得到什么信息。日志中打印了该SQL的执行计划。

代码语言:javascript
复制
Access granted:
表示授权。

Key condition: unknown
该查询语句没有使用主键索引

MinMax index condition: unknown
该查询语句没有使用分区索引

Selected 6/6 parts by partition key, 6 parts by primary key, 6/6 marks by primary key, 6 marks to read from 6 ranges
查询扫描6个分区目录,6个MarkRange

Read 6 rows, 106.00 B in 0.002814838 sec., 2131 rows/sec., 36.77 KiB/sec.
总共读6条数据,共106B

Peak memory usage (for query): 188.84 KiB.
该语句消耗内存最大为188,.84KB

Reading approx. 345563 rows with 8 streams
这一行表示需要扫描大约多少行数据,以及所使用的线程数。

从20.6开始可以通过explain来查询执行计划。

代码语言:javascript
复制
EXPLAIN [AST | SYNTAX | PLAN | PIPELINE] [setting = value, ...] 
SELECT ... [FORMAT ...]

PLAN:用于查看执行计划,默认值。

  1. header 打印计划中各个步骤的 head 说明,默认关闭,默认值 0;
  2. description 打印计划中各个步骤的描述,默认开启,默认值 1;
  3. actions 打印计划中各个步骤的详细信息,默认关闭,默认值 0。

AST :用于查看语法树;

SYNTAX:用于优化语法;

PIPELINE:用于查看 PIPELINE 计划。

  1. header 打印计划中各个步骤的 head说明,默认关闭;
  2. graph 用 DOT图形语言描述管道图,默认关闭,需要查看相关的图形需要配合graphviz 查看;
  3. actions 如果开启了,graph,紧凑打印打,默认开启。

1:简单查询:

代码语言:javascript
复制
EXPLAIN
SELECT *
FROM mytest.ReplacingMergeTreeTest
FINAL

Query id: fd89bfc1-a282-4163-b57a-43b263ce9fd2

┌─explain───────────────────────────────────────────────────────────────────┐
│ Expression ((Projection + Before ORDER BY))                               │
│   SettingQuotaAndLimits (Set limits and quota after reading from storage) │
│     ReadFromMergeTree                                                     │
└───────────────────────────────────────────────────────────────────────────┘

2:打开全部的参数的执行计划

代码语言:javascript
复制
EXPLAIN header = 1, actions = 1, description = 1
SELECT *
FROM mytest.ReplacingMergeTreeTest
FINAL

Query id: 2af33229-8002-4948-8894-72d7a57c2a0b

┌─explain───────────────────────────────────────────────────────────────────┐
│ Expression ((Projection + Before ORDER BY))                               │
│ Header: id UInt8                                                          │
│         age UInt8                                                         │
│         name String                                                       │
│ Actions: INPUT :: 0 -> id UInt8 : 0                                       │
│          INPUT :: 1 -> age UInt8 : 1                                      │
│          INPUT :: 2 -> name String : 2                                    │
│ Positions: 0 1 2                                                          │
│   SettingQuotaAndLimits (Set limits and quota after reading from storage) │
│   Header: id UInt8                                                        │
│           age UInt8                                                       │
│           name String                                                     │
│     ReadFromMergeTree                                                     │
│     Header: id UInt8                                                      │
│             age UInt8                                                     │
│             name String                                                   │
│     ReadType: Default                                                     │
│     Parts: 5                                                              │
│     Granules: 5                                                           │
└───────────────────────────────────────────────────────────────────────────┘

3:AST 语法树

代码语言:javascript
复制
EXPLAIN AST
SELECT *
FROM mytest.ReplacingMergeTreeTest
FINAL

Query id: 927e309b-9dfc-4da1-874a-1b6a91184319

┌─explain─────────────────────────────────────────────┐
│ SelectWithUnionQuery (children 1)                   │
│  ExpressionList (children 1)                        │
│   SelectQuery (children 2)                          │
│    ExpressionList (children 1)                      │
│     Asterisk                                        │
│    TablesInSelectQuery (children 1)                 │
│     TablesInSelectQueryElement (children 1)         │
│      TableExpression (children 1)                   │
│       TableIdentifier mytest.ReplacingMergeTreeTest │
└─────────────────────────────────────────────────────┘

4:pipeline执行

代码语言:javascript
复制
EXPLAIN PIPELINE header = 1, graph = 1
SELECT *
FROM mytest.ReplacingMergeTreeTest
FINAL

Query id: d193fe2b-cf9c-42cb-899f-7475dc52ad7f

┌─explain───────────────────────────────────────┐
│ digraph                                       │
│ {                                             │
│   rankdir="LR";                               │
│   { node [shape = rect]                       │
│         n3 [label="AddingSelector × 5"];      │
│         n4 [label="Copy × 5"];                │
│         n2 [label="ExpressionTransform × 7"]; │
│         n1 [label="MergeTreeInOrder × 5"];    │
│         n5 [label="ReplacingSorted × 2"];     │
│     subgraph cluster_0 {                      │
│       label ="Expression";                    │
│       style=filled;                           │
│       color=lightgrey;                        │
│       node [style=filled,color=white];        │
│       { rank = same;                          │
│         n6 [label="ExpressionTransform × 2"]; │
│       }                                       │
│     }                                         │
│   }                                           │
│   n3 -> n4 [label="× 5                        │
│ id UInt8 UInt8(size = 0)                      │
│ age UInt8 UInt8(size = 0)                     │
│ name String String(size = 0)"];               │
│   n4 -> n5 [label="× 10                       │
│ id UInt8 UInt8(size = 0)                      │
│ age UInt8 UInt8(size = 0)                     │
│ name String String(size = 0)"];               │
│   n2 -> n3 [label="× 5                        │
│ id UInt8 UInt8(size = 0)                      │
│ age UInt8 UInt8(size = 0)                     │
│ name String String(size = 0)"];               │
│   n2 -> n6 [label="× 2                        │
│ id UInt8 UInt8(size = 0)                      │
│ age UInt8 UInt8(size = 0)                     │
│ name String String(size = 0)"];               │
│   n1 -> n2 [label="× 5                        │
│ age UInt8 UInt8(size = 0)                     │
│ id UInt8 UInt8(size = 0)                      │
│ name String String(size = 0)"];               │
│   n5 -> n2 [label="× 2                        │
│ id UInt8 UInt8(size = 0)                      │
│ age UInt8 UInt8(size = 0)                     │
│ name String String(size = 0)"];               │
│ }                                             │
└───────────────────────────────────────────────┘

总结:

1:老版本sql执行计划如下:

代码语言:javascript
复制
clickhouse-client -h <host> --port <port> --password <pass> --send_logs_level=trace <<< "
// SQL statement here
" > /dev/null

2:新版本使用explain,语法参照如下:

代码语言:javascript
复制
EXPLAIN [AST | SYNTAX | PLAN | PIPELINE] [setting = value, ...] 
SELECT ... [FORMAT ...]
本文参与 腾讯云自媒体同步曝光计划,分享自微信公众号。
原始发表:2022-03-13,如有侵权请联系 cloudcommunity@tencent.com 删除

本文分享自 利志分享 微信公众号,前往查看

如有侵权,请联系 cloudcommunity@tencent.com 删除。

本文参与 腾讯云自媒体同步曝光计划  ,欢迎热爱写作的你一起参与!

评论
登录后参与评论
0 条评论
热度
最新
推荐阅读
领券
问题归档专栏文章快讯文章归档关键词归档开发者手册归档开发者手册 Section 归档