ClickHouse 语法优化规则

ClickHouse的SQL优化规则是基于RBO(RuleBasedOptimization)，下面是一些优化规则

1准备测试用表

1）上传官方的数据集

将visits_v1.tar和hits_v1.tar上传到虚拟机，解压到clickhouse数据路径下

//解压到clickhouse数据路径

sudotar-xvfhits_v1.tar-C/var/lib/clickhouse
sudotar-xvfvisits_v1.tar-C/var/lib/clickhouse

//修改所属用户
sudochown-Rclickhouse:clickhouse/var/lib/clickhouse/data/datasets
sudochown-Rclickhouse:clickhouse/var/lib/clickhouse/metadata/datasets

2）重启clickhouse-server

sudoclickhouserestart

3）执行查询

clickhouse-client–query”SELECTCOUNT(*)FROMdatasets.hits_v1″

clickhouse-client–query”SELECTCOUNT(*)FROMdatasets.visits_v1″

注意：官方的tar包，包含了建库、建表语句、数据内容，这种方式不需要手动建库、建表，最方便。

hits_v1表有130多个字段，880多万条数据

visits_v1表有180多个字段，160多万条数据

2COUNT优化

在调用count函数时，如果使用的是count()或者count(*)，且没有where条件，则会直接使用system.tables的total_rows，例如:

EXPLAINSELECTcount()FROMdatasets.hits_v1;

Union
Expression(Projection)
Expression(BeforeORDERBYandSELECT)
MergingAggregated
ReadNothing(Optimizedtrivialcount)

注意Optimizedtrivialcount，这是对count的优化。

如果count具体的列字段，则不会使用此项优化：

EXPLAINSELECTcount(CounterID)FROMdatasets.hits_v1;

Union
Expression(Projection)
Expression(BeforeORDERBYandSELECT)
Aggregating
Expression(BeforeGROUPBY)
ReadFromStorage(ReadfromMergeTree)

3消除子查询重复字段

下面语句子查询中有两个重复的id字段，会被去重:

EXPLAINSYNTAXSELECT
a.UserID,
b.VisitID,
a.URL,
b.UserID服务器托管网
FROM
hits_v1ASa
LEFTJOIN( 
SELECT
UserID,        
UserIDasHaHa,       
VisitID
FROMvisits_v1)ASb
USING(UserID)
limit3;

//返回优化语句：
SELECT
UserID,
VisitID,
URL,
b.UserID
FROMhits_v1ASa
ALLLEFTJOIN
(
SELECT
UserID,
VisitID
FROMvisits_v1
)ASbUSING(UserID)
LIMIT3

4谓词下推

当groupby有having子句，但是没有withcube、withrollup或者withtotals修饰的时候，having过滤会下推到where提前过滤。例如下面的查询，HAVINGname变成了WHEREname，在groupby之前过滤：

EXPLAINSYNTAXSELECTUserIDFROMhits_v1GROUPBYUserIDHAVINGUserID= '8585742290196126178';

//返回优化语句
SELECTUserID
FROMhits_v1
WHEREUserID='8585742290196126178'
GROUPBYUserID

子查询也支持谓词下推：

EXPLAINSYNTAX
SELECT*
FROM
(
SELECTUserID
FROMvisits_v1
)
WHEREUserID= '8585742290196126178'

//返回优化后的语句
SELECTUserID
FROM
(
SELECTUserID
FROMvisits_v1
WHEREUserID='8585742290196126178'
)
WHEREUserID='8585742290196126178'

再来一个复杂例子：

//返回优化后的语句
SELECTUserID
FROM
(
SELECTUserID
FROM
    (
SELECTUserID
FROMvisits_v1
WHEREUserID='8585742290196126178'
    )
WHEREUserID='8585742290196126178'
UNIONALL
SELECTUserID
FROM
    (
SELECTUserID
FROMvisits_v1
WHEREUserID='8585742290196126178'
    )
WHEREUserID='8585742290196126178'
)
WHEREUserID='8585742290196126178'

5聚合计算外推

聚合函数内的计算，会外推，例如：

EXPLAINSYNTAX
SELECTsum(UserID* 2)
FROMvisits_v1

//返回优化后的语句
SELECTsum(UserID) * 2
FROMvisits_v1

6聚合函数消除

如果对聚合键，也就是groupbykey使用min、max、any聚合函数，则将函数消除，例如：

EXPLAINSYNTAX
SELECT
    sum(UserID* 2),
    max(VisitID),
    max(UserID)
FROMvisits_v1
GROUPBYUserID

//返回优化后的语句
SELECT
    sum(UserID) * 2,
    max(VisitID),
UserID
FROMvisits_v1
GROUPBYUserID

7删除重复的orderbykey

例如下面的语句，重复的聚合键id字段会被去重:

EXPLAINSYNTAX
SELECT*
FROMvisits_v1
ORDERBY
UserIDASC,
UserIDASC,
VisitIDASC,
VisitIDASC


//返回优化后的语句：
select
	……
FROMvisits_v1
ORDERBY
UserIDASC,
V服务器托管网isitIDASC

8删除重复的limitbykey

例如下面的语句，重复声明的name字段会被去重：

EXPLAINSYNTAX
SELECT*
FROMvisits_v1
LIMIT3BY
VisitID,
VisitID
LIMIT10


//返回优化后的语句：
select
	……
FROMvisits_v1
LIMIT3BYVisitID
LIMIT10

9删除重复的USINGKey

例如下面的语句，重复的关联键id字段会被去重：

EXPLAINSYNTAX
SELECT
a.UserID,
a.UserID,
b.VisitID,
a.URL,
b.UserID
FROMhits_v1ASa
LEFTJOINvisits_v1ASbUSING(UserID,UserID)


//返回优化后的语句：
SELECT
UserID,
UserID,
VisitID,
URL,
b.UserID
FROMhits_v1ASa
ALLLEFTJOINvisits_v1ASbUSING(UserID)

10标量替换

如果子查询只返回一行数据，在被引用的时候用标量替换，例如下面语句中的total_disk_usage字段：

EXPLAINSYNTAX
WITH
    (
SELECTsum(bytes)
FROMsystem.parts
WHEREactive
    )AStotal_disk_usage
SELECT
    (sum(bytes) /total_disk_usage) * 100AStable_disk_usage,
table
FROMsystem.parts
GROUPBYtable
ORDERBYtable_disk_usageDESC
LIMIT10;


//返回优化后的语句：
WITHCAST(0,'UInt64')AStotal_disk_usage
SELECT
    (sum(bytes) /total_disk_usage) * 100AStable_disk_usage,
table
FROMsystem.parts
GROUPBYtable
ORDERBYtable_disk_usageDESC
LIMIT10

11三元运算优化

如果开启了optimize_if_chain_to_multiif参数，三元运算符会被替换成multiIf函数，例如：

EXPLAINSYNTAX
SELECTnumber= 1 ? 'hello' : (number= 2 ? 'world' : 'atguigu') 
FROMnumbers(10) 
settingsoptimize_if_chain_to_multiif= 1;

//返回优化后的语句：
SELECTmultiIf(number= 1,'hello',number= 2,'world','atguigu')
FROMnumbers(10)
SETTINGSoptimize_if_chain_to_multiif= 1