Fully refreshing a materialized view - PolarDB: Central de Documentação do Alibaba Cloud

Uma materialized view é um objeto de banco de dados que otimiza consultas complexas ao pré-calcular e armazenar os resultados. Com o armazenamento desses resultados, você consulta diretamente os dados pré-calculados. Isso evita a reexecução de operações que consomem muitos recursos e acelera as consultas, trocando espaço de armazenamento por tempo de consulta.

As materialized views em clusters PolarDB for MySQL oferecem suporte à atualização completa. Tanto os métodos de atualização agendada quanto manual são suportados. As materialized views utilizam um mecanismo de tarefas em segundo plano para as atualizações. É possível usá-las com um {{XREF_0}} para acelerar consultas, o que melhora a eficiência das tarefas de atualização e reduz a carga nos nós somente leitura. Baseado na arquitetura HTAP do PolarDB, esse recurso aprimora ainda mais as capacidades de processamento analítico (AP).

Uma materialized view difere de uma view regular porque contém uma cópia física dos dados que podem ser consultados diretamente. Ela é composta pela view em si e por sua tabela base física subjacente.

{{XREF_1}}: A criação da view e a materialização dos dados são duas etapas distintas. O processo de materialização é uma operação assíncrona enviada para uma tarefa em segundo plano, responsável por popular a tabela base física. O nome dessa tabela física fica associado à view. As tarefas de materialização e atualização incluem operações de consulta e inserção. A operação de consulta pode ser descarregada para um nó somente leitura com IMCI, acelerando as consultas e reduzindo o impacto no nó primário.
{{XREF_2}}: As consultas em uma materialized view são executadas na tabela base física subjacente.
{{XREF_3}}: O processo de atualização completa cria uma nova tabela base física oculta. Após a execução da instrução de atualização nessa tabela oculta, o sistema troca as tabelas base original e oculta. Essa ação atualiza a tabela de metadados com as informações da tabela base física mais recente e a sincroniza com a estrutura em memória para consultas.
{{XREF_4}}: Consulte a fila de tarefas de atualização em segundo plano para visualizar detalhes das tarefas. Também é possível monitorar e gerenciar tarefas pausando ou reiniciando atualizações agendadas de uma materialized view nos níveis de tabela, banco de dados ou global.

Requisitos

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

MySQL 8.0.1, com versão secundária do mecanismo 8.0.1.1.51 ou posterior.
MySQL 8.0.2, com versão secundária do mecanismo 8.0.2.2.31 ou posterior.

Benefícios

Evita cálculos repetidos: Para consultas complexas e intensivas em recursos, os resultados são calculados uma única vez e reutilizados diversas vezes, melhorando significativamente a eficiência das consultas.
Pré-agregação de dados: Agregue previamente dados para relatórios diários, semanais e mensais, acelerando a geração de relatórios e a análise de dados.
Consultas otimizadas em grandes conjuntos de dados: Reduz varreduras diretas em grandes volumes de dados brutos e acelera o tempo de resposta para consultas analíticas.
Suporte à análise multidimensional: Pré-calcule dados agregados para diferentes combinações de dimensões, oferecendo suporte eficiente para OLAP e outros cenários de análise multidimensional.

Casos de uso

As materialized views otimizam o desempenho das consultas ao pré-calcular e armazenar resultados. São especialmente úteis para processar dados em grande escala e consultas complexas. Elas reduzem a sobrecarga de computações em tempo real e simplificam lógicas de consulta complexas, sendo ideais para geração rápida de relatórios e análise de dados.

Evitar cálculos repetidos: Ideal para armazenar resultados de cálculos intensivos em recursos e frequentemente consultados. O uso de materialized views evita o recálculo dos mesmos dados, melhorando significativamente a eficiência das consultas.
Pré-agregação de dados: Na geração de relatórios e análise de dados, as materialized views podem pré-agregar informações, como vendas diárias, semanais ou mensais e dados de comportamento do usuário. Armazenar previamente os resultados de cálculo reduz o tempo e os recursos necessários para computação em tempo real.
Consultas otimizadas em grandes conjuntos de dados: Consultar diretamente grandes volumes de dados brutos pode ser demorado devido a varreduras frequentes e em larga escala. As materialized views reduzem a necessidade de acessar dados brutos diretamente, acelerando assim as consultas e análises.
Suporte à análise multidimensional: É possível pré-calcular dados agregados para diferentes combinações de dimensões, permitindo respostas rápidas e eficientes para análises multidimensionais baseadas em cenários.

Parâmetros

Os parâmetros do cluster PolarDB diferem entre o console e uma sessão de banco de dados. As diferenças são as seguintes:

