8.1 Por que a desnormalização é necessária?

A operação mais cara computacionalmente entre tabelas grandes é a junção. Conseqüentemente, se em uma consulta for necessário "ventilar" várias tabelas compostas por muitos milhões de linhas, o DBMS gastará muito tempo nesse processamento.

O usuário neste momento pode se afastar para tomar café. A interatividade do processamento praticamente desaparece e se aproxima da do processamento em lote. Pior ainda, no modo batch, o usuário recebe todos os dados solicitados na véspera pela manhã e trabalha com calma com eles, preparando novos pedidos para a noite.

Para evitar a situação de junções pesadas, as tabelas são desnormalizadas. Mas não de qualquer maneira. Existem algumas regras que permitem considerar tabelas desnormalizadas transacionalmente como "normalizadas" de acordo com as regras de construção de tabelas para data warehouses.

Existem dois esquemas principais considerados “normais” no processamento analítico: “floco de neve” e “estrela”. Os nomes refletem bem a essência e seguem diretamente da imagem das tabelas relacionadas.

Em ambos os casos, as chamadas tabelas de fatos são o elemento central do esquema, contendo os eventos, transações, documentos e outras coisas interessantes de interesse do analista. Mas se em um banco de dados transacional um documento é “manchado” em várias tabelas (pelo menos duas: cabeçalhos e linhas de conteúdo), então na tabela de fatos um documento, mais precisamente, cada uma de suas linhas ou um conjunto de linhas agrupadas, corresponde para um registro.

Isso pode ser feito desnormalizando as duas tabelas acima.

8.2 Exemplo de desnormalização

Agora você pode avaliar como será mais fácil para o DBMS executar uma consulta, por exemplo, do seguinte tipo: determinar o volume de vendas de farinha para os clientes da Pirozhki LLC e Vatrushki CJSC no período.

Em um banco de dados transacional normalizado:


SELECT
   SUM(dl.qty) AS total qty, SUM(dl.price) AS total amount, c.name 
FROM 
   docs d
   INNER JOIN doc lines dl ON d.id doc = dl.id doc 
   INNER JOIN customers c ON d.id customer = c.id customer 
   INNER JOIN products p ON dl.id product = p.id product 
WHERE
   c.name IN (’Pirozhki LLC’,	’Vatrushki CJSC’) AND
   p.name = ’Flour’ AND
   d.date BETWEEN ’2014-01-01’ AND ’2014-02-01’
GROUP BY c.name

No banco de dados analítico:


SELECT
   SUM(s.qty) AS total_qty, SUM(s.amount) AS total_amount, c.name
FROM
   sales s
   INNER JOIN customers c ON d.id_customer = c.id_customer
   INNER JOIN products p ON dl.id_product = p.id_product
WHERE
   c.name IN ('Pirozhki LLC', 'Vatrushki CJSC') AND
   p.name = 'Flour' AND
   s.date BETWEEN '2014-01-01' AND '2014-02-01'
GROUP BY c.name

Em vez de uma junção pesada entre duas tabelas de documentos e sua composição com milhões de linhas, o SGBD trabalha diretamente com a tabela de fatos e junções leves com pequenas tabelas auxiliares, das quais você também pode prescindir, conhecendo os identificadores.

Java webinar 

SELECT
   SUM(s.qty) AS total_qty, SUM(s.amount) AS total_amount, s.id_customer
FROM
   sales s
WHERE
   s.id_customer IN (1025, 20897) AND
   s.id_product = 67294 AND
   s.date BETWEEN '2014-01-01' AND '2014-02-01'
GROUP BY s.id_customer

Vamos voltar aos esquemas "estrela" e "floco de neve". Nos bastidores da primeira foto havia mesas de clientes, seus grupos, lojas, vendedores e, de fato, mercadorias. Quando desnormalizadas, essas tabelas, chamadas de dimensões, também são unidas à tabela de fatos. Se a tabela de fatos se referir a tabelas de dimensões que possuem links para outras dimensões (dimensões do segundo nível e acima), esse esquema é chamado de "floco de neve".

Como você pode ver, para consultas que incluem filtragem por grupos de clientes, é necessário fazer uma conexão adicional.


SELECT sum(amount)
FROM sales s
   INNER JOIN customers c ON s.id_customer = c.id_customer
WHERE c.id_customer_group IN (1, 2, 10, 55)

Nesse caso, a desnormalização pode continuar e descartar a dimensão de segundo nível para a primeira, facilitando a consulta à tabela de fatos.

Um esquema no qual uma tabela de fatos refere-se apenas a dimensões que não possuem um segundo nível é chamado de esquema em estrela. O número de tabelas de medição corresponde ao número de "raios" na estrela.

O esquema Star elimina completamente a hierarquia de dimensões e a necessidade de juntar as tabelas correspondentes em uma única consulta.


