SelectDB异步物化视图最佳实践-云数据库 SelectDB 版(SelectDB)-阿里云帮助中心

本文介绍异步物化视图的使用原则、刷新方式选择建议、查询加速策略和典型使用场景，帮助您构建高效的物化视图体系。

使用原则

时效性考虑：异步物化视图通常用于对数据时效性要求不高的场景，一般是T+1的数据。如果时效性要求高（秒级），应考虑使用同步物化视图。
加速效果与一致性考虑：在查询加速场景，创建物化视图时，DBA应将常见查询SQL模式分组，尽量使组之间无重合。SQL模式组划分越清晰，物化视图构建的质量越高。
定义与构建成本权衡：
- 物化视图定义和原查询越接近，查询加速效果越好，但通用性和复用性越差，构建成本越高。
- 物化视图定义越通用（例如没有WHERE条件和更多聚合维度），加速效果较低，但通用性和复用性越好，构建成本越低。
数量控制：物化视图并非越多越好。构建和刷新需要计算资源，透明改写时的代价模型评估时间会随数量增加而增长。建议定期通过mv_infos()检查物化视图的使用状态，及时删除未使用的物化视图。
grace_period（数据新鲜度容忍度）：通过设置grace_period属性（单位：秒），允许物化视图在基表数据变更后的指定时间内仍可用于透明改写，避免基表频繁更新导致物化视图持续失效。例如，设置grace_period = 3600表示允许1小时的数据滞后。

刷新方式选择原则

当满足以下条件时，建议创建分区物化视图：

物化视图的基表数据量很大，且基表是分区表。
物化视图使用的表除了分区表外，其他表不经常变化。
物化视图的定义SQL和分区字段满足分区推导的要求。
物化视图分区数不多（分区过多会导致构建时间过长）。

当物化视图的部分分区失效时，透明改写可以使用物化视图的有效分区UNION ALL基表返回数据，保证查询结果的正确性。

如果不能构建分区物化视图，可以考虑选择全量刷新的物化视图。

如何使用物化视图加速查询

在设计物化视图定义SQL时，可以从以下维度优化查询性能：

JOIN预计算

如果查询经常涉及多表JOIN，可以在物化视图中预先完成JOIN操作。查询时直接命中物化视图，避免运行时JOIN的计算开销。

-- 预计算订单和商品的JOIN
CREATE MATERIALIZED VIEW mv_order_item
REFRESH AUTO ON SCHEDULE EVERY 1 HOUR
AS SELECT
    o.order_id, o.order_date, o.customer_id,
    i.item_name, i.category, o.amount
FROM orders o
JOIN items i ON o.item_id = i.item_id;

聚合预计算

对于频繁执行的聚合查询，在物化视图中预先计算聚合结果。建议在物化视图中保留足够的维度列，使透明改写可以通过聚合上卷满足不同粒度的查询。

-- 按天+类别预计算销售额（支持按周/月上卷）
CREATE MATERIALIZED VIEW mv_daily_sales
PARTITION BY (sale_date)
REFRESH AUTO ON SCHEDULE EVERY 1 DAY
AS SELECT
    date_trunc(order_date, 'day') AS sale_date,
    category,
    region,
    sum(amount) AS total_amount,
    count(*) AS order_count
FROM orders
GROUP BY date_trunc(order_date, 'day'), category, region;

说明

物化视图中聚合维度应包含查询可能使用的所有GROUP BY列。例如上述物化视图包含category和region两个维度，可以同时满足"按类别聚合"和"按地区聚合"的查询。建议优先选择低基数（唯一值较少）的列作为维度。

过滤条件优化

如果查询通常只关注某个数据子集，可以在物化视图定义中添加过滤条件，减少物化视图的数据量。透明改写支持条件补偿，即查询的过滤条件比物化视图更严格时仍可命中。

-- 只物化最近1年的活跃订单
CREATE MATERIALIZED VIEW mv_active_orders
PARTITION BY (order_month)
REFRESH AUTO ON SCHEDULE EVERY 1 HOUR
AS SELECT
    date_trunc(order_date, 'month') AS order_month,
    customer_id, status, amount
FROM orders
WHERE status = 'active' AND order_date >= '2025-01-01';

计算表达式预计算

将复杂的计算表达式在物化视图中预先计算，避免查询时重复计算。

