怎样在 PostgreSQL 中使用物化视图

简介：关于视图和物化视图

对于复杂的 SQL 查询，每次需要其结果时都重写整个查询是不切实际的。观点解决这个问题。视图是一个命名的（预定义的）查询和一个带有该查询输出的伪表。基于查询创建视图的代码如下所示：

-- pseudocode

CREATE VIEW my_view AS

    SELECT ...

        FROM ... JOIN ... ON ...

        WHERE ... AND ... 

        ORDER BY ...

        LIMIT ...

本质上，查询前面加上 CREATE VIEW my_view AS. 这将创建一个新视图， my_view; 像查询普通表一样查询它：

-- pseudocode

SELECT ...  

    FROM my_view 

    WHERE ...

因此，您可以只使用视图访问查询结果，而不必写出整个查询。视图部分类似于编程语言中的“函数”——复杂查询的简称。它有助于提高用户体验和代码的可读性。

但在表面之下，每次访问视图时，数据库都会将视图转换为完整查询，并在呈现输出之前（重新）评估它。每次都重新计算一个复杂的查询是低效的，并且不会导致数据库的性能提升。相反，对大表重复执行复杂查询会降低性能。

喜欢的观点， 物化视图 为数据库提供一致的接口。物化视图抽象出数据库设计和实现细节，以向 API 层提供一致的查询接口。物化视图将查询结果缓存在持久结构中，因此无需重新计算即可访问它。这可以节省重复访问的复杂查询的时间。

物化视图通过在类似于表的持久数据结构中存储（缓存）命名（预定义）查询的输出来解决这个问题。你跑 SELECT 查询并在其上创建索引，就好像它们是普通表一样。也可以根据对其他物化视图的查询来构造物化视图。

物化视图在 PostgreSQL、Oracle 数据库、SQL Server 和其他一些数据库引擎中可用。此功能在 MySQL 上不可用。

先决条件

要从本指南中受益，必须基本熟悉 PostgreSQL 数据库。假设您有一些 PostgreSQL 基础知识 – 安装软件、创建新数据库、创建表、标准查询等。对于 SQL 示例，假定您已经在 Ubuntu、FreeBSD、CentOS 或其后续版本上设置了一个正在运行的 PostgreSQL 实例，或者您正在使用托管数据库服务。

本指南中的 SQL 示例在 FreeBSD 13.1-RELEASE 上运行的 PostgreSQL 14.5 上进行了测试。它们应该与在所有最新操作系统上运行的所有最新版本的 PostgreSQL 兼容。

设置测试表

在创建物化视图之前，设置两个测试表并用数据填充它们。

创建表 product:

CREATE TABLE IF NOT EXISTS product (

    product_id INTEGER PRIMARY KEY, 

    name VARCHAR(20) NOT NULL, 

    price SMALLINT NOT NULL

);

创建表 orders:

CREATE TABLE IF NOT EXISTS orders (

    order_id INTEGER, 

    product_id INTEGER REFERENCES product (product_id),

    PRIMARY KEY (order_id, product_id)

);

查看创建的表的说明：

d orders

将一些带有虚拟数据的行插入到 product 桌子：

 INSERT INTO product (product_id, name, price) VALUES (1, 'Floppy Disk Drive', 40);

 INSERT INTO product (product_id, name, price) VALUES (2, 'Oculus Quest', 400);

将虚拟数据插入 orders 桌子：

INSERT INTO orders (order_id, product_id) VALUES (1, 1);

INSERT INTO orders (order_id, product_id) VALUES (1, 2);

INSERT INTO orders (order_id, product_id) VALUES (2, 1);

检查表中的数据：

SELECT * FROM orders;



SELECT * FROM product;

创建物化视图

在连接查询上创建物化视图：

CREATE MATERIALIZED VIEW mv_products_orders

AS

SELECT 

    p.product_id, 

    o.order_id, 

    p.name, 

    p.price 

FROM 

    product p 

JOIN 

    orders o 

ON 

    p.product_id = o.product_id;

这创建了一个物化视图， mv_products_orders，并根据创建物化视图时基础表中的数据填充它。默认情况下，物化视图的列名派生自基础表的列名。

检查新创建的物化视图的定义：

d mv_products_orders

检查里面的数据 mv_products_orders:

SELECT * FROM mv_products_orders;

要重命名实体化视图，请使用 ALTER 命令：

ALTER MATERIALIZED VIEW mv_products_orders RENAME TO my_mv;

在物化视图上创建索引

物化视图上的索引与它们在常规表上的索引具有相同的好处 – 它们有助于快速查找。特别是，刷新（在后面的部分中讨论）物化视图的常用方法需要使用索引。您可以在物化视图的任何列上定义索引。

CREATE UNIQUE INDEX product_order ON mv_products_orders (order_id, product_id);

检查物化视图的描述现在是否包含索引：

d mv_products_orders

（物化）视图对（物化）视图

可以基于其他物化视图构建物化视图。

CREATE MATERIALIZED VIEW my_mv

AS

SELECT 

    * from mv_products_orders

    limit 2;

同样，也可以创建基于物化视图的视图和基于视图的物化视图。

刷新（更新）物化视图

PostgreSQL 不会自动刷新物化视图。这意味着，默认情况下，当基础表更新时，物化视图中的数据会过时。您需要手动更新物化视图或将系统配置为自动更新。