SELECT sum(amount)
FROM sales s
WHERE s.id_customer_group IN (1, 2, 10, 55)

A desvantagem da desnormalização é sempre a redundância , que causa um aumento no tamanho do banco de dados em aplicativos transacionais e analíticos. Vamos calcular um delta aproximado no exemplo acima de conversão de "floco de neve" em "estrela".

Em alguns SGBDs, como o Oracle, não existem tipos inteiros especiais no nível das definições do esquema do banco de dados, deve-se utilizar o tipo booleano genérico numeric(N), onde N é o número de bits armazenados. O tamanho de armazenamento desse número é calculado usando uma fórmula especial fornecida na documentação para armazenamento de dados físicos e, como regra, excede o de tipos de baixo nível como "inteiro de 16 bits" em 1 a 3 bytes.

Suponha que a tabela de vendas não use compactação de dados e contenha cerca de 500 milhões de linhas e o número de grupos de clientes seja cerca de 1.000. Nesse caso, podemos usar um inteiro curto (shortint, smallint) ocupando 2 bytes como um tipo de identificador id_customer_group.

Assumiremos que nosso DBMS suporta um tipo inteiro de dois bytes (por exemplo, PostgreSQL, SQL Server, Sybase e outros). Em seguida, adicionar a coluna correspondente id_customer_groupà tabela de vendas aumentará seu tamanho em pelo menos 500 000 000 * 2 = 1 000 000 000 byte ~ 1 GByte.

8.3 Quando é necessária a desnormalização?

Vejamos algumas situações comuns em que a desnormalização pode ser útil.

Grande número de junções de tabelas

Em consultas a um banco de dados totalmente normalizado, muitas vezes você precisa juntar até uma dúzia ou até mais tabelas. E cada conexão é uma operação com uso intensivo de recursos. Como resultado, essas solicitações consomem recursos do servidor e são executadas lentamente.

Em tal situação, pode ajudar:

  • desnormalização reduzindo o número de tabelas. É melhor combinar em uma várias tabelas de tamanho pequeno, contendo informações raramente alteradas (como costumam dizer, condicionalmente constantes ou de referência) e informações intimamente relacionadas em significado.
  • Em geral, se você precisar unir mais de cinco ou seis tabelas em um grande número de consultas, considere desnormalizar o banco de dados.
  • Desnormalização adicionando um campo adicional a uma das tabelas. Nesse caso, aparece redundância de dados, ações adicionais são necessárias para manter a integridade do banco de dados.

Valores estimados

Muitas vezes, as consultas são lentas e consomem muitos recursos, nas quais são realizados alguns cálculos complexos, principalmente quando se utiliza agrupamentos e funções de agregação (Sum, Max, etc.). Às vezes, faz sentido adicionar 1-2 colunas adicionais à tabela que contém dados calculados frequentemente usados ​​(e difíceis de calcular).

Suponha que você queira determinar o custo total de cada pedido. Para fazer isso, você deve primeiro determinar o custo de cada produto (de acordo com a fórmula "número de unidades do produto" * "preço unitário do produto" - desconto). Depois disso, você precisa agrupar os custos por pedidos.

A execução dessa consulta é bastante complexa e, caso o banco de dados armazene informações sobre um grande número de pedidos, pode levar muito tempo. Em vez de executar essa consulta, você pode determinar seu custo no estágio de fazer um pedido e armazená-lo em uma coluna separada da tabela de pedidos. Neste caso, para obter o resultado desejado, basta extrair desta coluna os valores pré-calculados.

Criar uma coluna que contém valores pré-calculados economiza muito tempo ao executar uma consulta, mas exige que você atualize os dados dessa coluna em tempo hábil.

borda longa

Se tivermos tabelas grandes no banco de dados que contenham campos longos (Blob, Long, etc.), podemos acelerar seriamente a execução de consultas a essa tabela se movermos os campos longos para uma tabela separada. Queremos, digamos, criar um catálogo de fotos no banco de dados, incluindo o armazenamento das próprias fotos em campos blob (qualidade profissional, alta resolução e tamanho apropriado). Do ponto de vista da normalização, a seguinte estrutura de tabela seria absolutamente correta:

  • Identificação com foto
  • ID do autor
  • ID do modelo da câmera
  • a própria foto (campo blob)

E agora vamos imaginar quanto tempo a consulta vai rodar, contando a quantidade de fotos tiradas por qualquer autor...

A solução correta (embora violando os princípios de normalização) em tal situação seria criar outra tabela composta por apenas dois campos - o ID da foto e um campo blob com a própria foto. Então as seleções da mesa principal (na qual não há mais um enorme campo blob) irão instantaneamente, mas quando quisermos ver a foto em si, bem, vamos esperar ...

Como determinar quando a desnormalização é justificada?

8.4 Prós e contras da desnormalização

