前言

博客：http://blog.healerjean.com

一、物化视图概览

物化视图（Materialized View, MV） 是一种通过预计算和存储查询结果来优化查询性能的核心功能。它结合了计算逻辑与数据存储，能够显著加速复杂查询，同时支持多种刷新策略和透明查询改写。以下是针对 Doris 物化视图的详细解析：

1、与普通视图的本质区别

特性	物化视图	普通视图
数据存储	存储预计算结果，占用物理空间	仅存储查询逻辑，不存数据
查询性能	直接读取结果，响应速度快	实时计算，响应慢
更新机制	支持定时/实时刷新	每次查询重新计算
透明改写	支持查询自动匹配物化视图	需显式引用视图

2、`Doris` 物化视图的优势

查询加速：对聚合、多表连接等耗时操作预计算，响应时间从分钟级降至毫秒级。
透明改写：查询无需修改，Doris 自动匹配最优物化视图，降低开发成本。
灵活刷新：支持全量、分区增量、实时刷新，适应不同时效性需求。
湖仓一体：直接物化外部数据源（如 Hive、MySQL），避免数据导入开销。

3、物化视图的使用场景

1）查询加速（`OLAP` 场景）

典型场景：BI 报表、Ad-Hoc 查询、复杂聚合分析。

示例：

-- 原始查询：多表连接 + 聚合，耗时 5s
SELECT d.date, p.category, SUM(s.amount) 
FROM sales s
	JOIN products p ON s.product_id = p.id
	JOIN dates d ON s.sale_date = d.id
GROUP BY d.date, p.category;
   
-- 创建物化视图：预计算结果，查询耗时降至 50ms
CREATE MATERIALIZED VIEW mv_sales_agg
DISTRIBUTED BY HASH(date, category) BUCKETS 10
REFRESH ASYNC
AS
SELECT d.date, p.category, SUM(s.amount) AS total_amount
FROM sales s
JOIN products p ON s.product_id = p.id
JOIN dates d ON s.sale_date = d.id
GROUP BY d.date, p.category;

2）轻量化 `ETL`（数据分层）

场景：构建 DWD（明细层）、DWM（中间层）、DWS（汇总层）数据模型。
优势：通过物化视图嵌套实现分层计算，避免手动调度 ETL 任务。

示例：

-- DWD 层：明细数据
CREATE MATERIALIZED VIEW mv_dwd_sales
REFRESH ASYNC
AS SELECT * FROM sales;
   
-- DWM 层：按产品分组聚合
CREATE MATERIALIZED VIEW mv_dwm_product_sales
REFRESH ASYNC
AS SELECT product_id, SUM(amount) AS total_sales 
   FROM mv_dwd_sales GROUP BY product_id;

3）湖仓一体（外部数据加速）

场景：直接查询 Hive、MySQL 等外部数据源，避免数据导入。

示例：

-- 创建 MySQL 物化视图（需配置 DataSource）
CREATE EXTERNAL RESOURCE "mysql_resource" 
PROPERTIES (
  "type" = "mysql",
  "host" = "127.0.0.1",
  "port" = "3306",
  "user" = "root",
  "password" = "123456",
  "database" = "test"
);
   
CREATE MATERIALIZED VIEW mv_mysql_data
REFRESH ASYNC
PROPERTIES (
  "resource" = "mysql_resource",
  "table" = "orders"
)
AS SELECT user_id, SUM(price) AS total_spent FROM orders GROUP BY user_id;

4、物化视图的分类与策略

1）按数据时效性分类

维度	同步物化视图	异步物化视图
一致性	强一致性（与基表实时同步）	最终一致性（允许延迟）
查询方式	不支持直接查询	支持直接查询
刷新延迟	毫秒级，实时刷新（基于 `Binlog`/`CDC`）	分钟/小时级，定时刷新（全量/分区增量）
SQL 模式	仅支持单表	支持单表/多表
典型场景	实时风控、金融交易	BI 报表、数据湖加速

2）按 `SQL` 模式分类

