ClickHouse 使用EXPLAIN 分析 SQL 执行计划

ClickHouse 使用EXPLAIN 分析 SQL 执行计划

1.1. 使用 EXPLAIN 分析 SQL 执行计划

本节介绍如何使用EXPLAIN命令分析SQL语句的执行计划。

1.1.1. EXPLAIN概述

执行计划是进行SQL查询调优的重要参考。在ClickHouse 中，可以使用EXPLAIN语句查看SQL查询的执行计划。EXPLAIN 的语法如下：

EXPLAIN [AST | SYNTAX | PLAN | PIPELINE | TABLE OVERRIDE][setting = value, ...][SELECT ... | tableFunction(...) [COLUMNS (...)][ORDER BY ...][PARTITION BY ...][PRIMARY KEY][SAMPLE BY ...][TTL ...]][FORMAT ...]

例如，执行 EXPLAIN 查看如下 SQL 的执行计划：

EXPLAINSELECT sum(number)FROM numbers(10)UNION ALLSELECT sum(number)FROM numbers(10)ORDER BY sum(number) DESCFORMAT TSV Query id: 6c7d7f0f-d875-4e55-b557-0088c3c6b563 Union Expression ((Projection + Before ORDER BY)) Aggregating Expression (Before GROUP BY) SettingQuotaAndLimits (Set limits and quota after reading from storage) ReadFromStorage (SystemNumbers) Expression (Projection) Sorting (Sorting for ORDER BY) Expression (Before ORDER BY) Aggregating Expression (Before GROUP BY) SettingQuotaAndLimits (Set limits and quota after reading from storage) ReadFromStorage (SystemNumbers) 13 rows in set. Elapsed: 0.002 sec.

还可以可视化查看 SQL 执行管道流水线 Pipeline，如下图（在后面的章节中，我们会介绍如何生成这样的图）：

1.1.2. EXPLAIN类型

EXPLAIN 常用语句类型如下：

1. EXPLAIN AST：用于查看抽象语法树（Abstract Syntax Tree）。支持查看所有类型的语句，不光是SELECT语句。

2. EXPLAIN SYNTAX：查询经过AST层优化后的 SQL 语句；

3. EXPLAIN PLAN：用于查看执行计划（Execution Plan），等价于直接使用 EXPLAIN。可以指定五个参数；

①　header：是否打印计划中各个步骤的 header 说明。1：开启，0：关闭（默认值）；

②　description：是否打印计划中各个步骤的描述。1：开启（默认值），0：关闭；

③　indexes：是否显示索引使用情况。1：开启，0：关闭（默认值）；

④　actions：是否打印计划中各个步骤的详细信息。1：开启，0：关闭（默认值）；

⑤　json：是否以 JSON 格式打印执行计划的详细信息。1：开启，0：关闭（默认值）；

4. EXPLAIN PIPELINE：用于查看 Pipeline 计划，可以指定三个参数；

①　header：是否打印计划中各个步骤的 header 说明。1：开启，0：关闭（默认值）；

②　graph：是否用DOT纯文本图形语言描述管道图。1：开启，0：关闭（默认值）；

③　compact：如果开启了 graph，紧凑打印行开关。1：开启（默认值），0：关闭；

5. EXPLAIN ESTIMATE：显示在处理查询时要从表中读取的行、标记和部分的估计数量。适用于 MergeTree系列表引擎。

1.1.3. EXPLAIN AST

查看查询的抽象语法树（AST）。

一个最简单的 SELECT 查询的AST

EXPLAIN ASTSELECT 1 Query id: 6823b212-e89e-40ef-a6a1-2dc36ca7b1f6 ┌─explain───────────────────────────┐│ SelectWithUnionQuery (children 1) ││ ExpressionList (children 1) ││ SelectQuery (children 1) ││ ExpressionList (children 1) ││ Literal UInt64_1 │└───────────────────────────────────┘ 5 rows in set. Elapsed: 0.002 sec.

一个ALTER 查询的AST

