同步物化视图
本文介绍如何创建、使用和管理**同步物化视图（Rollup）**。
对于同步物化视图，基表中的所有更改都会同时更新到相应的同步物化视图。 同步物化视图的刷新是自动触发的。 同步物化视图的维护和更新成本非常低，适合透明地加速实时单表聚合查询。
StarRocks 中的同步物化视图只能在默认 Catalog中的单个基表上创建。 它们本质上是一种用于查询加速的特殊索引，而不是像异步物化视图那样的物理表。
从 v2.4 开始，StarRocks 提供了异步物化视图，支持在多张表上创建，并支持更多的聚合运算符。 有关**异步物化视图**的用法，请参见异步物化视图。
- 同步物化视图从 v3.1.8 起支持 WHERE 子句。
- 从 v3.4.0 开始，共享数据集群中支持同步物化视图。
下表从支持的功能角度比较了 StarRocks v2.5、v2.4 中的异步物化视图 (ASYNC MV) 和同步物化视图 (SYNC MV)
| 单表聚合 | 多表 Join | 查询重写 | 刷新策略 | 基表 | |
|---|---|---|---|---|---|
| ASYNC MV | 是 | 是 | 是 |
| 来自多个表
|
| SYNC MV (Rollup) | 聚合函数的选择有限 | 否 | 是 | 数据加载期间的同步刷新 | 默认 Catalog 中的单个表 |
基本概念
-
基表
基表是物化视图的驱动表。
对于 StarRocks 的同步物化视图，基表必须是来自默认 Catalog的单个原生表。 StarRocks 支持在 Duplicate Key 表和 Aggregate 表上创建同步物化视图。
-
刷新
同步物化视图会在基表中的数据每次更改时自行更新。 您无需手动触发刷新。
-
查询重写
查询重写意味着当在具有物化视图的基表上执行查询时，系统会自动判断物化视图中预先计算的结果是否可以重用于查询。 如果可以重用，系统将直接从相关的物化视图加载数据，以避免耗时且消耗资源的计算或连接。
同步物化视图支持基于某些聚合运算符的查询重写。 有关更多信息，请参见聚合函数的对应关系。
准备工作
在创建同步物化视图之前，请检查您的数据仓库是否符合通过同步物化视图进行查询加速的条件。 例如，检查查询是否重用某些子查询语句。
以下示例基于表 sales_records，其中包含事务 ID record_id、销售员 ID seller_id、商店 ID store_id、日期 sale_date 和每次交易的销售额 sale_amt。 按照以下步骤创建表并将数据插入其中
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() 函数，消耗大量的计算资源。 您可以运行查询来记录其时间，并使用 EXPLAIN 命令查看其查询 Profile。
MySQL > SELECT store_id, SUM(sale_amt)
FROM sales_records
GROUP BY store_id;
+----------+-----------------+
| store_id | sum(`sale_amt`) |
+----------+-----------------+
| 2 | 16463 |
| 3 | 12946 |
| 1 | 12892 |
+----------+-----------------+
3 rows in set (0.02 sec)
MySQL > EXPLAIN SELECT store_id, SUM(sale_amt)
FROM sales_records
GROUP BY store_id;
+-----------------------------------------------------------------------------+
| Explain String |
+-----------------------------------------------------------------------------+
| PLAN FRAGMENT 0 |
| OUTPUT EXPRS:3: store_id | 6: sum |
| PARTITION: UNPARTITIONED |
| |
| RESULT SINK |
| |
| 4:EXCHANGE |
| |
| PLAN FRAGMENT 1 |
| OUTPUT EXPRS: |
| PARTITION: HASH_PARTITIONED: 3: store_id |
| |
| STREAM DATA SINK |
| EXCHANGE ID: 04 |
| UNPARTITIONED |
| |
| 3:AGGREGATE (merge finalize) |
| | output: sum(6: sum) |
| | group by: 3: store_id |
| | |
| 2:EXCHANGE |
| |
| PLAN FRAGMENT 2 |
| OUTPUT EXPRS: |
| PARTITION: RANDOM |
| |
| STREAM DATA SINK |
| EXCHANGE ID: 02 |
| HASH_PARTITIONED: 3: store_id |
| |
| 1:AGGREGATE (update serialize) |
| | STREAMING |
| | output: sum(5: sale_amt) |
| | group by: 3: store_id |
| | |
| 0:OlapScanNode |
| TABLE: sales_records |
| PREAGGREGATION: ON |
| partitions=1/1 |
| rollup: sales_records |
| tabletRatio=10/10 |
| tabletList=12049,12053,12057,12061,12065,12069,12073,12077,12081,12085 |
| cardinality=1 |
| avgRowSize=2.0 |
| numNodes=0 |
+-----------------------------------------------------------------------------+
45 rows in set (0.00 sec)
可以观察到查询大约需要 0.02 秒，并且没有使用同步物化视图来加速查询，因为查询 Profile 中 rollup 字段的值为 sales_records，这是基表。
创建同步物化视图
您可以使用CREATE MATERIALIZED VIEW基于特定的查询语句创建同步物化视图。
基于表 sales_records 和上面提到的查询语句，以下示例创建同步物化视图 store_amt 来分析每个商店的销售额总和。
CREATE MATERIALIZED VIEW store_amt AS
SELECT store_id, SUM(sale_amt)
FROM sales_records
GROUP BY store_id;
注意
- 在同步物化视图中使用聚合函数时，必须使用 GROUP BY 子句并在 SELECT 列表中指定至少一个 GROUP BY 列。
- 同步物化视图不支持在一个列上使用一个聚合函数。 不支持
sum(a+b)形式的查询语句。- 同步物化视图不支持在一个列上使用多个聚合函数。 不支持
select sum(a), min(a) from table形式的查询语句。- 创建同步物化视图时不支持 JOIN。
- 使用 ALTER TABLE DROP COLUMN 删除基表中的特定列时，需要确保基表的所有同步物化视图都不包含已删除的列，否则无法执行删除操作。 要删除同步物化视图中使用的列，需要先删除包含该列的所有同步物化视图，然后再删除该列。
- 为一个表创建太多的同步物化视图会影响数据加载效率。 当数据加载到基表时，同步物化视图和基表中的数据会同步更新。 如果基表包含
n个同步物化视图，则将数据加载到基表的效率与将数据加载到n个表的效率大致相同。- 目前，StarRocks 不支持同时创建多个同步物化视图。 只有在前一个同步物化视图完成后才能创建新的同步物化视图。
- 物化视图只能在 default_catalog 中创建。 您可以使用 default_catalog.database.mv 创建它，也可以通过
set catalog <default_catalog>语句切换到 default_catalog。
检查同步物化视图的构建状态
创建同步物化视图是一个异步操作。 成功执行 CREATE MATERIALIZED VIEW 表明已成功提交创建物化视图的任务。 您可以通过SHOW ALTER MATERIALIZED VIEW查看数据库中同步物化视图的构建状态。
MySQL > SHOW ALTER MATERIALIZED VIEW\G
*************************** 1. row ***************************
JobId: 12090
TableName: sales_records
CreateTime: 2022-08-25 19:41:10
FinishedTime: 2022-08-25 19:41:39
BaseIndexName: sales_records
RollupIndexName: store_amt
RollupId: 12091
TransactionId: 10
State: FINISHED
Msg:
Progress: NULL
Timeout: 86400
1 row in set (0.00 sec)
RollupIndexName 部分指示同步物化视图的名称，State 部分指示构建是否完成。
直接查询同步物化视图
由于同步物化视图本质上是基表的索引而不是物理表，因此您只能使用提示 [_SYNC_MV_] 查询同步物化视图
-- Do not omit the brackets [] in the hint.
MySQL > SELECT * FROM store_amt [_SYNC_MV_];
+----------+----------+
| store_id | sale_amt |
+----------+----------+
| 2 | 6948 |
| 3 | 8734 |
| 1 | 4319 |
| 2 | 9515 |
| 3 | 4212 |
| 1 | 8573 |
+----------+----------+
注意
目前，即使您已为同步物化视图中的列指定别名，StarRocks 也会自动生成名称。
使用同步物化视图重写和加速查询
您创建的同步物化视图包含与查询语句一致的完整预先计算结果集。 后续查询使用其中的数据。 您可以运行相同的查询来测试查询时间，就像您在准备阶段所做的那样。
MySQL > SELECT store_id, SUM(sale_amt)
FROM sales_records
GROUP BY store_id;
+----------+-----------------+
| store_id | sum(`sale_amt`) |
+----------+-----------------+
| 2 | 16463 |
| 3 | 12946 |
| 1 | 12892 |
+----------+-----------------+
3 rows in set (0.01 sec)
可以观察到查询时间减少到 0.01 秒。
检查查询是否命中同步物化视图
再次执行 EXPLAIN 命令以检查查询是否命中同步物化视图。
MySQL > EXPLAIN SELECT store_id, SUM(sale_amt) FROM sales_records GROUP BY store_id;
+-----------------------------------------------------------------------------+
| Explain String |
+-----------------------------------------------------------------------------+
| PLAN FRAGMENT 0 |
| OUTPUT EXPRS:3: store_id | 6: sum |
| PARTITION: UNPARTITIONED |
| |
| RESULT SINK |
| |
| 4:EXCHANGE |
| |
| PLAN FRAGMENT 1 |
| OUTPUT EXPRS: |
| PARTITION: HASH_PARTITIONED: 3: store_id |
| |
| STREAM DATA SINK |
| EXCHANGE ID: 04 |
| UNPARTITIONED |
| |
| 3:AGGREGATE (merge finalize) |
| | output: sum(6: sum) |
| | group by: 3: store_id |
| | |
| 2:EXCHANGE |
| |
| PLAN FRAGMENT 2 |
| OUTPUT EXPRS: |
| PARTITION: RANDOM |
| |
| STREAM DATA SINK |
| EXCHANGE ID: 02 |
| HASH_PARTITIONED: 3: store_id |
| |
| 1:AGGREGATE (update serialize) |
| | STREAMING |
| | output: sum(5: sale_amt) |
| | group by: 3: store_id |
| | |
| 0:OlapScanNode |
| TABLE: sales_records |
| PREAGGREGATION: ON |
| partitions=1/1 |
| rollup: store_amt |
| tabletRatio=10/10 |
| tabletList=12092,12096,12100,12104,12108,12112,12116,12120,12124,12128 |
| cardinality=6 |
| avgRowSize=2.0 |
| numNodes=0 |
+-----------------------------------------------------------------------------+
45 rows in set (0.00 sec)
可以观察到查询 Profile 中 rollup 部分的值现在是 store_amt，这是您构建的同步物化视图。 这意味着此查询已命中同步物化视图。
显示同步物化视图
您可以执行 DESC <tbl_name> ALL 来检查表的架构及其从属同步物化视图。
MySQL > DESC sales_records ALL;
+---------------+---------------+-----------+--------+------+-------+---------+-------+
| IndexName | IndexKeysType | Field | Type | Null | Key | Default | Extra |
+---------------+---------------+-----------+--------+------+-------+---------+-------+
| sales_records | DUP_KEYS | record_id | INT | Yes | true | NULL | |
| | | seller_id | INT | Yes | true | NULL | |
| | | store_id | INT | Yes | true | NULL | |
| | | sale_date | DATE | Yes | false | NULL | NONE |
| | | sale_amt | BIGINT | Yes | false | NULL | NONE |
| | | | | | | | |
| store_amt | AGG_KEYS | store_id | INT | Yes | true | NULL | |
| | | sale_amt | BIGINT | Yes | false | NULL | SUM |
+---------------+---------------+-----------+--------+------+-------+---------+-------+
8 rows in set (0.00 sec)
删除同步物化视图
在以下情况下，您需要删除同步物化视图
- 您创建了一个错误的物化视图，需要在构建完成之前删除它。
- 您创建了太多的物化视图，导致加载性能大幅下降，并且某些物化视图是重复的。
- 涉及的查询频率较低，您可以容忍相对较高的查询延迟。
删除未完成的同步物化视图
您可以通过取消正在进行的创建任务来删除正在创建的同步物化视图。 首先，您需要通过检查物化视图的构建状态来获取物化视图创建任务的作业 ID JobID。 获取作业 ID 后，您需要使用 CANCEL ALTER 命令取消创建任务。
CANCEL ALTER TABLE ROLLUP FROM sales_records (12090);
删除现有的同步物化视图
您可以使用DROP MATERIALIZED VIEW 命令删除现有的同步物化视图。
DROP MATERIALIZED VIEW store_amt;
最佳实践
精确去重计数
以下示例基于广告业务分析表 advertiser_view_record，其中记录了广告的查看日期 click_time、广告的名称 advertiser、广告的渠道 channel 以及查看了 ID 的用户 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))
FROM advertiser_view_record
GROUP BY advertiser, channel;
创建同步物化视图后，后续查询中的子查询 count(distinct user_id) 将自动重写为 bitmap_union_count (to_bitmap(user_id))，以便它们可以命中同步物化视图。
近似去重计数
再次以上面的表 advertiser_view_record 为例。 为了加速近似去重计数，您可以基于此表创建一个同步物化视图，并使用hll_union() 函数来预先聚合数据。
CREATE MATERIALIZED VIEW advertiser_uv2 AS
SELECT advertiser, channel, hll_union(hll_hash(user_id))
FROM advertiser_view_record
GROUP BY advertiser, channel;
设置额外的排序键
假设基表 tableA 包含列 k1、k2 和 k3，其中只有 k1 和 k2 是排序键。 如果必须加速包含子查询 where k3=x 的查询，您可以创建一个以 k3 作为第一列的同步物化视图。
CREATE MATERIALIZED VIEW k3_as_key AS
SELECT k3, k2, k1
FROM tableA
聚合函数的对应关系
当使用同步物化视图执行查询时，原始查询语句将自动重写并用于查询存储在同步物化视图中的中间结果。 下表显示了原始查询中的聚合函数与用于构建同步物化视图的聚合函数之间的对应关系。 您可以选择相应的聚合函数来根据您的业务场景构建同步物化视图。
| 原始查询中的聚合函数 | 物化视图的聚合函数 |
|---|---|
| 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 |