类型	单表物化视图	多表物化视图
定义 `SQL`	仅包含单表查询（如 `SELECT * FROM t`）	包含多表连接（如 `JOIN`、`UNION`）
典型场景	数据汇总、维度聚合	星型模型、雪花模型查询加速

2）按刷新策略分类

策略	描述
全量刷新	每次刷新重新计算全部数据，适用于小表或低频刷新场景。
分区增量刷新	当物化视图基表的分区数据发生变化时，识别出对应变化的分区并刷新这些分区，从而实现分区增量刷新，而无需刷新整个物化视图
实时刷新	通过 `Binlog` 或 `CDC` 捕获基表变更，实现近实时同步（同步物化视图专用）

5、问题

1）聚合和物化视图

特性	同步物化视图	异步物化视图	聚合模型
数据更新	实时同步	定期刷新（全量/分区增量）	导入时预聚合，后台 Compaction
支持操作	单表 `SELECT`	多表 `JOIN`、复杂聚合	单表预聚合
查询性能	极致优化（实时性高）	高（复杂查询加速明显）	高（预聚合减少计算量）
数据一致性	强一致性	最终一致性	最终一致性（查询时聚合）
是否存储数据	存储	存储	不存储
适用场景	实时报表、简单聚合	批量分析、复杂查询加速	多维度汇总、存储优化
典型用例	单表实时销售额统计	多表 `JOIN` 的月度销售分析	电商平台月销售业绩汇总

a、选型建议

聚合模型适合“弱实时”场景：
- 允许查询结果轻微波动（如用户画像、趋势分析）。
- 需长期存储汇总数据且查询维度固定（如日/周/月报表）。
同步物化视图适合“强实时”场景：
- 需严格保证每次查询结果一致（如金融交易、实时告警）。
- 写入频率低或查询逻辑简单（如单表聚合）。
异步物化视图是“复杂实时”的折中方案：
- 支持多表 JOIN 和复杂聚合，通过定期刷新平衡实时性与性能。
- 适合湖仓一体、批量分析等场景。

b、决策树：

1. 是否需要强一致性或精确结果？

是→
- 若查询简单（单表聚合）：同步物化视图（实时强一致）。
- 若查询复杂（多表 JOIN）：明细模型 + 应用层缓存（通过外部缓存如 Redis 保证一致性）。
否（允许最终一致性或轻微波动） → 进入下一步。

2. 写入模式与查询频率如何？

高频小批量写入 + 高频查询→
- 若聚合维度固定：同步物化视图（减少查询时聚合开销）。
- 若聚合维度灵活：明细模型 + 异步物化视图（按需构建聚合视图）。
低频大批量写入或低频查询 → 进入下一步。

3. 是否需存储优化或多维度汇总？

是 →
- 若查询维度固定且高频：聚合模型（预聚合减少存储和计算）。
- 若查询维度多变：异步物化视图（灵活定义聚合逻辑）。
否 → 明细模型（灵活支持任意查询）。

2）主键模型的表能用来创建物化视图吗？

物化视图对基表的数据模型没有要求。但是物化视图本身只能是明细模型。主键模型的表可以创建物化视图，但物化视图仅能改变列顺序或筛选列，无法对主键表的数据进行聚合。这是因为主键模型的核心是保证主键唯一性，物化视图不能破坏这一约束

适用场景：列裁剪与排序优化：主键模型的物化视图适合优化查询性能的场景，例如：

仅保留高频查询的列（减少 I/O）。
调整列顺序以匹配查询的前缀索引（加速过滤）

CREATE MATERIALIZED VIEW mv_reorder AS 
SELECT col1, col3, col2 FROM unique_key_table;  -- 仅重排列[5](@ref)

-- 主键模型表定义
CREATE TABLE unique_key_table (
    user_id BIGINT,
    date DATE,
    cost DECIMAL(10,2),
    PRIMARY KEY (user_id, date)
) UNIQUE KEY(user_id, date)
DISTRIBUTED BY HASH(user_id) BUCKETS 10;

-- 有效的物化视图（仅列重排）
CREATE MATERIALIZED VIEW mv_user_cost AS 
SELECT date, user_id, cost FROM unique_key_table;  -- 支持[5](@ref)

