物化视图，就是把一个复杂查询的结果“拍成照片”，存成一张真正的物理表。下次再查同样的内容，不用重新计算，直接读这张“照片”就行。这听起来是不是很像缓存？没错！但是它是数据库内部的一种结构化缓存机制，专为加速重复性复杂查询而生。

普通视图 vs 物化视图的区别：

一、为什么需要物化视图？

痛点1：深度分页卡死（OFFSET大偏移）

-- 用户想看第100000页，每页20条
SELECT * FROM orders ORDER BY created_at DESC LIMIT 20 OFFSET 1999980;

这个查询会让数据库：先按created_at排序全部订单（可能百万行），然后跳过前1,999,980行，最后取20行。结果：CPU飙升、响应超时、用户体验极差。

我们用物化视图：提前生成一个按时间倒序排列的“目录表”，里面只有行号 + 订单ID。查第100000页？我们直接WHERE row_number BETWEEN 1999981 AND 2000000，毫秒级返回！

痛点2：报表查询太慢（复杂聚合）

每天早上，我们运营都要跑这个报表：

SELECT 
  DATE(created_at) AS day,
  COUNT(*) AS order_count,
  SUM(amount) AS total_amount,
  AVG(amount) AS avg_amount
FROM orders
JOIN users ON orders.user_id = users.id
WHERE status = 'PAID'
GROUP BY day
ORDER BY day DESC;

如果订单表有千万行，每次跑都要全表扫描+JOIN+聚合，耗时几十秒甚至几分钟。

我们用物化视图：每天凌晨1点，把这份聚合结果存到一张叫mv_daily_sales的表里。白天，我们运营点开报表，直接SELECT * FROM mv_daily_sales，瞬间出结果！

痛点3：多表JOIN太重

用户中心要展示“最近购买过的商品详情”：

SELECT 
  o.id, o.amount, p.name, p.category
FROM orders o
JOIN products p ON o.product_id = p.id
JOIN users u ON o.user_id = u.id
WHERE u.id = 12345
ORDER BY o.created_at DESC
LIMIT 10;

三张大表JOIN，即使有索引，高频访问也会压垮数据库。

我们用物化视图：预计算一个mv_user_recent_orders表，把常用字段拼好存起来。查询变成单表扫描，速度提升10倍以上。

二、物化视图的组成四要素，缺一不可

任何物化视图都包含以下四个部分：

物化视图不是“魔法”，它只是一个由程序自动维护的普通表。它的“智能”体现在：有人（或系统）定期帮它更新数据。

三、物化视图怎么工作？

场景重现（电商订单分页优化）：假设我们有一个订单表orders，每天新增10万订单，总数据量5000万。用户经常在后台翻页查看订单，尤其是“最新订单”列表。

步骤1：创建物化视图表（容器）

CREATE TABLE mv_orders_by_time (
  seq_id INT UNSIGNED NOT NULL AUTO_INCREMENT, -- 连续行号（核心！）
  order_id BIGINT NOT NULL,
  PRIMARY KEY (seq_id),
  UNIQUE KEY uk_order_id (order_id)
) ENGINE=InnoDB;

注意：seq_id是连续自增的，代表排序后的“位置”。

步骤2：初始化数据（第一次“拍照”）

INSERT INTO mv_orders_by_time (order_id)
SELECT id FROM orders 
ORDER BY created_at DESC, id DESC; -- 按时间倒序，ID辅助排序防歧义

现在mv_orders_by_time就像一本按时间排好的“订单目录”。

步骤3：使用物化视图分页 用户要第3页（每页20条）：

• 计算行号范围：(3-1)*20 + 1 = 41到3*20 = 60；
• 查询目录：

  SELECT order_id FROM mv_orders_by_time 
  WHERE seq_id BETWEEN 41 AND 60;
  

• 用ID回主表查详情：

  SELECT o.* 
  FROM (
      SELECT order_id FROM mv_orders_by_time 
      WHERE seq_id BETWEEN 41 AND 60
      ORDER BY seq_id ASC -- 子查询先固定顺序
  ) AS tmp
  JOIN orders o ON o.id = tmp.order_id -- 按子查询顺序关联
  ORDER BY tmp.seq_id ASC; -- 按物化视图行号排序，彻底保证顺序
  

效果：无论总数据多少，定位ID永远是O(1)索引查找，且严格保留物化视图的排序！

步骤4：保持数据同步（刷新）

新订单来了，目录必须更新。有两种方式：

方式A：全量刷新（简单粗暴）

清空mv_orders_by_time；重新插入所有订单ID（按新顺序）。

