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

マテリアライズドビュー(Materialized View、MV)は、データベースオブジェクトの一種ですが、標準ビューとは異なり、ビューのクエリ結果を格納しています。集計や結合など、時間のかかる操作の結果を保存しておき、クエリ時に直接再利用することで、時間とリソースを消費する操作を繰り返し実行する必要性がなく、結果的にクエリの高速化を実現します。特にデータウェアハウスや意思決定支援システムにおいて、計算時間を大幅に短縮し、クエリの効率を著しく向上させることができます。

マテリアライズドビューの特性

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

マテリアライズドビューを作成する際、ユーザーは、ビューのデータとベーステーブルとの同期を保つために、様々な更新方式を選択することができます。更新方式の選択は、システムのパフォーマンスとクエリ結果のリアルタイム性に直接影響します。

フル更新と増分更新

• フル更新(Complete Refresh)：更新のたびに、マテリアライズドビューがクエリステートメントを再実行し、計算結果を既存のビュー結果データに上書きします。遅延要求とベーステーブルのデータ更新頻度が低く、クエリステートメントが複雑であるか、データ量が小さいシナリオに適しています。

• 増分更新(Incremental Refresh)：高速更新(Fast Refresh)とも呼ばれ、インクリメンタル変更されたデータのみを更新します。大規模なデータセットに特に適しています。増分更新は、マテリアライズドビューログ(Materialized View Log, Mlog)に依存しており、クエリステートメントには一定の要件があります。現時点では、単一テーブルの集計、複数テーブルの結合、および複数のテーブルの結合と集計のクエリステートメントに対応しており、集計関数と結合方法にはいくつかの要件があります。詳細については以下を参照してください：MySQLモードでのマテリアライズドビューの更新およびOracleモードでのマテリアライズドビューの更新。遅延要件が高く、データ量が膨大で、かつ変更頻度が高いビジネスシナリオに適しています。

自動更新と手動更新

• 自動更新(Automatic Refresh)：マテリアライズドビューの作成時に、マテリアライズドビューの更新間隔を指定することができます。システムは、設定された更新時間ルールに基づいて、マテリアライズドビューの更新タスクを自動的にスケジューリングします。
• 手動更新(Manual Refresh)：マテリアライズドビューが自動更新の設定がされていないか、自動更新の間隔が大きい場合、マテリアライズドビューの更新コマンドを手動で実行することで、マテリアライズドビューのデータとベーステーブルのデータを同期状態に保つことができます。

リアルタイム　マテリアライズドビュー

リアルタイムマテリアライズドビュー(Real-Time Materialized Views)は、その名の通り、マテリアライズドビューをクエリすることで、リアルタイムのデータを取得することができるビューです。クエリ結果は、ベーステーブルを直接クエリした場合と同じであり、マテリアライズドビューの事前計算結果を利用することでクエリを高速化することができます。Mlogのメカニズムを利用して、　下層のベーステーブルの変更をキャプチャおよび処理することにより、マテリアライズド・ビューのデータが遅延なく最新の状態を反映することを保証します。Mlogに依存するため、リアルタイムマテリアライズドビューのクエリステートメントは、増分更新するマテリアライズドビューと同様の要件を満たす必要があります。つまり、マテリアライズドビューを増分更新する要件を満たす場合にのみ、対応するマテリアライズドビューをリアルタイムマテリアライズドビューとして定義することができます。

リアルタイムマテリアライズドビューの詳細については、MySQLモードでのマテリアライズドビューの作成および Oracleモードでのマテリアライズドビューの作成を参照してください。

ネスト　マテリアライズドビュー

ネストされたマテリアライズドビューとは、あるマテリアライズドビューが別のマテリアライズドビューにインポートされて、依存関係にあるものを指します。この方法は、ETL(抽出、変換、ロード)のプロセスにおいて非常に役立ちます。ETLプロセスにおいて、ネストされたマテリアライズドビューは、異なる段階のデータの集計および変換の結果を独立したビューとして格納することができます。これにより、複数回の重複計算が回避され、ETLプロセスの全体的な効率が向上します。ネストされたマテリアライズドビューは、自動カスケード更新に対応していません。ネストされたマテリアライズドビューを使用する前に、ネストされたマテリアライズドビューの更新に関する注意事項を理解し、上位のマテリアライズドビューから取得したデータの結果が予期したとおりであることを確認してください。

