产品指南
表设计
物化视图(Beta)

物化视图

物化视图（Materialized View）功能说明

物化视图是ProtonBase中一种特殊的视图，它将查询结果实际存储在数据库中，而不是每次查询时动态计算。与普通视图不同，物化视图会占用物理存储空间，但对于复杂查询或频繁使用的聚合结果，它可以大大提高查询性能。物化视图可定期刷新，以保持与基表同步。

物化视图主要场景

  • 查询加速 当系统中存在频繁执行的复杂查询（例如多表连接、聚合计算等）且这些查询涉及到的表的数据不经常修改，物化视图可以极大地提高查询的响应速度。因为物化视图将复杂查询的结果预先计算并存储，避免了每次执行查询时都重新计算。比如在数据仓库报表生成的场景，数据通常需要从多个表或数据源进行汇总和分析，我们可以使用物化视图来存储报表内容，提升数据分析的效率。
  • 跨数据源查询 当查询涉及到多个数据源，尤其是外部数据源的时候，每次查询从外部数据源临时读取数据会带来巨大的数据传输和计算的开销。物化视图可以预先将这些分散的数据汇总起来，减少查询时的跨数据源访问，节约数据传输成本。
  • 降低系统负载 在高并发环境中，频繁执行复杂查询会给数据库服务器带来巨大的负载。对于需要快速响应的系统（如在线交易系统、实时决策支持系统），物化视图可以通过提供预先计算的查询结果，显著减少响应时间，降低系统负载。
  • 分层数据加工 在典型的数据仓库中，我们需要将来自不同系统或平台的数据集成在一起，并定义多个ETL作业进行分层数据加工。使用物化视图来保存中间计算结果可以简化这一流程。另外，借助物化视图增量刷新等技术，可以有效提升ETL的作业效率。

物化视图的特点

  • 预先计算并存储查询结果，提高查询速度。
  • 可像普通表一样进行查询，简化复杂查询。
  • 自动同步更新，保证数据一致性。
  • 减少计算资源占用，不用每次查询都执行复杂运算。
  • 可作为数据缓存，降低源表负载。

如何使用物化视图

物化视图主要有两种使用方式：

  • 作为一个普通的表直接进行查询。物化视图本质上也是一个物理表，可以像其他的表一样直接进行查询。同时用户也可以在物化视图上创建各种 index 来加速自己的查询。对物化视图的所有修改请求比如 INSERT/UPDATE/DELETE 将被禁止，更新物化视图的数据需要使用专门的 REFRESH 命令。
  • 优化器自动优化。当用户提交了一个复杂查询后，优化器可能会检测到这个查询的一部分可以替换成对物化视图的访问时，我们可能会直接将查询进行改写。一般来说，物化视图存储的往往是计算量比较大的查询结果，跳过这部分查询的计算能够大幅提升查询性能。需要注意的是，这个优化的前提是优化器认为这个物化视图的数据是“新鲜”的：
    1. 当物化视图刚创建和刚 REFRESH 时：优化器认为物化视图是新鲜的。
    2. 当物化视图的基础表发生修改操作，优化器会自动将相关的物化视图标记为过期，直到下一次 REFRESH。

综上，如何使用物化视图需要用户做一个权衡：

  • 让优化器自动发现和自动查询改写会更方便，但是物化视图一旦过期则不可用，导致查询性能不够稳定。
  • 直接查询物化视图本身会让查询效率更加稳定，但是需要理解物化视图的数据可能是过期的。

物化视图的类型

上面提到当物化视图所基于的基础数据发生变化时，我们需要更新物化视图以反映这些变化，这个操作称为刷新物化视图（REFRESH）。

根据刷新策略的不同，我们可以将物化视图分成以下三类：

  1. 全量物化视图：在刷新物化视图时，我们会重新执行视图定义中的查询语句，并使用最新的查询结果全量替换物化视图中的内容。
  2. 增量物化视图：在刷新物化视图时，我们会分析物化视图定义的查询语句，并计算自上次刷新以来的变化，然后将这些变化合并到物化视图中。
  3. 实时物化视图：实时物化视图不需要用户主动进行刷新。当物化视图依赖的表发生修改操作时，我们会同步的更新物化视图。具体来说，我们也会像增量物化视图一样，通过分析查询语句，只将本次DML修改带来的变化合并进物化视图。因此实时物化视图也可以看作是一种特殊的增量物化视图。