-- 无效的物化视图（尝试聚合）
CREATE MATERIALIZED VIEW mv_agg AS 
SELECT user_id, SUM(cost) FROM unique_key_table GROUP BY user_id;  -- 报错[5](@ref)

二、同步物化视图

同步物化视图是一种与基表数据保持 实时同步 的预计算数据结构，将预先计算（根据定义好的 SELECT 语句）的数据集，存储在 Doris 中的一个特殊的表。其本质是通过存储预计算结果（如聚合、过滤后的数据）来加速查询，同时消除数据延迟

基表数据发生变更（如插入、更新、删除）时，都能保证基表（Base Table）和物化视图表的数据同步更新并保持一致，只有同步完成后，相关命令才会结束，无需任何额外的人工维护成本。

查询时，Doris 会自动匹配到最优的物化视图，并直接从物化视图中读取数据。。

默认行为：创建时自动同步基表数据，物化视图会立即包含基表当前的所有数据。
原理：同步物化视图通过实时监听基表变更（如导入、更新），在创建时会触发一次全量同步，确保数据一致性。

1、适用场景

加速耗时的聚合运算
查询需要匹配不同的前缀索引
- 用户的原始表包含三列（k1、k2、k3），其中 k1 和 k2 被设置为前缀索引列。当用户查询条件中包含where k1=1 and k2=2时，查询可以通过索引进行加速。然而，在某些情况下，用户的过滤条件可能无法匹配到前缀索引，例如 where k3=3，此时无法通过索引来提升查询速度。为了解决这个问题，可以创建一个以 k3 作为第一列的物化视图。
通过预先过滤减少需要扫描的数据量
通过预先完成复杂的表达式计算来加速查询

2、使用物化视图

1、创建物化视图

create materialized view store_amt as 
select store_id, sum(sale_amt) from sales_records group by store_id;

2、检查物化视图是否创建完成

show alter table materialized view from test_db;

3、取消创建物化视图：如果创建物化视图的后台异步任务还未结束，可以通过以下命令取消任务：

cancel alter table materialized view from test_db.sales_records;

4、查看物化视图的表结构

desc sales_records all;

5、查看物化视图的创建语句

show create materialized view store_amt on sales_records;

4、查询物化视图：用户的查询依旧指定查询sales_records表

select store_id, sum(sale_amt) from sales_records group by store_id;

5、删除物化视图

drop materialized view store_amt on sales_records;

3、使用限制

1）`SQL` 语法限制

a、仅支持单表查询，不支持多表操作

限制：无法使用 JOIN、LATERAL VIEW 等多表操作。
原因：同步物化视图需与基表强一致，多表操作会引入复杂的数据依赖关系，难以保证实时同步的原子性。
替代方案：改用异步物化视图或通过应用层聚合。

-- 错误示例：尝试JOIN多表
CREATE MATERIALIZED VIEW mv_invalid_join
REFRESH SYNC
AS SELECT o.user_id, u.age, SUM(o.amount) 
   FROM orders o JOIN users u ON o.user_id = u.id  -- 报错：不支持JOIN
   GROUP BY o.user_id, u.age;

b、不支持 `HAVING`、`LIMIT` 子句

限制：HAVING 用于过滤聚合结果，LIMIT 限制返回行数，两者均需在查询时动态计算，无法预存储。

示例：

-- 错误示例：使用HAVING
CREATE MATERIALIZED VIEW mv_invalid_having
REFRESH SYNC
AS SELECT user_id, SUM(amount) AS total 
   FROM orders 
   GROUP BY user_id 
   HAVING total > 1000;  -- 报错：不支持HAVING

替代方案：在查询时添加 HAVING，条件，由优化器匹配物化视图：

-- 正确：查询时过滤，物化视图仍可加速
SELECT user_id, total 
FROM (SELECT user_id, SUM(amount) AS total FROM orders GROUP BY user_id) t 
WHERE total > 1000;

c、`SELECT` 列表限制

