SelectDB materialized view - ApsaraDB for SelectDB: Central de Documentação do Alibaba Cloud

Visualizações materializadas síncronas vs. assíncronas

Escolha o tipo adequado antes de iniciar a construção.

	Visualização materializada síncrona	Visualização materializada assíncrona
Suporte a tabelas base	Apenas tabela única	Várias tabelas
Suporte a JOIN	Não	Sim
Funções de agregação	Limitadas (SUM, MIN, MAX, COUNT, BITMAP_UNION, HLL_UNION)	Gama completa
Reescrita de consulta	Automática	Automática
Estratégia de atualização	Síncrona — atualizada em cada importação de dados	Assíncrona — atualização agendada ou manual
Consistência de dados	Sempre consistente com a tabela base	Eventualmente consistente

Use uma visualização materializada síncrona quando sua carga de trabalho envolver apenas uma tabela e exigir consistência em tempo real. Opte pela versão assíncrona para junções entre múltiplas tabelas ou lógicas de agregação mais complexas.

Casos de uso

Aceleração de agregações: Pré-compute agregações GROUP BY executadas repetidamente em grandes conjuntos de dados.
Correspondência de índice de prefixo: Crie uma visualização com uma coluna de ordenação inicial diferente para atender a consultas incapazes de usar o índice de prefixo da tabela base.
Pré-filtragem: Armazene um subconjunto filtrado da tabela base para reduzir o volume de varredura.
Pré-computação de expressões: Materialize colunas computadas complexas para que as consultas leiam os resultados diretamente.

Quando criar uma visualização materializada

Crie uma visualização materializada síncrona quando todas as condições abaixo forem verdadeiras:

A consulta tem como alvo uma única tabela.
A execução da consulta é frequente.
A consulta possui alto custo (agregação pesada, varredura extensa ou expressão complexa).

Evite criar a visualização materializada se qualquer uma das situações a seguir se aplicar:

A consulta já é rápida e de baixo custo.
São necessárias funções de agregação diferentes na mesma coluna (não suportado).
Já existem mais de 10 visualizações materializadas na tabela — cada visualização adicional desacelera todas as importações de dados, pois a tabela base e suas visualizações são atualizadas simultaneamente.

Limitações

Consultas diretas não são suportadas. Escreva as consultas na tabela base. O SelectDB seleciona automaticamente a melhor visualização materializada correspondente.
Restrição do modelo Unique: No modelo Unique, uma visualização materializada síncrona pode apenas reordenar colunas; ela não agrega dados.
Desempenho de importação: Cada visualização materializada adiciona sobrecarga a toda importação de dados. Mais de 10 visualizações em uma única tabela podem retardar significativamente as importações.

Criar uma visualização materializada

Sintaxe

CREATE MATERIALIZED VIEW <mv_name> AS <query>
[PROPERTIES ("key" = "value")]

Formato da query:

SELECT select_expr [, select_expr ...]
FROM <base_table_name>
[GROUP BY column_name [, column_name ...]]
[ORDER BY column_name [, column_name ...]]

Parâmetros

Parâmetro	Obrigatório	Descrição
`mv_name`	Sim	Nome da visualização materializada. Deve ser único por tabela base.
`query`	Sim	Instrução SELECT que define a visualização materializada. Deve referenciar uma única tabela — subconsultas não são permitidas.
`properties`	Não	Configuração opcional: `short_key` (número de colunas de ordenação) e `timeout` (tempo limite de construção em segundos).

Restrições da instrução SELECT:

Apenas tabela única — sem subconsultas.
As colunas não devem incluir colunas de incremento automático, constantes, expressões duplicadas ou funções de janela.
Se o SELECT incluir chave de partição ou colunas de bucketing, essas colunas devem ser colunas Key na visualização materializada.
Cláusulas permitidas: WHERE, GROUP BY, ORDER BY.
Cláusulas proibidas: JOIN, HAVING, LIMIT, LATERAL VIEW.

Restrições de funções de agregação:

Importante

Os parâmetros devem ser colunas únicas — expressões não são suportadas. Por exemplo, sum(a) é válido, mas sum(a+b) não é. Funções de agregação diferentes na mesma coluna também não são suportadas: SELECT sum(a), min(a) FROM table é inválido.

Funções de agregação suportadas:

