クエリ高速化のためのマテリアライズドビュー|OceanBase Cloud|ドキュメント|OceanBaseマニュアル

マテリアライズドビュー（MV）はデータベースオブジェクトの一種で、通常のビューとは異なり、クエリ結果を保存します。集計や結合など時間のかかる処理結果を保存しておくことで、クエリ時にその結果を直接再利用でき、リソースを消費する処理の再実行を避けられます。その結果、クエリ性能を高めることができます。特にデータウェアハウスや意思決定支援システムでは、計算時間を大幅に短縮し、クエリ効率を向上できます。

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

マテリアライズドビューのリフレッシュ方式

マテリアライズドビューを作成するときは、ベーステーブルとデータを同期させるために、複数のリフレッシュ方式から選択できます。どの方式を選ぶかは、システム性能とクエリ結果のリアルタイム性に直接影響します。

完全リフレッシュと増分リフレッシュ

完全リフレッシュ：マテリアライズドビューをリフレッシュするたびにクエリ文を再実行し、計算結果で既存のビュー結果データを上書きします。遅延要件が厳しくない、ベーステーブルの更新頻度が低い、クエリ文が複雑、またはデータ量が少ないシナリオに適しています。
増分リフレッシュ：高速リフレッシュとも呼ばれ、変更された増分データのみをリフレッシュします。大規模データセットに特に適しています。増分リフレッシュではマテリアライズドビューログ（mlog）が必要です。クエリ文には一定の要件があり、現在は単一テーブル集計、複数テーブル結合、複数テーブル結合集計のクエリ文に対応しています。また、集計関数や結合方式にも要件があります。詳細は、MySQL 互換モードでのマテリアライズドビューのリフレッシュおよび Oracle 互換モードでのマテリアライズドビューのリフレッシュを参照してください。遅延要件が厳しく、データ量が多く、変更が頻繁な業務シナリオに適しています。

自動リフレッシュと手動リフレッシュ

自動リフレッシュ：マテリアライズドビューの作成時に、リフレッシュ間隔を指定できます。システムは設定されたリフレッシュスケジュールに基づいて、マテリアライズドビューのリフレッシュタスクを自動的にスケジュールします。
手動リフレッシュ：マテリアライズドビューで自動リフレッシュが有効でない場合、または自動リフレッシュ間隔が長い場合は、リフレッシュコマンドを手動で実行し、マテリアライズドビューのデータをベーステーブルのデータと同期できます。

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

リアルタイムマテリアライズドビューは、その名のとおり、マテリアライズドビューをクエリするだけでリアルタイムデータを取得できます。結果はベーステーブルを直接クエリした場合と同じで、さらにマテリアライズドビューの事前計算結果を利用してクエリを高速化できます。リアルタイムマテリアライズドビューは、マテリアライズドビューログの仕組みにより基盤となるベーステーブルの変更を取得、処理し、ビュー内のデータが最新状態を速やかに反映するようにします。マテリアライズドビューログに依存するため、リアルタイムマテリアライズドビューのクエリ要件は、増分リフレッシュのマテリアライズドビューと同じです。つまり、増分リフレッシュの要件を満たすマテリアライズドビューだけを、リアルタイムマテリアライズドビューとして定義できます。

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

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

ネストされたマテリアライズドビューとは、別のマテリアライズドビューから参照または依存されるマテリアライズドビューを指します。この方式は ETL（抽出、変換、ロード）プロセスで特に有用です。ETL ワークフローでは、各段階の集計結果や変換結果を個別のビューとして保存できるため、重複計算を避け、ETL 全体の効率を高められます。ネストされたマテリアライズドビューは自動カスケードリフレッシュに対応していません。使用前にリフレッシュ時の注意点を理解し、上位のマテリアライズドビューをクエリしたときに期待どおりの結果が得られるようにしてください。

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

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

元のクエリ文を変更せずにマテリアライズドビューでクエリを高速化したい場合は、マテリアライズドビューのクエリリライト機能を利用できます。システムはクエリ文とマテリアライズドビュー定義を自動的に照合し、一致するマテリアライズドビューが見つかると、そのビューを使用するようにクエリを自動でリライトします。これにより、業務ロジックを変更せずにクエリ性能と効率を大幅に向上できます。

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

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

マテリアライズドビューは、データベースでクエリを高速化するための重要な手段です。クエリ結果を事前計算して保存することで、リアルタイム計算に伴う性能負荷を軽減します。マテリアライズドビューのクエリ性能をさらに最適化するには、次の方法を検討できます。

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

