Início / dba / Perguntas / 84825
Accepted
Mike Purcell
Asked: 2014-12-11 10:06:01 +0800 CST

SELECT INTO OUTFILE vs INSERT INTO ... SELECT

  • 772

Durante minha pesquisa superficial, não consegui encontrar uma resposta definitiva sobre os benefícios que ela SELECT INTO OUTFILEoferece INSERT INTO ... SELECT. Ao ler os documentos relacionados a INSERT INTO ... SELECTbloqueios em tabelas InnoDB, ele declara:

define um registro de índice exclusivo sem um bloqueio de lacuna em cada linha inserida em T. Se o nível de isolamento da transação for READ COMMITTED ou innodb_locks_unsafe_for_binlog estiver ativado e o nível de isolamento da transação não for SERIALIZABLE, o InnoDB faz a pesquisa em S como uma leitura consistente (sem fechaduras). Caso contrário, o InnoDB define bloqueios de próxima chave compartilhados em linhas de S.

Para evitar o bloqueio com INSERT INTO ... SELECTparece que devo garantir o nível READ COMMITTEDde isolamento é evitar bloqueios na tabela de origem durante a consulta.

No entanto, não consegui encontrar nenhuma resposta oficial sobre bloqueios e uso SELECT INTO OUTFILE, nem mesmo as informações de bloqueio de referência dos documentos do MySQL.

Meu objetivo é evitar o bloqueio da tabela de origem enquanto a consulta é executada para evitar o empilhamento de conexões.

mysql innodb

1 respostas

  1. Best Answer

    Você deve usar SELECT ... LOCK IN SHARE MODE . Por quê ?

    SELECT ... LOCK IN SHARE MODE define um bloqueio de modo compartilhado em todas as linhas que são lidas. Outras sessões podem ler as linhas, mas não podem modificá-las até que sua transação seja confirmada. Se alguma dessas linhas foi alterada por outra transação que ainda não foi confirmada, sua consulta aguardará até que a transação termine e, em seguida, usará os valores mais recentes.

    No seu caso, você pode tentar isso

    START TRANSACTION;
