PostgreSQL 教程: 物化视图

九月 8, 2023

摘要:本教程向您介绍 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 创建一个物化视图的临时更新版本,比较两个版本,并仅对差异数据执行 INSERTUPDATE

您可以在更新物化视图时对其进行查询。使用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;
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 materialized views example

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

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

CREATE UNIQUE INDEX rental_category ON rental_by_category (category);

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

REFRESH MATERIALIZED VIEW CONCURRENTLY rental_by_category;

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