PostgreSQL / 系统课时

创建与刷新物化视图

创建与刷新物化视图

本课解决统计查询耗时较长的问题。你将把聚合结果保存到物化视图中,观察底层数据变化后结果为何不会自动更新,并掌握手动刷新及并发刷新的适用条件。

学习目标

  • 创建并查询物化视图
  • 识别物化视图的数据延迟
  • 根据业务要求选择刷新方式

物化视图的核心特点

物化视图会实际存储查询结果,读取时通常比重复执行复杂连接或聚合更快。代价是结果存在时效性,底层表变化后必须刷新,物化视图才会重新计算。

它适合报表、排行榜、周期统计和读取频繁但允许短暂延迟的场景,不适合要求每次查询都看到最新事务结果的核心交易查询。

创建并观察旧数据

先运行 psql -d <DATABASE>,再执行以下完整脚本。

sql
DROP MATERIALIZED VIEW IF EXISTS daily_sales_mv;
DROP TABLE IF EXISTS sales_order;

CREATE TABLE sales_order (
    id integer PRIMARY KEY,
    order_date date NOT NULL,
    amount numeric(10, 2) NOT NULL CHECK (amount >= 0)
);

INSERT INTO sales_order (id, order_date, amount)
VALUES
    (1, DATE '2026-07-28', 100.00),
    (2, DATE '2026-07-28', 80.00),
    (3, DATE '2026-07-29', 50.00);

CREATE MATERIALIZED VIEW daily_sales_mv AS
SELECT
    order_date,
    count(*) AS order_count,
    sum(amount) AS total_amount
FROM sales_order
GROUP BY order_date;

SELECT * FROM daily_sales_mv ORDER BY order_date;

INSERT INTO sales_order (id, order_date, amount)
VALUES (4, DATE '2026-07-29', 70.00);

SELECT * FROM daily_sales_mv ORDER BY order_date;

预期结果

text
首次查询:
 order_date | order_count | total_amount
------------+-------------+--------------
 2026-07-28 |           2 |       180.00
 2026-07-29 |           1 |        50.00

插入新订单后再次查询:
 order_date | order_count | total_amount
------------+-------------+--------------
 2026-07-28 |           2 |       180.00
 2026-07-29 |           1 |        50.00

第二次结果没有变化,因为物化视图仍保存创建时的结果。

刷新物化视图

执行刷新后,PostgreSQL 会重新运行定义查询并替换已保存结果。

sql
REFRESH MATERIALIZED VIEW daily_sales_mv;

SELECT * FROM daily_sales_mv ORDER BY order_date;

预期结果

text
 order_date | order_count | total_amount
------------+-------------+--------------
 2026-07-28 |           2 |       180.00
 2026-07-29 |           2 |       120.00
(2 rows)

刷新策略与并发读取

普通 REFRESH MATERIALIZED VIEW 刷新期间会阻塞对该物化视图的读取。REFRESH MATERIALIZED VIEW CONCURRENTLY 可降低读取阻塞,但物化视图必须已有数据,并且至少存在一个覆盖所有行、只包含列名的唯一索引。

刷新频率应由业务允许的数据延迟决定。小时报表可按小时刷新,日终汇总可在结算后刷新;刷新任务失败时,还应监控最后成功时间,避免长期读取旧结果。

按自己的节奏,确认真正理解。

目录与阅读设置

阅读设置

17

记录一个想法

创建与刷新物化视图

0 / 10000 · 笔记保存到账号。未提交的修改会在关闭、离开或刷新后丢失。

登录哪都通