报表查询太慢，加了物化视图快了20倍——Rollup和物化视图怎么用？

-- 原表：用户行为明细（Duplicate模型）
CREATE TABLE ods_user_event (
    event_time DATETIME,
    user_id BIGINT,
    event_name VARCHAR(128),
    page_id VARCHAR(64),
    device_id VARCHAR(64),
    country VARCHAR(32),
    amount DECIMAL(12,2)
) DUPLICATE KEY(event_time, user_id)
PARTITION BY RANGE(event_time) (...)
DISTRIBUTED BY HASH(user_id) BUCKETS 16;
 
-- 创建Rollup：按天+国家的UV/PV/GMV预聚合
ALTER TABLE ods_user_event ADD ROLLUP rollup_daily_country (
    event_time, country, user_id, amount
);
-- Doris会自动维护这个Rollup
-- 查询 SELECT country, COUNT(DISTINCT user_id), SUM(amount)
-- FROM ods_user_event WHERE event_time = 'xxx' GROUP BY country
-- 会自动命中这个Rollup，速度提升10-20倍

-- 同步物化视图（单表，自动维护，查询自动命中）
CREATE MATERIALIZED VIEW mv_daily_stats AS
SELECT
    DATE(event_time) AS dt,
    country,
    COUNT(DISTINCT user_id) AS uv,
    COUNT(*) AS pv,
    SUM(amount) AS total_amount,
    AVG(amount) AS avg_amount
FROM ods_user_event
GROUP BY DATE(event_time), country;
 
-- 查询时不需要指定物化视图名称
-- Doris自动判断是否能命中物化视图
SELECT dt, country, uv, pv, total_amount
FROM ods_user_event  -- 还是查原表！
WHERE dt = '2024-01-15'
GROUP BY dt, country;
-- Doris自动路由到mv_daily_stats，秒出结果

-- 异步物化视图（Doris 2.1+，支持多表JOIN，更灵活）
CREATE MATERIALIZED VIEW mv_user_gift_stats
BUILD IMMEDIATE       -- 创建后立即构建
REFRESH AUTO           -- 自动刷新
AS
SELECT
    DATE(e.event_time) AS dt,
    u.country,
    u.vip_level,
    COUNT(DISTINCT e.user_id) AS uv,
    SUM(e.amount) AS total_amount
FROM ods_user_event e
JOIN dim_user u ON e.user_id = u.user_id
WHERE e.event_name = 'gift_send'
GROUP BY DATE(e.event_time), u.country, u.vip_level;
 
-- 这个物化视图包含了JOIN + WHERE过滤 + 聚合
-- 查询 '各国各VIP等级的送礼金额' 自动命中，秒出

Rollup vs 物化视图对比

维度	Rollup	同步物化视图	异步物化视图
定义方式	ALTER TABLE ADD ROLLUP	CREATE MATERIALIZED VIEW	CREATE MV ... BUILD/REFRESH
支持表达式	❌ 只能选原表列	✅ 支持DATE()/SUM()等	✅ 支持复杂表达式
支持JOIN	❌	❌	✅ 支持多表JOIN
支持WHERE	❌	❌	✅ 支持过滤条件
维护方式	导入时自动同步	导入时自动同步	定时/手动刷新
查询路由	自动命中	自动命中	自动命中（2.1+）
推荐度	旧版本用	单表聚合推荐	复杂场景推荐

什么时候该加物化视图？——我的判断标准

不要无脑加物化视图，它有存储和写入开销。我在元宇宙社交公司的判断标准：

① 查询频率高：同一种聚合查询每天被执行100次以上（比如实时大盘每分钟刷新一次），值得加物化视图。偶尔跑一次的临时分析不值得。

② 原始数据量大：原表有几千万到几亿行，每次GROUP BY都要全表扫描很慢。如果原表只有几十万行，加不加物化视图差别不大。

③ 聚合维度固定：大盘查的维度基本固定（按天、按国家、按事件类型），不会天天变。如果维度经常变化，物化视图就要频繁重建，得不偿失。

