物化视图设计最佳实践

刷新任务调度

• 防止CPU突高。

  刷新任务错峰编排，避免集中刷新；通过START WITH 和 EVERY设定；不同MV设置不同刷新时间。

• 防止计划跳变

  执行计划尽量HINT固定；减少计划跳变对查询性能的影响。

• 防止重复刷新

  如果级联使用物化视图，不必每一层都创建刷新任务，可以按数据流管道级联刷新；使用CASCADE BACKWARD控制。

• 上层刷新间隔必须大于下层最大间隔。

  正确示例：

  1. 上层 mv2：每10分钟刷新 (≥5)
  2. 下层 mv1：每5分钟刷新

  错误示例：上层刷新时看到的是下层旧数据。

  1. 上层 mv2：每 2分钟刷新 (<5)
  2. 下层 mv1：每 5分钟刷新

存储格式选择

推荐使用hstore_opt存储格式。

1
2
3

CREATE MATERIALIZED VIEW mv_test
WITH (orientation = COLUMN, enable_hstore_opt = ON)
AS SELECT ...

典型应用模式

模式一：报表加速

• 业务场景：每日销售报表
• 基表：销售明细表 (亿级数据)
• 查询：每日销售汇总

示例：

创建物化视图：

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13

CREATE MATERIALIZED VIEW mv_sales_daily
REFRESH FAST EVERY (interval '1 hour')
ENABLE QUERY REWRITE
WITH (orientation = COLUMN, enable_hstore_opt = ON)
AS
SELECT
DATE(sale_time) as sale_date,
region,
product_category,
SUM(amount) as total_amount,
COUNT(*) as order_count
FROM sales_detail
GROUP BY DATE(sale_time), region, product_category;

模式二：实时数仓分层

1. DWD层：明细清洗。

   1 2 3 4

   CREATE MATERIALIZED VIEW mv_dwd_sales REFRESH FAST EVERY (interval '5 min') ENABLE QUERY REWRITE AS SELECT * FROM source_sales WHERE status = 'valid';

2. DWS层：基础汇总。

   1 2 3 4 5 6 7 8 9 10 11

   CREATE MATERIALIZED VIEW mv_dws_sales REFRESH FAST EVERY (interval '10 min') ENABLE QUERY REWRITE AS SELECT sale_date, region, SUM(total_amount) as amount, SUM(order_count) as orders FROM mv_dwd_sales GROUP BY sale_date, region;

3. ADS层：应用报表。

   1 2 3 4 5 6 7 8 9 10 11

   CREATE MATERIALIZED VIEW mv_ads_sales REFRESH FAST EVERY (interval '30 min') ENABLE QUERY REWRITE AS SELECT sale_date, region, amount, orders, amount / orders as avg_order_value FROM mv_dws_sales;