物化视图（Materialized View）

概述

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

主要应用场景

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

物化视图特点

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

物化视图类型

上面提到当物化视图所基于的基础数据发生变化时，我们需要更新物化视图以反映这些变化，这个操作称为刷新物化视图（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 - 需等值条件
不支持的 Query 类型	无	- 所有 VOLATILE 的函数 - Order by - Exists / In 等子查询 - Union
是否过期	在上次刷新后，如果基础表发生变化，物化视图数据被视为过期	在上次刷新后，如果基础表发生变化，物化视图数据被视为过期
刷新代价	和基础表全量数据相关	和上次刷新后基础表的变动数据量和Query 增量计算复杂度相关
典型刷新间隔	天、小时级别	秒、分钟级别

VOLATILE 易变函数，是指任意输入时，函数结果可能随时发生变化，即使连续多次调用，还是同样的输入，其值可能不同。典型的 VOLATILE 函数包括：返回当前时间（如 clock_timestamp()等），随机数相关（random(), setseed()等），序列相关（nextval, setval等），环境变量相关（current_user 等），这些函数不能用在增量物化视图中。

可以通过下面 Query 获得完整的VOLATILE函数列表：

SELECT proname, provolatile FROM pg_proc WHERE provolatile = 'v';

全量物化视图管理

创建全量物化视图

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

参数说明:

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

示例:

-- 用户表
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 [, ...] ) ] [USING [ ROW | COLUMNAR | HYBRID ]]
    WITH (refresh_mode='incremental') AS query;

参数说明:

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'),
('ORD10004', 103, '2023-06-03 14:20:00', 450.00, 'P1006,P1007', '广州市天河区', 'created'),
('ORD10005', 102, '2023-06-03 16:45:00', 120.00, 'P1008', '上海市浦东新区', 'created'),
('ORD10006', 101, '2023-06-04 09:30:00', 75.50, 'P1009', '北京市海淀区', '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'),
('PAY20004', 'ORD10004', '2023-06-03 14:25:30', 450.00, 'alipay', 'success'),
('PAY20005', 'ORD10005', '2023-06-03 16:50:15', 120.00, 'wechat', 'failed');
 
-- 订单与支付关联的物化视图
CREATE MATERIALIZED VIEW order_payment_summary WITH (refresh_mode='incremental') 
USING HYBRID
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,
    SUM(CASE WHEN payment_status = 'processing' THEN 1 ELSE 0 END) AS processing_payments
FROM
    order_payment_summary
GROUP BY
    user_id;
    
    
-- 更新ORD10001订单状态为已支付
UPDATE orders SET order_status = 'paid' WHERE order_id = 'ORD10001';
 
-- 更新ORD10002订单状态为已发货
UPDATE orders SET order_status = 'shipped' WHERE order_id = 'ORD10002';
 
-- 更新ORD10003的支付状态为成功（之前是失败）
UPDATE payments SET payment_status = 'success', payment_time = '2023-06-02 10:15:00'
WHERE payment_id = 'PAY20003';
 
-- 更新ORD10003订单状态为已支付
UPDATE orders SET order_status = 'paid' WHERE order_id = 'ORD10003';
 
-- 为ORD10006订单添加支付记录（处理中状态）
INSERT INTO payments VALUES
('PAY20006', 'ORD10006', '2023-06-04 09:35:00', 75.50, 'credit_card', 'processing');
 
-- 更新ORD10005订单金额（因为有商品变更）
UPDATE orders SET total_amount = 150.00, product_list = 'P1008,P1010'
WHERE order_id = 'ORD10005';
 
-- 更新对应的支付金额
UPDATE payments SET payment_amount = 150.00
WHERE order_id = 'ORD10005';
 
-- 刷新Materialized View的数据
REFRESH MATERIALIZED VIEW order_payment_summary;
 
-- 按用户统计订单和支付情况（更新后的统计）
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,
    SUM(CASE WHEN payment_status = 'processing' THEN 1 ELSE 0 END) AS processing_payments
FROM
    order_payment_summary
GROUP BY
    user_id;

刷新增量物化视图数据

REFRESH MATERIALIZED VIEW view_name;

参数说明:

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

示例:

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

注意事项:

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

删除增量物化视图

语法和参数说明与全量物化视图相同。

修改增量物化视图的定义

ProtonBase 支持部分修改增量物化定义的能力，支持在最终结果集增加列、删除列操作，在修改物化视图定义后，Refresh 会触发全量重刷视图。

