使用同步物化视图实现单表聚合查询加速-开源大数据平台 E-MapReduce-阿里云-开源大数据平台 E-MapReduce(EMR)-阿里云帮助中心

使用物化视图（materialized view）可以进行查询加速和数仓分层。同步物化视图下，所有对于基表的数据变更都会自动同步更新到物化视图中。您无需手动调用刷新命令，即可实现自动同步刷新物化视图。同步物化视图的管理成本和更新成本都比较低，适合实时场景下单表聚合查询的透明加速。

背景信息

StarRocks中的同步物化视图仅能基于Default Catalog中的单个基表创建，是一种特殊的查询加速索引。

基本概念

概念	说明
基表（Base Table）	物化视图的驱动表。对于同步物化视图，基表只能是Default Catalog中的单个内部表。StarRocks支持在明细表、聚合表上创建同步物化视图。
刷新（Refresh）	同步物化视图中的数据将在数据导入基表时自动更新，无需手动执行刷新命令。
查询改写（Query Rewrite）	查询改写是指在对已构建了物化视图的基表进行查询时，系统自动判断是否可以复用物化视图中的预计算结果处理查询。如果可以复用，系统会直接从相关的物化视图读取预计算结果，以避免重复计算消耗系统资源和时间。同步物化视图支持部分聚合算子的查询改写。详细信息，请参见聚合函数匹配关系。

物化视图对比

物化视图

单表聚合

多表关联

查询改写

刷新策略

基表

异步物化视图

是

异步刷新
手动刷新

支持多表构建。基表可以来自：

Default Catalog
External Catalog
已有异步物化视图
已有视图

同步物化视图（Rollup）

仅部分聚合函数

否

是

导入同步刷新

仅支持基于 Default Catalog 的单表构建

聚合函数匹配关系

在使用同步物化视图查询时，原始查询语句将会被自动改写并用于查询同步物化视图中保存的中间结果。以下表格展示了原始查询中使用的聚合函数与构建同步物化视图时相应的聚合函数之间的匹配关系。您可以根据具体的业务场景选择合适的聚合函数来构建同步物化视图。

原始查询聚合函数	物化视图构建聚合函数
sum	sum
min	min
max	max
count	count
bitmap_union, bitmap_union_count, count(distinct)	bitmap_union
hll_raw_agg, hll_union_agg, ndv, approx_count_distinct	hll_union

使用限制

同步物化视图自v3.1.8起支持WHERE子句。
存算分离版实例暂不支持同步物化视图。

准备工作

在创建同步物化视图之前，您需检查数据仓库是否确实需要通过同步物化视图来加速查询。例如，您可以分析数据仓库中的查询是否重复使用特定的子查询语句。

以下示例基于表sales_records，该表包含每笔交易的交易 ID（record_id）、销售员 ID（seller_id）、售卖门店 ID（store_id）、销售时间（sale_date）以及销售额（sale_amt）。

请根据以下数据建立表格并导入相应数据。

CREATE DATABASE IF NOT EXISTS load_test;

USE load_test;
  
CREATE TABLE sales_records(
    record_id INT,
    seller_id INT,
    store_id INT,
    sale_date DATE,
    sale_amt BIGINT
) DISTRIBUTED BY HASH(record_id);

INSERT INTO sales_records
VALUES
    (001,01,1,"2022-03-13",8573),
    (002,02,2,"2022-03-14",6948),
    (003,01,1,"2022-03-14",4319),
    (004,03,3,"2022-03-15",8734),
    (005,03,3,"2022-03-16",4212),
    (006,02,2,"2022-03-17",9515);

查询消耗时间。
该示例业务场景需要频繁分析不同门店的销售额，因此查询需要大量调用sum() 函数，耗费大量系统资源。
```
SELECT store_id, SUM(sale_amt)
FROM load_test.sales_records
GROUP BY store_id;
```
返回信息如下所示。可以看到此时查询时间为25 ms。
使用EXPLAIN命令查看此查询的Query Profile。
```
EXPLAIN SELECT store_id, SUM(sale_amt)
FROM load_test.sales_records
GROUP BY store_id;
```
可以看到返回信息中的rollup项显示为sales_records（即基表），说明该查询未使用物化视图加速。

创建同步物化视图

您可以通过执行CREATE MATERIALIZED VIEW语句，为特定查询创建同步物化视图。

说明

使用聚合函数的要求：
- 查询语句必须包含GROUP BY子句，并且在SELECT列表中至少需包含一个分组列。
- 不支持对多列使用单个聚合函数，例如SUM(a+b)。
- 不支持在同一列上使用多个聚合函数，例如SELECT SUM(a), MIN(a) FROM table。
同步物化视图的创建语句不支持JOIN操作。
使用ALTER TABLE DROP COLUMN删除基表中的特定列时，必须确保该列在所有同步物化视图中均未使用，否则将无法进行删除操作。若需删除该列，需先删除所有引用该列的同步物化视图，然后再进行列的删除。
为一张表创建过多的同步物化视图会降低数据导入的效率。在向基表导入数据时，同步物化视图和基表数据同步更新。如果一张基表有n个同步物化视图，导入数据的效率将类似于导入n张表，进而导致速度减慢。
当前不支持同时创建多个同步物化视图。必须等当前任务完成后才能开始下一个创建任务。

以下示例依据上述查询语句，为表sales_records创建一个“基于售卖门店分组，对每个售卖门店内的所有交易额进行求和”的同步物化视图。

