同步物化视图
更新时间：2025-05-29
什么是同步物化视图
同步物化视图是将预先计算（根据定义好的 SELECT 语句）的数据集，存储在 Doris 中的一个特殊的表。Doris 会自动维护同步物化视图的数据，无论是新的导入还是删除操作，都能保证 Base 表和物化视图表的数据同步更新、保持一致后，相关命令才会结束，无需任何额外的人工维护成本。查询时，Doris 会自动匹配到最优的物化视图，并直接从物化视图中读取数据。
适用场景
- 加速耗时的聚合运算
- 查询需要匹配不同的前缀索引
- 通过预先过滤减少需要扫描的数据量
- 通过预先完成复杂的表达式计算来加速查询
局限性
- 同步物化视图只支持针对单个表的 SELECT 语句，支持 WHERE、GROUP BY、ORDER BY 等子句，但不支持 JOIN、HAVING、LIMIT 子句和 LATERAL VIEW。
- 与异步物化视图不同，不能直接查询同步物化视图。
- SELECT 列表中，不能包含自增列，不能包含常量，不能有重复表达式，也不支持窗口函数。
- 如果 SELECT 列表包含聚合函数，则聚合函数必须是根表达式（不支持 sum(a) + 1，支持 sum(a + 1)），且聚合函数之后不能有其他非聚合函数表达式（例如，SELECT x, sum(a) 可以，而 SELECT sum(a), x 不行）。
- 如果删除语句的条件列在物化视图中存在，则不能进行删除操作。如果确实需要删除数据，则需要先将物化视图删除，然后才能删除数据。
- 单表上过多的物化视图会影响导入的效率：导入数据时，物化视图和 Base 表的数据是同步更新的。如果一张表的物化视图表过多，可能会导致导入速度变慢，这就像单次导入需要同时导入多张表的数据一样。
- 物化视图针对 Unique Key 数据模型时，只能改变列的顺序，不能起到聚合的作用。因此，在 Unique Key 模型上不能通过创建物化视图的方式对数据进行粗粒度的聚合操作。
使用物化视图
Doris 系统提供了一整套针对物化视图的 DDL 语法，包括创建、查看和删除。下面通过一个示例来展示如何使用物化视图加速聚合计算。假设用户有一张销售记录明细表，该表存储了每个交易的交易 ID、销售员、售卖门店、销售时间以及金额。建表语句和插入数据语句如下：
Plain Text
1-- 创建一个 test_db
2create database test_db;
3use test_db;
4
5-- 创建表
6create table sales_records
7(
8 record_id int,
9 seller_id int,
10 store_id int,
11 sale_date date,
12 sale_amt bigint
13)
14distributed by hash(record_id)
15properties("replication_num" = "1");
16
17-- 插入数据
18insert into sales_records values(1,1,1,'2020-02-02',1);创建物化视图
如果用户经常需要分析不同卖场的销售量，则可以为sales_records表创建一个物化视图，该视图以售卖码头分组，视图相同售卖码头的创建售卖进行求和。语句如下：
Plain Text
1create materialized view store_amt as
2select store_id, sum(sale_amt) from sales_records group by store_id;检查物化视图是否创建
由于创建物化视图是一个异步操作，用户在提交创建物化视图任务后，需要异步地通过命令检查物化视图是否构建完成。命令如下：
Plain Text
1show alter table materialized view from test_db;该命令的结果将显示该数据库的所有创建物化视图的任务。结果示例如下：
Plain Text
1+--------+---------------+---------------------+---------------------+---------------+-----------------+----------+---------------+----------+------+----------+---------+
2| JobId | TableName | CreateTime | FinishTime | BaseIndexName | RollupIndexName | RollupId | TransactionId | State | Msg | Progress | Timeout |
3+--------+---------------+---------------------+---------------------+---------------+-----------------+----------+---------------+----------+------+----------+---------+
4| 494349 | sales_records | 2020-07-30 20:04:56 | 2020-07-30 20:04:57 | sales_records | store_amt | 494350 | 133107 | FINISHED | | NULL | 2592000 |
5+--------+---------------+---------------------+---------------------+---------------+-----------------+----------+---------------+----------+------+----------+---------+其中，TableName是指物化视图的数据来源表，RollupIndexName指的是物化视图的名称。比较重要的指标是State。当创建物化视图任务的State等于FINISHED时，就说明这个物化视图已经成功创建了。这意味着，在执行查询时有可能自动匹配到这个物化视图。
取消创建物化视图
如果创建物化视图的后台异步任务尚未结束，可以通过以下命令取消任务：
Plain Text
1cancel alter table materialized view from test_db.sales_records;查看物化视图的表格结构
可以通过以下命令查看目标表上创建的所有物化视图及其表结构：
Plain Text
1desc sales_records all;内容目的：
Plain Text
1+---------------+---------------+---------------------+--------+--------------+------+-------+---------+-------+---------+------------+-------------+
2| IndexName | IndexKeysType | Field | Type | InternalType | Null | Key | Default | Extra | Visible | DefineExpr | WhereClause |
3+---------------+---------------+---------------------+--------+--------------+------+-------+---------+-------+---------+------------+-------------+
4| sales_records | DUP_KEYS | record_id | INT | INT | Yes | true | NULL | | true | | |
5| | | seller_id | INT | INT | Yes | true | NULL | | true | | |
6| | | store_id | INT | INT | Yes | true | NULL | | true | | |
7| | | sale_date | DATE | DATEV2 | Yes | false | NULL | NONE | true | | |
8| | | sale_amt | BIGINT | BIGINT | Yes | false | NULL | NONE | true | | |
9| | | | | | | | | | | | |
10| store_amt | AGG_KEYS | mv_store_id | INT | INT | Yes | true | NULL | | true | `store_id` | |
11| | | mva_SUM__`sale_amt` | BIGINT | BIGINT | Yes | false | NULL | SUM | true | `sale_amt` | |
12+---------------+---------------+---------------------+--------+--------------+------+-------+---------+-------+---------+------------+-------------+可以看到，sales_records有一个名为store_amt的物化视图，这个物化视图就是前序创建步骤的。
查看物化视图的创建语句
可以通过以下命令查看物化视图的创建语句：
Plain Text
1show create materialized view store_amt on sales_records;显示如下：
Plain Text
1+---------------+-----------+------------------------------------------------------------------------------------------------------------+
2| TableName | ViewName | CreateStmt |
3+---------------+-----------+------------------------------------------------------------------------------------------------------------+
4| sales_records | store_amt | create materialized view store_amt as select store_id, sum(sale_amt) from sales_records group by store_id |
5+---------------+-----------+------------------------------------------------------------------------------------------------------------+查询物化情况
当物化视图完成后，用户在查询不同门店的销售量时，Doris会直接从刚才创建的物化视图store_amt中读取聚合好的数据，从而创建提升查询效率。用户的查询依然指定查询sales_records表，比如：
Plain Text
1select store_id, sum(sale_amt) from sales_records group by store_id;上面的查询能够自动匹配到store_amt。用户可以通过下面的命令，检查当前查询是否匹配到了合适的物化视图。
Plain Text
1explain select store_id, sum(sale_amt) from sales_records group by store_id;结果如下：
Plain Text
1+-----------------------------------------------------------------------------------------------+
2| Explain String(Nereids Planner) |
3+-----------------------------------------------------------------------------------------------+
4| PLAN FRAGMENT 0 |
5| OUTPUT EXPRS: |
6| store_id[#11] |
7| sum(sale_amt)[#12] |
8| PARTITION: HASH_PARTITIONED: mv_store_id[#7] |
9| |
10| HAS_COLO_PLAN_NODE: false |
11| |
12| VRESULT SINK |
13| MYSQL_PROTOCAL |
14| |
15| 3:VAGGREGATE (merge finalize)(145) |
16| | output: sum(partial_sum(mva_SUM__sale_amt)[#8])[#10] |
17| | group by: mv_store_id[#7] |
18| | sortByGroupKey:false |
19| | cardinality=1 |
20| | final projections: mv_store_id[#9], sum(mva_SUM__sale_amt)[#10] |
21| | final project output tuple id: 4 |
22| | distribute expr lists: mv_store_id[#7] |
23| | |
24| 2:VEXCHANGE |
25| offset: 0 |
26| distribute expr lists: |
27| |
28| PLAN FRAGMENT 1 |
29| |
30| PARTITION: HASH_PARTITIONED: record_id[#2] |
31| |
32| HAS_COLO_PLAN_NODE: false |
33| |
34| STREAM DATA SINK |
35| EXCHANGE ID: 02 |
36| HASH_PARTITIONED: mv_store_id[#7] |
37| |
38| 1:VAGGREGATE (update serialize)(139) |
39| | STREAMING |
40| | output: partial_sum(mva_SUM__sale_amt[#1])[#8] |
41| | group by: mv_store_id[#0] |
42| | sortByGroupKey:false |
43| | cardinality=1 |
44| | distribute expr lists: |
45| | |
46| 0:VOlapScanNode(136) |
47| TABLE: test_db.sales_records(store_amt), PREAGGREGATION: ON |
48| partitions=1/1 (sales_records) |
49| tablets=10/10, tabletList=494505,494507,494509 ... |
50| cardinality=1, avgRowSize=0.0, numNodes=1 |
51| pushAggOp=NONE |
52| |
53| |
54| Statistics |
55| planed with unknown column statistics |
56+-----------------------------------------------------------------------------------------------+从底部VOlapScanNode的test_db.sales_records(store_amt)可以表明，该查询命中了store_amt这个物化视图。值得注意的是，如果表中没有数据，那么可能不会命中物化视图。
删除物化视图
Plain Text
1drop materialized view store_amt on sales_records;使用示例
接下来，我们通过更多的例子来展示物化视图的作用。
示例一：加速聚合查询
业务场景：计算广告的UV（独立访客数）和PV（页面访问量）。
假设用户的原始广告点击数据存储在Doris中，那么针对广告PV和UV的查询就可以通过创建标记的bitmap_union物化视图来提升创建查询速度。首先，一个广告点击数据存储明细的表，包含每条点击的点击时间、点击的广告、点击的渠道以及点击的用户。原始表创建语句如下：
Plain Text
1create table advertiser_view_record
2(
3 click_time datetime,
4 advertiser varchar(10),
5 channel varchar(10),
6 user_id int
7) distributed by hash(user_id) properties("replication_num" = "1");
8insert into advertiser_view_record values("2020-02-02 02:02:02",'a','a',1);用户查询的是广告的UV值，显然需要对广告的用户进行准确去重，查询语句一般为：
Plain Text
1select
2 advertiser,
3 channel,
4 count(distinct user_id)
5from
6 advertiser_view_record
7group by
8 advertiser, channel;针对这种求UV的场景，可以创建一个标记的bitmap_union物化视图，以达到准确去重的效果。在Doris中，count(distinct)聚合的结果和bitmap_union_count聚合的结果是一致的。因此，如果查询到涉及到的内容count(distinct)，则通过创建标记bitmap_union聚合的物化视图可以加快查询。根据当前的使用场景，可以根据广告和通道分组创建一个，对user_id进行精确去重的物化视图。
Plain Text
1create materialized view advertiser_uv as
2select
3 advertiser,
4 channel,
5 bitmap_union(to_bitmap(user_id))
6from
7 advertiser_view_record
8group by
9 advertiser, channel;当物化视图表创建完成后，查询广告UV时，Doris就会自动从刚才创建好的物化视图advertiser_uv中查询数据。如果执行之前的SQL查询：
Plain Text
1select
2 advertiser,
3 channel,
4 count(distinct user_id)
5from
6 advertiser_view_record
7group by
8 advertiser, channel;在选中物化视图后，实际的查询会转化为：
Plain Text
1select
2 advertiser,
3 channel,
4 bitmap_union_count(to_bitmap(user_id))
5from
6 advertiser_uv
7group by
8 advertiser, channel;通过explain检查查询是否符合了物化视图：命令命令
Plain Text
1explain select
2 advertiser,
3 channel,
4 count(distinct user_id)
5from
6 advertiser_view_record
7group by
8 advertiser, channel;显示结果如下：
Plain Text
1+-------------------------------------------------------------------------------------------------------------------------------------------------------+
2| Explain String(Nereids Planner) |
3+-------------------------------------------------------------------------------------------------------------------------------------------------------+
4| PLAN FRAGMENT 0 |
5| OUTPUT EXPRS: |
6| advertiser[#13] |
7| channel[#14] |
8| count(DISTINCT user_id)[#15] |
9| PARTITION: HASH_PARTITIONED: mv_advertiser[#7], mv_channel[#8] |
10| |
11| HAS_COLO_PLAN_NODE: false |
12| |
13| VRESULT SINK |
14| MYSQL_PROTOCAL |
15| |
16| 3:VAGGREGATE (merge finalize)(145) |
17| | output: bitmap_union_count(partial_bitmap_union_count(mva_BITMAP_UNION__to_bitmap_with_check(cast(user_id as BIGINT)))[#9])[#12] |
18| | group by: mv_advertiser[#7], mv_channel[#8] |
19| | sortByGroupKey:false |
20| | cardinality=1 |
21| | final projections: mv_advertiser[#10], mv_channel[#11], bitmap_union_count(mva_BITMAP_UNION__to_bitmap_with_check(cast(user_id as BIGINT)))[#12] |
22| | final project output tuple id: 4 |
23| | distribute expr lists: mv_advertiser[#7], mv_channel[#8] |
24| | |
25| 2:VEXCHANGE |
26| offset: 0 |
27| distribute expr lists: |
28| |
29| PLAN FRAGMENT 1 |
30| |
31| PARTITION: HASH_PARTITIONED: user_id[#6] |
32| |
33| HAS_COLO_PLAN_NODE: false |
34| |
35| STREAM DATA SINK |
36| EXCHANGE ID: 02 |
37| HASH_PARTITIONED: mv_advertiser[#7], mv_channel[#8] |
38| |
39| 1:VAGGREGATE (update serialize)(139) |
40| | STREAMING |
41| | output: partial_bitmap_union_count(mva_BITMAP_UNION__to_bitmap_with_check(cast(user_id as BIGINT))[#2])[#9] |
42| | group by: mv_advertiser[#0], mv_channel[#1] |
43| | sortByGroupKey:false |
44| | cardinality=1 |
45| | distribute expr lists: |
46| | |
47| 0:VOlapScanNode(136) |
48| TABLE: test_db.advertiser_view_record(advertiser_uv), PREAGGREGATION: ON |
49| partitions=1/1 (advertiser_view_record) |
50| tablets=10/10, tabletList=494552,494554,494556 ... |
51| cardinality=1, avgRowSize=0.0, numNodes=1 |
52| pushAggOp=NONE |
53| |
54| |
55| Statistics |
56| planed with unknown column statistics |
57+-------------------------------------------------------------------------------------------------------------------------------------------------------+在explain的结果中，可以看到底部VOlapScanNode的advertiser_view_record(advertiser_uv)。其次，查询会直接扫描物化视图的数据，说明匹配成功。 其次，对于user_id字段求count(distinct)被改写为求bitmap_union_count(to_bitmap)，那么通过位图的方式来达到精确去重的效果。
译文二：匹配不同的索引
业务场景：匹配索引
用户的原始表包含三列（k1,k2,k3），其中k1和k2被设置为远端索引列。当用户查询中包含时where k1=1 and k2=2，查询可以通过索引进行加速。然而，在某些情况下，用户的过滤条件可能无法匹配到远端索引，例如where k3=3，此时无法通过索引来提升速度查询。为了解决这个问题，我们创建一个k3作为第一列的物化视图。
建表语句和插入数据语句如下：
Plain Text
1create table test_table
2(
3 k1 int,
4 k2 int,
5 k3 int,
6 kx date
7)
8distributed by hash(k1)
9properties("replication_num" = "1");
10
11insert into test_table values(1,1,1,1);创建 k3 为相邻索引的物化视图：
Plain Text
1create materialized view mv_1 as SELECT k3, k2, k1 FROM test_table;使用 EXPLAIN 检查查询是否匹配物化视图：
Plain Text
1explain select k1, k2, k3 from test_table where k3=3;显示结果如下：
Plain Text
1+---------------------------------------------------------------------------------------+
2| Explain String(Nereids Planner) |
3+---------------------------------------------------------------------------------------+
4| PLAN FRAGMENT 0 |
5| OUTPUT EXPRS: |
6| mv_k1[#2] |
7| mv_k2[#1] |
8| mv_k3[#0] |
9| PARTITION: HASH_PARTITIONED: mv_k1[#2] |
10| |
11| HAS_COLO_PLAN_NODE: false |
12| |
13| VRESULT SINK |
14| MYSQL_PROTOCAL |
15| |
16| 0:VOlapScanNode(112) |
17| TABLE: test_db.test_table(mv_1), PREAGGREGATION: ON |
18| PREDICATES: (mv_k3[#0] = 3) |
19| partitions=1/1 (test_table) |
20| tablets=10/10, tabletList=494599,494601,494603 ... |
21| cardinality=0, avgRowSize=0.0, numNodes=1 |
22| pushAggOp=NONE |
23| |
24| |
25| Statistics |
26| planed with unknown column statistics |
27+---------------------------------------------------------------------------------------+在 EXPLAIN 的结果中，可以看到底部 VOlapScanNode 的test_table(mv_1)，这表明查询成功命中了物化视图。
示例三：初步过滤和表达式计算加速
业务场景：需要提前过滤数据或加速表达式计算。
建表和插入数据语句如下：
Plain Text
1create table d_table (
2 k1 int null,
3 k2 int not null,
4 k3 bigint null,
5 k4 date null
6)
7duplicate key (k1,k2,k3)
8distributed BY hash(k1) buckets 3
9properties("replication_num" = "1");
10
11insert into d_table select 1,1,1,'2020-02-20';
12insert into d_table select 2,2,2,'2021-02-20';
13insert into d_table select 3,-3,null,'2022-02-20';创建物质景观：
Plain Text
1-- mv1 提前进行表达式计算
2create materialized view mv1 as
3select
4 abs(k1)+k2+1,
5 sum(abs(k2+2)+k3+3)
6from
7 d_table
8group by
9 abs(k1)+k2+1;
10
11-- mv2 提前用 where 表达式过滤以减少物化视图中的数据量
12create materialized view mv2 as
13select
14 year(k4),
15 month(k4)
16from
17 d_table
18where
19 year(k4) = 2020;通过查询测试检测是否成功命中物化视图：
Plain Text
1-- 命中 mv1
2select
3 abs(k1)+k2+1,
4 sum(abs(k2+2)+k3+3)
5from
6 d_table
7group by
8 abs(k1)+k2+1;
9
10-- 命中 mv1
11select
12 bin(abs(k1)+k2+1),
13 sum(abs(k2+2)+k3+3)
14from
15 d_table
16group by
17 bin(abs(k1)+k2+1);
18
19-- 命中 mv2
20select
21 year(k4) + month(k4)
22from
23 d_table
24where
25 year(k4) = 2020;
26
27-- 命中原始表 d_table 不会命中 mv2，因为 where 条件不匹配
28select
29 year(k4),
30 month(k4)
31from
32 d_table;常见问题
- 当创建好物化视图后，为什么没有改写成功？
如果发现没有匹配的数据，可能是因为物化视图还在一个构建过程中。此时，可以使用以下命令来查看物化视图的构建状态：
Plain Text
1show alter table materialized view from test_db;如果查询结果显示status字段不是FINISHED，则需要等待，直到状态变为FINISHED后，物化视图才会变得可用。