Esta postagem se origina em meu blog pessoal, em http://www.mullie.eu/aggregate-data-large-datasets-rollup-tables/

Uma miríade de recursos pode solicitar a necessidade de agregar seus dados, como mostrar uma pontuação média com base em vários valores ou mesmo simplesmente mostrar a quantidade de entradas que obedecem a uma determinada condição. Normalmente, esta é uma consulta trivial, mas muitas vezes não é verdade ao lidar com um grande conjunto de dados.

Qual é o problema com um grande conjunto de dados?

Em conjuntos de dados de tamanho moderado, você pode apenas construir uma consulta usando as funções agregadas do MySQL , como:

SELECT COUNT(*)
FROM products
WHERE
    category = 'accessories' AND
    color = 'green'

Obviamente, a consulta acima retornaria todas as entradas que, para as colunas categorye color, possuem os valores ‘acessórios’ e ‘verde’. Ele faria isso repetindo todas as entradas nesta tabela, comparando os valores para categorye colorcom os respectivos valores.

Em uma tabela com apenas algumas centenas de entradas, isso é fácil. Você pode imaginar que, uma vez que a quantidade de entradas em uma tabela chega a milhões, repetir todas as entradas não é uma ideia muito brilhante, pois o MySQL levará algum tempo e esforço para calcular esse resultado. Assim que a taxa de solicitações exceder a taxa que o MySQL é capaz de responder a essas solicitações, pronto.

Uma solução poderia ser adicionar índices precisos. O MySQL pode então retornar prontamente a quantidade solicitada sem ter que colocar muito esforço nisso, já que esses índices evitariam que ele tivesse que repetir todas as entradas. Se as condições se tornarem complexas, no entanto, você pode descobrir que fica cada vez menos lógico, se possível, tentar corrigir o problema aplicando um índice às colunas condicionais.

Rollup tables

As tabelas de acúmulo são tabelas onde os totais para (combinações de) condições são salvos. Uma “tabela de resumo”, que contém valores pré-agregados para todas as condições para as quais você pode precisar buscar totais.

Se sua tabela de dados tiver milhões de entradas, você pode imaginar que faz mais sentido direcionar suas consultas em uma tabela de rollup muito pequena do que em um enorme conjunto de dados primário.

Um exemplo de tabela cumulativa poderia ser assim:

<table>
<tr>
<th> categoria </th>
<th> cor </th>
<th> total </th>
</tr>
<tr>
<td> camisas </td>
<td> vermelhas < / td>
<td> 23578347 </td>
</tr>
<tr>
<td> camisas </td>
<td> verdes </td>
<td> 14364323 </td>
</tr>
<tr>
< td> camisas </td>
<td> azul </td>
<td> 46723343 </td>
</tr>
<tr>
<td> acessórios </td>
<td> vermelho </td>
<td> 3452465 </td>
</tr>
<tr>
<td> acessórios </td>
<td> verde </td>
<td> 867665 </td>
</tr>
<tr>
<td> acessórios </td>
<td> azul </td>
<td> 7609852 </td>
</tr>
<tr>
<td> calças </td>
<td> vermelha </td>
<td> 56878766 </td>
</tr>
<tr>
<td> calças </ td >
<td> verde </td>
<td> 87067876 </td>
</tr>
<tr>
<td> calças </td>
<td> azul </td>
<td> 759457363 </td>
</tr>
</table>

Supondo que você mantenha esses totais em sincronia com os dados de origem atômica products, seria muito melhor executar esta consulta:

SELECT total
FROM products_rollup
WHERE
    category = 'accessories' AND
    color = 'green'

Manter os dados de rollup sincronizados

Você sempre desejará que os totais da tabela de acúmulo reflitam com precisão os dados na tabela de dados de origem e mantê-los sincronizados é definitivamente o maior desafio. Para cada nova inserção, atualização ou exclusão na tabela primária, pode ser necessária uma atualização de várias linhas na tabela de rollup. Para conseguir isso, você precisará adicionar alguma lógica adicional ao seu aplicativo.

Recálculo total

Em sua forma mais básica, você pode recalcular todos os valores de rollup imediatamente após atualizar os dados na tabela de origem. O que você faria é consultar a tabela de origem (em nosso exemplo products) e GROUP BYas colunas de rollup. O MySQL fará um loop em todos os registros de origem e responderá com valores de rollup atualizados, que você pode gravar imediatamente em sua tabela de rollup:

REPLACE INTO products_rollup
SELECT category, color, COUNT(*)
FROM products
GROUP BY category, color

Esta é a abordagem mais fácil para manter seus dados sincronizados, onde, não importa o que você altere nos dados da tabela de origem, todos os dados de rollup serão atualizados com precisão.

A desvantagem, entretanto, é que você ainda executa essa consulta um tanto cara, que faz um loop em todas as entradas na tabela de origem. Todas as leituras terão como alvo a tabela de rollup, mas agora cada gravação resultará nesta consulta pesada. Se você estiver em um ambiente de gravação intensa, isso também pode levar a problemas.

Atualização por entrada

Permitir que o MySQL recalcule todos os valores de rollup é desnecessariamente intensivo. Se soubermos exatamente o que muda, podemos simplesmente atualizar apenas esses valores de rollup específicos.

Imagine que adicionamos um novo acessório vermelho à nossa tabela de produtos: não precisamos recalcular todos os valores, podemos apenas aumentar o valor de rollup para acessórios / red com 1. Todos os outros valores de rollup podem permanecer inalterados.

Embora seja um pouco mais trabalhoso de implementar do que o recálculo completo, isso também pode ser feito de forma genérica. Para cada atualização da tabela de origem, as mudanças relevantes na tabela de rollup podem ser deduzidas. Tudo o que precisamos fazer é realizar 1 consulta de leitura na tabela de origem antes de atualizar os dados e 1 depois. Então, sabemos os valores originais e atualizados e sabemos exatamente quais valores de rollup atualizar.

Para começar, consultaríamos a tabela de origem, as colunas dessa entrada relevantes para a tabela de rollup: categorye color:

SELECT category, color
FROM products
WHERE id = 1

Isso poderia, por exemplo, retornar:

<table>
<tr>
<th> categoria </th>
<th> cor </th>
</tr>
<tr>
<td> camisas </td>
<td> verdes </td>
</tr>
< / mesa>

Depois disso, atualizamos uma entrada, por exemplo:

UPDATE products
SET
    category = 'shirts',
    color = 'blue'
WHERE id = 1

E agora, novamente, emitimos outra consulta de leitura para essa entrada, para identificar quais valores relevantes para a tabela de rollup foram alterados:

SELECT category, color
FROM products
WHERE id = 1

Isso agora retornaria:

<table>
<tr>
<th> categoria </th>
<th> cor </th>
</tr>
<tr>
<td> camisas </td>
<td> azul </td>
</tr>
< / mesa>

Agora sabemos exatamente o que os rollups devem mudar. A entrada era originalmente camisas / verdes. Não é mais, portanto, devemos deduzir essa entrada daquele total para camisas / verdes. Como a entrada agora é camisetas / azul, devemos aumentar esse acúmulo:

UPDATE products_rollup
SET value = value - 1
WHERE category = 'shirts' AND color = 'green'

UPDATE products_rollup
SET value = value + 1
WHERE category = 'shirts' AND color = 'blue'

Você provavelmente terá notado que essa abordagem resulta em algumas consultas adicionais, embora nenhuma delas seja cara de executar. As 2 consultas de leitura podem usar com eficiência o índice (chave primária) na coluna de id da tabela de origem, e ambas as consultas de atualização são melhores do que substituir todos os valores na tabela de rollup.