增量重新整理會通過特定演算法,只計算變更部分的資料,然後定向更新物化視圖中的資料。增量重新整理相較於全量重新整理開銷更低,可以更高頻地更新。如果您僅需要重新整理變更的資料,可以選擇增量重新整理物化視圖。本文介紹如何在建立物化視圖時指定增量重新整理模式。
前提條件
增量重新整理物化視圖時,叢集核心版本應滿足以下條件:
增量重新整理單表物化視圖時,叢集的核心版本需為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都支援的,有一些限制。
增量重新整理可以代替一部分Realtime Compute的需求,尤其是對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>
關鍵字說明
關鍵字 | 是否必填 | 說明 |
| 否 | 根據是否存在重名物化視圖選擇對應的規則來建立物化視圖,具體規則如下:
|
| 是 | 物化視圖名稱。 |
| 否 | 定義物化視圖中表相關的特性。物化視圖使用一張普通的表結構來儲存資料,您可以使用該關鍵字指定物化視圖的主鍵,分區鍵,索引等。 增量重新整理的物化視圖會自動產生主鍵。您也可以手動指定主鍵,主鍵產生的規則如下:
說明 與建立表的文法一致,建立物化視圖時也不支援定義查詢中沒有輸出的列。 |
| 是 | 指定重新整理模式為增量重新整理。 |
| 否 | 按需觸發重新整理。增量重新整理僅支援根據配置的 |
| 否 | 定義自動重新整理物化視圖時的首次重新整理時間。若不填,預設首次重新整理時間為目前時間點。 |
| 是 | 定義自動重新整理物化視圖時的下次重新整理時間。 |
| 是 | 定義物化視圖的查詢主體。
|
增量重新整理單表物化視圖
建立基表。
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;