限制：
- 不能包含自增列、常量、重复表达式。
- 不支持窗口函数（如 ROW_NUMBER()）。
- 聚合函数必须是根表达式（如 SUM(a+1) 允许，SUM(a)+1 不允许）。

示例：

-- 错误示例1：包含自增列和常量
CREATE MATERIALIZED VIEW mv_invalid_columns
REFRESH SYNC
AS SELECT id, amount, 1 AS const_val, id+1 AS auto_inc  -- 报错：不支持自增列和常量
   FROM orders;
   
-- 错误示例2：聚合函数嵌套非聚合表达式
CREATE MATERIALIZED VIEW mv_invalid_agg
REFRESH SYNC
AS SELECT user_id, SUM(amount) + 1 AS total_plus_one  -- 报错：聚合函数后不能有非聚合表达式
   FROM orders 
   GROUP BY user_id;

替代方案：在查询时计算派生列：

-- 正确：查询时添加常量列
SELECT user_id, total, 1 AS const_val 
FROM (SELECT user_id, SUM(amount) AS total FROM orders GROUP BY user_id) t;

2）查询方式限制

a、不能直接查询同步物化视图

限制：同步物化视图仅用于查询改写（优化器自动匹配），无法通过 SELECT * FROM mv_sync 直接访问。
原因：直接查询需维护两套查询接口，增加复杂性且易引发数据不一致。

示例：

-- 创建同步物化视图
CREATE MATERIALIZED VIEW mv_sync_orders
REFRESH SYNC
AS SELECT user_id, SUM(amount) AS total FROM orders GROUP BY user_id;
   
-- 错误示例：尝试直接查询
SELECT * FROM mv_sync_orders;  -- 报错：不允许直接查询

正确用法：查询基表或兼容 SQL，由优化器自动改写：

-- 优化器自动匹配物化视图
SELECT user_id, SUM(amount) FROM orders GROUP BY user_id;

3）数据操作限制

a、删除操作受限

限制：若删除条件列存在于物化视图中，需先删除物化视图才能操作基表。
原因：同步物化视图与基表强绑定，删除数据需同步更新物化视图，否则会导致数据不一致。

示例：

-- 创建表和物化视图
CREATE TABLE orders (id INT, user_id INT, amount DECIMAL(10,2));
CREATE MATERIALIZED VIEW mv_sync_user_orders
REFRESH SYNC
AS SELECT user_id, COUNT(*) AS order_count FROM orders GROUP BY user_id;
   
-- 错误示例：尝试删除物化视图中的列
DELETE FROM orders WHERE user_id = 1001;  -- 若mv_sync_user_orders存在，此操作可能被阻塞

解决方案：

-- 步骤1：删除物化视图
DROP MATERIALIZED VIEW mv_sync_user_orders;
   
-- 步骤2：执行删除操作
DELETE FROM orders WHERE user_id = 1001;
   
-- 步骤3：重新创建物化视图（可选）
CREATE MATERIALIZED VIEW mv_sync_user_orders ...;

b、 `Unique` `Key` 模型无法聚合

限制：在 Unique Key 模型上，物化视图仅能调整列顺序，无法实现聚合（如 SUM、AVG）。
原因：Unique Key 模型要求每行数据唯一，聚合会破坏唯一性约束。

示例：

-- 创建Unique Key表
CREATE TABLE sales_unique (
  sale_date DATE,
  product_id INT,
  amount DECIMAL(10,2),
  UNIQUE KEY(sale_date, product_id)
) DISTRIBUTED BY HASH(product_id);
   
-- 错误示例：尝试聚合
CREATE MATERIALIZED VIEW mv_invalid_agg_unique
REFRESH SYNC
AS SELECT sale_date, SUM(amount) AS total_sales  -- 报错：Unique Key模型不支持聚合
   FROM sales_unique 
   GROUP BY sale_date;

替代方案：改用 Aggregate Key 模型或通过异步物化视图实现聚合。

4）性能限制

a、单表过多物化视图影响导入效率

限制：每张表的物化视图会同步更新，导入时需逐个刷新，导致延迟增加。

示例：

