未记录事实核验 · 状态：维护中

记录仅说明核验时间与维护状态，不保证适用于所有环境；使用前请留意正文中的版本、适用条件与风险提示。

warning 适用范围与待维护事项

本文用于物化视图设计与查询优化讨论，不是已验证的可直接执行脚本。未注明Doris版本，同步Rollup与异步物化视图的创建、分区、刷新及查看命令需分别核对；异步刷新不保证实时一致性。已给出的sales_detail缺少store_id、region_id、order_id等后续字段，多张维表及业务表也未提供DDL和数据。老李故事、90%以上CPU降幅及15秒至0.1秒等数字均为原稿未核实的教学示例，不代表实测效果。上线前须补齐表结构、刷新策略及执行计划与基准测试。

Doris物化视图应用 - 查询加速的利器

abstract 📋 本节概览

🎯 学习目标：掌握Doris物化视图的创建与应用技巧
📚 前置知识：10-SQL查询与分析|SQL查询与分析
⏱️ 预计用时：25分钟

以下为原稿未核实的教学假设场景，不代表真实人物或实测成果：性能优化专家老李遇到一个头疼的问题：公司的销售报表查询从原来的几秒钟变成了5分钟，用户投诉不断。仔细分析发现，原因是数据量从百万级增长到了十亿级，每次查询都要做全表聚合。"必须想办法预先计算这些聚合结果，"老李想到了物化视图，"让频繁查询的结果提前算好存起来。"果然，配置物化视图后，查询时间重新回到了秒级。

💡 物化视图的核心原理

物化视图是基于基表预先计算并实际存储的数据视图，它与普通视图的区别在于物化视图实际占用存储空间，而普通视图只是一个查询定义。

物化视图的工作原理：

预计算：基于基表数据预先计算聚合结果
物化存储：将计算结果实际存储在磁盘上
查询匹配：满足查询改写条件时，优化器可选择物化视图，是否命中需通过执行计划确认
数据维护：同步物化视图随基表写入维护；异步物化视图按配置的刷新策略更新，可能存在数据延迟

🔍 物化视图的类型与特点

下文介绍Rollup与聚合视图的应用形式；二者不是互斥的物化视图类型。理解维护机制时，应区分同步物化视图与异步物化视图，具体能力和语法须以目标Doris版本为准：

1. Rollup物化视图

-- 创建Rollup物化视图
ALTER TABLE sales_detail ADD ROLLUP sales_by_date(
    sale_time, 
    product_id
);

特点：

列裁剪：仅包含部分列
列重排序：调整列顺序以优化查询
自动更新：随基表数据同步更新
查询匹配：满足改写条件时，优化器可选择Rollup，需用EXPLAIN确认是否命中

2. 聚合物化视图

-- 创建聚合物化视图
CREATE MATERIALIZED VIEW product_sales_mv
DISTRIBUTED BY HASH(product_id) BUCKETS 10
REFRESH ASYNC
AS SELECT 
    product_id,
    DATE_FORMAT(sale_time, '%Y-%m-%d') AS sale_date,
    SUM(quantity) AS total_quantity,
    SUM(amount) AS total_amount,
    COUNT(DISTINCT user_id) AS buyer_count
FROM sales_detail
GROUP BY product_id, sale_date;

特点：

预聚合：预先计算聚合结果
降维：减少维度，提高查询效率
支持复杂聚合：COUNT、SUM、MIN、MAX、AVG等
数据刷新：异步物化视图按配置的刷新策略更新，不保证基表变更后立即可见

🚀 物化视图的创建与管理

创建物化视图

-- 基表定义
CREATE TABLE sales_detail (
    sale_id BIGINT,
    user_id BIGINT,
    product_id BIGINT,
    sale_time DATETIME,
    quantity INT,
    amount DECIMAL(15, 2)
) DISTRIBUTED BY HASH(sale_id) BUCKETS 16;

-- 创建简单列裁剪的Rollup
ALTER TABLE sales_detail ADD ROLLUP sales_id_product(
    sale_id,
    product_id,
    amount
);

-- 创建聚合物化视图
CREATE MATERIALIZED VIEW daily_product_sales
DISTRIBUTED BY HASH(product_id) BUCKETS 10
REFRESH ASYNC
AS SELECT 
    product_id,
    DATE_FORMAT(sale_time, '%Y-%m-%d') AS sale_date,
    SUM(quantity) AS total_quantity,
    SUM(amount) AS total_amount,
    COUNT(DISTINCT user_id) AS buyer_count
FROM sales_detail
GROUP BY product_id, sale_date;

-- 创建多维聚合物化视图
CREATE MATERIALIZED VIEW region_category_sales
DISTRIBUTED BY HASH(region_id, category_id) BUCKETS 10
REFRESH ASYNC
AS SELECT 
    region_id,
    category_id,
    SUM(quantity) AS total_quantity,
    SUM(amount) AS total_amount
FROM sales_detail
JOIN dim_product ON sales_detail.product_id = dim_product.product_id
JOIN dim_region ON sales_detail.store_id = dim_region.store_id
GROUP BY region_id, category_id;

