一、关于视图和实例化视图
对于复杂的 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 结构，并且预计负载较重
当基础查询简单或快速时，不需要使用实例化视图。
具体化视图不适用于支持实时应用程序（如实时交易、在线竞价、体育比分、消息传递、实时新闻源等）的查询。