ネストされたマテリアライズドビューの詳細については、MySQLモードでのマテリアライズドビューの作成および Oracleモードでのマテリアライズドビューの作成を参照してください。

マテリアライズドビューのクエリリライト

マテリアライズドビューを利用してクエリを高速化したいが、元のクエリステートメントを変更したくない場合は、マテリアライズドビューのクエリリライト機能を利用することができます。システムは、クエリステートメントとマテリアライズドビューの定義を自動的に照合し、マッチしたマテリアライズドビューが見つかったら、自動的にマテリアライズドビューを使用したクエリにオーバーライドします。これにより、ビジネスに変更を加えることなく、クエリパフォーマンスと効率を大幅に向上させることができます。

マテリアライズドビューのクエリリライトの詳細については、MySQLモードでのマテリアライズドビューのクエリリライトおよび Oracleモードでのマテリアライズドビューのクエリリライトを参照してください。

マテリアライズドビューによるクエリ高速化方法

マテリアライズドビューは、データベースでのクエリパフォーマンス向上における重要な手段です。クエリ結果を事前に計算して格納することで、リアルタイム計算によるパフォーマンスオーバーヘッドを低減します。マテリアライズドビューのクエリパフォーマンスをより最適化するためには、以下のいくつかの方法が考えられます：

マテリアライズドビューデータの格納形式

実際の利用シーンに応じて、行またはカラムナマテリアライズドビューを選択することで、マテリアライズドビューのクエリを高速化することができます。

• 行指向型のマテリアライズドビューの適用シナリオ：マテリアライズドビュー内のデータが既に集計されており、エリでは主にデータ行全体にアクセスする場合。
• カラムナマテリアライズドビューの適用シナリオ：データ量が多く、列数も多いワイドテーブルにおいて、マテリアライズドビューに基づくクエリでデータ分析や集計などの操作がより多く行われる場合。

主キーマテリアライズドビューの使用

主キーの仕様により、マテリアライズドビュー内のデータの一意性が保証され、より効率的な検索と更新操作が可能になります。

マテリアライズドビューに基づくインデックスの作成

マテリアライズドビューにインデックスを作成することで、クエリパフォーマンスを大幅に向上させることができます。インデックスの作成により、必要なデータの迅速な特定が可能になり、テーブル全体のスキャンを回避することができます。

マテリアライズドビューはクエリ性能を向上させる重要なツールですが、大規模データを扱う際にはパフォーマンスに影響が出る可能性があります。マテリアライズドビューのクエリ性能を最適化し、検索をさらに高速化するには、ビュー定義の調整、インデックスの追加、あるいはリフレッシュ・ポリシーの見直しなどが有効です。

マテリアライズドビューの作成例

ソーステーブルの作成

まず、生データを格納するためのソーステーブルを作成します。この例では、販売データと商品情報を含むsalesテーブルとitemsテーブルを作成します。

1. 販売データテーブルsalesを作成。

   CREATE TABLE sales (
   order_id INT primary key,
   user_id INT,
   item_id INT,
   item_count INT,
   region VARCHAR(100)
   );
   

2. 商品情報テーブルitemsを作成。

   CREATE TABLE items (
   order_id INT,
   product_id INT,
   quantity INT,
   price_per_item DECIMAL(10, 2) NOT NULL,
   pic_url varchar(1000),
   primary key (order_id, product_id)
   );
   

フル更新マテリアライズドビューの作成

salesテーブルに基づくマテリアライズドビューを作成します。このマテリアライズドビューにより、プロダクトおよび地域別に販売量を集計し、クエリを高速化します。

プロダクトおよび地域別に販売量を集計したマテリアライズドビューmv_sales_summaryを作成します。

