基於查詢歷史自動構建物化視圖 - AnalyticDB

在OLAP分析情境中，複雜查詢常面臨資源消耗高和響應延遲長等問題。AnalyticDB for MySQL支援物化視圖功能，使用者無需手動開發複雜的ETL作業，即可通過聲明式文法在一張表中同時定義資料加工邏輯與調度策略，使使用者能夠專註於報表的商務邏輯本身。然而，傳統物化視圖的構建依賴DBA人工分析SQL模式、提取子查詢與構建結果表，隨後需協同業務團隊重新編寫SQL以適配新架構，成本高昂。AnalyticDB for MySQL支援通過分析歷史查詢，能夠自動推薦並建立物化視圖，實現對業務無感的透明查詢加速。

物化視圖智能推薦

物化視圖智能推薦能力，能夠將傳統物化視圖的構建流程自動化：

智能分析與推薦。自動分析叢集近期的SQL查詢歷史（預設為大於1s的慢查詢），根據使用者設定的慢查詢閾值或每個子查詢能夠關聯的Pattern（SQL文本去常量後的模板化運算式，用來歸類總結相似的查詢）數量等參數，智能化提煉出可複用的公用子查詢。
量化評估收益。觀測公用子查詢的SQL文本及歷史收益，判斷是否需要建立為物化視圖。
一鍵建立。通過控制台一鍵建立物化視圖，並啟用透明改寫。

物化視圖建立後，會在後台自動定期重新整理。對於定時更新的底層未經處理資料，可以在物化視圖中定義更短的調度間隔，以維持物化視圖結果資料和底層基表資料的一致性。

適用情境

在OLAP情境中，自動物化視圖（AutoMV）能顯著降低複雜查詢的資源消耗並提升效率。例如：

固定報表開發：系統自動預計算每日銷售資料，業務人員可秒級擷取報表結果，無需等待Realtime Compute。
使用者行為分析：每小時更新使用者特徵快照，資料分析師無需手動關聯大表，即可快速提取特徵。
即時風控預警：通過緩衝關鍵計量，系統可在毫秒級觸發交易風險預警，避免高峰時段資源爭搶。
多維資料切入：自動適配時間、地區、品類等多維分析需求，業務人員無需依賴IT團隊即可靈活探索資料。

核心優勢

節省成本：避免重複掃描全量資料，減少計算資源浪費。
加速響應：查詢延遲從分鐘級降至秒級，支援即時決策。
簡化營運：系統自動維護預計算結果，無需開發複雜的ETL流程。
靈活擴充：業務側可自主調整分析需求，無需反覆申請資料支援。

這種模式讓企業既能高效利用現有資料資源，又能專註於業務創新而非底層技術實現。

實現原理

識別與歸納查詢模式。

系統將自動解析歷史SQL，剔除其中的常量，將其抽象為標準化的查詢範本。

例如，以下5個對students的單表查詢，過濾條件及投影列各異。

-- sql1/pattern1
SELECT gender, count(*) FROM students WHERE graduated = 'true' GROUP BY gender;

-- sql2/pattern1
SELECT gender, count(*) FROM students WHERE graduated = 'false' GROUP BY gender;

-- sql3/pattern2
SELECT s.age as a, count(s.id) as cnt FROM STUDENTS s WHERE s.gender between 1 and 3;
  
-- sql4/pattern2
SELECT s.age as a, count(s.id) as cnt FROM STUDENTS s WHERE s.gender between 3 and 6;

-- sql5/pattern3
SELECT gender, avg(age) FROM students GROUP BY gender;

如果上述查詢，在記錄中均大於使用者預期的慢查詢閾值，系統會將其歸納為三個可複用的查詢模式（pattern）。

-- pattern1
SELECT gender, count(*) FROM students WHERE graduated = ? GROUP BY gender;

-- pattern2
SELECT s.age as a, count(s.id) as cnt FROM STUDENTS s WHERE s.gender between ? and ?;
  
-- pattern3
SELECT gender, avg(age) FROM students GROUP BY gender;

合并與產生公用子查詢。
AnalyticDB for MySQL的最佳化器會先為每類pattern會產生一個對應的臨時物化視圖，並判斷是否可以合并。最終將基於同一基表的多個模式合并，產生一個公用子查詢。將這個子查詢物化後，便可以加速原始SQL的效能。
```
SELECT gender, graduated, count(id), avg(age), count(*)
FROM STUDENTS
GROUP BY gender,graduated;
```

版本限制

叢集核心版本為3.2.5及以上。

說明

請在雲原生資料倉儲AnalyticDB MySQL控制台集群信息頁面，配寘資訊地區，查看和升級核心版本。

操作步驟

步驟一：建立子查詢推薦任務

建立一個定時任務，該任務會定期掃描歷史查詢，並分析叢集裡相似的子查詢。

說明

任務建立和運行不消耗叢集的ACU資源，也不會產生費用。
建議您手動開啟，輔助開發人員定期觀測叢集查詢歷史的可複用性。

登入雲原生資料倉儲AnalyticDB MySQL控制台，在左上方選擇叢集所在地區。在左側導覽列，單擊集群清單。
單擊目的地組群ID，在左側導覽列，單擊數據管理 > 物化視圖自動推薦。
在任務管理頁簽下，單擊右上方的創建任務。

配置任務參數，單擊確定。

