第 9.6 课 · 阅读时间：约 9 分钟

物化视图是一种把结果像表一样保存在磁盘上的视图：查询只执行一次，读取时直接返回保存的行，而不是重新计算。这样繁重的报表会变得很快，代价是数据只更新到最近一次刷新的时刻。在本课中，你将学习在 PostgreSQL 中创建、查询和刷新物化视图，了解哪些数据库支持它，并在 MySQL 中用汇总表实现同样的效果。

SQL 中的物化视图

在上一课中你创建了视图：视图只保存查询，每次读取都会重新执行它。这很方便，但一个连接五张表、聚合成千上万行数据的报表，每次请求都要把这些工作重做一遍。

物化视图通过保存结果来解决这个问题。示例使用 PostgreSQL 语法下的 Sakila 架构；文中展示的行为已在 PostgreSQL 17 上验证，MySQL 部分在 MySQL 8.0 上验证。

什么是物化视图？

物化视图是一个有名称的查询，加上其结果的一份保存副本。读取来自这份副本，所以和读取一张小表一样快。副本不会自动更新：源表发生变化后，物化视图会保留旧数据，直到你刷新它。

创建物化视图

语法与视图相同，只是多了 MATERIALIZED 一词：

CREATE MATERIALIZED VIEW category_revenue AS
SELECT c.name AS category, COUNT(*) AS rentals, SUM(p.amount) AS revenue
FROM payment AS p
JOIN rental AS r ON r.rental_id = p.rental_id
JOIN inventory AS i ON i.inventory_id = r.inventory_id
JOIN film_category AS fc ON fc.film_id = i.film_id
JOIN category AS c ON c.category_id = fc.category_id
GROUP BY c.name;

结果：PostgreSQL 执行一次查询并保存 16 行，每个类别一行。

之后可以像查询表一样查询它：

SELECT category, rentals, revenue
FROM category_revenue
ORDER BY revenue DESC
LIMIT 5;

结果：Sports（1179 次租赁，5314.21）、Sci-Fi（4756.98）、Animation（4656.30）、Drama（4587.39）、Comedy（4383.58）。五个连接不会再次执行：这些行来自保存的结果。

如果想创建对象但不执行查询，可以加上 WITH NO DATA。这样的视图在第一次刷新之前无法读取：SELECT 会报错 "materialized view has not been populated"。

物化视图是只读的。INSERT INTO category_revenue … 会报错 "cannot change materialized view"。它的数据只能通过刷新来改变。

刷新数据

有新的支付记录时，物化视图仍然显示旧的汇总值。要重新计算它：

REFRESH MATERIALIZED VIEW category_revenue;

结果：查询重新执行并替换保存的行。在执行这条命令之前，新的支付记录完全不会出现在视图中。

普通的 REFRESH 会锁住物化视图：读取它的查询要等刷新结束。对于一直被读取的视图，请使用 CONCURRENTLY。这时新结果会在旧结果旁边构建，只替换发生变化的行，因此不会阻塞读取：

CREATE UNIQUE INDEX category_revenue_category_idx ON category_revenue (category);

REFRESH MATERIALIZED VIEW CONCURRENTLY category_revenue;

CONCURRENTLY 要求物化视图上有唯一索引；没有唯一索引时，命令会报错 "cannot refresh materialized view concurrently"。它也比普通刷新慢，所以只在读取被阻塞会造成问题时使用。

刷新频率取决于任务：每日报表每晚刷新一次，仪表板每隔几分钟刷新一次。刷新由调度器触发，例如 cron 或 pg_cron 扩展。

索引与删除

物化视图像表一样存储，所以可以为它创建普通索引，而普通视图做不到。索引会在第 10 模块中详细讲解。

删除物化视图：

DROP MATERIALIZED VIEW category_revenue;

PostgreSQL 中的物化视图列表在 pg_matviews 里；ispopulated 列显示视图是否已经填充数据。

不同数据库的支持情况

在 MySQL 中，CREATE MATERIALIZED VIEW 是语法错误。

MySQL 中的物化视图：汇总表

在 MySQL 中，同样的结果保存在一张普通表里，由你自己重建：