由于增量物化视图和实时物化视图需要分析视图的查询语句，并将其改写成可以增量计算的形式。由于增量计算的复杂性，不是所有 SQL 都能够很好的支持，因此会有一些限制。我们通过以下几个维度进一步对比这几种物化视图：

全量物化视图增量物化视图实时物化视图
支持的 Query 类型所有- Project
- ProjectSet
- Where
- Distinct
- Aggregate
 - SUM/COUNT/MAX/MIN/AVG
- Inner join
- Left/Right/Full outer join
 - outer join 需要是等值条件		同增量物化视图中的除掉 join 的部分
不支持的 Query 类型无- 所有 VOLATILE 的函数
- Over window
- Order by
- Agg with Filter 和 Distinct agg
- Exists / In 等子查询
- Union (all)		同增量物化视图，外加 join
是否过期在上次刷新后，如果基础表发生变化，物化视图数据被视为过期在上次刷新后，如果基础表发生变化，物化视图数据被视为过期物化视图数据永不过期
刷新代价和基础表全量数据相关和上次刷新后基础表的变动数据量和Query 增量计算复杂度相关和基础表的修改量和 Query 增量计算复杂度相关
典型刷新间隔天、小时级别秒、分钟级别无需刷新，实时生效

在实际场景中，我们需要根据查询复杂度，查询频繁程度，对物化视图时效性需求，并综合考虑物化视图的刷新成本等因素来评估是否使用物化视图，以及选择哪种物化视图。

全量物化视图管理

创建全量物化视图

语法

CREATE MATERIALIZED VIEW [ IF NOT EXISTS ] view_name
	[ (column_name [, ...] ) ]
	AS query
	[ WITH [ NO ] DATA ];

参数说明:

  • IF NOT EXISTS: 如果视图已存在，则不会报错
  • view_name: 物化视图名称
  • column_name: 可选，指定视图中的列名
  • query: SELECT查询语句，定义视图的内容
  • WITH [ NO ] DATA: 是否在创建时填充数据

示例

-- 基础Base明细表
CREATE TABLE order_details
(
    order_id     INT,
    product_id   INT,
    product_name VARCHAR(255),
    quantity     INT,
    price        DECIMAL(10, 2),
    PRIMARY KEY (order_id, product_id)
);
 
-- 创建一个物化视图
CREATE MATERIALIZED VIEW sales_mv AS
SELECT product_name, SUM(quantity) AS total_quantity
FROM order_details
GROUP BY product_name;
 
-- 创建一个普通视图
CREATE VIEW sales_v AS
SELECT product_name, SUM(quantity) AS total_quantity
FROM order_details
GROUP BY product_name;
 
-- 基于普通View创建一个物化视图
CREATE MATERIALIZED VIEW sales_mv2 AS
SELECT *
FROM sales_v;
 
-- 指定列名创建物化视图
CREATE MATERIALIZED VIEW sales_mv3 (upper_name, quantity) AS
SELECT UPPER(product_name), SUM(quantity) AS total_quantity
FROM order_details
GROUP BY product_name;
 
-- 查询Materialized View的数据
SELECT * FROM sales_mv;
 
-- 写入测试数据
INSERT INTO order_details (order_id, product_id, product_name, quantity, price)
VALUES
    (1, 1, 'Product A', 10, 9.99),
    (1, 2, 'Product B', 5, 19.99),
    (2, 1, 'Product A', 20, 9.99),
    (2, 3, 'Product C', 15, 14.99),
    (3, 2, 'Product B', 8, 19.99),
    (3, 3, 'Product C', 12, 14.99),
    (4, 1, 'Product A', 30, 9.99),
    (4, 2, 'Product B', 10, 19.99);
 
