九月 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 创建一个物化视图的临时更新版本,比较两个版本,并仅对差异数据执行 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;
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);
让我们同时刷新rental_by_category
视图的数据。
REFRESH MATERIALIZED VIEW CONCURRENTLY rental_by_category;
在本教程中,您学习了如何使用 PostgreSQL 物化视图,这对于需要快速数据检索的分析应用程序非常有用。