マテリアライズドビューによるクエリの高速化 - PolarDB

マテリアライズドビューは、クエリ結果を事前計算して格納することで、複雑なクエリのパフォーマンスを最適化するデータベースオブジェクトです。これにより、事前計算されたデータを直接クエリでき、リソースを大量に消費するこれらの操作を再度実行する必要がなくなります。この方法は、領域を時間と引き換えにクエリを高速化します。

PolarDB for MySQL クラスターは、マテリアライズドビューの完全更新をサポートするようになりました。定期更新または手動更新を実行できます。マテリアライズドビューは、バックグラウンドタスクを使用してデータを更新します。マテリアライズドビューを In-Memory Column Index (IMCI) と組み合わせて使用することで、クエリを高速化できます。これにより、更新効率が向上し、読み取り専用ノードの負荷が軽減されます。この機能は、PolarDB のハイブリッドトランザクション/分析処理 (HTAP) アーキテクチャの分析処理 (AP) 能力を強化します。

マテリアライズドビューと通常のビューの主な違いは、マテリアライズドビューにはデータの物理的なコピーが含まれており、そこから直接データをクエリできる点です。マテリアライズドビューは、ビューとその基になる物理ベーステーブルで構成されます。

マテリアライズドビューの作成：テーブルの作成とマテリアライズは、2つの別々のステップです。マテリアライズは、バックグラウンドタスクとして実行される非同期操作であり、物理ベーステーブルを作成します。その後、物理ベーステーブルの名前がビューに関連付けられます。更新タスクのクエリ部分は、IMCI 読み取り専用ノードにオフロードできます。これにより、クエリが高速化され、プライマリノードへの影響が軽減されます。
マテリアライズドビューのクエリ：クエリは、ビューと物理ベーステーブルを組み合わせて、物理ベーステーブル内のデータにアクセスすることによって実行されます。
マテリアライズドビューの更新：完全更新では、新しい非表示の物理ベーステーブルが作成されます。非表示のベーステーブルで更新文が実行された後、元のベーステーブルと非表示のベーステーブルが交換されます。このプロセスにより、メタデータテーブルが最新の物理ベーステーブル情報で更新され、クエリ用のメモリ内構造と同期されます。
タスクの監視と管理：バックグラウンド更新タスクキューに関する情報をクエリし、タスクを管理できます。管理には、特定のテーブル、データベース、またはグローバルレベルでの定期更新タスクの一時停止または再開が含まれます。

適用範囲

PolarDB for MySQL クラスターは、次の要件のいずれかを満たす必要があります。

クラスターが MySQL 8.0.1 を実行しており、マイナーエンジンバージョンが 8.0.1.1.51 以降であること。
クラスターが MySQL 8.0.2 を実行しており、マイナーエンジンバージョンが 8.0.2.2.31 以降であること。

メリット

繰り返し計算の回避：リソースを大量に消費する複雑なクエリの場合、計算を一度実行し、その結果を複数回再利用できます。これにより、クエリ効率が大幅に向上します。
データの事前集計：日次、週次、月次レポートなどのレポート用にデータを事前集計できます。これにより、レポートの生成とデータ分析が高速化されます。
ビッグデータクエリの最適化：マテリアライズドビューは、大量の生データに対する直接スキャンを削減します。これにより、分析クエリの応答時間が短縮されます。
多次元分析のサポート：マテリアライズドビューは、さまざまなディメンションの組み合わせの集計データを事前計算できます。これにより、オンライン分析処理 (OLAP) やその他の多次元分析シナリオを効率的にサポートします。

利用シーン

マテリアライズドビューは、クエリ結果を事前計算して格納することで、クエリパフォーマンスを最適化します。リアルタイムコンピューティングのオーバーヘッドを削減し、複雑なクエリロジックを簡素化するため、大規模なデータと複雑なクエリを伴うシナリオに特に役立ちます。マテリアライズドビューは、レポートの迅速な生成やデータ分析に広く使用されています。

繰り返し計算の回避：マテリアライズドビューは、頻繁にクエリされる、リソースを大量に消費する複雑な計算の結果を格納するのに適しています。これにより、同じデータを再計算する必要がなくなり、クエリ効率が大幅に向上します。
データの事前集計：レポート生成やデータ分析などのシナリオでは、マテリアライズドビューは、日次、週次、月次の売上データやユーザー行動データなどを事前集計できます。事前に格納された計算結果により、リアルタイム計算に必要な時間とリソースが削減されます。
ビッグデータクエリの最適化：ビジネスシナリオで大量のデータ分析が必要な場合、頻繁な大規模スキャンのために生データを直接クエリすると時間がかかることがあります。マテリアライズドビューは、生データへの直接アクセスを減らすことができ、クエリと分析を高速化します。
多次元分析のサポート：マテリアライズドビューは、さまざまなディメンションの組み合わせの集計データを事前計算できます。これにより、多次元データ分析シナリオに対して効率的で高速なクエリ応答を提供します。

パラメーターの説明

PolarDB クラスターパラメーターの変更方法は、コンソールとデータベースセッションで異なります。違いは次のとおりです：

PolarDB コンソールの場合
- 互換性：MySQL 設定ファイルとの互換性のために、PolarDB コンソールの一部のクラスターパラメーターには 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 |
+-----+--------------+------------+------------+---------------------+-----------+-------------------+------------------+---------------------+---------------------------+---------------------+---------------------+-------------------------+-----------------------+------------+---------------------+---------------------+

マテリアライズドビューの更新

現在、マテリアライズドビューは完全更新のみをサポートしています。このプロセスでは、非表示の物理ベーステーブルを作成し、その上で更新文を実行してから、元のテーブルと非表示のテーブルを交換する out-of-place メソッドが使用されます。そのため、完全更新操作には追加のストレージ領域が必要です。この方法の主な利点は、任意の SQL クエリをサポートすることです。

マテリアライズドビューは、手動更新とスケジュールされた更新の 2 つの更新方法をサポートしています。

手動更新

マテリアライズドビューが定期更新用に設定されていない場合、または更新間隔が長い場合は、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;