印刻数据库

外观

PostgreSQL视图与物化视图 ​

视图是数据库中的虚拟表，用于简化复杂查询和控制数据访问。物化视图则是存储查询结果的物理表，可以提高查询性能。本文将介绍PostgreSQL视图与物化视图的创建和使用。

视图概述 ​

什么是视图？ ​

视图是基于一个或多个表的查询结果集，是一个虚拟表。视图本身不存储数据，只存储查询定义。当查询视图时，PostgreSQL会执行视图的查询定义，然后返回结果。

视图的优势 ​

  • 简化复杂查询：将复杂查询封装为视图，简化用户查询
  • 控制数据访问：只允许用户访问视图中的特定列或行，提高安全性
  • 数据一致性：多个应用程序可以使用同一个视图，确保数据一致性
  • 逻辑数据独立性：可以在不影响应用程序的情况下修改底层表结构

视图的类型 ​

  • 普通视图：最常见的视图类型，不存储数据
  • 可更新视图：允许通过视图修改底层表数据
  • 物化视图：存储查询结果的物理表

创建和使用视图 ​

创建视图 ​

使用CREATE VIEW语句创建视图：

sql
CREATE [OR REPLACE] [TEMP | TEMPORARY] VIEW view_name [ (column_list) ]
[ WITH ( view_option_name [= view_option_value] [, ... ] ) ]
AS query
[ WITH [ CASCADED | LOCAL ] CHECK OPTION ];

示例：

sql
-- 创建一个简单视图
CREATE VIEW active_users AS
SELECT id, username, email, created_at
FROM users
WHERE is_active = true;

-- 创建带列别名的视图
CREATE VIEW product_summary (product_id, product_name, total_sales)
AS
SELECT p.id, p.name, SUM(oi.quantity * oi.unit_price)
FROM products p
JOIN order_items oi ON p.id = oi.product_id
GROUP BY p.id, p.name;

-- 创建带选项的视图
CREATE VIEW recent_orders
WITH (security_barrier = true)
AS
SELECT * FROM orders WHERE order_date > CURRENT_DATE - INTERVAL '3 months';

查询视图 ​

查询视图与查询普通表类似：

sql
-- 查询视图
SELECT * FROM active_users;
SELECT * FROM product_summary WHERE total_sales > 1000;

-- 在查询中使用视图
SELECT c.name, COUNT(au.id) AS active_user_count
FROM categories c
LEFT JOIN active_users au ON c.id = au.category_id
GROUP BY c.name;

修改视图 ​

使用CREATE OR REPLACE VIEW语句修改视图：

sql
-- 修改视图
CREATE OR REPLACE VIEW active_users AS
SELECT id, username, email, created_at, last_login_at
FROM users
WHERE is_active = true;

-- 重命名视图
ALTER VIEW active_users RENAME TO current_users;

-- 设置视图所有者
ALTER VIEW current_users OWNER TO admin;

-- 更改视图的架构
ALTER VIEW current_users SET SCHEMA reporting;

删除视图 ​

使用DROP VIEW语句删除视图：

sql
-- 删除视图
DROP VIEW IF EXISTS current_users;

-- 级联删除依赖对象
DROP VIEW active_users CASCADE;

可更新视图 ​

可更新视图允许通过视图修改底层表的数据。默认情况下，只有满足特定条件的视图才能更新。

可更新视图的条件 ​

  • 视图必须只基于一个表
  • 视图必须包含表的主键或唯一约束
  • 视图不能包含聚合函数、DISTINCT、GROUP BY、HAVING、UNION、EXCEPT等
  • 视图不能包含子查询

创建可更新视图 ​

sql
-- 创建可更新视图
CREATE VIEW user_profiles AS
SELECT id, username, email, first_name, last_name
FROM users
WHERE is_active = true;

-- 通过视图插入数据
INSERT INTO user_profiles (username, email, first_name, last_name)
VALUES ('new_user', 'new_user@example.com', 'New', 'User');

-- 通过视图更新数据
UPDATE user_profiles SET email = 'updated_email@example.com' WHERE username = 'new_user';

-- 通过视图删除数据
DELETE FROM user_profiles WHERE username = 'new_user';

使用CHECK OPTION ​

CHECK OPTION用于确保通过视图修改的数据仍然满足视图的查询条件：

sql
-- 创建带CHECK OPTION的视图
CREATE VIEW active_users_check AS
SELECT id, username, email, is_active
FROM users
WHERE is_active = true
WITH CHECK OPTION;

-- 尝试将活动用户改为非活动用户（会失败）
UPDATE active_users_check SET is_active = false WHERE username = 'user1';

带INSTEAD OF触发器的视图 ​

对于不满足可更新条件的视图，可以使用INSTEAD OF触发器实现更新：

