Lanson

V1

2022/11/10阅读:28主题:丘比特忙

大数据ClickHouse进阶(二十一):ClickHouse的Projection投影

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替换物化视图。

分类:

后端

标签:

大数据

作者介绍

Lanson
V1

CSDN大数据领域博客专家