跳到主要内容

PostgreSQL 物化视图

物化视图（Materialized View）是 PostgreSQL 提供的一种特殊数据库对象，它将查询结果物理存储在磁盘上，而不是像普通视图那样每次查询时动态计算。这使得物化视图成为优化复杂查询性能的强大工具。

物化视图与普通视图的区别​

理解物化视图的关键在于理解它与普通视图的本质区别。

普通视图​

普通视图是一个虚拟表，它只保存查询定义，每次访问视图时都会重新执行底层查询：

-- 创建普通视图
CREATE VIEW order_summary_view AS
SELECT 
    customer_id,
    COUNT(*) AS order_count,
    SUM(total_amount) AS total_spent
FROM orders
GROUP BY customer_id;

-- 每次查询都会重新执行 GROUP BY 聚合
SELECT * FROM order_summary_view WHERE customer_id = 100;

当基础表数据量大、查询复杂时，普通视图的性能可能不理想，因为每次查询都要重新计算。

物化视图​

物化视图将查询结果实际存储在磁盘上，查询时直接读取存储的数据：

-- 创建物化视图
CREATE MATERIALIZED VIEW order_summary_mv AS
SELECT 
    customer_id,
    COUNT(*) AS order_count,
    SUM(total_amount) AS total_spent
FROM orders
GROUP BY customer_id;

-- 查询直接读取存储的数据，无需重新计算
SELECT * FROM order_summary_mv WHERE customer_id = 100;

对比总结​

特性普通视图物化视图
数据存储不存储数据，只存储查询定义物理存储查询结果
查询性能每次查询需重新执行底层查询直接读取存储数据，速度快
数据实时性总是返回最新数据需要手动刷新才能更新
存储空间不占用数据存储空间占用磁盘空间
索引支持不能在视图上创建索引可以创建索引

创建物化视图​

基本语法​

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

基本示例​

首先创建测试数据：

-- 创建订单表
CREATE TABLE orders (
    id SERIAL PRIMARY KEY,
    customer_id INTEGER,
    order_date DATE,
    total_amount NUMERIC(10, 2),
    status VARCHAR(20)
);

-- 插入测试数据
INSERT INTO orders (customer_id, order_date, total_amount, status)
SELECT 
    (random() * 1000)::INTEGER,
    DATE '2024-01-01' + (random() * 365)::INTEGER,
    (random() * 1000 + 10)::NUMERIC(10, 2),
    (ARRAY['pending', 'completed', 'cancelled'])[floor(random() * 3 + 1)::INTEGER]
FROM generate_series(1, 100000);

-- 创建物化视图：每日销售汇总
CREATE MATERIALIZED VIEW daily_sales_summary AS
SELECT 
    order_date,
    COUNT(*) AS order_count,
    SUM(total_amount) AS total_sales,
    AVG(total_amount) AS avg_order_value
FROM orders
WHERE status = 'completed'
GROUP BY order_date
ORDER BY order_date;

延迟填充数据​

使用 WITH NO DATA 创建空的物化视图，稍后再填充：

-- 创建空的物化视图
CREATE MATERIALIZED VIEW monthly_report AS
SELECT 
    DATE_TRUNC('month', order_date) AS month,
    COUNT(*) AS order_count,
    SUM(total_amount) AS total_sales
FROM orders
GROUP BY DATE_TRUNC('month', order_date)
WITH NO DATA;

-- 此时查询会报错
SELECT * FROM monthly_report;
-- ERROR: materialized view "monthly_report" has not been populated

-- 填充数据
REFRESH MATERIALIZED VIEW monthly_report;

-- 现在可以查询了
SELECT * FROM monthly_report;

延迟填充适用于创建大表物化视图时，可以在系统负载低的时间段执行刷新。

指定列名​

CREATE MATERIALIZED VIEW customer_stats (
    cust_id,
    total_orders,
    total_amount,
    first_order,
    last_order
) AS
SELECT 
    customer_id,
    COUNT(*),
    SUM(total_amount),
    MIN(order_date),
    MAX(order_date)
FROM orders
GROUP BY customer_id;

刷新物化视图​

物化视图的数据不会自动更新，需要手动刷新才能同步基础表的变化。

基本刷新​

-- 完全刷新物化视图
REFRESH MATERIALIZED VIEW daily_sales_summary;

刷新过程会完全替换物化视图的内容：先执行底层查询获取新数据，然后用新数据替换旧数据。在刷新期间，物化视图会被锁定，无法查询。

并发刷新​

使用 CONCURRENTLY 选项可以在不阻塞查询的情况下刷新：

