物化视图 - Materialized View详解

定义

**物化视图 (Materialized View)**是一种数据库对象,它将某个查询的结果集物理存储在磁盘上。与普通视图(Virtual View)不同,物化视图不是虚拟的,而是真实存在的数据表,可以对其建立索引、分区等。当基表数据变化时,物化视图需要通过刷新机制保持数据同步。

核心特征

特征	说明
物理存储	实际占用磁盘空间
预计算	查询结果预先计算好
可建索引	可对物化视图建立索引加速查询
需要刷新	基表变化时需刷新视图
空间换时间	消耗存储换取查询性能

与普通视图对比

普通视图 (Virtual View)
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
CREATE VIEW v_user_orders AS
SELECT u.id, u.name, COUNT(o.id) as order_count
FROM users u LEFT JOIN orders o ON u.id = o.user_id
GROUP BY u.id, u.name;

• 不存储数据,只是SQL的封装
• 每次查询都执行底层SQL
• 实时性: 100%
• 性能: 取决于底层查询复杂度


物化视图 (Materialized View)
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
CREATE MATERIALIZED VIEW mv_user_orders AS
SELECT u.id, u.name, COUNT(o.id) as order_count
FROM users u LEFT JOIN orders o ON u.id = o.user_id
GROUP BY u.id, u.name;

• 物理存储查询结果
• 查询直接读取物化视图(超快)
• 实时性: 取决于刷新策略
• 性能: 极高(无需计算)

为什么需要物化视图?

问题1: 复杂聚合查询慢

-- 电商报表: 统计每个用户的订单总额
SELECT 
  u.id,
  u.username,
  COUNT(DISTINCT o.id) as order_count,
  SUM(o.amount) as total_amount,
  AVG(o.amount) as avg_amount,
  MAX(o.created_at) as last_order_time
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
LEFT JOIN order_items oi ON o.id = oi.order_id
WHERE o.status IN (1, 2)
GROUP BY u.id, u.username
ORDER BY total_amount DESC
LIMIT 100;

-- 执行时间: 30秒
-- 原因:
-- 1. 需要JOIN 3张大表
-- 2. 全表扫描
-- 3. 大量聚合计算
-- 4. 排序开销大

使用物化视图:

-- 创建物化视图
CREATE MATERIALIZED VIEW mv_user_order_stats AS
SELECT 
  u.id,
  u.username,
  COUNT(DISTINCT o.id) as order_count,
  SUM(o.amount) as total_amount,
  AVG(o.amount) as avg_amount,
  MAX(o.created_at) as last_order_time
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
LEFT JOIN order_items oi ON o.id = oi.order_id
WHERE o.status IN (1, 2)
GROUP BY u.id, u.username;

-- 建立索引
CREATE INDEX idx_total_amount ON mv_user_order_stats(total_amount DESC);

-- 查询物化视图
SELECT * FROM mv_user_order_stats
ORDER BY total_amount DESC
LIMIT 100;

-- 执行时间: 5ms ✓ (提升6000倍!)

问题2: 多表JOIN频繁

-- 数据仓库常见场景
SELECT 
  d.date,
  p.category,
  r.region_name,
  SUM(s.sales_amount) as total_sales
FROM sales s
JOIN dim_date d ON s.date_id = d.id
JOIN dim_product p ON s.product_id = p.id
JOIN dim_region r ON s.region_id = r.id
GROUP BY d.date, p.category, r.region_name;

-- 每次查询都要4表JOIN
-- 耗时: 10-60秒

物化视图优化:

CREATE MATERIALIZED VIEW mv_daily_sales_summary AS
SELECT 
  d.date,
  p.category,
  r.region_name,
  SUM(s.sales_amount) as total_sales
FROM sales s
JOIN dim_date d ON s.date_id = d.id
JOIN dim_product p ON s.product_id = p.id
JOIN dim_region r ON s.region_id = r.id
GROUP BY d.date, p.category, r.region_name;

-- 后续查询直接查物化视图
SELECT * FROM mv_daily_sales_summary
WHERE date = '2024-04-12';

-- 耗时: 10ms ✓

刷新策略

策略1: 完全刷新(Full Refresh)

-- MySQL示例(手动刷新)
REFRESH MATERIALIZED VIEW mv_user_order_stats;

-- Oracle示例
BEGIN
  DBMS_MVIEW.REFRESH('MV_USER_ORDER_STATS', 'C'); -- C=Complete
END;

-- PostgreSQL示例
REFRESH MATERIALIZED VIEW mv_user_order_stats;

-- 特点:
-- ✓ 数据完全一致
-- ✗ 耗时长(重新执行整个查询)
-- ✗ 刷新期间可能锁定视图

策略2: 增量刷新(Fast Refresh)

