Importando dados rapidamente no MySQL

Post originalmente publicado em inglês.

Há um tempo atrás, meu amigo Frank de Jonge me contou que conseguiu aumentar a performance em uma importação de 10h para 16 minutos. De acordo com ele, as alterações envolviam campos que eram muito longos que armazenavam pequenas strings, o número de índices e de chaves estrangeiras nas tabelas. Estamos falando de pelo menos 1 milhão de registros aqui, e ele estava se perguntando se era possível ter uma importação ainda mais rápida.

O básico

Existem várias maneiras de se importar informações para um banco de dados. Tudo depende da origem e o destino. Um pouco mais de contexto para melhor entendimento: você poderia retirar dados de uma aplicação legada que exporta dados em CSV para seu servidor de banco de dados ou até mesmo dados de diferentes servidores.

Se você está exportando informações de uma tabela MySQL para uma outra tabela MySQL (levando em consideração que elas estão em diferentes servidores) provavelmente usará o comando mysqldump.

Para exportar uma única tabela:

$ mysqldump -h localhost -u root -p --extended-insert --quick --no-create-info mydb mytable | gzip > mytable.sql.gz

Destrinchando essa linha de comando temos:

  • --extended-insert: garante que o comando INSERT tenha mais de um registro por linha.
  • --quick: recomendado para o dump de tabelas grandes, pois por padrão o MySQL lê toda a tabela em memória e então escreve o dump em disco. Desta maneira temos um streaming contínuo dos dados sem consumir muitos recursos de memória.
  • --no-create-info: isso significa que apenas os dados estão sendo exportados, as cláusulas de CREATE TABLE são excluídas.

O Complexo

Meu amigo tinha um problema um pouco mais complexo. Ele precisava gerar um arquivo de dump em que os arquivos tinham uma origem em transformação e parsing de objetos (em seguida eu o aconselhei a usar a LOAD FILE no futuro), porém como 90% do trabalho dele já estava pronto, ele tinha apenas uma dúvida:

Por que quando eu faço a exportação em blocos de 50 registros para serem inseridos é mais rápido do que quando eu faço com 500?

Podem existir N razões para isso:

  • fazer buffer de 500 registros na memória é mais lento que 50, lembre, você está lendo do disco, e isso sempre será mais lento.
  • se transações não estão sendo usadas, os índices são recalculados/recriados no final de cada INSERT. Para 1 milhão de registros com lotes de 50 teríamos 20 mil INSERT sendo executados, enquanto que lotes de 500 registros teríamos cerca de 2 mil execuções. Minha especulação aqui é que como os índices em uma tabela com engine InnoDB são BTREE, criar os registros em pequenos lotes significa que você “sabe” onde os valores estão na árvore, fazendo com que seja mais rápido para buscar, ordenar e organizar que com 500 itens de vez. Novamente, isso é apenas uma especulação.

Sugestões

Transações

Minha primeira sugestão foi para envolver tudo em uma transação única. Coloque um START TRANSACTION no começo e no final adicione um COMMIT. Desta maneira o processo de recriação dos índices e a validação das chaves estrangeiras ocorreria apenas na conclusão do script.

Ele reportou uma pequena melhora na performance.

O Perigo

Eu sabia de uma solução desde o começo que faria a vida dele um pouco mais fácil, porém, como a fonte de dados dele não é tão íntegra como o banco de dados é, isso poderia resultar em dados duplicados, chaves estrangeiras faltando, ou seja, poderia terminar de uma maneira muito trágica.

Quando você usa o mysqldump, o MySQL por padrão coloca essa opção no início e no fim do script. Ele assume (e de uma maneira até justa) que você estará importando esses dados para um banco de dados vazio, desse modo não tendo problemas com integridade de dados. O que não era o caso.

Os registros foram manipulados para serem inseridos, e o comando que vem por padrão ativado em scripts e que eu recomendei para ele foi:

SET foreign_key_checks = 0;
/* coloque aqui seu script, com MUITO CUIDADO */
SET foreign_key_checks = 1;

A importação caiu de 16 min para 6 min. Ele ficou muito contente com o resultado 😀:

Algumas pessoas perguntaram a ele como foi que ele/eu conseguimos, e esse post nasceu. Confesso que foi divertido ver o tempo cortado na metade, mas use esses comandos com muito cuidado.

Uma maneira ainda mais rápida

Arquivos CSV. Sim, é mais rápido. Especificamente falando de TSV, já que ter vírgula em uma string é uma ocorrência comum.

Para gerar um dump com CSV:

$ mysqldump -h localhost -u root -p --tab=/tmp mydb mytable

Se você está fazendo algum tipo de manipulação com os dados ou usando outra fonte que não um servidor de banco de dados, não esqueça de usar \N para valores NULL.

Para importar:

$ mysql -h localhost -u root -p mydb
mysql> LOAD DATA INFILE '/tmp/mytable.txt' INTO TABLE mytable;
Query OK, 881426 rows affected (29.30 sec)
Records: 881426 Deleted: 0 Skipped: 0 Warnings: 0

Os mesmos dados com INSERT em bloco levou por volta de um minuto. Existem variáveis a serem consideradas quando lidamos com esse comando como por exemplo tamanho de buffer, o check das chaves, entre outros. Portanto, para alto volume de informações e dados, importar direto de um arquivo texto ainda é a melhor opção.

Conclusão

Como eu demonstrei, fazer uma importação rápida pode consistir em apenas desabilitar a validação de chaves e constraints (condições?). Apenas faça isso se você tem certeza que os dados são válidos, em contrapartida, outras opções como net_buffer_length, max_allowed_packet and read_buffer_size podem ajudar na importação de arquivos SQL grandes. Algo que deve ser ponderado em uma situação dessas: Integridade de dados > Performance.

Deixe um comentário

Preencha os seus dados abaixo ou clique em um ícone para log in:

Logotipo do WordPress.com

Você está comentando utilizando sua conta WordPress.com. Sair / Alterar )

Imagem do Twitter

Você está comentando utilizando sua conta Twitter. Sair / Alterar )

Foto do Facebook

Você está comentando utilizando sua conta Facebook. Sair / Alterar )

Foto do Google+

Você está comentando utilizando sua conta Google+. Sair / Alterar )

Conectando a %s