PostgreSQL 教程: 物化视图索引

二月 17, 2025

摘要：在本教程中，您将学习如何在 PostgreSQL 中为物化视图建立索引。

介绍

物化视图如今已在 PostgreSQL 中被广泛使用。很多人都在通过外部数据包装器、单独的分析系统（如数据仓库），建立连接的系统，并使用 PostgreSQL 查询合并来自不同位置的数据。物化视图允许您为本地和远程的数据，预编译一个查询或部分表。物化视图是静态的，必须要刷新。

要高效地使用物化视图，索引是非常重要的一件事情。

通常，向 PostgreSQL 添加索引对于操作和查询的性能至关重要。通常还建议为物化视图添加索引，有几点原因。

物化视图通常用于较大的数据集，其中的查询或大表连接可受益于预编译，而索引可以带来额外的性能提升。
即使基础表数据带有索引，这些索引也不会在物化视图中被使用到。物化视图单独地保存在磁盘上，因此它需要一个独立的索引。
在查询物化视图时，PostgreSQL 会将其视为常规表。物化视图并没有特殊的查询规划器。如果其中的数据类型，跟表一样可以受益于索引，则物化视图将会从索引受益。

视图与物化视图

如果您最近没有接触过 PostgreSQL 的视图和物化视图，那么让我们快速复习一下。

一个视图是一个保存的查询。它没有存储在磁盘上。每当查询时，它都会从底层的表动态获取数据。由于视图没有自己的存储，因此视图不能有索引。
物化视图不会从底层的表动态获取数据 - 它们存储在磁盘上 - 并且必须要显式刷新以更新内容。这使它们非常适合涉及复杂查询或频繁访问相对静态数据集的场景。由于它们可以存储在磁盘上，因此可以对物化视图建立索引。

构建带索引的物化视图

在一个演示的电子商务站点中有三个表：products、orders、product_orders。我们想在我们的演示网站上展示该产品的购买频率。这对产品营销很有帮助，但我们不需要在每次显示产品时都从数据库中重新计算此值。因此，物化视图中的静态信息非常适合该用例。预先连接表，并准备好数据，会让对 SKU 的查询变得非常容易。

下面是一个物化视图的示例，其中按 SKU 显示最近的产品销售。

CREATE MATERIALIZED VIEW recent_product_sales AS
SELECT
    p.sku,
    SUM(po.qty) AS total_quantity
FROM
    products p
    JOIN product_orders po ON p.sku = po.sku
    JOIN orders o ON po.order_id = o.order_id
WHERE
    o.status = 'Shipped'
GROUP BY
    p.sku
ORDER BY
    2 DESC;

我们可能会按 sku 查找这些数据，因此我们可以添加一个简单的 B 树索引，像调用一个表一样调用物化视图。

CREATE INDEX sku_index ON recent_product_sales (sku);

为物化视图创建索引的工作方式，与处理表完全相同。PostgreSQL 在物化视图上支持所有主要的索引类型：B 树、哈希、GiST、GIN、BRIN 和其他索引类型。如果您需要一个关于索引的基本介绍，请参阅有关 PostgreSQL 索引类型的教程。

刷新物化视图和索引

物化视图是静态的，因此要添加新数据，我们必须刷新它。PostgreSQL 可以通过两种方式刷新物化视图。常规刷新和并发刷新。

非并发（锁定）刷新

这种刷新会完全替换物化视图的内容。在此之前构建的索引将保留，PostgreSQL 会使用刷新的数据重新创建索引。

在这种刷新的过程中，PostgreSQL 会获得物化视图的排他锁，从而阻止任何读取或写入。这是最快的方式，但它通常不适用于具有实时读取需要的生产系统。

REFRESH MATERIALIZED VIEW recent_product_sales;

除了构建物化视图之外，PostgreSQL 还必须从头开始重建索引。根据数据量大小，这可能是一个时间相当长的操作。

并发（无锁定）刷新

这种刷新在更新物化视图时，不会锁定表，从而允许您在刷新时读取当前的数据。这也会使用到 PostgreSQL 的 REINDEX CONCURRENTLY，如果您熟悉这个特性的话。PostgreSQL 会在刷新数据时重新索引所有的数据。由于采用了增量的方法，这通常比常规刷新要慢，但它允许在此过程中读取数据，这使其成为生产数据库的有利选择。

并发刷新需要物化视图上有唯一索引才能运行。唯一索引可确保物化视图中的每一行都可以唯一标识。我们之前添加的 B 树索引尚未显式声明唯一性，因此我们可以添加一个新的唯一索引，并删除旧的索引。

CREATE UNIQUE INDEX unique_idx_recent_product_sales ON recent_product_sales(sku);

DROP INDEX sku_index ON recent_product_sales(sku);

现在我们可以执行并发刷新了：

REFRESH MATERIALIZED VIEW CONCURRENTLY recent_product_sales;

生成带有非唯一值的列的物化视图不能使用唯一索引，也不能使用并发的刷新选项。在这种情况下，您必须通过常规刷新来处理它。

总结

索引总会有其他的注意事项。需要对每个项目进行独特的规划，以便根据查询模式、刷新频率和物化视图的大小，来审视索引的使用情况，以最大限度地提高效率。索引存储在磁盘上，因此它们需要有自己额外的存储空间。它们可能会对性能产生影响，尤其是对于大型或复杂的视图和索引。您可以使用 \timing 或日志，监控索引刷新的时间。

摘要说明：

即使基础表数据有索引，也必须在物化视图上重新创建它们。
物化视图通常可受益于索引。
物化视图是静态的，需要刷新。常规刷新会锁定视图，使其无法读取，而并发刷新则不会。
当对物化视图使用 REFRESH CONCURRENTLY 选项时，数据必须带有 UNIQUE 索引。

了解更多

PostgreSQL 教程

介绍