EXPLAIN ASTALTER TABLE tutorial.hits_v1 DELETE WHERE EventDate = toDate('2014-03-17') Query id: b2452669-68b6-4e5d-8b1e-1a42652f8343 ┌─explain──────────────────────────────────┐│ AlterQuery tutorial hits_v1 (children 3) ││ ExpressionList (children 1) ││ AlterCommand DELETE (children 1) ││ Function equals (children 1) ││ ExpressionList (children 2) ││ Identifier EventDate ││ Function toDate (children 1) ││ ExpressionList (children 1) ││ Literal '2014-03-17' ││ Identifier tutorial ││ Identifier hits_v1 │└──────────────────────────────────────────┘ 11 rows in set. Elapsed: 0.001 sec.

一个带聚合计算的查询 AST

EXPLAIN ASTSELECT uniqExact(UserID) AS cnt, sum(RequestNum) / cnt AS rpu, EventDateFROM tutorial.hits_v1 AS DELETEWHERE JavaEnable = 1GROUP BY EventDateORDER BY rpu DESCLIMIT 3 Query id: 60c3c601-cc57-4a56-bf71-bed4cc7c56e5 ┌─explain───────────────────────────────────────────────┐│ SelectWithUnionQuery (children 1) ││ ExpressionList (children 1) ││ SelectQuery (children 6) ││ ExpressionList (children 3) ││ Function uniqExact (alias cnt) (children 1) ││ ExpressionList (children 1) ││ Identifier UserID ││ Function divide (alias rpu) (children 1) ││ ExpressionList (children 2) ││ Function sum (children 1) ││ ExpressionList (children 1) ││ Identifier RequestNum ││ Identifier cnt ││ Identifier EventDate ││ TablesInSelectQuery (children 1) ││ TablesInSelectQueryElement (children 1) ││ TableExpression (children 1) ││ TableIdentifier tutorial.hits_v1 (alias DELETE) ││ Function equals (children 1) ││ ExpressionList (children 2) ││ Identifier JavaEnable ││ Literal UInt64_1 ││ ExpressionList (children 1) ││ Identifier EventDate ││ ExpressionList (children 1) ││ OrderByElement (children 1) ││ Identifier rpu ││ Literal UInt64_3 │└───────────────────────────────────────────────────────┘ 28 rows in set. Elapsed: 0.002 sec.

JOIN嵌套子查询的 AST

EXPLAIN ASTSELECT count()FROM( SELECT UserID FROM tutorial.hits_v1 PREWHERE EventDate = '2014-03-18') AS t1INNER JOIN( SELECT UserID FROM tutorial.hits_v1 PREWHERE EventDate = '2014-03-19') AS t2 ON t1.UserID = t2.UserID Query id: c42553b2-77fc-4657-b83c-9fc767e58b8c ┌─explain────────────────────────────────────────────┐│ SelectWithUnionQuery (children 1) ││ ExpressionList (children 1) ││ SelectQuery (children 2) ││ ExpressionList (children 1) ││ Function count (children 1) ││ ExpressionList ││ TablesInSelectQuery (children 2) ││ TablesInSelectQueryElement (children 1) ││ TableExpression (children 1) ││ Subquery (alias t1) (children 1) ││ SelectWithUnionQuery (children 1) ││ ExpressionList (children 1) ││ SelectQuery (children 3) ││ ExpressionList (children 1) ││ Identifier UserID ││ TablesInSelectQuery (children 1) ││ TablesInSelectQueryElement (children 1) ││ TableExpression (children 1) ││ TableIdentifier tutorial.hits_v1 ││ Function equals (children 1) ││ ExpressionList (children 2) ││ Identifier EventDate ││ Literal '2014-03-18' ││ TablesInSelectQueryElement (children 2) ││ TableExpression (children 1) ││ Subquery (alias t2) (children 1) ││ SelectWithUnionQuery (children 1) ││ ExpressionList (children 1) ││ SelectQuery (children 3) ││ ExpressionList (children 1) ││ Identifier UserID ││ TablesInSelectQuery (children 1) ││ TablesInSelectQueryElement (children 1) ││ TableExpression (children 1) ││ TableIdentifier tutorial.hits_v1 ││ Function equals (children 1) ││ ExpressionList (children 2) ││ Identifier EventDate ││ Literal '2014-03-19' ││ TableJoin (children 1) ││ Function equals (children 1) ││ ExpressionList (children 2) ││ Identifier t1.UserID ││ Identifier t2.UserID │└────────────────────────────────────────────────────┘ 44 rows in set. Elapsed: 0.001 sec.

