一、背景说明

物化视图（Materialized View，以下简称 MV）是数据库中一种将查询结果物理持久化的对象。与普通视图仅存储 SQL 定义不同，MV 将查询执行后的结果数据真正写入磁盘——查询时可直接读取这份数据，而不必重新执行复杂的 JOIN 和聚合运算。这种"以空间换时间"的策略带来显著的性能收益，但同时也伴随着存储开销和刷新维护成本。 在实际业务中，技术团队经常面临以下决策问题：

• 决策场景判断： 什么场景该用 MV？什么场景不该用？
• 刷新策略选择： COMPLETE、FAST、FORCE、NEVER 四种策略各有适用条件，如何根据数据特征和业务需求做出正确选择？
• 存储及其他考虑： MV本身会带来额外的存在空间，此外需要考虑哪些维护复问题？
• 保持数据新鲜度：如何选择数据刷新方式？
• ......

本文针对上述问题给出个人学习总结，帮助您独立判断业务场景是否适合 MV，如何根据业务场景选择数据刷新策略、数据刷新方式等等。下文若无特殊说明，相关SQL 语句均在 4.4.2.2 环境测试过，但需要注意的是OceanBase的物化视图功能也是在不断迭代和增强功能，个人总结难免有不足或者欠缺的地方，建议以相关的官网文档指引为准，相关语句仅供参考。

二、该不该用物化视图？

判断一个业务场景是否适合使用 MV，本质上需要从"查询特征"和"数据特征"两个维度出发，评估"以空间换时间"后系统是否能达到平衡或正收益。下面提供一个 3问 + 3评估 的方法供参考，具体的场景建议结合业务数据测试验证为准。

2.1 3问

第 1 问：查询是否被反复执行？

MV 的核心价值在于"一次计算，多次复用"。如果某个查询只是偶尔执行一次（如临时数据探查、随机明细点查），为其创建 MV 的维护成本远大于查询加速收益，不建议使用。

第 2 问：查询是否足够复杂（涉及聚合、多表 JOIN、大数据量扫描）？

MV 的缓存价值与查询本身的计算复杂度正相关。查询越复杂——涉及多张大表的 JOIN、多层嵌套聚合、全表扫描——MV 带来的加速效果越显著。

第 3 问：基表数据变更频率如何？

4.4.2版本OceanBase对于物化视图数据的刷新不支持ON COMMIT 和 ON STATEMENT 刷新模式，所有 MV 的刷新均需通过 ON DEMAND 方式触发，也就是MV 数据不会随基表 DML 提交自动同步，业务必须能接受一定程度的延迟。

实际上无论ON COMMIT 、ON STATEMENT 还是通过 ON DEMAND 方式触发MV数据刷新，其基表的变更频率直接决定了 MV 的刷新成本，基表数据变更频率是选择物化视图技术时候不可避免需要考虑的一个重要因素。

考虑这个因素问题的关键在于把握一个核心比率：变更数据量与基表总数据量的比值。当这个比值很低（如每天仅变更 1%~5%），增量刷新的代价远低于全量刷新，性价比极高；但当这个比值接近 100%（如基表被频繁全量 DELETE + INSERT 轮换），增量刷新需要处理的变更数据量几乎等同于全表数据，增量优势消失，甚至因为增量合并逻辑本身的额外开销而比直接全量刷新更慢。基表变更频率不仅决定了刷新成本的高低，更决定了增量刷新这一关键策略是否可行。评估时建议重点关注的两个维度：

特别需要注意的是： 当基表采用“批量 DELETE + INSERT 轮换”模式（即每天先删除全部旧数据再写入新数据）时，MLOG 捕获的变更记录量接近全表数据量。此时即使基表变更频率很低（每天只有一次），增量刷新的实际处理量也等同于全表扫描，优势完全消失。对于这类场景，应直接选择全量刷新，或重新评估 MV 是否仍然适用——如果基表每天都在全量替换，或许应考虑用普通表 + 定时 ETL 替代 MV。下面抽取总结了几种典型业务的基本变化场景，对于频繁全量更新/删除的场景需要慎重评估的。

2.2 3评估

评估 1：数据新鲜度要求

数据新鲜度从一定程度上来讲可以认为是基表数据变更频率快慢的时候业务角度需要评估因素，业务侧是否接受结果延迟，延迟的容忍度是多少？关注的评估项有如下：

ON COMMIT 不可用时建议考虑其他替代方案：

评估 2：存储成本与维护复杂度