ALTER MATERIALIZED VIEW [ IF EXISTS ] view_name AS query;

示例:

ALTER MATERIALIZED VIEW order_payment_summary AS
SELECT
    o.order_id,
    o.user_id,
    o.order_time,
    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;

如果修改的范围超过了增加列、减少列场景，有可能会触发物化视图结构性不适配，在更新时会提示"unsupported feature: alter primary key while altering query"，表示不支持这个定义变更。

修改增量物化视图的存储格式

ALTER MATERIALIZED VIEW [ IF EXISTS ] view_name SET ACCESS METHOD [ ROW | COLUMNAR | HYBRID ];

为增量物化视图增加索引

CREATE INDEX CONCURRENTLY index_name ON MV_NAME (column_name);

物化视图元数据

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
查看定义中过多嵌套的 SQL，优化查询结构
配合索引提升物化视图查询性能

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

CREATE INDEX idx_target_view ON target_view (column_name);

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

物化视图权限要求

在 ProtonBase 中，物化视图的权限体系与 PostgreSQL 保持完全一致，支持标准的对象访问控制、角色与权限继承、GRANT/REVOKE 授权、所有权管理等。主要规则如下：

权限总览

操作	所需权限	说明
创建物化视图	`CREATE` on schema	需对 schema 拥有 CREATE 权限，对定义中涉及的表/视图拥有 SELECT 权限
查询物化视图	`SELECT` on materialized view	需对物化视图拥有 SELECT 权限
刷新物化视图	`SELECT` on source table, `USAGE` on schema	对定义中涉及的表/视图拥有 SELECT 权限和 Schema 的 Usage 权限
修改定义/存储格式	`ALTER` on materialized view	需对物化视图拥有 ALTER 权限
删除物化视图	`DROP` on materialized view	需对物化视图拥有 DROP 权限

权限细则

对象所有权：物化视图的所有者拥有全部操作权限（包括 REFRESH、ALTER、DROP 等），可通过 ALTER MATERIALIZED VIEW ... OWNER TO ... 转移所有权。
授权与回收：可使用 GRANT/REVOKE 授权或回收 SELECT、ALTER、DROP 等权限，语法与 PostgreSQL 完全一致。
角色与继承：支持角色继承与组角色，权限可通过角色链传递。
访问控制：所有权限检查均与 PG 保持一致，支持细粒度的对象级授权。
刷新权限：只有物化视图所有者可执行 REFRESH，无法通过 GRANT 授权给其他用户。如需多用户刷新，可通过转移所有权、使用 SECURITY DEFINER 函数或角色切换等方式实现。

常用授权操作示例

-- 授予 user1 查询权限
GRANT SELECT ON MATERIALIZED VIEW user_order_summary TO user1;
 
-- 授予 user1 修改定义权限
GRANT ALTER ON MATERIALIZED VIEW user_order_summary TO user1;
 
-- 授予 user1 删除权限
GRANT DROP ON MATERIALIZED VIEW user_order_summary TO user1;
 
-- 撤销 user1 的查询权限
REVOKE SELECT ON MATERIALIZED VIEW user_order_summary FROM user1;
 
-- 转移所有权
ALTER MATERIALIZED VIEW user_order_summary OWNER TO user1;

物化视图与索引

物化视图作为一种物理存储的表，完全支持索引的创建和使用，这可以显著提升查询性能：

索引优势

查询加速: 在物化视图上创建索引可以显著提高查询性能，特别是对于点查询和范围查询
排序优化: 索引可以帮助优化ORDER BY操作，避免额外的排序开销
连接优化: 在连接键上创建索引可以优化物化视图与其他表的连接操作

索引创建示例

-- 在物化视图的user_id列上创建二级索引
CREATE INDEX idx_user_order_summary_user_id ON user_order_summary (user_id);
 
-- 在物化视图的region列上创建索引，优化按地区查询
CREATE INDEX idx_user_order_summary_region ON user_order_summary (region);
 
-- 创建复合索引优化复杂查询
CREATE INDEX idx_user_order_summary_vip_region ON user_order_summary (vip_level, region);

最佳实践:

在经常用于WHERE条件、JOIN条件和ORDER BY的列上创建索引
定期审查索引使用情况，删除不常用的索引
对于频繁查询但不频繁刷新的物化视图，可以创建更多索引来优化查询性能