文档首页/ 数据仓库服务 DWS/ 最佳实践/ 性能调优/ 使用物化视图加速查询
更新时间：2026-09-28 GMT+08:00
查看PDF
分享

使用物化视图加速查询

场景介绍

高频复杂聚合查询加速、多表关联加速、实时风控、广告投放分析、物联网设备监控等场景下，报表、看板、榜单类业务每次刷新都执行几十条聚合查询，反复扫描亿级明细表并做多表关联。数据量越大，重复计算越频繁，数据库CPU与I/O被持续消耗，查询延迟从毫秒级劣化到秒级甚至分钟级。

DWS的物化视图（Materialized View）功能支持将查询结果预先计算并物理化存储为实体表，查询时直接读取预存数据。查询重写机制自动将匹配的SQL路由到物化视图，实现透明加速，无需修改业务代码。

物化视图完整的功能及使用介绍可参考DWS物化视图。

核心原理

  • 数据预计算，创建、刷新时执行基础查询（可含关联、聚合、过滤），结果存储为物理表，与基础表解耦。
  • 数据一致性，基表增删改后，物化视图预存数据过期，通过刷新机制同步，平衡查询性能与数据实时性。
  • 透明加速，用户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分区键，创建分区物化视图。

    1
2
    		 
    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分区键，创建分区物化视图。

    1
2
    		 
    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。 

     1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
    		 
    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. 创建第一层物化视图，按照用户等级和地区，计算订单总数和总金额。 

     1
 2
 3
 4
 5
 6
 7
 8
 9
10
    		 
    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. 创建第二层物化视图，按照用户等级全局聚合。 

    1
2
3
4
5
6
7
8
    		 
    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. 创建第二层物化视图，按照地区全局聚合。 

    1
2
3
4
5
6
7
8
    		 
    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：广告行业精准投流

问题：海量日志数据的高频多维度聚合查询。广告曝光、点击、转化日志单日可达亿级，直接基于原始日志表进行（广告主 / 渠道 / 时段 / 地域）多维度统计，会触发全表扫描，查询耗时长达分钟级，无法支撑实时投放优化、报表展示等业务需求。

方案：创建物化视图，提前预计算 + 物理存储聚合结果，将多维度统计查询的响应时间从分钟级降至毫秒级，完美适配广告行业“准实时统计 + 高频报表”的核心需求。

场景示例表。

创建广告曝光点击转化日志表（核心基表，亿级数据量） 
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
 
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）
);
场景一：广告主实时查看广告指标（单表聚合物化视图） 
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
 
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 天投放效果，直接从物化视图获取，无需扫描亿级日志。 
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
 
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物化视图）

创建渠道信息表（维度表）

1
2
3
4
5
 
CREATE TABLE ad_channel_info (
    channel_id BIGINT PRIMARY KEY,      -- 渠道ID
    channel_name VARCHAR(50) NOT NULL,  -- 渠道名称（如抖音/微信/头条）
    channel_type VARCHAR(20) NOT NULL   -- 渠道类型（信息流/搜索/社交）
);

渠道-地域-设备多维度效果物化视图（双表JOIN+多维度聚合）

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
 
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天投放效果：

1
2
3
4
5
6
7
8
9
 
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;

场景三：广告投放周度趋势分析（嵌套物化视图）

广告主-渠道周度投放趋势物化视图（嵌套视图，基于日统计视图汇总）

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
 
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 周的投放趋势：

1
2
3
4
5
6
7
8
9
 
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. 创建用户行为日志表并插入数据。 

     1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
    		 
    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. 小时级统计物化视图，利用分区物化视图进行分区上卷计算。 

     1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
    		 
    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。 

     1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
    		 
    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;

    返回结果如下：

  4. 留存率统计。

    1. 首次访问物化视图。 
      1
2
3
4
5
6
7
8
9
      		 
      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）。 
       1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
      		 
      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）。 
       1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
      		 
      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. 查询示例：查看各首访日期的留存率。 
      1
2
3
4
5
6
7
      		 
      SELECT
    first_date,
    days_diff,
    user_count,
    retention_rate || '%' AS retention_rate
FROM mv_user_retention
ORDER BY first_date, days_diff;

父主题： 性能调优