提要:本教程将向您介绍 PostgreSQL 物化视图,这些视图允许您物理存储查询结果并定期更新数据。

我们知道,视图是表示基础表的数据的虚拟表。简单的视图也可以是可更新的。PostgreSQL 将视图概念扩展到更高级,允许视图物理地存储数据,这种视图称为物化视图(materialized views)。物化视图将开销非常大的复杂的查询结果进行缓存,然后允许您定期刷新此结果。

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

创建物化视图

使用 CREATE MATERIALIZED VIEW 语句来创建物化视图:

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

首先,在 CREATE MATERIALIZED VIEW 后指定视图名称。

其次,在 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;

物化视图示例

下面的语句创建了一个名为 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;

现在,可以查询数据了。

现在开始,我们可以使用 REFRESH MATERIALIZED VIEW 来刷新 rental_by_category 的数据了。然而,要在刷新数据时使用 CONCURRENTLY 选项,还得先创建一个 UNIQUE 索引:

CREATE UNIQUE INDEX rental_category ON rental_by_category (category);

让我们刷新数据吧:

REFRESH MATERIALIZED VIEW CONCURRENTLY rental_by_category;
上一篇:可更新视图