菜单

Clickhouse之物化视图实现高效查询

赵星 发表于

前言

随着市场推广业务发展，为了更精准地投放，我们需要更多的报表字段进行分析。目前报表数据有以下特点：

  1. 单个报表字段多（目前付费渠道趋势分析数据表字段多达180+）
  2. 数据量大（渠道数据表单日数据量有300w+）
  3. 数据来源分散，数据产出时间分散（渠道数据来源包括大数据离线聚合表、媒体离线数据报表、市场业务逻辑相关中间表例如回传数据表等）

由此引来以下问题：

  1. 查询效率越来越低
  2. 更新字段困难

为了解决以上问题，在【Clickhouse实现主键（排序键）更新】一文中已经提出一种针对于Clickhouse的字段更新方式，有效解决了Clickhouse更新瓶颈的问题，然而在单表字段特别多，查询带有聚合、列间计算操作等场景下，查询效率仍然很低。因此本文主要分享一种利用Clickhouse预聚合方式，来提高查询效率。

什么是物化视图

我们都知道，视图是从一个或者多个基本表（或视图）导出的表（虚拟表），实质上是将底层结构隐藏封装起来，
使得我们只需要专注于上层的查询，简化了我们的查询条件，但数据仍然是从基础表中查询，并不会提高任何查询效率。
img-1

而物化视图是将上述的虚拟表变成一个真实的表，将多个表的数据通过预设的条件提前存储到这张表中，从而达到一个预聚合的目的。这个时候我们查询的数据就来源于预聚合好的表。
img_1-1

因此物化视图能通过预聚合的方式提高查询效率。

Clickhouse物化视图主要的优缺点

优点：
查询效率高
缺点：
1.牺牲空间换取时间
2.使用难度较大，需要合理设计表结构对历史数据做聚合分析
3.消耗更多机器资源
4.仅支持插入触发（严格来说不算缺点，因为Clickhouse本身不建议更新删除操作）

案例

场景

存储渠道维度下90天的累计充值用户数和累计充值金额（单表180个字段），每天更新一次有充值的渠道数据。

两种方案

1.【Clickhouse实现主键（排序键）更新】方案中提到的 AggregatingMergeTree+SimpleAggregateFunction+Nullable

这种方案已经顺利的帮我们解决了数据的更新问题，建表语句如下：

CREATE TABLE xx.trend_data
(
    `date`                  String COMMENT '用户激活当天的渠道',
    `channel_name`          String COMMENT '渠道名',
    `type`                  Int8 COMMENT '属性类型: 1.不含vip，2.含vip',
    `book_id`               SimpleAggregateFunction(anyLast, Nullable(Int64)) COMMENT '书籍ID',
    `charge_fee_day1`       SimpleAggregateFunction(anyLast, Nullable(Float64)) COMMENT '充值day1',
    `charge_fee_day2`       SimpleAggregateFunction(anyLast, Nullable(Float64)) COMMENT '充值day2',
    ......
    `charge_fee_day89`      SimpleAggregateFunction(anyLast, Nullable(Float64)) COMMENT '充值day89',
    `charge_fee_day90`      SimpleAggregateFunction(anyLast, Nullable(Float64)) COMMENT '充值day90',
    `charge_users_day1`     SimpleAggregateFunction(anyLast, Nullable(Float64)) COMMENT '充值用户day1',
    `charge_users_day2`     SimpleAggregateFunction(anyLast, Nullable(Float64)) COMMENT '充值用户day2',
    ......
    `charge_users_day89`    SimpleAggregateFunction(anyLast, Nullable(Float64)) COMMENT '充值用户day89',
    `charge_users_day90`    SimpleAggregateFunction(anyLast, Nullable(Float64)) COMMENT '充值用户day90',
    `ver`                   SimpleAggregateFunction(max, DateTime64(3)) DEFAULT now() COMMENT '版本',
    `after_discount`        SimpleAggregateFunction(anyLast, Nullable(Float64)) COMMENT '推广费'
)
    ENGINE = AggregatingMergeTree() PARTITION BY date ORDER BY (date, type, channel_name) SETTINGS index_granularity = 8192

