物化视图概述

基本概念

异步物化视图和同步物化视图

创建物化视图后，当基表中的数据发生变化时，需要通过刷新物化视图更新数据变化。根据刷新方式，可分为异步物化视图和同步物化视图。

物化视图优势

• 提升查询性能

  物化视图预先计算并存储查询结果，当发起查询时，可以查询物化视图直接返回结果，从而提高查询性能。

• 降低计算资源消耗

  多次执行复杂查询通常需要显著的CPU和内存资源。物化视图通过避免这些重复计算，有助于减少数据库资源的负荷。

• 数仓建模

  物化视图可以作为数据访问层，支持复杂的业务逻辑，允许用户访问经预处理的数据，无需关心底层复杂SQL逻辑。

物化视图相关语法

创建物化视图。

 1
 2
 3
 4
 5
 6
 7
 8
 9
10

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} [, ... ] ) ]
[ TABLESPACE tablespace_name ]
[ DISTRIBUTE BY { REPLICATION | ROUNDROBIN | { HASH ( column_name [,...] ) } } ]
[ PARTITION BY {(column_name) | date_trunc('time_unit', column_name)} ]
AS query;

刷新物化视图。

1
2

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

删除物化视图。

1
2

DROP MATERIALIZED VIEW [ IF EXISTS ]
{[schema.]materialized_view_name} [, ...] [ CASCADE | RESTRICT ];

修改物化视图。

 1
 2
 3
 4
 5
 6
 7
 8
 9
10

ALTER MATERIALIZED VIEW [ IF EXISTS ] { materialized_view_name }[ ENABLE | DISABLE ] QUERY REWRITE;
ALTER MATERIALIZED VIEW [ IF EXISTS ] { materialized_view_name }
    REFRESH [ [DEFAULT] | [ RESTRICT ] | [ CASCADE FORWARD ] | [ CASCADE BACKWARD ] | [ CASCADE ALL ] ] [ COMPLETE ] [ ON DEMAND ] [ [ START WITH (timestamptz) ] | [ EVERY (interval) ] ];
ALTER MATERIALIZED VIEW [ IF EXISTS ] { materialized_view_name }
    REFRESH [DEFAULT] | [ RESTRICT ] | [ CASCADE FORWARD ] | [ CASCADE BACKWARD ] | [ CASCADE ALL ] ;
ALTER MATERIALIZED VIEW { materialized_view_name }
    OWNER TO new_owner;
ALTER MATERIALIZED VIEW { materialized_view_name }
    SET ( {storage_parameter = value} [, ... ] )
    | RESET ( storage_parameter [, ... ] );

物化视图语法具体参数说明请参考CREATE MATERIALIZED VIEW和ALTER MATERIALIZED VIEW章节。

物化视图应用场景

物化视图适用如下场景：

• 查询结果不经常改变，即基表稳定且执行频次高的查询。
• 查询包含非常耗时的操作，比如聚合、连接操作等。对于涉及多个表连接和聚合操作的查询，物化视图可以将这些耗时的操作预先计算并存储起来，从而避免在每次查询时都重新执行这些操作。
• 查询结果仅涉及表中的很小部分数据。

  表2 物化视图和视图、表的对比

  数据库对象	性能提升	持久化存储	业务逻辑简化
  物化视图	√	√	√
  视图	-	-	√
  表	-	√	-

操作权限

• 创建物化视图时需要schema的CREATE权限和基表或列的SELECT权限。
• 查询物化视图需要物化视图的SELECT权限。
• 刷新需要物化视图的INSERT和基表或列的SELECT权限。
• 删除物化视图需要有DROP TABLE的权限。

使用限制

• 不支持包含临时表，包括全局临时表、volatile临时表和普通临时表。
• 不支持物化视图定义中的CTE包含除SELECT语句以外的其他DML。
• 不支持基表上存在脱敏策略和行级访问控制或者基表属于私有用户。脱敏策略和行级访问控制可能会导致结果集错误，而私有用户可能导致刷新物化视图失败等问题
• 910.200及以上版本支持stable和volatile函数。需注意：物化视图无法感知函数内容变化。如果需要函数变化尽快反馈到物化视图结果，指定REFRESH MATERIALIZED VIEW RESTRICT进行物化视图刷新或者跟随数据更新反馈到物化视图。
• 一些影响查询执行时行为的参数修改，物化视图无法感知参数变化，需要再次刷新物化视图更新结果。

使用建议

• 存储资源：物化视图会占用额外的存储空间，需要考虑磁盘容量。
• 刷新维护：自动或手动刷新物化视图会消耗系统资源，如果基础表数据变化频繁，刷新操作可能会影响系统性能。
• 数据一致性和实时性：异步物化视图中的数据可能不是实时的，即数据不会随着原始数据的更新而自动更新。如果基础表数据发生变化，视图中的数据可能会过时，需要定期刷新以保持数据一致性。
• 设计复杂性：物化视图的设计和创建需要仔细考虑预期的查询模式和数据访问模式，以便实现最佳的性能优化。