-- 并发刷新（不阻塞读取）
REFRESH MATERIALIZED VIEW CONCURRENTLY daily_sales_summary;

CONCURRENTLY 的要求：

  • 物化视图必须已经有数据（不能是刚创建的空视图）
  • 必须至少有一个 UNIQUE 索引，且该索引只包含列名（不能是表达式索引或部分索引）
-- 创建唯一索引以支持并发刷新
CREATE UNIQUE INDEX idx_daily_sales_date ON daily_sales_summary(order_date);

-- 现在可以使用并发刷新
REFRESH MATERIALIZED VIEW CONCURRENTLY daily_sales_summary;

CONCURRENTLY 的工作原理​

并发刷新的执行过程比普通刷新复杂：

  1. 创建一个临时表存储新数据
  2. 比较新旧数据，找出差异
  3. 删除旧数据中不再存在的行
  4. 插入新数据中新增的行
  5. 更新已变化的行

这种方式的优点是刷新期间物化视图仍然可读，但缺点是：

  • 刷新速度较慢
  • 需要更多的临时空间
  • 必须有唯一索引

清空物化视图​

-- 清空物化视图数据，释放存储空间
REFRESH MATERIALIZED VIEW monthly_report WITH NO DATA;

-- 此时视图处于不可查询状态
SELECT * FROM monthly_report;
-- ERROR: materialized view "monthly_report" has not been populated

管理物化视图​

查看物化视图​

-- 查看所有物化视图
SELECT 
    schemaname,
    matviewname,
    matviewowner,
    ispopulated
FROM pg_matviews;

-- 查看物化视图定义
SELECT 
    schemaname,
    matviewname,
    definition
FROM pg_matviews
WHERE matviewname = 'daily_sales_summary';

-- 使用 psql 命令
\d+ daily_sales_summary

创建索引​

物化视图可以像普通表一样创建索引：

-- 在物化视图上创建索引
CREATE INDEX idx_daily_sales_order_count ON daily_sales_summary(order_count);
CREATE INDEX idx_daily_sales_total ON daily_sales_summary(total_sales);

-- 创建唯一索引（用于并发刷新）
CREATE UNIQUE INDEX idx_daily_sales_date ON daily_sales_summary(order_date);

修改物化视图​

-- 修改所有者
ALTER MATERIALIZED VIEW daily_sales_summary OWNER TO admin;

-- 重命名
ALTER MATERIALIZED VIEW daily_sales_summary RENAME TO daily_order_stats;

-- 设置存储参数
ALTER MATERIALIZED VIEW daily_order_stats SET (fillfactor = 90);

删除物化视图​

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

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

使用场景​

数据仓库和报表​

物化视图最常见的用途是加速报表查询：

-- 创建销售报表物化视图
CREATE MATERIALIZED VIEW sales_report AS
SELECT 
    p.category,
    p.name AS product_name,
    COUNT(o.id) AS order_count,
    SUM(o.quantity) AS total_quantity,
    SUM(o.quantity * o.price) AS total_revenue
FROM order_items o
JOIN products p ON o.product_id = p.id
GROUP BY p.category, p.name;

-- 创建索引加速报表查询
CREATE INDEX idx_sales_report_category ON sales_report(category);

-- 每天刷新报表数据
-- 可以配置定时任务执行
REFRESH MATERIALIZED VIEW CONCURRENTLY sales_report;

聚合计算缓存​

对于复杂的聚合查询，物化视图可以显著提升性能：

-- 用户行为统计物化视图
CREATE MATERIALIZED VIEW user_behavior_stats AS
SELECT 
    user_id,
    COUNT(*) FILTER (WHERE action = 'view') AS view_count,
    COUNT(*) FILTER (WHERE action = 'click') AS click_count,
    COUNT(*) FILTER (WHERE action = 'purchase') AS purchase_count,
    SUM(amount) FILTER (WHERE action = 'purchase') AS total_spent
FROM user_actions
GROUP BY user_id;

-- 创建唯一索引用于并发刷新
CREATE UNIQUE INDEX idx_user_behavior_user_id ON user_behavior_stats(user_id);

预计算复杂连接​

多表连接查询可以通过物化视图预计算：

-- 订单详情视图（多表连接）
CREATE MATERIALIZED VIEW order_details_mv AS
SELECT 
    o.id AS order_id,
    o.order_date,
    c.name AS customer_name,
    c.email AS customer_email,
    p.name AS product_name,
    oi.quantity,
    oi.price,
    oi.quantity * oi.price AS line_total
