Curso
Introdução aos Bancos de Dados Relacionais em SQL
4 h
178.9K
As visualizações materializadas são um recurso avançado dos bancos de dados SQL que ajudam a otimizar o desempenho da consulta, armazenando os resultados de uma consulta fisicamente no disco, oferecendo um desempenho mais rápido da consulta ao reduzir a recomputação. Isso os torna particularmente úteis para lidar com consultas complexas e com uso intensivo de recursos que envolvem junções, agregações e grandes conjuntos de dados.
Para começar, recomendo que você faça o curso de Introdução ao SQL do DataCamp e o curso de habilidades SQL Fundamentals para aprender os fundamentos do SQL e como extrair dados usando consultas. A folha de dicas básicas de SQL será um guia útil para as funções comuns de SQL para filtragem e agregação de dados.
O que são visualizações materializadas do SQL?
As visualizações materializadas são um tipo especial de objeto de banco de dados que armazena fisicamente os resultados de uma consulta, em vez de calculá-los em tempo real, como as visualizações comuns. Enquanto uma visualização SQL normal é uma consulta SQL salva que gera seus resultados dinamicamente sempre que é acessada, uma visualização materializada pré-computa e armazena os dados em uma estrutura semelhante a uma tabela.
Ao armazenar o conjunto de resultados em disco, as exibições materializadas podem reduzir significativamente a carga em um banco de dados, melhorar o desempenho da consulta e ajudar a otimizar o processamento de operações com uso intensivo de computação.
Como criar uma visualização materializada no SQL
O processo de criação de uma visualização materializada envolve o uso da sintaxe CREATE MATERIALIZED VIEW, que varia ligeiramente entre os diferentes bancos de dados SQL. Os métodos a seguir mostram como você pode criar visualizações materializadas no SQL Server, PostgreSQL e Oracle.
Visualização materializada no PostgreSQL
No PostgreSQL, você pode criar uma visualização materializada usando a seguinte sintaxe. Este exemplo cria uma visualização materializada chamada sales_summary que agrega a quantidade total e a receita de cada produto.
-- Create a materialized view to summarize sales data
CREATE MATERIALIZED VIEW sales_summary AS
SELECT product_id,
SUM(quantity) AS total_quantity,
SUM(price * quantity) AS total_revenue
FROM sales
GROUP BY product_id;Visualização materializada no SQL Server
No SQL Server, as visualizações materializadas são chamadas de "Visualizações indexadas". As tabelas subjacentes devem atender a requisitos específicos para criar uma exibição indexada, como habilitar a opção WITH SCHEMABINDING.
A opção WITH SCHEMABINDING garante que o esquema não possa ser alterado enquanto a exibição indexada existir. Um índice clusterizado exclusivo deve ser criado para que a visualização seja materializada.
-- Create an indexed view with schema binding to summarize sales data
CREATE VIEW sales_summary
WITH SCHEMABINDING
AS
SELECT product_id,
COUNT_BIG(*) AS record_count,
SUM(ISNULL(quantity, 0)) AS total_quantity,
SUM(ISNULL(price, 0) * ISNULL(quantity, 0)) AS total_revenue
FROM sales
GROUP BY product_id;
GO
-- Create a unique clustered index to materialize the view
CREATE UNIQUE CLUSTERED INDEX IX_sales_summary
ON sales_summary (product_id);
GOSe você quiser saber mais sobre o SQL Server, recomendo que confira nossa trilha de habilidades SQL Server Fundamentals para se familiarizar com as diferentes habilidades SQL para análise de dados.
Visualização materializada no Oracle
A sintaxe para criar visualizações materializadas no Oracle é semelhante à do banco de dados PostgreSQL. Também podemos especificar opções de atualização, como ON DEMAND ou ON COMMIT.
-- Create a materialized view to summarize sales data
CREATE MATERIALIZED VIEW sales_summary
BUILD IMMEDIATE
REFRESH FAST ON DEMAND
AS
SELECT product_id, SUM(quantity) AS total_quantity, SUM(price * quantity) AS total_revenue
FROM sales
GROUP BY product_id;Visualização materializada em bancos de dados distribuídos
As visualizações materializadas também podem especificar métodos de distribuição para melhorar o desempenho de bancos de dados como o Azure Synapse ou o Amazon Redshift, que oferecem suporte a data warehouses distribuídos.
Distribuição de hash para o Amazon Redshift
A consulta abaixo cria uma visualização materializada chamada sales_summary que agrega a quantidade total e a receita por product_id. As opções DISTSTYLE KEY e DISTKEY(product_id) garantem que os dados sejam distribuídos entre os nós com base em product_id, melhorando o desempenho das consultas que se unem a essa coluna.
-- Create a materialized view with key-based distribution for efficient joins
CREATE MATERIALIZED VIEW sales_summary
DISTSTYLE KEY
DISTKEY(product_id)
AS
SELECT product_id,
SUM(quantity) AS total_quantity,
SUM(price * quantity) AS total_revenue
FROM sales
GROUP BY product_id;Distribuição Round-Robin para o Azure Synapse
Essa consulta cria uma exibição materializada chamada sales_summary que agrega a quantidade total e a receita por product_id. A distribuição ROUND_ROBIN distribui uniformemente os dados entre os nós, o que é útil para cenários que não dependem muito de junções.
-- Create a materialized view with round-robin distribution for balanced data storage
CREATE MATERIALIZED VIEW sales_summary
WITH (DISTRIBUTION = ROUND_ROBIN)
AS
SELECT product_id,
SUM(quantity) AS total_quantity,
SUM(price * quantity) AS total_revenue
FROM sales
GROUP BY product_id;Atualização de uma visualização materializada no SQL
Os dados em visualizações materializadas podem ser atualizados para permanecerem atualizados com as tabelas subjacentes. A escolha do método de atualização depende dos requisitos comerciais e das considerações de desempenho do banco de dados específico. Vamos examinar os seguintes métodos de atualização de dados de exibições materializadas.
Atualização manual
Na atualização manual, a exibição materializada é atualizada somente quando solicitada explicitamente pelo usuário. Essa abordagem oferece o maior controle sobre quando os dados são atualizados, tornando-a adequada para cenários em que os dados são alterados com pouca frequência ou as atualizações são realizadas fora do horário de pico.
A consulta a seguir mostra o método de atualização manual no PostgreSQL.
REFRESH MATERIALIZED VIEW sales_summary;Atualização periódica
A visualização materializada é atualizada automaticamente em intervalos especificados durante o período de atualização, garantindo que os dados estejam atualizados sem a intervenção do usuário. Esse método é útil para aplicativos sensíveis ao tempo em que os dados precisam ser relativamente atuais.
O exemplo abaixo mostra como você pode incluir atualizações periódicas no Oracle definindo as agendas de atualização diretamente no comando CREATE MATERIALIZED VIEW. O tempo de atualização é definido em intervalos de uma hora.
-- Create a materialized view to aggregate sales data
-- Set to refresh completely every hour
CREATE MATERIALIZED VIEW sales_summary
REFRESH COMPLETE START WITH (SYSDATE) NEXT (SYSDATE + 1/24)
AS
SELECT product_id,
SUM(quantity) AS total_quantity,
SUM(price * quantity) AS total_revenue
FROM sales
GROUP BY product_id;Atualização sob demanda
A atualização sob demanda ocorre sempre que os dados subjacentes são alterados, normalmente por meio de um mecanismo de acionamento. Isso garante que a visualização materializada sempre contenha os dados atualizados. Por exemplo, no PostgreSQL, os acionadores podem ser configurados para atualizar a visualização quando ocorrerem alterações na tabela subjacente.
Atualizações completas ou incrementais
Você pode atualizar os dados em atualizações completas ou incrementais em visualizações materializadas. A tabela a seguir resume os dois métodos e os casos de uso.
| Tipo de atualização | Descrição | Vantagens | Desvantagens |
|---|---|---|---|
| Atualização completa | Recarrega todo o conjunto de dados, substituindo todos os dados existentes na exibição | - Simples de implementar - Reconstrói toda a visualização |
- Uso intensivo de recursos para grandes conjuntos de dados - Tempos de atualização mais longos |
| Atualização incremental | Atualiza apenas as partes alteradas da exibição | - Mais eficiente, processando apenas dados modificados - Adequado para grandes conjuntos de dados com alterações frequentes |
- Requer configuração adicional (por exemplo, registros para rastrear alterações) - Nem sempre há suporte para todas as consultas |
Práticas recomendadas para visualizações materializadas
Ao usar visualizações materializadas, é importante que você considere as seguintes práticas para otimizar o uso.
- Escolhendo as consultas certas para materializar: Materialize consultas complexas e com uso intensivo de recursos, como junções, agregações e subconsultas. Essas consultas se beneficiariam dos resultados pré-computados, reduzindo a carga no banco de dados.
- Equilíbrio entre atualização de dados e desempenho: Dependendo do seu caso de uso, escolha a estratégia de atualização apropriada, como métodos de atualização manual, periódica ou sob demanda. Use a atualização incremental para reduzir a carga computacional e monitore a frequência de alteração de dados para decidir os intervalos de atualização.
- Usando Materialized Views para otimizar cargas de trabalho com muitas consultas: Aproveite as visualizações materializadas para relatórios e painéis de BI em que tempos de resposta rápidos são essenciais. Você também pode indexar as colunas usadas nas exibições materializadas para filtragem e classificação mais rápidas.
Visualizações materializadas em diferentes sistemas de banco de dados
Como você viu, diferentes sistemas de banco de dados oferecem suporte variado para visualizações materializadas. A tabela a seguir resume os recursos exclusivos e as limitações das visualizações materializadas nesses bancos de dados.
| Sistema de banco de dados | Métodos de atualização | Atualização incremental | Atualização automática | Recursos especiais/limitações |
|---|---|---|---|---|
| PostgreSQL | Manual (REFRESCAR VISÃO MATERIALIZADA) | Não | Não | Não possui atualização incremental nativa. É necessário o agendamento manual. |
| SQL Server | Automático (visualizações indexadas) | Sim (sincronização automática) | Sim |
Você precisa do site |
| Oráculo | Manual, no compromisso, programado | Sim (atualização rápida) | Sim | Oferece suporte à atualização rápida, ao particionamento e ao paralelismo. Requer a visualização de registros. |
| Amazon Redshift | Manual, programado | Sim | Sim | Oferece suporte a dados distribuídos com distribuição de hash ou round-robin. |
| MySQL | Não suportado nativamente | Não | Não | Soluções alternativas necessárias (por exemplo, tabelas temporárias, ferramentas de terceiros). |
| Azure Synapse | Manual, programado | Sim | Sim | Permite diferentes estratégias de distribuição para otimização. |
Se você usa o SQL Server como seu banco de dados preferido, recomendo que faça o curso Introdução ao SQL Server da DataCamp para dominar os conceitos básicos do Microsoft SQL Server para análise de dados. Além disso, confira nosso curso de carreira de Desenvolvedor do SQL Server para entender como otimizar consultas e solucionar problemas no SQL Server.
Coisas adicionais a serem consideradas
Embora as exibições materializadas do SQL sejam úteis para a otimização de consultas, elas também apresentam alguns desafios e limitações. A seguir, você encontrará os problemas comuns com visualizações materializadas e como resolvê-los.
- Armazenamento em cima: As visualizações materializadas armazenam os resultados da consulta fisicamente no disco, o que aumenta os requisitos de armazenamento. Para evitar o consumo de espaço de armazenamento desnecessário, materialize as exibições somente para consultas com uso intensivo de recursos e particione as exibições materializadas para conjuntos de dados grandes.
- Atualizar custos e atualizar custos indiretos: Manter as exibições materializadas em sincronia com as tabelas subjacentes pode exigir muitos recursos, especialmente no caso de exibições que requerem atualizações frequentes ou envolvem cálculos complexos. Para evitar a sobrecarga de atualização, use a atualização incremental onde houver suporte ou defina os intervalos de atualização apropriados quando o uso do banco de dados for menor.
- Consistência e sincronização de dados: As visualizações materializadas podem ficar desatualizadas se os dados subjacentes forem alterados com frequência, o que leva a problemas de dados obsoletos. Para evitar esse problema, selecione a estratégia de atualização apropriada e monitore as alterações nos dados para ajustar a estratégia de atualização conforme necessário.
- Manutenção de despesas gerais: As visualizações materializadas exigem manutenção contínua, como a definição de cronogramas de atualização adequados, o monitoramento do uso do armazenamento e o rastreamento das dependências das tabelas subjacentes. Para superar esse desafio, sempre use agendas de atualização automatizadas, monitore o desempenho do sistema e configure alertas para atualizações com falha.
Conclusão
As visualizações materializadas são úteis em bancos de dados SQL para otimizar o desempenho das consultas. Eles armazenam os resultados da consulta fisicamente no disco, oferecendo um desempenho de consulta mais rápido ao reduzir a recomputação. Esse recurso faz com que as exibições materializadas sejam úteis para lidar com consultas complexas e com uso intensivo de recursos que envolvem uniões, agregações e grandes conjuntos de dados. Entender como implementar visualizações materializadas em diferentes bancos de dados ajudará você a aprimorar suas habilidades de otimização de consultas e bancos de dados.
Se você deseja aprimorar suas habilidades em SQL, recomendo que experimente o curso de carreira Associate Data Analyst in SQL da DataCamp para se tornar um analista de dados proficiente. O curso Reporting in SQL também ajudará você a se tornar proficiente na criação de relatórios e painéis complexos para a apresentação eficaz de dados. Por fim, você deve obter a Certificação SQL Associate para demonstrar seu domínio no uso do SQL para resolver problemas de negócios e se destacar entre outros profissionais.
Torne-se certificado em SQL
Comprove que suas habilidades em SQL estão prontas para o trabalho com uma certificação.
Eu crio artigos que simplificam a ciência e a análise de dados, tornando-as fáceis de entender e acessíveis.
Perguntas frequentes
O que é uma visualização materializada no SQL?
Uma visualização materializada é um objeto de banco de dados que armazena fisicamente o resultado de uma consulta, otimizando o desempenho ao evitar a recomputação.
Como uma visualização materializada é diferente de uma visualização normal?
Diferentemente das exibições regulares, que recuperam dados dinamicamente em cada acesso, as exibições materializadas armazenam dados como tabelas físicas, permitindo uma execução mais rápida das consultas.
Qual é a diferença entre atualização completa e incremental?
Uma atualização completa recalcula a exibição inteira, enquanto uma atualização incremental atualiza apenas os dados alterados, aumentando a eficiência.
Quais bancos de dados oferecem suporte a visualizações materializadas?
PostgreSQL, SQL Server (exibições indexadas), Oracle, Amazon Redshift e Azure Synapse Analytics oferecem suporte a exibições materializadas, cada um com diferentes recursos e limitações, enquanto o MySQL não tem suporte nativo.
As visualizações materializadas consomem armazenamento adicional?
Sim, como elas armazenam dados fisicamente, as visualizações materializadas aumentam os requisitos de armazenamento.
Tópicos