-- 预计算含税金额和利润率
CREATE MATERIALIZED VIEW mv_profit
REFRESH AUTO ON SCHEDULE EVERY 1 DAY
AS SELECT
    order_id, product_id,
    amount * (1 + tax_rate) AS amount_with_tax,
    (amount - cost) / amount AS profit_rate
FROM order_details;

典型使用场景

场景一：多表连接聚合查询加速

在报表和BI分析场景中，查询通常涉及多个事实表和维度表的JOIN及聚合操作。通过物化视图预计算，可以将复杂查询的响应时间从分钟级降低到秒级。

-- 预计算销售报表：订单 JOIN 客户 JOIN 产品
CREATE MATERIALIZED VIEW mv_sales_report
PARTITION BY (sale_month)
REFRESH AUTO ON SCHEDULE EVERY 1 DAY
AS SELECT
    date_trunc(o.order_date, 'month') AS sale_month,
    c.region, p.category,
    sum(o.amount) AS total_sales,
    count(DISTINCT o.customer_id) AS customer_count
FROM orders o
JOIN customers c ON o.customer_id = c.id
JOIN products p ON o.product_id = p.id
GROUP BY date_trunc(o.order_date, 'month'), c.region, p.category;

场景二：数据建模与ETL

利用物化视图构建数仓分层（ODS→DWD→DWS→ADS），通过触发式刷新实现层间级联更新，减少重复计算：

DWD层（明细宽表）：基于ODS表，通过物化视图完成多表JOIN和数据清洗。使用定时刷新（ON SCHEDULE）。
DWS层（汇总表）：基于DWD物化视图，进一步聚合计算。使用触发式刷新（ON COMMIT），DWD刷新完成后自动触发DWS刷新。
ADS层（应用表）：基于DWS物化视图，面向最终报表查询。同样使用触发式刷新或手动刷新。

场景三：湖仓一体联邦查询加速

对于存储在Hive、Iceberg等数据湖中的外部数据，查询性能通常受限于网络传输和数据格式解析。通过异步物化视图，可以将数据湖的热点查询结果缓存至SelectDB本地存储，利用SelectDB的高性能引擎大幅提升查询速度。

-- 将Hive中的用户行为日志缓存到SelectDB
CREATE MATERIALIZED VIEW mv_hive_user_behavior
PARTITION BY (event_date)
REFRESH AUTO ON SCHEDULE EVERY 4 HOUR
PROPERTIES (
    'excluded_trigger_tables' = 'hive_catalog.db.dim_user'
)
AS SELECT
    date_trunc(event_time, 'day') AS event_date,
    u.user_name, e.event_type,
    count(*) AS event_count
FROM hive_catalog.db.events e
JOIN hive_catalog.db.dim_user u ON e.user_id = u.id
GROUP BY date_trunc(event_time, 'day'), u.user_name, e.event_type;

说明

使用外表物化视图进行透明改写时，需设置SET materialized_view_rewrite_enable_contain_external_table = true。建议同时收集外表的统计信息，以提高代价模型的准确性。

场景四：提升写入效率

在高频写入的场景中，如果同时存在大量聚合查询，实时计算会与写入竞争资源。通过物化视图定时刷新，可以将聚合计算与写入操作在时间上分离，避免资源竞争。

将物化视图的刷新时间安排在业务低峰期（如凌晨），利用STARTS指定首次刷新时间。
配合grace_period属性，允许物化视图在一定时间内使用非最新数据响应查询，减少刷新频率。

分区物化视图使用方式

当物化视图的基表数据量很大且基表是分区表时，分区物化视图是性价比最高的方案。

CREATE MATERIALIZED VIEW rollup_partition_mv
BUILD IMMEDIATE REFRESH AUTO ON MANUAL
PARTITION BY (order_date)
DISTRIBUTED BY RANDOM BUCKETS 2
AS SELECT
    l_linestatus,
    sum(l_extendedprice * (1 - l_discount)) AS revenue,
    ps_partkey,
    date_trunc(l_ordertime, 'day') AS order_date
FROM lineitem
    LEFT JOIN partsupp ON l_partkey = ps_partkey AND l_suppkey = ps_suppkey
GROUP BY l_linestatus, ps_partkey, date_trunc(l_ordertime, 'day');

注意事项：

