使用物化視圖加速查詢 - PolarDB

物化視圖（Materialized View）是一種通過預計算和持久化儲存查詢結果來最佳化複雜查詢效能的資料庫物件。通過儲存某些耗時操作的結果，方便在查詢時直接查詢已經預計算好的資料，避免重複執行這些耗時耗資源的操作，通過空間換時間來加速查詢。

PolarDB MySQL版叢集的物化視圖現已支援全量重新整理，並提供定時重新整理與手動重新整理兩種方式。物化視圖採用系統後台重新整理任務機制，可結合列存索引（IMCI）加速查詢，有效提升任務重新整理效率，並減輕唯讀節點的壓力。基於PolarDB的HTAP一體架構，進一步增強了AP（分析）能力。

物化視圖與普通視圖的主要區別在於，物化視圖包含了資料的物理副本，可以直接從物化視圖中查詢資料。從原理上講，物化視圖是由視圖及其背後的物理基表所構成。

建立物化視圖：建表與物化操作被拆分為兩個步驟。物化過程為非同步作業，推送至背景工作，以實現物理基表的物化。物理基表的表名與視圖相關聯，物化重新整理任務包含查詢和插入兩個部分，其中查詢部分可以轉移至列存索引唯讀節點，以加速查詢並減輕任務對主節點的影響。
查詢物化視圖：通過結合視圖與物理基表的方式，完成對實際物理基表的查詢。
重新整理物化視圖：全量重新整理過程需要建立一個新的隱藏物理基表。在隱藏基表上執行重新整理語句後，切換原物理基表與隱藏基表，從而將最新物理基表資訊更新到中繼資料表，並同步至記憶體結構中以供查詢使用。
任務監控與管理：支援查詢後台重新整理任務隊列資訊，並允許對任務進行監控與管理，即在表、庫或全域維度上暫停或重新啟動物化視圖的定時重新整理任務。

適用範圍

PolarDB MySQL版叢集需滿足以下條件之一：

MySQL 8.0.1，且核心小版本需為8.0.1.1.51及以上。
MySQL 8.0.2，且核心小版本需為8.0.2.2.31及以上。

優勢

避免重複計算：對於資源消耗大的複雜查詢，一次計算，多次複用，大幅提高查詢效率。
資料預彙總：可預先完成對日、周、月報等資料的匯總，加速報表產生和資料分析。
最佳化巨量資料查詢：減少對海量未經處理資料的直接掃描，加快分析查詢的響應速度。
支援多維分析：能夠預先計算不同維度組合的彙總資料，為OLAP等多維分析情境提供高效支援。

應用情境

物化視圖通過預計算並儲存查詢結果來最佳化查詢效能，特別適用於處理大規模資料和複雜查詢的情境。它能夠減少Realtime Compute的開銷，同時簡化複雜的查詢邏輯，廣泛應用於快速產生報表和資料分析等領域。

避免重複計算：適合儲存需要頻繁查詢的複雜計算結果，這些查詢通常資源消耗較大，使用物化視圖可以避免反覆計算相同的資料，從而大幅提高查詢效率。
資料預彙總：在報表產生和資料分析等情境中，物化視圖能夠提前匯總日、周或月的銷售資料、使用者行為資料等，預先儲存計算結果，減少Realtime Compute所需的時間和資源。
最佳化巨量資料查詢：當業務情境涉及巨量資料量的分析時，直接查詢未經處理資料可能因頻繁的大量掃描而耗時，而通過物化視圖可以減少直接存取未經處理資料的需求，從而加速查詢和分析。
支援多維分析：物化視圖能夠預先計算不同維度組合的彙總資料，為情境化的多維資料分析提供高效且快速的查詢響應能力。

參數說明

PolarDB叢集參數在控制台與會話中修改方式存在差異，詳細區別如下：

在PolarDB控制台上修改：
- 相容性說明：部分叢集參數在PolarDB控制台上均已添加MySQL設定檔的相容性首碼loose_。
- 操作方法：找到並修改這些帶loose_首碼的參數。
在資料庫會話中修改（使用命令列或用戶端）：
- 操作方法：當您串連到資料庫，使用SET命令修改參數時，請去掉loose_首碼，直接使用參數的原始名稱進行修改。

參數

說明

loose_materialized_view_enabled

物化視圖開關。取值範圍如下：

ON（預設）：開啟
OFF：關閉

loose_enable_materialized_view_parallel

是否開啟物化視圖的並行查詢功能。取值範圍如下：

ON：開啟
OFF（預設）：關閉

建立物化視圖

文法說明