引入 MV 技术后的总存储增量 = MV 容器表存储 + MV Log 表存储 + Σ(MV 索引存储)。如果不涉及增量和索引，则MV LOG 和 MV索引存储取值为0。在评估项的时候考虑以下评估项，维护复杂度方面注意是否有嵌套MV的情况。

评估 3：当前数据库版本是否支持所需特性

OceanBase 对物化视图高级功能的支持依赖具体版本，使用前必须验证：执行 SELECT version() 确认当前OB版本，确定包括但不局限于如下的每个所需特性的支持状态，若当前版本有存在不支持的特性，若特性不支持，是否有版本升级计划？若只能通过版本升级获得，还得评估升级风险。

三、四种刷新策略：什么时候选哪个？

选定 MV 方案后，接下来的聚焦问题就是进一步确定具体采用哪种刷新策略？OceanBase 4.4.2 CE 提供四种刷新策略：

3.1 全量刷新（COMPLETE）

• 适用条件： 当基表数据量不大（百万行以内），全量重算的成本可以接受；或者 MV 的查询语句不满足增量刷新的条件（如包含 DISTINCT、窗口函数等），只能选择全量刷新。此外，即使在日常使用 FAST 刷新的场景下，也建议定期执行一次 COMPLETE 刷新作为兜底方案，修正增量刷新可能累积的数据偏差。

• 潜在代价： COMPLETE 刷新期间需要额外存储空间——系统会创建新的数据表来存储刷新结果，刷新完成后切换到新表并删除旧表，此过程类似 Online DDL 的双表切换。MV 上的索引也需要全量重建。刷新耗时与数据量成正比，对于千万级以上的大表，COMPLETE 刷新可能耗时数十分钟甚至更久。 调度限制： COMPLETE 刷新的定时调度有最小间隔限制（默认 600 秒，由租户配置 _mv_complete_refresh_min_interval 控制），间隔过短将报错 "the interval of complete refresh is less than the minimum interval"。

创建示例：

-- 全量刷新，手动触发
CREATE MATERIALIZED VIEW mv_daily_report
REFRESH COMPLETE ON DEMAND
AS
SELECT region, product, SUM(amount) AS total
FROM orders
WHERE status = 'COMPLETED'
GROUP BY region, product;

3.2 增量刷新（FAST）

适用条件： 基表数据量大（千万级以上），数据变更频繁但每次变更的比例不高（如每天变更 1%~5%），且 MV 的查询语句满足增量刷新的条件。增量刷新只处理自上次刷新以来基表发生的变更数据（通过 MLOG 记录），避免全量重算，是大数据量场景下性价比最高的刷新方式。增量刷新支持的查询场景有：

增量刷新不支持的场景：

• 含 DISTINCT 的查询（直接拒绝，错误信息明确）
• 含窗口函数的查询
• 含子查询的复杂嵌套
• 含 HAVING 子句的查询（完全不支持，非"部分支持"）
• 含 ROLLUP/CUBE/GROUPING SETS 的查询
• 含 ORDER BY 或 LIMIT 的查询
• 含 ROWNUM/ROWID/ORA_ROWSCN 伪列的查询
• 层次查询（CONNECT BY）
• 非确定性查询（如包含 RAND()、UUID() 等函数）
• 指定分区名的查询（PARTITION (p0) 语法）
• COUNT(DISTINCT ...) 等非基本聚合函数（通过 group recalculate 机制有限支持——仅限单表、非标量分组、不支持 ON QUERY COMPUTATION；多表 JOIN 场景下不支持）

MLOG 自动创建机制： 在 OceanBase 4.4.2 中，当租户配置 enable_mlog_auto_maintenance 为 true 且数据版本满足要求时，创建 FAST 刷新 MV 时系统会自动为基表创建 MLOG，用户不需要手动执行 CREATE MATERIALIZED VIEW LOG。若该配置未启用，则需手动创建 MLOG，否则 FAST 刷新将因缺少 MLOG 而无法执行，系统会自动降级为 COMPLETE 刷新（对于 FORCE 策略）或直接报错（对于 FAST 策略）。

创建示例：

-- 增量刷新，手动触发（系统自动创建 MLOG）
CREATE MATERIALIZED VIEW mv_daily_dau
REFRESH FAST ON DEMAND
AS
SELECT dt, channel, COUNT(*) AS dau
FROM user_behavior
GROUP BY dt, channel;

