使用物化视图进行查询改写
本主题介绍如何利用 StarRocks 的异步物化视图来重写和加速查询。
概述
StarRocks 的异步物化视图使用基于 SPJG (select-project-join-group-by) 形式的广泛采用的透明查询重写算法。无需修改查询语句，StarRocks 可以自动将针对基表的查询重写为针对包含预计算结果的相应物化视图的查询。因此，物化视图可以帮助您显着降低计算成本，并显着加速查询执行。
基于异步物化视图的查询重写功能在以下场景中特别有用
-
指标的预聚合
如果您处理的是高维度数据，则可以使用物化视图来创建预聚合指标层。
-
宽表的 Join
物化视图允许您透明地加速在复杂场景中具有多个大型宽表 Join 的查询。
-
数据湖中的查询加速
构建基于外部 Catalog 的物化视图可以轻松加速针对数据湖中数据的查询。
注意
在 JDBC Catalog 中的基表上创建的异步物化视图不支持查询重写。
功能
StarRocks 基于异步物化视图的自动查询重写功能具有以下属性
- 强大的数据一致性：如果基表是 Native Table，StarRocks 确保通过基于物化视图的查询重写获得的结果与从直接针对基表的查询返回的结果一致。
- 陈旧性重写：StarRocks 支持陈旧性重写，允许您容忍一定级别的数据过期，以应对频繁的数据更改场景。
- 多表 Join：StarRocks 的异步物化视图支持各种类型的 Join，包括一些复杂的 Join 场景，如 View Delta Join 和 Derivable Join，允许您在涉及大型宽表的场景中加速查询。
- 聚合重写：StarRocks 可以重写带有聚合的查询以提高报表性能。
- 嵌套物化视图：StarRocks 支持基于嵌套物化视图重写复杂查询，从而扩展可以重写的查询范围。
- Union 重写：您可以将 Union 重写功能与物化视图分区的 TTL (生存时间) 结合起来，以实现热数据和冷数据的分离，从而允许您从物化视图查询热数据，从基表查询历史数据。
- 基于视图的物化视图：您可以加速基于视图的数据建模场景中的查询。
- 外部 Catalog 上的物化视图：您可以加速数据湖中的查询。
- 复杂表达式重写：它可以处理复杂表达式，包括函数调用和算术运算，以满足高级分析和计算需求。
以下部分将详细阐述这些功能。
Join 重写
StarRocks 支持重写具有各种类型 Join 的查询，包括 Inner Join、Cross Join、Left Outer Join、Full Outer Join、Right Outer Join、Semi Join 和 Anti Join。
以下是使用 Join 重写查询的示例。创建两个基表如下
CREATE TABLE customer (
c_custkey INT(11) NOT NULL,
c_name VARCHAR(26) NOT NULL,
c_address VARCHAR(41) NOT NULL,
c_city VARCHAR(11) NOT NULL,
c_nation VARCHAR(16) NOT NULL,
c_region VARCHAR(13) NOT NULL,
c_phone VARCHAR(16) NOT NULL,
c_mktsegment VARCHAR(11) NOT NULL
) ENGINE=OLAP
DUPLICATE KEY(c_custkey)
DISTRIBUTED BY HASH(c_custkey) BUCKETS 12;
CREATE TABLE lineorder (
lo_orderkey INT(11) NOT NULL,
lo_linenumber INT(11) NOT NULL,
lo_custkey INT(11) NOT NULL,
lo_partkey INT(11) NOT NULL,
lo_suppkey INT(11) NOT NULL,
lo_orderdate INT(11) NOT NULL,
lo_orderpriority VARCHAR(16) NOT NULL,
lo_shippriority INT(11) NOT NULL,
lo_quantity INT(11) NOT NULL,
lo_extendedprice INT(11) NOT NULL,
lo_ordtotalprice INT(11) NOT NULL,
lo_discount INT(11) NOT NULL,
lo_revenue INT(11) NOT NULL,
lo_supplycost INT(11) NOT NULL,
lo_tax INT(11) NOT NULL,
lo_commitdate INT(11) NOT NULL,
lo_shipmode VARCHAR(11) NOT NULL
) ENGINE=OLAP
DUPLICATE KEY(lo_orderkey)
DISTRIBUTED BY HASH(lo_orderkey) BUCKETS 48;
使用上述基表，您可以创建物化视图如下
CREATE MATERIALIZED VIEW join_mv1
DISTRIBUTED BY HASH(lo_orderkey)
AS
SELECT lo_orderkey, lo_linenumber, lo_revenue, lo_partkey, c_name, c_address
FROM lineorder INNER JOIN customer
ON lo_custkey = c_custkey;
这样的物化视图可以重写以下查询
SELECT lo_orderkey, lo_linenumber, lo_revenue, c_name, c_address
FROM lineorder INNER JOIN customer
ON lo_custkey = c_custkey;
StarRocks 支持重写具有复杂表达式的 Join 查询，例如算术运算、字符串函数、日期函数、CASE WHEN 表达式和 OR 谓词。例如，上述物化视图可以重写以下查询
SELECT
lo_orderkey,
lo_linenumber,
(2 * lo_revenue + 1) * lo_linenumber,
upper(c_name),
substr(c_address, 3)
FROM lineorder INNER JOIN customer
ON lo_custkey = c_custkey;
除了传统场景之外，StarRocks 还支持在更复杂的场景中重写 Join 查询。
Query Delta Join 重写
Query Delta Join 是指查询中 Join 的表是物化视图中 Join 的表的超集的场景。例如，考虑以下涉及三个表 Join 的查询：lineorder、customer 和 part。如果物化视图 join_mv1 仅包含 lineorder 和 customer 的 Join，StarRocks 可以使用 join_mv1 重写查询。
示例
SELECT lo_orderkey, lo_linenumber, lo_revenue, c_name, c_address, p_name
FROM
lineorder INNER JOIN customer ON lo_custkey = c_custkey
INNER JOIN part ON lo_partkey = p_partkey;
其原始查询计划和重写后的查询计划如下
View Delta Join 重写
View Delta Join 是指查询中 Join 的表是物化视图中 Join 的表的子集的场景。此功能通常用于涉及大型宽表的场景。例如，在 Star Schema Benchmark (SSB) 的上下文中，您可以创建一个 Join 所有表的物化视图，以提高查询性能。通过测试发现，多表 Join 的查询性能可以通过物化视图透明地重写查询后达到与查询相应大型宽表相同的性能水平。
要执行 View Delta Join 重写，物化视图必须包含查询中不存在的 1:1 基数保留 Join。以下是九种被认为是基数保留 Join 的类型，满足其中任何一种都可以启用 View Delta Join 重写
以 SSB 测试为例，创建以下基表
CREATE TABLE customer (
c_custkey INT(11) NOT NULL,
c_name VARCHAR(26) NOT NULL,
c_address VARCHAR(41) NOT NULL,
c_city VARCHAR(11) NOT NULL,
c_nation VARCHAR(16) NOT NULL,
c_region VARCHAR(13) NOT NULL,
c_phone VARCHAR(16) NOT NULL,
c_mktsegment VARCHAR(11) NOT NULL
) ENGINE=OLAP
DUPLICATE KEY(c_custkey)
DISTRIBUTED BY HASH(c_custkey) BUCKETS 12
PROPERTIES (
"unique_constraints" = "c_custkey" -- Specify the unique constraints.
);
CREATE TABLE dates (
d_datekey DATE NOT NULL,
d_date VARCHAR(20) NOT NULL,
d_dayofweek VARCHAR(10) NOT NULL,
d_month VARCHAR(11) NOT NULL,
d_year INT(11) NOT NULL,
d_yearmonthnum INT(11) NOT NULL,
d_yearmonth VARCHAR(9) NOT NULL,
d_daynuminweek INT(11) NOT NULL,
d_daynuminmonth INT(11) NOT NULL,
d_daynuminyear INT(11) NOT NULL,
d_monthnuminyear INT(11) NOT NULL,
d_weeknuminyear INT(11) NOT NULL,
d_sellingseason VARCHAR(14) NOT NULL,
d_lastdayinweekfl INT(11) NOT NULL,
d_lastdayinmonthfl INT(11) NOT NULL,
d_holidayfl INT(11) NOT NULL,
d_weekdayfl INT(11) NOT NULL
) ENGINE=OLAP
DUPLICATE KEY(d_datekey)
DISTRIBUTED BY HASH(d_datekey) BUCKETS 1
PROPERTIES (
"unique_constraints" = "d_datekey" -- Specify the unique constraints.
);
CREATE TABLE supplier (
s_suppkey INT(11) NOT NULL,
s_name VARCHAR(26) NOT NULL,
s_address VARCHAR(26) NOT NULL,
s_city VARCHAR(11) NOT NULL,
s_nation VARCHAR(16) NOT NULL,
s_region VARCHAR(13) NOT NULL,
s_phone VARCHAR(16) NOT NULL
) ENGINE=OLAP
DUPLICATE KEY(s_suppkey)
DISTRIBUTED BY HASH(s_suppkey) BUCKETS 12
PROPERTIES (
"unique_constraints" = "s_suppkey" -- Specify the unique constraints.
);
CREATE TABLE part (
p_partkey INT(11) NOT NULL,
p_name VARCHAR(23) NOT NULL,
p_mfgr VARCHAR(7) NOT NULL,
p_category VARCHAR(8) NOT NULL,
p_brand VARCHAR(10) NOT NULL,
p_color VARCHAR(12) NOT NULL,
p_type VARCHAR(26) NOT NULL,
p_size TINYINT(11) NOT NULL,
p_container VARCHAR(11) NOT NULL
) ENGINE=OLAP
DUPLICATE KEY(p_partkey)
DISTRIBUTED BY HASH(p_partkey) BUCKETS 12
PROPERTIES (
"unique_constraints" = "p_partkey" -- Specify the unique constraints.
);
CREATE TABLE lineorder (
lo_orderdate DATE NOT NULL, -- Specify it as NOT NULL.
lo_orderkey INT(11) NOT NULL,
lo_linenumber TINYINT NOT NULL,
lo_custkey INT(11) NOT NULL, -- Specify it as NOT NULL.
lo_partkey INT(11) NOT NULL, -- Specify it as NOT NULL.
lo_suppkey INT(11) NOT NULL, -- Specify it as NOT NULL.
lo_orderpriority VARCHAR(100) NOT NULL,
lo_shippriority TINYINT NOT NULL,
lo_quantity TINYINT NOT NULL,
lo_extendedprice INT(11) NOT NULL,
lo_ordtotalprice INT(11) NOT NULL,
lo_discount TINYINT NOT NULL,
lo_revenue INT(11) NOT NULL,
lo_supplycost INT(11) NOT NULL,
lo_tax TINYINT NOT NULL,
lo_commitdate DATE NOT NULL,
lo_shipmode VARCHAR(100) NOT NULL
) ENGINE=OLAP
DUPLICATE KEY(lo_orderdate,lo_orderkey)
PARTITION BY RANGE(lo_orderdate)
(PARTITION p1 VALUES [("0000-01-01"), ("1993-01-01")),
PARTITION p2 VALUES [("1993-01-01"), ("1994-01-01")),
PARTITION p3 VALUES [("1994-01-01"), ("1995-01-01")),
PARTITION p4 VALUES [("1995-01-01"), ("1996-01-01")),
PARTITION p5 VALUES [("1996-01-01"), ("1997-01-01")),
PARTITION p6 VALUES [("1997-01-01"), ("1998-01-01")),
PARTITION p7 VALUES [("1998-01-01"), ("1999-01-01")))
DISTRIBUTED BY HASH(lo_orderkey) BUCKETS 48
PROPERTIES (
"foreign_key_constraints" = "
(lo_custkey) REFERENCES customer(c_custkey);
(lo_partkey) REFERENCES part(p_partkey);
(lo_suppkey) REFERENCES supplier(s_suppkey)" -- Specify the Foreign Keys.
);
创建 Join lineorder、customer、supplier、part 和 dates 的物化视图 lineorder_flat_mv
CREATE MATERIALIZED VIEW lineorder_flat_mv
DISTRIBUTED BY HASH(LO_ORDERDATE, LO_ORDERKEY) BUCKETS 48
PARTITION BY LO_ORDERDATE
REFRESH MANUAL
PROPERTIES (
"partition_refresh_number"="1"
)
AS SELECT /*+ SET_VAR(query_timeout = 7200) */ -- Set timeout for the refresh operation.
l.LO_ORDERDATE AS LO_ORDERDATE,
l.LO_ORDERKEY AS LO_ORDERKEY,
l.LO_LINENUMBER AS LO_LINENUMBER,
l.LO_CUSTKEY AS LO_CUSTKEY,
l.LO_PARTKEY AS LO_PARTKEY,
l.LO_SUPPKEY AS LO_SUPPKEY,
l.LO_ORDERPRIORITY AS LO_ORDERPRIORITY,
l.LO_SHIPPRIORITY AS LO_SHIPPRIORITY,
l.LO_QUANTITY AS LO_QUANTITY,
l.LO_EXTENDEDPRICE AS LO_EXTENDEDPRICE,
l.LO_ORDTOTALPRICE AS LO_ORDTOTALPRICE,
l.LO_DISCOUNT AS LO_DISCOUNT,
l.LO_REVENUE AS LO_REVENUE,
l.LO_SUPPLYCOST AS LO_SUPPLYCOST,
l.LO_TAX AS LO_TAX,
l.LO_COMMITDATE AS LO_COMMITDATE,
l.LO_SHIPMODE AS LO_SHIPMODE,
c.C_NAME AS C_NAME,
c.C_ADDRESS AS C_ADDRESS,
c.C_CITY AS C_CITY,
c.C_NATION AS C_NATION,
c.C_REGION AS C_REGION,
c.C_PHONE AS C_PHONE,
c.C_MKTSEGMENT AS C_MKTSEGMENT,
s.S_NAME AS S_NAME,
s.S_ADDRESS AS S_ADDRESS,
s.S_CITY AS S_CITY,
s.S_NATION AS S_NATION,
s.S_REGION AS S_REGION,
s.S_PHONE AS S_PHONE,
p.P_NAME AS P_NAME,
p.P_MFGR AS P_MFGR,
p.P_CATEGORY AS P_CATEGORY,
p.P_BRAND AS P_BRAND,
p.P_COLOR AS P_COLOR,
p.P_TYPE AS P_TYPE,
p.P_SIZE AS P_SIZE,
p.P_CONTAINER AS P_CONTAINER,
d.D_DATE AS D_DATE,
d.D_DAYOFWEEK AS D_DAYOFWEEK,
d.D_MONTH AS D_MONTH,
d.D_YEAR AS D_YEAR,
d.D_YEARMONTHNUM AS D_YEARMONTHNUM,
d.D_YEARMONTH AS D_YEARMONTH,
d.D_DAYNUMINWEEK AS D_DAYNUMINWEEK,
d.D_DAYNUMINMONTH AS D_DAYNUMINMONTH,
d.D_DAYNUMINYEAR AS D_DAYNUMINYEAR,
d.D_MONTHNUMINYEAR AS D_MONTHNUMINYEAR,
d.D_WEEKNUMINYEAR AS D_WEEKNUMINYEAR,
d.D_SELLINGSEASON AS D_SELLINGSEASON,
d.D_LASTDAYINWEEKFL AS D_LASTDAYINWEEKFL,
d.D_LASTDAYINMONTHFL AS D_LASTDAYINMONTHFL,
d.D_HOLIDAYFL AS D_HOLIDAYFL,
d.D_WEEKDAYFL AS D_WEEKDAYFL
FROM lineorder AS l
INNER JOIN customer AS c ON c.C_CUSTKEY = l.LO_CUSTKEY
INNER JOIN supplier AS s ON s.S_SUPPKEY = l.LO_SUPPKEY
INNER JOIN part AS p ON p.P_PARTKEY = l.LO_PARTKEY
INNER JOIN dates AS d ON l.LO_ORDERDATE = d.D_DATEKEY;
SSB Q2.1 涉及 Join 四个表，但与物化视图 lineorder_flat_mv 相比，缺少 customer 表。在 lineorder_flat_mv 中，lineorder INNER JOIN customer 本质上是一个基数保留 Join。因此，从逻辑上讲，可以消除此 Join 而不影响查询结果。因此，可以使用 lineorder_flat_mv 重写 Q2.1。
SSB Q2.1
SELECT sum(lo_revenue) AS lo_revenue, d_year, p_brand
FROM lineorder
JOIN dates ON lo_orderdate = d_datekey
JOIN part ON lo_partkey = p_partkey
JOIN supplier ON lo_suppkey = s_suppkey
WHERE p_category = 'MFGR#12' AND s_region = 'AMERICA'
GROUP BY d_year, p_brand
ORDER BY d_year, p_brand;
其原始查询计划和重写后的查询计划如下
同样，SSB 中的其他查询也可以使用 lineorder_flat_mv 透明地重写，从而优化查询性能。
Join 可推导性重写
Join 可推导性是指物化视图和查询中的 Join 类型不一致，但物化视图的 Join 结果包含查询的 Join 结果的场景。目前，它支持两种场景 - Join 三个或更多表，以及 Join 两个表。
-
场景一：Join 三个或更多表
假设物化视图包含表
t1和t2之间的 Left Outer Join 以及表t2和t3之间的 Inner Join。在两个 Join 中，Join 条件都包含来自t2的列。另一方面，查询包含 t1 和 t2 之间的 Inner Join，以及 t2 和 t3 之间的 Inner Join。在两个 Join 中，Join 条件都包含来自 t2 的列。
在这种情况下，可以使用物化视图重写查询。这是因为在物化视图中，首先执行 Left Outer Join，然后执行 Inner Join。Left Outer Join 生成的右表没有匹配的结果（即，右表中的列为 NULL）。这些结果随后在 Inner Join 期间被过滤掉。因此，物化视图和查询的逻辑是等效的，可以重写查询。
示例
创建物化视图
join_mv5CREATE MATERIALIZED VIEW join_mv5
PARTITION BY lo_orderdate
DISTRIBUTED BY hash(lo_orderkey)
PROPERTIES (
"partition_refresh_number" = "1"
)
AS
SELECT lo_orderkey, lo_orderdate, lo_linenumber, lo_revenue, c_custkey, c_address, p_name
FROM customer LEFT OUTER JOIN lineorder
ON c_custkey = lo_custkey
INNER JOIN part
ON p_partkey = lo_partkey;join_mv5可以重写以下查询SELECT lo_orderkey, lo_orderdate, lo_linenumber, lo_revenue, c_custkey, c_address, p_name
FROM customer INNER JOIN lineorder
ON c_custkey = lo_custkey
INNER JOIN part
ON p_partkey = lo_partkey;其原始查询计划和重写后的查询计划如下
同样，如果将物化视图定义为
t1 INNER JOIN t2 INNER JOIN t3，并且查询为LEFT OUTER JOIN t2 INNER JOIN t3，则也可以重写查询。此外，此重写功能扩展到涉及三个以上表的场景。 -
场景二：Join 两个表
涉及两个表的 Join 可推导性重写功能支持以下特定情况
在情况 1 到 9 中，必须将过滤谓词添加到重写后的结果中，以确保语义等效性。例如，创建物化视图如下
CREATE MATERIALIZED VIEW join_mv3
DISTRIBUTED BY hash(lo_orderkey)
AS
SELECT lo_orderkey, lo_linenumber, lo_revenue, c_custkey, c_address
FROM lineorder LEFT OUTER JOIN customer
ON lo_custkey = c_custkey;可以使用
join_mv3重写以下查询，并将谓词c_custkey IS NOT NULL添加到重写后的结果中SELECT lo_orderkey, lo_linenumber, lo_revenue, c_custkey, c_address
FROM lineorder INNER JOIN customer
ON lo_custkey = c_custkey;其原始查询计划和重写后的查询计划如下
在情况 10 中，Left Outer Join 查询必须在右表中包含过滤谓词
IS NOT NULL，例如=、<>、>、<、<=、>=、LIKE、IN、NOT LIKE或NOT IN。例如，创建物化视图如下CREATE MATERIALIZED VIEW join_mv4
DISTRIBUTED BY hash(lo_orderkey)
AS
SELECT lo_orderkey, lo_linenumber, lo_revenue, c_custkey, c_address
FROM lineorder INNER JOIN customer
ON lo_custkey = c_custkey;join_mv4可以重写以下查询，其中customer.c_address = "Sb4gxKs7"是过滤谓词IS NOT NULLSELECT lo_orderkey, lo_linenumber, lo_revenue, c_custkey, c_address
FROM lineorder LEFT OUTER JOIN customer
ON lo_custkey = c_custkey
WHERE customer.c_address = "Sb4gxKs7";其原始查询计划和重写后的查询计划如下
聚合重写
StarRocks 的异步物化视图支持重写具有所有可用聚合函数的多表聚合查询，包括 bitmap_union、hll_union 和 percentile_union。例如，创建物化视图如下
CREATE MATERIALIZED VIEW agg_mv1
DISTRIBUTED BY hash(lo_orderkey)
AS
SELECT
lo_orderkey,
lo_linenumber,
c_name,
sum(lo_revenue) AS total_revenue,
max(lo_discount) AS max_discount
FROM lineorder INNER JOIN customer
ON lo_custkey = c_custkey
GROUP BY lo_orderkey, lo_linenumber, c_name;
它可以重写以下查询
SELECT
lo_orderkey,
lo_linenumber,
c_name,
sum(lo_revenue) AS total_revenue,
max(lo_discount) AS max_discount
FROM lineorder INNER JOIN customer
ON lo_custkey = c_custkey
GROUP BY lo_orderkey, lo_linenumber, c_name;
其原始查询计划和重写后的查询计划如下
以下各节将详细说明聚合重写功能可能有用的场景。
聚合 Rollup 重写
StarRocks 支持重写带有聚合 Rollup 的查询，也就是说，StarRocks 可以使用使用 GROUP BY a,b 子句创建的异步物化视图重写带有 GROUP BY a 子句的聚合查询。例如，可以使用 agg_mv1 重写以下查询
SELECT
lo_orderkey,
c_name,
sum(lo_revenue) AS total_revenue,
max(lo_discount) AS max_discount
FROM lineorder INNER JOIN customer
ON lo_custkey = c_custkey
GROUP BY lo_orderkey, c_name;
其原始查询计划和重写后的查询计划如下
注意
目前，不支持重写分组集、带有 Rollup 的分组集或带有 Cube 的分组集。
只有某些聚合函数支持使用聚合 Rollup 重写查询。在前面的示例中，如果物化视图 order_agg_mv 使用 count(distinct client_id) 而不是 bitmap_union(to_bitmap(client_id))，则 StarRocks 无法使用聚合 Rollup 重写查询。
下表显示了原始查询中的聚合函数与用于构建物化视图的聚合函数之间的对应关系。您可以根据您的业务场景选择相应的聚合函数来构建物化视图。
| 原始查询中支持的聚合函数 | 物化视图中支持聚合 Rollup 的函数 |
|---|---|
| sum | sum |
| count | count |
| min | min |
| max | max |
| avg | sum / count |
| bitmap_union, bitmap_union_count, count(distinct) | bitmap_union |
| hll_raw_agg, hll_union_agg, ndv, approx_count_distinct | hll_union |
| percentile_approx, percentile_union | percentile_union |
没有相应 GROUP BY 列的 DISTINCT 聚合无法使用聚合 Rollup 重写。但是，从 StarRocks v3.1 开始，如果带有聚合 Rollup DISTINCT 聚合函数的查询没有 GROUP BY 列但具有相等谓词，则也可以通过相关的物化视图重写该查询，因为 StarRocks 可以将相等谓词转换为 GROUP BY 常量表达式。
在以下示例中，StarRocks 可以使用物化视图 order_agg_mv1 重写查询。
CREATE MATERIALIZED VIEW order_agg_mv1
DISTRIBUTED BY HASH(`order_id`) BUCKETS 12
REFRESH ASYNC START('2022-09-01 10:00:00') EVERY (interval 1 day)
AS
SELECT
order_date,
count(distinct client_id)
FROM order_list
GROUP BY order_date;
-- Query
SELECT
order_date,
count(distinct client_id)
FROM order_list WHERE order_date='2023-07-03';
聚合下推
从 v3.3.0 开始，StarRocks 支持物化视图查询重写的聚合下推。启用此功能后，聚合函数将在查询执行期间下推到 Scan 算子，并在 Join 算子执行之前由物化视图重写。这将缓解由 Join 引起的数据扩展，从而提高查询性能。
此功能默认处于禁用状态。要启用此功能，您必须将系统变量 enable_materialized_view_agg_pushdown_rewrite 设置为 true。
假设您要加速以下基于 SSB 的查询 SQL1
-- SQL1
SELECT
LO_ORDERDATE, sum(LO_REVENUE), max(LO_REVENUE), count(distinct LO_REVENUE)
FROM lineorder l JOIN dates d
ON l.LO_ORDERDATE = d.d_date
GROUP BY LO_ORDERDATE
ORDER BY LO_ORDERDATE;
SQL1 由表 lineorder 上的聚合以及 lineorder 和 dates 的 Join 组成。由于聚合发生在 lineorder 中，并且与 dates 的 Join 仅用于数据过滤，因此 SQL1 在逻辑上等效于以下 SQL2
-- SQL2
SELECT
LO_ORDERDATE, sum(sum1), max(max1), bitmap_union_count(bitmap1)
FROM
(SELECT
LO_ORDERDATE, sum(LO_REVENUE) AS sum1, max(LO_REVENUE) AS max1, bitmap_union(to_bitmap(LO_REVENUE)) AS bitmap1
FROM lineorder
GROUP BY LO_ORDERDATE) l JOIN dates d
ON l.LO_ORDERDATE = d.d_date
GROUP BY LO_ORDERDATE
ORDER BY LO_ORDERDATE;
SQL2 将聚合提前，从而缩小 Join 的数据大小。您可以基于 SQL2 的子查询创建物化视图，并启用聚合下推以重写和加速聚合
-- Create the materialized view mv0
CREATE MATERIALIZED VIEW mv0 REFRESH MANUAL AS
SELECT
LO_ORDERDATE,
sum(LO_REVENUE) AS sum1,
max(LO_REVENUE) AS max1,
bitmap_union(to_bitmap(LO_REVENUE)) AS bitmap1
FROM lineorder
GROUP BY LO_ORDERDATE;
-- Enable aggregation pushdown for materialized view query rewrite
SET enable_materialized_view_agg_pushdown_rewrite=true;
然后，物化视图将重写和加速 SQL1。它被重写为以下查询
SELECT
LO_ORDERDATE, sum(sum1), max(max1), bitmap_union_count(bitmap1)
FROM
(SELECT LO_ORDERDATE, sum1, max1, bitmap1 FROM mv0) l JOIN dates d
ON l.LO_ORDERDATE = d.d_date
GROUP BY LO_ORDERDATE
ORDER BY LO_ORDERDATE;
请注意，只有支持聚合 Rollup 重写的某些聚合函数才有资格进行下推。它们是
- MIN
- MAX
- COUNT
- COUNT DISTINCT
- 总和
- BITMAP_UNION
- HLL_UNION
- PERCENTILE_UNION
- BITMAP_AGG
- ARRAY_AGG_DISTINCT
- 下推后，需要将聚合函数 Rollup 以与原始语义对齐。有关聚合 Rollup 的更多说明，请参阅聚合 Rollup 重写。
- 聚合下推支持基于 Bitmap 或 HLL 函数对 Count Distinct 进行 Rollup 重写。
- 聚合下推仅支持将聚合函数下推到 Join、Filter 或 Where 算子之前的 Scan 算子。
- 聚合下推仅支持基于在单个表上构建的物化视图进行查询重写和加速。
COUNT DISTINCT 重写
StarRocks 支持将 COUNT DISTINCT 计算重写为基于 Bitmap 的计算，从而可以通过物化视图实现高性能、精确的去重。例如，创建物化视图如下
CREATE MATERIALIZED VIEW distinct_mv
DISTRIBUTED BY hash(lo_orderkey)
AS
SELECT lo_orderkey, bitmap_union(to_bitmap(lo_custkey)) AS distinct_customer
FROM lineorder
GROUP BY lo_orderkey;
它可以重写以下查询
SELECT lo_orderkey, count(distinct lo_custkey)
FROM lineorder
GROUP BY lo_orderkey;
嵌套物化视图重写
StarRocks 支持使用嵌套物化视图重写查询。例如，创建物化视图 join_mv2、agg_mv2 和 agg_mv3 如下
CREATE MATERIALIZED VIEW join_mv2
DISTRIBUTED BY hash(lo_orderkey)
AS
SELECT lo_orderkey, lo_linenumber, lo_revenue, c_name, c_address
FROM lineorder INNER JOIN customer
ON lo_custkey = c_custkey;
CREATE MATERIALIZED VIEW agg_mv2
DISTRIBUTED BY hash(lo_orderkey)
AS
SELECT
lo_orderkey,
lo_linenumber,
c_name,
sum(lo_revenue) AS total_revenue,
max(lo_discount) AS max_discount
FROM join_mv2
GROUP BY lo_orderkey, lo_linenumber, c_name;
CREATE MATERIALIZED VIEW agg_mv3
DISTRIBUTED BY hash(lo_orderkey)
AS
SELECT
lo_orderkey,
sum(total_revenue) AS total_revenue,
max(max_discount) AS max_discount
FROM agg_mv2
GROUP BY lo_orderkey;
它们的关系如下
agg_mv3 可以重写以下查询
SELECT
lo_orderkey,
sum(lo_revenue) AS total_revenue,
max(lo_discount) AS max_discount
FROM lineorder INNER JOIN customer
ON lo_custkey = c_custkey
GROUP BY lo_orderkey;
其原始查询计划和重写后的查询计划如下
Union 重写
谓词 Union 重写
当物化视图的谓词范围是查询的谓词范围的子集时，可以使用 UNION 操作重写查询。
例如，创建物化视图如下
CREATE MATERIALIZED VIEW agg_mv4
DISTRIBUTED BY hash(lo_orderkey)
AS
SELECT
lo_orderkey,
sum(lo_revenue) AS total_revenue,
max(lo_discount) AS max_discount
FROM lineorder
WHERE lo_orderkey < 300000000
GROUP BY lo_orderkey;
它可以重写以下查询
select
lo_orderkey,
sum(lo_revenue) AS total_revenue,
max(lo_discount) AS max_discount
FROM lineorder
GROUP BY lo_orderkey;
其原始查询计划和重写后的查询计划如下
在此上下文中，agg_mv5 包含 lo_orderkey < 300000000 的数据。lo_orderkey >= 300000000 的数据直接从基表 lineorder 获取。最后，使用 UNION 操作组合这两组数据，然后聚合以获得最终结果。
分区 Union 重写
假设您创建了一个基于分区表的已分区物化视图。当可重写查询扫描的分区范围是物化视图最新分区范围的超集时，将使用 UNION 操作重写查询。
例如，考虑以下物化视图 agg_mv4。它的基表 lineorder 当前包含从 p1 到 p7 的分区，物化视图也包含从 p1 到 p7 的分区。
CREATE MATERIALIZED VIEW agg_mv5
DISTRIBUTED BY hash(lo_orderkey)
PARTITION BY RANGE(lo_orderdate)
REFRESH MANUAL
AS
SELECT
lo_orderdate,
lo_orderkey,
sum(lo_revenue) AS total_revenue,
max(lo_discount) AS max_discount
FROM lineorder
GROUP BY lo_orderkey;
如果将新的分区 p8 添加到 lineorder，其分区范围为 [("19990101"), ("20000101"))，则可以使用 UNION 操作重写以下查询
SELECT
lo_orderdate,
lo_orderkey,
sum(lo_revenue) AS total_revenue,
max(lo_discount) AS max_discount
FROM lineorder
GROUP BY lo_orderkey;
其原始查询计划和重写后的查询计划如下
如上所示，agg_mv5 包含来自分区 p1 到 p7 的数据，并且直接从 lineorder 查询来自分区 p8 的数据。最后，使用 UNION 操作组合这两组数据。
基于视图的物化视图重写
从 v3.1.0 开始，StarRocks 支持基于视图创建物化视图。如果后续针对视图的查询是 SPJG 模式，则可以重写这些查询。默认情况下，针对视图的查询会自动转录为针对视图基表的查询，然后透明地匹配和重写。
但是，在实际场景中，数据分析师可能会对复杂的嵌套视图执行数据建模，这些视图无法直接转录。因此，基于此类视图创建的物化视图无法重写查询。为了提高其在上述场景中的能力，StarRocks 从 v3.3.0 开始优化了基于视图的物化视图查询重写逻辑。
原理
在之前的查询重写逻辑中，StarRocks 会将针对视图的查询转录为针对视图基表的查询。如果转录查询的执行计划与 SPJG 模式不匹配，查询重写将遇到失败。
为了解决这个问题，StarRocks 引入了一个新的算子 - LogicalViewScanOperator，以简化执行计划树的结构，而无需转录查询。此算子旨在将执行计划树与 SPJG 模式匹配，从而促进查询重写。
以下示例列出了一个带有 AGGREGATE 子查询的查询、基于该子查询构建的视图、基于该视图转录的查询以及基于该视图构建的物化视图
-- Original query:
SELECT
v1.a,
t2.b,
v1.total
FROM(
SELECT
a,
sum(c) AS total
FROM t1
GROUP BY a
) v1
INNER JOIN t2 ON v1.a = t2.a;
-- View:
CREATE VIEW view_1 AS
SELECT
t1.a,
sum(t1.c) AS total
FROM t1
GROUP BY t1.a;
-- Transcribed query:
SELECT
v1.a,
t2.b,
v1.total
FROM view_1 v1
JOIN t2 ON v1.a = t2.a;
-- Materialized view:
CREATE MATERIALIZED VIEW mv1
DISTRIBUTED BY hash(a)
REFRESH MANUAL
AS
SELECT
v1.a,
t2.b,
v1.total
FROM view_1 v1
JOIN t2 ON v1.a = t2.a;
原始查询的执行计划（如下图左侧所示）由于 JOIN 中的 LogicalAggregateOperator 而与 SPJG 模式不匹配。StarRocks 不支持此类情况的查询重写。但是，通过基于子查询定义视图，可以将原始查询转录为针对该视图的查询。借助 LogicalViewScanOperator，StarRocks 可以将不匹配的部分转移到 SPJG 模式中，因此允许在这种情况下进行查询重写。
用法
默认情况下，基于视图的物化视图查询重写处于禁用状态。
要启用此功能，您必须设置以下变量
SET enable_view_based_mv_rewrite = true;
用例
使用基于单视图的物化视图重写查询
StarRocks 支持使用基于单个视图构建的物化视图重写查询，包括带有聚合的查询。
例如，您可以为 TPC-H Query 18 构建以下视图和物化视图
CREATE VIEW q18_view
AS
SELECT
c_name,
c_custkey,
o_orderkey,
o_orderdate,
o_totalprice,
sum(l_quantity)
FROM
customer,
orders,
lineitem
WHERE
o_orderkey IN (
SELECT
l_orderkey
FROM
lineitem
GROUP BY
l_orderkey having
sum(l_quantity) > 315
)
AND c_custkey = o_custkey
AND o_orderkey = l_orderkey
GROUP BY
c_name,
c_custkey,
o_orderkey,
o_orderdate,
o_totalprice;
CREATE MATERIALIZED VIEW q18_mv
DISTRIBUTED BY hash(c_custkey, o_orderkey)
REFRESH MANUAL
AS
SELECT * FROM q18_view;
物化视图可以重写以下两个查询
mysql> EXPLAIN LOGICAL SELECT * FROM q18_view;
+-------------------------------------------------------------------------------------------------------+
| Explain String |
+-------------------------------------------------------------------------------------------------------+
| - Output => [2:c_name, 1:c_custkey, 9:o_orderkey, 10:o_orderdate, 13:o_totalprice, 52:sum] |
| - SCAN [q18_mv] => [1:c_custkey, 2:c_name, 52:sum, 9:o_orderkey, 10:o_orderdate, 13:o_totalprice] |
| MaterializedView: true |
| Estimates: {row: 9, cpu: 486.00, memory: 0.00, network: 0.00, cost: 243.00} |
| partitionRatio: 1/1, tabletRatio: 96/96 |
| 1:c_custkey := 60:c_custkey |
| 2:c_name := 59:c_name |
| 52:sum := 64:sum(l_quantity) |
| 9:o_orderkey := 61:o_orderkey |
| 10:o_orderdate := 62:o_orderdate |
| 13:o_totalprice := 63:o_totalprice |
+-------------------------------------------------------------------------------------------------------+
mysql> EXPLAIN LOGICAL SELECT c_name, sum(`sum(l_quantity)`) FROM q18_view GROUP BY c_name;
+-----------------------------------------------------------------------------------------------------+
| Explain String |
+-----------------------------------------------------------------------------------------------------+
| - Output => [2:c_name, 59:sum] |
| - AGGREGATE(GLOBAL) [2:c_name] |
| Estimates: {row: 9, cpu: 306.00, memory: 306.00, network: 0.00, cost: 1071.00} |
| 59:sum := sum(59:sum) |
| - EXCHANGE(SHUFFLE) [2] |
| Estimates: {row: 9, cpu: 30.60, memory: 0.00, network: 30.60, cost: 306.00} |
| - AGGREGATE(LOCAL) [2:c_name] |
| Estimates: {row: 9, cpu: 61.20, memory: 30.60, network: 0.00, cost: 244.80} |
| 59:sum := sum(52:sum) |
| - SCAN [q18_mv] => [2:c_name, 52:sum] |
| MaterializedView: true |
| Estimates: {row: 9, cpu: 306.00, memory: 0.00, network: 0.00, cost: 153.00} |
| partitionRatio: 1/1, tabletRatio: 96/96 |
| 2:c_name := 60:c_name |
| 52:sum := 65:sum(l_quantity) |
+-----------------------------------------------------------------------------------------------------+
使用基于视图的物化视图重写带有 JOIN 的查询
StarRocks 支持重写具有视图之间或视图与表之间 JOIN 的查询，包括基于 JOIN 的聚合。
例如，您可以创建以下视图和物化视图
CREATE VIEW view_1 AS
SELECT
l_partkey,
l_suppkey,
sum(l_quantity) AS total_quantity
FROM lineitem
GROUP BY
l_partkey,
l_suppkey;
CREATE VIEW view_2 AS
SELECT
l_partkey,
l_suppkey,
sum(l_tax) AS total_tax
FROM lineitem
GROUP BY
l_partkey,
l_suppkey;
CREATE MATERIALIZED VIEW mv_1
DISTRIBUTED BY hash(l_partkey, l_suppkey)
REFRESH MANUAL AS
SELECT
v1.l_partkey,
v2.l_suppkey,
total_quantity,
total_tax
FROM view_1 v1
JOIN view_2 v2 ON v1.l_partkey = v2.l_partkey
AND v1.l_suppkey = v2.l_suppkey;
物化视图可以重写以下两个查询
mysql> EXPLAIN LOGICAL
-> SELECT v1.l_partkey,
-> v2.l_suppkey,
-> total_quantity,
-> total_tax
-> FROM view_1 v1
-> JOIN view_2 v2 ON v1.l_partkey = v2.l_partkey
-> AND v1.l_suppkey = v2.l_suppkey;
+--------------------------------------------------------------------------------------------------------+
| Explain String |
+--------------------------------------------------------------------------------------------------------+
| - Output => [4:l_partkey, 25:l_suppkey, 17:sum, 37:sum] |
| - SCAN [mv_1] => [17:sum, 4:l_partkey, 37:sum, 25:l_suppkey] |
| MaterializedView: true |
| Estimates: {row: 799541, cpu: 31981640.00, memory: 0.00, network: 0.00, cost: 15990820.00} |
| partitionRatio: 1/1, tabletRatio: 96/96 |
| 17:sum := 43:total_quantity |
| 4:l_partkey := 41:l_partkey |
| 37:sum := 44:total_tax |
| 25:l_suppkey := 42:l_suppkey |
+--------------------------------------------------------------------------------------------------------+
mysql> EXPLAIN LOGICAL
-> SELECT v1.l_partkey,
-> sum(total_quantity),
-> sum(total_tax)
-> FROM view_1 v1
-> JOIN view_2 v2 ON v1.l_partkey = v2.l_partkey
-> AND v1.l_suppkey = v2.l_suppkey
-> group by v1.l_partkey;
+--------------------------------------------------------------------------------------------------------------------+
| Explain String |
+--------------------------------------------------------------------------------------------------------------------+
| - Output => [4:l_partkey, 41:sum, 42:sum] |
| - AGGREGATE(GLOBAL) [4:l_partkey] |
| Estimates: {row: 196099, cpu: 4896864.00, memory: 3921980.00, network: 0.00, cost: 29521223.20} |
| 41:sum := sum(41:sum) |
| 42:sum := sum(42:sum) |
| - EXCHANGE(SHUFFLE) [4] |
| Estimates: {row: 136024, cpu: 489686.40, memory: 0.00, network: 489686.40, cost: 19228831.20} |
| - AGGREGATE(LOCAL) [4:l_partkey] |
| Estimates: {row: 136024, cpu: 5756695.20, memory: 489686.40, network: 0.00, cost: 18249458.40} |
| 41:sum := sum(17:sum) |
| 42:sum := sum(37:sum) |
| - SCAN [mv_1] => [17:sum, 4:l_partkey, 37:sum] |
| MaterializedView: true |
| Estimates: {row: 799541, cpu: 28783476.00, memory: 0.00, network: 0.00, cost: 14391738.00} |
| partitionRatio: 1/1, tabletRatio: 96/96 |
| 17:sum := 45:total_quantity |
| 4:l_partkey := 43:l_partkey |
| 37:sum := 46:total_tax |
+--------------------------------------------------------------------------------------------------------------------+
使用基于外部表视图构建的物化视图重写查询
您可以在外部 Catalog 中的表上构建视图，然后在视图上构建物化视图以重写查询。用法与内部表类似。
基于外部 Catalog 的物化视图重写
StarRocks 支持在 Hive Catalog、Hudi Catalog、Iceberg Catalog 和 Paimon Catalog 上构建异步物化视图，并使用它们透明地重写查询。
基于文本的物化视图重写
从 v3.3.0 开始，StarRocks 支持基于文本的物化视图重写，这显着扩展了其查询重写能力。
基础知识
为了实现基于文本的物化视图重写，StarRocks 将查询（或其子查询）的抽象语法树与物化视图定义的抽象语法树进行比较。当它们相互匹配时，StarRocks 将基于物化视图重写查询。基于文本的物化视图重写简单、高效，并且比常规 SPJG 类型的物化视图查询重写具有更少的限制。正确使用此功能可以显着加速查询性能。
基于文本的物化视图重写不限于 SPJG 类型的算子。它还支持 Union、Window、Order、Limit 和 CTE 等算子。
用法
默认情况下启用基于文本的物化视图重写。您可以通过将变量 enable_materialized_view_text_match_rewrite 设置为 false 来手动禁用此功能。
FE 配置项 enable_materialized_view_text_based_rewrite 控制是否在创建异步物化视图时构建抽象语法树。默认情况下也启用此功能。将此项设置为 false 将在系统级别禁用基于文本的物化视图重写。
变量 materialized_view_subuqery_text_match_max_count 控制比较物化视图和子查询的抽象语法树的最大次数。默认值为 4。增加此值也会增加优化器的耗时。
请注意，只有当物化视图满足及时性（数据一致性）要求时，才能将其用于基于文本的查询重写。您可以在创建物化视图时使用属性 query_rewrite_consistency 手动设置一致性检查规则。有关更多信息，请参阅CREATE MATERIALIZED VIEW。
用例
在以下场景中，查询符合基于文本的物化视图重写的条件
- 原始查询与物化视图的定义匹配。
- 原始查询的子查询与物化视图的定义匹配。
与常规 SPJG 类型的物化视图查询重写相比，基于文本的物化视图重写支持更复杂的查询，例如多层聚合。
- 建议将要匹配的查询封装在原始查询的子查询中。
- 请不要在物化视图或原始查询的子查询的定义中封装 ORDER BY 子句。否则，由于默认情况下会消除子查询中的 ORDER BY 子句，因此无法重写查询。
例如，您可以创建以下物化视图
CREATE MATERIALIZED VIEW mv1 REFRESH MANUAL AS
SELECT
user_id,
count(1)
FROM (
SELECT
user_id,
time,
bitmap_union(to_bitmap(tag_id)) AS a
FROM user_tags
GROUP BY
user_id,
time) t
GROUP BY user_id;
物化视图可以重写以下两个查询
SELECT
user_id,
count(1)
FROM (
SELECT
user_id,
time,
bitmap_union(to_bitmap(tag_id)) AS a
FROM user_tags
GROUP BY
user_id,
time) t
GROUP BY user_id;
SELECT count(1)
FROM
（
SELECT
user_id,
count(1)
FROM (
SELECT
user_id,
time,
bitmap_union(to_bitmap(tag_id)) AS a
FROM user_tags
GROUP BY
user_id,
time) t
GROUP BY user_id
）m;
但是，物化视图无法重写以下查询，因为原始查询包含 ORDER BY 子句
SELECT
user_id,
count(1)
FROM (
SELECT
user_id,
time,
bitmap_union(to_bitmap(tag_id)) AS a
FROM user_tags
GROUP BY
user_id,
time) t
GROUP BY user_id
ORDER BY user_id;
配置查询重写
您可以通过以下会话变量配置异步物化视图查询重写
| 变量 | 默认 | 描述 |
|---|---|---|
| enable_materialized_view_union_rewrite | true | 布尔值，用于控制是否启用物化视图 Union 查询重写。 |
| enable_rule_based_materialized_view_rewrite | true | 布尔值，用于控制是否启用基于规则的物化视图查询重写。此变量主要用于单表查询重写。 |
| nested_mv_rewrite_max_level | 3 | 可用于查询重写的嵌套物化视图的最大级别。类型：INT。范围：[1, +∞)。值 1 表示在其他物化视图上创建的物化视图将不用于查询重写。 |
检查查询是否被重写
您可以通过使用 EXPLAIN 语句查看查询计划来检查查询是否被重写。如果 OlapScanNode 部分下的字段 TABLE 显示相应物化视图的名称，则表示查询已基于物化视图重写。
mysql> EXPLAIN SELECT
order_id, sum(goods.price) AS total
FROM order_list INNER JOIN goods
ON goods.item_id1 = order_list.item_id2
GROUP BY order_id;
+------------------------------------+
| Explain String |
+------------------------------------+
| PLAN FRAGMENT 0 |
| OUTPUT EXPRS:1: order_id | 8: sum |
| PARTITION: RANDOM |
| |
| RESULT SINK |
| |
| 1:Project |
| | <slot 1> : 9: order_id |
| | <slot 8> : 10: total |
| | |
| 0:OlapScanNode |
| TABLE: order_mv |
| PREAGGREGATION: ON |
| partitions=1/1 |
| rollup: order_mv |
| tabletRatio=0/12 |
| tabletList= |
| cardinality=3 |
| avgRowSize=4.0 |
| numNodes=0 |
+------------------------------------+
20 rows in set (0.01 sec)
禁用查询重写
默认情况下，StarRocks 启用为基于默认 Catalog 创建的异步物化视图进行查询重写。您可以通过将会话变量 enable_materialized_view_rewrite 设置为 false 来禁用此功能。
对于基于外部 Catalog 创建的异步物化视图，您可以使用 ALTER MATERIALIZED VIEW 将物化视图属性 force_external_table_query_rewrite 设置为 false 来禁用此功能。
局限性
在物化视图查询重写方面，StarRocks 目前有以下限制
- StarRocks 不支持重写带有非确定性函数的查询，包括 rand、random、uuid 和 sleep。
- StarRocks 不支持重写带有窗口函数的查询。
- 使用包含 LIMIT、ORDER BY、UNION、EXCEPT、INTERSECT、MINUS、GROUPING SETS、WITH CUBE 或 WITH ROLLUP 语句定义的物化视图不能用于查询重写。
- 无法保证基于外部目录构建的基础表和物化视图之间查询结果的强一致性。
- 在 JDBC Catalog 中的基表上创建的异步物化视图不支持查询重写。
在基于视图的物化视图查询重写方面，StarRocks 目前有以下限制
-
目前，StarRocks 不支持分区联合重写。
-
如果视图包含随机函数，包括 rand()、random()、uuid() 和 sleep()，则不支持查询重写。
-
如果视图包含具有相同名称的列，则不支持查询重写。 您必须为具有相同名称的列分配不同的别名。
-
用于创建物化视图的视图必须至少包含一列以下数据类型：整数类型、日期类型和字符串类型。 例如，您不能创建查询视图的物化视图，因为
total_cost是一个 DOUBLE 类型列。CREATE VIEW v1
AS
SELECT sum(cost) AS total_cost
FROM t1;