-- Oracle支持增量刷新
BEGIN
  DBMS_MVIEW.REFRESH('MV_USER_ORDER_STATS', 'F'); -- F=Fast
END;

-- 前提条件:
-- 1. 需要创建MATERIALIZED VIEW LOG
-- 2. 记录基表的变更
-- 3. 只应用变化的数据

-- 特点:
-- ✓ 刷新速度快
-- ✓ 资源消耗少
-- ✗ 配置复杂
-- ✗ 有限制条件

策略3: 定时刷新(Scheduled Refresh)

-- PostgreSQL: 创建时指定刷新策略
CREATE MATERIALIZED VIEW mv_daily_stats
REFRESH COMPLETE
AS SELECT ... ;

-- 通过定时任务刷新
-- pg_cron扩展
SELECT cron.schedule(
  'refresh-mv-daily',     -- 任务名
  '0 2 * * *',      -- 每天凌晨2点
  'REFRESH MATERIALIZED VIEW mv_daily_stats'
);

-- MySQL: 通过Event Scheduler
CREATE EVENT refresh_mv_event
ON SCHEDULE EVERY 1 HOUR
DO
  CALL sp_refresh_materialized_view();

实际应用案例

案例1: 电商实时大屏

需求:

• 实时显示销售数据
• 每秒更新
• 多维度统计

方案:

-- 物化视图: 实时销售统计
CREATE MATERIALIZED VIEW mv_realtime_sales AS
SELECT 
  DATE_FORMAT(created_at, '%Y-%m-%d %H:%i:00') as minute,
  COUNT(*) as order_count,
  SUM(amount) as total_amount,
  COUNT(DISTINCT user_id) as active_users,
  AVG(amount) as avg_order_value
FROM orders
WHERE created_at >= NOW() - INTERVAL 24 HOUR
GROUP BY minute;

-- 每30秒刷新
CREATE EVENT refresh_realtime_stats
ON SCHEDULE EVERY 30 SECOND
DO
  REFRESH MATERIALIZED VIEW mv_realtime_sales;

-- 前端查询(超快)
SELECT * FROM mv_realtime_sales
ORDER BY minute DESC;

案例2: BI报表系统

场景: 每日经营报表

-- 物化视图: 每日销售汇总
CREATE MATERIALIZED VIEW mv_daily_business_report AS
SELECT 
  DATE(o.created_at) as report_date,
  COUNT(DISTINCT o.id) as total_orders,
  COUNT(DISTINCT o.user_id) as unique_customers,
  SUM(o.amount) as gmv,
  AVG(o.amount) as aov,
  COUNT(DISTINCT p.category_id) as categories_sold,
  SUM(CASE WHEN o.status = 3 THEN 1 ELSE 0 END) as refunded_orders
FROM orders o
LEFT JOIN order_items oi ON o.id = oi.order_id
LEFT JOIN products p ON oi.product_id = p.id
GROUP BY DATE(o.created_at);

-- 建立索引
CREATE INDEX idx_report_date ON mv_daily_business_report(report_date);

-- 凌晨2点刷新
CREATE EVENT refresh_daily_report
ON SCHEDULE EVERY 1 DAY
STARTS TIMESTAMP(CURRENT_DATE, '02:00:00')
DO
  REFRESH MATERIALIZED VIEW mv_daily_business_report;

-- BI工具查询
SELECT * FROM mv_daily_business_report
WHERE report_date BETWEEN '2024-04-01' AND '2024-04-12';

最佳实践

1. 选择合适场景

适合使用物化视图:
✓ 复杂聚合查询(COUNT/SUM/AVG)
✓ 多表JOIN查询
✓ 查询频率高,数据变化少
✓ 允许短暂延迟(非实时)
✓ 报表统计、数据分析

不适合:
✗ 简单查询
✗ 数据实时性要求极高
✗ 基表频繁更新
✗ 存储空间紧张

2. 刷新策略选择

-- 实时性要求高: 频繁刷新(每分钟)
-- 实时性要求低: 低频刷新(每小时/每天)
-- 数据量大: 增量刷新
-- 数据量小: 完全刷新

-- 推荐配置:
-- 报表类: 每天凌晨刷新
-- 监控类: 每5-30秒刷新
-- 分析类: 每小时刷新

3. 监控物化视图

-- 查看物化视图大小
SELECT 
  schemaname,
  matviewname,
  pg_size_pretty(pg_relation_size(matviewname)) as size
FROM pg_matviews;

-- 查看最后刷新时间
SELECT 
  matviewname,
  ispopulated,
  definition
FROM pg_matviews;

参考资料

相关术语

• 临时表
• 派生表
• 读写分离

---

版本历史:

• 2026-04-12: 初始版本,讲解物化视图原理与实践