PolarDB:Incremental materialized views

Como funciona

O princípio fundamental de uma visualização materializada incremental é processar apenas o que mudou, e não todo o conjunto de dados.

Quando ocorrem operações INSERT, UPDATE ou DELETE em uma tabela base, o sistema registra essas alterações automaticamente. Esse processo é executado com eficiência em um nó de armazenamento colunar In-Memory Columnar Index (IMCI), sem necessidade de binlogs ou triggers, e não bloqueia a tabela base.

• FAST (atualização incremental): Processa somente os dados alterados desde a última atualização, resultando em alta velocidade e baixa sobrecarga.

• COMPLETE (atualização completa): Recalcula toda a consulta. Indicado para preenchimento inicial ou correção de dados.

Pré-requisitos

Para usar uma visualização materializada incremental, seu sistema deve atender aos seguintes requisitos:

• Cluster: Seu cluster PolarDB for MySQL deve atender a um dos seguintes requisitos:

  • MySQL 8.0.2 com versão secundária do kernel 8.0.2.2.35 ou posterior.

• Parâmetros do In-Memory Columnar Index (IMCI):

  Parameter	Default	Value	Description
  imci_enable_window_function	2	>= 2	Ativa o suporte a funções de janela IMCI.
  imci_enable_nci_async_pre_commit	OFF	OFF	Desativa o pré-commit assíncrono NCI para garantir a consistência dos dados incrementais. Nota Este parâmetro está definido como OFF por padrão e não pode ser alterado no momento.
  imci_enable_hybrid_plan	ON	OFF	Desativa o plano de execução híbrida para forçar a execução das atualizações incrementais no nó de armazenamento colunar somente leitura.

  Nota

  Modifique esses parâmetros no PolarDB console. Consulte Modificar parâmetros.

Principais benefícios

Casos de uso

• Pré-computação para análise: Pré-processe junções complexas de várias tabelas em uma tabela ampla para evitar operações de junção custosas em cada consulta.

• Aceleração de relatórios: Forneça dados pré-computados para data warehouse em tempo real ou relatórios de BI, reduzindo a sobrecarga de atualização em mais de 90% com atualizações incrementais.

• Reutilização de computação: Reutilize resultados de computação criando visualizações materializadas aninhadas.

• Data warehouse em tempo real: Suporte consultas analíticas que refletem alterações na tabela base quase em tempo real.

• Cargas de trabalho HTAP: Sincronize dados OLTP frequentemente atualizados para uma visualização analítica com baixa latência, separando cargas de trabalho TP e AP.

Limitações

• Limitações gerais:

  • Sintaxe não suportada: A atualização incremental não é suportada para consultas que contêm subconsulta, UNION, ORDER BY, LIMIT, função de janela, HAVING, ROLLUP ou CTE.

  • UNION ALL: Não suportado atualmente.

  • Requisitos da tabela base:

    • A tabela base deve ter o IMCI ativado (polar_enable_imci = ON) e todas as colunas da tabela base devem estar incluídas no IMCI.

    • A tabela base não pode ser uma visualização padrão ou uma visualização materializada não incremental. Visualizações materializadas aninhadas são permitidas, desde que a visualização interna também suporte atualização incremental.

  • Sobrecarga de armazenamento:

    • Uma visualização materializada armazena uma cópia dos dados, o que consome espaço de armazenamento adicional.

    • O sistema também consome armazenamento extra para registrar alterações para atualizações incrementais. Esse uso de armazenamento não cresce indefinidamente, pois dados obsoletos são recuperados periodicamente.

• Limitações para filtragem de tabela única:

  • A tabela base deve ter uma chave primária explícita.

  • A palavra-chave DISTINCT não é suportada.

• Limitações para agregação de tabela única:

  • A tabela base deve ter uma chave primária explícita.

  • A cláusula GROUP BY deve referenciar colunas individuais e não pode usar expressões. Por exemplo, GROUP BY YEAR(create_time) não é suportado; altere para GROUP BY create_year.

  • Funções de agregação suportadas: COUNT, SUM e AVG.

  • Funções de agregação não suportadas: STDDEV, VARIANCE, MIN e MAX.

  • Agregação sem cláusula GROUP BY não é suportada atualmente.

  • Agregação de múltiplas tabelas não é suportada atualmente.

• Limitações para junções de múltiplas tabelas

  • Todas as tabelas base devem ter uma chave primária explícita.

  • RIGHT JOIN não é suportado.

  • As junções devem seguir uma estrutura de árvore profunda à esquerda. Estruturas aninhadas como A JOIN (B LEFT JOIN C) JOIN D não são permitidas.

  • A condição ON para cada tabela deve incluir uma coluna de outra tabela. Sintaxes como A JOIN B ON B.id = 1 não são permitidas.