TRUNCATE mv_orders_by_time;
INSERT INTO mv_orders_by_time (order_id)
SELECT id FROM orders ORDER BY created_at DESC, id DESC;

优点：实现简单，数据绝对一致。
缺点：大数据量时锁表时间长，可能影响线上服务。

方式B：增量刷新（高级但复杂）

只处理新增/修改的订单；动态调整seq_id（非常复杂，需要算法支持）。⚠️ 增量刷新在MySQL中极难实现，通常只在Oracle、PostgreSQL等专业数据库中支持。

四、主流数据库对物化视图的支持

虽然MySQL不原生支持，但是通过“表+存储过程+事件”的组合，完全可以达到物化视图的效果，且更可控；PostgreSQL 9.4及以上版本支持并发刷新（不锁表），可以通过触发器实现自动刷新，大幅提升实用性。

五、在MySQL中模拟物化视图

下面是一个完整的、可以直接在MySQL 8.0上运行的示例。

1、创建主表并插入数据

-- 创建订单表
CREATE TABLE orders (
  id BIGINT NOT NULL AUTO_INCREMENT,
  user_id BIGINT NOT NULL,
  amount DECIMAL(10,2) NOT NULL,
  status VARCHAR(20) NOT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY idx_created_at (created_at)
) ENGINE=InnoDB;

-- 插入测试数据
INSERT INTO orders (user_id, amount, status, created_at) VALUES
(101, 99.90, 'PAID', '2024-09-20 10:00:00'),
(102, 199.00, 'PAID', '2024-09-21 11:00:00'),
(103, 29.90, 'UNPAID', '2024-09-22 12:00:00'),
(101, 499.00, 'PAID', '2024-09-23 13:00:00'),
(104, 89.00, 'PAID', '2024-09-24 14:00:00');

2、创建物化视图表

CREATE TABLE mv_orders_pagination (
  row_number INT UNSIGNED NOT NULL AUTO_INCREMENT,
  order_id BIGINT NOT NULL,
  PRIMARY KEY (row_number),
  UNIQUE KEY uk_order_id (order_id)
) ENGINE=InnoDB;

3、初始化数据

INSERT INTO mv_orders_pagination (order_id)
SELECT id FROM orders ORDER BY created_at DESC, id DESC;

-- 查看结果
SELECT * FROM mv_orders_pagination;
/*
+------------+----------+
| row_number | order_id |
+------------+----------+
|          1 |        5 |
|          2 |        4 |
|          3 |        2 |
|          4 |        1 |
|          5 |        3 |
+------------+----------+
*/

4、创建刷新存储过程

DELIMITER //
CREATE PROCEDURE refresh_mv_orders_pagination()
BEGIN
    SET autocommit = 0;
    SET unique_checks = 0;
    SET foreign_key_checks = 0;
    
    TRUNCATE TABLE mv_orders_pagination;
    
    INSERT INTO mv_orders_pagination (order_id)
    SELECT id FROM orders ORDER BY created_at DESC, id DESC;
    
    COMMIT;
    SET autocommit = 1;
    SET unique_checks = 1;
    SET foreign_key_checks = 1;
END //
DELIMITER ;

5、设置自动刷新（每5分钟）

-- 开启事件调度器
SET GLOBAL event_scheduler = ON;

-- 创建定时事件
CREATE EVENT event_refresh_mv_orders
ON SCHEDULE EVERY 5 MINUTE
STARTS CURRENT_TIMESTAMP
DO CALL refresh_mv_orders_pagination();

6、使用物化视图分页

-- 第2页，每页2条 → 行号3~4
-- 第一步：从物化视图获取有序的订单ID
SELECT order_id FROM mv_orders_pagination 
WHERE row_number BETWEEN 3 AND 4
ORDER BY row_number ASC; -- 显式保持物化视图的排序

-- 第二步：JOIN主表并按原顺序返回
-- 方案1：使用窗口函数（MySQL 8.0+推荐）
WITH paginated_ids AS (
    SELECT 
        order_id,
        ROW_NUMBER() OVER (ORDER BY row_number) as rn
    FROM mv_orders_pagination 
    WHERE row_number BETWEEN 3 AND 4
)
SELECT 
    o.*,
    pi.rn
FROM orders o
JOIN paginated_ids pi ON o.id = pi.order_id
ORDER BY pi.rn;

-- 方案2：使用变量保持顺序
SELECT 
    o.*,
    tmp.row_number
FROM orders o
JOIN (
    SELECT order_id, row_number 
    FROM mv_orders_pagination 
    WHERE row_number BETWEEN 3 AND 4
    ORDER BY row_number ASC
) tmp ON o.id = tmp.order_id
ORDER BY tmp.row_number;

