Menu

PostgreSQL 视图与物化视图:创建和刷新

视图是一条保存下来的查询,每次读取时都会运行。物化视图存储查询的结果,只有运行 REFRESH MATERIALIZED VIEW 时才会变化。

本页包含可运行的编辑器。编辑、运行,立即查看输出。

视图是一条保存下来、可以像表一样读取的查询:CREATE VIEW name AS SELECT ...。它不存储数据,所以每次读取都会重新运行查询,看到的总是当前的行。物化视图 CREATE MATERIALIZED VIEW name AS SELECT ... 把结果存储在磁盘上,只有运行 REFRESH MATERIALIZED VIEW name 时才会变化。

在视图创建之后插入的订单也出现在结果中(ben 的 90),因为视图的查询是在 SELECT 那一刻运行的。视图可以给经常重复的连接或筛选起个名字,对不该看到某些列的用户隐藏这些列,还能在表结构变化时让旧的查询继续可用。

修改或删除视图

CREATE OR REPLACE VIEW 直接替换查询。新查询必须返回相同的列,名字、类型和顺序都要一样;只能在末尾添加新列。其他修改需要先删除视图再重新创建。

CREATE OR REPLACE VIEW paid_totals AS
  SELECT customer, sum(amount) AS total, count(*) AS orders
  FROM orders WHERE paid GROUP BY customer;

DROP VIEW paid_totals;
DROP VIEW IF EXISTS paid_totals CASCADE;  -- also drops views built on it

通过视图插入和更新

基于单张表的视图,如果没有 GROUP BY、DISTINCT、聚合函数、窗口函数、LIMIT 或集合运算,就会自动成为可更新视图。这时对视图执行 INSERT、UPDATE 和 DELETE 会修改底层的表。WITH CHECK OPTION 会拒绝那些写入后视图无法显示的行:

两次修改都写进了 products。通过视图插入 (4, 'shelf', false) 会报错 new row violates check option for view "active_products",因为视图不会显示这一行。对于不可更新的视图,可以用 INSTEAD OF 触发器定义写入操作要做什么。

创建和刷新物化视图

物化视图只运行一次查询,并把结果行存储下来。读取它和读取表一样快,但在刷新之前,新数据不会出现:

刷新之前,ana 仍然显示 120;刷新之后是 180。REFRESH 会重新运行整条查询,替换存储的行。物化视图适合计算代价高、并且允许数据滞后几分钟或几小时的结果:仪表盘、报表、搜索摘要。

CREATE MATERIALIZED VIEW ... WITH NO DATA 创建一个空的物化视图,在第一次刷新之前读取它会报错。

刷新时不阻塞读取

普通的 REFRESH 会锁住物化视图,读取它的查询要等刷新结束。REFRESH MATERIALIZED VIEW CONCURRENTLY 单独构建新结果,只应用差异部分,所以读取可以继续。它要求物化视图上有一个唯一索引,索引建在普通的列名上,并且不带 WHERE:

没有唯一索引时,刷新会失败,并提示你创建一个。变化的行很少时,CONCURRENTLY 可能比普通刷新更快;它不能用于从未填充过数据的物化视图。无论哪种方式,同一个物化视图同一时间只能运行一个刷新。其他索引和在表上一样有效,所以给查询中用来筛选的列建索引。

定时刷新

PostgreSQL 不会自己刷新物化视图。要用调度器来运行刷新,比如用 cron 调用 psql,或者在安装了 pg_cron 扩展的地方使用它:

# crontab: every 15 minutes
*/15 * * * * psql "$DATABASE_URL" -c 'REFRESH MATERIALIZED VIEW CONCURRENTLY customer_totals'
SELECT cron.schedule('refresh-totals', '*/15 * * * *',
  'REFRESH MATERIALIZED VIEW CONCURRENTLY customer_totals');

视图和物化视图的对比

视图物化视图
存储行否是
数据是否最新总是最新截至上次刷新
读取速度和查询本身一样和读表一样
索引不能(使用表的索引)能
可写简单视图可以不可以

其余选项,比如存储参数和表空间,见 CREATE MATERIALIZED VIEW 参考文档。

常见问题

PostgreSQL 物化视图会自动更新吗?

不会。它保留上一次 REFRESH MATERIALIZED VIEW(或创建时)的结果,直到你再次刷新。可以用 cron、pg_cron 扩展或你的应用来定时刷新。

视图比直接运行查询快吗?

不会。普通视图不存储数据;PostgreSQL 会把它展开到读取它的查询中,所以速度和直接写出这条查询一样。只有物化视图能省下计算工作,代价是数据会过时。

可以给物化视图加索引吗?

可以。物化视图像表一样存储,所以可以对它使用 CREATE INDEX。REFRESH MATERIALIZED VIEW CONCURRENTLY 还要求有一个唯一索引。

怎么列出视图和物化视图?

在 psql 中,\dv 列出视图,\dm 列出物化视图。在 SQL 中,查询 pg_views 和 pg_matviews。

Coddy 编程语言插图

和 Coddy 一起学编程

开始