关于Doris的物化视图与异步物化视图

叫我莫言鸭

已于 2024-03-15 14:18:08 修改

阅读量1k

点赞数 4

文章标签： adb android

于 2024-03-14 17:29:51 首次发布

本文链接：https://blog.csdn.net/qq_42265608/article/details/136711371

版权

1.物化视图

1.物化视图与Mysql视图的区别

物化视图：物化视图是预先计算（根据定义的 SELECT 语句）并存储在 Doris 中的特殊表中的数据集。物化视图的出现主要是为了满足用户。可以分析任意维度的原始明细数据，也可以快速分析查询固定维度。
mysql:MySQL中的视图（View）保存的是对数据库中一个或多个表的查询定义
概括：物化视图是存储的实际的结果数据，而mysql存储的只是查询语句

2.物化视图什么时候使用？

比较耗时的操作
需要重复使用的子查询

3.物化视图与Roll up对比

物化视图支持聚合函数和Aggregation模型
物化视图是roll up加强版

4.要求

其实就是抽出共性重复，因为物化视图需要消耗资源

从查询语句中抽象出，多个查询共有的分组和聚合方式作为物化视图的定义。
不需要给所有维度组合都创建物化视图。

5.创建结构

meterialized view

CREATE MATERIALIZED VIEW [IF NOT EXISTS] mv_name
[PARTITION BY (partition_column)]
[DISTRIBUTE BY (distribution_column)]
[PROPERTIES ("key" = "value", ...)]
AS SELECT ...
FROM base_table
[WHERE conditions]
[GROUP BY ...]
[HAVING ...];

取消

CANCEL ALTER TABLE MATERIALIZED VIEW FROM db_name.table_name

2.异步物化视图

1.介绍(官方说法，其实我也不明白)

Doris 的异步物化视图采用基于 SPJG (SELECT-PROJECT-JOIN-GROUP-BY) 模式结构信息的算法来进行透明重写。

2.join重写

查询表与物化视图的表相同，这时候会与物化视图链接，
Join重写是指查询和物化中使用的表相同时。在这种情况下，优化器将通过将物化视图的输入与查询连接或将连接放置在查询的 WHERE 子句的外层来尝试透明重写。

这种重写模式支持多表连接，并支持内连接和左连接类型。对其他类型的支持正在不断扩大。

3.官网样例

物化视图定义，基于lineitem 表

1.join重写样例

1.物化视图创建语句

CREATE MATERIALIZED VIEW mv1
BUILD IMMEDIATE REFRESH AUTO ON SCHEDULE EVERY 1 hour
DISTRIBUTED BY RANDOM BUCKETS 12
PROPERTIES ('replication_num' = '1')
AS
SELECT t1.l_linenumber,
       o_custkey,
       o_orderdate
FROM (SELECT * FROM lineitem WHERE l_linenumber > 1) t1
         LEFT OUTER JOIN orders
                         ON l_orderkey = o_orderkey;

2.视图创建sql

SELECT t1.l_linenumber,
       o_custkey,
       o_orderdate
FROM (SELECT * FROM lineitem WHERE l_linenumber > 1) t1
LEFT OUTER JOIN orders
ON l_orderkey = o_orderkey;
##  上下是一致的注意看
SELECT t1.l_linenumber,
       o_custkey,
       o_orderdate
FROM  lineitem  t1
LEFT OUTER JOIN orders
ON l_orderkey = o_orderkey;
WHERE l_linenumber > 1

3.查询语句

SELECT l_linenumber,
       o_custkey
FROM lineitem
LEFT OUTER JOIN orders
ON l_orderkey = o_orderkey
WHERE l_linenumber > 1 and o_orderdate = '2023-12-31';

4.查询join优化后(后台做到的)

select 
       l_linenumber,
       o_custkey
from mv1
where o_orderdate = '2023-12-31';

2. JOIN 派生

1.物化视图创建语句（设定为rv2吧）

SELECT
    l_shipdate, l_suppkey, o_orderdate
    sum(o_totalprice) AS sum_total,
    max(o_totalprice) AS max_total,
    min(o_totalprice) AS min_total,
    count(*) AS count_all,
    count(distinct CASE WHEN o_shippriority > 1 AND o_orderkey IN (1, 3) THEN o_custkey ELSE null END) AS bitmap_union_basic
FROM lineitem
LEFT OUTER JOIN orders ON lineitem.l_orderkey = orders.o_orderkey AND l_shipdate = o_orderdate
GROUP BY
l_shipdate,
l_suppkey,
o_orderdate;

2.查询语句

SELECT
    l_shipdate, l_suppkey, o_orderdate
    sum(o_totalprice) AS sum_total,
    max(o_totalprice) AS max_total,
    min(o_totalprice) AS min_total,
    count(*) AS count_all,
    count(distinct CASE WHEN o_shippriority > 1 AND o_orderkey IN (1, 3) THEN o_custkey ELSE null END) AS bitmap_union_basic
FROM lineitem
INNER JOIN orders ON lineitem.l_orderkey = orders.o_orderkey AND l_shipdate = o_orderdate
WHERE o_orderdate = '2023-12-11' AND l_suppkey = 3
GROUP BY
l_shipdate,
l_suppkey,
o_orderdate;

3.查询优化

select 
  l_shipdate, l_suppkey, o_orderdate
    sum(o_totalprice) AS sum_total,
    max(o_totalprice) AS max_total,
    min(o_totalprice) AS min_total,
    count(*) AS count_all,
    count(distinct CASE WHEN o_shippriority > 1 AND o_orderkey IN (1, 3) THEN o_custkey ELSE null END) AS bitmap_union_basic
from rv2
WHERE o_orderdate = '2023-12-11' AND l_suppkey = 3

3.聚合重写

1.物化视图定义（rv3）

SELECT
    l_shipdate, o_orderdate, l_partkey, l_suppkey,
    sum(o_totalprice) AS sum_total,
    max(o_totalprice) AS max_total,
    min(o_totalprice) AS min_total,
    count(*) AS count_all,
    bitmap_union(to_bitmap(CASE WHEN o_shippriority > 1 AND o_orderkey IN (1, 3) THEN o_custkey ELSE null END)) AS bitmap_union_basic
FROM lineitem
LEFT OUTER JOIN orders ON lineitem.l_orderkey = orders.o_orderkey AND l_shipdate = o_orderdate
GROUP BY
l_shipdate,
o_orderdate,
l_partkey,
l_suppkey;

2.查询语句

SELECT
    l_shipdate, l_suppkey,
    sum(o_totalprice) AS sum_total,
    max(o_totalprice) AS max_total,
    min(o_totalprice) AS min_total,
    count(*) AS count_all,
    count(distinct CASE WHEN o_shippriority > 1 AND o_orderkey IN (1, 3) THEN o_custkey ELSE null END) AS bitmap_union_basic
FROM lineitem
LEFT OUTER JOIN orders ON lineitem.l_orderkey = orders.o_orderkey AND l_shipdate = o_orderdate
WHERE o_orderdate = '2023-12-11' AND l_partkey = 3
GROUP BY
l_shipdate,
l_suppkey;

3.查询语句优化

SELECT
    l_shipdate, l_suppkey,
    sum_total,
    max_total,
    min_total,
    count_all,
    count(distinct CASE WHEN o_shippriority > 1 AND o_orderkey IN (1, 3) THEN o_custkey ELSE null END) AS bitmap_union_basic
FROM rv3
WHERE o_orderdate = '2023-12-11' AND l_partkey = 3
GROUP BY
l_shipdate,
l_suppkey;