CREATE TABLE category_revenue AS
SELECT c.name AS category, COUNT(*) AS rentals, SUM(p.amount) AS revenue
FROM payment AS p
JOIN rental AS r ON r.rental_id = p.rental_id
JOIN inventory AS i ON i.inventory_id = r.inventory_id
JOIN film_category AS fc ON fc.film_id = i.film_id
JOIN category AS c ON c.category_id = fc.category_id
GROUP BY c.name;

为了刷新时不让读取方看到空表，先填充一份新副本，再用一条语句交换两张表：

CREATE TABLE category_revenue_new LIKE category_revenue;

INSERT INTO category_revenue_new
SELECT c.name, COUNT(*), SUM(p.amount)
FROM payment AS p
JOIN rental AS r ON r.rental_id = p.rental_id
JOIN inventory AS i ON i.inventory_id = r.inventory_id
JOIN film_category AS fc ON fc.film_id = i.film_id
JOIN category AS c ON c.category_id = fc.category_id
GROUP BY c.name;

RENAME TABLE category_revenue TO category_revenue_old,
             category_revenue_new TO category_revenue;

DROP TABLE category_revenue_old;

结果：RENAME TABLE 同时交换两张表，因此查询要么看到旧数据，要么看到新数据，永远不会看到空表。

对于小表，在同一个事务中先 DELETE 再 INSERT … SELECT 也可以。这里不要用 TRUNCATE：在 MySQL 中它会隐式提交，读取方会看到空表（见第 8.4 课）。

刷新可以用 MySQL 的事件调度器（CREATE EVENT … ON SCHEDULE EVERY 1 HOUR）或外部的 cron 任务来定时执行。

什么时候使用物化视图

适合的场景：

• 连接多张表、聚合大量数据的繁重报表和仪表板；
• 允许滞后几分钟或几小时的数据：当日销售、月度统计、排行榜；
• 被很多用户读取、很少变化的结果。

不适合的场景：

• 必须始终保持最新的数据，例如账户余额或可用库存；
• 源表一直在变化，视图需要不停地刷新；
• 简单快速的查询：普通视图或查询本身就够了。

本课要点：

• 物化视图保存其查询的结果；读取时返回保存的行，而不执行查询。
• 数据截至最近一次 REFRESH MATERIALIZED VIEW；在 PostgreSQL 中不会自动更新。
• REFRESH … CONCURRENTLY 不会阻塞读取，但要求物化视图上有唯一索引。
• 物化视图是只读的，并且可以有自己的索引。
• MySQL 和 MariaDB 没有物化视图：使用汇总表，并用 RENAME TABLE 进行交换。
• 物化视图适用于可以容忍数据略有滞后的繁重报表，而不适用于必须保持最新的数值。

常见问题

视图和物化视图有什么区别？

视图只保存查询，每次读取时执行它，所以数据始终是最新的。物化视图保存查询的结果：读取很快，但数据截至最近一次刷新。

物化视图会自动更新吗？

在 PostgreSQL 中不会：你需要手动或定时执行 REFRESH MATERIALIZED VIEW。Oracle 可以在每次提交时刷新，SQL Server 的索引视图会在每次数据变化时自动维护。

MySQL 支持物化视图吗？

不支持。MySQL 和 MariaDB 只有普通视图。常见的替代方案是用 INSERT … SELECT 填充的汇总表，并定时重建。

可以给物化视图创建索引吗？

可以。物化视图像表一样存储，所以可以建索引，而且 REFRESH … CONCURRENTLY 必须要有唯一索引。

面试题

什么时候你会用物化视图而不是普通视图？

当查询开销很大、结果被频繁读取，而且数据可以稍有滞后时：报表、仪表板、聚合统计。如果数据必须始终最新，或者查询本身很便宜，普通视图更合适。

REFRESH MATERIALIZED VIEW CONCURRENTLY 有什么作用？需要什么条件？

它在不阻塞读取方的情况下重新计算结果：新结果在旧结果旁边构建，只应用差异部分。它要求物化视图上有唯一索引，并且比普通刷新慢。

你会如何在 MySQL 中实现物化视图？

用 CREATE TABLE … AS SELECT 创建汇总表。刷新时先填充一份新副本，再用 RENAME TABLE 交换两张表，并通过事件调度器或 cron 定时执行刷新。