FROM orders o
JOIN customers c ON o.customer_id = c.id
JOIN order_items oi ON o.id = oi.order_id
JOIN products p ON oi.product_id = p.id;

-- 索引
CREATE INDEX idx_order_details_mv_order ON order_details_mv(order_id);
CREATE INDEX idx_order_details_mv_customer ON order_details_mv(customer_name);

数据同步和分发​

物化视图可以用于数据同步场景：

-- 为外部系统准备的数据视图
CREATE MATERIALIZED VIEW external_sync_data AS
SELECT 
    id,
    name,
    email,
    updated_at
FROM customers
WHERE status = 'active';

-- 增量同步时，可以只查询更新过的数据
SELECT * FROM external_sync_data 
WHERE updated_at > :last_sync_time;

自动刷新策略​

PostgreSQL 不提供内置的自动刷新机制，需要通过其他方式实现。

使用 pg_cron 定时刷新​

-- 安装 pg_cron 扩展
CREATE EXTENSION pg_cron;

-- 每天凌晨 2 点刷新
SELECT cron.schedule(
    'refresh_daily_sales',
    '0 2 * * *',
    $$REFRESH MATERIALIZED VIEW CONCURRENTLY daily_sales_summary$$
);

-- 每小时刷新
SELECT cron.schedule(
    'refresh_hourly_stats',
    '0 * * * *',
    $$REFRESH MATERIALIZED VIEW CONCURRENTLY user_behavior_stats$$
);

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

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

使用触发器自动刷新​

对于数据变更不频繁的场景，可以使用触发器：

-- 创建刷新函数
CREATE OR REPLACE FUNCTION refresh_order_summary()
RETURNS TRIGGER AS $$
BEGIN
    REFRESH MATERIALIZED VIEW CONCURRENTLY daily_sales_summary;
    RETURN NULL;
END;
$$ LANGUAGE plpgsql;

-- 创建触发器（在订单变更后刷新）
-- 注意：频繁触发可能影响性能，请谨慎使用
CREATE TRIGGER trg_refresh_order_summary
AFTER INSERT OR UPDATE OR DELETE ON orders
FOR EACH STATEMENT EXECUTE FUNCTION refresh_order_summary();

应用层控制刷新​

在应用代码中控制刷新时机：

# Python 示例
import psycopg2
from datetime import datetime

def refresh_materialized_views():
    conn = psycopg2.connect("dbname=mydb")
    cur = conn.cursor()
    
    # 在低峰期刷新
    cur.execute("REFRESH MATERIALIZED VIEW CONCURRENTLY daily_sales_summary")
    cur.execute("REFRESH MATERIALIZED VIEW CONCURRENTLY sales_report")
    
    conn.commit()
    cur.close()
    conn.close()

# 定时任务调度
# schedule.every().day.at("02:00").do(refresh_materialized_views)

性能优化​

选择合适的刷新时机​

-- 在系统负载低的时间刷新
-- 例如：凌晨 2 点

-- 使用 pg_stat_activity 监控刷新进度
SELECT 
    pid,
    state,
    query,
    now() - query_start AS duration
FROM pg_stat_activity
WHERE query LIKE '%REFRESH MATERIALIZED VIEW%';

优化刷新性能​

-- 1. 使用 CONCURRENTLY 避免阻塞（适合小量更新）
REFRESH MATERIALIZED VIEW CONCURRENTLY daily_sales_summary;

-- 2. 对于大量数据更新，使用普通刷新更快
REFRESH MATERIALIZED VIEW daily_sales_summary;

-- 3. 调整维护工作内存
SET maintenance_work_mem = '1GB';
REFRESH MATERIALIZED VIEW large_summary;

监控物化视图使用​

-- 查看物化视图大小
SELECT 
    schemaname,
    matviewname,
    pg_size_pretty(pg_total_relation_size(schemaname || '.' || matviewname)) AS size
FROM pg_matviews;

-- 查看物化视图扫描统计
SELECT 
    schemaname,
    matviewname,
    seq_scan,
    idx_scan,
    n_tup_ins,
    n_tup_upd,
    n_tup_del
FROM pg_stat_user_tables
WHERE relname IN (SELECT matviewname FROM pg_matviews);

比较刷新性能​

-- 比较两种刷新方式的时间

-- 普通刷新
EXPLAIN ANALYZE REFRESH MATERIALIZED VIEW daily_sales_summary;

-- 并发刷新
EXPLAIN ANALYZE REFRESH MATERIALIZED VIEW CONCURRENTLY daily_sales_summary;