-- 刷新Materialized View的数据
REFRESH MATERIALIZED VIEW sales_mv;
 
-- 再次查询Materialized View的数据
SELECT * FROM sales_mv;

需要注意，在创建物化视图时，如果使用了WITH DATA(默认)，会立即执行一次查询并填充数据。如果数据量很大，可能会耗时较长，阻塞其他操作。此时可以考虑使用WITH NO DATA，之后再单独执行REFRESH填充数据。

刷新全量物化视图数据

语法：

REFRESH MATERIALIZED VIEW [ CONCURRENTLY ] view_name [ WITH [ NO ] DATA ];

参数说明:

  • CONCURRENTLY: 可选，并行刷新视图，避免阻塞其他查询操作。但有以下限制：
    • 不能在事务块中执行
    • 不支持WITH NO DATA
  • view_name: 要刷新的物化视图名称
  • WITH [ NO ] DATA:
    • WITH DATA(默认):立即使用视图定义的查询语句刷新数据。
    • WITH NO DATA:不刷新数据，只更新视图的定义。一般在创建新视图时使用，之后再单独刷新数据。

示例

--普通刷新，该语句会立即执行视图定义的查询，更新mv_sales中的数据。刷新期间不会锁表。
REFRESH MATERIALIZED VIEW sales_mv;
 
--并行刷新，该语句会在后台并行执行刷新操作，不会阻塞对视图的查询。但刷新期间视图仍可能返回旧数据。
REFRESH MATERIALIZED VIEW CONCURRENTLY sales_mv;

注意事项

  1. 刷新物化视图会耗费资源，需要权衡刷新频率和实时性需求。
  2. 考虑在业务低峰时段执行定期刷新，或使用调度系统自动刷新。

删除物化视图

语法

DROP MATERIALIZED VIEW [ IF EXISTS ] view_name [, ...] [ CASCADE | RESTRICT ];

参数说明

  • IF EXISTS: 如果指定的视图不存在，不会报错。
  • view_name: 要删除的物化视图名称，可以指定多个，用逗号分隔。
  • CASCADE: 同时删除依赖于该视图的其他对象(如索引等)。
  • RESTRICT: 如果有其他对象依赖于该视图，则删除失败。默认行为。

示例

--普通删除，该语句会删除物化视图mv_sales。如果有其他对象依赖于该视图，会报错并中止删除。
DROP MATERIALIZED VIEW sales_mv;
 
--级联删除，该语句会删除视图sales_mv，同时删除所有依赖于它的对象，如索引等。
DROP MATERIALIZED VIEW sales_mv CASCADE;
 
--使用IF EXISTS，该语句会删除视图sales_mv，如果该视图不存在，不会报错。
DROP MATERIALIZED VIEW IF EXISTS sales_mv;

注意事项

  1. 删除物化视图是不可逆操作，会永久删除视图及其数据，请谨慎执行。
  2. 在删除前，确保视图不再需要，且没有其他对象依赖于它，确认视图的依赖关系，避免误删。
  3. 使用CASCADE选项时要特别小心，会同时删除所有依赖对象，影响范围可能很大。
  4. 删除视图需要有对应的权限，如果没有足够权限会报错。
  5. 删除视图不会删除源表中的数据，只是删除预先计算并存储的结果。
  6. 定期检查和清理不再使用的物化视图，节省存储空间。
  7. 合理控制物化视图的数量，过多的视图会增加维护成本和系统复杂性

增量物化视图管理

在V1.1将支持。

实时物化视图管理

在V1.1将支持。

实践推荐

  1. 评估是否使用物化视图

物化视图能产生收益一般需要满足以下几个特征：

  • 查询 pattern 计算开销大。常见开销较大的计算模式比如多表关联，聚合，开窗函数等。另外一些比较复杂的函数（如正则匹配等），以及需要访问外部资源的 UDF 等，也可以被认为是计算开销较大。
  • 查询 pattern 频繁出现。如果某个查询 pattern 开销很大，但是只是很偶尔使用，考虑到物化视图本身会带来一定的成本，也没有太大必要使用。如果查询足够频繁，带来的节省超过物化视图的成本，则可以考虑。

