# 使用物化视图加速查询
#### 场景介绍
高频复杂聚合查询加速、多表关联加速、实时风控、广告投放分析、物联网设备监控等场景下，报表、看板、榜单类业务每次刷新都执行几十条聚合查询，反复扫描亿级明细表并做多表关联。数据量越大，重复计算越频繁，数据库CPU与I/O被持续消耗，查询延迟从毫秒级劣化到秒级甚至分钟级。
DWS的物化视图（Materialized View）功能支持将查询结果预先计算并物理化存储为实体表，查询时直接读取预存数据。查询重写机制自动将匹配的SQL路由到物化视图，实现透明加速，无需修改业务代码。
物化视图完整的功能及使用介绍可参考[DWS物化视图](https://support.huaweicloud.com/devg-911-dws/dws_04_1413.html)。
#### 核心原理
- 数据预计算，创建、刷新时执行基础查询（可含关联、聚合、过滤），结果存储为物理表，与基础表解耦。
- 数据一致性，基表增删改后，物化视图预存数据过期，通过刷新机制同步，平衡查询性能与数据实时性。
- 透明加速，用户SQL与物化视图SQL匹配时，自动从物化视图读取数据。
 
#### 功能优势
- 失效轻量化，基表数据变化后触发物化视图失效，不影响基表并发写入，不重复失效。
- 锁轻量化，基表写入不阻塞刷新，刷新也不阻塞业务查询。
- 可靠失效，CN故障不影响物化视图的失效和刷新。
- 合理级联锁，对失效、刷新和基表DDL，进行统一设计，任意交叉依赖和嵌套，无死锁。
- 自动级联刷新，自底向上级联刷新每一个物化视图，全链路保障数据实时一致。
 
#### 使用物化视图的建议
物化视图本质上通过"相似归一"减少重复计算。建议从TopSQL分析历史SQL，归纳相似计算：
- 通过"unique_sql_id"统计同类语句使用频率，将公共部分提取为物化视图。
- 按大表"query\`"字段like该表上的SQL，分析近似计算。
物化视图设计得越基础、越简单、复用频率越高，收益越大。
#### 物化视图的存储格式与分布方式
表1物化视图的存储格式与分布方式 
| 选项   | 场景     | 推荐                                                   |
|:---|:---|:---|
| 存储格式  | 高频点查    | 行存 + 索引加速                                            |
| 存储格式  | 批量分析      | 列存（推荐WITH(orientation=COLUMN, enable_hstore_opt=ON)） |
| 分布方式 | 10万条以下  | REPLICATION复制表                                         |
| 分布方式 | 10万条以上   | HASH分布                                               |
| 分布方式 | 无均匀hash键 | ROUNDROBIN                                           |
   
#### 聚合物化视图
场景：按地区、品类统计订单等聚合查询重复执行，每次全表扫描，响应慢、负载高。
方案：创建单表聚合物化视图，预计算聚合结果。
```
CREATE MATERIALIZED VIEW mv_order_region_stats
AS
SELECT
    region,                           -- 聚合维度：地区 
    COUNT(*) AS order_count,          -- 指标1：该地区订单总数
    SUM(order_amount) AS total_amount -- 指标2：该地区订单总金额
FROM t_order
GROUP BY region;                      -- 单表聚合，无多表关联
```
#### JOIN物化视图
场景：订单事实表与用户维度表关联后聚合，涉及大表JOIN，查询耗时高。
方案：创建JOIN物化视图，预计算关联聚合结果。
```
CREATE MATERIALIZED VIEW mv_user_order_stats
AS
SELECT
    u.user_level,
    u.region,
    COUNT(o.order_id)  AS order_count,
    SUM(o.order_amount) AS total_amount
FROM t_order o
INNER JOIN t_user u ON o.user_id = u.user_id
GROUP BY u.user_level, u.region;
```
#### 分区物化视图
场景：亿级事实表按时间分区，查询聚焦最近时段，但每次仍需扫描大量历史分区。
方案：创建与基表分区映射的分区物化视图，基表分区变化时只失效并刷新对应分区。
1. 创建基表。 
   事实表。
   ```
   CREATE TABLE fact_table (start_time TIMESTAMPTZ NOT NULL, id INT)
   PARTITION BY RANGE(start_time) (
       PARTITION p1 VALUES LESS THAN ('2025-01-01'),
       PARTITION p2 VALUES LESS THAN ('2025-02-01'),
       PARTITION p3 VALUES LESS THAN ('2025-03-01'),
       PARTITION p4 VALUES LESS THAN ('2025-04-01'),
       PARTITION p5 VALUES LESS THAN ('2025-05-01'),
       PARTITION p6 VALUES LESS THAN ('2025-06-01')
   );
   ```
   维度表。
   ```
   CREATE TABLE dimen_table (start_time TIMESTAMPTZ NOT NULL, id INT);
   ```
   
   
2. 等比例方式，创建与事实表分区完全一样的分区物化视图。 
   ```
   CREATE MATERIALIZED VIEW level_normal ENABLE QUERY REWRITE
   DISTRIBUTE BY HASH(start_time)
   PARTITION BY start_time
   AS SELECT fact_table.start_time, dimen_table.id
   FROM fact_table JOIN dimen_table ON fact_table.id = dimen_table.id;
   ```
   
   
3. 创建分区物化视图。 
   按天上卷方式，根据事实表start_time分区键，创建分区物化视图。
   ```
   CREATE MATERIALIZED VIEW level_day enable query rewrite DISTRIBUTE BY HASH(start_time) PARTITION BY date_trunc('day', start_time)
   AS SELECT fact_table.start_time, dimen_table.id FROM fact_table JOIN dimen_table ON fact_table.id = dimen_table.id;
   ```
   按月上卷方式，根据事实表start_time分区键，创建分区物化视图。
   ```
   CREATE MATERIALIZED VIEW level_month enable query rewrite DISTRIBUTE BY HASH(start_time) PARTITION BY date_trunc('month', start_time)
   AS SELECT fact_table.start_time, dimen_table.id FROM fact_table JOIN dimen_table ON fact_table.id = dimen_table.id;
   ```
   按年上卷方式，根据事实表start_time分区键，创建分区物化视图。
   ```
   CREATE MATERIALIZED VIEW level_year enable query rewrite DISTRIBUTE BY HASH(start_time) PARTITION BY date_trunc('year', start_time) AS SELECT fact_table.start_time, dimen_table.id FROM fact_table JOIN dimen_table ON fact_table.id = dimen_table.id;
   ```
   
   
4. 刷新时自动识别失效分区，无需手动指定。设置mv_refresh_parts_per_trans，可实现分区由近及远分批刷新，保证最新分区先刷新。手动与自动刷新不冲突，一方进行时另一方自动跳过，防止重复浪费资源。 
   例如，设置分批提交的物化视图分区个数为10。
   ```
   set mv_refresh_parts_per_trans='10';
   ```
   
   
#### 嵌套物化视图
场景：既需要按地区统计订单，又需要按用户统计订单，直接基于基表分别聚合会产生重复的关联计算。
方案：先创建"地区+用户"细粒度中间层物化视图，再分别按地区和用户聚合，避免基表重复JOIN，减少整体计算量。
1. 创建基表t_user和t_order。 
   ```
   CREATE TABLE t_user (
   user_id     BIGSERIAL PRIMARY KEY,
   user_name   VARCHAR(32) NOT NULL,
   user_level  VARCHAR(10) NOT NULL,
   region      VARCHAR(20) NOT NULL,
   create_time TIMESTAMP NOT NULL DEFAULT NOW()
   );
   CREATE TABLE t_order (
   order_id     BIGSERIAL PRIMARY KEY,
   order_no     VARCHAR(32) NOT NULL,
   user_id      BIGINT NOT NULL,
   order_amount NUMERIC(10,2) NOT NULL,
   create_time  TIMESTAMP NOT NULL DEFAULT NOW()
   );
   ```
   
   
2. 创建第一层物化视图，按照用户等级和地区，计算订单总数和总金额。 
   ```
   CREATE MATERIALIZED VIEW mv_user_order_stats
   AS
   SELECT
   u.user_level,
   u.region,
   COUNT(o.order_id)   AS order_count,
   SUM(o.order_amount) AS total_amount
   FROM t_order o
   INNER JOIN t_user u ON o.user_id = u.user_id
   GROUP BY u.user_level, u.region;
   ```
   
   
3. 创建第二层物化视图，按照用户等级全局聚合。 
   ```
   CREATE MATERIALIZED VIEW mv_user_level_global_stats
   AS
   SELECT
       user_level,
       SUM(order_count) AS total_order_count,
       SUM(total_amount) AS total_order_amount
   FROM mv_user_order_stats
   GROUP BY user_level;
   ```
   
   
4. 创建第二层物化视图，按照地区全局聚合。 
   ```
   CREATE MATERIALIZED VIEW mv_region_global_stats
   AS
   SELECT
       region,
       SUM(order_count) AS total_order_count,
       SUM(total_amount) AS total_order_amount
   FROM mv_user_order_stats
   GROUP BY region;
   ```
   
   
 
#### 应用示例1：广告行业精准投流
问题：海量日志数据的高频多维度聚合查询。广告曝光、点击、转化日志单日可达亿级，直接基于原始日志表进行（广告主 / 渠道 / 时段 / 地域）多维度统计，会触发全表扫描，查询耗时长达分钟级，无法支撑实时投放优化、报表展示等业务需求。
方案：创建物化视图，提前预计算 + 物理存储聚合结果，将多维度统计查询的响应时间从分钟级降至毫秒级，完美适配广告行业"准实时统计 + 高频报表"的核心需求。
**场景示例表。**
创建广告曝光点击转化日志表（核心基表，亿级数据量）
```
CREATE TABLE ad_imp_click_log (
    log_id        BIGSERIAL PRIMARY KEY,     -- 日志唯一ID
    ad_id         BIGINT NOT NULL,           -- 广告ID
    advertiser_id BIGINT NOT NULL,           -- 广告主ID
    channel_id    BIGINT NOT NULL,           -- 投放渠道ID（如抖音/微信/头条）
    region        VARCHAR(20) NOT NULL,      -- 投放地域（省/市）
    imp_time      TIMESTAMP NOT NULL,        -- 曝光时间
    click_time    TIMESTAMP NULL,            -- 点击时间（NULL表示未点击）
    convert_time  TIMESTAMP NULL,            -- 转化时间（NULL表示未转化）
    cost          DECIMAL(10,2) NOT NULL,    -- 单条曝光消耗（元）
    device_type   VARCHAR(10) NOT NULL       -- 设备类型（安卓/ios/pc）
);
```
**场景一：广告主实时查看广告指标（单表聚合物化视图）**
```
CREATE MATERIALIZED VIEW mv_advertiser_daily_stats
AS
SELECT
    advertiser_id,                      -- 广告主ID
    DATE(imp_time) AS stat_date,        -- 统计日期
    COUNT(*) AS imp_count,              -- 曝光量
    COUNT(click_time) AS click_count,   -- 点击量
    COUNT(convert_time) AS convert_count, -- 转化量
    SUM(cost) AS total_cost,            -- 总消耗（元）
    ROUND(COUNT(click_time)::FLOAT / NULLIF(COUNT(*), 0) * 100, 2) AS ctr,  -- 点击率（避免除以0，用NULLIF）
    ROUND(COUNT(convert_time)::FLOAT / NULLIF(COUNT(click_time), 0) * 100, 2) AS cvr  -- 转化率（点击→转化）
FROM ad_imp_click_log
GROUP BY advertiser_id, DATE(imp_time); -- 聚合维度：广告主+日期
```
使用物化视图。广告主（ID=10）查询近 7 天投放效果，直接从物化视图获取，无需扫描亿级日志。
```
SELECT
    stat_date,
    imp_count,
    click_count,
    convert_count,
    total_cost,
    ctr || '%' AS ctr,
    cvr || '%' AS cvr
FROM mv_advertiser_daily_stats
WHERE advertiser_id = 10 AND stat_date >= CURRENT_DATE - 7
ORDER BY stat_date DESC;
```
**场景二：广告运营监控投放效果优化决策（JOIN物化视图）**
创建渠道信息表（维度表）
```
CREATE TABLE ad_channel_info (
    channel_id BIGINT PRIMARY KEY,      -- 渠道ID
    channel_name VARCHAR(50) NOT NULL,  -- 渠道名称（如抖音/微信/头条）
    channel_type VARCHAR(20) NOT NULL   -- 渠道类型（信息流/搜索/社交）
);
```
渠道-地域-设备多维度效果物化视图（双表JOIN+多维度聚合）
```
CREATE MATERIALIZED VIEW mv_channel_region_device_stats
AS
SELECT
    c.channel_id,
    c.channel_name,
    c.channel_type,
    l.region,
    l.device_type,
    DATE(l.imp_time) AS stat_date,
    COUNT(*) AS imp_count,
    COUNT(l.click_time) AS click_count,
    SUM(l.cost) AS total_cost
FROM ad_imp_click_log l
INNER JOIN ad_channel_info c ON l.channel_id = c.channel_id
GROUP BY c.channel_id, c.channel_name, c.channel_type, l.region, l.device_type, DATE(l.imp_time);
```
运营查询抖音渠道在北京市的安卓设备近3天投放效果：
```
SELECT
    stat_date,
    imp_count,
    click_count,
    total_cost,
    ROUND(click_count::FLOAT / imp_count * 100, 2) AS ctr
FROM mv_channel_region_device_stats
WHERE channel_name = '抖音' AND region = '北京' AND device_type = '安卓' AND stat_date >= CURRENT_DATE - 3
ORDER BY stat_date DESC;
```
**场景三：广告投放周度趋势分析（嵌套物化视图）**
广告主-渠道周度投放趋势物化视图（嵌套视图，基于日统计视图汇总）
```
CREATE MATERIALIZED VIEW mv_advertiser_channel_weekly_stats
AS
SELECT
    a.advertiser_id,
    l.channel_id,
    c.channel_name,
    DATE_TRUNC('week', a.stat_date) AS stat_week, -- 按周聚合（周一为每周第一天）
    SUM(a.imp_count) AS weekly_imp_count,
    SUM(a.click_count) AS weekly_click_count,
    SUM(a.total_cost) AS weekly_total_cost
FROM mv_advertiser_daily_stats a
INNER JOIN ad_imp_click_log l ON a.advertiser_id = l.advertiser_id AND a.stat_date = DATE(l.imp_time)
INNER JOIN ad_channel_info c ON l.channel_id = c.channel_id
GROUP BY a.advertiser_id, l.channel_id, c.channel_name, DATE_TRUNC('week', a.stat_date);
```
广告主（ID=10）查询在抖音渠道近 4 周的投放趋势：
```
SELECT
    TO_CHAR(stat_week, 'YYYY-MM-DD') AS week_start,
    weekly_imp_count,
    weekly_click_count,
    weekly_total_cost
FROM mv_advertiser_channel_weekly_stats
WHERE advertiser_id = 10 AND channel_name = '抖音'
ORDER BY stat_week DESC
LIMIT 4;
```
#### 应用示例2：实时分析用户浏览网站数据
问题：网站需对浏览网站的用户行为数据进行实时分析，分析数据包括：按小时/天统计UV（独立访客数）、PV（页面浏览量）、访问时长、留存率等。直接对日志表反复聚合查询会产生大量重复计算，查询性能低。
方案：使用物化视图的增量刷新及分区物化视图功能，提高查询响应速度。
1. 创建用户行为日志表并插入数据。 
   ```
   CREATE TABLE user_behavior_log (
     user_id BIGINT,
     event_type VARCHAR(50),
     page_url VARCHAR(200),
     duration INT, -- 停留时长(秒)
     event_time TIMESTAMP
   )
   WITH (orientation = COLUMN)
   PARTITION BY RANGE (event_time)
   (
     PARTITION p0 VALUES LESS THAN ('2025-08-01'),
     PARTITION p1 VALUES LESS THAN ('2025-09-01'),
     PARTITION p2 VALUES LESS THAN ('2025-10-01'),
     PARTITION p3 VALUES LESS THAN (MAXVALUE)
   );
   INSERT INTO user_behavior_log (user_id, event_type, page_url, duration, event_time) VALUES
   (1001, 'view',   '/home',         45,  '2025-09-15 09:05:00'),
   (1001, 'click',  '/home',         10,  '2025-09-15 09:06:00'),
   (1002, 'view',   '/product/A',    120, '2025-09-15 09:15:00'),
   (1002, 'view',   '/cart',         75,  '2025-09-15 09:18:00'),
   (1003, 'view',   '/home',         30,  '2025-09-15 10:02:00'),
   (1003, 'purchase','/checkout',    60,  '2025-09-15 10:05:00'),
   (1001, 'view',   '/product/B',    200, '2025-09-16 09:10:00'),
   (1004, 'view',   '/home',         90,  '2025-09-16 09:20:00'),
   (1004, 'click',  '/product/A',    15,  '2025-09-16 09:22:00'),
   (1002, 'view',   '/checkout',     180, '2025-09-16 10:05:00'),
   (1003, 'view',   '/product/A',    55,  '2025-09-17 09:01:00'),
   (1005, 'view',   '/home',         25,  '2025-09-17 09:30:00');
   ```
   
   
2. 小时级统计物化视图，利用分区物化视图进行分区上卷计算。 
   ```
   CREATE MATERIALIZED VIEW mv_user_stats_daily
   REFRESH COMPLETE EVERY (interval '10 min')
   ENABLE QUERY REWRITE
   WITH (orientation = COLUMN)
   PARTITION BY (date_trunc('day', stat_hour))
   AS
   SELECT
     DATE_TRUNC('day', event_time) as stat_hour,
     event_type,
     page_url,
     COUNT(*) as pv, -- 页面访问次数
     COUNT(DISTINCT user_id) as uv, -- 独立访客数
     AVG(duration) as avg_duration, -- 平均停留时长
     COUNT(CASE WHEN duration > 60 THEN 1 END) as long_stay_count -- 停留>1分钟
   FROM user_behavior_log
   GROUP BY stat_hour, event_type, page_url;
   ```
   
   
3. 查询2025-09-15当天各页面PV/UV。 
   ```
   SELECT
       TO_CHAR(stat_hour, 'YYYY-MM-DD') AS stat_date,
       page_url,
       pv,
       uv,
       ROUND(avg_duration, 2) AS avg_duration,
       long_stay_count
   FROM mv_user_stats_daily
   WHERE stat_hour >= '2025-09-15'
     AND stat_hour <  '2025-09-16'
   ORDER BY pv DESC;
   ```
   返回结果如下：
   ![](https://support.huaweicloud.com/bestpractice-dws/figure/zh-cn_image_0000002743147956.png "点击放大")
   
   
4. 留存率统计。 
   1. 首次访问物化视图。
      ```
      CREATE MATERIALIZED VIEW mv_first_visit
      REFRESH COMPLETE EVERY (interval '10 min')
      ENABLE QUERY REWRITE
      AS
      SELECT
        user_id,
        DATE_TRUNC('day', MIN(event_time)) AS first_date
      FROM user_behavior_log
      GROUP BY user_id;
      ```
      
   
   2. 留存明细物化视图（依赖mv_first_visit）。
      ```
      CREATE MATERIALIZED VIEW mv_retention_calc
      REFRESH COMPLETE EVERY (interval '10 min')
      ENABLE QUERY REWRITE
      AS
      SELECT
        fv.first_date,
        (DATE_TRUNC('day', u.event_time) - fv.first_date) AS days_diff,
        COUNT(DISTINCT u.user_id) AS user_count
      FROM user_behavior_log u
      JOIN mv_first_visit fv ON u.user_id = fv.user_id
      GROUP BY fv.first_date,
               (DATE_TRUNC('day', u.event_time) - fv.first_date);
      ```
      
   
   3. 留存率物化视图（依赖mv_retention_calc + mv_first_visit）。
      ```
      CREATE MATERIALIZED VIEW mv_user_retention
      REFRESH COMPLETE EVERY (interval '1 hour')
      ENABLE QUERY REWRITE
      AS
      SELECT
        rc.first_date,
        rc.days_diff,
        rc.user_count,
        ROUND(100.0 * rc.user_count /
          (SELECT COUNT(*) FROM mv_first_visit fv2
           WHERE fv2.first_date = rc.first_date), 2
        ) AS retention_rate
      FROM mv_retention_calc rc;
      ```
      
   
   4. 查询示例：查看各首访日期的留存率。
      ```
      SELECT
          first_date,
          days_diff,
          user_count,
          retention_rate || '%' AS retention_rate
      FROM mv_user_retention
      ORDER BY first_date, days_diff;
      ```
      ![](https://support.huaweicloud.com/bestpractice-dws/figure/zh-cn_image_0000002743145554.png "点击放大")