Função	Notas de uso
SUM, MIN, MAX, COUNT	Agregação padrão.
BITMAP_UNION	`BITMAP_UNION(TO_BITMAP(col))`: a coluna deve ser do tipo inteiro (exceto `largeint`). `BITMAP_UNION(col)`: a tabela base deve ser um modelo Aggregate.
HLL_UNION	`HLL_UNION(HLL_HASH(col))`: a coluna não pode ser `DECIMAL`. `HLL_UNION(col)`: a tabela base deve ser um modelo Aggregate.

Regras de complemento automático do ORDER BY (quando ORDER BY é omitido):

Visualização do tipo agregação: todas as colunas de agrupamento tornam-se colunas de ordenação.
Visualização sem agregação: os primeiros 36 bytes das colunas tornam-se colunas de ordenação.
Caso menos de três colunas sejam complementadas automaticamente, as três primeiras colunas serão utilizadas.
Se GROUP BY for especificado, o ORDER BY deve corresponder às colunas de agrupamento.

Princípios de design

Abstraia padrões compartilhados. Defina a visualização materializada com base em padrões de agregação comuns a várias consultas. Uma visualização usada por apenas uma consulta consome armazenamento com benefício mínimo.
Cubra apenas dimensões comuns. Nem toda combinação de dimensões precisa de uma visualização materializada. Foque nas combinações mais frequentes nas consultas de produção.

Exemplos

Os exemplos a seguir utilizam esta tabela base:

CREATE TABLE duplicate_table (
    k1 INT NULL,
    k2 INT NULL,
    k3 BIGINT NULL,
    k4 BIGINT NULL
)
DUPLICATE KEY (k1, k2, k3, k4)
DISTRIBUTED BY HASH(k4) BUCKETS 3;

Exemplo 1: Subconjunto de colunas com prefixo reordenado

CREATE MATERIALIZED VIEW k1_k2 AS
SELECT k2, k1 FROM duplicate_table;

Resultado: uma visualização contendo apenas k2 e k1, com k2 como coluna principal de ordenação. Consultas que filtram por k2 podem usar o índice de prefixo desta visualização em vez do índice da tabela base.

Exemplo 2: Ordem de classificação explícita

CREATE MATERIALIZED VIEW k2_order AS
SELECT k2, k1 FROM duplicate_table ORDER BY k2;

Exemplo 3: Agregação

CREATE MATERIALIZED VIEW k1_k2_sumk3 AS
SELECT k1, k2, sum(k3)
FROM duplicate_table
GROUP BY k1, k2;

Resultado: uma visualização com k1 e k2 como colunas Key e sum(k3) como valor agregado. O SelectDB complementa automaticamente k1, k2 como colunas de ordenação porque GROUP BY foi usado e ORDER BY foi omitido.

Verificar status de criação

A criação de uma visualização materializada é uma operação assíncrona. Após enviar a instrução CREATE, o SelectDB constrói a visualização em segundo plano. Acompanhe o progresso com:

SHOW ALTER TABLE MATERIALIZED VIEW FROM <database>;

Campos do resultado:

Campo	Descrição
`TableName`	Nome da tabela base.
`BaseIndexName`	Nome do índice da tabela base.
`RollupIndexName`	Nome da visualização materializada.
`State`	Status da tarefa: `PENDING` (agendada), `RUNNING` (em andamento), `FINISHED` (concluída), `CANCELLED` (cancelada).
`Timeout`	Tempo limite de construção em segundos.

A visualização estará pronta para uso quando State for FINISHED.

Exemplo:

SHOW ALTER TABLE MATERIALIZED VIEW FROM test_db;

+--------+---------------+---------------------+---------------------+---------------+-----------------+----------+---------------+----------+------+----------+---------+
| JobId  | TableName     | CreateTime          | FinishTime          | BaseIndexName | RollupIndexName | RollupId | TransactionId | State    | Msg  | Progress | Timeout |
+--------+---------------+---------------------+---------------------+---------------+-----------------+----------+---------------+----------+------+----------+---------+
| 494349 | sales_records | 2020-07-30 20:04:56 | 2020-07-30 20:04:57 | sales_records | store_amt       | 494350   | 133107        | FINISHED |      | NULL     | 2592000 |
+--------+---------------+---------------------+---------------------+---------------+-----------------+----------+---------------+----------+------+----------+---------+

Listar visualizações materializadas

Liste todas as visualizações materializadas de uma tabela base juntamente com seus esquemas:

DESC <table_name> ALL;

Exemplo:

DESC duplicate_table ALL;

