SQL 如何用 materialized view 缓存聚合结果并自动刷新

postgresql物化视图创建即预填数据：CREATE MATERIALIZED VIEW v AS select …; 默认WITH DATA，全量刷新需REFRESH命令，CONCURRENTLY刷新要求唯一索引，无自动机制，须结合pg_cron或crontab调度。

PostgreSQL 的 MATERIALIZED VIEW 怎么创建并预填数据

PostgreSQL 的 MATERIALIZED VIEW 本质是一张物理存储的只读表，创建时会立即执行查询并保存结果。它不自动响应底层表变更，所以“缓存聚合结果”这一步必须靠显式创建完成。

比如你有一张日志表 events，想缓存每天的请求数：

CREATE MATERIALIZED VIEW daily_request_count AS   SELECT date_trunc('day', created_at) AS day, COUNT(*) AS cnt   FROM events   GROUP BY date_trunc('day', created_at);

注意：CREATE MATERIALIZED VIEW 默认就带 WITH DATA（即立刻执行查询填充），不用额外写。如果只想建结构不填数据，得显式加 WITH NO DATA，但那样就不是“缓存结果”了。

怎么让物化视图“自动刷新”——其实它并不自动

PostgreSQL 的物化视图没有内置定时刷新机制。所谓“自动”，实际是靠外部调度 + REFRESH MATERIALIZED VIEW 命令组合实现的。

• REFRESH MATERIALIZED VIEW daily_request_count：阻塞式刷新，期间视图不可读（锁表）
• REFRESH MATERIALIZED VIEW CONCURRENTLY daily_request_count：并发刷新，要求视图有唯一索引（如在 day 上建 UNIQUE 索引），否则报错 Error: cannot refresh materialized view "daily_request_count" concurrently because it does not have a unique index
• 刷新本身不感知增量变化，而是全量重跑定义查询——这意味着底层表越大、聚合越复杂，刷新越慢

常见做法是用 pg_cron 扩展（需管理员安装）写定时任务：

SELECT cron.schedule('0 * * * *', 'REFRESH MATERIALIZED VIEW CONCURRENTLY daily_request_count');

或者用系统 crontab 调 psql -c "REFRESH MATERIALIZED VIEW CONCURRENTLY ..."。

mysql 和 SQL Server 没有原生 MATERIALIZED VIEW

MySQL 8.0+ 完全不支持 MATERIALIZED VIEW 语法；SQL Server 的 MATERIALIZED VIEW 实际叫“索引视图”（INDEXED VIEW），且限制极多：

• 必须用 SCHEMABINDING 创建
• 所有引用列必须显式写出，不能用 *
• 聚合函数仅允许 COUNT_BIG（不是 COUNT），且必须配 GROUP BY
• 刷新是实时的（靠维护索引），不是手动触发，但代价是写入性能下降明显

所以跨数据库谈“自动刷新”前，先确认你用的是 PostgreSQL——其他主流开源数据库基本要靠应用层 cache（如 redis）或定时导出表模拟。

容易被忽略的刷新时机和数据一致性风险

即使用了 CONCURRENTLY，刷新也不是原子性的：新数据写入和刷新过程可能交错，导致某次查询看到“昨天的数据还没刷，今天的已刷完”的中间态。更麻烦的是，如果刷新失败（比如磁盘满、查询超时），物化视图会停留在旧状态，且不会告警。

建议在刷新逻辑里加上检查：

REFRESH MATERIALIZED VIEW CONCURRENTLY daily_request_count; -- 紧接着查最新 day 是否覆盖到预期范围，例如： SELECT MAX(day) FROM daily_request_count;

生产环境别只依赖调度器“跑了就行”，得验证结果是否落在业务可接受的时间窗口内。另外，物化视图的统计信息（pg_stat_all_tables）不会自动更新，必要时手动 ANALYZE，否则查询计划可能走歪。