-- 根据实际测试结果选择合适的刷新方式
-- 数据量小、更新频繁：使用 CONCURRENTLY
-- 数据量大、全量更新：使用普通刷新

物化视图的限制​

不支持的功能​

-- 1. 不支持临时物化视图
CREATE TEMP MATERIALIZED VIEW temp_view AS SELECT * FROM orders;
-- ERROR: materialized views cannot be temporary

-- 2. 不能直接在物化视图上执行 INSERT、UPDATE、DELETE
INSERT INTO daily_sales_summary VALUES (...);
-- ERROR: cannot insert into materialized view "daily_sales_summary"

-- 3. 不能直接修改物化视图定义（需要删除重建）
-- 错误方式：
ALTER MATERIALIZED VIEW daily_sales_summary ALTER COLUMN total_sales TYPE NUMERIC(12,2);
-- ERROR: cannot alter materialized view "daily_sales_summary"

-- 正确方式：删除重建
DROP MATERIALIZED VIEW daily_sales_summary;
CREATE MATERIALIZED VIEW daily_sales_summary AS ...;

数据延迟​

物化视图的数据不是实时的，需要考虑数据延迟：

-- 查看物化视图最后刷新时间
SELECT 
    schemaname,
    matviewname,
    last_refresh
FROM pg_matviews
JOIN pg_class ON pg_matviews.matviewname = pg_class.relname
-- PostgreSQL 17+ 提供 last_refresh 列

-- 在应用中记录刷新时间
CREATE TABLE mv_refresh_log (
    mv_name VARCHAR(100),
    refresh_time TIMESTAMP DEFAULT NOW(),
    duration INTERVAL
);

-- 刷新后记录
INSERT INTO mv_refresh_log (mv_name, duration)
SELECT 
    'daily_sales_summary',
    now() - pg_stat_file(pg_relation_filepath('daily_sales_summary')).modification;

最佳实践​

选择合适的场景​

适合使用物化视图：

  • 复杂聚合查询，执行时间长
  • 数据变更不频繁，可以接受短暂延迟
  • 报表和数据分析场景
  • 需要预计算结果提高性能

不适合使用物化视图：

  • 需要实时数据的场景
  • 数据频繁变更
  • 查询本身已经很快
  • 存储空间有限

设计原则​

-- 1. 为物化视图选择有意义的名称，区分普通视图
CREATE MATERIALIZED VIEW mv_order_summary ...;  -- 使用 mv_ 前缀

-- 2. 只包含必要的列，减少存储空间
-- 好的做法：只选择需要的列
CREATE MATERIALIZED VIEW mv_customer_stats AS
SELECT customer_id, COUNT(*), SUM(total_amount)
FROM orders
GROUP BY customer_id;

-- 避免：SELECT * 

-- 3. 添加适当的筛选条件
CREATE MATERIALIZED VIEW mv_active_customers AS
SELECT * FROM customers WHERE status = 'active';

-- 4. 为并发刷新创建唯一索引
CREATE UNIQUE INDEX idx_mv_customer_id ON mv_customer_stats(customer_id);

-- 5. 为查询模式创建合适的索引
CREATE INDEX idx_mv_order_date ON mv_order_summary(order_date);

刷新策略选择​

场景推荐策略
每日报表定时任务，凌晨刷新
实时性要求高触发器刷新（谨慎使用）
数据量大普通刷新，避免 CONCURRENTLY
查询频繁CONCURRENTLY 刷新，避免阻塞
增量更新考虑使用分区表替代

小结​

本章我们学习了 PostgreSQL 物化视图：

  1. 基本概念：物化视图将查询结果物理存储，提供快速查询
  2. 创建方式：CREATE MATERIALIZED VIEW，支持延迟填充
  3. 刷新机制：手动刷新、并发刷新、清空数据
  4. 管理操作：创建索引、修改属性、删除视图
  5. 使用场景：报表、聚合缓存、复杂连接、数据同步
  6. 自动刷新：pg_cron 定时任务、触发器、应用层控制
  7. 性能优化：选择刷新时机、监控使用、比较性能
  8. 最佳实践：场景选择、设计原则、刷新策略

物化视图是 PostgreSQL 优化查询性能的重要工具。合理使用物化视图，可以在数据实时性和查询性能之间找到最佳平衡。

练习​

  1. 创建一个销售报表物化视图，包含按月统计的销售额
  2. 配置 pg_cron 定时刷新物化视图
  3. 比较普通刷新和并发刷新的性能差异
  4. 设计一个增量刷新策略，只刷新变化的数据

参考资源​