物化视图（Materialized View）

功能说明

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

物化视图主要场景

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

物化视图的特点

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

如何使用物化视图

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

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

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

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

物化视图的类型

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

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

全量物化视图：在刷新物化视图时，我们会重新执行视图定义中的查询语句，并使用最新的查询结果全量替换物化视图中的内容。
增量物化视图：在刷新物化视图时，我们会分析物化视图定义的查询语句，并计算自上次刷新以来的变化，然后将这些变化合并到物化视图中。

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

	全量物化视图	增量物化视图
支持的 Query 类型	所有	- Project - ProjectSet - Where - Distinct - Aggregate - SUM/COUNT/MAX/MIN/AVG - Agg with Filter 和 Distinct agg - Over window - Inner join - Left/Right/Full outer join - outer join 需要是等值条件
不支持的 Query 类型	无	- 所有 VOLATILE 的函数 - Order by - Exists / In 等子查询 - Union
是否过期	在上次刷新后，如果基础表发生变化，物化视图数据被视为过期	在上次刷新后，如果基础表发生变化，物化视图数据被视为过期
刷新代价	和基础表全量数据相关	和上次刷新后基础表的变动数据量和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: 是否在创建时填充数据

示例

-- 将使用电商在线交易背景，创建两个基础表(订单表和用户表)，然后构建一个多表关联的物化视图，并提供完整的SQL示例。
-- 用户表
CREATE TABLE users (
    user_id INT PRIMARY KEY,
    username VARCHAR(50) NOT NULL,
    email VARCHAR(100) NOT NULL,
    registration_date TIMESTAMP NOT NULL,
    vip_level INT DEFAULT 0,
    region VARCHAR(50),
    last_login TIMESTAMP
);
 
-- 插入示例数据
INSERT INTO users VALUES
(1, 'john_doe', 'john@example.com', '2023-01-15 10:00:00', 2, 'North', '2023-06-01 08:30:00'),
(2, 'jane_smith', 'jane@example.com', '2023-02-20 14:15:00', 1, 'South', '2023-06-02 16:45:00'),
(3, 'mike_brown', 'mike@example.com', '2023-03-10 09:30:00', 0, 'East', '2023-06-01 12:20:00'),
(4, 'sarah_wang', 'sarah@example.com', '2023-04-05 11:45:00', 3, 'West', '2023-06-03 10:10:00');
 
-- 订单表
CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    user_id INT NOT NULL,
    order_date TIMESTAMP NOT NULL,
    total_amount DECIMAL(10,2) NOT NULL,
    payment_method VARCHAR(20),
    status VARCHAR(20) DEFAULT 'processing',
    delivery_address TEXT
);
 
-- 插入示例数据
INSERT INTO orders VALUES
(1001, 1, '2023-05-10 09:15:00', 199.99, 'credit_card', 'delivered', '123 North St'),
(1002, 2, '2023-05-12 14:30:00', 89.50, 'paypal', 'shipped', '456 South Ave'),
(1003, 1, '2023-05-15 11:20:00', 249.99, 'credit_card', 'processing', '123 North St'),
(1004, 3, '2023-05-18 16:45:00', 59.99, 'debit_card', 'delivered', '789 East Blvd'),
(1005, 4, '2023-05-20 10:10:00', 399.99, 'credit_card', 'shipped', '321 West Rd'),
(1006, 2, '2023-05-22 13:25:00', 129.99, 'paypal', 'processing', '456 South Ave');
 
-- 创建用户订单汇总的物化视图
CREATE MATERIALIZED VIEW user_order_summary AS
SELECT
    u.user_id,
    u.username,
    u.email,
    u.vip_level,
    u.region,
    COUNT(o.order_id) AS total_orders,
    SUM(o.total_amount) AS total_spending,
    MAX(o.order_date) AS last_order_date,
    AVG(o.total_amount) AS avg_order_value,
    STRING_AGG(DISTINCT o.payment_method, ', ') AS payment_methods_used
FROM
    users u
LEFT JOIN
    orders o ON u.user_id = o.user_id
WHERE
    o.status != 'cancelled' OR o.status IS NULL
GROUP BY
    u.user_id, u.username, u.email, u.vip_level, u.region
WITH DATA;
 
 
-- 手动刷新物化视图
REFRESH MATERIALIZED VIEW user_order_summary;
 
-- 查询 VIP 用户消费情况
SELECT
    username,
    vip_level,
    total_orders,
    total_spending,
    avg_order_value
FROM
    user_order_summary
WHERE
    vip_level > 0
ORDER BY
    total_spending DESC;
 
-- 按地区统计销售情况
SELECT
    region,
    COUNT(user_id) AS customer_count,
    SUM(total_orders) AS order_count,
    SUM(total_spending) AS region_revenue,
    ROUND(SUM(total_spending)/COUNT(user_id), 2) AS revenue_per_customer
FROM
    user_order_summary
GROUP BY
    region
ORDER BY
    region_revenue DESC;

需要注意，在创建物化视图时，如果使用了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;

注意事项

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

删除物化视图

语法

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;

注意事项

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

增量物化视图管理

创建增量物化视图

语法

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

参数说明:

refresh_mode: 刷新模式，incremental表示增量刷新模式
其他参数含义参考全量物化视图定义示例

