1. 视图介绍

物化视图相当于自动把普通视图数据进行持久化，同时具备视图和物理表的能力。广泛用于数据流式加工和业务透明加速。

主要用于以下场景：

• 声明式编程：使用SQL语句实现数据加工落地，无需编写复杂的数据转换程序。
• 流式加工：通过将多个物化视图嵌套起来，轻松实现数据的线性或多维流转。
• 灵活刷新：通过编排刷新任务，可定时点刷，或按链路级联刷。做好刷新成本与数据新鲜度的平衡。
• 透明加速：把公共复杂的查询创建为物化视图，SQL会被自动查询重写为查物化视图，无需改造现有业务，直接实现业务加速。
• 数据分层：借用视图和权限管理的能力，实现数据分层管理，避免暴露细节的数据信息。
• 瞬时物化：使用自动分区管理，可实现物化视图数据的时效性管理。
• 冷热分仓：使用冷热数据管理，可以实现物化视图冷分区的自动转储。

2. 视图原理

• 1. 基表更新：当物化视图查询的表有数据变化，会立即对物化视图标记失效。
• 2. 基表查询：当物化视图有效，且查询结果在数学逻辑上能够包含在物化视图中，会直接从物化视图中读取数据，实现查询加速。
• 3. 物化视图查询：也可以直接查询物化视图获取业务结果。

2.1 相关概念

• 基表：物化视图定义中查询的表。
• 失效：基表数据变动时，会立即失效物化视图，防止查询到过期数据。但不影响直接查询物化视图。
• 重写：如果查询数据在逻辑上被包含在物化视图中，查询会直接使用物化视图中现成的数据。
• 刷新：可以手动或定时刷新物化视图，达成数据新鲜的目标。支持全表刷新和分区增量刷新。

2.2 视图种类

3. 视图使用

3.1 视图创建

需要先开启 enable_matview 参数。

• 支持多表JOIN：错选索引扫描性能差
• 支持子查询：错选nestloop而不走hashjoin
• 基表格式：被查询的基表没有格式要求，也支持外表。
• 物化格式：物化视图持久化的表支持常用格式，支持hstore_opt
• 不稳定函数：支持强行指定使用部分不稳定函数，比如sysdate

