マテリアライズドビューによるクエリの高速化|OceanBase Cloud|ドキュメント|分散データベース

マテリアライズドビュー(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モードにおけるマテリアライズドビューのクエリリライトを参照してください。

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

マテリアライズドビューは、データベースにおいてクエリパフォーマンスを向上させる重要な手段です。クエリ結果を事前に計算・格納することで、リアルタイム計算に伴うパフォーマンスオーバヘッドを削減します。マテリアライズドビューのクエリパフォーマンスをさらに最適化するために、以下の方法を検討できます：

マテリアライズドビューデータのストレージ形式

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

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

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

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

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

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

マテリアライズドビューはクエリパフォーマンスを向上させる重要なツールですが、大規模データを処理する際には、クエリパフォーマンスが低下する可能性があります。マテリアライズドビューのクエリパフォーマンスを最適化するために、マテリアライズドビューを参照するクエリのパフォーマンスを最適化することで、クエリをさらに高速化できます。

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

説明

以下の例はすべて、MySQLモードで実行されます。

ソーステーブルの作成

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

-- 販売データテーブル
CREATE TABLE sales (
  order_id INT primary key,
  user_id INT,
  item_id INT,
  item_count INT,
  region VARCHAR(100)
);

-- 商品情報テーブル
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時間ごとに定期的に更新します。

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

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

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

次に、増分更新マテリアライズドビューがサポートする3つのクエリシナリオの例を示します：

単一テーブルの集計

-- マテリアライズドビューのログを有効にする
CREATE MATERIALIZED VIEW LOG ON sales WITH primary key (item_id, item_count, region) including new values;

-- 増分更新マテリアライズドビューを作成する
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;

複数テーブルの結合

-- マテリアライズドビューが依存するベーステーブルすべてにマテリアライズドビューのログを作成する
CREATE MATERIALIZED VIEW LOG ON sales WITH primary key (user_id, item_id, item_count, region) including new values;

CREATE MATERIALIZED VIEW LOG ON items WITH primary key (price_per_item,pic_url) including new values;

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;

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

-- マテリアライズドビューが依存するベーステーブルすべてにマテリアライズドビューのログを作成する
CREATE MATERIALIZED VIEW LOG ON sales WITH primary key (item_id, item_count, region) including new values;

CREATE MATERIALIZED VIEW LOG ON items WITH primary key (price_per_item) including new values;

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;

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

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

-- マテリアライズドビューのログを有効にする
CREATE MATERIALIZED VIEW LOG ON sales WITH primary key (item_id, item_count, region) including new values;

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つは最初のマテリアライズドビューに基づいてさらに集計を行います。

CREATE MATERIALIZED VIEW LOG ON sales WITH primary key (user_id, item_id, item_count, region) including new values;
CREATE MATERIALIZED VIEW LOG ON items WITH primary key (price_per_item,pic_url) including new values;

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に基づいてマテリアライズドビューを作成
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
    wide_sales_mv1
group by region;

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

大量データ分析シナリオにおいてクエリ効率を向上させたい場合、カラムストアマテリアライズドビューを作成できます。列ストア方式により、クエリ時に必要な列のみを読み取ることができ、ディスクI/Oを大幅に削減します。

CREATE MATERIALIZED VIEW LOG ON sales WITH primary key (item_count, region) including new values;
CREATE MATERIALIZED VIEW LOG ON sales_items WITH primary key (price_per_item,pic_url) including new values;

-- カラムストアマテリアライズドビューの作成
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;

このカラムストアマテリアライズドビュー作成例では、<font style="color:rgba(61, 71, 92, 0.85);background-color:rgb(243, 244, 247);">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 を指定して、クエリのリライトが可能なリアルタイムマテリアライズドビューを作成します。

-- マテリアライズドビューのログを有効にする
CREATE MATERIALIZED VIEW LOG ON sales WITH primary key (item_id, item_count, region) including new values;

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;

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

クエリパフォーマンスをさらに最適化するために、マテリアライズドビューにインデックスを作成できます。マテリアライズドビューインデックスは、特に大量のデータが関わる場合、マテリアライズドビューに対するクエリの高速化に役立ちます。

-- マテリアライズドビューにインデックスを作成
CREATE INDEX idx_mv_sales_summary ON mv_sales_summary (region);

このインデックスは、mv_sales_summary マテリアライズドビュー上のクエリ、特に region 列に基づくクエリを高速化します。

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

手動更新コマンドのスケジューリング

CALL DBMS_MVIEW.REFRESH('mv1_test_tbl1','c');

CALL DBMS_MVIEW.REFRESH('mv1_test_tbl1','c','refresh_parallel => 8');

自動更新タスクの確認

select OWNER,MVIEW_NAME,CONTAINER_NAME,QUERY,QUERY_LEN,REFRESH_MODE,REFRESH_METHOD,BUILD_MODE,LAST_REFRESH_TYPE,LAST_REFRESH_DATE,LAST_REFRESH_END_TIME from oceanbase.DBA_MVIEWS;

select JOB_NAME,JOB_ACTION,START_DATE,REPEAT_INTERVAL,LAST_START_DATE,NEXT_RUN_DATE from OceanBase.DBA_SCHEDULER_JOBS where JOB_ACTION like "%mv4%";

select * from oceanbase.DBA_MVREF_RUN_STATS where MVIEWS='mv4';

マテリアライズドビューによるクエリのリライト例

システム変数とHintの有効な例は以下のとおりです：

-- マテリアライズドビューによるクエリのリライトを有効にする (デフォルトは無効)
SET  query_rewrite_enabled= true;

EXPLAIN SELECT item_id, region, SUM(item_count) AS total_count FROM sales GROUP BY item_id, region;

-- 非リアルタイムマテリアライズドビューを使用したクエリのリライトを許可する
SET query_rewrite_integrity = 'stale_tolerated';

EXPLAIN SELECT item_id, region, SUM(item_count) AS total_count FROM sales GROUP BY item_id, region;

-- マテリアライズドビューmv2_tbl2を強制的に指定してクエリをリライトする
EXPLAIN SELECT /*+mv_rewrite(mv2_tbl2)*/ item_id, region, SUM(item_count) AS total_count FROM sales GROUP BY item_id, region;

マテリアライズドビューのログを削除する

DROP MATERIALIZED VIEW LOG ON sales;
DROP MATERIALIZED VIEW LOG ON sales_items;

マテリアライズドビューの削除

DROP MATERIALIZED VIEW mv_sales_summary;

OceanBase

顧客事例

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