表里每日插入产生的充值数据，我们如果要取出1～90天累计的充值数据，那么需要列与列之间相加才能得到每天的累计数据，幸运的是Clickhouse提供了这样的查询机制，这样我们就能顺利取出180个累计字段。

select charge_fee_day1 as ac_charge_fee_day1, ac_charge_fee_day1 + charge_fee_day2 as ac_charge_fee_day2, ...
from xx.trend_data

现在我们用线上数据以上述查询方式查200条数据，90个字段，看一下结果，耗时在300ms左右，显然这个结果是不那么令人满意的。
v1-1
我们继续调研一下，如果不需要字段之间累加，按照下面查询方式直接取出每一列字段的数据，性能是否会好一些。

select ac_charge_fee_day1, ac_charge_fee_day2, ...
from xx.trend_data

v2-2
通过比对可以看出，即使Clickhouse支持select的字段之间的计算，但开销还是非常大的，因此我们需要尽可能找到一种开销小的查询方式。

我们可以通过将需要的累计字段预聚合提高查询效率，请看方案2。

2. Materialized View + AggregatingMergeTree，实现预聚合+更新

核心思想

  1. 我们建一张数据记录表，用于记录每日新增的充值数据；
  2. 建一张中间表，通过触发器从横向维度计算每日新增充值字段的累计值；
  3. 最后建一个聚合表，通过触发器从纵向维度聚合充值字段所有天数的累计值。
    mv-2

具体实现

首先建一个张记录表，用于插入每日的充值数据，用普通的存储引擎即可。

CREATE TABLE xx.trend_data_record
(
    `date`                  String COMMENT '用户激活当天的渠道',
    `channel_name`          String COMMENT '渠道名',
    `type`                  Int8 COMMENT '属性类型: 1.不含vip，2.含vip',
    `book_id`               Int64 COMMENT '书籍ID',
    `after_discount`        Float64 COMMENT '推广费',
    `charge_fee_day1`       Float64 COMMENT '充值day1',
    `charge_fee_day2`       Float64 COMMENT '充值day2',
    ......
    `charge_fee_day89`      Float64 COMMENT '充值day89',
    `charge_fee_day90`      Float64 COMMENT '充值day90',
    `charge_users_day1`     Float64 COMMENT '充值用户day1',
    `charge_users_day2`     Float64 COMMENT '充值用户day2',
    ......
    `charge_users_day89`    Float64 COMMENT '充值用户day89',
    `charge_users_day90`    Float64 COMMENT '充值用户day90'
)
    ENGINE = MergeTree() PARTITION BY date ORDER BY (date, type, channel_name) SETTINGS index_granularity = 8192

接下来建一个物化视图监听 trend_data_record，做第一次横向预聚合，并将聚合结果导入到trend_data_mid，这样每一条充值数据进入到
trend_data_record时，都会通过物化视图聚合出这条数据的每日累计数据。

CREATE TABLE `xxx`.`trend_data_mid`
(
    `date`                  String COMMENT '用户激活当天的渠道',
    `channel_name`          String COMMENT '渠道名',
    `type`                  Int8 COMMENT '属性类型: 1.不含vip，2.含vip',
    `book_id`               Int64 COMMENT '书籍ID',
    `after_discount`        Float64 COMMENT '推广费',
    `ac_charge_fee_day1`    Float64 COMMENT '累计充值day1',
    `ac_charge_fee_day2`    Float64 COMMENT '累计充值day2',
    ...
    `ac_charge_fee_day89`   Float64 COMMENT '累计充值day89',
    `ac_charge_fee_day90`   Float64 COMMENT '累计充值day90',
    `ac_charge_users_day1`    Float64 COMMENT '累计充值用户day1',
    `ac_charge_users_day2`    Float64 COMMENT '累计充值用户day2',
    ...
    `ac_charge_users_day89`   Float64 COMMENT '累计充值用户day89',
    `ac_charge_users_day90`   Float64 COMMENT '累计充值用户day90'
)
    ENGINE = MergeTree() PARTITION BY date ORDER BY (date, type, channel_name) SETTINGS index_granularity = 8192;

-- 通过物化视图将数据从记录表拿出，并计算今日累计字段的累计值，最后输出到中间表
CREATE MATERIALIZED VIEW xxx.trend_data_mv1
    to `xxx`.`trend_data_mid`
