二月 14, 2025
摘要：在本教程中，您将学习如何利用视图的内联能力。
目录
介绍
数据库视图是功能强大的工具，在构建数据库驱动的应用程序时，它们通常并没有被恰当地使用到。它们可以通过多种方式，使我们的数据库工作得更轻松：
- 它们允许我们重用通用的查询模式，而不是重复地编写它们
- 它们为我们提供了一个定义业务规则并在任何地方使用它们的地方
- 它们帮助我们编写更清晰、更有条理的查询
让我们通过一个实际的示例，来了解其工作原理。假设我们想要处理活跃的用户 - 在过去 7 天内使用过该应用程序的用户。我们可以定义一个视图，而不是在每个查询中都编写这个条件：
CREATE VIEW active_users AS
SELECT
*
FROM users;
WHERE
last_login > current_date - INTERVAL '7 days';
现在，每当我们需要活跃用户时，我们都可以轻松地使用该视图。例如，如果我们想要查找来自德国的活跃用户：
SELECT
user_id
FROM active_users
WHERE
country = 'Germany';
虽然这个简单的示例只是展示了，视图可以通过组织和重用通用的逻辑，来简化开发人员的工作，但故事远不止于此。在本文中，我们将探讨更有趣的事情：PostgreSQL 如何通过称为 “内联” 的过程来优化这些视图 - 使它们不仅方便，而且快速。
什么是视图内联
当您使用视图时，它就像 SQL 查询中的一块积木。以前面的示例为例，PostgreSQL 会将视图替换为其底层的子查询，来有效地转换查询。
SELECT
user_id
FROM (
SELECT
*
FROM users
WHERE
last_login > current_date - INTERVAL '7 days'
) active users
WHERE
country = 'Germany';
当我们使用active_users视图编写查询时，PostgreSQL 查询规划器会将其视为进一步优化的机会。它不是将子查询视为单独的步骤（以检索大量的用户集合），而是有效地转换查询，并将视图内联到查询本身。在幕后，PostgreSQL 将会执行类似下面的查询：
SELECT
user_id
FROM users
WHERE
last_login > current_date - INTERVAL '7 days'
AND country = 'Germany';
内联过程允许 PostgreSQL 查询规划器将整个查询作为一个整体进行优化。这使它能够选择最佳的索引、可能的连接策略和其他的方面，而不必单独地执行各个部分，再去过滤不会在其他地方用到的数据。这正是视图的用途 - 我们可以获得两全其美的优势 - 为开发人员提供干净、模块化的代码，并为数据库实现最佳的性能。
视图内联演示
要预览视图内联的最简单方法是，运行 EXPLAIN：
Seq Scan on users (cost=0.00..19.20 rows=1 width=4)
Filter: ((country = 'Germany'::text) AND (last_login > (CURRENT_DATE - '7 days'::interval)))
这里的 Filter 部分显示了，查询规划器如何将来自查询和视图的筛选条件合并在一起。
这特别适用于涉及连接、聚合和筛选条件的复杂查询。PostgreSQL 可以将整个查询链作为一个整体进行优化，而不是单独地执行各个部分。
CREATE VIEW order_analytics AS
SELECT
o.customer_id,
c.country,
DATE_TRUNC('month', o.created_at) as month,
COUNT(*) as orders,
SUM(o.total_amount) as revenue
FROM orders o
JOIN customers c ON c.id = o.customer_id
GROUP BY 1, 2, 3;
在查询中使用该视图时：
SELECT customer_id, revenue
FROM order_analytics
WHERE country = 'Germany'
AND month >= '2024-01-01'
AND revenue > 1000;
您将看到 PostgreSQL 如何有效地将筛选条件内联到它们所属的地方。
HashAggregate (cost=205.97..207.97 rows=200 width=16)
Group Key: o.customer_id, c.country, (date_trunc('month'::text, o.created_at))
Filter: (sum(o.total_amount) > 1000)
-> Hash Join (cost=16.12..185.25 rows=1235 width=20)
Hash Cond: (o.customer_id = c.id)
-> Seq Scan on orders o
Filter: (created_at >= '2024-01-01'::date)
-> Hash
-> Seq Scan on customers c
Filter: (country = 'Germany'::text)
这在查询的复杂性增加的情况下尤其有效。特别在涉及到连接和进一步的过滤时。PostgreSQL 可以将整个查询链作为一个整体进行优化，而不是单独地执行各个部分。
CREATE VIEW completed_orders AS
SELECT
o.id,
o.customer_id,
o.total_amount,
o.created_at,
o.status,
p.name as product_name,
c.email,
c.country
FROM orders o
JOIN customers c ON c.id = o.customer_id
JOIN order_items oi ON oi.order_id = o.id
JOIN products p ON p.id = oi.product_id
WHERE
o.status = 'completed';
SELECT
*
FROM completed_orders
WHERE
country = 'Germany';
CREATE INDEX idx_orders_customer_id ON orders(customer_id);
CREATE INDEX idx_order_items_order_id ON order_items(order_id);
CREATE INDEX idx_order_items_product_id ON order_items(product_id);
如查询计划所示：
Nested Loop (cost=0.30..19.70 rows=1 width=176)
-> Nested Loop (cost=0.15..13.52 rows=1 width=148)
Join Filter: (o.id = oi.order_id)
-> Nested Loop (cost=0.15..12.43 rows=1 width=144)
-> Seq Scan on orders o (cost=0.00..1.05 rows=1 width=80)
Filter: (status = 'completed'::text)
-> Index Scan using customers_pkey on customers c (cost=0.15..8.17 rows=1 width=68)
Index Cond: (id = o.customer_id)
Filter: (country = 'Germany'::text)
-> Seq Scan on order_items oi (cost=0.00..1.04 rows=4 width=8)
-> Index Scan using products_pkey on products p (cost=0.15..6.17 rows=1 width=36)
Index Cond: (id = oi.product_id)
规划器屏障
虽然 PostgreSQL 可以很好地内联视图，但某些操作会产生阻止优化的 “规划器屏障”。对于视图，会阻止其内联的一些条件有：
- DISTINCT ON 操作
- 窗口函数（OVER 子句）
- 集合操作（UNION/INTERSECT/EXCEPT）
- 更复杂的聚合
- 物化的公共表表达式（带有 MATERIALIZED 提示，或者由查询规划器决定的物化）
- 使用 VOLATILE 函数
- 在某些情况下的复杂子查询
当规划器无法内联视图时，可能会导致不必要的性能和资源影响。即，每个子查询可能会单独地物化结果，会出现已经检索到的行被查询的另一部分丢弃，这是最常见的情况之一。
使用 EXPLAIN 时，以下选项之一可以证明该情况：
Subquery scan on...节点- 物化节点
- 在主流程之前分离聚合和排序的步骤
如上所述，窗口函数正是这样一个规划器屏障。
CREATE VIEW order_insights AS
SELECT
o.customer_id,
o.total_amount,
o.created_at,
ROW_NUMBER() OVER (PARTITION BY o.customer_id ORDER BY o.created_at) as order_sequence,
SUM(o.total_amount) OVER (PARTITION BY o.customer_id ORDER BY o.created_at) as running_total
FROM orders o;
EXPLAIN
SELECT * FROM order_insights
WHERE customer_id = 1
AND total_amount > 500;
这让我们很容易发现Subquery scan节点。
Subquery Scan on order_insights (cost=1.06..1.10 rows=1 width=84)
Filter: (order_insights.total_amount > '500'::numeric)
-> WindowAgg (cost=1.06..1.08 rows=1 width=84)
-> Sort (cost=1.06..1.06 rows=1 width=44)
Sort Key: o.created_at
-> Seq Scan on orders o (cost=0.00..1.05 rows=1 width=44)
Filter: (customer_id = 1)
运行时物化
让我们澄清一个可能会混淆的点 - 我们不是在这里讨论CREATE MATERIALIZED VIEW。这是关于 PostgreSQL 在查询执行期间的运行时决策：将视图结果缓存到内存中。当查询需要多次引用视图结果或防止重复进行昂贵的计算时，规划器可能会使用显式的物化。查询规划器会通过 Materialize 节点显示出这一点：
CREATE VIEW customer_summary AS
SELECT
customer_id,
COUNT(*) as orders,
SUM(total_amount) as total_spent
FROM orders
GROUP BY 1;
EXPLAIN SELECT
a.customer_id,
a.total_spent,
b.total_spent as other_customer_spent
FROM customer_summary a
JOIN customer_summary b ON b.total_spent > a.total_spent;
这样就给出了一个完美的物化示例。
Nested Loop (cost=46.00..200.75 rows=3333 width=68)
Join Filter: (b.total_spent > (sum(orders.total_amount)))
-> HashAggregate (cost=23.00..24.25 rows=100 width=44)
Group Key: orders.customer_id
-> Seq Scan on orders (cost=0.00..18.00 rows=1000 width=15)
-> Materialize (cost=23.00..25.75 rows=100 width=32)
-> Subquery Scan on b (cost=23.00..25.25 rows=100 width=32)
-> HashAggregate (cost=23.00..24.25 rows=100 width=44)
Group Key: orders_1.customer_id
-> Seq Scan on orders orders_1 (cost=0.00..18.00 rows=1000 width=15)
除了物化 - 物化视图（CREATE MATERIALIZED VIEW）出于实际的目的 “只是另一种表”，它们提出了一个最终的规划器屏障。PostgreSQL 必须直接扫描物化的数据，以牺牲查询的灵活性来换取性能。
编写内联友好视图的提示
没有一种唯一 “正确” 的方法来编写视图。再者，视图应该使您的数据库更易于使用，而不是更难使用。在设计视图时，请关注单一的职责 - 每个视图都应该处理业务逻辑的一个方面，而不是试图一次优化所有方面。简单的视图更有可能被 PostgreSQL 的查询规划器内联。
有几种常见的模式，会阻止内联：
- 完全避免窗口函数、DISTINCT 和集合操作
- 如果可能，将复杂逻辑拆分为多个视图
- 最小化子查询的使用
- 考虑用物化视图进行聚合
视图依赖关系是一个关键的考虑因素。即使是微小的结构变更，也会级联地触发整个数据库的视图更改。不要创建深层的视图层次结构，而是将复杂的逻辑拆分为更小的可组合视图。对于许多应用程序所依赖的关键视图，请考虑版本控制（v1、v2），而不是修改现有的视图。
如有疑问，请先编写纯 SQL，然后将常见模式提取到视图中。这有助于避免过度设计，并保持数据库架构的可维护性。