No console do PolarDB:
- Compatibilidade: Para manter compatibilidade com arquivos de configuração do MySQL, alguns parâmetros de cluster no console do PolarDB possuem o prefixo loose_.
- Procedimento: Localize e modifique os parâmetros que possuem o prefixo loose_.
Em uma sessão de banco de dados (usando linha de comando ou cliente)
- Procedimento: Ao usar o comando SET para modificar um parâmetro em uma sessão de banco de dados, remova o prefixo loose_ e utilize o nome original do parâmetro.

Parâmetro

Descrição

loose_materialized_view_enabled

Especifica se as materialized views devem ser ativadas. Valores válidos:

ON (padrão): Ativa o recurso.
OFF: Desativa o recurso.

loose_enable_materialized_view_parallel

Especifica se o recurso de consulta paralela para materialized views deve ser ativado. Valores válidos:

ON: Ativado.
OFF (padrão): Desativado.

Criar uma materialized view

Sintaxe

SELECT TABLE_SCHEMA, TABLE_NAME, IS_STOPPED FROM mysql.view_materialized_info;

Permissões

A permissão CREATE no banco de dados onde a materialized view está localizada.
A permissão SELECT nas colunas relevantes (ou em toda a tabela) de todas as tabelas base da materialized view.

Parâmetros

Parâmetro	Obrigatório	Descrição
view_name	Sim	O nome da materialized view.
column_list	Não	As colunas a serem incluídas na materialized view. Para atribuir nomes específicos às colunas da view, liste-as na `column_list`, separadas por vírgulas (`,`).
REFRESH	Não	Especifica a política de atualização para a materialized view. COMPLETE (padrão): Atualização completa. FAST: Atualização incremental. Nota Este tópico descreve a política de atualização completa. Para obter informações sobre a política de atualização incremental, consulte {{XREF_6}}.
ON	Não	Especifica o tipo de materialização da materialized view. COMMIT: Materialized view em tempo real. DEMAND (padrão): Materialized view assíncrona. Nota Atualmente, apenas o tipo de materialized view assíncrona é suportado.
START WITH	Não	A hora de início para a atualização automática de uma materialized view assíncrona.
NEXT	Não	O intervalo de atualização para uma materialized view assíncrona. Nota Se você não especificar NEXT, a view só poderá ser atualizada manualmente.
QUERY REWRITE	Não	Especifica se a materialized view suporta reescrita de consulta. DISABLE (padrão): Não suportado. ENABLE: Suportado. Nota Atualmente, a reescrita de consulta não é suportada.
MATERIALIZED OPTIONS	Não	Especifica parâmetros estendidos para a materialized view. O valor é uma string, geralmente um objeto JSON. Para mais detalhes, consulte {{XREF_7}}.
select_statement	Sim	A instrução de consulta que define os dados da materialized view. Esta instrução recupera dados das tabelas base e armazena o resultado na materialized view.

Uso de mv_options

mv_options é especificado pela cláusula MATERIALIZED OPTIONS = 'json_options'. A cláusula deve aparecer antes de AS select_statement, e json_options deve ser um objeto JSON válido.

Versões suportadas:
- MySQL 8.0.1, versão secundária do kernel 8.0.1.1.55 ou posterior.
- MySQL 8.0.2, versão secundária do kernel 8.0.2.2.35 ou posterior.

mv_options suporta os seguintes campos:

Campo	Tipo	Valor	Descrição
`order_key`	STRING	Um nome de coluna ou texto de expressão.	Define a `order_key` da tabela contêiner da materialized view.
`engine`	STRING	`InnoDB` `XEngine`	Especifica o mecanismo de armazenamento usado pela tabela contêiner da materialized view. O valor não diferencia maiúsculas de minúsculas.
`table_format`	STRING	`row` `column`	Especifica o formato de armazenamento em linha ou em coluna da tabela contêiner. O valor não diferencia maiúsculas de minúsculas. O mecanismo `InnoDB` suporta apenas `row`. O mecanismo `XEngine` suporta tanto `row` quanto `column`, e é normalmente usado junto com `engine: "xengine"`.
`refresh_policy`	STRING	`MASTER` `PREFER_TPRO` `PREFER_IMCI` `FORCE_TPRO` `FORCE_IMCI` `COST`	Especifica a política de push-down para a tarefa de atualização completa. O valor não diferencia maiúsculas de minúsculas. Uma string vazia ou um valor desconhecido reverte para a política padrão especificada pela variável do sistema. `MASTER`: atualização no nó primário. `PREFER_TPRO`: prefere atualizar em um nó somente leitura (RO) com armazenamento em linha. Atualiza no nó primário se não houver nenhum nó RO com armazenamento em linha. `PREFER_IMCI`: prefere atualizar em um nó RO com índice de armazenamento em coluna. Atualiza no nó primário se não houver nenhum nó RO com índice de armazenamento em coluna. `FORCE_TPRO`: força a atualização em um nó RO com armazenamento em linha. Retorna erro se não houver nenhum nó RO com armazenamento em linha. `FORCE_IMCI`: força a atualização em um nó RO com índice de armazenamento em coluna. Retorna erro se não houver nenhum nó RO com índice de armazenamento em coluna. `COST`: escolhe entre um nó RO com armazenamento em linha e um nó RO com índice de armazenamento em coluna com base no custo da consulta para executar a atualização.
`unique_check`	INT	`1` `0`	Usado para tabelas contêiner com armazenamento em coluna XEngine. `0` flexibiliza a verificação de unicidade durante a atualização. `1` ou a omissão do campo mantém a verificação padrão.

