刷新物化视图

更新时间:2025-02-19 07:54:49

物化视图常用来加速复杂查询或者简化ETL流程,其本质是将用户定义的查询提前计算好,按用户要求刷新视图中的数据。您可以根据基表的写入模式、物化视图查询(query_body)的SQL计算复杂度,以及对物化视图数据时效性的预期,定义物化视图的刷新策略。

如何选择刷新策略

物化视图支持两种刷新策略——全量刷新(COMPLETE)和增量刷新(FAST)。

  • 全量刷新,即每次刷新时运行原始的查询SQL,扫描基表的全部目标分区的数据,用计算好的新数据全量覆盖旧数据。

  • 增量刷新,即改写物化视图的查询(query_body),使物化视图只扫描基表变更的部分数据,加工后写入物化视图,从而避免每次都扫描基表的全部数据,降低单次刷新的计算开销。

两种刷新策略的适用场景、优势和限制对比如下。

刷新策略

适用场景

特点

刷新策略

适用场景

特点

全量刷新

离线场景:

  • 数据每天批量更新一次(T+1)

  • 数据小时级更新

  • 能够容忍分钟级延迟,且数据量小、计算复杂度低。

优势:query_body支持任意SQL查询。

限制:只能批量更新全量数据。

增量刷新

实时场景:

  • 数据实时流入。

  • 需要实时更新的报表或ETL。

  • 对数据延迟要求高的秒级延迟需求。

优势:

  • 通常计算的数据量较小,往往开销较低。

  • 用增量刷新物化视图代替流计算,减少开发和维护成本。

限制:

  • 存在使用限制,包括版本限制、基表限制、query_body的限制等。

  • 不支持手动刷新和基表被覆盖写时自动刷新。

如何选择刷新触发机制

创建物化视图时,不仅需要定义刷新策略,还需要定义如何触发刷新,即刷新的触发机制。物化视图刷新触发机制分为按需刷新(ON DEMAND)和基表被INSERT OVERWRITE覆写后自动刷新(ON OVERWRITE)。其中,按需刷新又分为定时自动刷新和手动刷新。如果未指定刷新触发机制,则默认为按需刷新。

选择刷新触发机制时,需要考虑物化视图的数据时效性和集群负载。不同触发机制的特点和适用场景如下:

  • 手动刷新:物化视图不主动刷新数据。用户需要执行REFRESH MATERIALIZED VIEW来手动刷新数据。适用于对数据一致性要求不高或数据不经常变动的场景。

  • 定时自动刷新:物化视图将在指定时间自动刷新数据。如果到达指定的刷新时间,上次刷新还未完成,将自动跳过此次刷新,等到下一个刷新时间再刷新。适用于基表数据定期变化的场景,例如每日、每周固定时间段内会产生新的交易记录。

  • 基表被INSERT OVERWRITE覆写后自动刷新:物化视图将在基表被INSERT OVERWRITE覆盖写时自动刷新。适用于对数据实时性和一致性要求较高的场景。

不同刷新策略支持不同的刷新触发机制,具体如下:

刷新策略

按需刷新(ON DEMAND)

基表被覆盖写时自动刷新(ON OVERWRITE)

手动刷新

定时自动刷新(定义NEXT参数值)

刷新策略

按需刷新(ON DEMAND)

基表被覆盖写时自动刷新(ON OVERWRITE)

手动刷新

定时自动刷新(定义NEXT参数值)

全量刷新

✔️

✔️

✔️

增量刷新

✔️

定义物化视图的刷新策略和触发机制

下文以customer表、sales表和product表为例,指导您在创建物化视图时定义刷新策略和刷新触发机制。

CREATE TABLE customer (
    customer_id INT PRIMARY KEY,
    customer_name VARCHAR(255),
    is_vip Boolean
);
CREATE TABLE sales (
    sale_id INT PRIMARY KEY,
    product_id INT,
    customer_id INT,
    price DECIMAL(10, 2),
    quantity INT,
    sale_date TIMESTAMP
);
CREATE TABLE product (
    product_id INT PRIMARY KEY,
    product_name VARCHAR,
    category_id INT,
    unit_price DECIMAL(10, 2),
    stock_quantity INT
);

创建全量刷新的物化视图

创建物化视图时,通过关键字REFRESH COMPLETE指定物化视图的刷新策略为全量刷新。