參數		說明
任務運行周期		本文樣本：每天。
任務啟動時間		本文樣本：00:00:00。
掃描查詢範圍(天)		預設每次掃描過去3天的查詢歷史。
子查詢篩選條件說明叢集核心版本為3.2.6及以上支援修改此參數。	慢查詢閾值	如果一個查詢語句的耗時高於這個閾值，才能被這個任務掃描到。一般建議配置為1s，支援按需修改。
	最小可加速的Pattern數量	預設配置為1。
	Pattern配對嘅最少慢查詢次數	預設配置為5。

說明

如果您不確定如何配置參數，可以建立多個不同配置的任務並行運行，以對比和調試效果（任務運行不佔用實際的計算資源）。
目前僅支援從單表查詢裡構建子查詢。WITH CTE裡的子查詢也會納入推薦範圍，但多表Join部分不參與推薦和統計。

（可選）手動觸發任務。任務建立後，您可以等待下一個調度周期自動調度，或手動觸發以提前驗證和觀測效果。
單擊目標任務所在行操作列的手動運行。任務的運行比較快，一般在秒級或者分鐘級就會完成結果推薦。

步驟二：查看任務推薦結果

任務運行完成後，您可以通過以下方式查看並評估推薦的子查詢，以判斷其最佳化價值。

通過控制台查看推薦結果

單擊目標任務所在行操作列的詳情。

單擊相應任務運行歷史所在行操作列的查看子查詢，可以通過控制台查看本次調度的輸出結果。

推薦結果	說明
子查詢	系統從查詢歷史裡抽取出來的完整子查詢query body。
成功加速的查詢數量	本次運行，掃描的若干原始查詢中，可以加速的query數量佔比。
節約掃描的位元組數量	本次運行，如果藉助這個子查詢進行提前構建物化視圖（預計算），可以節約的掃描的位元組數量。
關聯的基礎資料表	查詢命中的base表。

通過SQL搜尋推薦結果

為實現更靈活的篩選和分析，您可以通過SQL查看推薦結果。例如，以下SQL可以查詢特定任務在某天針對lineitem表相關的子查詢。

-- 使用者可通過sql查詢系統資料表須版本大於等於326
SELECT statement AS mv_define, can_refresh_fast, hit_queries_cnt, est_saved_scan_bytes
FROM INFORMATION_SCHEMA.MV_RECOMMEND_RESULTS
WHERE 
task_name = 'task_xxxxx'
AND base_tables LIKE '%lineitem%'
AND DATE(create_time) = '2025-09-11';

步驟三：基於子查詢快速構建物化視圖

單擊目標子查詢所在行操作列的生成物化視圖。
輸入物化視圖的名稱後，即可快速產生一張物化視圖的DDL，您只需複製到SQL編輯器或相應用戶端執行即可建立。
AnalyticDB for MySQL通過智能化手段以及覆蓋大部分情境的通用配置，簡化物化視圖DDL定義：
- 是否開啟查詢改寫：建立物化視圖時預設啟用，儘可能加速單表查詢。
- 重新整理任務執行模式：系統會自動解析SQL語句的類型，並識別是否可以通過增量重新整理來運行。如果可以，會預設配置重新整理任務執行模式為FAST（增量重新整理），重新整理過程裡吞吐效率最優。
- 資源群組：如果物化視圖可以增量重新整理，此處預設會基於Interactive資源群組來執行重新整理，重新整理效能最優；如果物化視圖只能基於全量重新整理，則預設會基於名為Job的Serverless資源群組執行重新整理（僅重新整理任務運行期間涉及資源拉起，成本最優）。
- 重新整理間隔：如果物化視圖可以增量重新整理，此處預設5分鐘執行一次重新整理；如果物化視圖只能基於全量重新整理，預設2個小時重新整理一次。
如果您對物化視圖的定義策略有更個人化的配置訴求，比如自訂欄名稱或者修改更加頻繁的重新整理間隔，可以在DDL的基礎上進行二次修改。

步驟四：觀測物化視圖的線上收益

物化視圖發布上線後，最佳化器會自動改寫部分語句的執行並重新導向至物化視圖，從而提升query的執行效率，實現透明加速。

物化視圖改寫能力，存在全域開關MV_QUERY_REWRITE_ENABLED，預設開啟，無需手動再次進行全域開啟，僅需開啟物化視圖的表層級開關。

查看隱式叫用次數

在叢集詳情頁，左側導覽列，單擊數據管理 > 數據目錄。
單擊目標資料庫。
在物化視圖頁簽下，單擊希望查看的物化視圖。
查看物化視圖最近7天的隱式叫用次數，此參數代表了最佳化器成功改寫並路由到這個物化視圖的用戶端查詢次數。

通過SQL執行計畫查看加速效果

執行EXPLAIN命令分析原始查詢。如果輸出結果中的TableScan運算元顯示為物化視圖的名稱而非原始表名，則證明查詢改寫已生效，查詢已被成功加速。

樣本

EXPLAIN SELECT course_id,course_name,max(course_grade) AS max_grade FROM tb_courses;

-- 可看到輸出結果中， 基表變成了adb_mv的名字，代表改寫生效
+---------------+
| Plan Summary |
+---------------+
 1- Output[ Query plan ] {Est rowCount: 1.0}
 2    -> Exchange[GATHER] {Est rowCount: 1.0}
 3        - TableScan {table: adb_mv, Est rowCount: 1.0}