如果一个查询 pattern 同时满足上述两个特征，那么可以考虑针对其创建物化视图。

  1. 合理的定义物化视图

我们建议用户综合考虑查询的计算开销和查询频率，来抽取合适的子查询作为物化视图的定义。这样可以让这个子查询覆盖尽可能的 Query。常见的场景有：

  • 多表关联外加各种 Ad hoc 的聚合计算。一般的数仓分析中往往需要提前将事实表和维度表进行关联，然后基于关联后的表进行分析。如果我们发现这个多表关联的规则基本不变，但是后续的分析非常灵活，那么可以考虑将多表关联定义成物化视图，后续基于物化视图直接进行 Ad hoc 分析。
  • 复杂聚合后的随机查询。还有一些场景是多表关联和聚合规则都很固定，但是最终需要在聚合的结果上做大量的点查。比如在电商领域，一般会提前加工好商家和买家维度的一些聚合结果表，提供给商家和买家对自己的结果进行访问。我们建议对聚合后的结果表创建物化视图，而不是为最终每个可能的点查创建物化视图。
  1. 选择合适的存储

由于物化视图最终也是以一个物理表的形式存在，一些表级的设置手段在这里也都适用，常见的有：

  • 为物化视图的每一列定义名字。如果在创建物化视图时不指定列名，引擎会自动推断和产生。但考虑到后续在物化视图上的调优手段需要，我们建议手动指定每一列的名字。
  • 根据最终访问物化视图的模式选择合理的 Access Method。比如物化视图存储的是一个预聚合的结果，后续对其查询以点查为主，那么可以考虑使用行存(row)。如果后续需要大量全表扫描物化视图做进一步的计算，可以考虑使用列存(columnar)。如果各种访问方式都有，考虑使用混合存储模式(hybrid)。
  • 创建适合的索引。常见的比如加速点查的二级索引，加速列存过滤的 split index 等，都可以创建在物化视图上。这里也需要权衡更多的索引带来的更大的写入开销。
  1. 选择合适的刷新策略

选择合适的刷新策略需要结合 1) 基础表的修改频率 2) 对物化视图的时效性需求 3) 待物化的查询复杂度等因素来进行综合考虑。

首先我们看下比较硬性的条件，假如：

  • 基础表基本不变或者修改很不频繁
  • 对时效性要求较低（比如小时或者天级就可以满足）
  • 查询复杂度过高，无法使用增量计算

上述任一条件满足，我们使用全量物化视图即可。

如果原始数据修改频繁，我们又对结果有一定的时效性要求，则可以考虑使用增量或者实时物化视图。这种情况下，我们一般从时效性的角度来选择：

  • 实时物化视图：这个一般用于某些对时效性要求特别高（秒级以内）的业务场景。
  • 增量物化视图：针对一些秒级和分钟级延迟的场景需求。
  1. 监控物化视图的运行情况
  • 物化视图效果评估 在应用完物化视图后，最好从查询耗时，系统负载等维度评估一下物化视图带来的提升。因为物化视图本身会带领一定的成本，比如刷新成本，存储开销等。我们建议从这两方面入手来进行一个综合的判断。
  • 关注物化视图的刷新情况 从物化视图的刷新历史中，我们可以关注一下物化视图的一些刷新情况。比如全量物化视图刷新时间的变化，是否有预期之外的恶化。增量物化视图的刷新是否有失败的情况发生，刷新时间的变化等。
  • 关注物化视图的延迟情况 通过观察每个物化视图上一次成功刷新的时间，我们可以计算出它们的延迟情况。如果某个物化视图的延迟一直在增加，则可能是由于某种原因一直没有刷新成功导致。这会导致用户查询到的数据有可能是过期很久的，我们需要及时定位和修复。

实践案例

下面我们通过三个实践案例来展示不同类型的物化视图在不同场景下的作用。

用户增长分析

假如我们需要评估一些市场推广或广告投放的效果，新用户数量是一个很好的指标。判断一个用户是否是新用户，可以通过他是否在过去30天内在我们的网站内有任何活动来判断。我们需要类似下面这样的查询：