AS
SELECT date,
       channel_name,
       type,
       book_id,
       after_discount,
       ifNull(ac_charge_fee_day1, 0) as ac_charge_fee_day1,
       (ifNull(ac_charge_fee_day2, 0) + ac_charge_fee_day1) as ac_charge_fee_day2,
       (ifNull(ac_charge_fee_day3, 0) + ac_charge_fee_day2) as ac_charge_fee_day3,
       ...
FROM `xxx`.`trend_data_record`

最后再建一个物化视图监听 trend_data_mid，将数据直接插入到最终结果表trend_data_final，
AggregatingMergeTree引擎的 SimpleAggregateFunction(sum, Nullable(Float64)) 会自动帮我们按照排序键将数据汇总。

CREATE TABLE `xxx`.`trend_data_final`
(
    `date`                  String COMMENT '用户激活当天的渠道',
    `channel_name`          String COMMENT '渠道名',
    `type`                  Int8 COMMENT '属性类型: 1.不含vip，2.含vip',
    `book_id`               SimpleAggregateFunction(max, Nullable(Int64)) COMMENT '书籍ID',
    `after_discount`        SimpleAggregateFunction(max, Nullable(Float64)) COMMENT '推广费',
    `ac_charge_fee_day1`    SimpleAggregateFunction(sum, Nullable(Float64)) COMMENT '累计充值day1',
    `ac_charge_fee_day2`    SimpleAggregateFunction(sum, Nullable(Float64)) COMMENT '累计充值day2',
    ...
    `ac_charge_fee_day89`   SimpleAggregateFunction(sum, Nullable(Float64)) COMMENT '累计充值day89',
    `ac_charge_fee_day90`   SimpleAggregateFunction(sum, Nullable(Float64)) COMMENT '累计充值day90',
    `ac_charge_users_day1`  SimpleAggregateFunction(sum, Nullable(Float64)) COMMENT '累计充值用户day1',
    `ac_charge_users_day2`  SimpleAggregateFunction(sum, Nullable(Float64)) COMMENT '累计充值用户day2',
    ...
    `ac_charge_users_day89` SimpleAggregateFunction(sum, Nullable(Float64)) COMMENT '累计充值用户day89',
    `ac_charge_users_day90` SimpleAggregateFunction(sum, Nullable(Float64)) COMMENT '累计充值用户day90'
)
    ENGINE = AggregatingMergeTree() PARTITION BY date ORDER BY (date, type, channel_name) SETTINGS index_granularity = 8192;


CREATE MATERIALIZED VIEW xxx.trend_data_mv2
            to xxx.trend_data_final
AS
SELECT date,
	   type,
	   channel_name,
	   book_id,
	   after_discount,
       ac_charge_fee_day1,
       ac_charge_fee_day2,
       ac_charge_fee_day3
FROM `xxx`.`trend_data_mid`

到这就结束了吗？no no no！我们还需要执行optimize触发数据合并，这样用户最终查到的数据才是正确的数据。

optimize table xxx.trend_data_final partition '2022-01-01' final

到此我们就可以把查询方法从方案一中的
select charge_fee_day1 as ac_charge_fee_day1, ac_charge_fee_day1 + charge_fee_day2 as ac_charge_fee_day2, ...
优化成
select ac_charge_fee_day1, ac_charge_fee_day2, ...

结论

通过物化视图+AggregatingMergeTree引擎，以空间换取时间的方式，可以显著提高数据库的查询效率。

ps: 我们常用的mysql这样的关系型数据库没有提供物化视图，但我们可以通过触发器自己实现一个物化视图，感兴趣的话可以自己研究或者以后业务上用到我们会再详细介绍。

参考文档

https://clickhouse.com/docs/en/sql-reference/data-types/simpleaggregatefunction
https://clickhouse.com/docs/en/sql-reference/statements/create/view/#materialized-view
https://tech.qimao.com/clickhouseshi-xian-shu-ju-de-you-xian-geng-xin/

展示评论

主题

后端: 44 移动端: 20 测试: 17 前端: 15 团队&文化: 15 大数据: 14 运维: 6 算法: 3