PostgreSql
1.物化视图
物化视图是一种存储查询结果的数据库对象。与普通视图不同，物化视图将查询结果实际存储在磁盘上，而不是在每次查询时动态计算结果。因此，物化视图可以显著提高复杂查询的性能，但需要定期刷新以保持数据同步。
| 特性 | 普通视图 | 物化视图 |
|---|---|---|
| 数据存储方式 | 不存储数据，每次查询实时计算 | 实际存储查询结果在磁盘上 |
| 查询性能 | 查询时性能取决于源表和查询复杂度 | 查询速度快，因数据已预先计算并存储 |
| 数据实时性 | 数据始终是最新的 | 数据可能不是最新的，需要刷新 |
| 适用场景 | 对实时性要求高且查询频率低的场景 | 对性能要求高但实时性要求不高的场景 |
- 特性
- 存储查询结果: 物化视图会将查询的结果保存在物理存储中，类似于一张表。
- 提高性能: 适用于复杂查询或聚合操作，可以避免频繁对源表进行计算。
- 需要手动刷新: 因为结果是静态的，当源数据变化时，物化视图不会自动更新，必须通过刷新操作同步。
- 使用场景
- 频繁执行的复杂查询: 如带有聚合（SUM、AVG）或复杂联接的查询。
- 数据分析与报表: 固定时间段内生成统计数据，以支持数据分析工作。
- 离线计算和缓存: 在业务高峰时段通过物化视图减少对源表的压力。
- 优点
- 大幅提高复杂查询性能。
- 减少数据库的计算开销。
- 数据可以按需更新，灵活控制性能与数据实时性的平衡。
- 缺点
- 需要额外的存储空间。
- 数据可能滞后，需手动或定期刷新。
- 刷新操作可能较耗时，特别是对于大数据量的视图。
需求背景: detail表与main表进行关联时，使用到了PHYSICAL_KEYS列，这个列中存储了main表中的id信息, 内容格式为: bsm=1305;，SQL就要这么写(字符串拼接 / 正则表达式)
// 字符串拼接
SELECT * FROM main m INNER JOIN detail d
ON d."PHYSICAL_KEYS" = CONCAT('bsm=', m.id, ';');
// 正则表达式
SELECT * FROM main m INNER JOIN detail d
ON REGEXP_REPLACE(d."PHYSICAL_KEYS", '^.*bsm=([^;]+);.*$', '\1') = m.id
如果可能，提前生成CONCAT('bsm=', m.id, ';')的结果，并存储到中间表或临时列，以减少运行时的计算量。
1.1 创建物化视图
通过正则表达式从PHYSICAL_KEYS提取出bsm_id字段，创建物化视图，并在字段上添加索引。
-- 创建物化视图
CREATE MATERIALIZED VIEW mv_detail AS
SELECT *, regexp_replace("PHYSICAL_KEYS", '^.*bsm=([^;]+);.*$', '\1') AS bsm_id FROM detail;
-- 创建索引
CREATE INDEX mv_detail_bsm_id ON mv_detail (bsm_id);
1.2 查询物化视图
SELECT
c.relname AS view_name,
n.nspname AS schema,
u.usename AS owner
FROM
pg_class c
JOIN
pg_namespace n ON n.oid = c.relnamespace
JOIN
pg_user u ON u.usesysid = c.relowner
WHERE
c.relkind = 'm';
1.3 使用物化视图
SELECT * FROM main m INNER JOIN mv_detail d
ON d.bsm_id = m.id;
1.4 刷新物化视图
如果表中的数据发生了变化（例如删除旧数据并插入新数据），物化视图的内容不会自动更新。物化视图是静态的，它只保存了创建或最后刷新时的查询结果。因此，如果底层表的数据发生变化，物化视图的内容仍然是旧的数据，直到它被刷新。
为了确保物化视图反映最新的数据，您需要定期刷新物化视图。刷新操作会重新执行物化视图的查询，并将新的查询结果存储到物化视图中。
REFRESH MATERIALIZED VIEW mv_detail;
1.5 删除物化视图
在PostgreSQL中，删除物化视图时，与物化视图相关的索引会自动被删除。
执行DROP MATERIALIZED VIEW 物化视图名称，系统会自动清理物化视图的所有关联对象，包括索引。
DROP MATERIALIZED VIEW IF EXISTS mv_detail;
原文地址：https://blog.csdn.net/qq_24099547/article/details/144364906
免责声明：本站文章内容转载自网络资源，如本站内容侵犯了原著者的合法权益，可联系本站删除。更多内容请关注自学内容网（zxcms.com）！