关于 COUNT(DISTINCT) 的特别说明： 上例中使用 COUNT(*) 而非 COUNT(DISTINCT user_id)。在 FAST 刷新中，COUNT(DISTINCT ...) 不属于基本聚合函数，它通过 group recalculate 机制处理，该机制仅支持单表场景，不支持多表 JOIN、标量分组和 ON QUERY COMPUTATION。如需统计去重用户数，建议方案：(1) 先在基表或子查询中去重再聚合，使 MV 定义变为单表聚合；(2) 接受使用 COMPLETE 刷新。

3.3 混合刷新（FORCE）

适用条件： 当不确定 MV 是否满足增量刷新的条件，或查询语句可能在不同数据分布下满足/不满足增量条件时，选择 FORCE 策略。系统会自动判断：能增量就增量，不能增量就全量。即使增量失败也不会报错，自动降级为 COMPLETE，是一种"安全优先"的选择。

FORCE 策略特别适合以下场景：MV 查询语句中包含条件分支（如 CASE WHEN）导致在不同数据分布下可能满足或不满足增量条件；或者开发团队对增量刷新条件不够了解，希望系统自动决策。

创建示例：

-- 先尝试增量，失败则全量
CREATE MATERIALIZED VIEW mv_sales_summary
REFRESH FORCE ON DEMAND
AS
SELECT region, SUM(amount) AS total
FROM orders
GROUP BY region;

3.4 永不刷新（NEVER REFRESH）

适用条件： 数据是静态的，不会变化（如历史归档表、审计日志快照）；或只需要创建时的一份快照作为某个固定时间点的数据切片，之后不需要更新。NEVER REFRESH 的 MV 在创建时执行一次完整查询填充数据，之后无论基表如何变更，MV 数据始终保持创建时的状态。 创建示例：

-- 只创建一次，永不刷新
CREATE MATERIALIZED VIEW mv_2024_snapshot
NEVER REFRESH
AS
SELECT * FROM orders WHERE order_date BETWEEN '2024-01-01' AND '2024-12-31';

四、刷新触发方式：定时调度与手动触发

OceanBase 4.4.2 CE 仅支持 ON DEMAND 刷新模式——刷新不会在基表 DML 提交时自动触发，需要通过以下两种方式之一主动触发。

4.1 定时调度（START WITH ... NEXT ...）

在创建 MV 时通过 START WITH 指定首次刷新时间、NEXT 指定后续刷新间隔，系统会自动创建定时调度任务：

-- 每天自动全量刷新（1 小时后首次执行，之后每 1 天一次）
CREATE MATERIALIZED VIEW mv_daily_sales
REFRESH COMPLETE ON DEMAND
START WITH SYSDATE() + INTERVAL 1 HOUR
NEXT SYSDATE() + INTERVAL 1 DAY
AS SELECT region, SUM(amount) AS total FROM orders GROUP BY region;

-- 每 5 分钟增量刷新
CREATE MATERIALIZED VIEW mv_realtime_stats
REFRESH FAST ON DEMAND
START WITH SYSDATE() + INTERVAL 5 MINUTE
NEXT SYSDATE() + INTERVAL 5 MINUTE
AS SELECT channel, COUNT(*) AS cnt FROM orders GROUP BY channel;

4.2 手动刷新（DBMS_MVIEW.REFRESH）

OceanBase 在 MySQL 模式下提供了独立的 DBMS_MVIEW PL 系统包，通过 CALL 语法调用 REFRESH 存储过程手动触发刷新：

-- 全量刷新（method 参数使用简写 'C'）
CALL DBMS_MVIEW.REFRESH('mv_daily_sales', 'C');

-- 增量刷新（method 参数使用简写 'F'）
CALL DBMS_MVIEW.REFRESH('mv_realtime_stats', 'F');

-- 强制刷新——先尝试增量，失败则全量（method 参数使用 '?'）
CALL DBMS_MVIEW.REFRESH('mv_sales_summary', '?');

-- 使用 MV 定义的默认刷新方法（method 传 NULL）
CALL DBMS_MVIEW.REFRESH('mv_daily_sales', NULL);

-- 指定刷新并行度（第 3 个参数）
CALL DBMS_MVIEW.REFRESH('mv_daily_sales', 'C', 4);

DBMS_MVIEW.REFRESH 完整参数说明：

注意：method 参数必须使用简写字母（'C'/'F'/'?'），不支持完整单词。传入 'COMPLETE' 将返回 "Invalid argument" 错误。这与 Oracle 模式下的行为不同——Oracle 模式接受完整单词。MySQL 模式下的正确用法是 CALL DBMS_MVIEW.REFRESH('mv_name', 'C')。

4.3 刷新状态监控

通过 oceanbase.DBA_MVIEWS 视图查询 MV 的刷新状态和数据同步延迟：

