✅ 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;实践任务
请完成以下操作：
- 创建一个视图
user_orders，显示用户姓名、邮箱、订单总数、总消费金额 - 查询该视图，筛选消费超过 1000 的用户
- 创建一个物化视图
monthly_sales_report，按月汇总销售额 - 为物化视图创建唯一索引并执行并发刷新
- 比较直接查询聚合 SQL 与查询物化视图的性能差异（使用
\timing）
✅ 总结对比表
| 特性 | 视图（View） | 物化视图（Materialized View） |
|---|---|---|
| 数据存储 | ❌ 虚拟 | ✅ 物理存储 |
| 查询速度 | ⚠️ 取决于原查询复杂度 | ✅ 极快（查缓存） |
| 数据实时性 | ✅ 实时 | ❌ 需刷新 |
| 占用空间 | ❌ 几乎无 | ✅ 占用（等于结果集） |
| 可建索引 | ❌ | ✅ |
| 可更新 | ✅（简单视图） | ❌ |
| 适用场景 | 逻辑抽象、权限控制 | 性能优化、报表、缓存 |