AI 助理
文档备案控制台
官方文档
输入文档关键字查找
首页

创建、查询与维护异步物化视图

更新时间：
复制为 MD 格式
产品详情
我的收藏

本文介绍异步物化视图的创建语法、刷新配置、分区配置、直查与查询透明改写，以及常见运维操作和相关配置参数。

创建物化视图

权限说明

创建物化视图需要具有物化视图的创建权限（与建表权限相同），以及创建物化视图查询语句的查询权限（与SELECT权限相同）。

创建语法

CREATE MATERIALIZED VIEW [ IF NOT EXISTS ] <mv_name>
    [ (<columns_definition>) ]
    [ BUILD <build_mode> ]
    [ REFRESH <refresh_method> [refresh_trigger] ]
    [ [DUPLICATE] KEY (<key_cols>) ]
    [ COMMENT '<table_comment>' ]
    [ PARTITION BY (
        { <partition_col>
            | DATE_TRUNC(<partition_col>, <partition_unit>) }
    ) ]
    [ DISTRIBUTED BY { HASH (<distribute_cols>) | RANDOM }
        [ BUCKETS { <bucket_count> | AUTO } ]
    ]
    [ PROPERTIES (
        <table_property>
        [ , ... ])
    ]
    AS <query>

刷新配置

build_mode：刷新时机

物化视图创建完成后是否立即刷新：

  • IMMEDIATE：立即刷新（默认值）。

  • DEFERRED：延迟刷新，创建后不立即刷新。

refresh_method：刷新方式

  • COMPLETE：全量刷新，重新计算物化视图定义SQL所涉及的所有数据并完整写入。

  • AUTO：尽量增量刷新，只刷新自上次刷新后数据变化的分区。如果无法感知分区变化，则退化为全量刷新。

refresh_trigger：触发方式

  • ON MANUAL（手动触发）：通过SQL语句手动触发刷新。

    -- 增量刷新（仅刷新变化分区）
REFRESH MATERIALIZED VIEW mvName AUTO;
-- 全量刷新
REFRESH MATERIALIZED VIEW mvName COMPLETE;
-- 刷新指定分区
REFRESH MATERIALIZED VIEW mvName partitions(partitionName1, partitionName2);
    说明

    可通过SHOW PARTITIONS FROM mvName获取分区名称。如果物化视图使用JDBC外表，由于无法感知数据变化，刷新时需指定COMPLETE，否则AUTO模式可能导致物化视图无数据。

  • ON SCHEDULE（定时触发）：按照指定时间间隔自动刷新，单位可以是minute、hour、day、week等。支持通过STARTS指定首次刷新时间。

    -- 每10小时全量刷新一次
CREATE MATERIALIZED VIEW mv_1
REFRESH COMPLETE ON SCHEDULE EVERY 10 hour
AS SELECT * FROM lineitem;

-- 从指定时间开始，每天增量刷新一次
CREATE MATERIALIZED VIEW mv_2
BUILD DEFERRED REFRESH AUTO
ON SCHEDULE EVERY 1 DAY STARTS '2024-12-01 20:30:00'
PROPERTIES ('replication_num' = '1')
AS SELECT l_linestatus, to_date(o_orderdate) as date_alias
   FROM orders LEFT JOIN lineitem ON l_orderkey = o_orderkey;

  • ON COMMIT（自动触发，自4.0版本起支持）：基表数据发生变更后自动触发物化视图刷新。

    CREATE MATERIALIZED VIEW mv_3
BUILD IMMEDIATE REFRESH AUTO ON COMMIT
AS SELECT l_linestatus, to_date(o_orderdate) as date_alias
   FROM orders LEFT JOIN lineitem ON l_orderkey = o_orderkey;
    重要

    如果基表数据频繁变更，不建议使用ON COMMIT触发方式，以免频繁刷新消耗过多系统资源。

分区配置

分区物化视图通过PARTITION BY子句指定分区字段，支持增量分区刷新，仅刷新数据变化的分区，大幅降低刷新开销。分区字段引用的表达式仅允许使用date_trunc函数或直接引用列标识符。

基础分区示例

正确示例 —— 分区字段仅使用了date_trunc函数：