sql
-- 创建一个基于多个表的视图
CREATE VIEW order_details AS
SELECT o.id AS order_id, o.order_date, u.username, oi.product_id, p.name AS product_name, oi.quantity, oi.unit_price
FROM orders o
JOIN users u ON o.user_id = u.id
JOIN order_items oi ON o.id = oi.order_id
JOIN products p ON oi.product_id = p.id;

-- 创建INSTEAD OF INSERT触发器
CREATE OR REPLACE FUNCTION order_details_insert_trigger()
RETURNS TRIGGER AS $$
DECLARE
    v_order_id INTEGER;
BEGIN
    -- 插入订单
    INSERT INTO orders (user_id, order_date)
    VALUES (
        (SELECT id FROM users WHERE username = NEW.username),
        NEW.order_date
    )
    RETURNING id INTO v_order_id;
    
    -- 插入订单项
    INSERT INTO order_items (order_id, product_id, quantity, unit_price)
    VALUES (
        v_order_id,
        NEW.product_id,
        NEW.quantity,
        NEW.unit_price
    );
    
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- 关联触发器到视图
CREATE TRIGGER order_details_insert
INSTEAD OF INSERT ON order_details
FOR EACH ROW
EXECUTE FUNCTION order_details_insert_trigger();

-- 通过视图插入数据
INSERT INTO order_details (order_date, username, product_id, quantity, unit_price)
VALUES (CURRENT_DATE, 'user1', 1, 2, 99.99);

物化视图 ​

物化视图是存储查询结果的物理表，与普通视图不同，物化视图存储实际数据，而不是查询定义。物化视图可以提高查询性能，尤其是对于复杂查询或大型数据集。

物化视图的优势 ​

  • 提高查询性能：查询物化视图比查询原始表更快
  • 减少重复计算：复杂查询的结果只需要计算一次
  • 支持索引：可以为物化视图创建索引
  • 适合频繁查询的复杂查询：如报表、数据分析等

创建物化视图 ​

使用CREATE MATERIALIZED VIEW语句创建物化视图：

sql
CREATE [ OR REPLACE ] MATERIALIZED VIEW [ IF NOT EXISTS ] table_name
    [ (column_name [, ...] ) ]
    [ WITH ( storage_parameter [= value] [, ... ] ) ]
    [ TABLESPACE tablespace_name ]
    AS query
    [ WITH [ NO ] DATA ];

示例：

sql
-- 创建物化视图
CREATE MATERIALIZED VIEW monthly_sales AS
SELECT 
    DATE_TRUNC('month', order_date) AS month,
    product_id,
    SUM(quantity) AS total_quantity,
    SUM(quantity * unit_price) AS total_sales
FROM orders o
JOIN order_items oi ON o.id = oi.order_id
GROUP BY DATE_TRUNC('month', order_date), product_id
ORDER BY month, product_id;

-- 创建带索引的物化视图
CREATE MATERIALIZED VIEW product_performance AS
SELECT 
    p.id AS product_id,
    p.name AS product_name,
    COUNT(oi.id) AS order_count,
    SUM(oi.quantity) AS total_quantity,
    SUM(oi.quantity * oi.unit_price) AS total_sales
FROM products p
JOIN order_items oi ON p.id = oi.product_id
GROUP BY p.id, p.name
ORDER BY total_sales DESC;

-- 为物化视图创建索引
CREATE INDEX idx_product_performance_id ON product_performance (product_id);
CREATE INDEX idx_product_performance_sales ON product_performance (total_sales);

查询物化视图 ​

查询物化视图与查询普通表相同：

sql
-- 查询物化视图
SELECT * FROM monthly_sales WHERE month = '2023-01-01';
SELECT * FROM product_performance WHERE total_sales > 10000;

刷新物化视图 ​

物化视图不会自动更新，需要手动刷新或使用调度作业定期刷新：

sql
-- 刷新物化视图（阻塞查询）
REFRESH MATERIALIZED VIEW monthly_sales;

-- 刷新物化视图（不阻塞查询，PostgreSQL 9.4+）
REFRESH MATERIALIZED VIEW CONCURRENTLY monthly_sales;

-- 刷新物化视图并填充数据
REFRESH MATERIALIZED VIEW monthly_sales WITH DATA;

-- 刷新物化视图但不填充数据
REFRESH MATERIALIZED VIEW monthly_sales WITH NO DATA;

注意：使用CONCURRENTLY选项刷新物化视图需要物化视图上有唯一索引。

修改物化视图 ​

sql
-- 修改物化视图定义
CREATE OR REPLACE MATERIALIZED VIEW monthly_sales AS
SELECT 
    DATE_TRUNC('month', order_date) AS month,
    product_id,
    category_id,
    SUM(quantity) AS total_quantity,
    SUM(quantity * unit_price) AS total_sales