-- 创建10个物化视图（模拟过度设计）
CREATE TABLE large_table (id INT, col1 INT, col2 INT, ..., col100 INT);
-- 假设为每列创建物化视图（实际场景中极少见）
CREATE MATERIALIZED VIEW mv_sync_col1 REFRESH SYNC AS SELECT id, col1 FROM large_table;
CREATE MATERIALIZED VIEW mv_sync_col2 REFRESH SYNC AS SELECT id, col2 FROM large_table;
-- ... 共10个
   
-- 导入数据时性能下降
INSERT INTO large_table VALUES (1, 1, 2, ..., 100);  -- 需同步更新10个物化视图

优化建议：

控制单表物化视图数量（建议 < 5个）。
合并相似查询到单个物化视图（如多列聚合）。

三、异步物化视图

异步物化视图是一种与基表数据保持 最终一致性 的预计算数据结构，通过定义好的 SELECT 语句预先计算并存储聚合、过滤或关联后的数据集。其本质是将复杂查询结果缓存为独立表，以可控延迟换取更高吞吐，适用于对数据实时性要求不高的分析场景。

默认行为：创建时 不自动同步数据，物化视图初始为空，需通过手动刷新或定时任务填充数据。
原理：异步物化视图通过后台任务异步处理数据变更，创建时仅定义计算逻辑，不触发实际计算。
刷新机制 与同步物化视图所采用的实时增量刷新不同，异步物化视图提供了更为灵活的刷新选项
- 全量刷新： 在此模式下，系统会重新计算物化视图定义 SQL 所涉及的所有数据，并将结果完整地写入物化视图。此过程确保了物化视图中的数据与基表数据保持一致，但可能会消耗更多的计算资源和时间。
- 分区增量刷新： 当物化视图的基表分区数据发生变化时，系统能够智能地识别出这些变化，并仅针对受影响的分区进行刷新。这种机制显著降低了刷新物化视图所需的计算资源和时间，同时保证了数据的最终一致性。
- 手动触发：支持按需手动刷新（如REFRESH MATERIALIZED VIEW命令）。

1、适用场景

1）离线数据分析与报表

场景描述：每日/每周生成的固定报表（如销售日报、用户行为分析），对数据实时性要求低（容忍分钟级延迟）。

优化方案：

创建按天分区的物化视图，夜间全量刷新，白天查询直接读取缓存。

示例：

CREATE MATERIALIZED VIEW daily_sales_mv 
REFRESH AUTO 
PROPERTIES ("refresh_schedule" = "0 0 * * *") -- 每天0点刷新
AS 
SELECT date_trunc('day', order_time) AS dt, 
       product_id, 
       SUM(amount) AS total_sales 
FROM orders 
GROUP BY dt, product_id;

2）低频复杂查询加速

场景描述：每周运行一次的用户画像分析（涉及多表 JOIN、窗口函数），每次查询耗时较长。

优化方案：

将中间结果缓存为物化视图，减少重复计算。

示例：

CREATE MATERIALIZED VIEW user_profile_mv 
REFRESH AUTO 
PROPERTIES ("refresh_interval" = "7d") -- 每周刷新一次
AS 
SELECT user_id, 
       COUNT(DISTINCT order_id) AS order_count,
       AVG(amount) AS avg_order_amount
FROM orders 
GROUP BY user_id;

3）数据仓库与湖仓集成

场景描述：查询 Hive / Iceberg 等外部表时网络传输慢，需将热点数据缓存在 Doris 中。

优化方案：

创建异步物化视图定期同步外部表数据，平衡数据新鲜度与查询性能。

示例：

CREATE MATERIALIZED VIEW hive_cache_mv 
REFRESH INTERVAL '6h' 
PROPERTIES (
  "source_catalog" = "hive_catalog",
  "source_table" = "hive_db.user_info"
) 
AS 
SELECT user_id, age, gender FROM hive_catalog.hive_db.user_info;

4）批量数据处理管道

场景描述：ETL 流程中需对原始数据进行预聚合（如日志清洗、指标计算），后续分析直接基于物化视图。

优化方案：

通过物化视图封装 ETL 逻辑，减少分析查询的复杂度。