全量刷新的物化视图支持的刷新模式包括手动刷新、定时自动刷新、基表被INSERT OVERWRITE覆盖写时自动刷新。

  • 创建全量刷新的物化视图compl_mv1。该物化视图未定义刷新模式和NEXT参数,因此采用默认的刷新触发机制为按需刷新,且为手动刷新。

    CREATE MATERIALIZED VIEW compl_mv1
    REFRESH COMPLETE
    AS
    SELECT * FROM customer;
  • 创建全量刷新的物化视图compl_mv2。该物化视图定义了按需刷新(ON DEMAND),且定义了首次(START WITH)和下次(NEXT)的刷新时间。本例每天凌晨2点自动刷新。

    CREATE MATERIALIZED VIEW compl_mv2
    REFRESH COMPLETE ON DEMAND
     START WITH DATE_FORMAT(now() + interval 1 day, '%Y-%m-%d 02:00:00')
     NEXT DATE_FORMAT(now() + interval 1 day, '%Y-%m-%d 02:00:00')
    AS
    SELECT * FROM customer;
  • 创建全量刷新的物化视图compl_mv3。该物化视图定义了基表被INSERT OVERWRITE覆写时自动刷新(ON OVERWRITE),此刷新模式无需定义下次刷新时间(NEXT)。

    CREATE MATERIALIZED VIEW compl_mv3
    REFRESH COMPLETE ON OVERWRITE
    AS
    SELECT * FROM customer;

创建增量刷新的物化视图

创建物化视图时,通过关键字REFRESH FAST指定物化视图的刷新策略为增量刷新。增量刷新的物化视图仅支持定时自动刷新。

开启Binlog特性

在创建增量刷新的物化视图前,需先开启集群的Binlog特性开关以及基表的Binlog功能。

SET ADB_CONFIG BINLOG_ENABLE=true; --3.2.0.0以下版本集群需执行该命令手动开启Binlog特性。3.2.0.0及以上版本默认开启。
ALTER TABLE customer binlog=true;
ALTER TABLE sales binlog=true;
ALTER TABLE product binlog=true;
重要
  • 对于开启Binlog功能的表,仅3.2.0.0及以上内核版本才支持INSERT OVERWRITE INTOTRUNCATE操作。

  • 增量刷新的物化视图创建完成后,不允许关闭基表的Binlog功能。

  • 删除增量刷新的物化视图后,可以执行SET ADB_CONFIG BINLOG_ENABLE=false;ALTER TABLE <table_name> binlog=false;手动关闭Binlog特性和基表的Binlog功能。

单表物化视图

  • 创建无聚合操作的增量刷新的单表物化视图fast_mv1,每10秒钟刷新一次,

    CREATE MATERIALIZED VIEW fast_mv1
    REFRESH FAST NEXT now() + INTERVAL 10 second
    AS
    SELECT sale_id, sale_date, price
    FROM sales
    WHERE price > 10;
  • 创建分组聚合操作的增量刷新的单表物化视图fast_mv2,每5秒钟刷新一次。

    CREATE MATERIALIZED VIEW fast_mv2
    REFRESH FAST NEXT now() + INTERVAL 5 second
    AS
    SELECT
       customer_id, sale_date,                 -- 系统会自动输出GROUP BY列作为物化视图主键。
       COUNT(sale_id) AS cnt_sale_id,          -- 聚合输出列。
       SUM(price * quantity) AS total_revenue, -- 聚合输出列。
       customer_id / 100 AS new_customer_id    -- 非聚合输出列可以使用任意表达式。
    FROM sales
    WHERE ifnull(price, 1) > 0                 -- 条件可以使用任何表达式。
    GROUP BY customer_id, sale_date;
  • 创建无分组聚合操作的增量刷新的单表物化视图fast_mv3,每分钟刷新一次。

    CREATE MATERIALIZED VIEW fast_mv3
    REFRESH FAST NEXT now() + INTERVAL 1 minute
    AS
    SELECT count(*) AS cnt   -- 系统会自动生成常量主键,确保全局只有一条记录在物化视图中。
    FROM sales;

多表物化视图

  • 创建无聚合操作的增量刷新的多表物化视图fast_mv4,每5秒钟刷新一次,

    CREATE MATERIALIZED VIEW fast_mv4
    REFRESH FAST NEXT now() + INTERVAL 5 second
    AS
    SELECT 
        c.customer_id,
        c.customer_name,
        p.product_id,
        s.sale_id,
        (s.price * s.quantity) AS revenue
    FROM 
        sales s
    JOIN 
        customer c ON s.customer_id = c.customer_id
    JOIN 
        product p ON s.product_id = p.product_id;
  • 创建分组聚合操作的增量刷新的多表物化视图fast_mv5,每10秒钟刷新一次。

    CREATE MATERIALIZED VIEW fast_mv5
    REFRESH FAST NEXT now() + INTERVAL 10 second
    AS
    SELECT 
        s.sale_id,
        c.customer_name,
        p.product_name,
        COUNT(*) AS cnt,           
        SUM(s.price * s.quantity) AS revenue,         
        SUM(p.unit_price) AS sum_p          
    FROM 
        sales s
    JOIN 
        (SELECT customer_id, customer_name FROM customer) c ON c.customer_id = s.customer_id
    JOIN 
        (SELECT * FROM product WHERE stock_quantity > 0) p ON p.product_id = s.product_id
    GROUP BY 
        s.sale_id, c.customer_name, p.product_name;