CREATE
  MATERIALIZED VIEW view_name [(column_list)]
  [REFRESH [COMPLETE|FAST]]
  [ON [COMMIT|DEMAND]]
  [START WITH now()] [NEXT now() + interval 1 hour]
  [[DISABLE|ENABLE] QUERY REWRITE]
  AS select_statement

許可權要求

具有物化視圖所在資料庫的CREATE許可權。
具有物化視圖所有基表的相關列（或整個表）的SELECT許可權。

參數說明

參數名	必填	說明
view_name	是	物化視圖的名稱。
column_list	否	物化視圖指定列。若您希望為視圖中的列指定明確的名稱，可以在`column_list`中明確列名，並使用`,`進行分隔。
REFRESH	否	指定物化視圖的重新整理策略。 COMPLETE（預設）：全量重新整理。 FAST：增量重新整理。說明當前僅支援全量重新整理策略。
ON	否	指定物化視圖的物化類型。 COMMIT：即時物化視圖。 DEMAND（預設）：非同步物化視圖。說明當前僅支援非同步物化檢視類型。
START WITH	否	非同步物化視圖的自動重新整理起始時間。
NEXT	否	非同步物化視圖的定時重新整理時間間隔。說明未定義NEXT時，只能手動重新整理。
QUERY REWRITE	否	物化視圖是否支援查詢改寫。 DISABLE（預設）：不支援。 ENABLE：支援。說明當前不支援查詢改寫。
select_statement	是	用於定義物化視圖資料的查詢語句。該語句用於從基表中檢索資料，並將結果儲存到物化視圖中。

樣本

建立表table1和table2作為物化視圖的基表。

-- 建立基表
CREATE TABLE table1 (col1 INT PRIMARY KEY, col2 VARCHAR(20), col3 INT);
CREATE TABLE table2 (col1 INT PRIMARY KEY, col2 VARCHAR(20), col3 INT);

-- 向基表中插入資料
INSERT INTO table1 VALUES (1, 'A', 100), (2, 'B', 200), (3, 'C', 300), (4, 'D', 400);
INSERT INTO table2 VALUES (1, 'X', 10), (2, 'Y', 20), (3, 'Z', 30), (5, 'W', 50);

建立物化視圖。

CREATE MATERIALIZED VIEW mv1 
REFRESH COMPLETE 
ON DEMAND 
START WITH now() NEXT now() + INTERVAL 1 hour 
AS 
SELECT 
    SUM(t1.col3) AS sum_value, 
    AVG(t1.col3) AS avg_value 
FROM table1 t1 
JOIN table2 t2 ON t1.col1 = t2.col1;

查詢物化視圖

說明

查詢物化視圖需具備物化視圖的SELECT許可權，無需具備物化視圖所引用基表的SELECT許可權。

查詢物化視圖

SELECT * FROM mv1;

預期返回結果：

+-----------+-----------+
| sum_value | avg_value |
+-----------+-----------+
|       600 |  200.0000 |
+-----------+-----------+

查詢物化視圖定義

SHOW CREATE VIEW mv1;

預期返回結果：

+------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+
| View | Create View                                                                                                                                                                                                                                                                    | character_set_client | collation_connection |
+------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+
| mv1  | CREATE MATERIALIZED VIEW `mv1` REFRESH COMPLETE ON DEMAND START WITH ('2025-10-15 11:20:15') NEXT (now() + interval 1 hour)  AS select sum(`t1`.`col3`) AS `sum_value`,avg(`t1`.`col3`) AS `avg_value` from (`table1` `t1` join `table2` `t2` on((`t1`.`col1` = `t2`.`col1`))) | utf8mb4              | utf8mb4_0900_ai_ci   |
+------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+

使用系統資料表查詢物化視圖資訊

SELECT * FROM mysql.view_materialized_info;

表欄位說明

欄位	說明
TABLE_SCHEMA	物化視圖所在的資料庫名。
TABLE_NAME	物化視圖的名稱。
IS_DROPPED	是否已刪除。 0：否。 1：是。
FIRST_REFRESH_TIME	如果已配置自動重新整理，則表示首次重新整理的時間。
TIME_ZONE	時區設定。
REFRESH_CONDITION	重新整理的觸發機制。 DEMAND：非同步重新整理，定時自動觸發或手動觸發物化視圖的重新整理。 COMMIT：即時重新整理。
REFRESH_STRATEGY	物化視圖的重新整理策略。 COMPLETE：全量重新整理。 FAST：增量重新整理。
REFRESH_START_TIME	啟動重新整理的時間。
NEXT_TIME_EXPRESSION	非同步物化視圖的下次重新整理時間定義。
LAST_START_TIME	上次實際啟動重新整理的時間。
LAST_END_TIME	上次實際重新整理結束的時間。
CONTAINER_TABLE_POSTFIX	資料存放區物理表的尾碼。
EXPIRED_TABLE_POSTFIX	下一個到期資料存放區物理表的尾碼。
IS_STOPPED	是否已停止定時重新整理。 0：否。 1：是。
CREATE_TIME	物化視圖元資訊的建立時間。
UPDATE_TIME	物化視圖元資訊的更新時間。

