详解 ClickHouse 的语法优化规则

ClickHouse 的 SQL 优化规则是基于 RBO(Rule Based Optimization)

一、count 优化

--1. count()、count(1) 和 count(*)，且没有 where 条件，则会直接使用 system.tables 的 total_rows
EXPLAIN SELECT count()FROM datasets.hits_v1;--2. count(column)，没有 Optimized trivial count 优化
EXPLAIN SELECT count(CounterID) FROM datasets.hits_v1;

二、消除子查询重复字段

--编写的 sql 中查询了重复字段
EXPLAIN SYNTAX SELECT a.UserID, b.VisitID, a.URL, b.UserID FROM hits_v1 AS a
LEFT JOIN (SELECTUserID,UserID as uid,VisitIDFROM visits_v1
) AS b USING (UserID)
limit 3;--在 hive 中会直接查询展示两个重复字段的值--但在 clickhouse 中会优化去除重复字段
SELECTUserID,VisitID,URL,b.UserID
FROM hits_v1 AS a
ALL LEFT JOIN
(SELECTUserID,VisitIDFROM visits_v1
) AS b USING (UserID)
LIMIT 3;

三、谓词下推

通俗的说是指提前过滤

--1. 当 group by 有 having 子句，但是没有 with cube、with rollup 或者 with totals 修饰的时，having 过滤会下推到 where 提前过滤
EXPLAIN SYNTAX SELECT UserID FROM hits_v1 GROUP BY UserID HAVING UserID = '8585742290196126178';--返回优化语句
SELECT UserID FROM hits_v1 WHERE UserID = '8585742290196126178' GROUP BY UserID--2. 子查询的谓词下推 (ps：hive 中子查询不会谓词下推)
EXPLAIN SYNTAX
SELECT *
FROM
(SELECT UserIDFROM visits_v1
)
WHERE UserID = '8585742290196126178'--返回优化后的语句
SELECT UserID
FROM
(SELECT UserIDFROM visits_v1WHERE UserID = '8585742290196126178'
)
WHERE UserID = '8585742290196126178'EXPLAIN SYNTAX
SELECT * FROM 
(SELECT*FROM(SELECTUserIDFROM visits_v1)UNION ALLSELECT* FROM(SELECTUserIDFROM visits_v1)
)
WHERE UserID = '8585742290196126178'--返回优化后的语句
SELECT UserID
FROM
(SELECT UserIDFROM(SELECT UserIDFROM visits_v1WHERE UserID = '8585742290196126178')WHERE UserID = '8585742290196126178'UNION ALLSELECT UserIDFROM(SELECT UserIDFROM visits_v1WHERE UserID = '8585742290196126178')WHERE UserID = '8585742290196126178'
)
WHERE UserID = '8585742290196126178'

四、聚合计算外推

--聚合函数内的计算，会外推 (ps：hive中不会外推)
EXPLAIN SYNTAX SELECT sum(UserID * 2) FROM visits_v1;--返回优化后的语句
SELECT sum(UserID) * 2 FROM visits_v1

五、聚合函数消除

--对于无意义的聚合函数会优化消除
EXPLAIN SYNTAX
SELECTsum(UserID * 2),max(VisitID),max(UserID)
FROM visits_v1
GROUP BY UserID--返回优化后的语句
SELECTsum(UserID) * 2,max(VisitID),UserID
FROM visits_v1
GROUP BY UserID

六、不同语法删除重复的 key

--order by
EXPLAIN SYNTAX
SELECT *
FROM visits_v1
ORDER BYUserID ASC,UserID ASC,VisitID ASC,
VisitID ASC--返回优化后的语句：
select*
FROM visits_v1
ORDER BYUserID ASC,
VisitID ASC--limit by
EXPLAIN SYNTAX
SELECT *
FROM visits_v1
LIMIT 3 BYVisitID,VisitID
LIMIT 10--返回优化后的语句：
select*
FROM visits_v1
LIMIT 3 BY VisitID
LIMIT 10--using
EXPLAIN SYNTAX
SELECTa.UserID,a.UserID,b.VisitID,a.URL,b.UserID
FROM hits_v1 AS a
LEFT JOIN visits_v1 AS b USING (UserID, UserID)--返回优化后的语句：
SELECTUserID,UserID,VisitID,URL,b.UserID
FROM hits_v1 AS a
ALL LEFT JOIN visits_v1 AS b USING (UserID)

七、标量替换

--如果子查询只返回一行数据，在被引用的时候用标量替换
EXPLAIN SYNTAX
WITH
(SELECT sum(bytes)FROM system.partsWHERE active
) AS total_disk_usage
SELECT(sum(bytes) / total_disk_usage) * 100 AS table_disk_usage,table
FROM system.parts
GROUP BY table
ORDER BY table_disk_usage DESC
LIMIT 10;--返回优化后的语句：
WITH CAST(0, 'UInt64') AS total_disk_usage
SELECT(sum(bytes) / total_disk_usage) * 100 AS table_disk_usage,table
FROM system.parts
GROUP BY table
ORDER BY table_disk_usage DESC
LIMIT 10

八、三元运算符优化

--开启 optimize_if_chain_to_multiif 参数，会进行三元运算符优化
EXPLAIN SYNTAX
SELECT number = 1 ? 'hello' : (number = 2 ? 'world' : 'atguigu')
FROM numbers(10)
settings optimize_if_chain_to_multiif = 1;--返回优化后的语句：
SELECT multiIf(number = 1, 'hello', number = 2, 'world', 'atguigu')
FROM numbers(10) SETTINGS optimize_if_chain_to_multiif = 1