增量重新整理物化視圖 - AnalyticDB

增量重新整理會通過特定演算法，只計算變更部分的資料，然後定向更新物化視圖中的資料，且增量重新整理相較於全量重新整理開銷更低，可以更高頻地更新。如果您僅需要重新整理變更的資料，可以選擇增量重新整理物化視圖。本文介紹如何在建立物化視圖時指定增量重新整理模式。

前提條件

增量重新整理物化視圖時，叢集核心版本應滿足以下條件：

增量重新整理單表物化視圖時，叢集的核心版本需為3.1.9.0及以上版本。
增量重新整理多表物化視圖時，叢集的核心版本需為3.2.1.0及以上版本。

說明

如何查看叢集核心版本，請參見如何查看執行個體版本資訊。如需升級核心版本，請聯絡支援人員。

適用情境

物化視圖常用來加速複雜查詢或者簡化ETL流程，物化視圖的本質是將使用者定義的查詢提前計算好，按使用者要求自動重新整理視圖中的資料。重新整理的方式一般分為兩種，全量重新整理和增量重新整理。兩者區別如下：

全量重新整理每次重新運行SQL，將算好的新資料全量覆蓋舊資料。
增量重新整理通過特定演算法，每次只計算新變更部分的資料，然後定向更新物化視圖中的部分資料。

因此，增量重新整理往往開銷更低，可以更高頻的更新。從更新機制上看，增量重新整理是優於全量重新整理的，但其實並不是所有情境都適合增量重新整理。它們各自有適用的情境。

全量重新整理適用於T+1類的離線情境。常見的適用情境如下：

資料每天批次更新一次，或者小時層級更新一次，更適合全量重新整理。這類SQL通常比較複雜，全量重新整理對SQL文法沒有任何約束。
在某些分鐘層級延遲的情境中也可以使用全量重新整理，往往這類SQL即使全量計算成本也不大，通常十幾秒能完成，還可以達到一定的即時效果。

增量重新整理適用於即時情境。常見的適用情境如下：

資料即時資料流入。
需要即時更新的報表或ETL。
對資料延遲要求高的秒級延遲需求。

物化視圖本質是過去某一時刻的資料查詢結果，重新整理的延遲越低，就越接近實際結果。一些數倉會保證強一致，但維護成本高，基表寫入效能也不穩定。AnalyticDB for MySQL是全非同步演算法，支援任意DML，不影響基表寫入。

累加式更新成本更低，是勝在只算增量。如果計算相同的資料量，由於演算法更複雜，計算成本要比全量重新整理高。所以在T+1類定期大批資料量計算的情境，並不合適。並且要能夠維護查詢資料的累加式更新，並不是任意SQL都支援的，有一些限制。

增量重新整理可以代替一部分Realtime Compute的需求，尤其是對SQL查詢語意有要求的（要求增量重新整理物化視圖的結果和查詢基表的結果完全一致），使用增量重新整理不需要您額外維護流引擎組件，成本更低。

使用限制

增量重新整理只支援自動重新整理，不允許手動重新整理。增量重新整理模式下的自動重新整理間隔最短5秒（s），最長5分鐘（min）。
由於物化視圖要保證結果和您查詢基表的結果完全一致，且要支援任意DML變更，所以並不是所有QUERY BODY都可以增量重新整理。如果建立的物化視圖無法增量重新整理，建立時會報錯。
增量重新整理多表物化視圖時，您還需注意以下內容：
- 多表物化視圖目前僅支援使用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特性。
如何查看叢集核心版本，請參見如何查看執行個體版本資訊。如需升級核心版本，請聯絡支援人員。
```
SET ADB_CONFIG BINLOG_ENABLE=true;
```
開啟基表的Binlog功能。
```
ALTER TABLE <table_name> binlog=true;
```
重要
- 開啟Binlog功能的表無法執行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函數，AVG可通過SUM和COUNT實現，不能對彙總操作的結果進行投影。彙總操作不支援HAVING關鍵字。彙總操作不支援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;