Doris--基础--5.3--物化视图

Doris–基础–5.3–物化视图

---

1、介绍

1.1、什么是 物化视图

1. 将预先计算(根据定义好的 SELECT 语句)好的数据集，存储在 Doris 中的一个特殊的表，这个表就叫物化视图
2. 物化视图的出现主要是为了满足用户，既能对原始明细数据的任意维度分析，也能快速的对固定维度进行分析查询。
3. 总结：物化视图 就是 预先存储查询结果 的一种数据库对象。
   1. 可能是对远程数据的本地copy
   2. 可能是一个表或多表join后结果的行或列的子集
   3. 可能是聚合后的结果。

1.2、适用场景

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

1.3、优势

1. 对于那些经常重复的使用相同的子查询结果的查询性能大幅提升
2. Doris自动更新物化视图的数据，保证base 表和物化视图表的数据一致性。无需额外的维护成本
3. 查询的时候也可以自动匹配最优的物化视图

1.4、物化视图 VS Rollup

在没有物化视图功能之前，用户一般都是使用 Rollup 功能通过预聚合方式提升查询效率的。但是 Rollup 具有一定的局限性，他不能基于明细模型做预聚合。

物化视图则在覆盖了 Rollup 的功能的同时，还能支持更丰富的聚合函数。所以物化视图其实是 Rollup 的一个超集。

也就是说，之前 ALTER TABLE ADD ROLLUP 语法支持的功能现在均可以通过 CREATE MATERIALIZED VIEW 实现。

2、使用 物化视图

1. Doris 系统提供了一整套对物化视图的 DDL 语法，包括创建，查看，删除。
2. DDL 的语法和 PostgreSQL， Oracle都是一致的。

2.1、创建物化视图

2.1.1、原则

因为物化试图是需要占用机器资源的，所以不能每个select都建立一个物化视图，这里给出以下2个原则

1. 从查询语句中抽象出，多个查询共有的分组和聚合方式作为物化视图的定义。也就是 多个查询都可以匹配到这张物化视图。
2. 不需要给所有维度组合都创建物化视图

如果物化视图只和某个特殊的查询很贴合，而其他查询均用不到这个物化视图。则会导致这张物化视图的性价比不高，既占用了集群的存储资源，还不能为更多的查询服务。

2.1.2、创建物化视图 命令

创建物化视图是一个异步的操作，也就是说用户成功提交创建任务后，Doris 会在后台对存量的数据进行计算，直到创建成功。

# 创建物化视图
CREATE MATERIALIZED VIEW 视图名称 AS  selec .....


# 查看帮助
HELP CREATE MATERIALIZED VIEW

2.2、支持的聚合函数

2.3、更新策略

1. 为保证 物化视图表 和 Base表的数据一致性，Doris 会将导入，删除等对 base表的操作 都同步到物化视图表中。
   1. 通过增量更新的方式来提升更新效率。
   2. 通过事务方式来保证原子性。
2. 举例：
   1. 如果用户通过 INSERT命令 插入数据到 base表中，则这条数据会同步插入到物化视图中。当 base表和物化视图表均写入成功后，INSERT 命令才会成功返回。

2.4、查询自动匹配

1. 物化视图创建成功后，用户的查询不需要发生任何改变，也就是还是查询的 base表。Doris 会根据当前查询的语句去自动选择一个最优的物化视图，从物化视图中读取数据并计算。
2. 用户可以通过 EXPLAIN 命令来检查当前查询是否使用了物化视图。

2.5、查询物化视图

查看当前表的物化视图

desc tableName all;

2.6、局限性

1. 物化视图的聚合函数的参数 不支持表达式，仅支持单列，比如： sum(a+b)不支持。
2. 如果 物化视图的Key中 不包含 删除语句中的条件列，则不能进行删除操作。如果一定要删除数据，则需要先将物化视图删除，然后方可删除数据。
3. 单表上过多的物化视图会影响导入的效率：
   1. 导入数据时，物化视图和 base 表数据是同步更新的，如果一张表的物化视图表超过10张，则有可能导致导入速度很慢。
4. 相同列，不同聚合函数，不能同时出现在一张物化视图中，比如：select sum(a), min(a) from table 不支持。
5. 物化视图 针对 Unique Key数据模型，只能改变列顺序，不能起到聚合的作用，所以在Unique Key模型上不能通过创建物化视图的方式对数据进行粗粒度聚合操作

3、演示

3.1、创建 物化视图

3.1.1、创建Base表

CREATE TABLE sales_records(
    id INT COMMENT "销售记录的id",
    seller_id INT COMMENT "销售员的id",
    store_id  INT COMMENT "门店的id",
    sale_date DATE COMMENT "销售日期",
    sale_amt BIGINT COMMENT "金额"
)
DISTRIBUTED BY HASH(id) BUCKETS 10;

3.1.2、创建物化视图

基于这个Base表的数据提交一个创建物化视图的任务

CREATE MATERIALIZED VIEW mv_1 AS 
SELECT seller_id, sale_date, SUM( sale_amt ) 
FROM sales_records  
GROUP BY seller_id, sale_date;

