Doris--物化视图

• 物化视图就是包含了查询结果的数据库对象，可能是对远程数据的本地 copy，也可能是一个表或多表 join 后结果的行或列的子集，也可能是聚合后的结果。说白了，就是预先存储查询结果的一种数据库对象。
• 在 Doris 中的物化视图，就是查询结果预先存储起来的特殊的表。
• 物化视图的出现主要是为了满足用户，既能对原始明细数据的任意维度分析，也能快速的对固定维度进行分析查询。

一、适用场景

• 分析需求覆盖明细数据查询以及固定维度查询两方面。
• 查询仅涉及表中的很小一部分列或行。
• 查询包含一些耗时处理操作，比如:时间很久的聚合操作等。
• 查询需要匹配不同前缀索引。

二、优势

• 对于那些经常重复的使用相同的子查询结果的查询性能大幅提升。
• Doris自动维护物化视图的数据，无论是新的导入，还是删除操作都能保证base表和物化视图表的数据一致性。无需任何额外的人工维护成本。
• 查询时，会自动匹配到最优物化视图，并直接从物化视图中读取数据。 但注意自动维护物化视图的数据会造成一些维护开销。

三、物化视图 VS Rollup

• 在没有物化视图功能之前，用户一般都是使用 Rollup 功能通过预聚合方式提升查询效 率的。但是 Rollup 具有一定的局限性，他不能基于明细模型做预聚合。
• 物化视图则在覆盖了 Rollup 的功能的同时，还能支持更丰富的聚合函数。所以物化视图其实是 Rollup 的一个超集。
• 也就是说，之前 ALTER TABLE ADD ROLLUP 语法支持的功能现在均可以通过 CREATE MATERIALIZED VIEW 实现。

四、原理

• Doris 系统提供了一整套对物化视图的 DDL 语法，包括创建，查看，删除。DDL 的语法和 PostgreSQL, Oracle 都是一致的。但是 Doris 目前创建物化视图只能在单表操作，不支持 join。

4.1 创建物化视图

• 首先要根据查询语句的特点来决定创建一个什么样的物化视图。并不是说物化视图定义和某个查询语句一模一样就最好。这里有两个原则:

  • (1)从查询语句中抽象出，多个查询共有的分组和聚合方式作为物化视图的定义。
  • (2)不需要给所有维度组合都创建物化视图。
  • 首先第一个点
    • 一个物化视图如果抽象出来，并且多个查询都可以匹配到这张物化视图。这种物化视图效果最好。因为物化视图的维护本身也需要消耗资源。
    • 如果物化视图只和某个特殊的查询很贴合，而其他查询均用不到这个物化视图。则会导致这张物化视图的性价比不高，既占用了集群的存储资源，还不能为更多的查询服务。
    • 所以用户需要结合自己的查询语句，以及数据维度信息去抽象出一些物化视图的定义。
  • 第二点
    • 在实际的分析查询中，并不会覆盖到所有的维度分析。所以给常用的维度组合创建物化视图即可，从而到达一个空间和时间上的平衡。

• 创建物化视图是一个异步的操作，也就是说用户成功提交创建任务后，Doris 会在后台对存量的数据进行计算，直到创建成功。具体的语法可以通过下面命令查看:

help create materialized view

• 比如我们有一张销售记录明细表，存储了每个交易的时间，销售员，销售门店，和金额。
• 提交完创建物化视图的任务后，Doris 就会异步在后台生成物化视图的数据，构建物化视图。
• 在构建期间，用户依然可以正常的查询和导入新的数据。创建任务会自动处理当前的存量数据和所有新到达的增量数据，从而保持和 base 表的数据一致性。用户不需关心一致性问题。
  

4.2 查询

• 物化视图创建完成后，用户的查询会根据规则自动匹配到最优的物化视图。
• 比如我们有一张销售记录明细表，并且在这个明细表上创建了三张物化视图。一个存储了不同时间不同销售员的售卖量，一个存储了不同时间不同门店的销售量，以及每个销售员的总销售量。
• 当查询 7 月 19 日，各个销售员都买了多少钱的话。就可以匹配 mv_1 物化视图。直接 对 mv_1 的数据进行查询。

4.3 查询自动匹配

• 物化视图的自动匹配分为下面两个步骤:
  • (1)根据查询条件筛选出一个最优的物化视图:这一步的输入是所有候选物化视图表 的元数据，根据查询的条件从候选集中输出最优的一个物化视图
  • (2)根据选出的物化视图对查询进行改写:这一步是结合上一步选择出的最优物化视图，进行查询的改写，最终达到直接查询物化视图的目的。