查看物化视图

-- 查看表的所有物化视图
SHOW MATERIALIZED VIEW ON sales_detail;

-- 查看物化视图详细信息
DESC MATERIALIZED VIEW daily_product_sales;

-- 查看物化视图创建语句
SHOW CREATE MATERIALIZED VIEW daily_product_sales;

删除物化视图

-- 删除Rollup
ALTER TABLE sales_detail DROP ROLLUP sales_id_product;

-- 删除聚合物化视图
DROP MATERIALIZED VIEW daily_product_sales;

📊 物化视图应用场景

1. 高频聚合查询加速

场景：电商平台每天需要统计各类商品的销售情况

-- 创建物化视图
CREATE MATERIALIZED VIEW product_daily_sales
DISTRIBUTED BY HASH(product_id) BUCKETS 10
REFRESH ASYNC
AS SELECT 
    product_id,
    DATE_FORMAT(sale_time, '%Y-%m-%d') AS sale_date,
    SUM(amount) AS total_sales,
    COUNT(DISTINCT order_id) AS order_count,
    COUNT(DISTINCT user_id) AS buyer_count
FROM order_detail
GROUP BY product_id, sale_date;

-- 原始查询（需要全表扫描和聚合计算）
SELECT 
    product_id,
    DATE_FORMAT(sale_time, '%Y-%m-%d') AS sale_date,
    SUM(amount) AS total_sales
FROM order_detail
WHERE DATE_FORMAT(sale_time, '%Y-%m-%d') = '2023-06-01'
GROUP BY product_id, sale_date;

-- 使用物化视图后（直接从物化视图读取预计算结果）
SELECT 
    product_id,
    sale_date,
    total_sales
FROM product_daily_sales
WHERE sale_date = '2023-06-01';

性能提升（原稿未核实的教学示例）：假设查询时间从秒级降至毫秒级，CPU使用率降低90%以上；这些数字未提供实测依据，实际效果需在目标环境中评估

2. 多维分析优化

场景：需要从不同维度分析用户行为数据

-- 创建多维物化视图
CREATE MATERIALIZED VIEW user_behavior_stats
DISTRIBUTED BY HASH(behavior_date, region_id) BUCKETS 10
REFRESH ASYNC
AS SELECT 
    DATE_FORMAT(behavior_time, '%Y-%m-%d') AS behavior_date,
    region_id,
    behavior_type,
    COUNT(*) AS behavior_count,
    COUNT(DISTINCT user_id) AS user_count
FROM user_behavior
GROUP BY behavior_date, region_id, behavior_type;

-- 多维分析查询
SELECT 
    behavior_date,
    region_id,
    behavior_type,
    behavior_count,
    user_count
FROM user_behavior_stats
WHERE behavior_date BETWEEN '2023-06-01' AND '2023-06-07'
  AND region_id IN (1, 2, 3)
ORDER BY behavior_date, region_id, behavior_count DESC;

3. 实时报表加速

场景：需要展示各区域销售情况的仪表盘；本例采用异步物化视图，数据时效取决于刷新策略与任务完成时间，不保证实时一致性

-- 创建实时报表物化视图
CREATE MATERIALIZED VIEW realtime_sales_dashboard
DISTRIBUTED BY HASH(region_id) BUCKETS 10
REFRESH ASYNC
AS SELECT 
    region_id,
    DATE_FORMAT(sale_time, '%Y-%m-%d %H:00:00') AS sale_hour,
    SUM(amount) AS hourly_sales,
    COUNT(DISTINCT order_id) AS order_count
FROM sales_detail
GROUP BY region_id, sale_hour;

-- 实时仪表盘查询
SELECT 
    r.region_name,
    s.sale_hour,
    s.hourly_sales,
    s.order_count
FROM realtime_sales_dashboard s
JOIN dim_region r ON s.region_id = r.region_id
WHERE s.sale_hour >= DATE_FORMAT(DATE_SUB(NOW(), INTERVAL 24 HOUR), '%Y-%m-%d %H:00:00')
ORDER BY s.region_id, s.sale_hour;

🔧 物化视图设计最佳实践

1. 选择合适的列和聚合

-- 好的设计：针对高频查询模式
CREATE MATERIALIZED VIEW good_mv
DISTRIBUTED BY HASH(product_category) BUCKETS 10
REFRESH ASYNC
AS SELECT 
    product_category,
    region_id,
    DATE_FORMAT(sale_time, '%Y-%m-%d') AS sale_date,
    SUM(amount) AS total_amount
FROM sales_detail
JOIN dim_product ON sales_detail.product_id = dim_product.product_id
GROUP BY product_category, region_id, sale_date;

-- 不好的设计：包含过多维度，使用率低
CREATE MATERIALIZED VIEW bad_mv
DISTRIBUTED BY HASH(product_id) BUCKETS 10
REFRESH ASYNC
AS SELECT 
    product_id,
    store_id,
    user_id,
    payment_type,
    DATE_FORMAT(sale_time, '%Y-%m-%d %H:%i:%s') AS sale_time,
    SUM(amount) AS total_amount