1. 提交完创建物化视图的任务后，Doris就会异步在后台生成物化视图的数据，构建物化视图。
2. 在构建期间，用户依然可以正常的查询和导入新的数据。
3. 创建任务会自动处理当前的存量数据和所有新到达的增量数据，从而保持和Base表的数据一致性。用户无需担心一致性问题。
4. mv_1物化视图：
   1. 是一个提供 seller_id, sale_date和sale_amt字段的mv_1表，mv_1表的数据是通过 销售员id和销售日期分组
   2. 如果要查询 销售员id和销售日期 对应的销售金额，那么优先匹配mv_1表。

3.2、查询物化视图

desc sales_records all;

3.3、自动匹配

这里通过举例的方式，来说明select 语句是怎么匹配物化视图的

3.3.1、创建物化视图

基于这个Base表的数据再次提交两个创建物化视图的任务

CREATE MATERIALIZED VIEW mv_1 AS 
SELECT seller_id, sale_date, SUM( sale_amt ) 
FROM sales_records  
GROUP BY seller_id, sale_date;

1. 是一个提供 seller_id, sale_date和SUM(sale_amt)字段的mv_1表，mv_1表的数据是通过 销售员id和销售日期分组
2. 如果要查询 销售员id和销售日期 对应的销售金额，那么优先匹配mv_1表。

CREATE MATERIALIZED VIEW mv_2 AS SELECT store_id,sale_date,SUM(sale_amt) 
FROM sales_records 
GROUP BY store_id,sale_date; 

1. 是一个提供 store_id, sale_date 和SUM(sale_amt)字段的mv_2表，mv_2表的数据是通过 门店id和销售日期分组
2. 如果要查询 门店id和销售日期 对应的销售金额，那么优先匹配mv_2表。

CREATE MATERIALIZED VIEW mv_3 AS
SELECT seller_id,SUM(sale_amt)
FROM sales_records
GROUP BY seller_id;

1. 是一个提供 seller_id和SUM(sale_amt)字段的mv_3表，mv_3表的数据是通过 销售员id分组
2. 如果要查询 销售员id 对应的销售金额，那么优先匹配mv_3表。

3.3.2、自动匹配 步骤

1. 根据查询的条件，从 候选集 的所有物化视图中，找到最优的物化视图
2. 把 查询base表 改为 查询物化视图表，最终达到直接查询物化视图的目的。

3.4、选择最优

根据查询的条件，从 候选集 的所有物化视图中，找到最优的物化视图

3.4.1、选择最优 步骤

1. 对候选集合进行一个过滤。只要是查询的结果能从物化视图数据计算(取部分行，部分列，或部分行列的聚合)出都可以留在候选集中，过滤完成后候选集合大小 >= 1。
2. 从候选集合中根据聚合程度，索引等条件选出一个最优的也就是查询花费最少物化视图。

3.4.2、举例

1. 查询2月10日各个销售员都买了多少钱
2. 候选集过滤目前分为4层，每一层过滤后去除不满足条件的物化视图。

SELECT seller_id, SUM(sale_amt)
FROM sales_records  
WHERE sale_date='2021-02010'
GROUP BY seller_id

3.4.2.1、步骤1

获取候选集，目前候选集中包括所有的物化视图以及Base表共4个。

3.4.2.2、步骤2

第1层过滤，先判断查询Where中的谓词涉及到的数据是否能从物化视图中得到，也就是销售时间列是否在表中存在。由于mv_3物化视图中 根本不存在销售时间列。所以在这一层过滤中，mv_3就被淘汰了。

3.4.2.3、步骤3

第2层过滤，查询的分组列是否为候选集的分组列的子集，也就是销售员id是否为表中分组列的子集。由于mv_2物化视图中的分组列并不涉及销售员id。所以在这一层过滤中，mv_2也被淘汰了。

3.4.2.4、步骤4

第3层过滤，查询的聚合列是否为候选集中聚合列的子集，也就是候选集中 是否存在SUM(sale_amt)，这里Base表和mv_1物化视图表均满足标准。

3.4.2.5、步骤5

第4层过滤，看查询需要的列是否存在于候选集合的列中。也就是候选集中 是否存在seller_id，这里Base表和mv_1物化视图表均满足标准。

3.4.2.5、步骤6

1. 目前候选集剩下Base表和mv_1表，都能满足查询的需求，但每张表的查询效率都不同。
2. 这时候就需要在 候选集中 根据前缀索引是否能匹配到，以及聚合程度的高低来选出一个最优的物化视图。
   1. 从表结构中可以看出，Base表的销售日期列是一个非排序列，而物化视图表的日期是一个排序列，同时聚合程度上mv_1表明显比Base表高。所以最后选择出mv_1作为该查询的最优匹配。

3.5、查询改写

将查询改写为从mv_1中读取数据，过滤出日志为2月10日的mv_1中的数据然后返回即可。

SELECT seller_id, SUM(sale_amt)
FROM sales_records  
WHERE sale_date='2021-02010'
GROUP BY seller_id

变为

SELECT seller_id, SUM(sale_amt)
FROM mv_1  
WHERE sale_date='2021-02010'
GROUP BY seller_id

4、 其他 操作

4.1、检查 物化视图是否构建完成

 

-- 检查物化视图是否构建完成 
SHOW ALTER TABLE MATERIALIZED VIEW FROM test_db;

4.2、删除 物化视图

-- 删除物化视图 
DROP MATERIALIZED VIEW mv_3 ON sales_records;

desc sales_records all;

删掉mv_3