至此，一个完整的、无排序问题的“MySQL物化视图”就搭建完成了！

说明：在MySQL中，ROW_NUMBER是一个保留关键字（从MySQL 8.0开始作为窗口函数引入），因此不能直接用作列名，我们可以用反引号将其转义（大家自己动手吧）。

六、物化视图的三种刷新策略详解

1、全量刷新（Complete Refresh）

• 做法：清空旧数据 → 重新计算全部数据 → 插入。
• 优点：逻辑简单，数据100%准确。
• 缺点：大数据量时耗时长、锁表、资源消耗大。
• 适用：数据量<100万，或可接受短暂停服的场景。
• 通用语法：MySQL（TRUNCATE+INSERT）、PostgreSQL（REFRESH MATERIALIZED VIEW mv_name;）。

刷新时间估算：假设主表1000万行，先执行排序查询耗时约800秒，再按1万行/秒速度INSERT耗时约1000秒，总刷新需约1800秒（≈30分钟）；且TRUNCATE后至INSERT完成前，mv_orders_pagination表为空，查询会返回空结果！

2、增量刷新（Incremental Refresh）

• 做法：只处理新增/修改/删除的记录，动态维护物化表。
• 优点：刷新快（秒级），不影响线上查询。
• 缺点：实现极其复杂，需要跟踪变更（如用binlog、触发器）。
• 适用：专业数据库（Oracle）、高可用要求场景。

MySQL实现难点：如何知道哪些订单变了？新订单插入后，后面的row_number全部要+1？删除订单后，前面的row_number要-1？ → 几乎无法高效实现连续行号的增量维护！

3、手动刷新（On-Demand）

• 做法：由程序或DBA在合适时机调用刷新命令。
• 优点：完全可控，避免高峰期刷新。
• 缺点：依赖人工，容易遗忘，导致数据滞后。
• 适用：报表系统、非实时业务。
• PostgreSQL增强：支持并发刷新，刷新时不锁表，不影响线上查询，语法：REFRESH MATERIALIZED VIEW CONCURRENTLY mv_name;（需要物化视图有唯一索引）。

最佳实践：在业务低峰期（如：凌晨2点）通过定时任务手动刷新，平衡性能与一致性；PostgreSQL优先使用并发刷新，减少对业务的影响。

七、物化视图的典型应用场景

场景1：深度分页优化（最常用！）

• 问题：OFFSET 1000000性能差。
• 方法：物化视图存储(row_number, id)映射。
• 效果：分页查询从秒级降到毫秒级。

场景2：BI报表预计算

• 问题：每日销售统计SQL复杂，跑一次5分钟。
• 方法：每天凌晨生成mv_daily_sales表。
• 效果：报表打开速度从5分钟 → 0.1秒。

场景3：多维度聚合（数据立方体）

-- 物化视图：按地区+品类+月份聚合
CREATE MATERIALIZED VIEW mv_sales_cube AS
SELECT 
  region, category, DATE_FORMAT(created_at, '%Y%m') AS ym,
  SUM(amount) AS total,
  COUNT(*) AS orders
FROM orders
GROUP BY region, category, ym;

• 效果：任意维度组合查询都极快。

场景4：跨库/跨表JOIN结果缓存

• 问题：用户表在A库，订单表在B库，每次JOIN都要跨库。
• 方法：ETL工具，定期将JOIN结果写入物化视图表。
• 效果：查询变为单库单表。

八、物化视图的三大反模式（千万别踩坑！）

反模式1：对实时性要求高的场景使用

错误做法：用物化视图显示“当前在线用户数”，但是刷新间隔5分钟 → 用户看到的是5分钟前的数据，严重误导！

正确做法：实时数据用普通查询或Redis缓存，不要用物化视图。

反模式2：小表或简单查询也建物化视图

错误做法：用户表只有1000行，还建物化视图SELECT * FROM users → 白白浪费存储，刷新反而增加负担。

正确做法：物化视图只用于复杂、慢、高频的查询。简单查询直接查原表更快！

反模式3：建了物化视图但不建索引

错误做法：

CREATE MATERIALIZED VIEW mv_orders AS
SELECT id, user_id, amount FROM orders WHERE status='PAID';
-- 忘记建索引！

SELECT * FROM mv_orders WHERE user_id = 123; -- 全表扫描！

正确做法：物化视图是表，必须像普通表一样设计索引！

CREATE INDEX idx_mv_user ON mv_orders(user_id);

反模式4：MySQL中使用物化视图分页时，直接用IN查询获取主表数据 → 排序失效，分页结果混乱。

九、物化视图 vs 其他优化手段对比

