Clickhouse如何分析sql查询计划完整指南
ClickHouse20.6之前目前并没有直接提供EXPLAIN查询，但是借助后台的服务日志，能变相实现该功能。
下面我们来看个例子：
clickhouse-client --send_logs_level=trace <<< 'select * from mytest.ReplacingMergeTreeTest final'
执行结果如下：
[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的执行计划。
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来查询执行计划。
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，紧凑打印打，默认开启。
1：简单查询：
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：打开全部的参数的执行计划
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 语法树
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执行
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执行计划如下：
clickhouse-client -h <host> --port <port> --password <pass> --send_logs_level=trace <<< "
// SQL statement here
" > /dev/null
2：新版本使用explain，语法参照如下：
EXPLAIN [AST | SYNTAX | PLAN | PIPELINE] [setting = value, ...]
SELECT ... [FORMAT ...]
本文参与 腾讯云自媒体同步曝光计划，分享自微信公众号。
原始发表：2022-03-13，如有侵权请联系 cloudcommunity@tencent.com 删除
评论
登录后参与评论
推荐阅读
腾讯云开发者