CREATE MATERIALIZED VIEW mv_sales_summary(PRIMARY KEY(item_id))
    REFRESH COMPLETE
    START WITH sysdate()
        NEXT sysdate() + interval 1 hour
    AS SELECT item_id, region, SUM(item_count) AS total_count
        FROM sales
        GROUP BY item_id, region;

このマテリアライズドビューの作成例では、以下の特性を指定しています：

• PRIMARY KEY：マテリアライズドビューに主キーを指定しました。
• REFRESH COMPLETE：フル更新方式を採用していることを意味します。
• START WITH sysdate() NEXT sysdate() + interval 1 hour：1時間に1回、定期的に更新します。

増分更新マテリアライズドビューの作成

データの変更頻度が高いシナリオでは、増分更新を用いることで、更新効率を向上させることができます。増分更新するマテリアライズドビューを作成する前に、マテリアライズドビューのベーステーブルに基づいてマテリアライズドビューログ(Mlog)を作成する必要があります。そうすることで、増分更新するマテリアライズドビューを正常に作成することができます。

マテリアライズドビューログの詳細については、MySQLモードでのマテリアライズドビューログおよびOracleモードでのマテリアライズドビューログを参照してください。

次に、増分更新マテリアライズドビューが対応している3つのクエリシナリオの例を紹介します：

単一テーブルの集計

1. salesテーブルにマテリアライズドビューログを作成します。

   CREATE MATERIALIZED VIEW LOG ON sales
       WITH PRIMARY KEY (item_id, item_count, region) INCLUDING NEW VALUES;
   

2. 増分更新マテリアライズドビューmv_sales_summary_fastを作成します。

    CREATE MATERIALIZED VIEW mv_sales_summary_fast
        REFRESH FAST
        START WITH sysdate() NEXT sysdate() + interval 1 hour
        AS SELECT item_id, region, SUM(item_count) AS total_count, count(*)  as c, count(item_count) as count
            FROM sales
            GROUP BY item_id, region;
   

複数テーブルの結合

1. salesテーブルにマテリアライズドビューログを作成します。

   1. (オプション)salesテーブル上のマテリアライズドビューのログを削除します。

      salesテーブルにマテリアライズドビューのログが作成されていない場合は、このステップをスキップしてください。

      DROP MATERIALIZED VIEW LOG ON sales;
      

   2. テーブルにマテリアライズドビューのログを作成します。

      CREATE MATERIALIZED VIEW LOG ON sales
          WITH PRIMARY KEY (user_id, item_id, item_count, region) INCLUDING NEW VALUES;
      

2. itemsテーブルにマテリアライズドビューのログを作成します。

   CREATE MATERIALIZED VIEW LOG ON items
       WITH PRIMARY KEY (price_per_item, pic_url) INCLUDING NEW VALUES;
   

3. 複数テーブル結合の増分更新マテリアライズドビューmv_sales_items_joinを作成します。

   CREATE MATERIALIZED VIEW mv_sales_items_join
       PARTITION BY HASH(order_id)
           PARTITIONS 10
       REFRESH FAST
       START WITH sysdate()
           NEXT sysdate() + interval 1 hour
       AS SELECT s.order_id AS order_id,
               s.user_id AS customer_id,
               s.item_id AS item_id,
               s.item_count AS quantity,
               s.region AS region,
               i.order_id AS i_id,
               i.product_id AS i_item_id,
               i.price_per_item AS price_per_item,
               i.pic_url AS pic_url
           FROM sales s JOIN items i
               ON s.order_id = i.order_id;
   

複数テーブルの結合と集計

1. salesテーブルにマテリアライズドビューログを作成します。

   1. (オプション)salesテーブルのマテリアライズドビューログを削除します。

      salesテーブルにマテリアライズドビューログを作成したことがない場合は、このステップをスキップしてください。

      DROP MATERIALIZED VIEW LOG ON sales;
      

   2. salesテーブルにマテリアライズドビューログを作成します。

      CREATE MATERIALIZED VIEW LOG ON sales
          WITH PRIMARY KEY (item_id, item_count, region) INCLUDING NEW VALUES;
      