Nota

engine, table_format, refresh_policy e unique_check são suportados apenas no MySQL 8.0.2 por enquanto.

Observações:
- MATERIALIZED OPTIONS = '' é tratado como ausência de parâmetros estendidos definidos.
- Quando SHOW CREATE TABLE gera a definição da materialized view, a cláusula MATERIALIZED OPTIONS configurada é incluída.
- A camada de sintaxe aceita apenas uma string. Se a análise subsequente falhar, um WARNING é registrado e o processamento continua com o comportamento padrão, como se nenhum parâmetro correspondente tivesse sido analisado.

Exemplo:

SELECT TABLE_SCHEMA, TABLE_NAME, IS_STOPPED FROM mysql.view_materialized_info;

Exemplo

Crie as tabelas table1 e table2 para usar como tabelas base da materialized view.

SELECT TABLE_SCHEMA, TABLE_NAME, IS_STOPPED FROM mysql.view_materialized_info;

Crie uma materialized view.

SELECT TABLE_SCHEMA, TABLE_NAME, IS_STOPPED FROM mysql.view_materialized_info;

Crie uma materialized view com mv_options.

SELECT TABLE_SCHEMA, TABLE_NAME, IS_STOPPED FROM mysql.view_materialized_info;

Consultar uma materialized view

Nota

Para consultar uma materialized view, é necessária a permissão SELECT na view. Não é necessária a permissão SELECT nas tabelas base subjacentes da view.

Consultar uma materialized view

SELECT TABLE_SCHEMA, TABLE_NAME, IS_STOPPED FROM mysql.view_materialized_info;

A saída esperada é:

SELECT TABLE_SCHEMA, TABLE_NAME, IS_STOPPED FROM mysql.view_materialized_info;

Consultar definição da view

SELECT TABLE_SCHEMA, TABLE_NAME, IS_STOPPED FROM mysql.view_materialized_info;

A saída esperada é:

SELECT TABLE_SCHEMA, TABLE_NAME, IS_STOPPED FROM mysql.view_materialized_info;

Consultar tabela do sistema

SELECT TABLE_SCHEMA, TABLE_NAME, IS_STOPPED FROM mysql.view_materialized_info;

Descrições dos campos

Campo	Descrição
TABLE_SCHEMA	O nome do banco de dados onde a materialized view está localizada.
TABLE_NAME	O nome da materialized view.
IS_DROPPED	Indica se a view foi removida. 0: Não 1: Sim
FIRST_REFRESH_TIME	Se a atualização automática estiver configurada, esta é a hora da primeira atualização.
TIME_ZONE	A configuração de fuso horário.
REFRESH_CONDITION	O mecanismo de acionamento da atualização. DEMAND: Atualização assíncrona, acionada automaticamente por agendamento ou manualmente. COMMIT: Atualização em tempo real.
REFRESH_STRATEGY	A política de atualização para a materialized view. COMPLETE: Atualização completa. FAST: Atualização incremental.
REFRESH_START_TIME	A hora de início configurada para a primeira atualização.
NEXT_TIME_EXPRESSION	A definição para a próxima hora de atualização de uma materialized view assíncrona.
LAST_START_TIME	A hora em que a última atualização realmente começou.
LAST_END_TIME	A hora em que a última atualização realmente terminou.
CONTAINER_TABLE_POSTFIX	O sufixo da tabela física usada para armazenamento de dados.
EXPIRED_TABLE_POSTFIX	O sufixo da tabela física que contém dados expirados após uma atualização.
IS_STOPPED	Indica se a atualização agendada está parada. 0: Não 1: Sim
CREATE_TIME	A hora em que os metadados da materialized view foram criados.
UPDATE_TIME	A hora em que os metadados da materialized view foram atualizados pela última vez.

