Este artigo apresenta as estratégias do MySQL para melhorar a eficiência das operações de carregamento de dados. Muitas vezes você está preocupado em otimizar consultas SELECT porque elas são as consultas mais usadas e determinar como otimizá-las nem sempre é simples. Carregar dados em um banco de dados é relativamente simples. Muitas vezes você está preocupado em otimizar consultas SELECT porque elas são as consultas mais usadas e determinar como otimizá-las nem sempre é simples. Carregar dados em um banco de dados é relativamente simples. No entanto, existem estratégias que podem ser utilizadas para melhorar a eficiência das operações de carregamento de dados, cujos princípios básicos são os seguintes:
O carregamento em massa é mais rápido do que o carregamento de linha única porque o cache do índice não precisa ser liberado após o carregamento de cada registro; ele pode ser liberado após o carregamento do lote de registros;
Carregar uma tabela sem índice é mais rápido do que carregar após um índice. Se houver índices, não apenas os registros deverão ser adicionados aos arquivos de dados, mas cada índice deverá ser modificado para refletir a adição de novos registros.
Instruções SQL mais curtas são mais rápidas do que instruções SQL mais longas porque envolvem menos análise no lado do servidor e porque são mais rápidas para serem enviadas pela rede do cliente para o servidor. Alguns desses fatores podem parecer triviais (especialmente o último), mas se você estiver carregando uma grande quantidade de dados, mesmo pequenos fatores poderão fazer uma grande diferença nos resultados. Podemos usar os princípios gerais acima para tirar várias conclusões práticas sobre como carregar dados mais rapidamente:
LOAD DATA (em todas as suas formas) é mais eficiente que INSERT porque carrega linhas em lotes. As atualizações de índice são menores e o servidor só precisa analisar e interpretar uma instrução em vez de várias.
LOAD DATA é mais eficiente que LOAD DATA LOCAL. Com LOAD DATA, o arquivo deve estar localizado no servidor e ter permissões FILE, mas o servidor pode ler o arquivo diretamente do disco. Com LOAD DATA LOCAL, o cliente lê o arquivo e o envia pela rede para o servidor, que é lento.
Se você precisar usar INSERT, deverá usar um formulário que permita que várias linhas sejam especificadas em uma única instrução, como:
Quanto mais linhas você especificar em uma instrução, melhor. Isso reduz o número de instruções necessárias e a quantidade de atualizações de índice. Se você usar mysqldump para gerar um arquivo de backup de banco de dados, você deve usar a opção --extended-insert para que o arquivo de dump contenha instruções INSERT multilinhas. Você também pode usar --opt (otimização), que habilita a opção --extended-insert. Por outro lado, o uso da opção --complete-insert para mysqldump deve ser evitado; esta opção faz com que as instruções INSERT sejam de linha única, demorem mais para serem executadas e exijam mais análise do que as instruções geradas sem a opção --complete-insert.
Use protocolos cliente/servidor compactados para reduzir o tráfego de dados de rede. Para a maioria dos clientes MySQL, isso pode ser especificado com a opção de linha de comando --compress. Geralmente é usado apenas em redes mais lentas porque a compactação requer muito tempo do processador.
Deixe o MySQL inserir valores padrão; não especifique colunas na instrução INSERT que serão atribuídas a valores padrão de forma alguma. Em média, isso resulta em instruções mais curtas e reduz o número de caracteres enviados pela rede para o servidor. Além disso, instruções contendo menos valores requerem menos análise e transformação por parte do servidor.
Se a tabela for indexada, você poderá usar inserções em massa (instruções LOAD DATA ou instruções INSERT de várias linhas) para reduzir a sobrecarga do índice. Isto minimiza o impacto das atualizações do índice porque o índice só precisa ser atualizado quando todas as linhas tiverem sido processadas, e não após cada linha.
Se você precisar carregar uma grande quantidade de dados em uma nova tabela, você deve criar a tabela e carregá-la quando ela não estiver indexada e, em seguida, criar o índice após carregar os dados. Criar o índice uma vez (em vez de modificá-lo uma vez por linha) é mais rápido.
Se você descartar ou desabilitar um índice antes de carregá-lo, recriar ou habilitar o índice depois de carregar os dados poderá tornar o carregamento mais rápido. Se você quiser usar uma estratégia de exclusão ou desabilitação para carregamento de dados, faça alguns experimentos para ver se vale a pena (se você estiver carregando uma pequena quantidade de dados em uma tabela grande, a reconstrução e a indexação podem demorar mais do que carregar os dados) ).
DROP INDEX e CREATE INDEX podem ser usados para eliminar e reconstruir índices. Uma alternativa é desabilitar e habilitar índices usando myisamchk ou isamchk. Isso requer uma conta no host do servidor MySQL com acesso de gravação aos arquivos da tabela. Para desabilitar os índices da tabela, entre no diretório do banco de dados correspondente e execute um dos seguintes comandos:
Use myisamchk para tabelas MyISAM com arquivos de índice com extensão .MYI e isamchk para tabelas ISAM com arquivos de índice com extensão .ISM. Após carregar os dados na tabela, ative o índice da seguinte forma:
Se você decidir usar a desativação e ativação do índice, você deve usar o protocolo de bloqueio de reparo de tabela descrito no Capítulo 13 para evitar que o servidor altere os bloqueios ao mesmo tempo (embora a tabela não seja reparada neste momento, ela é modificada como uma tabela processo de reparo, então você precisa usar o mesmo protocolo de bloqueio).
Os princípios de carregamento de dados descritos acima também se aplicam a consultas fixas relacionadas a clientes que precisam realizar diferentes operações. Por exemplo, geralmente você deseja evitar a execução de consultas SELECT longas em tabelas atualizadas com frequência. Consultas SELECT de longa duração podem criar muita contenção e reduzir o desempenho do gravador. Uma solução possível é primeiro armazenar os registros em uma tabela temporária e depois adicioná-los periodicamente à tabela principal se as gravações forem principalmente operações INSERT. Esta não é uma abordagem viável se for necessário acesso imediato a novos registos. Mas este método pode ser usado desde que não sejam acessados por um curto período de tempo. Existem dois benefícios em usar tabelas temporárias. Primeiro, reduz a contenção com a instrução de consulta SELECT na tabela principal e, portanto, executa mais rapidamente. Em segundo lugar, o tempo total para carregar os registros da tabela temporária na tabela principal é menor que o tempo total para carregar os registros separadamente, o cache do índice correspondente só precisa ser atualizado no final de cada carregamento em lote, e não após cada linha; carregar. Uma aplicação desta estratégia é acessar o banco de dados MySQL a partir da página Web do servidor Web. Neste cenário, pode não haver um nível de autoridade superior que garanta a entrada imediata do registro na tabela principal.
Se os dados não forem exatamente o tipo de registro único que seria inserido no caso de desligamento do sistema, outra estratégia para reduzir as atualizações de índice é usar a opção de criação de tabela DELAYED_KEY_WRITE para tabelas MyISAM (o que pode ser possível se o MySQL for usado para algum trabalho de entrada de dados). Esta opção faz com que o cache do índice seja atualizado apenas ocasionalmente, em vez de após cada inserção.
Se você quiser aproveitar a atualização atrasada do índice em todo o servidor, basta iniciar o mysqld com a opção --delayed-key-write. Nesse cenário, as gravações do bloco de índice são atrasadas até que o bloco precise ser liberado para liberar espaço para outros valores de índice, até que um comando flush-tables seja executado ou até que a tabela de índice seja fechada.
-