示例：

-- 原始日志表
CREATE TABLE raw_logs (
  log_time DATETIME,
  user_id INT,
  action VARCHAR(50)
);
     
-- 物化视图预聚合用户行为
CREATE MATERIALIZED VIEW user_action_stats_mv 
REFRESH AUTO 
PROPERTIES ("refresh_interval" = "1h") -- 每小时刷新
AS 
SELECT user_id, 
       action, 
       COUNT(*) AS action_count 
FROM raw_logs 
GROUP BY user_id, action;

2、使用限制

1）数据一致性延迟

最终一致性：异步物化视图与基表的数据可能存在秒级到小时级延迟（取决于刷新策略），无法保证实时强一致。
- 示例：若刷新间隔为5分钟，基表在10:00:00插入数据，物化视图最早在10:00:05才能体现。
冲突风险：若基表数据频繁变更（如高频UPDATE），异步刷新可能导致数据短暂不一致，影响分析结果准确性。

2）刷新策略复杂度高

依赖后台任务：需通过 REFRESH 命令或定时任务（如Cron）触发刷新，需额外维护刷新逻辑。
- 全量刷新：重新计算整个物化视图，资源消耗大，适合数据量小或周期性刷新的场景。
- 增量刷新：仅处理基表变更部分，但需物化视图定义包含变更跟踪列（如时间戳、版本号），否则无法实现。
失败重试：若刷新任务失败（如网络中断），需手动或通过监控系统触发重试，否则物化视图数据会过期。

3）查询改写限制

不支持动态函数：若查询包含物化视图未定义的函数（如 NOW()、RAND()），无法改写为物化视图。
- 错误示例：
```
-- 假设物化视图不包含`create_time`列
SELECT user_id, NOW() FROM user_behavior_mv; -- 报错：无法改写
```
复杂查询可能失效：多表 JOIN、嵌套聚合等复杂查询可能因物化视图定义不匹配而无法改写，需通过 EXPLAIN 验证。

4. 存储与资源开销

额外存储成本：物化视图会存储一份预计算数据，若基表数据量大，可能显著增加存储占用。
- 监控建议：通过SHOW DATA FROM mv_name 查看物化视图存储大小，避免无限制增长。
后台任务资源竞争：增量刷新需扫描基表变更日志（如 Binlog ），可能占用较多 CPU 和内存，尤其在基表写入频繁时。

ContactAuthor

一、物化视图概览

1、与普通视图的本质区别

2、Doris 物化视图的优势

3、物化视图的使用场景

1）查询加速（OLAP 场景）

2）轻量化 ETL（数据分层）

3）湖仓一体（外部数据加速）

4、物化视图的分类与策略

1） 按数据时效性分类

2）按 SQL 模式分类

2）按刷新策略分类

5、问题

1）聚合 和 物化视图

a、选型建议

b、决策树：

2）主键模型的表能用来创建物化视图吗？

二、同步物化视图

1、适用场景

2、使用物化视图

3、使用限制

1）SQL 语法限制

a、仅支持单表查询，不支持多表操作

b、不支持 HAVING、LIMIT 子句

c、SELECT 列表限制

2）查询方式限制

a、不能直接查询同步物化视图

3）数据操作限制

a、 删除操作受限

b、 Unique Key 模型无法聚合

4）性能限制

a、单表过多物化视图影响导入效率

三、异步物化视图

1、适用场景

1）离线数据分析与报表

2）低频复杂查询加速

3）数据仓库与湖仓集成

4）批量数据处理管道

2、使用限制

1）数据一致性延迟

2）刷新策略复杂度高

3）查询改写限制

4. 存储与资源开销

2、`Doris` 物化视图的优势

1）查询加速（`OLAP` 场景）

2）轻量化 `ETL`（数据分层）

1）按数据时效性分类

2）按 `SQL` 模式分类

1）聚合和物化视图

1）`SQL` 语法限制

b、不支持 `HAVING`、`LIMIT` 子句

c、`SELECT` 列表限制

a、删除操作受限

b、 `Unique` `Key` 模型无法聚合