+-----------------+---------------+---------------+--------+--------------+------+-------+---------+-------+---------+------------+-------------+
| IndexName       | IndexKeysType | Field         | Type   | InternalType | Null | Key   | Default | Extra | Visible | DefineExpr | WhereClause |
+-----------------+---------------+---------------+--------+--------------+------+-------+---------+-------+---------+------------+-------------+
| duplicate_table | DUP_KEYS      | k1            | INT    | INT          | Yes  | true  | NULL    |       | true    |            |             |
|                 |               | k2            | INT    | INT          | Yes  | true  | NULL    |       | true    |            |             |
|                 |               | k3            | BIGINT | BIGINT       | Yes  | true  | NULL    |       | true    |            |             |
|                 |               | k4            | BIGINT | BIGINT       | Yes  | true  | NULL    |       | true    |            |             |
|                 |               |               |        |              |      |       |         |       |         |            |             |
| k2_order        | DUP_KEYS      | mv_k2         | INT    | INT          | Yes  | true  | NULL    |       | true    | `k2`       |             |
|                 |               | mv_k1         | INT    | INT          | Yes  | false | NULL    | NONE  | true    | `k1`       |             |
|                 |               |               |        |              |      |       |         |       |         |            |             |
| k1_k2           | DUP_KEYS      | mv_k2         | INT    | INT          | Yes  | true  | NULL    |       | true    | `k2`       |             |
|                 |               | mv_k1         | INT    | INT          | Yes  | true  | NULL    |       | true    | `k1`       |             |
|                 |               |               |        |              |      |       |         |       |         |            |             |
| k1_k2_sumk3     | AGG_KEYS      | mv_k1         | INT    | INT          | Yes  | true  | NULL    |       | true    | `k1`       |             |
|                 |               | mv_k2         | INT    | INT          | Yes  | true  | NULL    |       | true    | `k2`       |             |
|                 |               | mva_SUM__`k3` | BIGINT | BIGINT       | Yes  | false | NULL    | SUM   | true    | `k3`       |             |
+-----------------+---------------+---------------+--------+--------------+------+-------+---------+-------+---------+------------+-------------+

Visualizar a instrução de criação

Recupere o SQL usado para criar uma visualização materializada:

SHOW CREATE MATERIALIZED VIEW <mv_name> ON <table_name>;

Este comando funciona apenas para visualizações materializadas existentes. Não é possível consultar visualizações excluídas.

Exemplo:

SHOW CREATE MATERIALIZED VIEW id_col1 ON table3;

+-----------+----------+----------------------------------------------------------------+
| TableName | ViewName | CreateStmt                                                     |
+-----------+----------+----------------------------------------------------------------+
| table3    | id_col1  | create materialized view id_col1 as select id,col1 from table3 |
+-----------+----------+----------------------------------------------------------------+
1 row in set (0.00 sec)

Excluir uma visualização materializada

Cancelar uma criação em andamento

Se o job de criação ainda não terminou, cancele-o com:

CANCEL ALTER TABLE MATERIALIZED VIEW FROM <database>.<table_name>;

Parâmetro	Obrigatório	Descrição
`database`	Sim	Banco de dados que contém a tabela base.
`table_name`	Sim	Nome da tabela base.

Exemplo:

CANCEL ALTER TABLE MATERIALIZED VIEW FROM test_db.duplicate_table;

Se a visualização já estiver construída, este comando não terá efeito — use DROP em seu lugar.

Remover uma visualização materializada concluída

DROP MATERIALIZED VIEW [IF EXISTS] <mv_name> ON <table_name>;

Parâmetro	Obrigatório	Descrição
`IF EXISTS`	Não	Suprime o erro caso a visualização não exista.
`mv_name`	Sim	Nome da visualização materializada a ser removida.
`table_name`	Sim	Tabela base da visualização materializada.

Exemplo:

-- List materialized views before dropping
DESC duplicate_table ALL;

-- Drop the view named k1_k2
DROP MATERIALIZED VIEW k1_k2 ON duplicate_table;

-- Confirm the view is gone
DESC duplicate_table ALL;

Correspondência automática de consultas

Depois que uma visualização materializada é criada e seu State é FINISHED, todas as consultas existentes continuam tendo como alvo a tabela base sem alterações. O SelectDB seleciona transparentemente a melhor visualização materializada correspondente e reescreve a consulta internamente.

A tabela de correspondência para funções de agregação é:

