寿光网站建设_网页设计与制作教程第四版课后答案_2020十大网络热词_汕头疫情最新消息

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;