postgreSQL—物化视图_refresh meterialized view concurrently

postgreSQL—物化视图

物化视图概念

物化视图是PostgreSQL中的一种特殊对象，用于存储查询结果的物理副本，并可以周期性地进行更新。与简单视图不同，物化视图将数据实际存储在数据库中，因此可以提供更快的查询性能。创建物化视图时，需要指定一个查询，该查询将用于填充物化视图。之后，可以通过REFRESH MATERIALIZED VIEW命令来更新物化视图的内容。物化视图可以用于优化复杂查询的性能，提高系统的响应速度。

创建物化视图

PostgreSQL可以使用CREATE MATERIALIZED VIEW语句来创建视图

CREATE MATERIALIZED VIEW [ IF NOT EXISTS ] name
AS query [ WITH [ NO ] DATA ];
1
2

其中，IF NOT EXISTS 可以避免重复创建，name是物化视图的名称，query是物化视图的查询语句，WITH DATA表示创建时填充物化视图中的数据，WITH NO DATA表示创建时不填充数据，此时无法查询物化视图中的数据，需要执行REFRESH MATERIALIZED VIEW命令来更新物化视图的内容。

这里我们创建一个物化视图，包含员工的统计信息

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;
1
2
3
4
5
6
7

物化视图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|
1
2
3
4
5
6
7
8
9
10
11

物化视图可以像表一样支持索引，实现约束和查询优化。例如，我们为emp_stat_mv创建了一个唯一索引：

CREATE UNIQUE INDEX uk_emp_stat_mv ON emp_stat_mv(department_name, job_title);
1

刷新物化视图

postgreSQL物化视图不会自动刷新数据，需要手动执行REFRESH MATERIALIZED VIEW命令

   REFRESH MATERIALIZED VIEW [ CONCURRENTLY ] name [ WITH [ NO ] DATA ];
1

CONCURRENTLY可以支持刷新物化视图时其他连接的并发读取。使用该选项的前提是物化视图上至少存在一个唯一索引，而且不能是表达式索引或者部分索引

CONCURRENTLY 和WITH NO DATA选项不能一起使用。未填充数据的物化视图刷新时不支持CONCURRENTLY选项。

定期刷新物化视图

   REFRESH MATERIALIZED VIEW CONCURRENTLY emp_stat_mv;
1

清空物化视图中数据

REFRESH MATERIALIZED VIEW emp_stat_mv WITH NO DATA;
1

修改物化视图

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;
1
2
3
4
5
6
7
8

第一个语法用于修改物化视图的字段名称，第二个语法用于修改物化视图的名称，第三个语言用于修改物化视图所在的模式

删除物化视图

postgreSQL 使用DROP MATERIALIZED VIEW语句来删除物化视图

DROP MATERIALIZED VIEW [ IF EXISTS ] name [ CASCADE | RESTRICT ];
1

其中，IF EXIXTS可以避免删除一个不存在的物化视图，CASCADE表示级联删除依赖于该物化视图的对象，RESTRICT表示如果存在依赖对象则提示错误

例如：

  DROP MATERIALIZED VIEW emp_stat_mv2;
1

转载自：https://blog.csdn.net/horses/article/details/132361360?utm_medium=distribute.pc_relevant.none-task-blog-2_defaultbaidujs_baidulandingword~default-0-132361360-blog-125596690.235^v38pc_relevant_sort_base3&spm=1001.2101.3001.4242.1&utm_relevant_index=3