IN嵌套子查询 AST

EXPLAIN ASTSELECT count()FROM( SELECT UserID FROM tutorial.hits_v1 PREWHERE (EventDate = '2014-03-18') AND (UserID IN ( SELECT UserID FROM tutorial.hits_v1 PREWHERE EventDate = '2014-03-19' ))) Query id: 5885eb3a-ddea-4cf4-a5cf-0bc38c02a02c ┌─explain────────────────────────────────────────────────────┐│ SelectWithUnionQuery (children 1) ││ ExpressionList (children 1) ││ SelectQuery (children 2) ││ ExpressionList (children 1) ││ Function count (children 1) ││ ExpressionList ││ TablesInSelectQuery (children 1) ││ TablesInSelectQueryElement (children 1) ││ TableExpression (children 1) ││ Subquery (children 1) ││ SelectWithUnionQuery (children 1) ││ ExpressionList (children 1) ││ SelectQuery (children 3) ││ ExpressionList (children 1) ││ Identifier UserID ││ TablesInSelectQuery (children 1) ││ TablesInSelectQueryElement (children 1) ││ TableExpression (children 1) ││ TableIdentifier tutorial.hits_v1 ││ Function and (children 1) ││ ExpressionList (children 2) ││ Function equals (children 1) ││ ExpressionList (children 2) ││ Identifier EventDate ││ Literal '2014-03-18' ││ Function in (children 1) ││ ExpressionList (children 2) ││ Identifier UserID ││ Subquery (children 1) ││ SelectWithUnionQuery (children 1) ││ ExpressionList (children 1) ││ SelectQuery (children 3) ││ ExpressionList (children 1) ││ Identifier UserID ││ TablesInSelectQuery (children 1) ││ TablesInSelectQueryElement (children 1) ││ TableExpression (children 1) ││ TableIdentifier tutorial.hits_v1 ││ Function equals (children 1) ││ ExpressionList (children 2) ││ Identifier EventDate ││ Literal '2014-03-19' │└────────────────────────────────────────────────────────────┘ 42 rows in set. Elapsed: 0.001 sec.

1.1.4. EXPLAIN SYNTAX

查看 AST语法优化后的查询SQL。

自动优化PREWHERE

一个WHERE 自动优化成PREWHERE例子 ：

EXPLAIN SYNTAXSELECT UserID, WatchIDFROM tutorial.hits_v1WHERE JavaEnable = 1 Query id: 81dd2b32-8256-4afe-b65d-8017c15daa8a ┌─explain─────────────────┐│ SELECT ││ UserID, ││ WatchID ││ FROM tutorial.hits_v1 ││ PREWHERE JavaEnable = 1 │└─────────────────────────┘ 5 rows in set. Elapsed: 0.002 sec.

自动谓词下推优化

SQL 里面的过滤条件：

WHERE (JavaEnable = 1) AND (EventDate = '2014-03-18')

自动谓词下推优化成了分区EventDate的过滤条件在前面

PREWHERE (EventDate = '2014-03-18') AND (JavaEnable = 1)

执行EXPLAIN SYNTAX如下：

