本文介绍如何在 StarRocks 中创建、使用以及管理单表物化视图。有关多表异步刷新物化视图，请参阅 多表异步刷新物化视图。
StarRocks 中的单表物化视图（Rollup）是一种特殊的索引，无法直接查询。如果您的数据仓库中存在大量复杂或重复的查询，您可以通过创建单表物化视图加速查询。
创建单表物化视图前，您需要检查数据仓库是否需要通过单表物化视图加速查询。比如，您可以查看数据仓库中的查询是否重复使用特定子查询语句。
以下示例基于表 sales_records，其中包含每笔交易的交易 ID record_id、销售员 seller_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 命令查看此查询的 Query 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 秒，其 Query Profile 中的 rollup 项显示为 sales_records（即基表），说明该查询未使用物化视图加速。
您可以通过 CREATE MATERIALIZED VIEW 语句为特定查询语句创建单表物化视图。
以下示例根据上述查询语句，为表 sales_records 创建一个”以售卖门店为分组，对每一个售卖门店里的所有交易额求和”的单表物化视图。
CREATE MATERIALIZED VIEW store_amt AS SELECT store_id, SUM(sale_amt) FROM sales_records GROUP BY store_id;
复制
新建的单表物化视图将预计算并保存上述查询的结果，后续查询将直接调用该结果以加速查询。创建成功后，您可以再次运行同样的查询以测试查询时间。
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)
复制
可以看到，此时 Query Profile 中的 rollup 项显示为 store_amt（即单表物化视图），说明该查询已命中单表物化视图。
创建单表物化视图是一个异步的操作。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 项为 FINISHED，代表该单表物化视图构建完成。
您可以通过 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 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 为例，如果想在查询点击广告的 UV 时实现近似去重查询加速，可基于该明细表创建一张单表物化视图，并使用 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 |
单表物化视图仅支持单列聚合函数，不支持形如
sum(a+b)的查询语句。单表物化视图创建语句不支持 JOIN、WHERE 等子句。
当前版本暂时不支持同时创建多个单表物化视图。仅当当前创建任务完成时，方可执行下一个创建任务。
一个单表物化视图仅支持对同一列数据使用一种聚合函数，不支持形如
select sum(a), min(a) from table的查询语句。使用 ALTER TABLE DROP COLUMN 删除基表中特定列时，需要保证该基表所有单表物化视图中都包含被删除列，否则无法进行删除操作。如果必须删除该列，则需要将所有包含该列的单表物化视图删除，然后进行删除列操作。
为一张表创建过多的单表物化视图会影响导入的效率。导入数据时，单表物化视图和基表数据将同步更新，如果一张基表包含 n 个单表物化视图，向基表导入数据时，其导入效率大约等同于导入 n 张表，数据导入的速度会变慢。
当前版本单表物化视图中使用聚合函数需要与 GROUP BY 语句一起使用，且 SELECT LIST 中至少包含一个分组列。
