PolarDB:Tampilan materialisasi bertahap

Contoh 1: Filter satu tabel (tipe FILTER)

1. Buat tabel dasar dengan indeks kolom diaktifkan.

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

2. Buat tampilan materialisasi bertahap.

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

   Lakukan pembaruan penuh untuk mengisi data awal.

   REFRESH MATERIALIZED VIEW mv_filter;

3. Lakukan pembaruan bertahap setelah data tabel dasar berubah.

   -- Ubah tabel dasar
   INSERT INTO t1 VALUES (2, 2, 3);
   DELETE FROM t1 WHERE c1 = 1;
   
   -- Lakukan pembaruan bertahap untuk memproses hanya perubahan di atas
   REFRESH MATERIALIZED VIEW mv_filter;

Contoh 2: Statistik agregasi (tipe AGGREGATE)

-- Buat tabel dasar dengan indeks kolom diaktifkan
CREATE TABLE orders (
    order_id INT PRIMARY KEY,                  -- ID pesanan unik (primary key)
    user_id INT NOT NULL,                      -- ID pengguna (kolom GROUP BY)
    amount DECIMAL(12, 2) NOT NULL,            -- Jumlah pesanan (kolom agregasi)
    order_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP -- Waktu pesanan (bidang bisnis standar)
)COMMENT 'COLUMNAR=1';

-- Statistik agregasi
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;

Fungsi agregat yang didukung: COUNT(*), COUNT(expr), SUM(expr), dan AVG(expr).

Contoh 3: INNER JOIN multi-tabel

1. Buat tabel dasar.

   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');

   Buat tampilan materialisasi bertahap untuk INNER JOIN multi-tabel.

   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. Lakukan pembaruan penuh dan verifikasi.

   -- Lakukan pembaruan penuh untuk mengisi data awal
   REFRESH MATERIALIZED VIEW mv_inner_with_where_on;
   
   -- Ubah tabel dasar
   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');
   
   -- Lakukan pembaruan bertahap
   REFRESH MATERIALIZED VIEW mv_inner_with_where_on;

Contoh 4: LEFT JOIN

-- Buat tabel dasar dengan indeks kolom diaktifkan
-- Buat tabel produk (tabel yang digabungkan / tabel kanan)
CREATE TABLE products (
    product_id INT PRIMARY KEY,                -- Kunci join, harus primary key
    product_name VARCHAR(100) NOT NULL,        -- Nama produk
    price DECIMAL(10, 2)                       -- Bidang bisnis lainnya
) COMMENT 'COLUMNAR=1';                       

-- Buat tabel item pesanan (tabel utama / tabel kiri)
CREATE TABLE order_items (
    item_id INT PRIMARY KEY,                   -- ID item, harus primary key
    order_id INT NOT NULL,                     -- ID pesanan
    product_id INT,                            -- Kunci join, bisa NULL (karena ini LEFT JOIN)
    quantity INT NOT NULL,                     -- Jumlah pembelian
    FOREIGN KEY (product_id) REFERENCES products(product_id) -- Kendala kunci asing opsional
) 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;

Contoh 5: Tampilan materialisasi bersarang (penggunaan ulang komputasi)

Tampilan materialisasi bersarang dibangun di atas tampilan materialisasi bertahap lainnya, memungkinkan Anda menggunakan kembali hasil komputasi. Misalnya, Anda dapat terlebih dahulu memfilter data valid dari tabel dasar, lalu melakukan agregasi pada hasil yang telah difilter. Selama pembaruan, setiap lapisan hanya memproses perubahan bertahap masing-masing.

1. Buat tabel dasar.

   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. Buat tampilan materialisasi bertahap tingkat pertama untuk memfilter pesanan yang telah dibayar.

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

   Lakukan pembaruan penuh untuk mengisi data awal.

   REFRESH MATERIALIZED VIEW mv_paid_orders;

3. Buat tampilan materialisasi bertahap tingkat kedua untuk mengagregasi statistik berdasarkan kategori, berdasarkan tampilan tingkat pertama.

   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;

   Lakukan pembaruan penuh untuk mengisi data awal.

   REFRESH MATERIALIZED VIEW mv_category_revenue;

4. Setelah data tabel dasar berubah, lakukan pembaruan bertahap untuk setiap tingkat secara berurutan.

   -- Tambahkan data baru ke tabel dasar
   INSERT INTO order_log VALUES
     (6, 105, 'electronics', 199.00, 'paid', 2025),
     (7, 106, 'clothing',     75.00, 'cancelled', 2025);
   
   -- Refresh sesuai urutan dependensi: tingkat pertama, lalu tingkat kedua
   REFRESH MATERIALIZED VIEW mv_paid_orders;
   REFRESH MATERIALIZED VIEW mv_category_revenue;
   
   -- Verifikasi hasil: order_id=7 difilter oleh tingkat pertama karena statusnya 'cancelled'
   -- dan tidak akan dimasukkan dalam statistik tingkat kedua.
   SELECT * FROM mv_category_revenue;

Daftar tampilan materialisasi

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

Semua tampilan materialisasi

SELECT * FROM mysql.view_materialized_info;

Segarkan Antrian Tugas

SELECT * FROM information_schema.materialized_view_refresh_queue;

Bagaimana cara memverifikasi pembaruan bertahap?

Gunakan pernyataan berikut untuk memverifikasi bahwa refresh_strategy bernilai FAST. Anda juga dapat memeriksa kolom last_refresh_status untuk melihat hasil refresh terbaru.

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

Kapan tampilan materialisasi diperbarui?

Sebelum dilakukan refresh, data tampilan mencerminkan kondisinya pada saat refresh terakhir. Untuk memperbarui data, Anda harus menjalankan secara manual REFRESH MATERIALIZED VIEW mv_name.

Apakah tabel dasar dikunci selama refresh?

Tidak. Anda dapat terus melakukan operasi baca dan tulis normal pada tabel dasar selama pembaruan bertahap tanpa dampak apa pun.

Apakah agregasi didukung untuk pembaruan bertahap?

Pembaruan bertahap saat ini didukung untuk agregasi satu tabel. Agregasi multi-tabel dan UNION ALL saat ini tidak didukung.

Berapa interval refresh minimum?

Interval refresh minimum saat ini adalah 1 detik.