-- 创建按月分区的物化视图
CREATE MATERIALIZED VIEW mv_partition
BUILD IMMEDIATE REFRESH AUTO ON MANUAL
PARTITION BY (order_date_month)
DISTRIBUTED BY RANDOM BUCKETS 2
AS SELECT
    l_linestatus,
    date_trunc(o_orderdate, 'month') AS order_date_month,
    o_shippriority
FROM orders LEFT JOIN lineitem ON l_orderkey = o_orderkey;

错误示例 —— 分区字段order_date_month使用了date_add()函数，会报错because column to check use invalid implicit expression：

-- 错误：分区字段不支持 date_add 等函数
CREATE MATERIALIZED VIEW mv_wrong
BUILD IMMEDIATE REFRESH AUTO ON MANUAL
PARTITION BY (order_date_month)
DISTRIBUTED BY RANDOM BUCKETS 2
AS SELECT
    l_linestatus,
    date_trunc(date_add(o_orderdate, INTERVAL 2 DAY), 'month') AS order_date_month,
    o_shippriority
FROM orders LEFT JOIN lineitem ON l_orderkey = o_orderkey;

分区上卷

当基表数据经过聚合处理后，各分区的数据量可能会显著减少。在这种情况下，可以采用分区上卷策略，通过date_trunc函数降低物化视图的分区粒度，减少分区数量。

假设基表按天分区：

CREATE TABLE t1 (
    k1 LARGEINT NOT NULL,
    k2 DATE NOT NULL
) ENGINE=OLAP
DUPLICATE KEY(k1)
PARTITION BY range(k2)
(
    PARTITION p_20200101 VALUES [("2020-01-01"),("2020-01-02")),
    PARTITION p_20200102 VALUES [("2020-01-02"),("2020-01-03")),
    PARTITION p_20200201 VALUES [("2020-02-01"),("2020-02-02"))
)
DISTRIBUTED BY HASH(k1) BUCKETS 2;

按月上卷 —— 物化视图将包含两个分区：[("2020-01-01","2020-02-01")]和[("2020-02-01","2020-03-01")]：

CREATE MATERIALIZED VIEW mv_monthly_agg
BUILD DEFERRED REFRESH AUTO ON MANUAL
partition by (date_trunc(k2, 'month'))
DISTRIBUTED BY RANDOM BUCKETS 2
AS SELECT * FROM t1;

按年上卷 —— 物化视图将只包含一个分区：[("2020-01-01","2021-01-01")]：

CREATE MATERIALIZED VIEW mv_yearly_agg
BUILD DEFERRED REFRESH AUTO ON MANUAL
partition by (date_trunc(k2, 'year'))
DISTRIBUTED BY RANDOM BUCKETS 2
AS SELECT * FROM t1;
说明

如果分区字段为字符串类型，可以通过设置物化视图的partition_date_format属性来指定日期格式，例如'%Y-%m-%d'。

仅保留最近分区

有些基表有很多分区，但物化视图只关注最近一段时间的热数据。通过partition_sync_limit和partition_sync_time_unit属性，可以控制物化视图只保留最近N个时间单位的分区数据，避免物化视图数据量无限增长。

假设基表按天分区：

CREATE TABLE t1 (
    k1 INT,
    k2 DATE NOT NULL
) ENGINE=OLAP
DUPLICATE KEY(k1)
PARTITION BY range(k2)
(
    PARTITION p26 VALUES [("2024-03-26"),("2024-03-27")),
    PARTITION p27 VALUES [("2024-03-27"),("2024-03-28")),
    PARTITION p28 VALUES [("2024-03-28"),("2024-03-29"))
)
DISTRIBUTED BY HASH(k1) BUCKETS 2;

物化视图只关注最近1天的数据。若当前时间为 2024-03-28，物化视图将仅有一个分区[("2024-03-28"),("2024-03-29")]：

CREATE MATERIALIZED VIEW mv_recent
BUILD DEFERRED REFRESH AUTO ON MANUAL
PARTITION BY (k2)
DISTRIBUTED BY RANDOM BUCKETS 2
PROPERTIES (
    'partition_sync_limit' = '1',
    'partition_sync_time_unit' = 'DAY'
)
AS SELECT * FROM t1;