Sintaxe

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

• FAST: Especifica o modo de atualização incremental, que processa apenas as alterações da tabela base.

• COMPLETE: Especifica o modo de atualização completa, que recalcula toda a consulta a cada vez.

• ON DEMAND: Especifica uma atualização sob demanda, acionada manualmente ou por um intervalo de atualização em segundo plano predefinido.

Exemplos

Exemplo 1: Filtro de tabela única (tipo FILTER)

1. Crie uma tabela base com o índice colunar ativado.

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

2. Crie uma visualização materializada incremental.

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

   Execute uma atualização completa para preencher os dados iniciais.

   REFRESH MATERIALIZED VIEW mv_filter;

3. Execute uma atualização incremental após alterar os dados da tabela 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;

Exemplo 2: Estatísticas de agregação (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;

Funções de agregação suportadas: COUNT(*), COUNT(expr), SUM(expr) e AVG(expr).

Exemplo 3: INNER JOIN de múltiplas tabelas

1. Crie as tabelas 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');

   Crie uma visualização materializada incremental para um INNER JOIN de múltiplas tabelas.

   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. Execute uma atualização completa e verifique.

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

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

Exemplo 5: Visualização materializada aninhada (reutilização de computação)

Uma visualização materializada aninhada é construída sobre outra visualização materializada incremental, permitindo reutilizar resultados de computação. Por exemplo, filtre primeiro dados válidos de uma tabela base e depois execute agregação no resultado filtrado. Durante uma atualização, cada camada processa apenas suas respectivas alterações incrementais.

1. Crie uma tabela 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. Crie a visualização materializada incremental de primeiro nível para filtrar pedidos pagos.

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

   Execute uma atualização completa para preencher os dados iniciais.

   REFRESH MATERIALIZED VIEW mv_paid_orders;

3. Crie a visualização materializada incremental de segundo nível para agregar estatísticas por categoria, com base na visualização de primeiro nível.

   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;

   Execute uma atualização completa para preencher os dados iniciais.

   REFRESH MATERIALIZED VIEW mv_category_revenue;

4. Após alterar os dados da tabela base, execute uma atualização incremental para cada nível em ordem.

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

Nota

Para garantir a consistência dos dados, atualize as visualizações materializadas aninhadas em sua ordem de dependência, da visualização base até a visualização de nível superior. A visualização materializada subjacente deve ser do tipo de atualização incremental (REFRESH FAST).

Executar uma atualização incremental

Para acionar manualmente uma atualização, execute o seguinte comando:

REFRESH MATERIALIZED VIEW mv_name;

Uma atualização incremental processa apenas as alterações ocorridas na tabela base desde a última atualização, tornando-a rápida e com baixa sobrecarga, sem exigir uma varredura completa da tabela.

Nota

Ao executar REFRESH MATERIALIZED VIEW pela primeira vez, o sistema realiza automaticamente uma atualização completa para preencher os dados iniciais. As atualizações subsequentes são incrementais.

Excluir uma visualização materializada

DROP MATERIALIZED VIEW mv_name;

Após excluir uma visualização materializada, o sistema limpa automaticamente a tabela delta correspondente se o parâmetro delta_mv_auto_cleanup = ON estiver ativado (padrão).

Gerenciamento e monitoramento

Lista de visualizações 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 as visualizações materializadas

SELECT * FROM mysql.view_materialized_info;

Fila de tarefas de atualização

SELECT * FROM information_schema.materialized_view_refresh_queue;

Perguntas frequentes

Como verificar a atualização incremental?

Use a seguinte instrução para verificar se o refresh_strategy é FAST. Verifique também a coluna last_refresh_status para obter o resultado da atualização mais recente.

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

Quando a visualização materializada é atualizada?

Antes de uma atualização, os dados da visualização refletem seu estado no momento da última atualização. Para atualizar os dados, execute manualmente REFRESH MATERIALIZED VIEW mv_name.

A tabela base é bloqueada durante a atualização?

Não. É possível continuar a realizar operações normais de leitura e gravação na tabela base durante uma atualização incremental sem qualquer impacto.

A agregação é suportada para atualização incremental?

A atualização incremental é atualmente suportada para agregação de tabela única. Agregação de múltiplas tabelas e UNION ALL não são suportados atualmente.

Qual é o intervalo mínimo de atualização?

O intervalo mínimo de atualização é atualmente de 1 segundo.