深入解析：PostgreSQL 视图与物化视图（View / Materialized View）详解

✅ PostgreSQL 视图与物化视图（View / Materialized View）详解

视图（View）和物化视图（Materialized View）是 PostgreSQL 中非常重要的逻辑表抽象机制，用于简化复杂查询、提升安全性、提高性能。

---

一、核心概念对比

特性	视图（View）	物化视图（Materialized View）
数据存储	❌ 不存储数据，是“虚拟表”	✅ 存储查询结果（物理数据）
查询性能	每次查询都执行原始 SQL（实时）	查询快（直接查缓存数据），但数据可能过期
数据实时性	✅ 实时（总是最新）	❌ 需手动或自动刷新（REFRESH）
创建语句	CREATE VIEW ... AS SELECT ...	CREATE MATERIALIZED VIEW ... AS SELECT ...
刷新机制	无需刷新	REFRESH MATERIALIZED VIEW [CONCURRENTLY]
是否支持索引	❌（不能直接建索引）	✅（可建索引加速查询）
是否可更新（可写）	✅ 简单视图支持 INSERT/UPDATE/DELETE	❌ 不可直接更新（需刷新）
占用存储空间	❌ 几乎不占	✅ 占用（等于结果集大小）
适用场景	简化查询、权限控制、逻辑抽象	性能优化、报表、聚合缓存、大数据查询

简单理解：

• 视图 = 保存的 SELECT 语句（实时执行）
• 物化视图 = 保存的 SELECT 结果（快照缓存）

---

一、视图（View）详解

---

✅ 1. 创建视图

CREATE [OR REPLACE] VIEW view_name AS
SELECT column1, column2, ...
FROM table_name
[WHERE ...]
[GROUP BY ...];

示例：创建员工基本信息视图

CREATE VIEW employee_info AS
SELECT
e.id,
e.name,
e.email,
d.department_name,
e.salary
FROM employees e
JOIN departments d ON e.dept_id = d.id;

---

✅ 2. 查询视图（和查表一样）

SELECT * FROM employee_info WHERE salary >
8000;

→ PostgreSQL 会自动将此查询“展开”为原始 SELECT 语句执行。

---

✅ 3. 可更新视图（Updatable View）

如果视图满足以下条件，可直接进行 INSERT/UPDATE/DELETE：

• 基于单表
• 不包含 DISTINCT, GROUP BY, HAVING, 聚合函数, 窗口函数, 子查询等
• SELECT 列表包含主键或唯一键

示例：可更新视图

CREATE VIEW active_employees AS
SELECT id, name, email, salary
FROM employees
WHERE is_active = true;
-- 可直接更新
UPDATE active_employees SET salary = 9000 WHERE id = 1;
-- 实际更新的是 employees 表

---

✅ 4. 使用 INSTEAD OF 触发器实现复杂视图更新

对于不可自动更新的视图，可通过触发器自定义行为：

CREATE VIEW employee_summary AS
SELECT
d.department_name,
COUNT(*) as employee_count,
AVG(e.salary) as avg_salary
FROM employees e
JOIN departments d ON e.dept_id = d.id
GROUP BY d.department_name;
-- 创建触发器函数
CREATE OR REPLACE FUNCTION update_employee_summary()
RETURNS TRIGGER AS $$
BEGIN
RAISE EXCEPTION '不能直接更新汇总视图';
END;
$$ LANGUAGE plpgsql;
-- 创建 INSTEAD OF 触发器
CREATE TRIGGER tr_employee_summary_update
INSTEAD OF UPDATE OR INSERT OR DELETE ON employee_summary
FOR EACH ROW EXECUTE FUNCTION update_employee_summary();

---

✅ 5. 删除视图

DROP VIEW IF EXISTS view_name;
-- 示例：
DROP VIEW IF EXISTS employee_info;

---

✅ 6. 查看视图定义

-- psql 中
\d+ view_name
-- 或 SQL 查询
SELECT definition FROM pg_views WHERE viewname = 'view_name';
-- 或使用系统函数
SELECT pg_get_viewdef('view_name');

---

二、物化视图（Materialized View）详解

✅ PostgreSQL 9.3+ 支持物化视图

---

✅ 1. 创建物化视图

CREATE MATERIALIZED VIEW mv_name AS
SELECT ...
[WITH DATA];
-- 默认包含数据，WITH NO DATA 可选（创建空结构）

示例：创建销售汇总物化视图

CREATE MATERIALIZED VIEW sales_summary AS
SELECT
product_id,
product_name,
SUM(quantity) as total_sold,
SUM(amount) as total_revenue
FROM sales
JOIN products USING (product_id)
GROUP BY product_id, product_name
ORDER BY total_revenue DESC;

→ 创建时即执行查询并存储结果。

---

✅ 2. 查询物化视图

SELECT * FROM sales_summary WHERE total_revenue >
10000;

→ 直接读取缓存数据，速度极快！

---

✅ 3. 刷新物化视图

数据不会自动更新，必须手动刷新：