SELECT
    mview_name,
    refresh_method,
    refresh_mode,
    last_refresh_type,
    last_refresh_date,
    on_query_computation,
    rewrite_enabled,
    data_sync_delay    -- 数据同步延迟（秒），反映 MV 数据与基表的时间差
FROM oceanbase.DBA_MVIEWS
WHERE mview_name = 'your_mv_name';

字段语义说明：

• DATA_SYNC_DELAY：诊断 MV 数据延迟的首选字段，单位为秒，计算公式为 TIMESTAMPDIFF(SECOND, SCN_TO_TIMESTAMP(data_sync_scn), NOW())，直接反映 MV 数据落后基表的时间程度。
• STALENESS：在 OceanBase 4.4.2 CE 中恒为 NULL（CAST(NULL AS CHAR(19)) AS STALENESS），不可用于判断刷新状态。
• LAST_REFRESH_DATE / LAST_REFRESH_TYPE：分别表示最近一次刷新的时间和类型（COMPLETE/FAST），字段可用。
• ON_QUERY_COMPUTATION：值为 Y/N，表示是否启用了实时查询合并。

五、CREATE MATERIALIZED VIEW 完整语法

5.1 语法结构

CREATE MATERIALIZED VIEW [IF NOT EXISTS] view_name
    [ (column_list) ]
    [ PRIMARY KEY(column_list) ]
    [table_option_list]
    [partition_option]
    [mv_column_group_option]
    [refresh_clause]
    [query_rewrite_clause]
    [on_query_computation_clause]
AS view_select_stmt;

5.2 refresh_clause 详解

refresh_clause:
    REFRESH [COMPLETE | FAST | FORCE] [PARALLEL n] [mv_refresh_on_clause]
    | NEVER REFRESH

mv_refresh_on_clause:
    [ON DEMAND] [[START WITH expr] [NEXT expr]]

5.3 query_rewrite_clause 详解

query_rewrite_clause:
    DISABLE QUERY REWRITE | ENABLE QUERY REWRITE

• ENABLE QUERY REWRITE：开启查询改写，优化器可自动将基表查询改写为访问 MV
• DISABLE QUERY REWRITE：关闭查询改写（默认） >约束： 开启查询改写的 MV，其定义只能是 SPJG 查询（仅含 SELECT、JOIN、WHERE、GROUP BY）。含 DISTINCT、窗口函数、集合查询、子查询等的 MV 即使指定了 ENABLE QUERY REWRITE 也不会被用于改写——创建时不会报错，但改写永远不会触发。

5.4 on_query_computation_clause 详解

on_query_computation_clause:
    DISABLE ON QUERY COMPUTATION | ENABLE ON QUERY COMPUTATION

• ENABLE ON QUERY COMPUTATION：创建实时物化视图，查询时自动合并 MV 已有数据与 MLOG 中的未刷新变更
• DISABLE ON QUERY COMPUTATION：普通物化视图（默认） ON QUERY COMPUTATION 要求 MV 必须满足 FAST 刷新条件。不满足 FAST 刷新条件的 MV 无法启用 ON QUERY COMPUTATION，创建时将报错 OB_ERR_MVIEW_CAN_NOT_ON_QUERY_COMPUTE

六、查询改写：让应用透明受益

6.1 工作原理

查询改写（Query Rewrite）是优化器的一项高级能力：当用户查询基表时，如果存在一个数据新鲜的 MV 能够回答该查询，优化器会自动将查询改写为访问 MV，从而避免重新执行昂贵的 JOIN 和聚合运算。这一过程对应用完全透明——应用层无需修改任何 SQL 即可获得性能加速。

6.2 启用条件

查询改写需要同时满足 MV 端、系统端和查询端三个层面的条件： MV 端条件：

• 创建时指定 ENABLE QUERY REWRITE
• 定义必须是 SPJG 查询（仅含 SELECT、JOIN、WHERE、GROUP BY）
• MV 必须已刷新且数据新鲜（未过期）

系统端条件 ：

-- 开启查询改写（会话级）
SET query_rewrite_enabled = true;

-- 或全局开启
SET GLOBAL query_rewrite_enabled = true;

-- 强制使用查询改写（即使 MV 数据可能过期）
SET query_rewrite_enabled = 'FORCE';

-- 设置一致性检查级别
-- ENFORCED（默认）：仅使用数据新鲜的 MV
-- STALE_TOLERATED：允许使用数据过期的 MV

SET query_rewrite_integrity = 'STALE_TOLERATED';

