PolarDB:インクリメンタルマテリアライズドビュー

例 1：単一テーブルフィルター (FILTER 型)

1. 列指向インデックスを有効にしてベーステーブルを作成します。

   CREATE TABLE t1 (c1 INT PRIMARY KEY, c2 INT, c3 INT) COMMENT 'COLUMNAR=1';
   
   INSERT INTO t1 VALUES (1, 1, 1);

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

   CREATE MATERIALIZED VIEW mv_filter REFRESH FAST ON DEMAND AS
     SELECT * FROM t1 WHERE c2 < 5;

   完全リフレッシュを実行して、初期データを投入します。

   REFRESH MATERIALIZED VIEW mv_filter;

3. ベーステーブルのデータが変更された後、インクリメンタルリフレッシュを実行します。

   -- ベーステーブルを変更
   INSERT INTO t1 VALUES (2, 2, 3);
   DELETE FROM t1 WHERE c1 = 1;
   
   -- 上記の変更のみを処理するためにインクリメンタルリフレッシュを実行
   REFRESH MATERIALIZED VIEW mv_filter;

例 2：集計統計 (AGGREGATE 型)

-- 列指向インデックスを有効にしてベーステーブルを作成
CREATE TABLE orders (
    order_id INT PRIMARY KEY,                  -- 一意の注文 ID (プライマリキー)
    user_id INT NOT NULL,                      -- ユーザー ID (GROUP BY 列)
    amount DECIMAL(12, 2) NOT NULL,            -- 注文金額 (集計列)
    order_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP -- 注文時間 (標準的なビジネスフィールド)
) COMMENT 'COLUMNAR=1';

-- 集計統計
CREATE MATERIALIZED VIEW mv_user_order_stats
  REFRESH FAST ON DEMAND
  AS SELECT
       user_id,
       COUNT(*)                   AS order_count,
       SUM(amount * 2)            AS total_amount,
       AVG(CAST(amount AS INT))   AS avg_amount
     FROM orders
     GROUP BY user_id;

サポートされている集計関数：COUNT(*)、COUNT(expr)、SUM(expr)、AVG(expr)。

例 3：複数テーブルの INNER JOIN

1. ベーステーブルを作成します。

   CREATE TABLE t4 (id4 INT PRIMARY KEY, c2 INT, c3 VARCHAR(50)) COMMENT 'COLUMNAR=1';
   CREATE TABLE t5 (id5 INT PRIMARY KEY, c2 INT, c3 VARCHAR(50)) COMMENT 'COLUMNAR=1';
   CREATE TABLE t6 (id6 INT PRIMARY KEY, c2 INT, c3 VARCHAR(50)) COMMENT 'COLUMNAR=1';
   
   INSERT INTO t4 VALUES (1, 10, 'A'), (2, 20, 'B'), (3, 30, 'C');
   INSERT INTO t5 VALUES (1, 100, 'X'), (2, 200, 'Y'), (4, 400, 'Z');
   INSERT INTO t6 VALUES (1, 1000, 'P'), (2, 2000, 'Q'), (3, 3000, 'R');

   複数テーブルの INNER JOIN のためのインクリメンタルマテリアライズドビューを作成します。

   CREATE MATERIALIZED VIEW mv_inner_with_where_on REFRESH FAST ON DEMAND AS
     SELECT t4.id4, t4.c2, t5.id5, t5.c2 AS t5_c2, t6.id6, t6.c2 AS t6_c2
     FROM t4
     INNER JOIN t5 ON t4.id4 = t5.id5
     INNER JOIN t6 ON t4.id4 = t6.id6
     WHERE t4.c2 > 5 AND t5.c2 > 50;

2. 完全リフレッシュを実行して検証します。

   -- 完全リフレッシュを実行して初期データを投入
   REFRESH MATERIALIZED VIEW mv_inner_with_where_on;
   
   -- ベーステーブルを変更
   INSERT INTO t4 VALUES (4, 40, 'D');
   INSERT INTO t6 VALUES (4, 4000, 'S');
   UPDATE t4 SET c2 = 15 WHERE id4 = 1;
   DELETE FROM t5 WHERE id5 = 4;
   INSERT INTO t5 VALUES (5, 400, 'W');
   
   -- インクリメンタルリフレッシュを実行
   REFRESH MATERIALIZED VIEW mv_inner_with_where_on;

例 4：LEFT JOIN

-- 列指向インデックスを有効にしてベーステーブルを作成
-- products テーブルを作成 (結合対象テーブル / 右テーブル)
CREATE TABLE products (
    product_id INT PRIMARY KEY,                -- 結合キー、プライマリキーである必要があります
    product_name VARCHAR(100) NOT NULL,        -- 製品名
    price DECIMAL(10, 2)                       -- その他のビジネスフィールド
) COMMENT 'COLUMNAR=1';                       