SELECT ... LOCK IN SHARE MODE;
SELECT ... INTO OUTFILE;
ROLLBACK;

    Isso faria duas SELECTconsultas

    • Primeiro SELECTpara bloquear as linhas na tabela que você deseja
    • segundo SELECTa executarSELECT ... INTO OUTFILE

    Pessoalmente, não acho que você precise ser tão opressor. O isolamento da transação deve ser inteligente o suficiente para retirar esse atômico SELECTe usar as mesmas linhas para o arquivo INSERT. Eu sei que disse should bee é por isso que você está fazendo a pergunta em primeiro lugar.

    Quer você faça SELECT ... INTO OUTFILEcomo um comando ou da maneira pesada que estou propondo, os dados da linha da tabela de origem serão totalmente legíveis.

    DE UMA CHANCE !!!

    ATUALIZAÇÃO 2014-12-10 15:12 EST

    Seu comentário

    Obrigado pela resposta, e ajuda, mas o ponto principal do OP foi determinar se há um benefício em usar SELECT INTO OUTFILE em vez de INSERT INTO ... SELECT?

    Eles são operacionalmente diferentes

    • SELECT INTO OUTFILEcria um arquivo de texto
    • INSERT INTO SELECTcarrega uma tabela dos resultados doSELECT

    ATUALIZAÇÃO 2014-12-11 12:21 EST

    A única coisa em que consigo pensar nesse contexto é o ponto no tempo dos dados e quando você os está usando. Com ambos os tipos de operações, haverá algum bloqueio compartilhado implícito.

    Com SELECT INTO OUTFILE, você está preparando um resultado e salvando-o externamente. Carregar esses dados em uma tabela usando LOAD DATA INFILEnão envolverá nenhum bloqueio compartilhado durante o processo de carregamento. Lembre-se de que SELECT INTO OUTFILEisso incorrerá em E/S de disco e ainda imporá algum armazenamento em cache ao longo do caminho.

    Com INSERT INTO SELECT, os bloqueios compartilhados provavelmente teriam que durar mais no InnoDB porque você está bloqueando linhas e usando essas mesmas linhas para INSERT em outra tabela.

    Portanto, se eu estivesse procurando por um bônus de desempenho, eu daria a vantagem INSERT INTO SELECTporque você está fazendo a mesma quantidade de bloqueio de linha SELECT INTO OUTFILEcompartilhada LOAD DATA INFILE. Claro, você teria que comparar os dois métodos com seu conjunto de dados. O que poderia ser um bônus de desempenho para um conjunto de dados pode ser um custo de desempenho para outro conjunto de dados.

    ATUALIZAÇÃO 2014-12-17 00:00 EST

    Seu comentário

    Não fui notificado de que você atualizou sua resposta, então fiz uma recompensa assumindo que não. Sua explicação faz sentido, em teoria, no entanto, estou procurando uma resposta mais confiável, na esperança de que a sobrecarga do arquivo separado (como você mencionou corretamente) possa valer a pena em termos de complexidade para melhorar o desempenho.

    A única resposta autorizada viria da documentação do MySQL.

    Primeiro, o que a documentação do MySQL LOAD DATA INFILE diz?

    A instrução LOAD DATA INFILE lê linhas de um arquivo de texto em uma tabela em uma velocidade muito alta. LOAD DATA INFILE é o complemento de SELECT ... INTO OUTFILE. (Consulte a Seção 13.2.9.1, “Sintaxe SELECT ... INTO”.) Para gravar dados de uma tabela em um arquivo, use SELECT ... INTO OUTFILE. Para ler o arquivo de volta em uma tabela, use LOAD DATA INFILE.

    Dois parágrafos depois , diz

    Para obter mais informações sobre a eficiência de INSERT versus LOAD DATA INFILE e sobre a aceleração de LOAD DATA INFILE, consulte a Seção 8.2.2.1, “Velocidade das instruções INSERT”.

    Quando você olha para Speed ​​of INSERT Statements , ele diz o seguinte:

    Para otimizar a velocidade da pastilha, combine várias pequenas operações em uma única grande operação. Idealmente, você faz uma única conexão, envia os dados para muitas novas linhas de uma só vez e atrasa todas as atualizações de índice e verificação de consistência até o final.

    O tempo necessário para inserir uma linha é determinado pelos seguintes fatores, onde os números indicam proporções aproximadas:

    Conexão: (3)

    Enviando consulta ao servidor: (2)

    Consulta de análise: (2)

    Inserindo linha: (1 × tamanho da linha)

    Inserindo índices: (1 × número de índices)

    Fechamento: (1)

    Isso não leva em consideração a sobrecarga inicial para abrir tabelas, o que é feito uma vez para cada consulta em execução simultânea.

    O tamanho da tabela retarda a inserção de índices por log N, assumindo índices de árvore B.

    Você pode usar os seguintes métodos para acelerar as inserções:

    Se você estiver inserindo várias linhas do mesmo cliente ao mesmo tempo, use instruções INSERT com várias listas VALUES para inserir várias linhas de uma vez. Isso é consideravelmente mais rápido (muitas vezes mais rápido em alguns casos) do que usar instruções INSERT de linha única separadas. Se estiver adicionando dados a uma tabela não vazia, você pode ajustar a variável bulk_insert_buffer_size para tornar a inserção de dados ainda mais rápida. Consulte a Seção 5.1.4, “Variáveis ​​do sistema do servidor”.

    Ao carregar uma tabela de um arquivo de texto, use LOAD DATA INFILE. Isso geralmente é 20 vezes mais rápido do que usar instruções INSERT. Consulte a Seção 13.2.6, “Sintaxe LOAD DATA INFILE”.

    Aproveite o fato de que as colunas têm valores padrão. Insira valores explicitamente apenas quando o valor a ser inserido for diferente do padrão. Isso reduz a análise que o MySQL deve fazer e melhora a velocidade de inserção.

    Consulte a Seção 8.5.4, “Carregamento de dados em massa para tabelas InnoDB” para obter dicas específicas para tabelas InnoDB.

    Consulte a Seção 8.6.2, “Carregamento de dados em massa para tabelas MyISAM” para obter dicas específicas para tabelas MyISAM.

    As coisas começam a parecer um pouco nebulosas neste ponto porque você deve ajustar o processo de carregamento em termos do mecanismo de armazenamento. MyISAM é bastante direto nesta declaração porque o buffer de inserção em massa é apenas para MyISAM e LOAD DATA INFILE aproveitará o buffer de inserção em massa . InnoDB não .

    Dê uma olhada nesta representação pictórica do InnoDB (Percona CTO Vadim Tchachenko)

    Arquitetura InnoDB

    Existem outras considerações para ajustar as opções , mas LOAD DATA INFILEliteralmente colocar tudo no InnoDB Buffer Pool, canalizando as alterações por meio do Log Buffer, Double Write Buffer, Insert Buffer (se a tabela de destino tiver índices não exclusivos), Redo Logs (ib_logfile0,ib_logfile1) e o Arquivo Físico da Tabela. É aqui que os benefícios de LOAD DATA INFILE devem ser anulados.

    eu escrevi sobre isso

    EPÍLOGO

    Como eu já disse na minha atualização anterior para esta resposta

    Portanto, se eu estivesse procurando por um bônus de desempenho, eu daria a vantagem INSERT INTO SELECTporque você está fazendo a mesma quantidade de bloqueio de linha SELECT INTO OUTFILEcompartilhada LOAD DATA INFILE. Claro, você teria que comparar os dois métodos com seu conjunto de dados. O que poderia ser um bônus de desempenho para um conjunto de dados pode ser um custo de desempenho para outro conjunto de dados.

    Basicamente, você terá que testar SELECT INTO OUTFILE/LOAD DATA INFILEcontra INSERT INTO SELECT. Pode ser 6 de um, meia dúzia do outro para um conjunto de dados e uma vitória terrestre para outro conjunto de dados.

    Tudo dito a partir do MySQL Docs e de minhas postagens anteriores, ainda dou vantagem ao INSERT INTO SELECT. Você apenas terá que testar os dois métodos.

    • 5

relate perguntas