5、聚合计算外推
聚合函数内的计算,会外推,例如:EXPLAIN SYNTAX SELECT sum(UserID * 2) FROM datasets.visits_v1; //优化后效果 SELECT sum(UserID) * 2 FROM datasets.visits_v1
6 聚合函数消除
如果对聚合键,也就是 group by key 使用 min、max、any 聚合函数,则将函数消除,例如:EXPLAIN SYNTAX SELECT sum(UserID * 2), max(VisitID), max(UserID) FROM datasets.visits_v1 GROUP BY UserID; //返回优化后的语句 SELECT sum(UserID) * 2, max(VisitID), UserID FROM datasets.visits_v1 GROUP BY UserID
7、删除重复的 order by key
EXPLAIN SYNTAX SELECT * FROM datasets.visits_v1 ORDER BY UserID ASC, UserID ASC, VisitID ASC, VisitID ASC; //返回优化后的语句: select …… FROM visits_v1 ORDER BY UserID ASC, VisitID ASC
8、删除重复的 limit by key
例如下面的语句,重复声明的 name 字段会被去重EXPLAIN SYNTAX SELECT * FROM datasets.visits_v1 LIMIT 3 BY VisitID, VisitID LIMIT 10 //返回优化后的语句: select …… FROM datasets.visits_v1 LIMIT 3 BY VisitID LIMIT 10
9、删除重复的 USING Key
例如下面的语句,重复的关联键 id 字段会被去重:EXPLAIN SYNTAX SELECT a.UserID, a.UserID, b.VisitID, a.URL, b.UserID FROM datasets.hits_v1 AS a LEFT JOIN datasets.visits_v1 AS b USING (UserID, UserID) //返回优化后的语句: SELECT UserID, UserID, VisitID, URL, b.UserID FROM datasets.hits_v1 AS a ALL LEFT JOIN datasets.visits_v1 AS b USING (UserID)
10、标量替换
如果子查询只返回一行数据,在被引用的时候用标量替换,例如下面语句中的 total_disk_usage 字段EXPLAIN SYNTAX WITH ( SELECT sum(bytes) FROM system.parts WHERE 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
11 三元运算优化
如果开启了 optimize_if_chain_to_multiif 参数,三元运算符会被替换成 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;标签:datasets,UserID,visits,语法,v1,细节,ClickHouse,SELECT,VisitID From: https://www.cnblogs.com/wdh01/p/16877668.html