PostgreSQL 中的 VIEW 视图和 MATERIALIZED VIEW

sql CREATE VIEW dept_employee_count AS SELECT d.department_name, COUNT(e.employee_id) AS total_employees FROM departments d LEFT JOIN employees e ON d.department_id = e.department_id GROUP BY d.department_name;

之后就可以像查普通表一样使用：

```sql
SELECT * FROM dept_employee_count;

修改视图

如果需要修改视图定义，可以使用 CREATE OR REPLACE VIEW：

CREATE OR REPLACE VIEW dept_employee_count AS
SELECT
    d.department_name,
    d.location,
    COUNT(e.employee_id) AS total_employees
FROM departments d
LEFT JOIN employees e ON d.department_id = e.department_id
GROUP BY d.department_name, d.location;

删除视图

DROP VIEW IF EXISTS dept_employee_count;

可更新视图

简单视图（基于单表，没有聚合、DISTINCT、GROUP BY 等）可以被用来执行 INSERT、UPDATE、DELETE 操作，PostgreSQL 会将该操作转换为对基表的操作。例如：

CREATE VIEW active_employees AS
SELECT employee_id, first_name, last_name, salary
FROM employees
WHERE is_active = true;

-- 更新视图数据，实际更新 employees 表
UPDATE active_employees
SET salary = salary * 1.1
WHERE employee_id = 101;

对于复杂视图，可以使用 INSTEAD OF 触发器来实现更新，这超出了入门范围，但你需要知道这种可能性。

MATERIALIZED VIEW 深入

创建物化视图

语法与普通视图类似，只是关键字不同：

CREATE MATERIALIZED VIEW sales_summary AS
SELECT
    product_id,
    DATE_TRUNC('month', sale_date) AS month,
    SUM(amount) AS total_sales,
    COUNT(*) AS sale_count
FROM sales
GROUP BY product_id, DATE_TRUNC('month', sale_date);

此时，sales_summary 表存储了查询结果的物理副本。直接查询速度非常快。

刷新物化视图

数据不会自动变化。为了同步基表的最新数据，需要执行刷新命令：

REFRESH MATERIALIZED VIEW sales_summary;

默认情况下，刷新会锁定物化视图，期间无法查询。如果要允许并发查询，可以添加 CONCURRENTLY 选项（需要物化视图有唯一索引）：

-- 首先创建唯一索引
CREATE UNIQUE INDEX idx_sales_summary ON sales_summary (product_id, month);

-- 并发刷新，不阻塞读操作
REFRESH MATERIALIZED VIEW CONCURRENTLY sales_summary;

删除物化视图

DROP MATERIALIZED VIEW IF EXISTS sales_summary;

查看物化视图状态

你可以检查所有物化视图及其最新刷新时间：

SELECT
    schemaname,
    matviewname,
    ispopulated,
    last_refresh
FROM pg_matviews;

何时选择 VIEW 还是 MATERIALIZED VIEW

实践技巧与注意事项

对 VIEW 的建议

• 避免嵌套过深：视图多层嵌套会导致查询计划极难优化，性能下降明显。
• 可更新视图要谨慎：对复杂视图的自动更新可能失败，建议只用在简单场景。
• 善用物化视图替代视图：当发现某个视图查询频繁且慢时，考虑改为物化视图，并设计合理的刷新策略。

对 MATERIALIZED VIEW 的建议

• 建立合适的索引：在刷新后分析表并创建索引，可以大幅提升查询性能。
• 规划刷新策略：根据业务容忍度，安排在业务低峰期（如每天深夜）刷新，或使用触发器事件驱动刷新（需要借助外部工具或 cron 任务）。
• 并发刷新的限制：CONCURRENTLY 需要至少一个唯一索引，刷新速度稍慢，但可避免锁表。适合 7×24 服务。
• 存储成本：物化视图会占用额外磁盘空间，注意监控。

使用 EXPLAIN 检查性能

无论是视图还是物化视图，分析查询计划都是优化利器：

EXPLAIN SELECT * FROM dept_employee_count;
EXPLAIN SELECT * FROM sales_summary;

物化视图的查询计划通常显示为对物理表的简单扫描，而视图会展示完整的执行计划。

动手任务：创建你的第一个物化视图

1. 在测试库中创建两张表：

CREATE TABLE products (
    id serial PRIMARY KEY,
    name text,
    price numeric
);

CREATE TABLE orders (
    id serial PRIMARY KEY,
    product_id int REFERENCES products(id),
    quantity int,
    order_date date
);