基表分区变更导致对应分区失效：如果基表的分区发生数据变更（如新增分区、删除分区、分区内数据修改等），物化视图对应的分区也会失效。失效分区不能用于透明改写，但可以通过直查物化视图访问；透明改写时，SelectDB 会使用物化视图的有效分区与基表进行 UNION ALL 合并后返回数据，保证查询结果正确性。可通过 SHOW PARTITIONS FROM mv_name 查看物化视图各分区的有效状态。
非分区表变更导致全部分区失效：如果物化视图引用的非分区表发生数据变更，会触发物化视图所有分区失效，此时物化视图不能用于透明改写。需通过 REFRESH MATERIALIZED VIEW mv_name AUTO; 刷新所有数据变化的分区。因此，建议将数据频繁变化的表设计为分区表作为引用表，将不经常变化的维表作为非分区表参与计算。
如果非分区表只有数据新增（无修改），可在创建物化视图时通过 excluded_trigger_tables 属性排除其变更的影响（例如 'excluded_trigger_tables' = '非分区表名1,非分区表名2'），避免非分区表新增数据触发物化视图全量失效，下次刷新时仅刷新分区表对应的失效分区。

分区物化视图只保留最近分区数据

在基表分区数量庞大、仅需查询近期数据的场景下，物化视图可以只保留最近若干个分区的数据，每次刷新时自动删除过期分区，降低构建成本和存储占用。通过以下属性组合实现：

partition_sync_limit：基表分区字段为时间类型时，配置同步基表的分区范围，需配合 partition_sync_time_unit 使用。例如 partition_sync_limit = 3 且 partition_sync_time_unit = DAY，表示仅同步基表近 3 天的分区和数据。
partition_sync_time_unit：分区刷新的时间单位，支持 DAY、MONTH、YEAR，默认 DAY。
partition_date_format：当基表分区字段为字符串类型时，用于指定日期格式（如 yyyy-MM-dd），以便 partition_sync_limit 能够正确解析时间范围。

示例：物化视图只保留最近 3 天的数据，超过 3 天的分区会在刷新时自动删除；如果最近 3 天没有数据，直查该物化视图不会返回任何结果。

CREATE MATERIALIZED VIEW latest_partition_mv
BUILD IMMEDIATE REFRESH AUTO ON MANUAL
PARTITION BY (order_date)
DISTRIBUTED BY RANDOM BUCKETS 2
PROPERTIES (
    'partition_sync_limit' = '3',
    'partition_sync_time_unit' = 'DAY',
    'partition_date_format' = 'yyyy-MM-dd'
)
AS SELECT
    l_linestatus,
    sum(l_extendedprice * (1 - l_discount)) AS revenue,
    ps_partkey,
    date_trunc(l_ordertime, 'day') AS order_date
FROM lineitem
    LEFT JOIN partsupp ON l_partkey = ps_partkey AND l_suppkey = ps_suppkey
GROUP BY l_linestatus, ps_partkey, date_trunc(l_ordertime, 'day');

创建包含UNION ALL的分区物化视图

分区物化视图定义中不能直接包含UNION ALL子句。替代方案是：对UNION ALL的每部分分别创建分区物化视图，然后创建一个普通视图（VIEW）合并结果。

-- 分别为UNION ALL的两部分创建分区物化视图
CREATE MATERIALIZED VIEW union_sub_mv1
BUILD IMMEDIATE REFRESH AUTO ON MANUAL
PARTITION BY (order_date)
DISTRIBUTED BY RANDOM BUCKETS 2
AS SELECT
    l_linestatus,
    sum(l_extendedprice * (1 - l_discount)) AS revenue,
    ps_partkey,
    date_trunc(l_ordertime, 'day') AS order_date
FROM lineitem LEFT JOIN partsupp ON l_partkey = ps_partkey AND l_suppkey = ps_suppkey
GROUP BY l_linestatus, ps_partkey, date_trunc(l_ordertime, 'day');

CREATE MATERIALIZED VIEW union_sub_mv2
BUILD IMMEDIATE REFRESH AUTO ON MANUAL
PARTITION BY (order_date)
DISTRIBUTED BY RANDOM BUCKETS 2
AS SELECT
    l_linestatus,
    l_extendedprice AS revenue,
    ps_partkey,
    date_trunc(l_ordertime, 'day') AS order_date
FROM lineitem LEFT JOIN partsupp ON l_partkey = ps_partkey AND l_suppkey = ps_suppkey;

-- 创建普通视图合并两个物化视图
CREATE VIEW union_view AS
SELECT * FROM union_sub_mv1
UNION ALL
SELECT * FROM union_sub_mv2;