增量刷新物化视图 - 云原生数据仓库AnalyticDB

增量刷新会通过特定算法，只计算变更部分的数据，然后定向更新物化视图中的数据。增量刷新相较于全量刷新开销更低，可以更高频地更新。如果您仅需要刷新变更的数据，可以选择增量刷新物化视图。本文介绍如何在创建物化视图时指定增量刷新模式。

前提条件

增量刷新物化视图时，集群内核版本应满足以下条件：

增量刷新单表物化视图时，集群的内核版本需为3.1.9.0及以上版本。
增量刷新多表物化视图时，集群的内核版本需为3.2.1.0及以上版本。

说明

查看湖仓版集群的内核版本，请执行SELECT adb_version();。如需升级内核版本，请联系技术支持。
查看和升级数仓版集群的内核版本，请参见查看和升级版本。

适用场景

物化视图常用来加速复杂查询或者简化ETL流程，物化视图的本质是将用户定义的查询提前计算好，按用户要求自动刷新视图中的数据。刷新的方式一般分为两种，全量刷新和增量刷新。两者区别如下：

全量刷新每次重新运行SQL，将算好的新数据全量覆盖旧数据。
增量刷新通过特定算法，每次只计算新变更部分的数据，然后定向更新物化视图中的部分数据。

因此，增量刷新往往开销更低，可以更高频地更新。从更新机制上看，增量刷新是优于全量刷新的，但其实并不是所有场景都适合增量刷新。它们各自有适用的场景。

全量刷新适用于T+1类的离线场景。常见的适用场景如下：

数据每天批量更新一次，或者小时级别更新一次，更适合全量刷新。这类SQL通常比较复杂，全量刷新对SQL语法没有任何约束。
在某些分钟级别延迟的场景中也可以使用全量刷新，往往这类SQL即使全量计算成本也不大，通常十几秒能完成，还可以达到一定的实时效果。

增量刷新适用于实时场景。常见的适用场景如下：

数据实时流入。
需要实时更新的报表或ETL。
对数据延迟要求高的秒级延迟需求。

物化视图本质是过去某一时刻的数据查询结果，刷新的延迟越低，就越接近实际结果。一些数仓会保证强一致，但维护成本高，基表写入性能也不稳定。AnalyticDB for MySQL是全异步算法，支持任意DML，不影响基表写入。

增量更新成本更低，是胜在只算增量。如果计算相同的数据量，由于算法更复杂，计算成本要比全量刷新高。所以在T+1类定期大批数据量计算的场景，并不合适。并且要能够维护查询数据的增量更新，并不是任意SQL都支持的，有一些限制。

增量刷新可以代替一部分流计算的需求，尤其是对SQL查询语意有要求的（要求增量刷新物化视图的结果和查询基表的结果完全一致），使用增量刷新不需要您额外维护流引擎组件，成本更低。

使用限制

增量刷新只支持自动刷新，不允许手动刷新。增量刷新模式下的自动刷新间隔最短5秒（s），最长5分钟（min）。
由于物化视图要保证结果和您查询基表的结果完全一致，且要支持任意DML变更，所以并不是所有QUERY BODY都可以增量刷新。如果创建的物化视图无法增量刷新，创建时会报错。
内核版本为3.2.3.0以下的集群，不支持将定义了分区的表作为增量物化视图的基表。
增量刷新的物化视图，其基表在执行INSERT OVERWRITE以及TRUNCATE操作时会报错，因此基表禁止执行这些操作。
增量刷新多表物化视图时，您还需注意以下内容：
- 多表物化视图目前仅支持使用INNER JOIN。
- 多表物化视图中默认最多关联5张表，若您有需求，可提交工单联系技术支持，根据集群规格调大该限制。
- 多表物化视图中的关联字段需为表的原始字段，且关联字段的数据类型相同，均有INDEX索引。
- 基表为复制表时，不支持增量刷新多表物化视图。

权限要求

创建物化视图需要有数据库或表级别的CREATE权限。
刷新物化视图需要有数据库或表级别的INSERT权限。
需要有物化视图所涉及的所有表的相关列（或整个表）的SELECT权限。
如果在创建物化视图时指定物化视图为自动刷新模式，需要具备通过服务器本地（即127.0.0.1）或者任意IP（即'%'）刷新视图的权限。
如果在创建物化视图时指定物化视图为增量刷新模式，需要有物化视图所涉及的所有基表的ALTER权限。

准备工作

在创建增量刷新的物化视图前，需要执行以下操作：