-- 订单表
CREATE TABLE orders (
    order_id VARCHAR(20) PRIMARY KEY,
    user_id INT NOT NULL,
    order_time TIMESTAMP NOT NULL,
    total_amount DECIMAL(10,2) NOT NULL,
    product_list TEXT, -- 商品ID列表，用逗号分隔
    shipping_address TEXT,
    order_status VARCHAR(20) DEFAULT 'created' -- created, paid, shipped, completed
);
 
-- 插入示例数据
INSERT INTO orders VALUES
('ORD10001', 101, '2023-06-01 10:00:00', 299.99, 'P1001,P1002', '北京市海淀区', 'created'),
('ORD10002', 102, '2023-06-01 11:30:00', 159.50, 'P1003', '上海市浦东新区', 'created'),
('ORD10003', 101, '2023-06-02 09:15:00', 89.99, 'P1004,P1005', '北京市海淀区', 'created');
 
-- 支付表
CREATE TABLE payments (
    payment_id VARCHAR(20) PRIMARY KEY,
    order_id VARCHAR(20) NOT NULL,
    payment_time TIMESTAMP NOT NULL,
    payment_amount DECIMAL(10,2) NOT NULL,
    payment_method VARCHAR(20), -- alipay, wechat, credit_card
    payment_status VARCHAR(20) -- success, failed, processing
);
 
-- 插入示例数据
INSERT INTO payments VALUES
('PAY20001', 'ORD10001', '2023-06-01 10:05:23', 299.99, 'alipay', 'success'),
('PAY20002', 'ORD10002', '2023-06-01 11:35:45', 159.50, 'wechat', 'success'),
('PAY20003', 'ORD10003', '2023-06-02 09:20:10', 89.99, 'credit_card', 'failed');
 
-- 订单与支付关联的物化视图
CREATE MATERIALIZED VIEW order_payment_summary WITH (refresh_mode='incremental') AS
SELECT
    o.order_id,
    o.user_id,
    o.order_time,
    o.total_amount,
    o.order_status,
    p.payment_id,
    p.payment_time,
    p.payment_method,
    p.payment_status,
    CASE
        WHEN p.payment_status = 'success' THEN 'paid'
        WHEN p.payment_status = 'failed' THEN 'payment_failed'
        ELSE 'awaiting_payment'
    END AS payment_status_category
FROM
    orders o
LEFT JOIN
    payments p ON o.order_id = p.order_id;
 
-- 刷新Materialized View的数据
REFRESH MATERIALIZED VIEW order_payment_summary;
 
-- 查询支付失败的订单
SELECT * FROM order_payment_summary
WHERE payment_status_category = 'payment_failed';
 
-- 按用户统计订单和支付情况
SELECT
    user_id,
    COUNT(*) AS total_orders,
    SUM(CASE WHEN payment_status = 'success' THEN 1 ELSE 0 END) AS paid_orders,
    SUM(CASE WHEN payment_status = 'failed' THEN 1 ELSE 0 END) AS failed_payments
FROM
    order_payment_summary
GROUP BY
    user_id;

刷新增量物化视图数据

语法：

REFRESH MATERIALIZED VIEW view_name;

参数说明:

view_name: 要刷新的物化视图名称

示例

--增量刷新，该语句会立即执行视图定义的查询，增量更新mv_sales中的数据。刷新期间不会锁表。
REFRESH MATERIALIZED VIEW sales_mv;

注意事项

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

删除物化视图

语法

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

参数说明

同全量物化视图参数含义

物化视图元数据

pg_matviews 是 ProtonBase 提供的系统视图，可用于查询数据库中创建的物化视图的元数据。通过它可以高效地管理物化视图，包括查询、监控和优化其性能。在实际场景中，我们可以利用 pg_matviews 来执行以下常见管理工作：

列出所有物化视图

如果需要查看当前数据库中有哪些物化视图，可以简单地查询 pg_matviews：

SELECT matviewname AS view_name, schemaname AS schema
FROM pg_matviews;

这样可以快速定位所有的物化视图及其所属的 schema，为进一步管理提供信息。

查看物化视图的定义

需要识别物化视图的基础查询时，可以查询 pg_matviews 中的 definition 列。例如：

SELECT matviewname AS view_name, definition
FROM pg_matviews
WHERE matviewname = 'target_view';

这对于理解物化视图的底层逻辑、调试或优化非常有用。

检查某个物化视图是否自动填充数据

通过查看 ispopulated 字段，可以判断物化视图被创建时是否已经执行了 REFRESH：

SELECT matviewname AS view_name, ispopulated
FROM pg_matviews
WHERE matviewname = 'target_view';

如果 ispopulated 为 false，表示视图未填充，需要手动执行 REFRESH MATERIALIZED VIEW。

审查和优化物化视图

通过结合元数据，可以对存储和性能做优化。例如：

避免使用 OUTER JOIN，尽量使用 INNER JOIN
检查 tablespace 是否合理。
查看定义中过多嵌套的 SQL，优化查询结构。
配合索引提升物化视图查询性能。

例如，对于频繁被查询的物化视图，添加索引来加速查询：

CREATE INDEX idx_target_view ON target_view (column_name);

有关物化视图更多命令参考 CREATE MATERIALIZED VIEW，ALTER MATERIALIZED VIEW，DROP MATERIALIZED VIEW。