PostgreSQL 物化视图

简介：本教程向您介绍 PostgreSQL 物化视图，它允许您物理存储查询结果并定期更新数据。

在PostgreSQL 视图教程 中，您已经了解到视图是表示底层表数据的虚拟表。简单的视图也可以更新。

PostgreSQL 将视图概念扩展到下一个级别，允许视图物理存储数据。这些视图称为物化视图。

物化视图缓存复杂且昂贵的查询结果，并允许您定期刷新此结果。

物化视图在许多需要快速数据访问的情况下很有用，因此它们通常用于数据仓库和商业智能应用程序。

创建物化视图

要创建物化视图，请使用 CREATE MATERIALIZED VIEW 如下语句：

CREATE MATERIALIZED VIEW view_name
AS
query
WITH [NO] DATA;

语法说明：

  • 首先，在 CREATE MATERIALIZED VIEW 关键字后指定物化视图名称 view_name。
  • 其次，在 AS 关键字之后添加从基础表中获取数据的查询。
  • 第三，如果要在创建时将数据加载到物化视图中，使用 WITH DATA 选项；否则，您使用 WITH NO DATA . 如果您使用 WITH NO DATA ，视图将被标记为不可读。这意味着在将数据加载到视图之前，您无法从视图查询数据。

刷新物化视图的数据

要将数据加载到物化视图中，请使用 REFRESH MATERIALIZED VIEW 如下所示的 语句：

REFRESH MATERIALIZED VIEW view_name;

当您为物化视图刷新数据时，PostgreSQL 会锁定整个表，因此您无法对其查询数据。为避免这种情况，您可以使用该 CONCURRENTLY 选项。

REFRESH MATERIALIZED VIEW CONCURRENTLY view_name;

使用 CONCURRENTLY 选项，PostgreSQL 创建物化视图的临时更新版本，比较两个版本，并仅执行INSERT和UPDATE差异。

您可以在更新物化视图时对其进行查询。使用 CONCURRENTLY 选项的一个要求是物化视图必须有一个 UNIQUE 索引。

请注意，该 CONCURRENTLY 选项仅在 PostgreSQL 9.4 中可用。

删除物化视图

删除物化视图非常简单，就像我们对表或视图所做的那样。这是使用以下语句完成的：

DROP MATERIALIZED VIEW view_name;

PostgreSQL 物化视图示例

以下语句创建一个名为 的物化视图 rental_by_category ：

CREATE MATERIALIZED VIEW rental_by_category
AS
 SELECT c.name AS category,
    sum(p.amount) AS total_sales
   FROM (((((payment p
     JOIN rental r ON ((p.rental_id = r.rental_id)))
     JOIN inventory i ON ((r.inventory_id = i.inventory_id)))
     JOIN film f ON ((i.film_id = f.film_id)))
     JOIN film_category fc ON ((f.film_id = fc.film_id)))
     JOIN category c ON ((fc.category_id = c.category_id)))
  GROUP BY c.name
  ORDER BY sum(p.amount) DESC
WITH NO DATA;

由于该 WITH NO DATA 选项，您无法从视图中查询数据。如果您尝试这样做，您将收到如下错误消息：

SELECT
    *
FROM
    rental_by_category;

[Err] ERROR: materialized view "rental_by_category" has not been populated
HINT: Use the REFRESH MATERIALIZED VIEW command.

PostgreSQL 有助于提示您要求将数据加载到视图中。让我们通过执行以下语句来实现：

REFRESH MATERIALIZED VIEW rental_by_category;

现在，如果您再次查询数据，您将得到预期的结果。

postgresql 物化视图示例

从现在开始，您可以 rental_by_category 使用该 REFRESH MATERIALIZED VIEW 语句刷新视图中的数据。

但是，要使用 CONCURRENTLY 选项刷新它，您需要先 UNIQUE 为视图创建索引。

CREATE UNIQUE INDEX rental_category ON rental_by_category (category);

让我们同时为 rental_by_category 视图刷新数据。

REFRESH MATERIALIZED VIEW CONCURRENTLY rental_by_category;

在本教程中，您学习了如何使用 PostgreSQL 物化视图，这对于需要快速数据检索的分析应用程序非常有用。