PostgreSQL实战指南：多领域数据库应用与实践

第一部分：关系型数据库深度探索

第6章：视图与物化视图的应用

在复杂的数据库系统中，直接查询多张表、进行复杂的联接和聚合操作是常态。然而，这些复杂的查询语句往往冗长难懂，维护起来也十分困难。此外，为了安全和数据封装的考虑，我们可能不想直接暴露底层表结构给所有用户。PostgreSQL通过**视图（Views）和物化视图（Materialized Views）**提供了优雅的解决方案。

本章将详细介绍视图和物化视图的概念、它们的创建与管理、各自的优势与局限性，并结合实际应用场景，展示如何利用它们来简化数据访问、优化查询性能、以及实施精细化的权限控制。

6.1 视图（Views）基础

视图是一个虚拟的表，它的内容由查询定义。视图本身不存储数据，它只是一个存储在数据库中的查询语句。每当你查询视图时，数据库都会执行这个定义视图的查询，并返回结果。因此，视图可以被看作是一个“窗口”，透过它你可以看到底层数据的一个特定子集或组合。

视图的优势：

简化复杂查询：将复杂的联接、聚合或子查询封装在视图中，用户只需查询视图即可获取所需数据，无需关心底层复杂逻辑。
数据安全与权限控制：可以只授予用户对特定视图的访问权限，而不授予对底层表的直接访问权限，从而限制用户只能看到部分数据或某些列，有效保护敏感信息。
数据抽象与独立性：当底层表结构发生变化时（例如，添加或移除列），如果视图的设计得当，可以修改视图定义以适应变化，而应用程序代码无需修改。
一致性：为不同的应用程序提供数据的一致性视图。

视图的局限性：

性能开销：每次查询视图时，都会执行其底层查询，如果底层查询很复杂或数据量很大，可能会有性能开销。视图不会预先计算和存储数据。
更新限制：并非所有视图都是可更新的。如果视图涉及复杂的联接、聚合函数或非主键列，通常是不可更新的。对于可更新的视图，其更新操作会转换为对底层表的更新。

创建视图的语法：

1
2
3
4
5
CREATE [OR REPLACE] VIEW view_name AS
SELECT column1, column2, ...
FROM table1
JOIN table2 ON table1.id = table2.id
WHERE condition;

OR REPLACE：如果视图已存在，则替换其定义。

实战举例：简化电子商务订单查询

假设我们的电子商务系统需要频繁查询每个用户的订单总览，包括用户名、订单ID、订单日期和总金额。

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
-- 1. 创建一个视图来简化用户订单查询
CREATE OR REPLACE VIEW user_orders_summary AS
SELECT
    u.user_id,
    u.username,
    o.order_id,
    o.order_date,
    o.total_amount,
    o.status
FROM
    users u
JOIN
    orders o ON u.user_id = o.user_id;

-- 现在，用户可以直接查询这个视图，而无需编写复杂的 JOIN 语句
SELECT * FROM user_orders_summary WHERE username = 'alice';

SELECT order_id, total_amount FROM user_orders_summary WHERE status = 'processing' ORDER BY order_date DESC;

-- 2. 创建一个视图来显示每个商品的销售情况 (聚合数据)
CREATE OR REPLACE VIEW product_sales_overview AS
SELECT
    p.product_id,
    p.product_name,
    SUM(oi.quantity) AS total_quantity_sold,
    SUM(oi.quantity * oi.unit_price) AS total_revenue
FROM
    products p
JOIN
    order_items oi ON p.product_id = oi.product_id
GROUP BY
    p.product_id, p.product_name;

-- 查询商品销售概览
SELECT product_name, total_revenue FROM product_sales_overview ORDER BY total_revenue DESC;

-- 3. 演示视图更新 (简单视图可更新)
-- 创建一个只包含部分列的简单视图
CREATE OR REPLACE VIEW active_users AS
SELECT user_id, username, email FROM users WHERE registration_date > '2023-01-01';

-- 可以通过视图更新底层数据 (如果视图满足可更新条件)
-- 注意：并非所有视图都可更新，具体规则请查阅 PostgreSQL 官方文档
INSERT INTO active_users (username, email) VALUES ('david', '[email protected]'); -- 此时 registration_date 会默认 NOW()
UPDATE active_users SET email = '[email protected]' WHERE username = 'david';
DELETE FROM active_users WHERE username = 'david';

6.2 物化视图（Materialized Views）

与普通视图不同，物化视图将查询结果预先计算并存储在磁盘上。这意味着当你查询物化视图时，你直接读取的是存储好的数据，而不是重新执行底层查询。这极大地提高了查询性能，特别是对于复杂的、数据不经常变化的查询。

物化视图的优势：

显著提升查询性能：由于数据已经预计算并存储，查询物化视图的速度远快于查询普通视图或直接执行复杂底层查询。
适用于报表和数据分析：非常适合用于生成周期性报表、聚合数据或作为数据仓库中的汇总表。
减轻源系统负载：避免了每次查询都对源表进行昂贵的计算，从而减轻了源数据库的负载。

物化视图的局限性：

数据时效性：物化视图中的数据不会随源表的变化而自动更新。它们是数据的一个快照。
需要手动或调度刷新：为了获取最新数据，物化视图需要定期刷新（REFRESH）。刷新操作会重新执行底层查询并更新存储的数据，这可能是一个耗时的过程。
存储空间：物化视图需要额外的存储空间来存储其数据。