EXPLAIN SYNTAXSELECT count(UserID) AS cntFROM( SELECT UserID, WatchID FROM tutorial.hits_v1 WHERE (JavaEnable = 1) AND (EventDate = '2014-03-18')) AS t1INNER JOIN( SELECT UserID, WatchID FROM tutorial.hits_v1 WHERE (EventDate = '2014-03-19') AND (JavaEnable = 1)) AS t2 ON t1.UserID = t2.UserID Query id: b3b1cc3e-61b8-4ae7-9130-bc1f82fbdb45 ┌─explain──────────────────────────────────────────────────────┐│ SELECT count(UserID) AS cnt ││ FROM ││ ( ││ SELECT ││ UserID, ││ WatchID ││ FROM tutorial.hits_v1 ││ PREWHERE (EventDate = '2014-03-18') AND (JavaEnable = 1) ││ ) AS t1 ││ ALL INNER JOIN ││ ( ││ SELECT UserID ││ FROM tutorial.hits_v1 ││ PREWHERE EventDate = '2014-03-19' ││ WHERE (EventDate = '2014-03-19') AND (JavaEnable = 1) ││ ) AS t2 ON UserID = t2.UserID │└──────────────────────────────────────────────────────────────┘ 16 rows in set. Elapsed: 0.006 sec.

1.1.5. EXPLAIN PLAN

查看 SQL 执行计划。可以配置参数header、indexes、actions、json、description等。

一个最简单的SQL的执行计划

EXPLAIN header = 1, indexes = 1, actions = 1, json = 0, description = 0SELECT 1 Query id: d41efec8-c69e-41bd-bb44-0e2b6781b2f2 ┌─explain─────────────────────────────────────┐│ Expression ││ Header: 1 UInt8 ││ Actions: COLUMN Const(UInt8) -> 1 UInt8 : 0 ││ Positions: 0 ││ SettingQuotaAndLimits ││ Header: dummy UInt8 ││ ReadFromStorage ││ Header: dummy UInt8 │└─────────────────────────────────────────────┘ 8 rows in set. Elapsed: 0.001 sec.

一个简单SQL查询执行计划

EXPLAIN header = 1, indexes = 1, actions = 1, json = 0, description = 0SELECT UserID, WatchIDFROM tutorial.hits_v1WHERE JavaEnable = 1 Query id: 1c3b84e0-87c0-44ef-9736-fcd203d1f9a5 ┌─explain───────────────────────────────────┐│ Expression ││ Header: UserID UInt64 ││ WatchID UInt64 ││ Actions: INPUT :: 0 -> WatchID UInt64 : 0 ││ INPUT :: 1 -> UserID UInt64 : 1 ││ Positions: 1 0 ││ SettingQuotaAndLimits ││ Header: WatchID UInt64 ││ UserID UInt64 ││ ReadFromMergeTree ││ Header: WatchID UInt64 ││ UserID UInt64 ││ ReadType: Default ││ Parts: 1 ││ Granules: 1084 ││ Indexes: ││ MinMax ││ Condition: true ││ Parts: 1/1 ││ Granules: 1084/1084 ││ Partition ││ Condition: true ││ Parts: 1/1 ││ Granules: 1084/1084 ││ PrimaryKey ││ Condition: true ││ Parts: 1/1 ││ Granules: 1084/1084 │└───────────────────────────────────────────┘ 28 rows in set. Elapsed: 0.003 sec.

一个复杂嵌套子查询JOIN的执行计划