-- order_items テーブルを作成 (主テーブル / 左テーブル)
CREATE TABLE order_items (
    item_id INT PRIMARY KEY,                   -- 項目 ID、プライマリキーである必要があります
    order_id INT NOT NULL,                     -- 注文 ID
    product_id INT,                            -- 結合キー、NULL の場合があります (LEFT JOIN のため)
    quantity INT NOT NULL,                     -- 購入数量
    FOREIGN KEY (product_id) REFERENCES products(product_id) -- オプションの外部キー制約
) COMMENT 'COLUMNAR=1';                       


-- LEFT JOIN
CREATE MATERIALIZED VIEW mv_order_with_product
  REFRESH FAST ON DEMAND
  AS SELECT
       oi.item_id,
       oi.order_id,
       p.product_name,
       oi.quantity
     FROM order_items oi
     LEFT JOIN products p ON oi.product_id = p.product_id;

例 5：ネストされたマテリアライズドビュー (計算の再利用)

ネストされたマテリアライズドビューは、別のインクリメンタルマテリアライズドビュー上に構築され、計算結果を再利用できます。たとえば、最初にベーステーブルから有効なデータをフィルターし、そのフィルター結果に対して集計を実行できます。リフレッシュ時には、各レイヤーはそれぞれのインクリメンタルな変更のみを処理します。

1. ベーステーブルを作成します。

   CREATE TABLE order_log (
     order_id    INT PRIMARY KEY,
     user_id     INT,
     product_category VARCHAR(50),
     amount      DECIMAL(10,2),
     status      VARCHAR(20),
     created_year INT
   ) COMMENT 'COLUMNAR=1';
   
   INSERT INTO order_log VALUES
     (1, 101, 'electronics', 299.00, 'paid', 2025),
     (2, 102, 'clothing',     59.90, 'paid', 2025),
     (3, 103, 'electronics', 499.00, 'cancelled', 2025),
     (4, 101, 'clothing',    120.00, 'paid', 2025),
     (5, 104, 'electronics',  89.00, 'paid', 2025);

2. 第一階層のインクリメンタルマテリアライズドビューを作成し、支払い済みの注文をフィルターします。

   CREATE MATERIALIZED VIEW mv_paid_orders REFRESH FAST ON DEMAND AS
     SELECT order_id, user_id, product_category, amount, created_year
     FROM order_log
     WHERE status = 'paid';

   完全リフレッシュを実行して、初期データを投入します。

   REFRESH MATERIALIZED VIEW mv_paid_orders;

3. 第一階層のビューに基づいて、第二階層のインクリメンタルマテリアライズドビューを作成し、カテゴリー別に統計を集計します。

   CREATE MATERIALIZED VIEW mv_category_revenue REFRESH FAST ON DEMAND AS
     SELECT product_category,
            COUNT(*)    AS order_count,
            SUM(amount) AS total_revenue
     FROM mv_paid_orders
     GROUP BY product_category;

   完全リフレッシュを実行して、初期データを投入します。

   REFRESH MATERIALIZED VIEW mv_category_revenue;

4. ベーステーブルのデータが変更された後、各階層を順番にインクリメンタルリフレッシュします。

   -- ベーステーブルに新しいデータを追加
   INSERT INTO order_log VALUES
     (6, 105, 'electronics', 199.00, 'paid', 2025),
     (7, 106, 'clothing',     75.00, 'cancelled', 2025);
   
   -- 依存関係の順にリフレッシュ：第一階層、次に第二階層
   REFRESH MATERIALIZED VIEW mv_paid_orders;
   REFRESH MATERIALIZED VIEW mv_category_revenue;
   
   -- 結果を検証：order_id=7 はステータスが 'cancelled' であるため第一階層でフィルターされ、第二階層の統計には含まれません。
   SELECT * FROM mv_category_revenue;

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

SELECT
  table_name,
  table_schema,
  base_tables,
  first_refresh_time,
  refresh_strategy,
  last_start_time,
  last_end_time
FROM mysql.view_materialized_info
WHERE refresh_strategy = 'FAST';

すべてのマテリアライズドビュー

SELECT * FROM mysql.view_materialized_info;

リフレッシュタスクキュー

SELECT * FROM information_schema.materialized_view_refresh_queue;

インクリメンタルリフレッシュの検証方法

refresh_strategy が FAST であることを確認するには、次のステートメントを実行します。last_refresh_status 列で最新のリフレッシュ結果を確認することもできます。

SELECT table_name, refresh_strategy, last_refresh_status, last_start_time, last_end_time
FROM mysql.view_materialized_info;

マテリアライズドビューはいつ更新されますか？

リフレッシュ前は、ビューのデータは最後のリフレッシュ時点の状態を反映しています。データを更新するには、手動で REFRESH MATERIALIZED VIEW mv_name を実行する必要があります。

リフレッシュ中にベーステーブルはロックされますか？

いいえ。インクリメンタルリフレッシュ中も、ベーステーブルに対して通常の読み取りおよび書き込み操作を影響なく続行できます。

インクリメンタルリフレッシュで集計はサポートされていますか？

現在、インクリメンタルリフレッシュは単一テーブルの集計をサポートしています。複数テーブルの集計と UNION ALL は現在サポートされていません。

最小リフレッシュ間隔はどのくらいですか？

現在の最小リフレッシュ間隔は 1 秒です。