預期返回結果：

+-----+--------------+------------+------------+---------------------+-----------+-------------------+------------------+---------------------+---------------------------+---------------------+---------------------+-------------------------+-----------------------+------------+---------------------+---------------------+
| id  | table_schema | table_name | is_dropped | first_refresh_time  | time_zone | refresh_condition | refresh_strategy | refresh_start_time  | next_time_expression      | last_start_time     | last_end_time       | container_table_postfix | expired_table_postfix | is_stopped | create_time         | update_time         |
+-----+--------------+------------+------------+---------------------+-----------+-------------------+------------------+---------------------+---------------------------+---------------------+---------------------+-------------------------+-----------------------+------------+---------------------+---------------------+
| 471 | testmv       | mv1        |          0 | 2025-10-15 11:20:16 | +08:00    | DEMAND            | COMPLETE         | 2025-10-15 11:20:15 | (now() + interval 1 hour) | 2025-10-15 11:20:15 | 2025-10-15 11:20:16 |           2022929596417 |         2022929596417 |          0 | 2025-10-15 11:20:16 | 2025-10-15 11:20:16 |
+-----+--------------+------------+------------+---------------------+-----------+-------------------+------------------+---------------------+---------------------------+---------------------+---------------------+-------------------------+-----------------------+------------+---------------------+---------------------+

重新整理物化視圖

目前，物化視圖僅支援全量重新整理。這一過程採用異地重新整理的方式進行全量重新整理，即建立一個隱藏的物理基表，在該隱藏基表上執行重新整理語句，隨後切換原物理基表與隱藏基表。因此，全量重新整理的操作需要額外的儲存空間。其主要優勢在於能夠支援任意SQL查詢。

物化視圖支援兩種重新整理方式，即手動重新整理和定時重新整理。

手動重新整理

如果物化視圖未配置定時重新整理，或者定時重新整理間隔較大時，可通過手動重新整理命令REFRESH MATERIALIZED VIEW來實現資料重新整理。

REFRESH MATERIALIZED VIEW <view_name>;

樣本

插入新測試資料。

INSERT INTO table1 VALUES (5, 'E', 500), (6, 'F', 600);
INSERT INTO table2 VALUES (7, 'U', 60), (8, 'V', 70);

手動重新整理物化視圖。
```
REFRESH MATERIALIZED VIEW mv1;
```

查詢物化視圖。

SELECT * FROM mv1;

預期返回結果：

+-----------+-----------+
| sum_value | avg_value |
+-----------+-----------+
|      1100 |  275.0000 |
+-----------+-----------+

定時重新整理

在建立物化視圖時，支援設定定時重新整理起始時間START WITH以及配置自動重新整理的時間間隔NEXT。系統將根據設定的重新整理時間配置，自動調度重新整理機制。

CREATE MATERIALIZED VIEW mv1 
REFRESH COMPLETE 
ON DEMAND 
START WITH now() NEXT now() + INTERVAL 1 hour 
AS 
SELECT 
    SUM(t1.col3) AS sum_value, 
    AVG(t1.col3) AS avg_value 
FROM table1 t1 
JOIN table2 t2 ON t1.col1 = t2.col1;

刪除物化視圖

說明

刪除物化視圖需具有物化視圖所在資料庫的DROP許可權。

文法說明

DROP MATERIALIZED VIEW <view_name>;

樣本

DROP MATERIALIZED VIEW mv1;

定時重新整理任務監控與管理

物化視圖的定時重新整理依賴於系統的自動調度重新整理機制。當調度任務數量過多時，您可以對任務進行監控與管理，即在表、庫或全域維度上暫停或重新啟動物化視圖的定時重新整理任務。

查看任務隊列

通過系統檢視表information_schema.materialized_view_refresh_queue，可以查詢重新整理隊列資訊。

SELECT * FROM information_schema.materialized_view_refresh_queue;

暫停任務隊列

以表維度

STOP MATERIALIZED VIEW task FOR TABLE mv1;

以庫維度

STOP MATERIALIZED VIEW task FOR DATABASE mv1;

以全域維度

STOP MATERIALIZED VIEW task FOR ALL;

重啟任務隊列

以表維度

RESTART MATERIALIZED VIEW task FOR TABLE mv1;

以庫維度

RESTART MATERIALIZED VIEW task FOR DATABASE mv1;

以全域維度

RESTART MATERIALIZED VIEW task FOR ALL;