2. itemsテーブルにマテリアライズドビューログを作成します。

   1. (オプション)itemsテーブルのマテリアライズドビューログを削除します。

      itemsテーブルにマテリアライズドビューログを作成したことがない場合は、このステップをスキップしてください。

      DROP MATERIALIZED VIEW LOG ON items;
      

   2. itemsテーブルにマテリアライズド・ビュー・ログを作成します。

      CREATE MATERIALIZED VIEW LOG ON items
          WITH PRIMARY KEY (price_per_item) INCLUDING NEW VALUES;
      

3. 複数テーブルを結合・集計する、増分更新のマテリアライズドビューmv_sales_item_join_groupを作成します。

   CREATE MATERIALIZED VIEW mv_sales_item_join_group
       REFRESH FAST
       START WITH sysdate()
           NEXT sysdate() + interval 1 hour
       AS SELECT s.item_id AS item_id,
               s.region AS region,
               SUM(s.item_count * i.price_per_item) AS sum_price,
               count(*) AS c,
               count(s.item_count * i.price_per_item) AS count
           FROM sales s JOIN items i
               ON s.order_id = i.order_id
           GROUP BY item_id, region;
   

リアルタイムマテリアライズドビューの作成

リアルタイムマテリアライズドビューは、データの変更があった時に、マテリアライズドビューのクエリ結果がベーステーブルと同期されるように保証します。リアルタイムマテリアライズドビューはマテリアライズドビューログに依存するため、増分更新するマテリアライズドビューと同様に、マテリアライズドビューを作成する前にマテリアライズドビューログを作成する必要があります。

1. salesテーブルにマテリアライズドビューログを作成します。

   1. (オプション)salesテーブルのマテリアライズドビューログを削除します。

      salesテーブルにマテリアライズドビューログを作成したことがない場合は、このステップをスキップしてください。

      DROP MATERIALIZED VIEW LOG ON sales;
      

   2. salesテーブルにマテリアライズドビューログを作成します。

      CREATE MATERIALIZED VIEW LOG ON sales
          WITH PRIMARY KEY (item_id, item_count, region) INCLUDING NEW VALUES;
      

2. リアルタイムマテリアライズドビューmv_sales_summary_comを作成します。

   CREATE MATERIALIZED VIEW mv_sales_summary_com
       REFRESH FORCE
       START WITH sysdate()
           NEXT sysdate() + interval 1 hour
       ENABLE ON QUERY COMPUTATION
       AS SELECT item_id,
               region,
               SUM(item_count) AS total_count,
               count(*) as c,
               count(item_count) as count
           FROM sales
           GROUP BY item_id, region;
   

この例ではENABLE ON QUERY COMPUTATIONを有効にすることで、クエリ時にマテリアライズドビューをリアルタイムで更新し、最新のデータを取得できるようにしています。また、マテリアライズドビューのクエリ文は増分更新マテリアライズドビューの作成要件を満たしています。

ネストされたマテリアライズドビューの作成

ETLのプロセスにおいて、ネストされたマテリアライズドビューは、複数のマテリアライズドビューを組み合わせて、より複雑なデータ処理プロセスとするために使用されます。ここでは、2つのマテリアライズドビューを作成します：1つは販売情報と商品情報を関連付け、もう1つは最初のマテリアライズドビューに基づいてさらに集計を行います。

1. salesテーブルにマテリアライズドビューログを作成します。

   1. (オプション)salesテーブルのマテリアライズドビューログを削除します。

      salesテーブルにマテリアライズドビューログを作成したことがない場合は、このステップをスキップしてください。

      DROP MATERIALIZED VIEW LOG ON sales;
      

   2. salesテーブルにマテリアライズドビューログを作成します。

      CREATE MATERIALIZED VIEW LOG ON sales
          WITH PRIMARY KEY (user_id, item_id, item_count, region) INCLUDING NEW VALUES;
      