-- 完全刷新（锁表，阻塞查询）
REFRESH MATERIALIZED VIEW sales_summary;
-- 并发刷新（PostgreSQL 9.4+，不阻塞查询，但需有唯一索引）
REFRESH MATERIALIZED VIEW CONCURRENTLY sales_summary;

⚠️ CONCURRENTLY 要求物化视图上至少有一个 UNIQUE 索引（不含 nullable 列）。

---

✅ 4. 为物化视图创建索引（强烈推荐！）

-- 为并发刷新创建唯一索引
CREATE UNIQUE INDEX idx_sales_summary_product ON sales_summary (product_id);
-- 为常用查询字段创建索引
CREATE INDEX idx_sales_summary_revenue ON sales_summary (total_revenue);

---

✅ 5. 自动刷新策略（通过定时任务）

物化视图本身不支持自动刷新，但可通过以下方式实现：

方法一：pg_cron 扩展（推荐）

-- 安装扩展（需先启用）
CREATE EXTENSION pg_cron;
-- 每天凌晨2点刷新
SELECT cron.schedule('0 2 * * *', $$REFRESH MATERIALIZED VIEW CONCURRENTLY sales_summary$$);

方法二：操作系统定时任务（crontab / Windows Task Scheduler）

# Linux crontab 示例
0 2 * * * psql -U user -d db -c "REFRESH MATERIALIZED VIEW CONCURRENTLY sales_summary"

---

✅ 6. 删除物化视图

DROP MATERIALIZED VIEW IF EXISTS mv_name;
-- 示例：
DROP MATERIALIZED VIEW IF EXISTS sales_summary;

---

✅ 7. 查看物化视图

-- psql 中
\dM -- 列出所有物化视图
\dM+ mv_name
-- SQL 查询
SELECT matviewname, matviewowner FROM pg_matviews;

---

三、性能对比示例

假设有一个包含百万行销售记录的表：

-- 原始查询（每次执行都要聚合）
SELECT product_id, SUM(amount) FROM sales GROUP BY product_id;
-- 耗时 2.5 秒
-- 创建物化视图后
CREATE MATERIALIZED VIEW mv_sales_agg AS
SELECT product_id, SUM(amount) as total FROM sales GROUP BY product_id;
-- 查询物化视图
SELECT * FROM mv_sales_agg;
-- 耗时 0.01 秒！
-- 刷新（每天一次）
REFRESH MATERIALIZED VIEW CONCURRENTLY mv_sales_agg;
-- 耗时 2.6 秒（后台执行）

→ 查询性能提升 250 倍！

---

四、使用场景推荐

✅ 何时使用视图（View）？

• 简化复杂 JOIN 查询
• 限制用户访问敏感列（列级权限控制）
• 提供统一接口，屏蔽底层表结构变化
• 逻辑数据分组（如“活跃用户”、“VIP客户”）

✅ 何时使用物化视图（Materialized View）？

• 报表系统（每日/每周汇总）
• 大数据聚合查询（COUNT, SUM, AVG 等）
• 数据仓库/BI 场景
• 高频查询 + 低频更新的数据
• 需要索引加速的复杂查询结果

---

五、高级技巧

---

✅ 1. 增量刷新物化视图（手动实现）

通过记录上次刷新时间，只处理新增数据：

-- 添加 last_refreshed 字段
ALTER TABLE sales ADD COLUMN last_updated TIMESTAMP DEFAULT NOW();
-- 创建增量物化视图（伪代码思路）
CREATE MATERIALIZED VIEW sales_daily_agg AS
SELECT
DATE(sale_date) as sale_day,
SUM(amount) as daily_total
FROM sales
WHERE sale_date >= CURRENT_DATE - INTERVAL '30 days'
GROUP BY DATE(sale_date);
-- 刷新时只处理最近一天数据（需自定义函数）

---

✅ 2. 结合分区表使用

物化视图可基于分区表创建，刷新时只处理最新分区，效率更高。

---

✅ 3. 监控物化视图状态

-- 查看物化视图是否已加载数据
SELECT matviewname, ispopulated FROM pg_matviews;
-- 查看大小
SELECT schemaname, matviewname, pg_size_pretty(pg_total_relation_size(C.oid)) as size
FROM pg_class C
JOIN pg_matviews M ON C.relname = M.matviewname;

---

实践任务

请完成以下操作：

1. 创建一个视图 user_orders，显示用户姓名、邮箱、订单总数、总消费金额
2. 查询该视图，筛选消费超过 1000 的用户
3. 创建一个物化视图 monthly_sales_report，按月汇总销售额
4. 为物化视图创建唯一索引并执行并发刷新
5. 比较直接查询聚合 SQL 与查询物化视图的性能差异（使用 \timing）

---

✅ 总结对比表

特性	视图（View）	物化视图（Materialized View）
数据存储	❌ 虚拟	✅ 物理存储
查询速度	⚠️ 取决于原查询复杂度	✅ 极快（查缓存）
数据实时性	✅ 实时	❌ 需刷新
占用空间	❌ 几乎无	✅ 占用（等于结果集）
可建索引	❌	✅
可更新	✅（简单视图）	❌
适用场景	逻辑抽象、权限控制	性能优化、报表、缓存