SELECT COUNT(DISTINCT user_id)
FROM user_activities
WHERE pt = current_date
AND user_id not in (
	-- 统计过去30天出现过的所有用户
	SELECT DISTINCT user_id    FROM user_activities
	WHERE pt >= current_date - internal '30 day' AND pt < current_date AND pt)

我们注意到“统计过去30天出现过的所有用户”这样的查询计算量还是比较大的，并且结果基本不变（假设不考虑 user_activities 有一些迟到的数据）。如果新用户分析的查询经常发生，我们就可以考虑把 “统计过去30天出现过的所有用户” 这部分结果使用物化视图的方式存储起来。考虑到这个查询只需要每天运行一次，我们可以选择全量物化视图，在每天凌晨全量刷新一下结果即可。

类似的思路，也可以扩展到用户留存分析，不同渠道新用户增长分析等场景。

数仓分层

在典型的数仓架构中，我们一般都会采取分层策略，比如：

  • ODS（明细数据层）：也称贴源层，这里存储最原始未加工的数据，后后续数据加工的来源。
  • DWD（数据基础层）：主要对 ODS 层的数据做一些清洗/过滤/规范化的加工操作，也经常涉及到宽表的加工等
  • DWS / ADS（数据服务层）：基于 DWD 层的数据，做不同程度的汇总操作。

离线数仓和实时数仓的主要区别，就在于每一层的时效性，即最原始的数据变化，在多少时间后会体现在这一层的内容里。从模型上来讲，除去 ODS 层之外，其他层的数据都可以看作是某种意义上的物化视图，定期根据数据的依赖关系进行刷新操作。

结合 ProtonBase 强大的存储和查询能力，我们可以构建一个离线和实时一体化的数仓架构：

  • ODS
    • 由外部实时写入或由工具实时同步进入 Protonbase
    • 一些最灵活的业务场景（如数据分析师验证想法等）可以直接查询 ODS 数据
  • DWD
    • 按不同的时效性需求，基于 ODS 层数据构建物化视图。通过 pg_depend, pg_class 等系统表，可以自动分析出物化视图和表之间的依赖关系
    • 小时/天级时效性：构建全量物化视图，定期按照依赖关系全量刷新
    • 分钟级时效性：构建增量物化视图，支持常见的过滤，多表关联等，定期按照依赖关系增量刷新
  • DWS/AWS：
    • 根据不同的业务场景需要，基于 DWD 和 ODS 层构建物化视图。
    • 一些较为固定的查询 pattern：可以构建一些轻度聚合的物化视图，直接在上面做二次聚合分析或者点查。
    • 灵活性很高的查询：可以不使用物化视图，直接查询 ODS 和 DWD 层的数据

通过这个架构，我们不需要为离线和实时各准备一套完整的数据架构，只需要根据时效性和查询灵活性等需求在 Protonbase 里创建合理的表和物化视图等，即可同时满足离线和实时的需求，极大的降低了成本，也提高了易用性。

广告计费

在广告场景中，在投放引擎决定投放某一个广告时，一般会先检查一下该广告主的余额是不是充足。在按点击收费的模式下，用户的每一次点击都会引发对这一广告主的收费。由于用户的点击无时无刻不在发生，我们需要实时的统计每个广告主的花费情况：

SELECT SUM(cost) AS total_cost FROM user_clicks GROUP BY advertiser

如果发现某个广告主的总开销已经超过他的余额，那么就停止展现他的广告。反过来说，如果该花费统计有延迟，那就有可能导致一些超投的情况而造成资损。

目前，一些常见的手段会基于一些流计算引擎来做这部分实时统计，但是也不能避免一小段时间的延迟，同时系统在预期之外的重启也会导致延迟不可控。这时候我们就可以考虑基于 user_clicks 构建实时的物化视图。当用户点击的行为写进 user_clicks 表时，相应的物化视图也会实时的进行更新，做到真正的 0 延迟。

半结构化数据（JSONB）外表设计(Beta)