选择建议：

• 能用索引解决的，优先用索引（成本最低）；
• 需要复杂计算结果复用的，用物化视图；
• 热点Key查询，用Redis；
• 超大表按时间查询，用分区表；
• 深度分页优化时，MySQL物化视图需要配合JOIN+FIELD保证排序，避免直接使用IN查询。

十、PostgreSQL原生物化视图示例

PostgreSQL 9.4及以上版本原生支持物化视图，语法简洁且功能增强，支持并发刷新（不锁表）和触发器自动刷新，大幅提升生产实用性。

1、基础使用（创建+查询+基础刷新）

-- 1. 创建物化视图
CREATE MATERIALIZED VIEW mv_paid_orders AS
SELECT id, user_id, amount, created_at
FROM orders
WHERE status = 'PAID'
ORDER BY created_at DESC;

-- 2. 创建唯一索引（并发刷新必备！）
CREATE UNIQUE INDEX idx_mv_paid_orders_id ON mv_paid_orders(id);
CREATE INDEX idx_mv_paid_orders_created_at ON mv_paid_orders(created_at);

-- 3. 查询（极速！深度分页不再慢）
SELECT * FROM mv_paid_orders
WHERE created_at >= '2024-09-01'
LIMIT 20 OFFSET 100000;

-- 4. 普通手动刷新（会锁表，期间无法查询）
REFRESH MATERIALIZED VIEW mv_paid_orders;

-- 5. 9.4+新增：并发刷新（不锁表，不影响线上查询）
REFRESH MATERIALIZED VIEW CONCURRENTLY mv_paid_orders;

2、9.4+新增：触发器实现自动刷新（准实时同步）

-- 1. 创建记录变更时间的表
CREATE TABLE mv_refresh_log (
    mv_name VARCHAR(100) PRIMARY KEY,
    last_refresh TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    next_refresh TIMESTAMP,
    refresh_interval INTERVAL DEFAULT '5 minutes'
);

-- 2. 修改触发器，只标记需要刷新
CREATE OR REPLACE FUNCTION mark_mv_needs_refresh()
RETURNS TRIGGER AS $$
BEGIN
    UPDATE mv_refresh_log 
    SET next_refresh = CURRENT_TIMESTAMP
    WHERE mv_name = 'mv_paid_orders';
    RETURN NULL;
END;
$$ LANGUAGE plpgsql;

-- 3. 定时任务检查并刷新
CREATE OR REPLACE FUNCTION check_and_refresh_mv()
RETURNS void AS $$
DECLARE
    mv_record RECORD;
BEGIN
    FOR mv_record IN 
        SELECT * FROM mv_refresh_log 
        WHERE next_refresh <= CURRENT_TIMESTAMP
    LOOP
        -- 并发刷新物化视图
        EXECUTE format('REFRESH MATERIALIZED VIEW CONCURRENTLY %I', mv_record.mv_name);
        
        -- 更新刷新时间
        UPDATE mv_refresh_log 
        SET last_refresh = CURRENT_TIMESTAMP,
            next_refresh = CURRENT_TIMESTAMP + refresh_interval
        WHERE mv_name = mv_record.mv_name;
    END LOOP;
END;
$$ LANGUAGE plpgsql;

-- 4. 设置定时执行（每分钟检查一次）
SELECT cron.schedule('*/1 * * * *', 'SELECT check_and_refresh_mv();');

⚠️ 注意：PostgreSQL的自动刷新，我们需要谨慎使用，高频变更的源表会导致物化视图频繁刷新，消耗数据库资源；我们建议对低频变更的表使用，或结合定时刷新+触发器的混合策略。

十一、总结：什么时候该用物化视图？

我们的查询是否满足以下所有条件？
│
├─ 是复杂查询（JOIN/聚合/排序）？
├─ 被频繁执行（每天>100次）？
├─ 执行很慢（>1秒）？
└─ 对数据实时性要求不高（可接受几分钟/几小时延迟）？
   │
   └─ 那就用物化视图！

物化视图：用磁盘空间换CPU时间，在合适的场景下，能让我们的慢查询从“无法忍受”变成“瞬间响应”。

我们顺便提醒一下：

1. MySQL物化视图分页严禁直接使用IN查询，必须通过JOIN+子查询+FIELD函数保证排序与物化视图一致；
2. PostgreSQL 9.4+是物化视图的黄金版本，并发刷新和触发器自动刷新能大幅提升实用性，优先使用；
3. 所有物化视图分页场景，都需要显式保证“物化视图行号顺序”与“主表查询结果顺序”一致，避免分页混乱。

阅读原文：原文链接