• 其中 bitmap 和 hll 的聚合函数在查询匹配到物化视图后，查询的聚合算子会根据物化视图的表结构进行一个改写。详细见实例 2.

4.4 最优路径选择

• 这里分为两个步骤:
  • (1)对候选集合进行一个过滤。只要是查询的结果能从物化视图数据计算(取部分行， 部分列，或部分行列的聚合)出都可以留在候选集中，过滤完成后候选集合大小>=1。
  • (2)从候选集合中根据聚合程度，索引等条件选出一个最优的也就是查询花费最少物化视图。
• 这里再举一个相对复杂的例子，来体现这个过程:

• 候选集过滤目前分为 4 层，每一层过滤后去除不满足条件的物化视图。
• 比如查询 7 月 19 日，各个销售员都买了多少钱，候选集中包括所有的物化视图以及 base 表共4个:
• 第一层过滤先判断查询 where 中的谓词涉及到的数据是否能从物化视图中得到。也就是 销售时间列是否在表中存在。由于第三个物化视图中根本不存在销售时间列。所以在这一层过滤中，mv_3 就被淘汰了。
• 第二层是过滤查询的分组列是否为候选集的分组列的子集。也就是销售员 id 是否为表中分组列的子集。由于第二个物化视图中的分组列并不涉及销售员 id。所以在这一层过滤中，mv_2 也被淘汰了。
• 第三层过滤是看查询的聚合列是否为候选集中聚合列的子集。也就是对销售额求和是否 能从候选集的表中聚合得出。这里 base 表和物化视图表均满足标准。
• 最后一层是过滤看查询需要的列是否存在于候选集合的列中。由于候选集合中的表均满 足标准，所以最终候选集合中的表为销售明细表，以及 mv_1，这两张。

• 候选集过滤完后输出一个集合，这个集合中的所有表都能满足查询的需求。但每张表的查询效率都不同。这时候就需要再这个集合根据前缀索引是否能匹配到，以及聚合程度的高低来选出一个最优的物化视图。
• 从表结构中可以看出，base 表的销售日期列是一个非排序列，而物化视图表的日期是一 个排序列，同时聚合程度上 mv_1 表明显比 base 表高。所以最后选择出 mv_1 作为该查询的 最优匹配。
  
• 最后再根据选择出的最优解，改写查询。
• 刚才的查询选中 mv_1 后，将查询改写为从 mv_1 中读取数据，过滤出日志为 7 月 19 日的 mv_1 中的数据然后返回即可。

4.5 查询改写

• 有些情况下的查询改写还会涉及到查询中的聚合函数的改写。
• 比如业务方经常会用到 count distinct 对 PV UV 进行计算。
• 例如; 广告点击明细记录表中存放哪个用户点击了什么广告，从什么渠道点击的，以及点击的时间。并且在这个 base 表基础上构建了一个物化视图表，存储了不同广告不同渠道的用户 bitmap 值。
• 由于 bitmap union 这种聚合方式本身会对相同的用户 user id 进行一个去重聚合。当用户查询广告在 web 端的 uv 的时候，就可以匹配到这个物化视图。匹配到这个物化视图表后就需要对查询进行改写，将之前的对用户id求count(distinct) 改为对物化视图中bitmapunion 列求 count。
• 所以最后查询取物化视图的第一和第三行求 bitmap 聚合中有几个值。

4.6 使用及限制

• (1)目前支持的聚合函数包括，常用的 sum，min，max ，count，以及计算 pv ，uv， 留存率，等常用的去重算法 hll_union，和用于精确去重计算 count(distinct)的算法 bitmap_union。
• (2)物化视图的聚合函数的参数不支持表达式仅支持单列，比如: sum(a+b)不支持。
• (3)使用物化视图功能后，由于物化视图实际上是损失了部分维度数据的。所以对表 的 DML 类型操作会有一些限制:
  • 如果表的物化视图 key 中不包含删除语句中的条件列，则删除语句不能执行。
  • 比如想要删除渠道为 app 端的数据，由于存在一个物化视图并不包含渠道这个字段，则这个删除不能执行，因为删除在物化视图中无法被执行。这时候你只能把物化视图先删除， 然后删除完数据后，重新构建一个新的物化视图。