FROM sales_detail
GROUP BY product_id, store_id, user_id, payment_type, sale_time;

2. 分区与分桶设计

-- 合理的分区分桶设计
CREATE MATERIALIZED VIEW well_distributed_mv
PARTITION BY RANGE(sale_date) (
    PARTITION p20230601 VALUES [('2023-06-01'), ('2023-06-02')),
    PARTITION p20230602 VALUES [('2023-06-02'), ('2023-06-03'))
)
DISTRIBUTED BY HASH(product_id) BUCKETS 10
REFRESH ASYNC
AS SELECT 
    product_id,
    DATE_FORMAT(sale_time, '%Y-%m-%d') AS sale_date,
    SUM(amount) AS total_amount
FROM sales_detail
GROUP BY product_id, sale_date;

3. 刷新策略选择

-- 异步刷新（适合大多数场景）
CREATE MATERIALIZED VIEW async_refresh_mv
DISTRIBUTED BY HASH(product_id) BUCKETS 10
REFRESH ASYNC
AS SELECT 
    product_id,
    COUNT(*) AS sale_count
FROM sales_detail
GROUP BY product_id;

-- 手动刷新（适合特定时间点需要最新数据的场景）
CREATE MATERIALIZED VIEW manual_refresh_mv
DISTRIBUTED BY HASH(product_id) BUCKETS 10
REFRESH MANUAL
AS SELECT 
    product_id,
    COUNT(*) AS sale_count
FROM sales_detail
GROUP BY product_id;

-- 手动触发刷新
REFRESH MATERIALIZED VIEW manual_refresh_mv;

⚠️ 常见问题与解决方案

1. 物化视图不被使用

问题：创建了物化视图但查询没有使用它
解决方案：
- 检查查询条件是否与物化视图匹配
- 使用EXPLAIN查看查询计划
- 调整物化视图设计以匹配查询模式

-- 使用EXPLAIN查看查询是否使用物化视图
EXPLAIN SELECT 
    product_id, 
    SUM(amount) AS total_amount
FROM sales_detail
GROUP BY product_id;

2. 物化视图更新延迟

问题：物化视图数据不是最新的
解决方案：
- 检查刷新策略（ASYNC vs MANUAL）
- 手动触发刷新
- 监控刷新任务状态

-- 手动刷新物化视图
REFRESH MATERIALIZED VIEW product_sales_mv;

-- 查看刷新任务状态
SHOW MATERIALIZED VIEW REFRESH STATUS WHERE TableName = 'sales_detail';

3. 物化视图占用空间过大

问题：物化视图占用过多存储空间
解决方案：
- 减少物化视图数量
- 优化物化视图设计，减少冗余
- 定期清理不常用的物化视图

-- 查看物化视图占用空间
SHOW DATA FROM sales_detail;

-- 删除不常用的物化视图
DROP MATERIALIZED VIEW rarely_used_mv;

📈 性能提升案例分析

本节为原稿未核实的教学假设：SQL注释中的15秒、0.1秒及下述150倍均不是已验证的实测结果，缺少测试环境、数据规模与测量记录。

案例1：电商平台日报表查询优化

优化前：

-- 原始查询（执行时间：15秒）
SELECT 
    DATE_FORMAT(order_time, '%Y-%m-%d') AS order_date,
    product_category,
    COUNT(DISTINCT order_id) AS order_count,
    COUNT(DISTINCT user_id) AS buyer_count,
    SUM(amount) AS total_amount
FROM orders
JOIN products ON orders.product_id = products.product_id
WHERE order_time >= '2023-06-01' AND order_time < '2023-07-01'
GROUP BY order_date, product_category;

优化后：

-- 创建物化视图
CREATE MATERIALIZED VIEW daily_category_stats
DISTRIBUTED BY HASH(order_date, product_category) BUCKETS 10
REFRESH ASYNC
AS SELECT 
    DATE_FORMAT(order_time, '%Y-%m-%d') AS order_date,
    product_category,
    COUNT(DISTINCT order_id) AS order_count,
    COUNT(DISTINCT user_id) AS buyer_count,
    SUM(amount) AS total_amount
FROM orders
JOIN products ON orders.product_id = products.product_id
GROUP BY order_date, product_category;

-- 优化后查询（执行时间：0.1秒）
SELECT 
    order_date,
    product_category,
    order_count,
    buyer_count,
    total_amount
FROM daily_category_stats
WHERE order_date BETWEEN '2023-06-01' AND '2023-06-30';

性能提升（原稿未核实的教学示例）：假设查询时间从15秒降至0.1秒，耗时之比为150倍；不代表已验证的实际收益

✅ 掌握检查

[ ] 理解物化视图的核心原理和优势
[ ] 能根据查询模式设计合适的物化视图
[ ] 掌握物化视图的创建、查看和管理方法
[ ] 了解物化视图的常见问题及解决方案
[ ] 能评估物化视图对查询性能的提升效果

🔗 学习连接

前置知识: SQL查询与 相关知识: 数据模型设计