PolarDB:Incremental materialized views

CREATE MATERIALIZED VIEW mv_name
  [REFRESH [COMPLETE | FAST] [ON DEMAND]]
  [START WITH now()] [NEXT now() + interval 5 second]
  AS SELECT ... FROM base_table;

Ejemplo 1: filtro de una sola tabla (tipo FILTER)

1. Cree una tabla base con el índice columnar habilitado.

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

2. Cree una vista materializada incremental.

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

   Ejecute una actualización completa para cargar los datos iniciales.

   REFRESH MATERIALIZED VIEW mv_filter;

3. Ejecute una actualización incremental después de que cambien los datos de la tabla base.

   -- Change the base table
   INSERT INTO t1 VALUES (2, 2, 3);
   DELETE FROM t1 WHERE c1 = 1;
   
   -- Perform an incremental refresh to process only the changes above
   REFRESH MATERIALIZED VIEW mv_filter;

Ejemplo 2: estadísticas de agregación (tipo AGGREGATE)

-- Create a base table with the columnar index enabled
CREATE TABLE orders (
    order_id INT PRIMARY KEY,                  -- Unique order ID (primary key)
    user_id INT NOT NULL,                      -- User ID (GROUP BY column)
    amount DECIMAL(12, 2) NOT NULL,            -- Order amount (aggregation column)
    order_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP -- Order time (standard business field)
)COMMENT 'COLUMNAR=1';

-- Aggregate statistics
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;

Funciones de agregación compatibles: COUNT(*), COUNT(expr), SUM(expr) y AVG(expr).

Ejemplo 3: INNER JOIN de varias tablas

1. Cree las tablas base.

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

   Cree una vista materializada incremental para un INNER JOIN de varias tablas.

   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. Ejecute una actualización completa y compruebe el resultado.

   -- Perform a full refresh to populate the initial data
   REFRESH MATERIALIZED VIEW mv_inner_with_where_on;
   
   -- Change the base tables
   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');
   
   -- Perform an incremental refresh
   REFRESH MATERIALIZED VIEW mv_inner_with_where_on;

Ejemplo 4: LEFT JOIN

-- Create base tables with the columnar index enabled
-- Create the products table (joined table / right table)
CREATE TABLE products (
    product_id INT PRIMARY KEY,                -- Join key, must be the primary key
    product_name VARCHAR(100) NOT NULL,        -- Product name
    price DECIMAL(10, 2)                       -- Other business fields
) COMMENT 'COLUMNAR=1';                       

-- Create the order items table (primary table / left table)
CREATE TABLE order_items (
    item_id INT PRIMARY KEY,                   -- Item ID, must be the primary key
    order_id INT NOT NULL,                     -- Order ID
    product_id INT,                            -- Join key, can be NULL (because this is a LEFT JOIN)
    quantity INT NOT NULL,                     -- Purchase quantity
    FOREIGN KEY (product_id) REFERENCES products(product_id) -- Optional foreign key constraint
) 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;

Ejemplo 5: vista materializada anidada (reutilización de cálculos)

Una vista materializada anidada se construye sobre otra vista materializada incremental, lo que le permite reutilizar los resultados de los cálculos. Por ejemplo, primero puede filtrar los datos válidos de una tabla base y luego aplicar una agregación sobre el resultado filtrado. Durante una actualización, cada capa procesa únicamente sus respectivos cambios incrementales.

1. Cree una tabla base.

   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. Cree la vista materializada incremental de primer nivel para filtrar los pedidos pagados.

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

   Ejecute una actualización completa para cargar los datos iniciales.

   REFRESH MATERIALIZED VIEW mv_paid_orders;

3. Cree la vista materializada incremental de segundo nivel para agregar estadísticas por categoría, con base en la vista de primer nivel.

   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;

   Ejecute una actualización completa para cargar los datos iniciales.

   REFRESH MATERIALIZED VIEW mv_category_revenue;

4. Después de que cambien los datos de la tabla base, ejecute una actualización incremental para cada nivel en orden.

   -- Add new data to the base table
   INSERT INTO order_log VALUES
     (6, 105, 'electronics', 199.00, 'paid', 2025),
     (7, 106, 'clothing',     75.00, 'cancelled', 2025);
   
   -- Refresh in dependency order: first level, then second level
   REFRESH MATERIALIZED VIEW mv_paid_orders;
   REFRESH MATERIALIZED VIEW mv_category_revenue;
   
   -- Verify the result: order_id=7 is filtered out by the first level because its status is 'cancelled'
   -- and will not be included in the second-level statistics.
   SELECT * FROM mv_category_revenue;

REFRESH MATERIALIZED VIEW mv_name;

DROP MATERIALIZED VIEW mv_name;

Lista de vistas materializadas

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

Todas las vistas materializadas

SELECT * FROM mysql.view_materialized_info;

Cola de tareas de actualización

SELECT * FROM information_schema.materialized_view_refresh_queue;

¿Cómo comprobar la actualización incremental?

Use la siguiente instrucción para comprobar que refresh_strategy sea FAST. También puede consultar la columna last_refresh_status para ver el resultado de la actualización más reciente.

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

¿Cuándo se actualiza la vista materializada?

Antes de una actualización, los datos de la vista reflejan su estado en el momento de la última actualización. Para actualizar los datos, debe ejecutar manualmente REFRESH MATERIALIZED VIEW mv_name.

¿Se bloquea la tabla base durante la actualización?

No. Puede continuar con las operaciones normales de lectura y escritura en la tabla base durante una actualización incremental sin ninguna afectación.

¿Se admite la agregación en la actualización incremental?

Actualmente se admite la actualización incremental para la agregación de una sola tabla. La agregación de varias tablas y UNION ALL no son compatibles por ahora.

¿Cuál es el intervalo mínimo de actualización?

El intervalo mínimo de actualización es de 1 segundo actualmente.