使用限制

增量刷新的物化视图限制如下:

  • 内核版本为3.2.3.0以下的集群,不支持分区表作为增量刷新的物化视图的基表。

  • 内核版本为3.2.3.1以下的集群,增量刷新的物化视图,基表不支持INSERT OVERWRITETRUNCATE,执行会报错。

  • 增量刷新只支持定时自动刷新,不允许手动刷新。定时自动的增量刷新,刷新间隔最短5秒(s),最长5分钟(min)。

  • 增量刷新的物化视图,query_body有以下限制:

    • 由于物化视图要保证结果和您查询基表的结果完全一致,且要支持任意DML变更,所以并不是所有query_body都可以增量刷新。如果创建的物化视图无法增量刷新,创建时会报错。

    • 不允许出现非确定性的表达式作为条件,如:now()rand()等。

    • 仅支持以下聚合函数:COUNT、SUM、MAX、MIN、AVG、APPROX_DISTINCTCOUNT(DISTINCT)。

    • query_body中使用MAX、MIN、APPROX_DISTINCTCOUNT(DISTINCT)聚合函数时,增量物化视图的基表只允许执行INSERT操作,禁止执行DELETE、UPDATE、REPLACE、INSERT ON DUPLICATE KEY UPDATE等会导致数据删除的操作。

    • COUNT(DISTINCT)外,其余聚合函数均不支持DISTINCT关键字。

    • COUNT(DISTINCT)仅支持INTEGER类型。

    • AVG不支持DECIMAL类型。

    • 聚合操作不支持HAVING关键字。

    • 不支持窗口函数。

    • 不支持排序操作。

    • 不支持UNION、EXCEPT、INTERSECT等集合操作。

  • 增量刷新的多表物化视图,还有以下限制:

    • 多表物化视图目前仅支持使用INNER JOIN。

    • 多表物化视图中默认最多关联5张表。若您有需求,可提交工单联系技术支持,根据集群规格调大该限制。

    • 多表物化视图中的关联字段需为表的原始字段,且关联字段的数据类型相同,均有INDEX索引。

手动刷新物化视图

如果创建物化视图时定义的刷新策略是按需刷新(ON DEMAND),且未定义下次刷新时间(NEXT),那么物化视图是不会自动刷新的。您可以手动刷新物化视图的数据。

REFRESH MATERIALIZED VIEW <mv_name>;

发起刷新请求后,系统会将刷新任务放入后台队列中,您无需等待刷新完成即可继续执行其他操作。

返回Query OK执行成功,说明刷新完成。

查询物化视图的刷新记录

查询自动刷新记录

通过下列SQL查询指定物化视图的自动刷新记录,包括刷新的开始时间(start_time)、结束时间(end_time)、状态(state)、查询ID(process_id)。更多关于返回结果的字段说明,请参见管理物化视图

SELECT * FROM information_schema.mv_auto_refresh_jobs where mv_name = '<mv_name>';

查询手动刷新记录

  • 查询过去30天的物化视图手动刷新记录,可以使用SQL审计功能。查询时,输入关键字REFRESH MATERIALIZED VIEW mv_name,可查询手动刷新的时间、时长、IP、用户名等信息。

    SQL审计功能需单独开通。开通前的SQL操作,不会记录在审计日志中。

    image

  • 查询过去14天的物化视图手动刷新记录和自动刷新记录,可以使用SQL诊断优化功能。查询时,输入物化视图的名称,例如compl_mv1,可查询该物化视图的所有相关SQL查询(包括创建、手动刷新、自动刷新、变更等)的开始时间、用户名、耗时、查询ID等。

    image

停止正在进行的刷新任务

如果物化视图刷新时间过长,您可以联系技术支持停止此次刷新任务。

注意事项

如果您已通过KILL PROCESS <process_id>;尝试停止本次刷新任务,需要注意的是,即使停止此次刷新,之后在到达下次刷新时间或者基表下次被覆盖写时,仍会触发下次刷新。

相关文档

  • 本页导读 (1)
  • 如何选择刷新策略
  • 如何选择刷新触发机制
  • 定义物化视图的刷新策略和触发机制
  • 创建全量刷新的物化视图
  • 创建增量刷新的物化视图
  • 手动刷新物化视图
  • 查询物化视图的刷新记录
  • 查询自动刷新记录
  • 查询手动刷新记录
  • 停止正在进行的刷新任务
  • 注意事项
  • 相关文档
AI助理

点击开启售前

在线咨询服务

你好,我是AI助理

可以解答问题、推荐解决方案等