CREATE MATERIALIZED VIEW [ IF NOT EXISTS ] materialized_view_name
[ ( column_name [, ...] ) ] --可选，自定义各列的别名
[ BUILD { DEFERRED | IMMEDIATE } ] --创建后再刷新或创建时立即刷新数据
[ REFRESH [ [DEFAULT] | [ RESTRICT ] | [ CASCADE FORWARD ] | [ CASCADE BACKWARD ] | [ CASCADE ALL ] ] [ COMPLETE ] [ ON DEMAND ] 
[ [ START WITH (timestamptz) ] | [ EVERY (interval) ] ] ] --定义数据的定时或级联刷新方式
[ { ENABLE | DISABLE } QUERY REWRITE ] --是否用于查询重写
[ WITH ( {storage_parameter = value} [, ... ] ) ] --表格式相关参数，推荐WITH(orientation=COLUMN, enable_hstore_opt=ON)
[ TABLESPACE tablespace_name ]
[ DISTRIBUTE BY { REPLICATION | ROUNDROBIN | { HASH ( column_name [,...] ) } } ]
[PARTITION BY { 
        {RANGE (partition_key) ( partition_less_than_item [, ... ] )} |
        {RANGE (partition_key) ( partition_start_end_item [, ... ] )}]
AS query;

支持嵌套使用物化视图。

创建样例：

CREATE MATERIALIZED VIEW test_mv1
BUILD IMMEDIATE
REFRESH COMPLETE START WITH ('2024-11-03 16:00:00+00'::TIMESTAMPTZ) EVERY (interval '00:01:00') 
ENABLE QUERY REWRITE 
WITH(orientation=COLUMN, enable_hstore_opt=ON) 
DISTRIBUTE BY ROUNDROBIN AS 
Your_Select;

3.2 视图分区

3.2.1 分区目的

为了实现物化视图能够按分区快速刷新，可以把物化视图建成分区，并且与基表的分区建立映射关系。

当基表的部分分区发生变化时，就可以只查询变化分区，然后把最新数据导入物化视图对应分区即可。

 约束

1. 物化视图的分区键必须是基表的某个分区键，以建立映射关系。
2. 物化视图的分区键必须包含在查询的最外层目标列中，这样才能按分区写入物化视图。
3. 为了建立清晰的映射关系，暂时只支持范围分区。
4. 有分区映射的基表变化可以分区刷新，无映射关系的维度表变化，需要全量刷新。
5. 如果存在多个带分区的基表，且分区条件都一样，按一个指定映射后，会自动都进行关联映射。
6. 大表在左边，right outer join不支持，不支持full outer join。

支持

1. 支持基于多个基表建立分区映射关系
2. 分区物化视图支持union
3. 基表可以是组合分区键，物化视图只选第一个键。

 3.2.2 等比映射

物化视图的分区与某个基表分区，分区条件完全一样。数据类型：无特殊要求。

物化视图的分区名字和范围与基表分区，完全一致。

3.2.3 上卷映射

物化视图分区范围大于基表分区，比如：基表按天分区，物化视图按月分区。一般通过date_trunc函数，实现时间的向上截取。数据类型：只支持time, timestamp, timestamptz类型。

--创建事实表
CREATE TABLE fact_table (start_time TIMESTAMPTZ NOT NULL, id int)
PARTITION BY RANGE(start_time) (
    PARTITION p1 VALUES LESS THAN ('2024-01-01'),
    PARTITION p2 VALUES LESS THAN ('2024-02-01'),
    PARTITION p3 VALUES LESS THAN ('2024-03-01'),
    PARTITION p4 VALUES LESS THAN ('2024-04-01'),
    PARTITION p5 VALUES LESS THAN ('2024-05-01'),
    PARTITION p6 VALUES LESS THAN ('2024-06-01')
);

--创建维度表
CREATE TABLE dimen_table (start_time TIMESTAMPTZ NOT NULL, id INT);

--等比例方式，创建与事实表分区完全一样的分区物化视图
CREATE MATERIALIZED VIEW level_normal enable query rewrite DISTRIBUTE BY HASH(start_time) PARTITION BY start_time
AS SELECT fact_table.start_time, dimen_table.id FROM fact_table JOIN dimen_table ON fact_table.id = dimen_table.id;

--按天上卷方式，根据事实表start_time分区键，创建分区物化视图
CREATE MATERIALIZED VIEW level_day enable query rewrite DISTRIBUTE BY HASH(start_time) PARTITION BY date_trunc('day', start_time)
AS SELECT fact_table.start_time, dimen_table.id FROM fact_table JOIN dimen_table ON fact_table.id = dimen_table.id;

--按月上卷方式，根据事实表start_time分区键，创建分区物化视图
CREATE MATERIALIZED VIEW level_month enable query rewrite DISTRIBUTE BY HASH(start_time) PARTITION BY date_trunc('month', start_time)
AS SELECT fact_table.start_time, dimen_table.id FROM fact_table JOIN dimen_table ON fact_table.id = dimen_table.id;

--按年上卷方式，根据事实表start_time分区键，创建分区物化视图
CREATE MATERIALIZED VIEW level_year enable query rewrite DISTRIBUTE BY HASH(start_time) PARTITION BY date_trunc('year', start_time)
AS SELECT fact_table.start_time, dimen_table.id FROM fact_table JOIN dimen_table ON fact_table.id = dimen_table.id;

基表按天分区，物化视图按月分区，进行上卷映射。

3.2.4 分区DDL

基表添加分区：物化视图会根据上卷规则自动添加上卷的新分区。

基表删除分区：将物化视图上卷对应分区进行失效，如果是最后一个映射，则删除物化视图的上卷分区。

有物化视图依赖的基表，不支持分区的merge, split。

3.3 视图嵌套

物化视图中的查询可以查其它物化视图，实现物化视图与物化视图的多层嵌套，默认最大支持10层，由mv_max_cascade_depth参数控制。

层层嵌套使用物化视图，构造实时数据流管道，通过级联刷新，可以实现业务数据的流式实时加工。

3.4 视图管理

--pg_matview 用于存储物化视图元信息
--pg_matview_depend 用于存储物化视图与基表（或分区）的依赖关系
--pg_matview_partition 用于存储物化分区的有效性信息
--pv_matview_detail 查询上述系统表，综合展示物化信息
--查询创建的物化视图
select * from pv_matview_detail where matview ~ 'level_';

3.5 视图刷新

刷新语法

REFRESH MATERIALIZED VIEW {[schema.]materialized_view_name} [ [DEFAULT] | [ RESTRICT ] | [ CASCADE FORWARD ] | [ CASCADE BACKWARD ] | [ CASCADE ALL ] ]

• 轻量锁：基表一级锁，不堵塞查询和DML。
• 不重复：有效的物化视图会自动跳过刷新
• 不重叠：已有刷新正在执行会跳过刷新
• 可并行：不同分区可并发刷新
• 可级联：编排为级联刷新可减少并发降低资源消耗

3.5.1 刷新方式

自动刷新：创建物化视图时，指定刷新的起始时间和刷新间隔，可实现轮询的自动刷新。

手动刷新：用户在作业中手动按需刷新，也支持强制刷新。

级联刷新：支持向上、向下、双向级联刷新物化视图。

3.5.2 刷新并发

不重复刷：如果发现被刷新的物化视图是有效的，会自动跳过刷新任务。

不重叠刷：同一分区并发刷新，后面的人会跳过。

可并发刷：不同分区支持并发刷新。

3.5.3 物化刷新与基表修改并发

刷新过程中基表无修改：提交刷新数据，并激活视图。

刷新过程中基表有修改：提交刷新数据，不激活视图。不影响直接查询物化视图和强制重写时间窗内的查询。

3.5.4 刷新编排

错峰编排：没有依赖关系的物化视图，在定义自动刷新间隔时，尽量错峰进行刷新，防止资源争抢。

级联编排：有级联依赖的物化视图，尽量选择自底向上级联刷新，减少并发调度。同时能保证数据快照的一致。

3.6 视图失效

• 全场景失效：支持所有数据修改触发失效，包括DN直连。
• 分区失效：支持识别分区级的失效。
• 并发失效：支持基表并发触发失效，且不重复失效。
• 失效一致性：失效过程中CN故障，不影响业务。
• 失效可靠性：已有CN故障或CN重启，都不影响业务。

全表操作：DML（IUD，Copy, Merge, Upsert）或DDL(InserOverWrite, truncate, drop/add/exchange partition)时，会对物化视图全表进行失效。

分区操作：DML或DDL操作分区时，CN会收集修改了哪些分区，并对物化分区进行失效。

级联失效：当存在物化视图嵌套时，会对物化视图分区或全表进行级联失效。

4. 查询重写

需要设置mv_rewrite_rule参数，选择重写规则。

1. 支持全文匹配的查询重写。

2. 支持查询条件的代数逻辑推理，如果物化视图中的数据能满足查询，则优先使用物化视图的数据。

3. 支持对子查询的物化视图重写。

通过explain命令可以查看查询是否被重写为使用物化视图。

5. 资源管控

物化视图的刷新默认使用创建者的资源池，如果需要对刷新任务做资源管控，请对创建用户进行资源池设置。

6. 使用场景

6.1 批量加工场景

批量加工场景，如果在作业流中存在反复查询的公共SQL，可以将这个SQL创建为物化视图。

不需要改造现有业务，公共SQL会自动使用物化视图的数据，实现透明加速。

优势：

1. 减少重复计算，不需改造业务，自动加速。

2. 结果物化，可持续使用。

3. 数据分层，避免暴露细节数据。

6.2 实时写入场景

实时写入/更新场景，数据实时都在变化，会导致物化视图刷新完后马上失效。

因此，我们支持设置物化视图表级属性force_rewrite_timeout，一段时间窗内不关心数据变化强制重写。

优势：

1. 像创建索引一样，实现透明加速，不需要改写业务。

2. 可以创建多个事务视图，实现报表业务。通过定时刷新，实现实时报表。

6.3 实时数据管道

数据从上游到下游，一层层的，实时进行数据加工和消费。例如：flink(实时处理)+kafka(持久化和分发)。

通过物化视图嵌套，搭建数据流管道，就可以直接在数据库内完成数据管道的实时加工。性能和可靠性大大提升。

7. 注意事项

1. 格式：推荐建成hstore_opt+turbo格式

2. 优化：尽量把执行计划调优，加hint固定计划，减少计划跳变

3. 编排：避免集中刷新，做好任务错峰编排。

4. 级联：识别好数据流管道，尽量级联刷新，减少刷新任务。

5. 快照：顶层视图刷新间隔，必须大于下层最大间隔，避免数据快照不一致。