AllYouNeedIsBackend

Sou um grande fã de bases de dados. Eu até queria fazer meu próprio SGBD quando eu estava na universidade. Agora trabalho tanto com soluções RDBMS como NoSQL, e estou muito entusiasmado com isso. Sabe, não há nenhum Martelo de Ouro, cada problema tem a sua própria solução. Alternativamente, um subconjunto de soluções.

Na série de posts do blog The SQL I Love <3 eu te acompanho através de alguns problemas resolvidos com SQL que eu achei particularmente interessante. As soluções são testadas usando uma tabela com mais de 100 milhões de registros. Todos os exemplos usam MySQL, mas as idéias se aplicam a outras lojas de dados relacionais como PostgreSQL, Oracle e SQL Server.

>

Este capítulo é focado na digitalização eficiente de uma tabela grande usando paginação com offset na chave primária. Isto também é conhecido como paginação de conjunto de chaves.

Background

No capítulo, nós usamos a seguinte estrutura de banco de dados, por exemplo. O exemplo canônico sobre usuários deve caber em qualquer domínio.

CREATE TABLE `users` ( `user_id` int(11) unsigned NOT NULL AUTO_INCREMENT, `external_id` varchar(32) NOT NULL, `name` varchar(100) COLLATE utf8_unicode_ci NOT NULL, `metadata` text COLLATE utf8_unicode_ci, `date_created` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (`user_id`), UNIQUE KEY `uf_uniq_external_id` (`external_id`), UNIQUE KEY `uf_uniq_name` (`name`), KEY `date_created` (`date_created`)) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

Um pequeno comentário sobre a estrutura:

• external_id coluna armazena referência ao mesmo usuário em outro sistema no formato UUID
• name representa Firstname Lastname
• metadata coluna contém JSON blob com todos os tipos de dados não estruturados

A tabela é relativamente grande e contém cerca de 100 000 000 de registros. Vamos começar nossa jornada de aprendizagem.

Varredura de uma tabela grande

Problema: Você precisa caminhar através da tabela, extrair cada registro, transformá-lo dentro do código da sua aplicação e inseri-lo em outro lugar. Focamos na primeira etapa do post – escanear a tabela.

>

Solução anterior e errada

>

SELECT user_id, external_id, name, metadata, date_createdFROM users;

>

No meu caso com 100 000 000 de registros, a consulta nunca é terminada. O SGBD apenas a mata. Porquê? Provavelmente, porque levou à tentativa de carregar a tabela inteira na RAM. Antes de retornar os dados para o cliente. Outra suposição – demorou muito tempo para carregar previamente os dados antes de enviar e a consulta foi cronometrada. De qualquer forma, a nossa tentativa de obter todos os registos a tempo falhou. Precisamos encontrar outra solução.

Solução #2

Podemos tentar obter os dados em páginas. Como os registros não são garantidos de serem ordenados em uma tabela em nível físico ou lógico – precisamos ordená-los no lado do SGBD com ORDER BY cláusula.

SELECT user_id, external_id, name, metadata, date_createdFROM usersORDER BY user_id ASCLIMIT 0, 10 000;10 000 rows in set (0.03 sec)

Sweet. Funcionou. Pedimos a primeira página de 10 000 registros, e levou apenas 0.03 seg. para devolvê-lo. No entanto, como funcionaria para a 5000ª página?

SELECT user_id, external_id, name, metadata, date_createdFROM usersORDER BY user_id ASCLIMIT 50 000 000, 10 000; --- 5 000th page * 10 000 page size10 000 rows in set (40.81 sec)

Indeed, isto é muito lento. Vamos ver quanto tempo é necessário para obter os dados para a última página.

SELECT user_id, external_id, name, metadata, date_createdFROM usersORDER BY user_id ASCLIMIT 99 990 000, 10 000; --- 9999th page * 10 000 page size10 000 rows in set (1 min 20.61 sec)

Isto é uma loucura. No entanto, pode ser OK para soluções que funcionam em segundo plano. Mais um problema oculto com a abordagem pode ser revelado se você tentar apagar um registro da tabela no meio da digitalização. Digamos que terminou a 10ª página (100 000 registos já são visitados), indo digitalizar os registos entre 100 001 e 110 000. Mas os registos 99 998 e 99 999 são apagados antes da próxima SELECT execução. Nesse caso, a seguinte consulta retorna o resultado inesperado:

 SELECT user_id, external_id, name, metadata, date_created FROM users ORDER BY user_id ASC LIMIT 100 000, 10 000; N, id, ... 1, 100 003, ... 2, 100 004, ...

