ClickHouse调优（⼆）语法优化
1、ClicHouse语法优化规则
ClickHouse的SQL优化规则是基于RBO（Rule Based Optimization），下⾯是⼀些优化规则。
1.1、COUNT优化
在调⽤count函数时，如果使⽤的是count()或者count(*)，且没有where条件，则会直接使⽤system.tables的total_rows，例如：
注意：Optimized trivial count，这是对count的优化。
1.2、消除⼦查询重复字段
语句⼦查询中有两个重复的id字段，会被去重。
1.3、谓词下推
当group by有having⼦句，但是没有with cube、with rollup或者with totals修饰的时候，having会下推到where提前过滤。EXPLAIN SYNTAX SELECT UserID FROM hits_v1 GROUP BY UserID HAVING UserID='123456789';
1.4、聚合计算外推
聚合函数内的计算，会外推，如下：
EXPLAIN SYNTAX SELECT sum(UserID * 2) FROM visits_v1;
1.5、聚合函数消除
如果对聚合键，也就是group by key使⽤min、max等聚合函数，则会将函数消除。
EXPLAIN SYNTAX SELECT sum(UserID * 2),max(VisitID),max(UserID) FROM visits_v1 GROUP BY UserID;
1.6、删除重复的order by key、删除重复的limit by key
当order by、limit后有跟相同的key，SYNTAX会提⽰可进⾏优化。
1.7、三元运算优化
set optimize_if_chain_to_multiif = 1;
或者直接使⽤如下SQL：
-- 查看语法优化
EXPLAIN SYNTAX SELECT number =1? 'hello':(number=2?'world':'abcdef') from numbers(10)
settings optimize_if_chain_to_multiif = 1;
2、查询优化
2.1、单表查询
2.1.1、Prewhere替代where
Prewhere和where语句的作⽤相同，⽤来过滤数据。不同之处在于prewhere只⽀持*MergeTree族系列引擎的表，⾸先会读取指定的列数据，来判断数据过滤，等待数据过滤之后再读取select声明的列字段来补全其余属性。
当查询列明显多于筛选列时使⽤PreWhere可⼗倍提升查询性能，Prewhere会⾃动优化执⾏过滤阶段的数据读取⽅式，降低io操作。参数optimize_move_to_prewhere=1为开启状态，可以设置为0对其关闭。
在某些场合下，prewhere语句⽐where语句处理的数据量更少性能更⾼。
默认情况，我们肯定不会关闭where⾃动优化成prewhere，但是某些场景即使开启优化，也不会⾃动转换成prewhere，需要⼿动指定prewhere：
使⽤常量表达式
使⽤默认值为alias类型的字段
包含了arrayJOIN，globalIn，globalNotIn或者IndexHint的查询
select查询的列字段和where的谓词相同
使⽤了主键字段
2.1.2、数据采样
通过采样运算可极⼤提升数据分析的性能distinct查询
select Title,count(*) As PageViews
FROM hits_v1
SAMPLE 01.
WHERE CounterID = 57
GROUP BY Title
ORDER BY PageViews DESC LIMIT 1000
采样修饰符只有在MergeTree engine表中才有效，且在创建表时需要制定采样策略。
2.1.3、列裁剪与分区裁剪
数据量太⼤应避免使⽤select * 操作，查询的性能会与查询的字段⼤⼩和数量成线性关系，字段越少，消耗的io资源越少，性能就会越⾼。
列裁剪表⽰只选取所需要的列。
分区裁剪就是只读取需要的分区，在过滤条件中指定。
2.1.4、order by 结合where、limit使⽤
千万以上数据集进⾏order by 查询时需要配合where条件和limit语句⼀起使⽤。
2.1.5、避免构建虚拟列
如⾮必须，不要在结果集上构建虚拟列，虚拟列⾮常消耗资源浪费性能，可以考虑在前端处理，或者在表中构造实际字段进⾏额外存储。
2.1.6、uniqueCombined替代distinct
性能可提升10倍以上，uniqCombined底层采⽤类似HyperLogLog算法实现，能接收2%左右的数据误差，可直接使⽤这种去重⽅式提升查询性能。Count(distinct)会使⽤uniqExact精确去重。
不建议在千万级不同数据上执⾏distinct去重查询，改为近似去重uniqCombined。
2.2、多表关联
2.2.1、⽤in代替join
当多表联查时，查询的数据仅从其中⼀张表出事，可以考虑⽤in操作⽽不是join
2.2.2、⼤⼩表join
多表join时要满⾜⼩表在右的原则，右表关联时被加载到内存中与左表进⾏⽐较，ClickHouse中⽆论是Left join、Right Join还是Inner join永远都是拿着右表中的每⼀条记录到左表中查该记录是否存在，所以右表必须是⼩表。
2.2.3、注意谓词下推（版本差异）
ClikHouses在join查询时不会主动发起谓词下推的操作，需要每个⼦查询提前完成过滤操作，需要注意的是，是否执⾏谓词下推，对性能影响差别很⼤（新版本中已经不存在此问题，但是需要注意谓词的位置不⽤依然有性能的差异）。
2.2.4、分布式表使⽤GLOBAL
两张分布式表的in和join之前必须加上GLOBAL关键字，右表只会在接收查询请求的那个节点查询⼀次，并将其分发到其它节点上，如果不加GLOBAL关键字的话，每个节点都会单独发起⼀次对右表的查询，⽽右表⼜是分布式表，就导致右表⼀共会被查询次（N是该分布式表的分⽚数量），这就是查询放⼤，会带来很⼤开销。
2.2.5、使⽤字典表
将⼀些需要关联分析的业务创建成字典表进⾏join操作，前提是字典表不宜太⼤，因为字典表会常驻内存。
2.2.6、提前过滤
通过增加逻辑过滤可以减少数据扫描，达到提供执⾏速度及降低内存消息的⽬的。
3、数据⼀致性
ReplacingMergeeTree：数据的去重只会在数据合并期间进⾏。合并会在后台⼀个不确定的时间进⾏，因此你⽆法预先作出计划。有⼀些数据可能仍未被处理。集暖你可以调⽤optimize语句发起计划外的合并，但请不要依靠它，因为OPTIMIZE语句会引发对护具的⼤量读写。
因此，ReplacingMergeTree适⽤于在后台清除重复的数据以节省空间，但是它不保证没有重复的数据出现。
3.1、案例
1、创建表
CREATE TABLE test_a(
user_id UInt64,
score String,
deleted UInt8 DEFAULT 0,
create_time DateTime DEFAULT toDateTime(0)
)ENGINE = ReplacingMergeTree(create_time)
ORDER BY user_id;
其中：
user_id是数据去重更新的标识；
create_time是版本号字段，每组数据中create_time最⼤的⼀⾏表⽰最新的数据；
delete是⾃定义的⼀个标记位，⽐如0代表为删除，1代表删除数据。
2、写⼊与修改数据
INSERT INTO TABLE test_a(user_id,score)
WITH(
SELECT ['A','B','C','D','E','F','G']
)
AS dict
SELECT number AS user_id,dict[number%7+1] FROM numbers(10000);
INSERT INTO TABLE test_a(user_id,score,create_time)
WITH(
SELECT ['AA','BB','CC','DD','EE','FF','GG']
)AS dict
SELECT number AS user_id,dict[number%7+1],now() as create_time FROM numbers(5000);
3、此时尚未触发分区合并，所以还未去重。
3.2、⼿动OPTIMIZE
在写⼊数据后，⽴刻执⾏OPTIMIZE强制触发新写⼊分区的合并动作
语法：OPTIMIZE TABLE [db.]name [ON CLUSTER cluster]  [PARTITION partition | PARTITION ID 'partition_id'] [FINAL] [DEDUPLICATE [BY expression]]
OPTIMIZE TABLE test_a FINAL;
3.3、通过Group by 去重
1、执⾏去重的查询
SELECT
user_id,
argMax(score,create_time) AS score,
argMax(deleted,create_time) AS deleted,
max(create_time) AS ctime
FROM test_a
GROUP BY user_id
HAVING deleted = 0;
函数说明：
argMax(field1,field2)：按照field2的最⼤值取field1的值。
当我们更新数据时，会写⼊⼀⾏新的数据，例如上⾯语句中，通过查询最⼤的create_time得到修改后的score字段值。
2、创建视图
CREATE VIEW view_test_a AS
SELECT
user_id,
argMax(score,create_time) AS score,
argMax(deleted,create_time) AS deleted,
max(create_time) AS ctime
FROM test_a
GROUP BY user_id
HAVING deleted = 0;
3、删除数据测试
-- 原有1W条数据
select count(*) from test_a;
-- 插⼊并查询，原表有10001条数据，视图表有10000条数据
INSERT INTO TABLE test_a(user_id,score,create_time) VALUES (0,'AAAA',now());
-- 插⼊并查询，原表有10002条数据，视图表有9999条数据
INSERT INTO TABLE test_a(user_id,score,deleted,create_time) VALUES (0,'AAAA',1,now());

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。