大数据ClickHouse进阶（二十一）：ClickHouse的Projection投影

原创

Lansonli

发布于 2022-11-03 00:24:02

2.3K0

发布于 2022-11-03 00:24:02

文章被收录于专栏：Lansonli技术博客Lansonli技术博客

ClickHouse的Projection投影

在使用ClickHouse MergeTree引擎时，如果某张MergeTree表建表排序规则如下：

order by A,B,C

那么通常过滤查询Where A很快，但是Where C会慢一些。

此外，我们在使用物化视图时，经常基于一张底表构建许多物化视图，以帮助更进一步提升查询性能、降低数据分析开销，例如：

#创建普通MergeTree 表作为底表
create table personinfo(id UInt32,name String,age UInt32,score UInt32) engine = MergeTree() order by id;

#基于以上底表创建统计平均年龄物化视图表
create materialized view  t_view1 engine = Log as select sum(age)/count(name) as avg_age from personinfo;

#基于以上底表创建统计每人总分数物化视图表
create materialized view  t_view2 engine = Log as select name,sum(score) as total_score from personinfo group by name;

#向表personinfo中插入以下数据
insert into personinfo values (1,'zs',18,100),(2,'ls',19,200),(3,'ww',20,300),(4,'zs',18,400),(5,'ls',19,500),(6,'ww','20',600);

#查询t_view1与t_view2两张物化视图结果
node1 :) select * from t_view1;
┌─avg_age─┐
│      19 │
└─────────┘

node1 :) select * from t_view2;
┌─name─┬─total_score─┐
│ ls   │         700 │
│ ww   │         900 │
│ zs   │         500 │
└──────┴─────────────┘

以上构建的物化视图实际上在ClickHouse中相当于是独立的表，也会单独存储在数据目录“/var/lib/ClickHouse/data/${DB}”中：

既然物化视图也是独立的表，那么就存在与原表数据一致性问题，如果物化视图很多，维护起来也是问题。

目前使用ClickHouse我们遇到以上不完美的地方，总结下来就是：

MergeTree只支持一种排序规则
物化视图不够智能

一、Projection投影介绍

ClickHouse Projection功能的出现完美解决了以上两点不完美。Projection(投影)指一组列的组合，可以按照与原表不同的排序存储，并且支持聚合函数查询，可以将Projection看成一种更加智能的物化视图，与物化视图一样本质也是用空间换时间，其具备以下特点：

part-level存储：

相比普通物化视图是一张独立的表，Projection 物化的数据就保存在原表的分区目录中，支持明细数据的普通Projection和预聚合Projection。

无感使用，自动命中：

可以对一张 MergeTree 创建多个 Projection ，当执行 Select 语句的时候，能根据查询范围，自动匹配最优的 Projection 提供查询加速。如果没有命中 Projection,就直接查询底表。

数据同源、同生共死：

因为物化的数据保存在原表的分区，所以数据的更新、合并都是同源的，也就不会出现不一致的情况了。

二、创建Projection投影测试

下面我们通过案例来测试Projection的使用性能，示例如下：

#向MySQL 库ck_db中导入 song表,数据量为17万左右，在ClickHouse库mysql_ck_db中会有对应的物化引擎表，这时在ClickHouse默认default库中执行如下语句，将song表数据导入到default.song_info表中
node1 :) create table song_info engine=MergeTree() order by source_id partition by status as select * from mysql_ck_db.song;

#没有Projection的时候，查询非主键name列
node1 :) select name from song_info where name = '独家女孩';
1 rows in set. Elapsed: 0.017 sec. Processed 177.31 thousand rows, 4.16 MB (10.68 million rows/s., 250.61 MB/s.)

针对name列创建一个Projection，为特定的where字段加速，按照查询的需求生成有别于主键的另一种排序规则：

ALTER TABLE song_info ADD PROJECTION p1
( 
    SELECT 
      name,album
    ORDER BY name
)

注意：只有在创建Projection之后，再被写入的数据才会自动物化，对于历史数据需要执行如下命令手动触发物化：

ALTER TABLE song_info MATERIALIZE PROJECTION p1;

我们可以执行如下命令查询物化是否完成：

SELECT
    table,
    mutation_id,
    command,
    is_done
FROM system.mutations AS m
where table = 'song_info';

以上完成后，我们可以进入到song_info数据目录：“/var/lib/ClickHouse/data/default/song_info/”，发现生成新的数据目录：

编辑且每个分区对应的新目录中多了一个p1.proj子目录，进入到此子目录，我们发现与MergeTree表存储格式一样，如下：

有了p1 projection后我们执行同样查询，首先需要设置参数开启projection功能：

SET allow_experimental_projection_optimization = 1;

开启之后，重新执行原有的SQL，我们发现由原来扫码4.16M数据变成了扫描407Kb数据：

node1 :) select name from song_info where name = '独家女孩';
1 rows in set. Elapsed: 0.011 sec. Processed 17.08 thousand rows, 407.83 KB (1.60 million rows/s., 38.23 MB/s.)

三、explain验证是否使用Projection优化

我们可以执行如下命令来查看执行的SQL是否使用了Projection功能：

node1 :)explain select name from song_info where name = '独家女孩';

如上，如果看到“MergeTree(with 0 projection p1)”代表使用了Projection优化。

四、Projection支持预聚合

projection同样支持预聚合，在没有优化的情况下，以下查询会全表扫描：

node1 :) select source,count(*) from song_info group by source;
7 rows in set. Elapsed: 0.011 sec. Processed 177.31 thousand rows, 886.58 KB (16.12 million rows/s., 80.60 MB/s.)

创建另外一个Projection：

ALTER TABLE song_info ADD PROJECTION agg_p2
   ( 
     SELECT
         source, 
         count(*)
       GROUP BY source
   )

由于历史数据存在，需要手动触发下物化：

ALTER TABLE song_info MATERIALIZE PROJECTION agg_p2;

执行完成之后，再次执行相同查询：

node1 :) select source,count(*) from song_info group by source;
7 rows in set. Elapsed: 0.009 sec.

可以通过以上看出速度快了很多。

五、删除Projection投影

我们可以执行如下命令删除Projection：

node1 :) ALTER TABLE song_info DROP PROJECTION p1;
node1 :) ALTER TABLE song_info DROP PROJECTION agg_p2;

六、Projection投影总结

在使用Projection时，查询使用Projection的匹配规则如下：

设置SET allow_experimental_projection_optimization = 1
返回的数据行小于基表总数
查询覆盖的分区part超过一半
Where必须是Projection定义中Group By的子集
Group By必须是Projection定义中Group By的子集
Select 必须是Projection定义中Select的子集
匹配多个Projection的时候，自动选取读取part最少的

利用Projection，我们只需要面对一张表查询就行，即拥有了原来物化视图的性能，又免去了维护成本与数据一致性的问题，相信未来可以使用Projection替换物化视图。

原创声明：本文系作者授权腾讯云开发者社区发表，未经许可，不得转载。

如有侵权，请联系 cloudcommunity@tencent.com 删除。

云数据仓库 ClickHouse

原创声明：本文系作者授权腾讯云开发者社区发表，未经许可，不得转载。

如有侵权，请联系 cloudcommunity@tencent.com 删除。

云数据仓库 ClickHouse

#ClickHouse

登录后参与评论

0 条评论

热度