ClickHouse调优（二）语法优化

最新推荐文章于 2024-04-17 07:11:30 发布

Yuan_CSDF

最新推荐文章于 2024-04-17 07:11:30 发布

阅读量3.5k

点赞数 1

分类专栏： # ClickHouse 文章标签：大数据 big data clickhouse

本文链接：https://blog.csdn.net/Yuan_CSDF/article/details/122293843

版权

ClickHouse 专栏收录该内容

10 篇文章 6 订阅

订阅专栏

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、数据采样

通过采样运算可极大提升数据分析的性能

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());

这行数据并没有真正的被删除，而是被过滤掉了。在一些合适场景下，可以结合表级别的TTL最终将物理数据删除。

3.4、通过FINAL查询

在查询语句后增加FINAL修饰符，这样在查询的过程中将会执行Merge的特殊逻辑（例如数据去重，预聚合等）。

但是这种方法在早期版本基本没有人使用，因为在增加FINAL之后，我们的查询将会变成一个单线程的执行过程，查询速度非常慢。

在v20.5.2.7-stable版本中，FINAL查询是支持多线程执行，并且可以通过max_final_threads参数控制单个查询的线程数。但是目前读取part部分的动作依然是串行的。

FIANL查询最终的性能和很多因素相关，列字段的大小、分区的数量等等都会影响到最终的查询时间，所以还要结合实际场景取舍。

参考链接：https://github.com/ClickHouse/ClickHouse/pull/10463

4、物化视图

ClickHouse的物化视图是一种查询结果的持久化，它确实是给我们带来了查询效率的提升。用户查起来跟表没有quiet，它就会一张表，它也像是一张时刻在预计算的表，创建的过程它是用了一个特殊引擎，加上后来as select，就是create一个table as select的写法。

“查询结果集”的范围很宽泛，可以是基础表中部分数据的一份简单拷贝，也可以使多表Join之后产生的结果或其子集，或者原始数据的聚合指标等等。所以，物化视图不会随着基础表的变化而变化，所以它也称为快照(snapshot)。

4.1、概述

4.1.1、物化视图与普通视图的区别

普通视图不保存数据，保存的仅仅是查询语句，查询的时候还是从原表读取数据，可以将普通视图理解为是个子查询。物化视图是把查询的结果根据相应的引擎存入到了磁盘或内存中，对数据重新进行了组织，你可以理解物化视图是完全的一张新表。

4.1.2、优缺点

优点：查询速度快，要是把物化视图这些规则全部写好，它比元数据查询快乐很多，总的行数少了，因为都预计算好了。

缺点：它的本质是一个流式数据的使用场景，是累加式的技术，所以要用历史数据做去重，去核这样的分析，在物化视图里面是不太好用的。在某些场景的使用也是有限的。而且如果一战表加了很多物化视图，在写这张表的时候，就会消耗很多机器的资源，比如护具带宽占满、存储一下子增加了很多。

4.1.3、基本语法

也是create语法，会创建一个隐藏的目标表来保存视图数据。也可以TO表名，保存到一张显示的表。没有加TO表名，表名默认就会.inner.物化视图名。

CREATE [MATERIALIZED] VIEW [IF NOT EXISTS] [db.]table_name [TO[db.]name] [ENGINE = engine] [POPULATE] AS SELECT ...

1、创建物化视图的限制

必须指定物化视图的engine用于数据存储
TO [db].[table]语法的使用，不得使用POPULATE。
查询语句(select)可以包含下面的子句：DISTINCT,GROUP BY,ORDER BY,LIMIT...
物化视图的alter操作有些限制，操作起来不大方便。
如物化视图的定义使用了 TO [db.]name子语句，则可以将目标表的视图卸载DETACH再装载ATTACH。

2、物化视图的数据更新

物化视图创建好之后，若源表被写入新数据则物化视图也会同步更新。
POPULATE关键字决定了物化视图的更新策略：
1. 若有POPULATE则在创建视图的过程会将源表已经存在的数据一并导入，类似于create table ... as
2. 若无POPULATE则物化视图在创建之后没有数据，只会在创建只有同步之后写入源表的数据。
3. clickhouse官方并不推荐使用POPULATE，因为在创建物化视图的过程中同时写入的数据不能被插入物化视图。
物化视图不支持同步删除，若源表的数据不存在（删除了）则物化视图的数据仍然保留。
物化视图是一种特殊的数据表，可以用show table查看。

Yuan_CSDF

关注

1
点赞
踩
15

收藏

觉得还不错? 一键收藏
0
评论
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、消除子查询重复字段语...
复制链接

扫一扫