有谁做彩票网站,重庆市建设工程造价信息网公众号,泉州建站哪些公司,jsp网站地图生成器物化视图概述
物化视图#xff08;Materialized View#xff09;是 PostgreSQL 提供的一个扩展功能#xff0c;它是介于视图和表之间的一种对象。
物化视图和视图的最大区别是它不仅存储定义中的查询语句#xff0c;而且可以像表一样存储数据。物化视图和表的最大区别是它…物化视图概述
物化视图Materialized View是 PostgreSQL 提供的一个扩展功能它是介于视图和表之间的一种对象。
物化视图和视图的最大区别是它不仅存储定义中的查询语句而且可以像表一样存储数据。物化视图和表的最大区别是它不支持 INSERT、UPDATE、DELETE 以及 MERGE 语句只能通过刷新物化视图进行数据的更新。
物化视图通过提前运行并存储查询结果通常用于查询优化、数据仓库、数据集成等场景。
另外PostgreSQL 目前不支持物化视图的实时更新因此物化视图中的数据通常不是最新数据。
创建物化视图
PostgreSQL 使用 CREATE MATERIALIZED VIEW 语句创建视图
CREATE MATERIALIZED VIEW [ IF NOT EXISTS ] name
AS query
[ WITH [ NO ] DATA ];其中IF NOT EXISTS 可以避免重复创建一个已经存在的物化视图时产生错误name 是物化视图的名称query 是物化视图的查询语句。
WITH DATA 表示创建时填充物化视图中的数据这个是默认选项WITH NO DATA 表示创建时不填充数据此时无法查询物化视图中的数据需要执行 REFRESH MATERIALIZED VIEW 命令刷新物化视图数据之后才能查询。 虽然物化视图定义中的查询语句支持 ORDER BY 子句但是不推荐使用。如果想要以指定顺序显示数据应该在查询数据时明确指定排序字段而不应该依赖表中的数据存储顺序。 以下语句创建了一个包含员工统计信息的物化视图
CREATE MATERIALIZED VIEW emp_stat_mv
AS
SELECT d.department_name, j.job_title, count(*)
FROM employees e
JOIN departments d ON d.department_id e.department_id
JOIN jobs j ON j.job_id e.job_id
GROUP BY d.department_name, j.job_title;物化视图 emp_stat_mv 包含了按照部门和职位统计的员工数量。
我们可以直接查询 emp_stat_mv获取员工统计信息
SELECT *
FROM emp_stat_mv
ORDER BY department_name;department_name |job_title |count|
----------------------------------------------------
Accounting |Public Accountant | 1|
Accounting |Accounting Manager | 1|
Administration |Administration Assistant | 1|
Executive |Administration Vice President | 2|
Executive |President | 1|
Finance |Accountant | 5|
Finance |Finance Manager | 1|
Human Resources |Human Resources Representative | 1|
IT |Programmer | 5|
Marketing |Marketing Manager | 1|
Marketing |Marketing Representative | 1|
Public Relations|Public Relations Representative| 1|
Purchasing |Purchasing Clerk | 5|
Purchasing |Purchasing Manager | 1|
Sales |Sales Representative | 29|
Sales |Sales Manager | 5|
Shipping |Stock Manager | 5|
Shipping |Stock Clerk | 20|
Shipping |Shipping Clerk | 20|物化视图可以像表一样支持索引实现约束和查询优化。例如以下语句为 emp_stat_mv 创建了一个唯一索引
CREATE UNIQUE INDEX uk_emp_stat_mv ON emp_stat_mv(department_name, job_title);刷新物化视图
PostgreSQL 物化视图不会自动刷新数据需要手动执行 REFRESH MATERIALIZED VIEW 语句
REFRESH MATERIALIZED VIEW [ CONCURRENTLY ] name
[ WITH [ NO ] DATA ];其中CONCURRENTLY 可以支持刷新物化视图时其他连接的并发读取。使用该选项的前提是物化视图上至少存在一个唯一索引而且不能是表达式索引或者部分索引。
WITH DATA 表示刷新时填充物化视图中的数据这个是默认选项WITH NO DATA 表示刷新时不填充数据刷新后物化视图处于不可查询状态。
CONCURRENTLY 和 WITH NO DATA 选项不能一起使用。未填充数据的物化视图刷新时不支持 CONCURRENTLY 选项。 如果在物化视图定义的查询语句中指定了 ORDER BY 子句刷新物化视图数据时不会保证数据仍然按照指定顺序进行存储。 例如我们可以定期执行以下语句刷新物化视图 emp_stat_mv 中的数据
REFRESH MATERIALIZED VIEW CONCURRENTLY emp_stat_mv;或者执行以下语句清空物化视图 emp_stat_mv 中的数据
REFRESH MATERIALIZED VIEW emp_stat_mv
WITH NO DATA;修改物化视图
ALTER MATERIALIZED VIEW 语句可以修改物化视图的一些属性例如名称、字段名等
ALTER MATERIALIZED VIEW [ IF EXISTS ] name
RENAME [ COLUMN ] column_name TO new_column_name;ALTER MATERIALIZED VIEW [ IF EXISTS ] name
RENAME TO new_name;ALTER MATERIALIZED VIEW [ IF EXISTS ] name
SET SCHEMA new_schema;第一个语法用于修改物化视图的字段名称第二个语法用于修改物化视图的名称第三个语法用于修改物化视图所在的模式。
例如以下语句将物化视图 emp_stat_mv 的名称修改为 emp_stat_mv2
ALTER MATERIALIZED VIEW emp_stat_mv
RENAME TO emp_stat_mv2;ALTER MATERIALIZED VIEW 语句不能修改物化视图定义中的查询语句如果想要修改物化视图定义中的查询语句需要删除并重建物化视图。 ALTER MATERIALIZED VIEW 语句还提供了其他的修改功能具体可以参考官方文档。 删除物化视图
PostgreSQL 使用 DROP MATERIALIZED VIEW 语句删除物化视图
DROP MATERIALIZED VIEW [ IF EXISTS ] name [ CASCADE | RESTRICT ];其中IF EXISTS 可以避免删除一个不存在的物化视图时产生错误CASCADE 表示级联删除依赖于该物化视图的对象RESTRICT 表示如果存在依赖对象则提示错误信息这是默认值。
我们可以使用以下语句删除物化视图 emp_stat_mv2
DROP MATERIALIZED VIEW emp_stat_mv2;
