概述

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

Doris 可以分析查询 SQL 的结构信息，自动寻找满足要求的物化视图，并尝试进行透明改写，使用最优的物化视图来表达查询SQL。

通过使用预计算的物化视图结果，可以大幅提高查询性能，减少计算成本。

以 TPC-H 的三张 lineitem，orders 和 partsupp 表来描述直接查询物化视图和使用物化视图进行查询透明改写的能力。 表的定义如下：

1. CREATE TABLE IF NOT EXISTS lineitem (
2. l_orderkey    integer not null,
3. l_partkey     integer not null,
4. l_suppkey     integer not null,
5. l_linenumber  integer not null,
6. l_quantity    decimalv3(15,2) not null,
7. l_extendedprice  decimalv3(15,2) not null,
8. l_discount    decimalv3(15,2) not null,
9. l_tax         decimalv3(15,2) not null,
10. l_returnflag  char(1) not null,
11. l_linestatus  char(1) not null,
12. l_shipdate    date not null,
13. l_commitdate  date not null,
14. l_receiptdate date not null,
15. l_shipinstruct char(25) not null,
16. l_shipmode     char(10) not null,
17. l_comment      varchar(44) not null
18. )
19. DUPLICATE KEY(l_orderkey, l_partkey, l_suppkey, l_linenumber)
20. PARTITION BY RANGE(l_shipdate)
21. (FROM ('2023-10-17') TO ('2023-10-20') INTERVAL 1 DAY)
22. DISTRIBUTED BY HASH(l_orderkey) BUCKETS 3
23. PROPERTIES ("replication_num" = "1");

1. CREATE TABLE IF NOT EXISTS orders  (
2. o_orderkey       integer not null,
3. o_custkey        integer not null,
4. o_orderstatus    char(1) not null,
5. o_totalprice     decimalv3(15,2) not null,
6. o_orderdate      date not null,
7. o_orderpriority  char(15) not null,
8. o_clerk          char(15) not null,
9. o_shippriority   integer not null,
10. o_comment        varchar(79) not null
11. )
12. DUPLICATE KEY(o_orderkey, o_custkey)
13. PARTITION BY RANGE(o_orderdate)(
14. FROM ('2023-10-17') TO ('2023-10-20') INTERVAL 1 DAY)
15. DISTRIBUTED BY HASH(o_orderkey) BUCKETS 3
16. PROPERTIES ("replication_num" = "1");

1. CREATE TABLE IF NOT EXISTS partsupp (
2. ps_partkey     INTEGER NOT NULL,
3. ps_suppkey     INTEGER NOT NULL,
4. ps_availqty    INTEGER NOT NULL,
5. ps_supplycost  DECIMALV3(15,2)  NOT NULL,
6. ps_comment     VARCHAR(199) NOT NULL
7. )
8. DUPLICATE KEY(ps_partkey, ps_suppkey)
9. DISTRIBUTED BY HASH(ps_partkey) BUCKETS 3
10. PROPERTIES (
11. "replication_num" = "1"
12. );

直查物化视图

物化视图可以看作是表，可以像正常的表一样直接查询。

用例1:

物化视图的定义语法，详情见 CREATE-ASYNC-MATERIALIZED-VIEW

mv 定义:

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

查询语句:

可以对物化视图添加过滤条件和聚合等，进行直接查询。

1. SELECT l_linenumber,
2. o_custkey
3. FROM mv1
4. WHERE l_linenumber > 1 and o_orderdate = '2023-12-31';

透明改写能力

JOIN 改写

Join 改写指的是查询和物化使用的表相同，可以在物化视图和查询 Join 的输入或者 Join 的外层写 where，优化器对此 pattern 的查询会尝试进行透明改写。

支持多表 Join，支持 Join 的类型为 inner，left。其他类型在不断拓展中。

用例1:

如下查询可进行透明改写，条件 l_linenumber > 1可以上拉，从而进行透明改写，使用物化视图的预计算结果来表达查询。

mv 定义:

1. SELECT t1.l_linenumber,
2. o_custkey,
3. o_orderdate
4. FROM (SELECT * FROM lineitem WHERE l_linenumber > 1) t1
5. LEFT OUTER JOIN orders
6. ON l_orderkey = o_orderkey;

查询语句:

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

用例2:

JOIN衍生，当查询和物化视图的 JOIN 的类型不一致时，如果物化可以提供查询所需的所有数据时，通过在 JOIN 的外部补偿谓词，也可以进行透明改写，

举例如下

mv 定义:

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

查询语句:

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

聚合改写

查询和物化视图定义中，聚合的维度可以一致或者不一致，可以使用维度中的字段写 WHERE 对结果进行过滤。

物化视图使用的维度需要包含查询的维度，并且查询使用的指标可以使用物化视图的指标来表示。

用例1

如下查询可以进行透明改写，查询和物化使用聚合的维度一致，可以使用维度中的字段进行过滤结果，并且查询会尝试使用物化视图 SELECT 后的表达式。

mv 定义:

1. SELECT
2. o_shippriority, o_comment,
3. count(distinct CASE WHEN o_shippriority > 1 AND o_orderkey IN (1, 3) THEN o_custkey ELSE null END) AS cnt_1,
4. count(distinct CASE WHEN O_SHIPPRIORITY > 2 AND o_orderkey IN (2) THEN o_custkey ELSE null END) AS cnt_2,
5. sum(o_totalprice),
6. max(o_totalprice),
7. min(o_totalprice),
8. count(*)
9. FROM orders
10. GROUP BY
11. o_shippriority,
12. o_comment;

查询语句:

1. SELECT
2. o_shippriority, o_comment,
3. count(distinct CASE WHEN o_shippriority > 1 AND o_orderkey IN (1, 3) THEN o_custkey ELSE null END) AS cnt_1,
4. count(distinct CASE WHEN O_SHIPPRIORITY > 2 AND o_orderkey IN (2) THEN o_custkey ELSE null END) AS cnt_2,
5. sum(o_totalprice),
6. max(o_totalprice),
7. min(o_totalprice),
8. count(*)
9. FROM orders
10. WHERE o_shippriority in (1, 2)
11. GROUP BY
12. o_shippriority,
13. o_comment;

用例2

如下查询可以进行透明改写，查询和物化使用聚合的维度不一致，物化视图使用的维度包含查询的维度。 查询可以使用维度中的字段对结果进行过滤，

查询会尝试使用物化视图 SELECT 后的函数进行上卷，如物化视图的 bitmap_union 最后会上卷成 bitmap_union_count，和查询中 count(distinct) 的语义保持一致。

mv 定义:

1. SELECT
2. l_shipdate, o_orderdate, l_partkey, l_suppkey,
3. sum(o_totalprice) AS sum_total,
4. max(o_totalprice) AS max_total,
5. min(o_totalprice) AS min_total,
6. count(*) AS count_all,
7. 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
8. FROM lineitem
9. LEFT OUTER JOIN orders ON lineitem.l_orderkey = orders.o_orderkey AND l_shipdate = o_orderdate
10. GROUP BY
11. l_shipdate,
12. o_orderdate,
13. l_partkey,
14. l_suppkey;

查询语句:

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

暂时目前支持的聚合上卷函数列表如下：

Query partial 透明改写（Coming soon）

当物化视图的表比查询多时，如果物化视图比查询多的表满足 JOIN 消除的条件，那么也可以进行透明改写，如下可以进行透明改写，待支持。

用例1

mv 定义:

1. SELECT
2. l_linenumber,
3. o_custkey,
4. ps_availqty
5. FROM lineitem
6. LEFT OUTER JOIN orders ON L_ORDERKEY = O_ORDERKEY
7. LEFT OUTER JOIN partsupp ON l_partkey = ps_partkey
8. AND l_suppkey = ps_suppkey;

查询语句：

1. SELECT
2. l_linenumber,
3. o_custkey,
4. ps_availqty
5. FROM lineitem
6. LEFT OUTER JOIN orders ON L_ORDERKEY = O_ORDERKEY;

