多表Join和複雜彙總計算通常消耗較多計算資源，且查詢耗時間長度。AnalyticDB for MySQL物化視圖可以解決此類問題。物化視圖會將使用者定義的查詢提前計算好並將查詢結果儲存起來。查詢時可以直接從物化視圖中讀取預先計算好的查詢結果，從而加快查詢響應速度。本文主要介紹如何建立物化視圖。
前提條件
核心為3.1.3.4及以上版本。
請在雲原生資料倉儲AnalyticDB MySQL控制台集群信息頁面，配寘資訊地區，查看和升級核心版本。
建立物化視圖
許可權要求
建立物化視圖的使用者需要具備以下所有許可權：
物化視圖所在資料庫的建立表的許可權（CREATE許可權）。
物化視圖所有基表的相關列（或整個表）的SELECT許可權。
如需建立自動重新整理的物化視圖，還需要具備以下兩個許可權：
從任意IP（即
'%'）串連AnalyticDB for MySQL的許可權。物化視圖或物化視圖所在資料庫所有表的INSERT許可權，否則物化視圖中的資料無法重新整理。
準備用於建立物化視圖的基表
下文以customer表和sales表為例，指導您快速建立物化視圖。
/*+ RC_DDL_ENGINE_REWRITE_XUANWUV2=false */ -- 指定表的引擎為XUANWU引擎。
CREATE TABLE customer (
customer_id INT PRIMARY KEY,
customer_name VARCHAR(255),
is_vip Boolean
);/*+ RC_DDL_ENGINE_REWRITE_XUANWUV2=false */ -- 指定表的引擎為XUANWU引擎。
CREATE TABLE sales (
sale_id INT PRIMARY KEY,
product_id INT,
customer_id INT,
price DECIMAL(10, 2),
quantity INT,
sale_date TIMESTAMP
);下文建立物化視圖的樣本未指定資源群組。當未指定資源群組時，由預設資源群組（Interactive型資源群組）的計算資源來建立和重新整理物化視圖。如果您想瞭解Interactive資源群組和Job型資源群組在建立和重新整理物化視圖的區別，或者想使用Job型資源群組建立和重新整理物化視圖，請參見使用彈性資源建立或重新整理物化視圖。
建立全量重新整理的物化視圖
您可以基於AnalyticDB for MySQL內表、外表、已有的物化視圖和視圖建立全量重新整理的物化視圖（以下簡稱全量物化視圖）。
本文以基表customer和sales為例，建立全量物化視圖join_mv，重新整理觸發機製為手動重新整理。
CREATE MATERIALIZED VIEW join_mv
REFRESH COMPLETE ON DEMAND
AS
SELECT
sale_id,
SUM(price * quantity) AS price
FROM customer
INNER JOIN (SELECT sale_id,customer_id,price,quantity FROM sales) sales ON customer.customer_id = sales.customer_id
GROUP BY customer.customer_id;如需更新物化視圖的資料，請手動執行REFRESH MATERIALIZED VIEW join_mv;。
建立增量重新整理的物化視圖
您可以基於AnalyticDB for MySQL內表建立增量重新整理的物化視圖（以下簡稱增量物化視圖）。
下文將指導您建立一個多表關聯的增量物化視圖。
在建立增量物化視圖之前，請完成以下準備工作：
檢查叢集的核心版本是否滿足3.1.9.0及以上版本。
開啟叢集層級的Binlog特性及基表的Binlog功能。
如果開啟基表的Binlog功能報錯，解決方案請參見下文的Query execution error: : Can not create FAST materialized view, because demotable doesn't support getting incremental data。
SET ADB_CONFIG BINLOG_ENABLE=true; ALTER TABLE customer binlog=true; ALTER TABLE sales binlog=true;
建立增量物化視圖sales_mv_incre。該物化視圖只涉及sales表的查詢計算。
CREATE MATERIALIZED VIEW sales_mv_incre
REFRESH FAST NEXT now() + INTERVAL 3 minute
AS
SELECT
sale_id,
SUM(price * quantity) AS price
FROM sales
GROUP BY sale_id;如果叢集的核心版本為3.2.1.0及以上版本，您還可以建立多表關聯的增量物化視圖join_mv_incre，重新整理間隔為每3分鐘自動重新整理一次。
CREATE MATERIALIZED VIEW join_mv_incre
REFRESH FAST NEXT now() + INTERVAL 3 minute
AS
SELECT
customer.customer_id,
SUM(sales.price) AS price
FROM customer
INNER JOIN (SELECT customer_id,price FROM sales) sales ON customer.customer_id = sales.customer_id
GROUP BY customer.customer_id;物化視圖的文法詳情和更多樣本，請參見CREATE MATERIALIZED VIEW。
查看建立中的物化視圖
通過SHOW PROCESSLIST WHERE info LIKE '%CREATE MATERIALIZED VIEW%';，可以查詢正在建立的物化視圖。
返回結果的每一行記錄代表一個正在建立的物化視圖。其中，user為建立物化視圖的資料庫帳號，status為建立的狀態，Info包含了物化視圖的建立語句。更多關於返回結果的欄位資訊，請參見SHOW PROCESSLIST。
如果SHOW PROCESSLIST未查詢到任何記錄，說明物化視圖已建立完成（包括已建立物化視圖的表結構，且物化視圖中已載入初始資料）。
建立物化視圖時，基表查詢怎麼寫
全量物化視圖的基表查詢
全量重新整理的物化視圖，基表可以是AnalyticDB for MySQL內表、外表、已有的物化視圖和視圖。基表的查詢沒有限制。查詢文法，可以參考SELECT。
增量物化視圖的基表查詢
增量重新整理的物化視圖，基表只能是AnalyticDB for MySQL內表。基表的查詢有以下規則：
SELECT輸出資料行
分組彙總查詢（帶GROUP BY的彙總查詢），SELECT的輸出資料行必須包含GROUP BY子句中的所有分組列。
非分組彙總查詢（不帶GROUP BY的彙總查詢），SELECT僅輸出常量列與彙總列或僅輸出彙總列。
非彙總查詢，SELECT必須輸出基表的所有主鍵列。
UNION ALL 查詢，不支援與彙總函式一起使用，每一路輸入都需要輸出一個名為“union_all_marker”的列，該列必須是常量，且每一路該列的輸出必須是不同值，否則物化視圖將不能保證資料正確性。SELECT必須輸出基表的所有主鍵列，並將基表主鍵以及union_all_marker列一起設為物化視圖主鍵。
CREATE MATERIALIZED VIEW demo_union_all_mv (PRIMARY KEY(id, union_all_marker)) REFRESH FAST NEXT now() + INTERVAL 5 minute AS SELECT customer_id as id, "customer" as union_all_marker FROM customer UNION ALL SELECT sale_id as id, "sales" as union_all_marker FROM sales;輸出的運算式列必須定義別名。建議使用具有實際意義的別名，例如
SUM(price) AS total_price。
其他限制
使用彈性資源建立或重新整理物化視圖
彈性資源是指Job型資源群組的計算資源。建立以及後續重新整理物化視圖，都會消耗叢集的計算資源。在未指定資源群組（未定義MV_PROPERTIES='{"mv_resource_group":"rg_name"}'）的情況下，建立和重新整理物化視圖會使用預設Interactive型資源群組（名稱為user_default的資源群組）的預留計算資源。
如果建立物化視圖時，指定了Job型資源群組，那麼建立和後續重新整理物化視圖，都會使用該Job型資源群組。同時，您可以通過"mv_refresh_hints":{"elastic_job_max_acu":"value"}定義物化視圖可以使用的資源量，用法詳情請參見mv_properties。
使用預設Interactive型資源群組和Job型資源群組的區別在於：使用Job型資源群組，無需提前購買資源，有效避免了資源浪費，因此可降低成本。但物化視圖的重新整理效能不如Interactive型資源群組，因為每次重新整理物化視圖，Job型資源群組都需要臨時拉起計算資源，拉起資源的時間大約為秒級或分鐘級。
如果叢集滿足以下條件，您可以使用彈性資源去建立和重新整理物化視圖。
叢集的產品系列為企業版、基礎版或湖倉版。
叢集核心版本需為3.1.9.3及以上版本。
以上文的customer表為例，使用Job型資源群組my_job_rg的彈性資源建立和重新整理物化視圖，且優先順序為高。
CREATE MATERIALIZED VIEW job_mv
MV_PROPERTIES='{
"mv_resource_group":"my_job_rg",
"mv_refresh_hints":{"query_priority":"HIGH"}
}'
REFRESH COMPLETE ON DEMAND
START WITH now()
NEXT now() + INTERVAL 1 DAY
AS
SELECT * FROM customer;物化視圖的重新整理觸發機制
物化視圖反映最近一次重新整理的資料狀態，可能不是基表的最新資料。物化視圖也不會在每次查詢時自動重新整理資料。為了確保物化視圖資料的及時性和準確性，您可以設定物化視圖定時自動重新整理、基表覆蓋寫時自動重新整理，或者手動重新整理。
重新整理物化視圖時，根據物化視圖的重新整理策略，重新整理全量資料或增量資料。
關於重新整理觸發機制和重新整理策略的區別、應用情境等更多詳情，請參見重新整理物化視圖。
使用限制
通用限制
通用限制適用於所有物化視圖，包括全量物化視圖和增量物化視圖。
不支援對物化視圖執行
INSERT、DELETE或UPDATE操作。不支援刪除或重新命名物化視圖的基表或基表中的列。如需修改基表需要先刪除物化視圖。
預設情況下，一個AnalyticDB for MySQL叢集支援的物化視圖數量上限如下：
核心版本等於或大於3.1.4.7：最多支援建立64個物化視圖。
核心版本低於3.1.4.7：最多支援建立8個物化視圖。
說明如果物化視圖的數量已達到上限，您可以聯絡支援人員，申請提高物化視圖數量上限。
全量物化視圖的限制
叢集增加或減少預留節點數期間，無法執行非同步任務。物化視圖全量重新整理為非同步任務，因此此時無法全量重新整理。增量重新整理不受影響。
增量物化視圖的限制
增量物化視圖對基表的限制：
核心版本為3.2.6.0以下的叢集，不支援XUANWU_V2表作為增量物化視圖的基表。
原因為XUANWU_V2引擎的表暫不支援開啟Binlog。
核心版本為3.2.3.0以下的叢集，不支援分區表作為增量物化視圖的基表。
核心版本為3.2.3.1以下的叢集，增量物化視圖的基表不支援INSERT OVERWRITE和TRUNCATE，執行會報錯。
當query_body中使用MAX、MIN、APPROX_DISTINCT或COUNT(DISTINCT)彙總函式時，增量物化視圖的基表只支援INSERT操作，不支援DELETE、UPDATE、REPLACE、INSERT ON DUPLICATE KEY UPDATE等會導致資料刪除的操作。
核心版本為3.2.5.0及以上的叢集，物化視圖支援開啟Binlog功能，因此可以作為基表，即增量物化視圖可以嵌套使用。
增量物化視圖的重新整理觸發機制的限制：
只支援定時自動重新整理，不支援手動重新整理。重新整理間隔最短5秒（s），最長5分鐘（min）。
增量物化視圖，query_body的規則與限制：
不支援非確定性運算式，如：NOW()、RAND()等。
不支援ORDER BY排序操作。
不支援HAVING子句。
不支援視窗函數。
不支援UNION、EXCEPT、INTERSECT等集合操作。3.2.5.0及以上版本支援UNION ALL。
JOIN操作僅支援INNER JOIN。關聯欄位須滿足所有條件：須為表的原始欄位，資料類型須相同，且有INDEX索引。最多關聯5張表。
如需關聯更多表，請聯絡支援人員。
僅支援以下彙總函式：COUNT、SUM、MAX、MIN、AVG、APPROX_DISTINCT和COUNT(DISTINCT)。
AVG不支援DECIMAL類型。
COUNT(DISTINCT)僅支援INTEGER類型。
常見問題
物化視圖如何只保留最近一年的資料?
在定義物化視圖時，使用日期列作為分區鍵（PARTITION BY）並設定生命週期（LIFECYCLE），可實現僅保留最近一年的資料。
假設sales表每天都會產生新的銷售資料，那麼物化視圖使用銷售日期（sales_date）作為分區鍵，並設定生命週期為365（即最多保留365個分區），即可實現僅保留最近一年的資料。
CREATE MATERIALIZED VIEW sales_mv_lifecycle
PARTITION BY VALUE(DATE_FORMAT(sale_date, '%Y%m%d')) LIFECYCLE 365
REFRESH FAST NEXT now() + INTERVAL 100 second
AS
SELECT
sale_date,
SUM(price * quantity) AS price
FROM sales
GROUP BY sale_date;常見報錯
Query execution error: : Can not create FAST materialized view, because demotable doesn't support getting incremental data
報錯原因：增量重新整理的物化視圖，基表demotable沒有開啟Binlog功能。增量重新整理的物化視圖，需要所有涉及的基表開啟Binlog功能。
解決方案：執行ALTER TABLE demotable binlog=true;開啟基表的Binlog功能。
如果開啟基表的Binlog時，出現報錯XUANWU_V2 engine not support ALTER_BINLOG_ENABLE now，說明基表demotable的引擎是XUANWU_V2，而XUANWU_V2引擎暫不支援Binlog功能。由於表的引擎無法變更，您需要關閉XUANWU_V2引擎的開關，重新建立XUANWU引擎的表，將XUANWU_V2表的資料移轉到XUANWU表，並基於XUANWU表建立增量重新整理的物化視圖。
關閉XUANWU_V2引擎的開關，方法如下：
如果基表是通過DTS、無感整合、控制台資料同步等工具自動建立的，那麼您可以在叢集層級關閉XUANWU_V2引擎的開關。關閉後，建立的表都會是XUANWU引擎。
在叢集全域，關閉XUANWU_V2引擎：
SET adb_config RC_DDL_ENGINE_REWRITE_XUANWUV2=false;如果基表是通過CREATE TABLE手動建立的，且需要建立增量物化視圖的基表數量不多，那麼可以選擇當前建表語句關閉XUANWU_V2引擎的開關。僅當前建表語句，建立的表是XUANWU引擎。而其他建表語句，建立的表仍是XUANWU_V2引擎。
在表層級，關閉XUANWU_V2引擎：
/*+ RC_DDL_ENGINE_REWRITE_XUANWUV2=false */CREATE TABLE ...
Query execution error: : PRIMARY KEY id must output to MV.
報錯原因：增量重新整理的物化視圖查詢（query_body）為無GROUP BY的非彙總查詢，此時物化視圖的主鍵需要與基表的主鍵相同，但物化視圖查詢的SELECT列表中沒有輸出基表的主鍵列。
錯誤樣本如下：
CREATE MATERIALIZED VIEW wrong_example1
REFRESH FAST ON DEMAND
NEXT now() + interval 200 second
AS
SELECT product_id,price -- 基表sales的主鍵為sale_id，但SELECT列表中沒有sale_id。
FROM sales;解決方案：在物化視圖查詢的SELECT列表中輸出基表的主鍵列。
正確樣本為：
CREATE MATERIALIZED VIEW correct_example1
REFRESH FAST ON DEMAND
NEXT now() + interval 200 second
AS
SELECT sale_id,product_id, price -- 在SELECT列表中添加基表主鍵sale_id。
FROM sales;Query execution error: : MV PRIMARY KEY must be equal to base table PRIMARY KEY.
報錯原因：增量重新整理的物化視圖查詢（query_body）為無GROUP BY的非彙總查詢，此時物化視圖的主鍵需要與基表的主鍵相同，但物化視圖的定義（mv_definition）中，將基表主鍵和非基表主鍵的列定義為了物化視圖的主鍵。
錯誤樣本如下：
CREATE MATERIALIZED VIEW wrong_example2
(PRIMARY KEY(sale_id,product_id)) -- product_id不是基表sales的主鍵列。
REFRESH FAST ON DEMAND
NEXT now() + interval 200 second
AS
SELECT sale_id,product_id,price
FROM sales;解決方案：修改物化視圖的主鍵，將不是基表主鍵的列移除。
正確樣本如下：
CREATE MATERIALIZED VIEW correct_example2
(PRIMARY KEY(sale_id)) -- 物化視圖的主鍵列移除product_id。
REFRESH FAST ON DEMAND
NEXT now() + interval 200 second
AS
SELECT sale_id,product_id,price
FROM sales;Query execution error: : FAST materialized view must define PRIMARY KEY
報錯原因：報錯可能有以下兩個原因：
增量重新整理物化視圖沒有定義正確的主鍵。
在物化視圖的查詢（query_body）中對基表的主鍵列使用了函數。
解決方案：請根據不同的原因，選擇合適的解決方案。
沒有定義正確的主鍵：修改物化視圖的定義（mv_definition），確保主鍵滿足如下規則。
分組彙總查詢（帶有GROUP BY的彙總查詢），主鍵必須為GROUP BY列。例如，
GROUP BY a,b，主鍵必須是a和b。非分組彙總查詢（無GROUP BY的彙總查詢），主鍵必須為常量。
非彙總查詢，主鍵必須與基表的主鍵完全相同。例如，基表主鍵為PRIMARY KEY(sale_id,sale_date)，那麼物化視圖的主鍵也必須為PRIMARY KEY(sale_id,sale_date)。
基表的主鍵列使用了函數：修改物化視圖查詢（query_body），刪除基表主鍵列的函數。
Query execution error: : The join graph is not supported.
報錯原因：增量重新整理的物化視圖查詢（query_body）中多表Join的關聯列資料類型不同。例如，sales INNER JOIN customer ON customer.id=sales.id，如果customer.id與sales.id資料類型不同，就會出現該報錯。
解決方案：執行ALTER TABLE tablename MODIFY COLUMN columnname newtype;修改多表Join關聯列的資料類型。詳情請參見變更列的資料類型。
Query execution error: : Unable to use index join to refresh this fast MV.
報錯原因：增量重新整理的物化視圖查詢（query_body）中多表Join的關聯列沒有INDEX索引。例如，sales INNER JOIN customer ON customer.id=sales.id，如果基表customer中的customer.id或基表sales中的sales.id沒有建立INDEX索引，就會出現該報錯。
解決方案：執行ALTER TABLE tablename ADD KEY idx_name(columnname);為基表中的列添加索引。詳情請參見增加索引。
Query execution error: : Query exceeded reserved memory limit
報錯原因：查詢在單個節點上佔用的記憶體超過限制。
解決方案：佔用記憶體較高的SQL一般包含Aggregation運算元、TopN運算元、Window運算元以及Join運算元。您可以利用SQL診斷功能，查詢記憶體佔用較高的Stage和運算元，然後對記憶體佔用較高的運算元執行SQL調優。詳情請參見記憶體指標和使用Stage和Task詳情分析查詢。
相關文檔
物化視圖：瞭解物化視圖的概念、使用情境、特性變更記錄。
CREATE MATERIALIZED VIEW：瞭解物化視圖的文法詳情。
重新整理物化視圖：瞭解物化視圖的重新整理策略、觸發機制，以及如何手動重新整理物化視圖。
管理物化視圖：查詢物化視圖的定義和重新整理記錄，查詢所有物化視圖的列表，刪除物化視圖。
查詢物化視圖：查詢物化視圖。