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 可降低读取阻塞,但物化视图必须已有数据,并且至少存在一个覆盖所有行、只包含列名的唯一索引。
刷新频率应由业务允许的数据延迟决定。小时报表可按小时刷新,日终汇总可在结算后刷新;刷新任务失败时,还应监控最后成功时间,避免长期读取旧结果。
按自己的节奏,确认真正理解。