EXPLAIN header = 1, indexes = 1, actions = 1, json = 0, description = 0SELECT count(UserID) AS cntFROM( SELECT UserID, WatchID FROM tutorial.hits_v1 WHERE (JavaEnable = 1) AND (EventDate = '2014-03-18')) AS t1INNER JOIN( SELECT UserID, WatchID FROM tutorial.hits_v1 WHERE (EventDate = '2014-03-19') AND (JavaEnable = 1)) AS t2 ON t1.UserID = t2.UserIDFORMAT TSV Query id: 10bcac80-8e24-4c84-99f4-31a81961e978 ExpressionHeader: cnt UInt64Actions: INPUT : 0 -> count(UserID) UInt64 : 0 ALIAS count(UserID) :: 0 -> cnt UInt64 : 1Positions: 1 Aggregating Header: count(UserID) UInt64 Keys: Aggregates: count(UserID) Function: count(UInt64) → UInt64 Arguments: UserID Argument positions: 0 Expression Header: UserID UInt64 Actions: INPUT :: 0 -> UserID UInt64 : 0 Positions: 0 Join Header: UserID UInt64 Expression Header: UserID UInt64 Actions: INPUT :: 0 -> UserID UInt64 : 0 Positions: 0 Filter Header: UserID UInt64 Filter column: and(equals(EventDate, \'2014-03-18\'), equals(JavaEnable, 1)) (removed) Actions: INPUT : 0 -> equals(EventDate, \'2014-03-18\') UInt8 : 0 INPUT : 1 -> JavaEnable UInt8 : 1 INPUT :: 2 -> UserID UInt64 : 2 COLUMN Const(UInt8) -> 1 UInt8 : 3 FUNCTION equals(JavaEnable :: 1, 1 :: 3) -> equals(JavaEnable, 1) UInt8 : 4 FUNCTION and(equals(EventDate, \'2014-03-18\') :: 0, equals(JavaEnable, 1) :: 4) -> and(equals(EventDate, \'2014-03-18\'), equals(JavaEnable, 1)) UInt8 : 3 Positions: 2 3 SettingQuotaAndLimits Header: equals(EventDate, \'2014-03-18\') UInt8 JavaEnable UInt8 UserID UInt64 ReadFromMergeTree Header: equals(EventDate, \'2014-03-18\') UInt8 JavaEnable UInt8 UserID UInt64 ReadType: Default Parts: 1 Granules: 792 Indexes: MinMax Keys: EventDate Condition: and((EventDate in [16147, 16147]), (EventDate in [16147, 16147])) Parts: 1/1 Granules: 1084/1084 Partition Keys: toYYYYMM(EventDate) Condition: and((toYYYYMM(EventDate) in [201403, 201403]), (toYYYYMM(EventDate) in [201403, 201403])) Parts: 1/1 Granules: 1084/1084 PrimaryKey Keys: EventDate Condition: and((EventDate in [16147, 16147]), (EventDate in [16147, 16147])) Parts: 1/1 Granules: 792/1084 Expression Header: t2.UserID UInt64 Actions: INPUT : 0 -> UserID UInt64 : 0 ALIAS UserID :: 0 -> t2.UserID UInt64 : 1 Positions: 1 Filter Header: UserID UInt64 Filter column: and(equals(EventDate, \'2014-03-19\'), equals(JavaEnable, 1)) (removed) Actions: INPUT : 0 -> equals(EventDate, \'2014-03-19\') UInt8 : 0 INPUT : 1 -> JavaEnable UInt8 : 1 INPUT :: 2 -> UserID UInt64 : 2 COLUMN Const(UInt8) -> 1 UInt8 : 3 FUNCTION equals(JavaEnable :: 1, 1 :: 3) -> equals(JavaEnable, 1) UInt8 : 4 FUNCTION and(equals(EventDate, \'2014-03-19\') :: 0, equals(JavaEnable, 1) :: 4) -> and(equals(EventDate, \'2014-03-19\'), equals(JavaEnable, 1)) UInt8 : 3 Positions: 2 3 SettingQuotaAndLimits Header: equals(EventDate, \'2014-03-19\') UInt8 JavaEnable UInt8 UserID UInt64 ReadFromMergeTree Header: equals(EventDate, \'2014-03-19\') UInt8 JavaEnable UInt8 UserID UInt64 ReadType: Default Parts: 1 Granules: 807 Indexes: MinMax Keys: EventDate Condition: and((EventDate in [16148, 16148]), (EventDate in [16148, 16148])) Parts: 1/1 Granules: 1084/1084 Partition Keys: toYYYYMM(EventDate) Condition: and((toYYYYMM(EventDate) in [201403, 201403]), (toYYYYMM(EventDate) in [201403, 201403])) Parts: 1/1 Granules: 1084/1084 PrimaryKey Keys: EventDate Condition: and((EventDate in [16148, 16148]), (EventDate in [16148, 16148])) Parts: 1/1 Granules: 807/1084 108 rows in set. Elapsed: 0.012 sec.

1.1.6. EXPLAIN PIPELINE

显示查询Pipeline管道流水线图。可以指定header、graph、compact等参数。

通过 graph=1输出DOT 纯文本图形描述语言。如果安装了 graphviz dot 命令行环境，就可以把*.dot 纯文本文件生成图片，然后可视化查看SQL查询管道流水线。例如下面的 SQL

EXPLAIN PIPELINE header = 1, graph = 1, compact = 1SELECT count(UserID) AS cntFROM( SELECT UserID, WatchID FROM tutorial.hits_v1 WHERE (JavaEnable = 1) AND (EventDate = '2014-03-18')) AS t1INNER JOIN( SELECT UserID, WatchID FROM tutorial.hits_v1 WHERE (EventDate = '2014-03-19') AND (JavaEnable = 1)) AS t2 ON t1.UserID = t2.UserIDFORMAT TSV Query id: 8eb30d70-ed05-42df-8c50-aee146227e45 digraph{ rankdir="LR"; { node [shape = rect] n8 [label="FillingRightJoinSide"]; n4 [label="JoiningTransform × 6"]; n1 [label="MergeTreeThread × 12"]; n7 [label="Resize × 2"]; subgraph cluster_0 { label ="Aggregating"; style=filled; color=lightgrey; node [style=filled,color=white]; { rank = same; n11 [label="AggregatingTransform × 6"]; n12 [label="Resize"]; n10 [label="StrictResize"]; } } subgraph cluster_1 { label ="Filter"; style=filled; color=lightgrey; node [style=filled,color=white]; { rank = same; n2 [label="FilterTransform × 6"]; } } subgraph cluster_2 { label ="Expression"; style=filled; color=lightgrey; node [style=filled,color=white]; { rank = same; n3 [label="ExpressionTransform × 6"]; } } subgraph cluster_3 { label ="Expression"; style=filled; color=lightgrey; node [style=filled,color=white]; { rank = same; n13 [label="ExpressionTransform"]; } } subgraph cluster_4 { label ="Expression"; style=filled; color=lightgrey; node [style=filled,color=white]; { rank = same; n9 [label="ExpressionTransform × 6"]; } } subgraph cluster_5 { label ="Expression"; style=filled; color=lightgrey; node [style=filled,color=white]; { rank = same; n6 [label="ExpressionTransform × 6"]; } } subgraph cluster_6 { label ="Filter"; style=filled; color=lightgrey; node [style=filled,color=white]; { rank = same; n5 [label="FilterTransform × 6"]; } } } n8 -> n7 [label=""]; n4 -> n9 [label="× 6UserID UInt64 UInt64(size = 0)"]; n1 -> n2 [label="× 6equals(EventDate, \'2014-03-18\') UInt8 UInt8(size = 0)JavaEnable UInt8 UInt8(size = 0)UserID UInt64 UInt64(size = 0)"]; n1 -> n5 [label="× 6equals(EventDate, \'2014-03-19\') UInt8 UInt8(size = 0)JavaEnable UInt8 UInt8(size = 0)UserID UInt64 UInt64(size = 0)"]; n7 -> n4 [label="× 6"]; n7 -> n8 [label="t2.UserID UInt64 UInt64(size = 0)"]; n11 -> n12 [label="× 6count(UserID) UInt64 UInt64(size = 0)"]; n12 -> n13 [label="count(UserID) UInt64 UInt64(size = 0)"]; n10 -> n11 [label="× 6UserID UInt64 UInt64(size = 0)"]; n2 -> n3 [label="× 6UserID UInt64 UInt64(size = 0)"]; n3 -> n4 [label="× 6UserID UInt64 UInt64(size = 0)"]; n9 -> n10 [label="× 6UserID UInt64 UInt64(size = 0)"]; n6 -> n7 [label="× 6t2.UserID UInt64 UInt64(size = 0)"]; n5 -> n6 [label="× 6UserID UInt64 UInt64(size = 0)"];} 105 rows in set. Elapsed: 0.011 sec.

把上面的DOT 图形描述语言文本digraph{...} ，保存一个文本文件 a.dot中，然后执行命令（需要安装 graphviz环境）：

dot -Tpng a.dot -o a.png

渲染出来a.png 图片就是SQL查询管道图，图片打开如下：

为了方便展示，这里把布局改成从上到下布局：rankdir="TD"。

Ø Tips：DOT纯文本图形描述语言

DOT 是一种纯文本图形描述语言。这是一种描述人类和计算机程序都可以阅读的图形的简单方法。

环境安装

graphviz dot 使用步骤

安装graphviz

Mac OS X系统上安装Graphviz可以使用MacPorts 或Homebrew 。

MacPorts 安装Graphviz

sudo port install graphviz

MacPorts提供了Graphviz 和 Mac GUI Graphviz.app的稳定版本和开发版本。这些可以通过port graphviz、 graphviz-devel、 graphviz-gui 和graphviz-gui-devel 获得。

Homebrew 安装Graphviz

brew install graphviz

如果遇到 Error 报错（通常可能是环境、网络、IO等问题），一般重试几次就会成功。

在其他操作系统（Linux、Windows 等）上安装Graphviz ，请参考： 编写a.dot脚本。

关于 dot 语法，请参考：-Tpng a.dot -o a.png

其中，a 是文件名。这样就可以用脚本渲染出你要绘制的图片了。

简单图形

graph graphname {rankdir = TD;a -- b; b -- c; b -- d; d -- a;}

图形布局

graph graphname { rankdir = LR; //LR: Rank Direction Left to Right; TD: Top to Down a -- b; b -- c; b -- d; d -- a;}

其中，

rankdir = LR: 左右布局 (Rank Direction Left to Right)

rankdir = TD: 上下布局 (Rank Direction Top to Down)

简单有向图 (Directional Graph)

digraph graphname{ rankdir = LR; a -> b; b -> c; a -> c;}

添加标记说明

digraph graphname { rankdir = TD; T [label = "Teacher"] // node T P [label = "Pupil"] // node P T -> P [label = "Instructions", fontcolor = darkgreen] // edge T->P}

更多内容请参考：ESTIMATE

查看在处理查询时要从表中读取的数据行数、Mark和Part的预估数量。这个命令在分析 SQL 查询性能的时候非常有用。SQL 实例如下：

EXPLAIN ESTIMATESELECT count(UserID) AS cntFROM( SELECT UserID, WatchID FROM tutorial.hits_v1 WHERE (JavaEnable = 1) AND (EventDate = '2014-03-18')) AS t1INNER JOIN( SELECT UserID, WatchID FROM tutorial.hits_v1 WHERE (EventDate = '2014-03-19') AND (JavaEnable = 1)) AS t2 ON t1.UserID = t2.UserID Query id: a848f250-9e30-44b3-90eb-8e03c429441f ┌─database─┬─table───┬─parts─┬─────rows─┬─marks─┐│ tutorial │ hits_v1 │ 2 │ 13086546 │ 1599 │└──────────┴─────────┴───────┴──────────┴───────┘ 1 rows in set. Elapsed: 0.011 sec.

1.2. 本章总结

通过对本章的学习，我们对ClickHouse SQL有了系统化的掌握。我们知道了如何使用 SQL 来创建数据库、创建表、创建视图，新增数据库用户账户、角色等，并进行库表权限管理。同时，我们还知道如何向 ClickHouse 表中插入数据，并对表中的数据进行查询。按照对SQL 的解析执行顺序，我们还详细介绍了各种SQL查询子句的用法。为了加深对SQL 查询原理的理解，我们还介绍了 SQL 发展简史、关系代数和关系演算相关的内容。最后，介绍了通过EXPLAIN语句探索查询执行过程。

下一章我们将进入项目实战开发，介绍如何使用 Spring Boot 连接 ClickHouse，实现一个简单的BI数据报表分析系统。

版权声明：本文内容由网络用户投稿，版权归原作者所有，本站不拥有其著作权，亦不承担相应法律责任。如果您发现本站中有涉嫌抄袭或描述失实的内容，请联系我们jiasou666@gmail.com 处理，核实后本网站将在24小时内删除侵权内容。