物化视图实践推荐

实践策略

评估是否使用物化视图

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

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

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

合理的定义物化视图

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

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

选择合适的存储

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

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

选择合适的刷新策略

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

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

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

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

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

实时物化视图：这个一般用于某些对时效性要求特别高（秒级以内）的业务场景。
增量物化视图：针对一些秒级和分钟级延迟的场景需求。

监控物化视图的运行情况

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

实践案例

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

用户增长分析

假如我们需要评估一些市场推广或广告投放的效果，新用户数量是一个很好的指标。判断一个用户是否是新用户，可以通过他是否在过去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 延迟。