Início / dba / Perguntas / 16809
Accepted
Alireza
Asked: 2012-04-21 02:35:01 +0800 CST

Por que 'LOAD DATA INFILE' é mais rápido que as instruções INSERT normais?

  • 772

Eu li um artigo que mencionou que podemos alcançar 60.000 inserções por segundo usando a LOAD DATA IN FILEinstrução, que lê arquivos csv e insere os dados em um banco de dados.

Por que deve ser diferente das inserções normais?

EDIT:
reduzi a viagem de ida e volta chamando apenas uma INSERTinstrução: 

INSERT INTO tblname
VALUES (NULL,2,'some text here0'),(NULL,2,'some text here1')
    ,(NULL,2,'some text here2'),(NULL,2,'some text here3')
    .....,(NULL,2,'some text here3000');

O que dizer disso?

mysql import

3 respostas

  1. Best Answer

    LOAD DATA INFILE e INSERTs estendidos têm suas vantagens distintas.

    LOAD DATA INFILE foi projetado para carregamento em massa de dados de tabela em uma única operação, juntamente com sinos e assobios para executar ações como:

    • Ignorando as linhas iniciais
    • Ignorando colunas específicas
    • Transformando Colunas Específicas
    • Carregando colunas específicas
    • Como lidar com problemas de chave duplicada

    Menos sobrecarga é necessária para analisar

    Por outro lado, se você estiver importando apenas 100 linhas em vez de 1.000.000 linhas, o INSERT estendido é sensato.

    Observe que o mysqldump foi projetado em torno de INSERTs estendidos para carregar o design da tabela junto com os dados, pois ele executa a injeção de centenas ou milhares de linhas por INSERT. LOAD DATA INFILE sempre cria uma dicotomia física entre esquema e dados.

    Do ponto de vista do aplicativo, LOAD DATA INFILE também é mais insensível à mudança de esquema do que INSERTs estendidos.

    Pode-se ir e voltar no bom, no mau e no feio de usar LOAD DATA INFILE. Não importa qual técnica você use, você deve sempre definir o bulk_insert_buffer_size . Por quê?

    De acordo com a documentação do MySQL em bulk_insert_buffer_size:

    MyISAM usa um cache especial em forma de árvore para fazer inserções em massa mais rápidas para INSERT ... SELECT, INSERT ... VALUES (...), (...), ... e LOAD DATA INFILE ao adicionar dados a não vazios mesas. Essa variável limita o tamanho da árvore de cache em bytes por encadeamento. Defini-lo como 0 desabilita essa otimização. O valor padrão é 8 MB.

    Durante anos, vi cliente após cliente não definir isso e deixá-lo em 8 MB. Então, quando eles decidem usar LOAD DATA INFILE ou importar mysqldumps, eles podem sentir algo errado. Eu geralmente recomendo configurar isso para 256M moderados. Em alguns casos, 512M.

    Uma vez que você tenha um buffer INSERT grande o suficiente, o uso de qualquer uma das técnicas se torna acadêmico e se resume a uma escolha pessoal. Para aplicativos em que você insere apenas 100 linhas sob demanda, use INSERTs estendidos.

    Com toda a justiça, dizer que LOAD DATA INFILE é mais rápido que instruções INSERT normais é meio que uma instrução carregada principalmente porque a configuração não é levada em consideração. Mesmo se você configurar um benchmark entre LOAD DATA INFILE e INSERTs estendidos com um bulk_insert_buffer_size adequado, os nanossegundos salvos na análise de cada linha podem produzir apenas resultados nominais na melhor das hipóteses em favor de LOAD DATA INFILE.

    Vá em frente e adicione isso ao my.cnf

    [mysqld]
bulk_inset_buffer_size=256M

    Você também pode configurá-lo apenas para sua sessão antes de iniciar INSERTs estendidos

    SET bulk_insert_buffer_size= 1024 * 1024 * 256;

    ATUALIZAÇÃO 2012-07-19 14:58 EDT

    Para manter as coisas em perspectiva, o buffer de inserção em massa só é útil para carregar tabelas MyISAM, não InnoDB. Eu escrevi um post mais recente sobre carregamento em massa InnoDB : Carga do Mysql do arquivo preso esperando no disco rígido

    • 28

  2. A maioria dos sistemas de gerenciamento de banco de dados possui um recurso de carregamento em massa para carregar grandes volumes de dados rapidamente. Uma INSERTinstrução tem uma quantidade significativa de bagagem por instrução - bloqueio, demarcação de transação, verificações de integridade referencial, alocação de recursos, E/S que deve ser feita por instrução.

    As operações de inserção em massa simplificam o processo para que esse material tenha muito, muito menos sobrecarga por linha. Um DBMS pode carregar ordens de dados em massa mais rapidamente do que por meio de instruções de inserção.

    • 4

  3. Analisar e executar INSERTinstruções individuais carrega uma sobrecarga muito maior do que dividir um arquivo CSV em colunas e carregá-las diretamente.

    Cada INSERTinstrução deve ser analisada individualmente pelo mecanismo MySQL e verificada quanto à validade - isso consome recursos extras da CPU e também requer mais viagens de ida e volta cliente<>servidor. Isso não precisa acontecer ao carregar em massa via LOAD DATA INFILE. Também existem otimizações que podem ocorrer ao usar LOAD DATA INFILEpara carregar em uma mesa vazia. Veja este link para mais informações.

    • 3

relate perguntas