2. itemsテーブルにマテリアライズドビューログを作成します。

   1. (オプション)itemsテーブルのマテリアライズドビューログを削除します。

      itemsテーブルにマテリアライズドビューログを作成したことがない場合は、このステップをスキップしてください。

      DROP MATERIALIZED VIEW LOG ON items;
      

   2. itemsテーブルにマテリアライズドビューログを作成します。

      CREATE MATERIALIZED VIEW LOG ON items
          WITH PRIMARY KEY (price_per_item,pic_url) INCLUDING NEW VALUES;
      

3. 販売情報と商品情報を関連付けるマテリアライズドビューmv1_sales_items_joinを作成します。

   CREATE MATERIALIZED VIEW mv1_sales_items_join
       REFRESH FAST
       START WITH sysdate()
           NEXT sysdate() + interval 1 hour
       AS SELECT
               s.order_id AS order_id,
               s.user_id AS customer_id,
               s.item_id AS item_id,
               s.item_count AS quantity,
               s.region AS region,
               i.order_id AS i_id,
               i.product_id AS i_item_id,
               i.price_per_item AS price_per_item,
               i.pic_url
           FROM sales s JOIN items i
               ON s.order_id = i.order_id;
   

4. マテリアライズドビューmv1_sales_items_joinにマテリアライズドビューログを作成します。

   CREATE MATERIALIZED VIEW LOG ON mv1_sales_items_join
       WITH PRIMARY KEY (region,quantity,price_per_item) INCLUDING NEW VALUES;
   

5. マテリアライズドビューmv1_sales_items_joinを基にマテリアライズドビューmv2_join_sumを作成します。

   CREATE MATERIALIZED VIEW mv2_join_sum
       REFRESH FAST
       START WITH sysdate()
           NEXT sysdate() + interval 1 hour
       AS SELECT
               region,
               sum(quantity * price_per_item) AS sum_price,
               count(*) as c,
               count(quantity * price_per_item) as count
           FROM mv1_sales_items_join
           GROUP BY region;
   

カラムナマテリアライズドビューの作成

大規模データの分析でクエリ性能を向上させたい場合、カラムナマテリアライズドビューを作成できます。列単位でデータを格納することで、クエリ時には必要な列だけを読み込み、ディスクI/Oを大幅に削減できます。

1. salesテーブルにマテリアライズドビューログを作成します。

   1. (オプション)salesテーブルのマテリアライズドビューログを削除します。

      salesテーブルにマテリアライズドビューログを作成したことがない場合は、このステップをスキップしてください。

      DROP MATERIALIZED VIEW LOG ON sales;
      

   2. salesテーブルにマテリアライズドビューログを作成します。

      CREATE MATERIALIZED VIEW LOG ON sales
          WITH PRIMARY KEY (user_id, item_id, item_count, region) INCLUDING NEW VALUES;
      

2. itemsテーブルにマテリアライズドビューログを作成します。

   1. (オプション)itemsテーブルのマテリアライズドビューログを削除します。

      itemsテーブルにマテリアライズドビューログを作成したことがない場合は、このステップをスキップしてください。

      DROP MATERIALIZED VIEW LOG ON items;
      

   2. itemsテーブルにマテリアライズドビューログを作成します。

      CREATE MATERIALIZED VIEW LOG ON items
          WITH PRIMARY KEY (price_per_item,pic_url) INCLUDING NEW VALUES;
      

3. カラムナマテリアライズドビューwide_sales_columnを作成します。

   CREATE MATERIALIZED VIEW wide_sales_column
       WITH COLUMN GROUP(each column)
       REFRESH FAST
       START WITH sysdate()
           NEXT sysdate() + interval 1 hour
       AS SELECT
               s.order_id AS order_id,
               s.user_id AS customer_id,
               s.item_id AS item_id,
               s.item_count AS quantity,
               s.region AS region,
               i.order_id AS i_id,
               i.product_id AS i_item_id,
               i.price_per_item AS price_per_item,
               i.pic_url
           FROM sales s JOIN items i
               ON s.order_id = i.order_id;
   

