如何在 PostgreSQL 中使用物化视图

一、关于视图和实例化视图

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

-- pseudocode

CREATE VIEW my_view AS

    SELECT ...

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

        WHERE ... AND ... 

        ORDER BY ...

        LIMIT ...

实质上，查询以 auto 开头。这将创建一个新视图;像查询常规表一样查询它：CREATE VIEW my_view ASmy_view

-- pseudocode

SELECT ...  

    FROM my_view 

    WHERE ...

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

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

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

实例化视图通过在类似于表的持久数据结构中存储（缓存）命名（预定义）查询的输出来解决此问题。您可以运行查询并在其上创建索引，就好像它们是常规表一样。还可以基于对其他实例化视图的查询来构造实例化视图。SELECT

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

1.1、准备工作

要从本指南中受益，必须对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

7.1、该选项CONCURRENTLY

使用该选项刷新可解决此问题。CONCURRENTLY

REFRESH MATERIALIZED VIEW CONCURRENTLY mv_products_orders;

使用该选项，数据库在刷新实例化视图时不会阻止对实例化视图的查询。指定此选项时，它将在内部使用实例化视图查询的新结果创建一个临时数据结构。比较新旧结果，并使用 and 操作将更改应用于原始实例化视图。CONCURRENTLYSELECTUPDATEINSERT

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

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

7.2、折衷

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

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

八、自动刷新

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

8.1、cron 工作

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

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

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

对于上面的命令，请注意，如果您没有显式创建或连接到特定数据库，则默认情况下，查询将在数据库中执行。psqlpostgres

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

6.2、触发器

也可以使用触发器来更新实例化视图。为此，请创建一个刷新实例化视图的函数。在数据进入实例化视图的那些表上，设置触发器以在 、 和操作之后调用此函数。INSERTUPDATEDELETE

创建一个 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;

$$;

创建一个触发器，该触发器在表上运行某些操作（，和）时调用此函数：INSERTUPDATEDELETEorders

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_ordersmy_mv

八、结论

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

8.1、成本

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

8.2、用例

具体化视图有助于提高性能，在系统需要处理大量相同（事先已知）复杂查询的情况下，通常可以显著提高性能。例如：

• 涉及对大型表进行复杂查询的报告和分析应用程序
• 涉及非结构化或半结构化数据的数据库设计，其中查询效率低下
• 显示整理或合并（每日、每月等）信息的仪表板
• 涉及外部表和数据存储的查询 – 重复查询数据源可能很慢或成本高昂
• 向第三方提供 API 服务，其中合同要求通常需要一致的 API 结构，并且预计负载较重

当基础查询简单或快速时，不需要使用实例化视图。

具体化视图不适用于支持实时应用程序（如实时交易、在线竞价、体育比分、消息传递、实时新闻源等）的查询。