打开Binlog特性。
重要
- 3.2.0.0及以上内核版本集群默认打开Binlog特性；3.2.0.0以下内核版本集群需执行SET命令手动打开Binlog特性。
- 查看湖仓版集群的内核版本，请执行SELECT adb_version();。如需升级内核版本，请联系技术支持。
- 查看和升级数仓版集群的内核版本，请参见查看和升级版本。
```
SET ADB_CONFIG BINLOG_ENABLE=true;
```
打开基表的Binlog功能。
```
ALTER TABLE <table_name> 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功能。

创建物化视图时指定增量刷新模式

CREATE [OR REPLACE] MATERIALIZED VIEW <mv_name>
[MV DEFINITION]
REFRESH FAST [ON DEMAND] [START WITH date] <NEXT date>
AS
<QUERY BODY>

关键字说明

关键字	是否必填	说明
`OR REPLACE`	否	根据是否存在重名物化视图选择对应的规则来创建物化视图，具体规则如下：若不存在重名物化视图，AnalyticDB for MySQL会直接创建一个新视图。若存在重名物化视图，AnalyticDB for MySQL会先删除原有的重名物化视图，再重新创建。
`mv_name`	是	物化视图名称。
`[MV DEFINITION]`	否	定义物化视图中表相关的特性。物化视图使用一张普通的表结构来存储数据，您可以使用该关键字指定物化视图的主键，分区键，索引等。增量刷新的物化视图会自动生成主键。您也可以手动指定主键，主键生成的规则如下：带GROUP BY的场景，主键必须为GROUP BY列。例如，`GROUP BY a,b`，主键必须是a和b。不带GROUP BY的聚合操作场景，主键需为常量主键，确保全局唯一。不带GROUP BY的非聚合操作场景，主键需为基表的主键。说明与创建表的语法一致，创建物化视图时也不支持定义查询中没有输出的列。
`REFRESH FAST`	是	指定刷新模式为增量刷新。
`ON DEMAND`	否	按需触发刷新。增量刷新仅支持根据配置的`START WITH date`和`NEXT date`自动触发刷新。
`START WITH date`	否	定义自动刷新物化视图时的首次刷新时间。若不填，默认首次刷新时间为当前时间点。
`NEXT date`	是	定义自动刷新物化视图时的下次刷新时间。
`QUERY BODY`	是	定义物化视图的查询主体。增量刷新的物化视图QUERY BODY中，不允许出现非确定性的表达式作为条件，如：`now()`、`rand()`等。聚合操作支持COUNT、SUM、MAX、MIN、AVG、APPROX_DISTINCT和COUNT(DISTINCT)函数。说明仅3.2.2.1及以上版本，支持MAX、MIN、AVG、APPROX_DISTINCT和COUNT(DISTINCT)函数。使用MAX、MIN、APPROX_DISTINCT或COUNT(DISTINCT)函数时，增量物化视图的基表只允许执行INSERT操作。基表在执行DELETE、UPDATE、REPLACE、INSERT ON DUPLICATE KEY UPDATE等会导致数据删除的操作时会报错，因此基表禁止执行这些操作。 COUNT(DISTINCT)仅支持INTEGER类型。 AVG不支持DECIMAL类型。聚合操作不支持HAVING关键字。除COUNT(DISTINCT)外，其余聚合操作均不支持DISTINCT关键字。

增量刷新单表物化视图

创建基表。

CREATE TABLE tbl0
(a bigint,
 b tinyint,
 c boolean,
 d decimal(15, 2),
 PRIMARY KEY(a))
DISTRIBUTED BY HASH (a);

打开Binlog特性。

SET ADB_CONFIG BINLOG_ENABLE=true;

ALTER TABLE tbl0 binlog=true;

创建增量刷新的物化视图。

创建无聚合操作的单表增量刷新的物化视图，每10秒钟刷新一次，示例如下：

CREATE MATERIALIZED VIEW mv0
REFRESH FAST NEXT now() + INTERVAL 10 second
AS
SELECT a, b, c   #系统自动输出基表主键作为物化视图主键。
FROM tbl0
WHERE d > 1000;

创建分组聚合操作的单表增量刷新的物化视图，每5秒钟刷新一次，示例如下：

CREATE MATERIALIZED VIEW mv1
REFRESH FAST NEXT now() + INTERVAL 5 second
AS
SELECT
   b, c,                # 系统会自动输出GROUP BY列作为物化视图主键。
   COUNT(a) AS cnt_a,   # 聚合输出列。
   sum(d) AS sum_d,     # 聚合输出列。
   b / 100 AS new_b     # 非聚合输出列可以使用任意表达式。
FROM tbl0
WHERE ifnull(d, 1) > 0  # 条件可以使用任何表达式。
GROUP BY b, c;

创建无分组聚合操作的单表增量物化视图，每分钟刷新一次，示例如下：

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

增量刷新多表物化视图

创建基表，并打开Binlog特性。

CREATE TABLE tbl0 (
  a0 bigint,
  b0 varchar,
  c0 int,
  d0 varchar,
  e0 boolean,
  PRIMARY KEY(a0)
)
DISTRIBUTED BY HASH (a0) BINLOG=true;

CREATE TABLE tbl1 (
  a1 bigint,
  b1 varchar,
  c1 int,
  d1 varchar,
  e1 boolean,
  PRIMARY KEY(a1)
)
DISTRIBUTED BY HASH (a1) BINLOG=true;

CREATE TABLE tbl2 (
  a2 bigint,
  b2 varchar,
  c2 int,
  d2 varchar,
  e2 boolean,
  PRIMARY KEY(a2)
)
DISTRIBUTED BY HASH (a2) BINLOG=true;

创建增量刷新的物化视图。

创建无聚合操作的多表增量刷新的物化视图，每5秒钟刷新一次，示例如下：

CREATE MATERIALIZED VIEW mv3
REFRESH FAST NEXT now() + INTERVAL 5 second
AS
SELECT a0, a1, a2, (c0 + c1 + c2) AS c
FROM tbl0
JOIN tbl1 ON b1 = b0
JOIN tbl2 ON b2 = b1;

创建分组聚合操作的多表增量刷新的物化视图，每10秒钟刷新一次，示例如下：

CREATE MATERIALIZED VIEW mv4
REFRESH FAST NEXT now() + INTERVAL 10 second
AS
SELECT 
d0, d1, d2,                
COUNT(*) AS cnt,           
sum(c1) AS sum_c1,         
sum(c2) AS sum_c2          
FROM tbl0
JOIN (SELECT b1, c1, d1 FROM tbl1) ON b1 = b0
JOIN (SELECT * FROM tbl2 WHERE e2 = true) ON b2 = b1
GROUP BY d0, d1, d2;