このカラムナマテリアライズドビューを作成する例では、WITH COLUMN GROUP(each column)と指定することで、マテリアライズドビューをカラムストアの形式で使用できるようにしています。これは、OLAPシナリオの場合、特に大量のデータとワイドテーブルクエリにおいて非常に役立ちます。

クエリリライトするマテリアライズドビューの作成

この例では、ENABLE QUERY REWRITEと指定することで、クエリリライトが可能なフル更新マテリアライズドビューを作成しました。

CREATE MATERIALIZED VIEW mv_sales_summary_select
    REFRESH COMPLETE
    START WITH sysdate()
        NEXT sysdate() + interval 1 hour
    ENABLE QUERY REWRITE
    AS SELECT item_id, region, SUM(item_count) AS total_count
        FROM sales
        GROUP BY item_id, region;

この例では、ENABLE QUERY REWRITEおよびENABLE ON QUERY COMPUTATIONを指定して、クエリ書き換えが可能なリアルタイムマテリアライズドビューを作成しました。

1. salesテーブルにマテリアライズドビューログを作成します。

   1. (オプション)salesテーブルのマテリアライズドビューログを削除します。

      salesテーブルにマテリアライズドビューログを作成したことがない場合は、このステップをスキップしてください。

      DROP MATERIALIZED VIEW LOG ON sales;
      

   2. salesテーブルにマテリアライズドビューログを作成します。

      CREATE MATERIALIZED VIEW LOG ON sales
          WITH PRIMARY KEY (item_id, item_count, region) INCLUDING NEW VALUES;
      

2. クエリリライトが可能なリアルタイムマテリアライズドビューmv_sales_summary_com_selectを作成します。

   CREATE MATERIALIZED VIEW mv_sales_summary_com_select
       REFRESH FAST
       START WITH sysdate() NEXT sysdate() + interval 1 hour
       ENABLE ON QUERY COMPUTATION
       ENABLE QUERY REWRITE
       AS SELECT
               item_id,
               region,
               SUM(item_count) AS total_sales,
               count(*)  as c,
               count(item_count) as count
           FROM sales
           GROUP BY item_id, region;
   

マテリアライズドビューインデックスの作成

クエリパフォーマンスをさらに最適化するために、マテリアライズドビューにインデックスを作成することができます。マテリアライズドビューインデックスは、特に大量のデータを扱う状況において、マテリアライズドビューのクエリを高速化させるのに役立ちます。

1. (オプション)製品および地域別に販売量を集計するマテリアライズドビューmv_sales_summaryを作成します。

   フル更新マテリアライズドビューを作成の例に沿ってマテリアライズドビューmv_sales_summaryを既に作成している場合は、このステップをスキップしてください。

   CREATE MATERIALIZED VIEW mv_sales_summary(PRIMARY KEY(item_id))
       REFRESH COMPLETE
       START WITH sysdate()
           NEXT sysdate() + interval 1 hour
       AS SELECT item_id, region, SUM(item_count) AS total_count
           FROM sales
           GROUP BY item_id, region;
   

2. マテリアライズドビューmv_sales_summaryのregion列にインデックスidx_mv_sales_summaryを作成します。

   CREATE INDEX idx_mv_sales_summary ON mv_sales_summary (region);
   

   このインデックスにより、マテリアライズドビューmv_sales_summaryに対するクエリ、特にregion列を条件とするクエリが高速化されます。

関連ドキュメント

• マテリアライズドビューの詳細と使用ガイドについては、マテリアライズドビューの概要(MySQLモード)および マテリアライズドビューの概要(Oracleモード)を参照してください。
• マテリアライズドビューログの更新に関する詳細と使用方法については、マテリアライズドビューログ（MySQLモード）およびマテリアライズドビューログ（Oracleモード）を参照してください。
• マテリアライズドビューの削除に関する詳細と使用方法については、マテリアライズドビューログ（MySQLモード）およびマテリアライズドビューログ（Oracleモード）を参照してください。