产品指南
表设计
物化视图

物化视图（Materialized View）

概述

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

主要应用场景

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

物化视图特点

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

物化视图管理

物化视图完整的命令请参考 CREATE MATERIALIZED VIEW，ALTER MATERIALIZED VIEW，DROP MATERIALIZED VIEW。

创建物化视图

CREATE MATERIALIZED VIEW [ IF NOT EXISTS ] table_name
    [ (column_name [, ...] ) ]
    [ USING method ]
    [ WITH ( storage_parameter [= value] [, ... ] ) ]
    [ TABLESPACE tablespace_name ]
    AS query
    [ WITH [ NO ] DATA ]

参数说明:

  • IF NOT EXISTS: 如果视图已存在，则不会报错
  • view_name: 物化视图名称
  • column_name: 可选，指定视图中的列名
  • USING: 可选，指定物化视图的存储格式
  • TABLESPACE：可选，指定物化视图存储的 tablespace
  • 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 view_name [ WITH [ NO ] DATA ];

参数说明:

  • view_name: 要刷新的物化视图名称
  • WITH [ NO ] DATA:
    • WITH DATA(默认): 立即使用视图定义的查询语句刷新数据。
    • WITH NO DATA: 将物化视图数据清空，此时物化视图将无法访问，直到下一次 REFRESH WITH DATA 之后才可以。

示例:

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

注意事项:

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

删除物化视图

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;

注意事项:

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

物化视图权限要求

在 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 权限

权限细则

  1. 对象所有权：物化视图的所有者拥有全部操作权限（包括 REFRESH、ALTER、DROP 等），可通过 ALTER MATERIALIZED VIEW ... OWNER TO ... 转移所有权。
  2. 授权与回收：可使用 GRANT/REVOKE 授权或回收 SELECT、ALTER、DROP 等权限，语法与 PostgreSQL 完全一致。
  3. 角色与继承：支持角色继承与组角色，权限可通过角色链传递。
  4. 访问控制：所有权限检查均与 PG 保持一致，支持细粒度的对象级授权。
  5. 刷新权限：只有物化视图所有者可执行 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的列上创建索引
  • 定期审查索引使用情况，删除不常用的索引
  • 对于频繁查询但不频繁刷新的物化视图，可以创建更多索引来优化查询性能
半结构化数据 (JSONB)增量物化视图