跳到主要内容
跳到主要内容

同步物化视图

什么是同步物化视图

同步物化视图是将预先计算(根据定义好的 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、销售员、售卖门店、销售时间以及金额。建表语句和插入数据语句如下:

-- 创建一个 test_db
create database test_db;
use test_db;

-- 创建表
create table sales_records
(
record_id int,
seller_id int,
store_id int,
sale_date date,
sale_amt bigint
)
distributed by hash(record_id)
properties("replication_num" = "1");

-- 插入数据
insert into sales_records values(1,1,1,'2020-02-02',1);

创建物化视图

如果用户经常需要分析不同门店的销售量,则可以为 sales_records 表创建一个物化视图,该视图以售卖门店分组,并对相同售卖门店的销售额进行求和。创建语句如下:

create materialized view store_amt as 
select store_id, sum(sale_amt) from sales_records group by store_id;

检查物化视图是否创建完成

由于创建物化视图是一个异步操作,用户在提交创建物化视图任务后,需要异步地通过命令检查物化视图是否构建完成。命令如下:

show alter table materialized view from test_db;

该命令的结果将显示该数据库的所有创建物化视图的任务。结果示例如下:

+--------+---------------+---------------------+---------------------+---------------+-----------------+----------+---------------+----------+------+----------+---------+
| JobId | TableName | CreateTime | FinishTime | BaseIndexName | RollupIndexName | RollupId | TransactionId | State | Msg | Progress | Timeout |
+--------+---------------+---------------------+---------------------+---------------+-----------------+----------+---------------+----------+------+----------+---------+
| 494349 | sales_records | 2020-07-30 20:04:56 | 2020-07-30 20:04:57 | sales_records | store_amt | 494350 | 133107 | FINISHED | | NULL | 2592000 |
+--------+---------------+---------------------+---------------------+---------------+-----------------+----------+---------------+----------+------+----------+---------+

其中,TableName 指的是物化视图的数据来源表,RollupIndexName 指的是物化视图的名称。比较重要的指标是 State。当创建物化视图任务的 State 变为 FINISHED 时,就说明这个物化视图已经创建成功了。这意味着,在执行查询时有可能自动匹配到这张物化视图。

取消创建物化视图

如果创建物化视图的后台异步任务还未结束,可以通过以下命令取消任务:

cancel alter table materialized view from test_db.sales_records;

如果物化视图已经创建完毕,则无法通过该命令取消创建,但可以通过删除命令来删除物化视图。

查看物化视图的表结构

可以通过以下命令查看目标表上创建的所有物化视图及其表结构:

desc sales_records all;

该命令的结果如下:

+---------------+---------------+---------------------+--------+--------------+------+-------+---------+-------+---------+------------+-------------+
| IndexName | IndexKeysType | Field | Type | InternalType | Null | Key | Default | Extra | Visible | DefineExpr | WhereClause |
+---------------+---------------+---------------------+--------+--------------+------+-------+---------+-------+---------+------------+-------------+
| sales_records | DUP_KEYS | record_id | INT | INT | Yes | true | NULL | | true | | |
| | | seller_id | INT | INT | Yes | true | NULL | | true | | |
| | | store_id | INT | INT | Yes | true | NULL | | true | | |
| | | sale_date | DATE | DATEV2 | Yes | false | NULL | NONE | true | | |
| | | sale_amt | BIGINT | BIGINT | Yes | false | NULL | NONE | true | | |
| | | | | | | | | | | | |
| store_amt | AGG_KEYS | mv_store_id | INT | INT | Yes | true | NULL | | true | `store_id` | |
| | | mva_SUM__`sale_amt` | BIGINT | BIGINT | Yes | false | NULL | SUM | true | `sale_amt` | |
+---------------+---------------+---------------------+--------+--------------+------+-------+---------+-------+---------+------------+-------------+

可以看到,sales_records有一个名叫store_amt的物化视图,这个物化视图就是前序步骤创建的。

查看物化视图的创建语句

可以通过以下命令查看物化视图的创建语句:

show create materialized view store_amt on sales_records;

输出如下:

+---------------+-----------+------------------------------------------------------------------------------------------------------------+
| TableName | ViewName | CreateStmt |
+---------------+-----------+------------------------------------------------------------------------------------------------------------+
| sales_records | store_amt | create materialized view store_amt as select store_id, sum(sale_amt) from sales_records group by store_id |
+---------------+-----------+------------------------------------------------------------------------------------------------------------+

查询物化视图

当物化视图创建完成后,用户在查询不同门店的销售量时,Doris 会直接从刚才创建的物化视图store_amt中读取聚合好的数据,从而提升查询效率。用户的查询依旧指定查询sales_records表,比如:

select store_id, sum(sale_amt) from sales_records group by store_id;

上面的查询就能自动匹配到store_amt。用户可以通过下面的命令,检验当前查询是否匹配到了合适的物化视图。

explain select store_id, sum(sale_amt) from sales_records group by store_id;

结果如下:

+-----------------------------------------------------------------------------------------------+
| Explain String(Nereids Planner) |
+-----------------------------------------------------------------------------------------------+
| PLAN FRAGMENT 0 |
| OUTPUT EXPRS: |
| store_id[#11] |
| sum(sale_amt)[#12] |
| PARTITION: HASH_PARTITIONED: mv_store_id[#7] |
| |
| HAS_COLO_PLAN_NODE: false |
| |
| VRESULT SINK |
| MYSQL_PROTOCAL |
| |
| 3:VAGGREGATE (merge finalize)(145) |
| | output: sum(partial_sum(mva_SUM__sale_amt)[#8])[#10] |
| | group by: mv_store_id[#7] |
| | sortByGroupKey:false |
| | cardinality=1 |
| | final projections: mv_store_id[#9], sum(mva_SUM__sale_amt)[#10] |
| | final project output tuple id: 4 |
| | distribute expr lists: mv_store_id[#7] |
| | |
| 2:VEXCHANGE |
| offset: 0 |
| distribute expr lists: |
| |
| PLAN FRAGMENT 1 |
| |
| PARTITION: HASH_PARTITIONED: record_id[#2] |
| |
| HAS_COLO_PLAN_NODE: false |
| |
| STREAM DATA SINK |
| EXCHANGE ID: 02 |
| HASH_PARTITIONED: mv_store_id[#7] |
| |
| 1:VAGGREGATE (update serialize)(139) |
| | STREAMING |
| | output: partial_sum(mva_SUM__sale_amt[#1])[#8] |
| | group by: mv_store_id[#0] |
| | sortByGroupKey:false |
| | cardinality=1 |
| | distribute expr lists: |
| | |
| 0:VOlapScanNode(136) |
| TABLE: test_db.sales_records(store_amt), PREAGGREGATION: ON |
| partitions=1/1 (sales_records) |
| tablets=10/10, tabletList=494505,494507,494509 ... |
| cardinality=1, avgRowSize=0.0, numNodes=1 |
| pushAggOp=NONE |
| |
| |
| Statistics |
| planed with unknown column statistics |
+-----------------------------------------------------------------------------------------------+

从底部VOlapScanNodetest_db.sales_records(store_amt)可以表明,该查询命中了store_amt这个物化视图。值得注意的是,如果表中没有数据,那么可能不会命中物化视图。

删除物化视图

drop materialized view store_amt on sales_records;

使用示例

接下来,我们通过更多示例来展示物化视图的作用。

示例一:加速聚合查询

业务场景:计算广告的 UV(独立访客数)和 PV(页面访问量)。

假设用户的原始广告点击数据存储在 Doris 中,那么针对广告 PV 和 UV 的查询就可以通过创建带有 bitmap_union 的物化视图来提升查询速度。首先,创建一个存储广告点击数据明细的表,包含每条点击的点击时间、点击的广告、点击的渠道以及点击的用户。原始表创建语句如下:

create table advertiser_view_record
(
click_time datetime,
advertiser varchar(10),
channel varchar(10),
user_id int
) distributed by hash(user_id) properties("replication_num" = "1");
insert into advertiser_view_record values("2020-02-02 02:02:02",'a','a',1);

用户想要查询的是广告的 UV 值,也就是需要对相同广告的用户进行精确去重,查询语句一般为:

select 
advertiser,
channel,
count(distinct user_id)
from
advertiser_view_record
group by
advertiser, channel;

针对这种求 UV 的场景,可以创建一个带有 bitmap_union 的物化视图,以达到预先精确去重的效果。在 Doris 中,count(distinct) 聚合的结果和 bitmap_union_count 聚合的结果是完全一致的。因此,如果查询中涉及到 count(distinct),则通过创建带有 bitmap_union 聚合的物化视图可以加快查询。根据当前的使用场景,可以创建一个根据广告和渠道分组,对 user_id 进行精确去重的物化视图。

create materialized view advertiser_uv as 
select
advertiser,
channel,
bitmap_union(to_bitmap(user_id))
from
advertiser_view_record
group by
advertiser, channel;

当物化视图表创建完成后,查询广告 UV 时,Doris 就会自动从刚才创建好的物化视图 advertiser_uv 中查询数据。如果执行之前的 SQL 查询:

select 
advertiser,
channel,
count(distinct user_id)
from
advertiser_view_record
group by
advertiser, channel;

在选中物化视图后,实际的查询会转化为:

select 
advertiser,
channel,
bitmap_union_count(to_bitmap(user_id))
from
advertiser_uv
group by
advertiser, channel;

通过 explain 命令检查查询是否匹配到了物化视图:

explain select 
advertiser,
channel,
count(distinct user_id)
from
advertiser_view_record
group by
advertiser, channel;

输出结果如下:

+-------------------------------------------------------------------------------------------------------------------------------------------------------+
| Explain String(Nereids Planner) |
+-------------------------------------------------------------------------------------------------------------------------------------------------------+
| PLAN FRAGMENT 0 |
| OUTPUT EXPRS: |
| advertiser[#13] |
| channel[#14] |
| count(DISTINCT user_id)[#15] |
| PARTITION: HASH_PARTITIONED: mv_advertiser[#7], mv_channel[#8] |
| |
| HAS_COLO_PLAN_NODE: false |
| |
| VRESULT SINK |
| MYSQL_PROTOCAL |
| |
| 3:VAGGREGATE (merge finalize)(145) |
| | output: bitmap_union_count(partial_bitmap_union_count(mva_BITMAP_UNION__to_bitmap_with_check(cast(user_id as BIGINT)))[#9])[#12] |
| | group by: mv_advertiser[#7], mv_channel[#8] |
| | sortByGroupKey:false |
| | cardinality=1 |
| | final projections: mv_advertiser[#10], mv_channel[#11], bitmap_union_count(mva_BITMAP_UNION__to_bitmap_with_check(cast(user_id as BIGINT)))[#12] |
| | final project output tuple id: 4 |
| | distribute expr lists: mv_advertiser[#7], mv_channel[#8] |
| | |
| 2:VEXCHANGE |
| offset: 0 |
| distribute expr lists: |
| |
| PLAN FRAGMENT 1 |
| |
| PARTITION: HASH_PARTITIONED: user_id[#6] |
| |
| HAS_COLO_PLAN_NODE: false |
| |
| STREAM DATA SINK |
| EXCHANGE ID: 02 |
| HASH_PARTITIONED: mv_advertiser[#7], mv_channel[#8] |
| |
| 1:VAGGREGATE (update serialize)(139) |
| | STREAMING |
| | output: partial_bitmap_union_count(mva_BITMAP_UNION__to_bitmap_with_check(cast(user_id as BIGINT))[#2])[#9] |
| | group by: mv_advertiser[#0], mv_channel[#1] |
| | sortByGroupKey:false |
| | cardinality=1 |
| | distribute expr lists: |
| | |
| 0:VOlapScanNode(136) |
| TABLE: test_db.advertiser_view_record(advertiser_uv), PREAGGREGATION: ON |
| partitions=1/1 (advertiser_view_record) |
| tablets=10/10, tabletList=494552,494554,494556 ... |
| cardinality=1, avgRowSize=0.0, numNodes=1 |
| pushAggOp=NONE |
| |
| |
| Statistics |
| planed with unknown column statistics |
+-------------------------------------------------------------------------------------------------------------------------------------------------------+

explain 的结果中,可以看到底部 VOlapScanNodeadvertiser_view_record(advertiser_uv)。也就是说,查询会直接扫描物化视图的数据,说明匹配成功。其次,对于 user_id 字段求 count(distinct) 被改写为求 bitmap_union_count(to_bitmap),也就是通过 Bitmap 的方式来达到精确去重的效果。

示例二:匹配不同前缀索引

业务场景:匹配前缀索引

用户的原始表包含三列(k1, k2, k3),其中 k1 和 k2 被设置为前缀索引列。当用户查询条件中包含where k1=1 and k2=2时,查询可以通过索引进行加速。然而,在某些情况下,用户的过滤条件可能无法匹配到前缀索引,例如 where k3=3,此时无法通过索引来提升查询速度。为了解决这个问题,我们可以创建一个以 k3 作为第一列的物化视图。

建表语句和插入数据语句如下:

create table test_table
(
k1 int,
k2 int,
k3 int,
kx date
)
distributed by hash(k1)
properties("replication_num" = "1");

insert into test_table values(1,1,1,1);

创建 k3 为前缀索引的物化视图:

create materialized view mv_1 as SELECT k3, k2, k1 FROM test_table;

使用 EXPLAIN 检查查询是否匹配物化视图:

explain select k1, k2, k3 from test_table where k3=3;

输出结果如下:

+---------------------------------------------------------------------------------------+
| Explain String(Nereids Planner) |
+---------------------------------------------------------------------------------------+
| PLAN FRAGMENT 0 |
| OUTPUT EXPRS: |
| mv_k1[#2] |
| mv_k2[#1] |
| mv_k3[#0] |
| PARTITION: HASH_PARTITIONED: mv_k1[#2] |
| |
| HAS_COLO_PLAN_NODE: false |
| |
| VRESULT SINK |
| MYSQL_PROTOCAL |
| |
| 0:VOlapScanNode(112) |
| TABLE: test_db.test_table(mv_1), PREAGGREGATION: ON |
| PREDICATES: (mv_k3[#0] = 3) |
| partitions=1/1 (test_table) |
| tablets=10/10, tabletList=494599,494601,494603 ... |
| cardinality=0, avgRowSize=0.0, numNodes=1 |
| pushAggOp=NONE |
| |
| |
| Statistics |
| planed with unknown column statistics |
+---------------------------------------------------------------------------------------+

在 EXPLAIN 的结果中,可以看到底部 VOlapScanNode 的test_table(mv_1),这表明查询成功命中了物化视图。

示例三:预先过滤和表达式计算加速查询

业务场景:需要提前过滤数据或加速表达式计算。

建表和插入数据语句如下:

create table d_table (
k1 int null,
k2 int not null,
k3 bigint null,
k4 date null
)
duplicate key (k1,k2,k3)
distributed BY hash(k1) buckets 3
properties("replication_num" = "1");

insert into d_table select 1,1,1,'2020-02-20';
insert into d_table select 2,2,2,'2021-02-20';
insert into d_table select 3,-3,null,'2022-02-20';

创建物化视图:

-- mv1 提前进行表达式计算
create materialized view mv1 as
select
abs(k1)+k2+1,
sum(abs(k2+2)+k3+3)
from
d_table
group by
abs(k1)+k2+1;

-- mv2 提前用 where 表达式过滤以减少物化视图中的数据量
create materialized view mv2 as
select
year(k4),
month(k4)
from
d_table
where
year(k4) = 2020;

通过查询测试检测是否成功命中物化视图:

-- 命中 mv1
select
abs(k1)+k2+1,
sum(abs(k2+2)+k3+3)
from
d_table
group by
abs(k1)+k2+1;

-- 命中 mv1
select
bin(abs(k1)+k2+1),
sum(abs(k2+2)+k3+3)
from
d_table
group by
bin(abs(k1)+k2+1);

-- 命中 mv2
select
year(k4) + month(k4)
from
d_table
where
year(k4) = 2020;

-- 命中原始表 d_table 不会命中 mv2,因为 where 条件不匹配
select
year(k4),
month(k4)
from
d_table;

常见问题

当创建好物化视图后,如果发现没有匹配的数据,可能是因为物化视图还处于构建过程中。此时,可以使用以下命令来查看物化视图的构建状态:

show alter table materialized view from test_db;

如果查询结果显示status字段不是FINISHED,那么需要等待,直到状态变为FINISHED后,物化视图才会变得可用。