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

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

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

🧭 一、核心概念对比

💡 简单理解：

• 视图 = 保存的 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）

✅ 总结对比表