query_rewrite_enabled 接受三个值：FALSE（关闭）、TRUE（开启）、FORCE（强制）。query_rewrite_integrity 接受两个值：ENFORCED（仅使用新鲜 MV，默认）、STALE_TOLERATED（允许使用过期 MV）。 查询端条件：

• 查询必须是 SELECT 语句，不含窗口函数、集合查询、层次查询
• FROM 子句包含 MV 中出现的全部表
• MV 的 WHERE 条件是查询 WHERE 条件的子集（即 MV 的过滤范围更宽）
• 查询引用的所有列都在 MV 的 SELECT 列表中
• 有聚合时，FROM 和 WHERE 必须完全匹配

6.3 示例与实测说明

-- 准备基表
CREATE TABLE test_tbl1 (col1 INT, col2 INT, col3 INT);
CREATE TABLE test_tbl2 (col1 INT, col2 INT, col3 INT);

-- 创建支持改写的 MV
CREATE MATERIALIZED VIEW mv_test_join
REFRESH COMPLETE ON DEMAND
ENABLE QUERY REWRITE
AS
SELECT t1.col1, t1.col2, t2.col3
FROM test_tbl1 t1, test_tbl2 t2
WHERE t1.col1 = t2.col1;

-- 刷新 MV（改写前需确保 MV 已刷新）
CALL DBMS_MVIEW.REFRESH('mv_test_join', 'C');

-- 开启查询改写
SET query_rewrite_enabled = true;

-- 以下查询可被改写为访问 mv_test_join
SELECT t1.col1, t1.col2, t2.col3
FROM test_tbl1 t1, test_tbl2 t2
WHERE t1.col1 = t2.col1 AND t1.col2 > 10;
-- 系统自动改写为：SELECT col1, col2, col3 FROM mv_test_join WHERE col2 > 10;

验证方法： 使用 EXPLAIN 查看执行计划，若改写成功，计划中将出现 MV 名称而非基表名称。

七、ON QUERY COMPUTATION：实时物化视图

7.1 原理与机制

普通 MV 在两次刷新之间的数据是"过期"的——基表发生的 DML 变更不会立即反映到 MV 中。而开启了 ENABLE ON QUERY COMPUTATION 的实时 MV 解决了这一问题：在查询时，系统会自动将 MV 已有的持久化数据与 MLOG 中尚未刷新的增量变更进行合并计算，返回与基表一致的最新结果。 这一机制的核心逻辑是：MV 存储的是"上一次刷新时的快照数据"，MLOG 存储的是"自上次刷新以来的增量变更"。实时 MV 在查询时将两者合并——相当于在查询执行时动态完成一次"增量刷新"，但结果不持久化，仅在本次查询有效。 这意味着：

• 不需要频繁触发刷新即可获得实时数据，减少了刷新调度的资源消耗
• 查询性能略低于普通 MV（因为需要合并 MLOG 中的增量数据），但远优于直接查询基表执行完整聚合
• 基表每次 DML 仍会产生 MLOG 写入开销（与普通 FAST 刷新 MV 相同）
• 实时 MV 必须满足 FAST 刷新条件（因为依赖 MLOG 进行增量合并）

7.2 适用场景

• 运营大屏、实时看板： 要求秒级数据新鲜度，基表写入频繁但查询需要实时聚合结果
• 监控系统状态汇总： 需要实时展示各维度的统计指标，但不想频繁触发刷新
• 高频写入 + 高频查询并存： 传统 FAST 刷新需要频繁调度刷新任务，而 ON QUERY COMPUTATION 将"刷新"推迟到查询时按需执行，但该场景建议要结合数据做深入测试验收评估收益。

7.3 使用示例

-- 创建实时物化视图
CREATE MATERIALIZED VIEW mv_realtime_channel_stats
REFRESH FAST ON DEMAND
ENABLE ON QUERY COMPUTATION
AS
SELECT channel, COUNT(*) AS order_count, SUM(amount) AS total_amount
FROM orders
WHERE status = 'COMPLETED'
GROUP BY channel;

-- 首次刷新（填充初始数据，后续基表变更会自动反映在查询结果中）
CALL DBMS_MVIEW.REFRESH('mv_realtime_channel_stats', 'F');

-- 此后基表写入新数据后，直接查询 MV 即可获取最新结果（无需再次刷新）
SELECT * FROM mv_realtime_channel_stats;

生产提示：文中实时物化视图能力特性存在一定复杂度，上线前务必充分测试、评估风险，谨慎落地。

END

瑞蓝创 OceanBase OBCP V4 精英训练营

点击下方图片立即了解详情

··

·

▼ 点击「阅读原文」，了解更多产品技术文章