滚动行为：若时间又过了一天（当前时间为 2024-03-29），基表新增分区[("2024-03-29"),("2024-03-30")]，此时刷新物化视图后，物化视图将仅有一个分区[("2024-03-29"),("2024-03-30")]，旧分区会被自动清理。

说明

如果分区字段为字符串类型，可以设置物化视图属性partition_date_format，例如'%Y-%m-%d'。

基表有多列分区（Hive外表）

目前仅支持Hive外表有多列分区。Hive外表有很多多级分区的情况，例如一级分区按照日期，二级分区按照区域。物化视图可以选择Hive的某一级分区列作为物化视图的分区列。

Hive建表语句示例：

CREATE TABLE hive1 (
    k1 int)
PARTITIONED BY (
    year int,
    region string)
STORED AS ORC;

ALTER TABLE hive1 ADD IF NOT EXISTS
PARTITION(year=2020,region="bj")
PARTITION(year=2020,region="sh")
PARTITION(year=2021,region="bj")
PARTITION(year=2021,region="sh")
PARTITION(year=2022,region="bj")
PARTITION(year=2022,region="sh")

按year分区时，物化视图将有三个分区：('2020'), ('2021'), ('2022')：

CREATE MATERIALIZED VIEW mv_hive
BUILD DEFERRED REFRESH AUTO ON MANUAL
partition by(year)
DISTRIBUTED BY RANDOM BUCKETS 2
AS SELECT k1,year,region FROM hive1;

按region分区时，物化视图将有两个分区：('bj'), ('sh')：

CREATE MATERIALIZED VIEW mv_hive2
BUILD DEFERRED REFRESH AUTO ON MANUAL
partition by(region)
DISTRIBUTED BY RANDOM BUCKETS 2
AS SELECT k1,year,region FROM hive1;

直查物化视图

物化视图本质上是一张物理表，可以像普通表一样直接查询：

SELECT * FROM mv_name WHERE ...;

直查不受物化视图状态（NORMAL/SCHEMA_CHANGE）的限制，即使物化视图状态异常，直查仍可返回已有数据（可能不是最新数据）。

查询透明改写

SelectDB的查询优化器（Nereids）支持基于SPJG（SELECT-PROJECT-JOIN-GROUP-BY）模式的透明改写：当用户提交查询时，优化器会自动分析该查询是否可以被已有的物化视图满足，如果可以，则将查询路由到物化视图，而不执行原始查询，从而无需修改业务SQL即可实现查询加速。

前提条件

  • 物化视图状态为NORMAL（已成功刷新且基表无Schema变更）。

  • 启用新优化器：SET enable_nereids_planner = true（默认已开启）。

  • 启用物化视图改写：SET enable_materialized_view_rewrite = true（默认已开启）。

支持的改写类型

透明改写支持以下场景：

  • 条件补偿：查询的过滤条件比物化视图更严格时，自动在物化视图之上添加额外的过滤条件。

  • JOIN改写：查询和物化视图包含相同的JOIN关系时，自动匹配并使用物化视图。

  • JOIN衍生：查询比物化视图多出的JOIN表可以通过物化视图的列推导得出时，自动添加额外的JOIN。

  • 聚合改写：查询包含聚合操作且物化视图已预计算相关聚合结果时，直接使用预计算结果。

  • 聚合上卷：物化视图的聚合粒度比查询更细时，可在物化视图结果上进一步聚合。例如物化视图按天聚合，查询按月聚合。

  • 分区补偿改写：对于分区物化视图，当部分分区数据失效时，自动合并物化视图有效分区的数据和基表最新分区的数据。

  • 嵌套物化视图改写：支持基于物化视图创建的物化视图参与改写。

查看改写情况

通过EXPLAIN查看查询是否命中物化视图：

EXPLAIN SELECT l_linestatus, sum(l_extendedprice) FROM orders
LEFT JOIN lineitem ON l_orderkey = o_orderkey
GROUP BY l_linestatus;

在EXPLAIN结果中，可以观察以下标记：

  • MaterializedViewRewriteSuccessAndChose：改写成功且选择了该物化视图。

  • MaterializedViewRewriteSuccessButNotChose：改写成功但基于代价模型未选择该物化视图。

  • MaterializedViewRewriteFail：改写失败，后跟失败原因摘要。

说明

