MaxCompute：物化視圖查詢改寫

樣本一：改寫帶過濾條件的查詢語句

1. 建立物化視圖。

   CREATE MATERIALIZED VIEW mv AS SELECT a,b,c FROM src WHERE a>5;

2. 基於建立的物化視圖執行查詢語句，查詢改寫對照如下。

   原始查詢語句	改寫後的查詢語句
   SELECT a,b FROM src WHERE a>5;	SELECT a,b FROM mv;
   SELECT a, b FROM src WHERE a=10;	SELECT a,b FROM mv WHERE a=10;
   SELECT a, b FROM src WHERE a=10 AND b=3;	SELECT a,b FROM mv WHERE a=10 AND b=3;
   SELECT a, b FROM src WHERE a>3;	(SELECT a,b FROM src WHERE a>3 AND a<=5) UNION (SELECT a,b FROM mv);
   SELECT a, b FROM src WHERE a=10 AND d=4;	改寫不成功，因為mv中沒有d列。
   SELECT d, e FROM src WHERE a=10;	改寫不成功，因為mv中沒有d、e列。
   SELECT a, b FROM src WHERE a=1;	改寫不成功，因為mv中沒有a=1的資料。

樣本二：改寫帶彙總函式的查詢語句

樣本三：改寫帶JOIN的查詢語句

以上三種語句可以相互結合，如果查詢語句符合改寫條件，就可以改寫。

使用物化視圖查詢改寫的目標是加速查詢，因此MaxCompute會優先運行更好的改寫規則，若改寫後增加了一些操作，導致查詢加速效果不佳，那麼該改寫最後不會被選中。

樣本四：改寫帶LEFT JOIN的查詢語句

1. 建立物化視圖。

   CREATE MATERIALIZED VIEW mv LIFECYCLE 7(
           user_id,
           job,
           total_amount
   ) AS SELECT t1.user_id, t1.job, sum(t2.order_amount) AS total_amount 
         FROM user_info AS t1 LEFT JOIN sale_order AS t2 ON t1.user_id=t2.user_id GROUP BY t1.user_id;

2. 基於建立的物化視圖執行查詢語句，查詢改寫對照如下。

   原始查詢語句	改寫後的查詢語句
   SELECT t1.user_id, sum(t2.order_amout) AS total_amount FROM user_info AS t1 LEFT JOIN sale_order AS t2 ON t1.user_id=t2.user_id GROUP BY t1.user_id;	SELECT user_id, total_amount FROM mv;

樣本五：改寫帶UNION ALL的查詢語句

1. 建立物化視圖。

   CREATE MATERIALIZED VIEW mv LIFECYCLE 7( 
           user_id, 
           tran_amount, 
           tran_date 
   ) AS SELECT user_id, tran_amount, tran_date FROM alipay_tran UNION ALL 
   SELECT user_id, tran_amount, tran_date FROM unionpay_tran;

2. 基於建立的物化視圖執行查詢語句，查詢改寫對照如下。

   原始查詢語句	改寫後的查詢語句
   SELECT user_id, tran_amount FROM alipay_tran UNION ALL SELECT user_id, tran_amount FROM unionpay_tran;	SELECT user_id, total_amount FROM mv;

樣本六：情境樣本

1. 情境假設

   假設有一張頁面訪問表visit_records，記錄了各個使用者訪問的頁面ID、使用者ID、訪問時間。使用者經常要對不同頁面的訪問量進行查詢分析。

   此時，可以給visit_records表建立一個以頁面ID分組，統計各個頁面訪問次數的物化視圖，並基於物化視圖執行後續查詢操作。

   visit_records的結構如下：

   +------------------------------------------------------------------------------------+
   | Field           | Type       | Label | Comment                                     |
   +------------------------------------------------------------------------------------+
   | page_id         | string     |       |                                             |
   | user_id         | string     |       |                                             |
   | visit_time      | string     |       |                                             |
   +------------------------------------------------------------------------------------+

2. 建立物化視圖。

   -- 給visit_records表建立一個以頁面ID分組，統計各個頁面訪問次數的物化視圖。
   
   CREATE MATERIALIZED VIEW count_mv AS SELECT page_id, count(*) FROM visit_records GROUP BY page_id;

3. 執行查詢語句如下。

   SET odps.sql.materialized.view.enable.auto.rewriting=true; 
   SELECT page_id, count(*) FROM visit_records GROUP BY page_id;

   執行該查詢語句時，MaxCompute能自動匹配到物化視圖count_mv，從count_mv中讀取彙總好的資料。

4. 執行如下命令檢驗查詢語句是否匹配到物化視圖。

   EXPLAIN SELECT page_id, count(*) FROM visit_records GROUP BY page_id;

   返回結果如下：

   job0 is root job
   
   In Job job0:
   root Tasks: M1
   
   In Task M1:
       Data source: doc_test_dev.count_mv
       TS: doc_test_dev.count_mv
           FS: output: Screen
               schema:
                 page_id (string)
                 _c1 (bigint)
   
   
   OK

   從返回結果中的Data source可查看到當前查詢讀取的表是doc_test_dev專案下的count_mv，說明物化視圖有效，查詢改寫成功。