实战经验：从5秒到300ms的优化过程

在某元宇宙社交公司，实时大盘有一个核心查询：查每天每个国家的UV、PV、GMV。原始数据在ods_user_event表，每天5000万条。

优化前：直接查原表做GROUP BY，扫描5000万行，响应时间5-8秒。大盘每分钟自动刷新，每次都要等好几秒，运营同事体验很差。

优化方案：创建同步物化视图mv_daily_stats，包含DATE(event_time)、country、COUNT(DISTINCT user_id)、COUNT(*)、SUM(amount)。

优化后：同样的查询Doris自动路由到物化视图，只需要扫描几百行预聚合数据，响应时间降到200-300ms。大盘刷新体验变得流畅。

额外开销：物化视图大约占用原表5%的存储空间，数据导入延迟增加了约10%（因为要同步更新物化视图）。对于我们的场景完全可以接受。

实战踩坑：物化视图命中失败

有一次我创建了一个物化视图用来加速'按天统计UV'的查询，但发现查询速度没有任何提升。用EXPLAIN看执行计划，发现没有命中物化视图。

-- 物化视图定义
CREATE MATERIALIZED VIEW mv_daily_uv AS
SELECT DATE(event_time) AS dt, COUNT(DISTINCT user_id) AS uv
FROM ods_user_event GROUP BY DATE(event_time);
 
-- 查询（没有命中！）
SELECT DATE(event_time) AS dt, COUNT(DISTINCT user_id) AS uv
FROM ods_user_event
WHERE event_name = 'page_view'  -- 多了一个过滤条件！
GROUP BY DATE(event_time);
 
-- 原因：物化视图没有event_name列，无法做过滤
-- 查询带了WHERE event_name = 'page_view'，物化视图无法满足
 
-- 修复：在物化视图里加上event_name维度
CREATE MATERIALIZED VIEW mv_daily_event_uv AS
SELECT DATE(event_time) AS dt, event_name,
       COUNT(DISTINCT user_id) AS uv
FROM ods_user_event GROUP BY DATE(event_time), event_name;

教训：物化视图能否命中取决于查询SQL能不能从物化视图的预计算结果中得到答案。如果查询带了物化视图没有的列做过滤或分组，就命中不了。创建物化视图前要分析最常用的查询模式，把常用的WHERE和GROUP BY列都包含进去。

面试高频追问

追问一：物化视图会影响写入性能吗？

会。同步物化视图在数据导入时需要同步更新，物化视图越多、越复杂，导入延迟就越高。我的经验是：一张表的同步物化视图不要超过5个，否则导入延迟会明显增加。如果需要更多预计算，考虑用异步物化视图+定时刷新。

追问二：怎么知道查询有没有命中物化视图？

用EXPLAIN命令查看执行计划。如果命中了物化视图，计划里会显示物化视图的名字（比如mv_daily_stats）。如果没命中，显示的是原表名。这是排查物化视图问题的第一步。

追问三：物化视图和在DWS层建汇总表有什么区别？

本质上是一回事——都是预计算。区别在于：物化视图由Doris自动维护（数据导入时同步更新），DWS汇总表需要你写调度任务手动更新。物化视图更省心但灵活性有限，DWS汇总表更灵活但要维护调度逻辑。我的做法是简单的预聚合用物化视图，复杂的ETL逻辑建DWS表。

总结

物化视图面试讲清楚三点：① 原理（预计算，查询自动路由）；② Rollup vs 同步物化视图 vs 异步物化视图的区别和适用场景；③ 什么时候该加（查询频繁+数据量大+维度固定）。踩坑点是物化视图命中条件——查询的WHERE和GROUP BY列必须被物化视图覆盖。能讲出从5秒优化到300ms的实际案例和量化数字，面试官会很满意。

下一篇聊Doris的JOIN优化：两张大表JOIN很慢怎么办？Colocate Join、Bucket Shuffle Join都是什么？

你们Doris用了物化视图吗？效果如何？评论区聊聊～

关注「Wood聊大数据」| 9年大数据老兵，聊技术、聊职场、聊成长