Agregação da visualização materializada	Agregação da consulta correspondente
sum	sum
min	min
max	max
count	count
bitmap_union	bitmap_union, bitmap_union_count, count(distinct)
hll_union	hll_raw_agg, hll_union_agg, ndv, approx_count_distinct

Quando a agregação bitmap ou hll corresponde, o SelectDB reescreve o operador de agregação da consulta com base no esquema da visualização materializada.

Para confirmar se uma consulta está usando uma visualização materializada, execute EXPLAIN nela:

EXPLAIN <your_query>;

Na saída, procure por OlapScanNode. O atributo rollup mostra qual índice está sendo verificado. Se ele exibir o nome da visualização materializada em vez do nome da tabela base, a correspondência está confirmada. Para mais detalhes sobre como ler a saída do EXPLAIN, consulte Query Explain.

Melhores práticas

Contagem distinta exata com BITMAP_UNION

Cenário: Contagem de usuários únicos (ou qualquer coluna inteira de alta cardinalidade) agrupada por múltiplas dimensões.

Consulta típica:

SELECT advertiser, channel, COUNT(DISTINCT user_id)
FROM advertiser_view_record
GROUP BY advertiser, channel;

O uso de COUNT(DISTINCT ...) em tabelas grandes é custoso. Uma visualização materializada com BITMAP_UNION pré-deduplica os dados para que a consulta leia bitmaps agregados em vez de linhas brutas.

Crie a visualização:

CREATE MATERIALIZED VIEW advertiser_uv AS
SELECT advertiser, channel, bitmap_union(to_bitmap(user_id))
FROM advertiser_view_record
GROUP BY advertiser, channel;

Como user_id é INT, envolva-o com to_bitmap() antes de aplicar bitmap_union . Isso converte inteiros para o formato bitmap exigido pela função.

Após a construção da visualização (State = FINISHED), a consulta original é reescrita automaticamente para:

SELECT advertiser, channel, bitmap_union_count(to_bitmap(user_id))
FROM advertiser_uv
GROUP BY advertiser, channel;

Verifique a correspondência:

EXPLAIN SELECT advertiser, channel, COUNT(DISTINCT user_id)
FROM advertiser_view_record
GROUP BY advertiser, channel;

Na saída do EXPLAIN, localize OlapScanNode. Confirme se o valor de rollup é advertiser_uv. Verifique também se count(distinct) foi reescrito para bitmap_union_count(to_bitmap).

Contagem distinta aproximada com HLL_UNION

Cenário: Estimativa de contagens únicas onde a precisão exata não é necessária e a velocidade da consulta é prioritária.

CREATE MATERIALIZED VIEW approx_uv AS
SELECT advertiser, channel, hll_union(hll_hash(user_id))
FROM advertiser_view_record
GROUP BY advertiser, channel;

Consultas usando approx_count_distinct, ndv, hll_union_agg ou hll_raw_agg em user_id correspondem automaticamente a esta visualização.

user_id não pode ser DECIMAL ao usar o formato HLL_UNION(HLL_HASH(col)) .

Reordenação de índice de prefixo

Cenário: Uma consulta executada frequentemente filtra por uma coluna que não é a coluna principal da chave de ordenação da tabela base.

Se a tabela base usa DUPLICATE KEY (k1, k2, k3, k4), mas as consultas frequentemente filtram por k2:

CREATE MATERIALIZED VIEW k2_order AS
SELECT k2, k1 FROM duplicate_table ORDER BY k2;

Consultas com WHERE k2 = ... agora podem usar o índice de prefixo desta visualização, reduzindo significativamente o intervalo de varredura.

Solução de problemas

Erro: DATA_QUALITY_ERR: "The data quality does not satisfy, please check your data."

Este erro ocorre quando problemas de qualidade de dados ou alterações de esquema fazem com que o uso de memória exceda os limites durante a construção da visualização. Se a causa for pressão de memória, aumente o parâmetro memory_limitation_per_thread_for_schema_change_bytes.

Duas causas adicionais específicas para visualizações bitmap:

Inteiros negativos nos dados de source: BITMAP_UNION suporta apenas inteiros positivos. Se a coluna contiver valores negativos, a criação da visualização falhará. Verifique os dados de source e filtre ou transforme os valores negativos antes de criar a visualização.
Colunas de string: Use bitmap_hash ou bitmap_hash64 para calcular um valor hash a partir de colunas de string antes de aplicar bitmap_union.

Próximos passos

Query Explain — entenda a saída do EXPLAIN para depurar o planejamento de consultas e verificar a correspondência de visualizações materializadas.