添加一个新行到 orders 桌子：

INSERT INTO orders (order_id, product_id) VALUES (2, 2);

重新检查物化视图：

SELECT * FROM mv_products_orders;

输出仍然和以前一样。

手动刷新

这 REFRESH 命令用于刷新物化视图的内容：

REFRESH MATERIALIZED VIEW mv_products_orders;

检查实体化视图现在是否包括新添加的数据到 orders 桌子：

SELECT * FROM mv_products_orders;

进行刷新会丢弃旧内容并重新创建物化视图。请注意，在以这种方式刷新物化视图时，无法查询它。刷新操作锁定了物化视图，甚至阻塞 SELECT 对其进行查询。该锁一直保持到（刷新）事务结束。

这 `CONCURRENTLY` 选项

令人耳目一新 CONCURRENTLY 选项解决了这个问题。

REFRESH MATERIALIZED VIEW CONCURRENTLY mv_products_orders;

随着 CONCURRENTLY 选项，数据库不阻塞 SELECT 在刷新实体化视图时对其进行查询。指定此选项后，它会在内部创建一个临时数据结构，其中包含物化视图查询的新结果。比较新旧结果，并将更改应用于原始物化视图，使用 UPDATE 和 INSERT 操作。

请注意，为了并发刷新，物化视图必须包含至少一个基于列的唯一索引。当您尝试在没有唯一索引的物化视图上使用它时，它会抛出错误：

ERROR:  cannot refresh materialized view "public.mv_products_orders" concurrently

HINT:  Create a unique index with no WHERE clause on one or more columns of the materialized view.

另请注意，一次只能在物化视图上运行一个刷新操作（即使使用 CONCURRENTLY 选项）。

权衡

如果更新（刷新）涉及大量新数据，则刷新速度越快 CONCURRENTLY 选项是不是用过的。这是因为并发刷新中涉及的所有比较和更新操作。

实用提示： 通常，定期清理数据库以清理未使用的数据结构并释放空间是有帮助的。这与并发刷新尤其相关，因为此操作涉及创建临时数据结构。建议在刷新后进行吸尘。吸尘本身就是一个广泛的主题，超出了本指南的范围。

自动刷新

截至 2022 年 11 月，PostgreSQL 没有自动刷新物化视图的功能。但是，可以使用其他工具设置自动刷新。

计划任务

自动刷新物化视图的常用方法是使用 cron 作业：

15 * * * * psql -d name_of_your_database -c "REFRESH MATERIALIZED VIEW CONCURRENTLY mv_products_orders"

将此行添加到用户的 crontab postgres 将调用 psql 每 15 分钟执行一次命令，并将数据库名称和刷新物化视图的 SQL 命令作为参数传递给它。

为了 psql 上面的命令，请注意，如果您没有显式创建或连接到特定数据库，默认情况下，查询将在 postgres 数据库。

实用提示： 由于一次只能运行一个刷新操作，因此在为其安排 cron 作业之前了解刷新操作需要多长时间很重要。

触发器

也可以使用触发器更新物化视图。为此，创建一个刷新物化视图的函数。在那些数据进入物化视图的表上，设置一个触发器在之后调用这个函数 INSERT, UPDATE，和 DELETE 操作。

创建一个 PL/pgSQL – SQL 过程语言刷新物化视图的函数：

CREATE OR REPLACE FUNCTION mv_refresh()

RETURNS trigger LANGUAGE plpgsql AS $$

BEGIN

    REFRESH MATERIALIZED VIEW CONCURRENTLY mv_products_orders;

    RETURN NULL;

END;

$$;

创建一个触发器，在某些操作（INSERT, UPDATE，和 DELETE) 运行于 orders 桌子：

CREATE TRIGGER mv_trigger 

AFTER INSERT OR UPDATE OR DELETE

ON orders

FOR EACH STATEMENT EXECUTE PROCEDURE mv_refresh();

检查的定义 orders 表包括触发器：

d orders

同样，添加一个触发器来调用 mv_refresh() 函数中的数据时 products 表变化。

从订单表中删除一行：

DELETE FROM orders WHERE order_id = 1 AND product_id = 2;

检查实体化视图是否不再包含删除的行：

SELECT * FROM mv_products_orders;

删除物化视图

删除物化视图类似于删除常规视图。

DROP MATERIALIZED VIEW mv_products_orders;

要删除实体化视图以及依赖它的所有其他对象，请使用 CASCADE 选项：

DROP MATERIALIZED VIEW IF EXISTS mv_products_orders CASCADE;

上面的命令删除了两个物化视图： mv_products_orders，也 my_mv，这是基于它创建的。

结论

与所有优化工具一样，物化视图的使用涉及权衡取舍。在决定工具是否合适之前，了解每个用例的具体需求非常重要。

费用

物化视图会消耗额外的存储空间，但实际上，当存储空间便宜时，额外存储空间的成本并不是决定性因素。此外，还要考虑数据新近度；如果底层表经常更新，那么实体化视图缓存的数据很可能在使用时已经部分过时。对于需要返回实时数据的查询来说，这是一个问题。自动刷新有助于提高数据新近度，但它们的使用需要权衡取舍，尤其是对于大型表和写入密集型数据库。