• (4)单表上过多的物化视图会影响导入的效率:导入数据时，物化视图和 base 表数据是同步更新的，如果一张表的物化视图表超过 10 张，则有可能导致导入速度很慢。这就像单次导入需要同时导入 10 张表数据是一样的。
• (5)相同列，不同聚合函数，不能同时出现在一张物化视图中，比如:select sum(a), min(a) from table 不支持。
• (6)物化视图针对 Unique Key 数据模型，只能改变列顺序，不能起到聚合的作用，所以在 Unique Key 模型上不能通过创建物化视图的方式对数据进行粗粒度聚合操作

五、案例演示

5.1 创建和删除物化视图

-- step1:创建一个 Base 表 
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,2,3,'2020-02-02',10);


-- step2:基于这个 Base 表的数据提交一个创建物化视图的任务
create materialized view store_amt as 
select store_id
    ,sum(sale_amt)
from sales_records
group by store_id
;

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

show alter table materialized view from test_db; 

查看 base 表的所有物化视图 

desc sales_records all;


-- step4:检验当前查询是否匹配到了合适的物化视图
explain 
select store_id
    ,sum(sale_amt) 
from sales_records 
group by store_id
;
 
-- step5:删除物化视图语法
drop materialized view 物化视图名 on base 表名;

5.2 计算广告的 pv、uv

• 假设用户的原始广告点击数据存储在 Doris，那么针对广告 PV, UV 查询就可以通过创 建 bitmap_union 的物化视图来提升查询速度。

-- step1:创建一个 Base 表 
create table advertiser_view_record( 
    time date,
    advertiser varchar(10),
    channel varchar(10),
    user_id int
)
distributed by hash(time) 
properties("replication_num" = "1")
;

插入数据
insert into advertiser_view_record values('2020-02- 02','a','app',123);


-- step2:基于这个 Base 表的数据提交一个创建物化视图的任务
create materialized view advertiser_uv as
select advertiser
    ,channel
    ,bitmap_union(to_bitmap(user_id)) 
from advertiser_view_record
group by advertiser
        ,channel
;
 
在 Doris 中，count(distinct) 聚合的结果和 bitmap_union_count 聚合的结果是完全一致的。
而 bitmap_union_count 等于 bitmap_union 的结果求 count，所以如果查询中涉及到 count(distinct) 则通过创建带 bitmap_union 聚合的物化视图方可加快查询。
因为本身 user_id 是一个 INT 类型，所以在 Doris 中需要先将字段通过函数 to_bitmap 转 换为 bitmap 类型然后才可以进行 bitmap_union 聚合。


-- step3:查询自动匹配 
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
;
 

-- step4:检验是否匹配到物化视图
explain 
select advertiser
    ,channel
    ,count(distinct user_id) 
from advertiser_view_record 
group by advertiser
        ,channel
;
 

在EXPLAIN的结果中，首先可以看到OlapScanNode的rollup属性值为 advertiser_uv。 
也就是说，查询会直接扫描物化视图的数据。说明匹配成功。

其次对于 user_id 字段求 count(distinct)被改写为求 bitmap_union_count(to_bitmap)。
也就是通过 bitmap 的方式来达到精确去重的效果。

5.3 前缀索引加快查询速度

• 用户的原始表有(k1, k2, k3)三列。其中 k1, k2 为前缀索引列。这时候如果用户查询条件中包含 where k1=1 and k2=2 就能通过索引加速查询。
• 但是有些情况下，用户的过滤条件无法匹配到前缀索引，比如 where k3=3。则无法通过索引提升查询速度。
• 创建以 k3 作为第一列的物化视图就可以解决这个问题。

 
-- step1:查询

explain 
select record_id
    ,seller_id
    ,store_id 
from sales_records
where store_id=3
;
 

-- step2:创建物化视图
create materialized view mv_1 as
select
  store_id,
  record_id,
  seller_id,
  sale_date,
  sale_amt
from sales_records
;


通过上面语法创建完成后，物化视图中既保留了完整的明细数据，且物化视图的前缀索 引为 store_id 列。


-- step3:查看表结构
desc sales_records all;


-- step4:查询匹配
explain 
select record_id
    ,seller_id
    ,store_id 
from sales_records
where store_id=3
;
 

 
这时候查询就会直接从刚才创建的 mv_1 物化视图中读取数据。
物化视图对 store_id 是存在前缀索引的，查询效率也会提升。