Uma maneira de determinar se certas etapas são justificadas é realizar uma análise em termos de custos e possíveis benefícios. Quanto custará um modelo de dados desnormalizado?

Determinar os requisitos (o que queremos alcançar) → determinar os requisitos de dados (o que precisamos seguir) → encontrar a etapa mínima que satisfaça esses requisitos → calcular os custos de implementação → implementar.

Os custos incluem aspectos físicos, como espaço em disco, recursos necessários para gerenciar essa estrutura e oportunidades perdidas devido aos atrasos associados à manutenção desse processo. Você tem que pagar pela desnormalização. Um banco de dados desnormalizado aumenta a redundância de dados, o que pode melhorar o desempenho, mas requer mais esforço para controlar os dados relacionados. O processo de criação de aplicativos se tornará mais difícil, pois os dados serão repetidos e mais difíceis de rastrear. Além disso, a implementação da integridade referencial não é fácil - os dados relacionados são divididos em tabelas diferentes.

Os benefícios incluem desempenho de consulta mais rápido e a capacidade de obter uma resposta mais rápida. Você também pode colher outros benefícios, incluindo maior rendimento, satisfação do cliente e produtividade, bem como uso mais eficiente de ferramentas de desenvolvedor externo.

Taxa de solicitação e consistência de desempenho

Por exemplo, 72% das 1.000 consultas geradas diariamente por uma empresa são consultas em nível de resumo, não consultas detalhadas. Ao usar uma tabela de resumo, as consultas são executadas em aproximadamente 6 segundos em vez de 4 minutos, resultando em 3.000 minutos a menos no tempo de processamento. Mesmo ajustando os 100 minutos que devem ser gastos na manutenção das tabelas dinâmicas a cada semana, isso economiza 2.500 minutos por semana, o que justifica a criação da tabela dinâmica. Com o tempo, pode acontecer que a maioria das consultas não sejam endereçadas a dados resumidos, mas a dados detalhados. Quanto menos consultas usarem a tabela de resumo, mais fácil será eliminá-la sem afetar outros processos.

E…

Os critérios listados acima não são os únicos a serem considerados ao decidir se deve dar o próximo passo na otimização. Outros fatores precisam ser considerados, incluindo prioridades de negócios e necessidades do usuário final. Os usuários devem entender como, do ponto de vista técnico, a arquitetura do sistema é afetada pela exigência dos usuários que desejam que todas as solicitações sejam concluídas em poucos segundos. A maneira mais fácil de obter esse entendimento é delinear os custos associados à criação e ao gerenciamento dessas tabelas.

8.5 Como implementar a desnormalização com competência.

Salvar tabelas detalhadas

Para não limitar as capacidades da base de dados importantes para o negócio, é necessário adotar uma estratégia de coexistência, não de substituição, ou seja, manter tabelas detalhadas para análise profunda, agregando-lhes estruturas desnormalizadas. Por exemplo, o contador de visitas. Para negócios, você precisa saber o número de visitas a uma página da web. Mas para a análise (por período, por país...) muito provavelmente precisaremos de dados detalhados - uma tabela com informações sobre cada visita.

Usando gatilhos

É possível desnormalizar uma estrutura de banco de dados e ainda aproveitar os benefícios da normalização usando gatilhos de banco de dados para preservar a integrityintegridade dos dados duplicados.

Por exemplo, ao adicionar um campo calculado, cada uma das colunas das quais o campo calculado depende é desligada com um gatilho que chama um único procedimento armazenado (isso é importante!), Que grava os dados necessários no campo calculado. Só é necessário não pular nenhuma das colunas das quais o campo calculado depende.

Suporte de software

Se você não usar gatilhos integrados e procedimentos armazenados, os desenvolvedores de aplicativos devem garantir a consistência dos dados em um banco de dados desnormalizado.

Por analogia com os gatilhos, deve haver uma função que atualize todos os campos que dependem do campo que está sendo alterado.

conclusões

Ao desnormalizar, é importante manter um equilíbrio entre aumentar a velocidade do banco de dados e aumentar o risco de dados inconsistentes, entre facilitar a vida dos programadores que escrevem Select-se complicar a tarefa de quem fornece população de banco de dados e atualizações de dados. Portanto, é necessário desnormalizar o banco de dados com muito cuidado, muito seletivamente, apenas onde for indispensável.

Se for impossível calcular antecipadamente os prós e contras da desnormalização, inicialmente é necessário implementar um modelo com tabelas normalizadas e só então, para otimizar as consultas problemáticas, realizar a desnormalização.

É importante introduzir a desnormalização gradualmente e apenas para os casos em que há buscas repetidas de dados relacionados de tabelas diferentes. Lembre-se, ao duplicar dados, o número de registros aumentará, mas o número de leituras diminuirá. Também é conveniente armazenar dados calculados em colunas para evitar seleções agregadas desnecessárias.