実際のアプリケーションシナリオに応じて、行ストアまたは列ストアのマテリアライズドビューを選択することで、クエリを高速化できます。

行ストアマテリアライズドビューが適したシナリオ：マテリアライズドビュー内のデータがすでに集計されており、クエリが通常、行全体にアクセスする場合。
列ストアマテリアライズドビューが適したシナリオ：列数と行数が多いワイドテーブルで、クエリがデータ分析や集計処理を主に行う場合。

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

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

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

マテリアライズドビューにインデックスを作成すると、クエリ性能を大きく向上できます。インデックスにより必要なデータを素早く特定でき、全表スキャンを減らせます。

マテリアライズドビューはクエリ性能向上の重要な手段ですが、大量データを扱う場合は性能が影響を受けることがあります。マテリアライズドビューのクエリ性能を最適化するには、前述の方法でさらにクエリを高速化できます。

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

注

次の例は MySQL 互換モードで実行しています。

ソーステーブルの作成

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

販売データテーブル sales を作成します。

CREATE TABLE sales (
  order_id   INT PRIMARY KEY,
  user_id    INT,
  item_id    INT,
  item_count INT,
  region     VARCHAR(100)
);

商品情報テーブル 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 テーブルを基にマテリアライズドビューを作成します。このマテリアライズドビューは商品と地域別に販売数を集計し、クエリを高速化します。

-- Materialized view that summarizes sales by product and region
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 時間ごとの定期リフレッシュです。

増分リフレッシュのマテリアライズドビューの作成

データ変更が頻繁なシナリオでは、増分リフレッシュを使用してリフレッシュ効率を高められます。増分リフレッシュのマテリアライズドビューを作成する前に、マテリアライズドビューのベーステーブルに基づいてマテリアライズドビューログ（mlog）を作成する必要があります。その後、増分リフレッシュのマテリアライズドビューを作成できます。

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

次の例では、増分リフレッシュのマテリアライズドビューでサポートされる 3 つのクエリシナリオを示します。

単一テーブルのデータ集計

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

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

単一テーブル集計用に、増分リフレッシュのマテリアライズドビュー 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;

複数テーブルのデータ結合

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;
```

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

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

複数テーブル結合用に、増分リフレッシュのマテリアライズドビュー 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;

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

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;
```
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;
```

複数テーブル結合集計用に、増分リフレッシュのマテリアライズドビュー 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;

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

リアルタイムマテリアライズドビューでは、データ変更が発生したときに、マテリアライズドビューのクエリ結果をベーステーブルと同期した状態に保てます。リアルタイムマテリアライズドビューは増分リフレッシュのマテリアライズドビューと同様にマテリアライズドビューログに依存するため、ビューを作成する前にログを作成する必要があります。

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;
```

リアルタイムマテリアライズドビュー 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 つを作成します。

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;
```
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;
```

販売情報と商品情報を結合するマテリアライズドビュー 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;

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

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

マテリアライズドビュー 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 を大幅に削減できます。

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;
```
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;
```

列ストアマテリアライズドビュー 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 の両方を指定し、クエリリライトに対応するリアルタイムマテリアライズドビューを作成します。

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;
```

クエリリライトに対応するリアルタイムマテリアライズドビュー 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;

これらの例ではクエリリライトを有効にしているため、オプティマイザはマテリアライズドビューを自動的に使用してクエリに応答し、性能を向上できます。リアルタイムマテリアライズドビューは、必要に応じて結果を動的に計算することもできます。

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

クエリ性能をさらに最適化するには、マテリアライズドビューにインデックスを作成できます。マテリアライズドビュー上のインデックスは、特に大量データを扱う場合に、ビューに対するクエリを高速化します。

（任意）商品と地域別に販売数を集計するマテリアライズドビュー 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;
```
マテリアライズドビュー 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 互換モード）を参照してください。
マテリアライズドビューの削除に関する詳細な概要と利用ガイドについては、マテリアライズドビューの削除（MySQL 互換モード）およびマテリアライズドビューの削除（Oracle 互換モード）を参照してください。

OceanBase

顧客事例

クエリ高速化のためのマテリアライズドビュー

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

マテリアライズドビューのリフレッシュ方式

完全リフレッシュと増分リフレッシュ

自動リフレッシュと手動リフレッシュ

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

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

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

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

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

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

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

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

注

ソーステーブルの作成

完全リフレッシュのマテリアライズドビューの作成

増分リフレッシュのマテリアライズドビューの作成

単一テーブルのデータ集計

複数テーブルのデータ結合

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

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

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

列ストアマテリアライズドビューの作成

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

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

参考情報