创建物化视图的语法：

1
2
3
4
5
CREATE MATERIALIZED VIEW mv_name AS
SELECT column1, column2, ...
FROM table1
JOIN table2 ON table1.id = table2.id
WHERE condition;

刷新物化视图的语法：

1
2
REFRESH MATERIALIZED VIEW mv_name; -- 会锁定视图，直到刷新完成
REFRESH MATERIALIZED VIEW CONCURRENTLY mv_name; -- 更高级的刷新方式，允许并发读写，但有额外要求 (需要唯一索引)

实战举例：优化商品销售月度报表

假设我们需要生成每月热门商品的销售总额报表。这个查询通常涉及大量数据聚合，且数据每天可能只更新少量次。

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
-- 1. 创建一个物化视图来存储每个商品的累计销售总额
-- 假设我们已经有 order_items 和 products 表
CREATE MATERIALIZED VIEW mv_product_total_sales AS
SELECT
    p.product_id,
    p.product_name,
    SUM(oi.quantity * oi.unit_price) AS total_sales_amount,
    COUNT(DISTINCT oi.order_id) AS total_orders
FROM
    products p
JOIN
    order_items oi ON p.product_id = oi.product_id
GROUP BY
    p.product_id, p.product_name
ORDER BY
    total_sales_amount DESC;

-- 首次创建时，PostgreSQL会立即填充数据
SELECT * FROM mv_product_total_sales;

-- 2. 模拟底层数据变化
-- 假设一个新的订单进来，购买了 Laptop Pro
INSERT INTO orders (user_id, total_amount, status) VALUES (1, 1200.00, 'processing');
INSERT INTO order_items (order_id, product_id, quantity, unit_price) VALUES (
    (SELECT order_id FROM orders WHERE user_id = 1 ORDER BY order_date DESC LIMIT 1),
    1, 1, 1200.00
);

-- 此时查询物化视图，数据不会改变，因为它是旧的快照
SELECT * FROM mv_product_total_sales WHERE product_name = 'Laptop Pro'; -- 仍然是旧的总额

-- 3. 刷新物化视图以获取最新数据
-- 注意：REFRESH MATERIALIZED VIEW 会在刷新期间锁定物化视图，阻止其他查询。
REFRESH MATERIALIZED VIEW mv_product_total_sales;

-- 刷新后，再次查询会看到更新后的数据
SELECT * FROM mv_product_total_sales WHERE product_name = 'Laptop Pro'; -- 此时会看到更新后的总额

-- 4. 使用 CONCURRENTLY 刷新 (推荐用于生产环境，需要唯一索引)
-- 首先，为物化视图添加一个唯一索引，这是 CONCURRENTLY 的前提条件
CREATE UNIQUE INDEX mv_product_total_sales_pk ON mv_product_total_sales (product_id);

-- 然后可以无锁刷新 (允许在刷新期间对物化视图进行读操作)
REFRESH MATERIALIZED VIEW CONCURRENTLY mv_product_total_sales;

-- 5. 定期调度刷新：
-- 在生产环境中，你会使用 cron job 或其他调度工具（如 pg_cron 扩展）来定期执行 REFRESH 命令。
-- 例如，每天凌晨3点刷新一次：
-- SELECT cron.schedule('0 3 * * *', 'REFRESH MATERIALIZED VIEW CONCURRENTLY mv_product_total_sales;');

6.3 视图与物化视图的选择

特性	普通视图（View）	物化视图（Materialized View）
数据存储	不存储数据，只是存储查询定义	存储查询结果数据
数据实时性	实时，每次查询都执行底层查询	非实时，是数据的快照，需要手动或调度刷新
查询性能	依赖底层查询性能，可能较慢	显著提升，直接读取预计算结果
存储空间	几乎不占用额外存储空间	占用额外存储空间
更新数据	简单视图可更新，复杂视图不可更新	不可直接更新，只能通过刷新操作更新
适用场景	简化复杂查询、数据抽象、权限控制、数据不经常被查询或需要实时数据	报表、数据分析、聚合计算、数据仓库、查询性能敏感的场景

选择建议：

如果查询结果需要实时性，并且底层查询复杂度适中，或者主要用于数据抽象和权限控制，选择普通视图。
如果查询结果不要求实时性（允许数据有一定的延迟），但对查询性能有极高要求，且底层数据变化不频繁（或可以接受周期性刷新），选择物化视图。

6.4 总结

本章我们深入学习了PostgreSQL中的视图和物化视图。我们理解了普通视图作为虚拟表的概念，它如何通过封装复杂查询来简化数据访问、提供数据抽象和增强安全性。同时，我们也掌握了物化视图作为数据快照的特性，它如何通过预计算和存储数据来显著提升查询性能，尤其适用于报表和分析场景。

通过本章的学习，你现在应该能够：

区分普通视图和物化视图的特点、优势与局限性。
熟练创建和管理普通视图，用于简化查询和实现权限控制。
创建和刷新物化视图，以优化复杂查询的性能。
根据业务需求和数据特性，合理选择使用视图或物化视图。

在下一章中，我们将进一步探索PostgreSQL的编程能力，深入学习存储过程、函数与触发器，这些高级特性将帮助你实现更复杂的业务逻辑、自动化数据库操作，并增强数据库的智能性。

文章目录