PostgreSQL: PostgreSQL视图与物化视图
最后更新：2026-08-26
1. 你将学到
- CREATE VIEW / ALTER VIEW / DROP VIEW
- 可更新视图（简单视图支持 INSERT/UPDATE/DELETE）
- WITH CHECK OPTION 防止更新逃逸
- 物化视图（MATERIALIZED VIEW）——PostgreSQL 特色
- REFRESH MATERIALIZED VIEW / CONCURRENTLY
- 视图 vs 物化视图 vs 临时表的选型
2. 故事
Alice 是电商平台的数据工程师。运营团队每天 8:00 查看一份销售汇总报表，该报表需要关联 5 张表、聚合 3 million 条订单数据，查询耗时 2 小时。
老板说："能不能让报表秒出？"
Alice 将查询定义为物化视图，每天凌晨 4:00 自动刷新一次。预计算后的报表查询从 2 小时降到 3 秒。但物化视图数据不是实时的——这就是普通视图和物化视图的取舍。
3. Concept：普通视图
(1) CREATE VIEW 语法
SQL
CREATE [OR REPLACE] VIEW view_name [(column_aliases)] AS
SELECT ...;
视图是一个存储的查询定义，不存储数据。每次查询视图时，PostgreSQL 将视图定义展开（重写）为底层查询执行。
| 特性 | 说明 |
|---|---|
| 存储内容 | 仅存储查询文本 |
| 数据时效 | 实时，始终反映基表最新数据 |
| 性能 | 与直接执行底层查询一致 |
| 占用空间 | 几乎为零 |
▶ 示例：创建销售汇总视图
SQL
CREATE VIEW v_daily_sales AS
SELECT
order_date,
region,
COUNT(*) AS order_count,
SUM(amount) AS total_revenue,
AVG(amount) AS avg_order_value
FROM orders
GROUP BY order_date, region;
输出：
TEXT
📖 仅展示
count
-------
5
(1 row)
▶ 示例：查询视图
SQL
SELECT * FROM v_daily_sales
WHERE order_date >= '2025-01-01'
ORDER BY total_revenue DESC;
输出：
TEXT
📖 仅展示
id | name | value
----+----------+-------
1 | example | 42
(1 row)
与直接写底层 SELECT 等价，PostgreSQL 自动展开。
(2) ALTER VIEW 与 DROP VIEW
| 操作 | 语法 | 说明 |
|---|---|---|
| 重命名 | ALTER VIEW v RENAME TO v_new |
改视图名 |
| 设置默认列 | ALTER VIEW v ALTER COLUMN c SET DEFAULT d |
改列默认值 |
| 设置属主 | ALTER VIEW v OWNER TO role |
改所有者 |
| 删除 | DROP VIEW [IF EXISTS] v [CASCADE] |
CASCADE 级联删除依赖视图 |
▶ 示例：修改与删除视图
SQL
ALTER VIEW v_daily_sales RENAME TO v_daily_summary;
DROP VIEW IF EXISTS v_daily_summary CASCADE;
输出：
TEXT
📖 仅展示
-- SQL 语句执行成功
(3) 视图的展开机制
flowchart LR
A["SELECT * FROM v_daily_sales"] --> B["Rewrite<br/>expand view definition"]
B --> C["SELECT order_date, region, COUNT(*)...<br/>FROM orders<br/>GROUP BY ..."]
C --> D[Optimizer]
D --> E[Execute]
style B fill:#fff9c4
style D fill:#c8e6c9
4. Concept：可更新视图
(1) 什么视图可更新
PostgreSQL 中，满足以下条件的简单视图自动可更新（支持 INSERT / UPDATE / DELETE）：
| 条件 | 说明 |
|---|---|
| FROM 只有一张基表 | 不能 JOIN |
| 无 GROUP BY / HAVING | 不能聚合 |
| 无 DISTINCT | 不能去重 |
| 无窗口函数 | 不能有 OVER |
| 无集合操作 | 不能 UNION / INTERSECT / EXCEPT |
| SELECT 列是基表列 | 不能有表达式/计算列 |
▶ 示例：可更新视图
SQL
CREATE VIEW v_active_users AS
SELECT user_id, name, email, status
FROM users
WHERE status = 'active';
输出：
TEXT
📖 仅展示
CREATE TABLE
SQL
UPDATE v_active_users SET name = 'Alice Wang' WHERE user_id = 1;
DELETE FROM v_active_users WHERE user_id = 99;
INSERT INTO v_active_users (user_id, name, email, status)
VALUES (101, 'Charlie', 'charlie@example.com', 'active');
(2) WITH CHECK OPTION
默认情况下，通过视图 UPDATE/INSERT 的行可以"逃逸"出视图范围（例如把 status 改为 'inactive'，该行就不再出现在视图中）。WITH CHECK OPTION 禁止这种逃逸。
| 选项 | 行为 |
|---|---|
| 无 CHECK OPTION | 允许逃逸，更新后行可能不再出现在视图中 |
| WITH CHECK OPTION | 禁止逃逸，更新后行必须仍满足视图条件 |
| WITH CASCADED CHECK OPTION | 当前视图 + 依赖视图都检查（递归） |
| WITH LOCAL CHECK OPTION | 仅检查当前视图条件 |
▶ 示例：WITH CHECK OPTION 防止逃逸
SQL
CREATE VIEW v_active_users_strict AS
SELECT user_id, name, email, status
FROM users
WHERE status = 'active'
WITH CHECK OPTION;
SQL
UPDATE v_active_users_strict SET status = 'inactive' WHERE user_id = 1;
TEXT
📖 仅展示
ERROR: new row violates check option for view "v_active_users_strict"
DETAIL: Failing row contains (1, ..., inactive).
▶ 示例：可更新视图插入后再查询
SQL
INSERT INTO v_active_users (user_id, name, email, status)
VALUES (200, 'Bob', 'bob@example.com', 'active');
SELECT * FROM v_active_users WHERE user_id = 200;
TEXT
📖 仅展示
user_id | name | email | status
---------+------+--------------------+--------
200 | Bob | bob@example.com | active
5. Concept：物化视图
(1) MATERIALIZED VIEW 概述
物化视图将查询结果实际存储到磁盘，查询时直接读取预计算数据，无需重新执行底层查询。这是 PostgreSQL 的特色功能。
| 特性 | 普通视图 | 物化视图 |
|---|---|---|
| 存储内容 | 查询文本 | 查询文本 + 结果数据 |
| 数据时效 | 实时 | 刷新时才更新 |
| 查询性能 | 与底层查询一致 | 极快（读预计算结果） |
| 占用空间 | 几乎为零 | 与结果集大小一致 |
| 可更新 | 简单视图可以 | 不支持直接 DML |
| 刷新方式 | 不需要 | REFRESH MATERIALIZED VIEW |
▶ 示例：创建物化视图
SQL
CREATE MATERIALIZED VIEW mv_monthly_sales AS
SELECT
DATE_TRUNC('month', order_date)::date AS month,
region,
COUNT(*) AS order_count,
SUM(amount) AS total_revenue,
AVG(amount) AS avg_order_value
FROM orders
GROUP BY DATE_TRUNC('month', order_date), region
WITH DATA;
输出：
TEXT
📖 仅展示
count
-------
5
(1 row)
▶ 示例：查询物化视图
SQL
SELECT * FROM mv_monthly_sales
WHERE month >= '2025-01-01'
ORDER BY total_revenue DESC;
输出：
TEXT
📖 仅展示
id | name | value
----+----------+-------
1 | example | 42
(1 row)
毫秒级返回，因为数据已经预计算并存储。
(2) REFRESH 刷新
| 语法 | 行为 | 锁 | 速度 |
|---|---|---|---|
REFRESH MATERIALIZED VIEW mv |
全量刷新，替换所有数据 | 加排他锁，阻塞读取 | 较慢 |
REFRESH MATERIALIZED VIEW CONCURRENTLY mv |
增量刷新（需唯一索引） | 不阻塞读取 | 较快 |
CONCURRENTLY 是 PostgreSQL 特色：刷新期间视图仍可查询，不会阻塞业务。
▶ 示例：全量刷新
SQL
REFRESH MATERIALIZED VIEW mv_monthly_sales;
输出：
TEXT
📖 仅展示
-- SQL 语句执行成功
刷新期间所有对该物化视图的 SELECT 被阻塞。
▶ 示例：CONCURRENTLY 增量刷新
SQL
REFRESH MATERIALIZED VIEW CONCURRENTLY mv_monthly_sales;
输出：
TEXT
📖 仅展示
-- SQL 语句执行成功
前提条件：物化视图上必须有至少一个唯一索引。
SQL
CREATE UNIQUE INDEX idx_mv_monthly_sales_pk
ON mv_monthly_sales (month, region);
REFRESH MATERIALIZED VIEW CONCURRENTLY mv_monthly_sales;
(3) WITH DATA vs WITH NO DATA
| 选项 | 行为 |
|---|---|
| WITH DATA | 创建时立即填充数据（默认） |
| WITH NO DATA | 创建时不填充，首次查询前必须 REFRESH |
▶ 示例：延迟填充
SQL
CREATE MATERIALIZED VIEW mv_expensive_report AS
SELECT ... FROM ... WITH NO DATA;
REFRESH MATERIALIZED VIEW mv_expensive_report;
输出：
TEXT
📖 仅展示
CREATE TABLE
▶ 示例：自动定时刷新（pg_cron 扩展）
SQL
SELECT cron.schedule(
'refresh_monthly_sales',
'0 4 * * *',
$$REFRESH MATERIALIZED VIEW CONCURRENTLY mv_monthly_sales$$
);
输出：
TEXT
📖 仅展示
id | name | value
----+----------+-------
1 | example | 42
(1 row)
每天凌晨 4:00 自动刷新。
6. 视图 vs 物化视图 vs 临时表
| 维度 | 普通视图 | 物化视图 | 临时表 |
|---|---|---|---|
| 存储查询定义 | 是 | 是 | 否 |
| 存储数据 | 否 | 是 | 是 |
| 数据实时性 | 实时 | 手动刷新 | 手动维护 |
| 查询性能 | 依赖底层查询 | 极快 | 快 |
| 跨会话持久化 | 是 | 是 | 否（会话结束消失） |
| 支持索引 | 否（用基表索引） | 是 | 是 |
| 支持 DML | 简单视图可更新 | 否 | 是 |
| 典型场景 | 简化查询 | 报表预计算 | 临时中间结果 |
classDiagram
class View {
+存储查询文本
+实时数据
+简单视图可DML
+WITH CHECK OPTION
}
class MaterializedView {
+存储查询文本+数据
+REFRESH刷新
+CONCURRENTLY
+可建索引
+WITH DATA/NO DATA
}
class TempTable {
+仅存数据
+会话结束消失
+完全可DML
+可建索引
}
View <|-- MaterializedView : 扩展
MaterializedView ..|> TempTable : 性能相似
7. 视图管理最佳实践
(1) 命名规范
| 类型 | 推荐前缀 | 示例 |
|---|---|---|
| 普通视图 | v_ |
v_active_users |
| 物化视图 | mv_ |
mv_monthly_sales |
| 临时表 | tmp_ |
tmp_import_data |
(2) 视图依赖与安全
| 操作 | 风险 | 解决 |
|---|---|---|
| 删除基表 | 视图失效 | DROP TABLE CASCADE 自动删除依赖视图 |
| 修改基表列 | 视图可能报错 | 用 CREATE OR REPLACE VIEW 更新定义 |
| 权限控制 | 视图可限制列可见性 | GRANT SELECT ON view TO role |
▶ 示例：用视图实现列级权限
SQL
CREATE VIEW v_user_public AS
SELECT user_id, name
FROM users;
GRANT SELECT ON v_user_public TO reporter_role;
REVOKE SELECT ON users FROM reporter_role;
输出：
TEXT
📖 仅展示
CREATE TABLE
reporter_role 只能看到 user_id 和 name，看不到 email 等敏感列。
8. Comprehensive Example
Alice 的报表优化——物化视图预计算 + CONCURRENTLY 刷新 + 视图简化查询：
SQL
CREATE MATERIALIZED VIEW mv_sales_report AS
SELECT
DATE_TRUNC('month', o.order_date)::date AS month,
p.category,
o.region,
COUNT(*) AS order_count,
COUNT(DISTINCT o.customer_id) AS unique_customers,
SUM(o.amount) AS total_revenue,
SUM(o.amount) FILTER (WHERE o.amount >= 50000) AS big_deal_revenue,
AVG(o.amount) AS avg_order_value,
SUM(oi.quantity * oi.unit_price) AS total_gmv
FROM orders o
JOIN order_items oi ON o.order_id = oi.order_id
JOIN products p ON oi.product_id = p.product_id
GROUP BY DATE_TRUNC('month', o.order_date), p.category, o.region
WITH DATA;
CREATE UNIQUE INDEX idx_mv_sales_report_pk
ON mv_sales_report (month, category, region);
CREATE VIEW v_sales_dashboard AS
SELECT
month,
region,
SUM(total_revenue) AS region_revenue,
SUM(order_count) AS region_orders,
SUM(unique_customers) AS region_customers
FROM mv_sales_report
GROUP BY month, region;
REFRESH MATERIALIZED VIEW CONCURRENTLY mv_sales_report;
9. 执行流程
物化视图的创建与刷新流程：
flowchart TD
A[CREATE MATERIALIZED VIEW] --> B[执行底层查询]
B --> C[写入结果到磁盘]
C --> D[可创建索引]
D --> E[查询直接读磁盘数据]
E --> F{需要刷新?}
F -->|REFRESH| G[重新执行底层查询]
G --> H[替换旧数据]
H --> E
F -->|CONCURRENTLY| I[增量对比刷新]
I --> J[不阻塞读取]
J --> E
style A fill:#e1f5fe
style E fill:#c8e6c9
style I fill:#fff9c4
| 步骤 | 说明 |
|---|---|
| 创建 | 执行底层查询，结果持久化到磁盘 |
| 查询 | 直接读取磁盘数据，不执行底层查询 |
| 刷新 | 重新执行底层查询，替换旧数据 |
| CONCURRENTLY | 增量刷新，刷新期间不阻塞查询 |
❓ 常见问题
Q 普通视图影响性能吗？
A 不。视图只是查询重写，与直接写底层 SQL 性能完全一致。复杂视图不会额外增加开销。
Q 为什么通过视图 INSERT 后查不到数据？
A 可能插入的行不满足视图 WHERE 条件。用 WITH CHECK OPTION 防止此类问题。
Q CONCURRENTLY 刷新为什么需要唯一索引？
A CONCURRENTLY 通过对比新旧数据做增量刷新，唯一索引用于标识每行。没有唯一索引会报错。
Q 物化视图支持直接 UPDATE/DELETE 吗？
A 不支持。物化视图是只读的，只能通过 REFRESH 刷新。如需修改，刷新基表数据后重新 REFRESH。
Q WITH CASCADED CHECK OPTION 和 WITH LOCAL CHECK OPTION 有什么区别？
A CASCADED 检查当前视图及所有依赖视图的条件；LOCAL 只检查当前视图的条件。嵌套视图时选 CASCADED 更安全。
Q 物化视图可以建立在其他物化视图上吗？
A 可以。PostgreSQL 支持嵌套物化视图，但刷新时需手动按依赖顺序依次刷新。
📖 小节
- 普通视图只存储查询文本，查询时重写展开，数据始终实时
- 简单视图（单表、无聚合）自动可更新，支持 INSERT/UPDATE/DELETE
- WITH CHECK OPTION 防止 DML 操作导致行逃逸出视图范围
- 物化视图存储查询结果到磁盘，查询极快但数据非实时
- REFRESH MATERIALIZED VIEW 全量刷新，CONCURRENTLY 增量刷新不阻塞读取
- CONCURRENTLY 刷新前提：物化视图上必须有唯一索引
- 视图可用于列级权限控制，隐藏敏感列
📝 作业
- ⭐ 创建一个视图 v_recent_orders，查询最近 30 天的订单，包含 customer_name 和 product_name
- ⭐ 创建一个可更新视图 v_active_customers，只显示 status = 'active' 的客户，并加上 WITH CHECK OPTION
- ⭐⭐ 创建物化视图 mv_daily_category_sales，按天和品类汇总销售额，并添加唯一索引支持 CONCURRENTLY 刷新
- ⭐⭐ 设计一个嵌套视图方案：物化视图预计算明细数据，普通视图在物化视图上做维度汇总
- ⭐⭐⭐ 写一个 pg_cron 定时任务：每天凌晨 3:00 刷新 mv_daily_category_sales，并监控刷新耗时超过 5 分钟时发送告警日志