Union 改写（Coming soon）

当物化视图不足以提供查询的所有数据时，可以通过 Union 的方式，将查询原表和物化视图 Union 起来返回数据，如下可以进行透明改写，待支持。

用例1

mv 定义:

1. SELECT
2. o_orderkey,
3. o_custkey,
4. o_orderstatus,
5. o_totalprice
6. FROM orders
7. WHERE o_orderkey > 10;

查询语句：

1. SELECT
2. o_orderkey,
3. o_custkey,
4. o_orderstatus,
5. o_totalprice
6. FROM orders
7. WHERE o_orderkey > 5;

改写结果示意：

1. SELECT *
2. FROM mv
3. UNION ALL
4. SELECT
5. o_orderkey,
6. o_custkey,
7. o_orderstatus,
8. o_totalprice
9. FROM orders
10. WHERE o_orderkey > 5 AND o_orderkey <= 10;

辅助功能

透明改写后数据一致性问题

grace_period 的单位是秒，指的是容许物化视图和所用基表数据不一致的时间。 比如 grace_period 设置成0，意味要求物化视图和基表数据保持一致，此物化视图才可用于透明改写；对于外表，因为无法感知数据变更，所以物化视图使用了外表，

无论外表的数据是不是最新的，都可以使用此物化视图用于透明改写，如果外表配置了 HMS 元数据源，是可以感知数据变更的，配置数据源和感知数据变更的功能会在后面迭代支持。

如果设置成10，意味物化视图和基表数据允许10s的延迟，如果物化视图的数据和基表的数据有延迟，如果在10s内，此物化视图都可以用于透明改写。

对于物化视图中的内表，可以通过设定 grace_period 属性来控制透明改写使用的物化视图所允许数据最大的延迟时间。 可查看 CREATE-ASYNC-MATERIALIZED-VIEW

查询透明改写命中情况查看和调试

可通过如下语句查看物化视图的透明改写命中情况，会展示查询透明改写简要过程信息。

explain <query_sql> 返回的信息如下，截取了物化视图相关的信息

1. | MaterializedView                                                                                                                                                                                         |
2. | MaterializedViewRewriteFail:                                                                                                                                                                             |
3. | MaterializedViewRewriteSuccessButNotChose:                                                                                                                                                               |
4. |   Names:                                                                                                                                                                                                 |
5. | MaterializedViewRewriteSuccessAndChose:                                                                                                                                                                  |
6. |   Names: mv1

MaterializedViewRewriteSuccessAndChose：透明改写成功，并且CBO选择的物化视图名称列表。

MaterializedViewRewriteSuccessButNotChose：透明改写成功，但是最终CBO没有选择的物化视图名称列表。

MaterializedViewRewriteFail：列举透明改写失败及原因摘要。

如果想知道物化视图候选，改写和最终选择情况的过程详细信息，可以执行如下语句，会展示透明改写过程详细的信息。

explain memo plan <query_sql>

相关环境变量

限制

• 物化视图定义语句中只允许包含 SELECT、FROM、WHERE、JOIN、GROUP BY 语句，JOIN 的输入可以包含简单的 GROUP BY（单表聚合），其中JOIN的支持的类型为 INNER 和 LEFT OUTER JOIN 其他类型的 JOIN 操作逐步支持。
• 基于 External Table 的物化视图不保证查询结果强一致。
• 不支持使用非确定性函数来构建物化视图，包括 rand、now、current_time、current_date、random、uuid等。
• 不支持窗口函数和 LIMIT 的透明改写。
• 物化视图的定义暂时不能使用视图和物化视图。
• 目前 WHERE 条件补偿，支持物化视图没有 WHERE，查询有 WHERE情况的条件补偿；或者物化视图有 WHERE 且查询的 WHERE 条件是物化视图的超集。 目前暂时还不支持范围的条件补偿，比如物化视图定义是 a > 5，查询是 a > 10，逐步支持。