A saída esperada é:

SELECT TABLE_SCHEMA, TABLE_NAME, IS_STOPPED FROM mysql.view_materialized_info;

Atualizar uma materialized view

Atualmente, as materialized views suportam apenas atualização completa. Esse processo usa um método out-of-place: ele cria uma tabela base física oculta, executa a instrução de atualização nela e, em seguida, troca as tabelas base original e oculta. Portanto, uma atualização completa requer espaço de armazenamento adicional. Sua principal vantagem é o suporte a qualquer consulta SQL.

SELECT TABLE_SCHEMA, TABLE_NAME, IS_STOPPED FROM mysql.view_materialized_info;

As materialized views suportam dois métodos de atualização: atualização manual e atualização agendada.

Atualização manual

Se uma materialized view não estiver configurada para atualização agendada, ou se o intervalo de atualização for longo, use o comando REFRESH MATERIALIZED VIEW para atualizar os dados manualmente.

SELECT TABLE_SCHEMA, TABLE_NAME, IS_STOPPED FROM mysql.view_materialized_info;

Exemplo

Insira novos dados de teste.

SELECT TABLE_SCHEMA, TABLE_NAME, IS_STOPPED FROM mysql.view_materialized_info;

Atualize manualmente a materialized view.

SELECT TABLE_SCHEMA, TABLE_NAME, IS_STOPPED FROM mysql.view_materialized_info;

Consulte a materialized view.

SELECT TABLE_SCHEMA, TABLE_NAME, IS_STOPPED FROM mysql.view_materialized_info;

A saída esperada é:

SELECT TABLE_SCHEMA, TABLE_NAME, IS_STOPPED FROM mysql.view_materialized_info;

Atualização agendada

Ao criar uma materialized view, especifique uma hora de início para atualizações agendadas usando START WITH e configure um intervalo de atualização automática usando NEXT. O sistema agenda automaticamente a tarefa de atualização com base nas configurações especificadas.

SELECT TABLE_SCHEMA, TABLE_NAME, IS_STOPPED FROM mysql.view_materialized_info;

Remover uma materialized view

Nota

A remoção de uma materialized view requer a permissão DROP no banco de dados onde a materialized view está localizada.

Sintaxe

SELECT TABLE_SCHEMA, TABLE_NAME, IS_STOPPED FROM mysql.view_materialized_info;

Exemplo

SELECT TABLE_SCHEMA, TABLE_NAME, IS_STOPPED FROM mysql.view_materialized_info;

Monitorar e gerenciar tarefas

A atualização agendada de uma materialized view depende do mecanismo de agendamento automático do sistema. Talvez seja necessário monitorar e gerenciar um grande número de tarefas agendadas. Pause ou reinicie tarefas de atualização agendada para uma materialized view nos níveis de tabela, banco de dados ou global.

Visualizar fila de tarefas

Consulte a view de sistema information_schema.materialized_view_refresh_queue para visualizar a fila de atualização.

SELECT TABLE_SCHEMA, TABLE_NAME, IS_STOPPED FROM mysql.view_materialized_info;

Pausar fila de tarefas

Por tabela

SELECT TABLE_SCHEMA, TABLE_NAME, IS_STOPPED FROM mysql.view_materialized_info;

Por banco de dados

SELECT TABLE_SCHEMA, TABLE_NAME, IS_STOPPED FROM mysql.view_materialized_info;

Globalmente

SELECT TABLE_SCHEMA, TABLE_NAME, IS_STOPPED FROM mysql.view_materialized_info;

Reiniciar fila de tarefas

Por tabela

SELECT TABLE_SCHEMA, TABLE_NAME, IS_STOPPED FROM mysql.view_materialized_info;

Por banco de dados

SELECT TABLE_SCHEMA, TABLE_NAME, IS_STOPPED FROM mysql.view_materialized_info;

Globalmente

SELECT TABLE_SCHEMA, TABLE_NAME, IS_STOPPED FROM mysql.view_materialized_info;

Verificar status de pausa ou reinício

Após executar um comando STOP ou RESTART, consulte a coluna IS_STOPPED da tabela mysql.view_materialized_info para confirmar se a operação teve efeito:

SELECT TABLE_SCHEMA, TABLE_NAME, IS_STOPPED FROM mysql.view_materialized_info;

IS_STOPPED=1: A tarefa de atualização agendada está pausada.
IS_STOPPED=0: A tarefa de atualização agendada está em execução.

Nota

A coluna IS_STOPPED não está disponível na view de sistema information_schema.materialized_view_refresh_queue. Use mysql.view_materialized_info em vez disso.