FROM orders o
JOIN order_items oi ON o.id = oi.order_id
JOIN products p ON oi.product_id = p.id
GROUP BY DATE_TRUNC('month', order_date), product_id, category_id
ORDER BY month, product_id;

-- 重命名物化视图
ALTER MATERIALIZED VIEW monthly_sales RENAME TO monthly_product_sales;

-- 设置物化视图所有者
ALTER MATERIALIZED VIEW monthly_product_sales OWNER TO admin;

-- 更改物化视图的表空间
ALTER MATERIALIZED VIEW monthly_product_sales SET TABLESPACE reporting_ts;

删除物化视图 ​

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

sql
-- 删除物化视图
DROP MATERIALIZED VIEW IF EXISTS monthly_product_sales;

-- 级联删除依赖对象
DROP MATERIALIZED VIEW monthly_sales CASCADE;

视图与物化视图的比较 ​

特性普通视图物化视图
存储方式存储查询定义存储实际数据
查询性能较慢（每次查询都执行定义）较快（直接查询存储的数据）
数据新鲜度总是最新需要手动或自动刷新
存储空间占用很少占用与查询结果相当的空间
支持索引不支持（索引在底层表）支持
刷新方式无需刷新需要手动或自动刷新
适用场景简化查询、控制访问复杂查询、频繁查询、报表

最佳实践 ​

视图最佳实践 ​

  1. 使用视图简化复杂查询：将频繁使用的复杂查询封装为视图
  2. 使用视图控制数据访问：只暴露必要的列和行
  3. 为视图添加注释：描述视图的用途和维护者
  4. 定期审查视图：确保视图仍然满足需求
  5. 避免嵌套视图：过多的嵌套视图会降低性能
  6. 使用security_barrier选项：对于包含敏感数据的视图，提高安全性

物化视图最佳实践 ​

  1. 为频繁查询的复杂查询创建物化视图：如报表、数据分析等
  2. 为物化视图创建适当的索引：提高查询性能
  3. 合理设置刷新策略：根据数据更新频率和新鲜度要求设置刷新频率
  4. 使用CONCURRENTLY选项刷新：减少对查询的影响
  5. 监控物化视图大小：定期检查物化视图的存储空间使用情况
  6. 考虑使用自动刷新：使用pg_cron等工具定期刷新物化视图

自动刷新物化视图 ​

可以使用pg_cron扩展自动刷新物化视图：

sql
-- 安装pg_cron扩展
CREATE EXTENSION IF NOT EXISTS pg_cron;

-- 设置pg_cron配置
SET cron.database_name = 'mydb';

-- 创建定时任务，每天凌晨2点刷新物化视图
SELECT cron.schedule('0 2 * * *', 'REFRESH MATERIALIZED VIEW CONCURRENTLY monthly_sales');

-- 创建定时任务，每小时刷新一次
SELECT cron.schedule('@hourly', 'REFRESH MATERIALIZED VIEW CONCURRENTLY product_performance');

-- 查看定时任务
SELECT * FROM cron.job;

-- 取消定时任务
SELECT cron.unschedule(job_id);

常见问题（FAQ） ​

什么时候应该使用视图？ ​

  • 当需要简化复杂查询时
  • 当需要控制数据访问时
  • 当需要保持数据一致性时
  • 当需要逻辑数据独立性时

什么时候应该使用物化视图？ ​

  • 当查询非常复杂时
  • 当查询需要频繁执行时
  • 当查询涉及大量数据时
  • 当数据更新频率较低时

物化视图和缓存有什么区别？ ​

  • 物化视图是数据库级别的缓存，由数据库管理
  • 应用程序缓存是应用级别的缓存，由应用程序管理
  • 物化视图更适合复杂查询的结果缓存
  • 应用程序缓存更适合频繁访问的简单数据

如何选择物化视图的刷新频率？ ​

  • 考虑数据更新的频率
  • 考虑业务对数据新鲜度的要求
  • 考虑刷新操作对系统性能的影响
  • 进行性能测试，找到最佳平衡点

物化视图刷新时会锁定表吗？ ​

  • 使用普通刷新会锁定物化视图，阻止查询
  • 使用CONCURRENTLY选项刷新不会锁定物化视图，但需要唯一索引

如何监控物化视图的使用情况？ ​

  • 使用pg_stat_user_tables视图查看物化视图的访问统计
  • 使用pg_stat_statements扩展查看查询执行统计
  • 监控物化视图的大小和刷新时间

总结 ​

视图和物化视图是PostgreSQL中强大的工具，可以帮助你简化复杂查询、控制数据访问和提高查询性能。视图适用于需要简化查询和控制访问的场景，而物化视图适用于需要提高复杂查询性能的场景。

在实际项目中，建议根据具体需求选择合适的视图类型，并遵循最佳实践，确保视图的可维护性和性能。对于物化视图，合理设置刷新策略和创建适当的索引可以进一步提高性能。