全部產品
Search
文件中心

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

更新時間:Nov 09, 2024

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

前提條件

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

  • 增量重新整理單表物化視圖時,叢集的核心版本需為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許可權。

準備工作

在建立增量重新整理的物化視圖前,需要執行以下操作:

  1. 開啟Binlog特性。

    重要
    • 3.2.0.0及以上核心版本叢集預設開啟Binlog特性;3.2.0.0以下核心版本叢集需執行SET命令手動開啟Binlog特性。

    • 查看湖倉版叢集的核心版本,請執行SELECT adb_version();。如需升級核心版本,請聯絡支援人員。

    • 查看和升級數倉版叢集的核心版本,請參見查看和升級版本

    SET ADB_CONFIG BINLOG_ENABLE=true;
  2. 開啟基表的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 dateNEXT 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關鍵字。

增量重新整理單表物化視圖

  1. 建立基表。

    CREATE TABLE tbl0
    (a bigint,
     b tinyint,
     c boolean,
     d decimal(15, 2),
     PRIMARY KEY(a))
    DISTRIBUTED BY HASH (a);
  2. 開啟Binlog特性。

    SET ADB_CONFIG BINLOG_ENABLE=true;
    ALTER TABLE tbl0 binlog=true;
  3. 建立增量重新整理的物化視圖。

    • 建立無彙總操作的單表增量重新整理的物化視圖,每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;

增量重新整理多表物化視圖

  1. 建立基表,並開啟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;
  2. 建立增量重新整理的物化視圖。

    • 建立無彙總操作的多表增量重新整理的物化視圖,每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;