如果物化视图包含UNION ALL、LIMIT、ORDER BY或CROSS JOIN等操作，物化视图可正常构建和直查，但不能用于透明改写。窗口函数同理，当前版本不支持透明改写，但支持直查。

维护物化视图

查看物化视图信息

-- 查看物化视图基本信息和状态
SELECT * FROM mv_infos('database'='db_name') WHERE Name = 'mv_name' \G

-- 查看物化视图分区状态（含各分区是否与基表同步）
SHOW PARTITIONS FROM mv_name;

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

-- 查看物化视图表结构
DESC mv_name;

查看刷新任务

-- 查看物化视图的刷新任务历史
SELECT * FROM tasks('type'='mv') WHERE MvName = 'mv_name';

-- 查看物化视图的调度JOB
SELECT * FROM jobs('type'='mv') WHERE MvName = 'mv_name';

暂停和恢复刷新任务

-- 暂停物化视图刷新
PAUSE MATERIALIZED VIEW JOB ON mv_name;

-- 恢复物化视图刷新
RESUME MATERIALIZED VIEW JOB ON mv_name;

-- 取消正在运行的刷新任务
CANCEL MATERIALIZED VIEW TASK taskId ON mv_name;

修改物化视图

-- 修改物化视图刷新间隔（物化视图特有属性）
ALTER MATERIALIZED VIEW mv_name SET (
    'refresh_interval' = '2 HOUR'
);

-- 修改公共属性（如副本数）
ALTER TABLE mv_name SET (
    'replication_num' = '2'
);

-- 重命名物化视图
ALTER MATERIALIZED VIEW mv_name RENAME new_mv_name;

-- 原子替换物化视图
ALTER MATERIALIZED VIEW mv_old REPLACE WITH MATERIALIZED VIEW mv_new;

删除物化视图

DROP MATERIALIZED VIEW IF EXISTS mv_name;
说明

删除基表不会自动删除其关联的物化视图，但会导致物化视图状态异常。需手动删除相关物化视图。不能使用DROP TABLE或RENAME TABLE命令操作物化视图。

相关配置

Session Variables 开关

开关

说明

SET enable_nereids_planner = true;

异步物化视图只有在新优化器下才支持，所以物化视图透明改写没有生效时，需要开启新优化器

SET enable_materialized_view_rewrite = true;

开启或者关闭查询透明改写，从 2.1.5 版本开始默认开启

SET materialized_view_rewrite_enable_contain_external_table = true;

参与透明改写的物化视图是否允许包含外表，默认不允许，如果物化视图的定义 SQL 中包含外表，也想参与到透明改写，可以打开此开关。

SET materialized_view_rewrite_success_candidate_num = 3;

透明改写成功的结果集合，允许参与到 CBO 候选的最大数量，默认是 3。如果发现透明改写的性能很慢，可以考虑把这个值调小。

SET enable_materialized_view_union_rewrite = true;

当分区物化视图不足以提供查询的全部数据时，是否允许基表和物化视图 union all 来响应查询，默认允许。如果发现命中物化视图时数据错误，可以把此开关关闭。

SET enable_materialized_view_nest_rewrite = true;

是否允许嵌套改写，默认不允许。如果查询 SQL 很复杂，需要构建嵌套物化视图才可以命中，那么需要打开此开关。

SET materialized_view_relation_mapping_max_count = 8;

透明改写过程中，relation mapping 最大允许数量，如果超过，进行截取。relation mapping 通常由表自关联产生，数量一般会是笛卡尔积，比如 3 张表，可能会产生 8 种组合。默认是 8。如果发现透明改写时间很长，可以把这个值调低

SET enable_dml_materialized_view_rewrite = true;

DML 时，是否开启基于结构信息的物化视图透明改写，默认开启

SET enable_dml_materialized_view_rewrite_when_base_table_unawareness = true;

DML 时，当物化视图存在无法实时感知数据的外表时，是否开启基于结构信息的物化视图透明改写，默认关闭

fe.conf 配置

  • job_mtmv_task_consumer_thread_num： 此参数控制同时运行的物化视图刷新任务数量，默认是 10，超过这个数量的任务将处于 pending 状态 修改这个参数需要重启 FE 才可以生效。

上一篇：异步物化视图概述下一篇：异步物化视图最佳实践