CREATE MATERIALIZED VIEW store_amt AS
SELECT store_id, SUM(sale_amt)
FROM load_test.sales_records
GROUP BY store_id;

查看同步物化视图构建状态

创建同步物化视图是一个异步的操作。CREATE MATERIALIZED VIEW 命令执行成功即代表创建同步物化视图的任务提交成功。您可以通过SHOW ALTER MATERIALIZED VIEW命令查看当前数据库中同步物化视图的构建状态。

USE load_test;
SHOW ALTER MATERIALIZED VIEW;

本示例返回信息如下表所示，其中RollupIndexName为同步物化视图名称（store_amt），State项为FINISHED，代表该同步物化视图构建完成。

查询同步物化视图

因为同步物化视图本质上是基表的索引而不是物理表，所以您只能使用Hint[_SYNC_MV_]查询同步物化视图。

说明

目前，StarRocks会自动为同步物化视图中的列生成名称。您为同步物化视图中的列指定的Alias将无法生效。

SELECT * FROM <mv_name> [_SYNC_MV_];-- 请勿省略Hint中的括号[]。

本文示例中，执行以下命令查看同步物化视图。

USE load_test;
SELECT * FROM store_amt [_SYNC_MV_];

使用同步物化视图改写加速查询

新建的同步物化视图将预计算并保存上述查询的结果，后续查询将直接调用该结果以加速查询。创建成功后，您可以再次运行同样的查询以测试查询时间。

SELECT store_id, SUM(sale_amt)
FROM load_test.sales_records
GROUP BY store_id;

返回信息如下所示。可以看到此时查询时间为17 ms，查询时间已比之前有所缩短。

验证查询是否命中同步物化视图

您可以再次使用EXPLAIN命令查看该查询是否命中同步物化视图。

EXPLAIN SELECT store_id, SUM(sale_amt)
FROM load_test.sales_records
GROUP BY store_id;

可以查看返回信息，此时Query Profile中的rollup项显示为store_amt（即同步物化视图），说明该查询已命中同步物化视图。

查看同步物化视图的表结构

您可以通过DESC <tbl_name> ALL命令查看特定表的表结构及其下属所有同步物化视图。

USE load_test;
DESC sales_records ALL;

返回示例如下所示。

删除同步物化视图

删除同步物化视图的情况如下：

创建错误：当同步物化视图创建过程中出现错误时，需要将其删除。
过多视图导致性能下降：如果创建了大量同步物化视图，可能会导致数据导入速度显著减慢，同时可能存在部分同步物化视图重复。
查询需求低：在查询频率较低且业务场景能够容忍较高查询延迟的情况下，考虑删除相应的同步物化视图。

删除正在创建的同步物化视图

可以通过取消当前正在进行的同步物化视图创建任务，以删除正在创建的同步物化视图。首先需要通过查看同步物化视图构建状态获取该同步物化视图的任务的JobID，得到任务ID后，需要通过CANCEL ALTER命令取消该创建任务。

CANCEL ALTER TABLE ROLLUP FROM sales_records (12090);

删除已创建的同步物化视图

可以通过DROP MATERIALIZED VIEW <mv_name>命令删除已创建的同步物化视图。

USE load_test;
DROP MATERIALIZED VIEW store_amt;

最佳实践

精确去重

以下示例基于广告业务相关的详细表 advertiser_view_record，记录了点击日期 click_time、广告客户 advertiser、点击渠道 channel 以及点击用户 ID user_id。

CREATE TABLE advertiser_view_record (
    click_time DATE,
    advertiser VARCHAR(10),
    channel VARCHAR(10),
    user_id INT
) DISTRIBUTED BY HASH(click_time);

该场景需要频繁使用以下语句查询点击广告的UV。

SELECT advertiser, channel, count(distinct user_id)
FROM advertiser_view_record
GROUP BY advertiser, channel;

为了加速精确去重的查询，可以基于该明细表创建一个同步物化视图，并使用 bitmap_union() 函数进行预聚合。

CREATE MATERIALIZED VIEW advertiser_uv AS
SELECT advertiser, channel, bitmap_union(to_bitmap(user_id)) AS user_bitmap
FROM advertiser_view_record
GROUP BY advertiser, channel;

同步物化视图创建完成后，后续查询的 COUNT(DISTINCT user_id) 会被自动改写为 bitmap_union_count(to_bitmap(user_id))，使得查询能够更加高效地命中物化视图。

近似去重

以上文的advertiser_view_record表为例，如果需要在UV查询时实现近似去重，可以基于该明细表创建一个同步物化视图，并使用 hll_union() 函数预先聚合数据。

CREATE MATERIALIZED VIEW advertiser_uv2 AS
SELECT advertiser, channel, hll_union(hll_hash(user_id)) AS user_hll
FROM advertiser_view_record
GROUP BY advertiser, channel;

增设前缀索引

例如，基表 tableA 包含列 k1、k2 和 k3，其中 k1 和 k2 为排序键。如果业务场景需要在查询中包含子查询 WHERE k3 = x并通过前缀索引加速查询，则可以创建以 k3 为第一列的同步物化视图。您可以在查询中利用 k3 作为索引，以提高检索速度，从而有效减少查询响应时间。

CREATE MATERIALIZED VIEW k3_as_key AS
SELECT k3, k2, k1
FROM tableA;