Como você pode ver, a consulta saltou os registros com ids 100 001 e 100 002. Eles não serão processados pelo código da aplicação com a abordagem, pois após as duas operações de exclusão, eles aparecem nos primeiros 100 000 registros. Portanto, o método não é confiável se o conjunto de dados for mutável.

Solução #3 – a última para hoje

A abordagem é muito similar à anterior porque ainda usa paginação, mas agora ao invés de confiar no número de registros escaneados, usamos o user_id do último registro visitado como o offset.

Algoritmo simplificado:

1. Recebemos PAGE_SIZE número de registos da tabela. O valor inicial do offset é 0.
2. Utilizar o valor máximo retornado para user_id no lote como o offset para a próxima página.
3. Recuperar o próximo lote dos registros que têm user_id valor maior que o atual offset.

A consulta em ação para a 5 000ª página, cada página contém dados sobre 10 000 usuários:

SELECT user_id, external_id, name, metadata, date_createdFROM usersWHERE user_id > 51 234 123 --- value of user_id for 50 000 000th recordORDER BY user_id ASCLIMIT 10 000;10 000 rows in set (0.03 sec)

Nota, que os valores de user_id não são sequenciais e podem ter intervalos como 25 348 é logo após 25 345. A solução também funciona se algum registro de páginas futuras for excluído – mesmo nesse caso, a consulta não salta registros. Sweet, right?

Explicar o desempenho

Para mais aprendizagem, recomendo investigar os resultados de EXPLAIN EXTENDED para cada versão da consulta para obter os próximos 10 000 registos após 50 000 000.

Focalizemos a diferença chave entre planos de execução de 2ª e 3ª soluções, uma vez que a 1ª não é praticamente útil para tabelas grandes.

• Tipo de união: index vs range. A primeira significa que toda a árvore de índice é digitalizada para encontrar os registros. range tipo diz-nos que o índice é usado apenas para encontrar linhas de correspondência dentro de um intervalo especificado. Então, range digite é mais rápido que index.
• Possíveis chaves: NULL vs PRIMARY. A coluna mostra as chaves que podem ser utilizadas pelo MySQL. BTW, olhando na coluna de chaves, podemos ver que eventualmente PRIMARY chave é utilizada para as duas consultas.
• Linhas: 50 010 000 vs 50 000 000. O valor exibe um número de registros analisados antes de retornar o resultado. Para a segunda consulta, o valor depende de quão profundo é o nosso scroll. Por exemplo, se tentarmos obter os próximos 10 000 registros após a 9999ª página, então 99 990 000 registros são examinados. Ao contrário, a 3ª consulta tem um valor constante; não importa se carregamos dados para a 1ª página da última. É sempre metade do tamanho da tabela.
• Filtrado: 200.00 vs 100.00. A coluna indica a porcentagem estimada da tabela a ser filtrada antes do processamento. Ter o valor mais alto é melhor. O valor de 100.00 significa que a consulta se parece através de toda a tabela. Para a 2ª consulta, o valor não é constante e depende do número da página: se perguntarmos a 1ª página o valor da coluna filtrada seria 1000000.00. Para a última página, seria 100.00.
• Extra: NULL vs Using where. Fornece informação adicional sobre como o MySQL resolve a consulta. O uso da tecla WHERE em PRIMARY torna a execução da consulta mais rápida.

Suspeito que o tipo join é o parâmetro da consulta que fez a maior contribuição à performance para tornar a 3ª consulta mais rápida. Outra coisa importante é que a 2ª consulta é extremamente dependente do número de páginas a rolar. Paginação mais profunda é mais lenta nesse caso.

Mais orientações sobre subestimação de saída para o comando EXPLAIN podem ser encontradas na documentação oficial do seu RDBMS.

Sumário

O tópico principal para o post do blog foi relacionado à digitalização de uma tabela grande com 100 000 000 de registros usando offset com uma chave primária (paginação do conjunto de teclas). No total, 3 abordagens diferentes foram revistas e testadas no conjunto de dados correspondente. Eu recomendo apenas uma delas se você precisar escanear uma tabela grande mutável.

Também, nós revisamos o uso do comando EXPLAIN EXTENDED para analisar o plano de execução das consultas do MySQL. Estou certo que outros RDBMS têm análogos para a funcionalidade.

No próximo capítulo, vamos prestar atenção à agregação de dados e otimização de armazenamento. Fique atento!

Qual é o seu método de scan de tabelas grandes?

Lembra-se de qualquer outro propósito de usar paginação de conjuntos de chaves como na Solução #3?