刷新物化视图
物化视图常用来加速复杂查询或者简化ETL流程,其本质是将用户定义的查询提前计算好,按用户要求刷新视图中的数据。您可以根据基表的写入模式、物化视图查询(query_body)的SQL计算复杂度,以及对物化视图数据时效性的预期,定义物化视图的刷新策略。
如何选择刷新策略
物化视图支持两种刷新策略——全量刷新(COMPLETE)和增量刷新(FAST)。
全量刷新,即每次刷新时运行原始的查询SQL,扫描基表的全部目标分区的数据,用计算好的新数据全量覆盖旧数据。
增量刷新,即改写物化视图的查询(query_body),使物化视图只扫描基表变更的部分数据,加工后写入物化视图,从而避免每次都扫描基表的全部数据,降低单次刷新的计算开销。
两种刷新策略的适用场景、优势和限制对比如下。
刷新策略 | 适用场景 | 特点 |
刷新策略 | 适用场景 | 特点 |
全量刷新 | 离线场景:
| 优势:query_body支持任意SQL查询。 |
限制:只能批量更新全量数据。 | ||
增量刷新 | 实时场景:
| 优势:
|
限制:
|
如何选择刷新触发机制
创建物化视图时,不仅需要定义刷新策略,还需要定义如何触发刷新,即刷新的触发机制。物化视图刷新触发机制分为按需刷新(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 INTO和TRUNCATE操作。
增量刷新的物化视图创建完成后,不允许关闭基表的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 OVERWRITE和TRUNCATE,执行会报错。
增量刷新只支持定时自动刷新,不允许手动刷新。定时自动的增量刷新,刷新间隔最短5秒(s),最长5分钟(min)。
增量刷新的物化视图,query_body有以下限制:
由于物化视图要保证结果和您查询基表的结果完全一致,且要支持任意DML变更,所以并不是所有query_body都可以增量刷新。如果创建的物化视图无法增量刷新,创建时会报错。
不允许出现非确定性的表达式作为条件,如:
now()
、rand()
等。仅支持以下聚合函数:COUNT、SUM、MAX、MIN、AVG、APPROX_DISTINCT和COUNT(DISTINCT)。
当query_body中使用MAX、MIN、APPROX_DISTINCT或COUNT(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操作,不会记录在审计日志中。
查询过去14天的物化视图手动刷新记录和自动刷新记录,可以使用SQL诊断优化功能。查询时,输入物化视图的名称,例如compl_mv1,可查询该物化视图的所有相关SQL查询(包括创建、手动刷新、自动刷新、变更等)的开始时间、用户名、耗时、查询ID等。
停止正在进行的刷新任务
如果物化视图刷新时间过长,您可以联系技术支持停止此次刷新任务。
注意事项
如果您已通过KILL PROCESS <process_id>;
尝试停止本次刷新任务,需要注意的是,即使停止此次刷新,之后在到达下次刷新时间或者基表下次被覆盖写时,仍会触发下次刷新。
相关文档
创建物化视图的详细介绍,请参见创建物化视图。
物化视图的语法详解,请参见CREATE MATERIALIZED VIEW。
查询物化视图的定义、变更物化视图,请参见管理物化视图。
- 本页导读 (1)
- 如何选择刷新策略
- 如何选择刷新触发机制
- 定义物化视图的刷新策略和触发机制
- 创建全量刷新的物化视图
- 创建增量刷新的物化视图
- 手动刷新物化视图
- 查询物化视图的刷新记录
- 查询自动刷新记录
- 查询手动刷新记录
- 停止正在进行的刷新任务
- 注意事项
- 相关文档