AnalyticDB：重新整理物化視圖

開啟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;

單表物化視圖

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