Performance das Consultas que Utilizam INSERT

O tempo para inserir um registro consiste aproximadamente de:

• Conexão: (3)
• Enviar a consulta para o servidor: (2)
• Analisar a consulta (2)
• Inserir o registro: (1 x tamanho do registro)
• Inserir os índices: (1 x número de índices)
• Fechar: (1)

onde os números são de certa forma proporcionais ao tempo total. Isto não leva em consideracão o sobrecarga inicial para abrir tabelas (que é feita uma vez para cada consulta concorrente em execução).

O tamanho da tabela diminuem a velocidade da inserção de índices em N log N (Arvores B).

Algumas maneiras de acelerar as inserções:

• Se você estiver inserindo vários registros do mesmo cliente ao mesmo tempo, utilize instruções INSERT com listas de múltiplos valores. Isto é muito mais rápido (muitas vezes em alguns casos) do que utilizar instruções INSERT separadas. Se você esta adicionando dados a uma tabela que não está vazia, você pode ajustar a variável bulk_insert_buffer_size para tornár isto mais rápido. Leia "SHOW VARIABLES".
• Se você inserir vários registros de diferentes clientes, você pode obter velocidades mais altas utilizando a instrução INSERT DELAYED. Leia "Sintaxe INSERT".
• Perceba que com MyISAM você pode inserir registros ao mesmo tempo que SELECTs estejam executando se não existirem registros apagados nas tabelas.
• Ao carregar uma tabela de um arquivo texto, utilize LOAD DATA INFILE. Isto é normalmente 20 vezes mais rápido do que utilizar várias instruções INSERT Leia "Sintaxe LOAD DATA INFILE".
• É possível com algum trabalho extra fazer o LOAD DATA INFILE executar ainda mais rápido quando a tabela tiver vários índices. Utilize o seguinte procedimento:
  1. Opcionalmente crie a tabela com CREATE TABLE. Por exemplo, utilizando MariaDB ou Perl-DBI.
  2. Execute a instrução FLUSH TABLES ou o comando shell mysqladmin flush-tables.
  3. Utilize myisamchk --keys-used=0 -rq /path/to/db/nome_tabela. Isto removerá o uso de todos os índices da tabela.
  4. Insira dados na tabela com LOAD DATA INFILE. Isto não atualizará índices e será muito mais rápido.
  5. Se no futuro você precisar da tabela somente para leitura, execute myisampack na mesma para torná-la menor. Leia "Características de Tabelas Compactadas".
  6. Recrie os índices com myisamchk -r -q /caminho/para/bd/nome_tabela. Isto criará a árvore de índices em memória antes de escrevê-la para o disco, que é muito mais rápido porque evita que seja feita muita busca disco. A árvore de índices resultante é também balanceada perfeitamente.
  7. Execute uma instrução FLUSH TABLES ou o comando shell mysqladmin flush-tables.

  Note que LOAD DATA INFILE també faz a otimização acima se você a inserção for em uma tabela vazia; a principal diferença com o procedimento acima é qeu você pode deixar o myisamchk alocar muita mais memória temporária para a criação do índice que você deseje que o MariaDB alocasse para todas as recriações de índice.

  Desde o MariaDB você também pode usar ALTER TABLE nome_tbl DISABLE KEYS em vez de myisamchk --keys-used=0 -rq /caminho/para/bd/nome_tbl e ALTER TABLE nome_tbl ENABLE KEYS em vez de myisamchk -r -q /caminho/para/bd/nome_tbl. Deste modo você também pode saltar os passos FLUSH TABLES.

• Você pode acelerar inserções feitas usando várias instruções bloqueando suas tabelas:

  mysql> LOCK TABLES a WRITE;
  mysql> INSERT INTO a VALUES (1,23),(2,34),(4,33);
  mysql> INSERT INTO a VALUES (8,26),(6,29);
  mysql> UNLOCK TABLES;
  

  A principal diferença na velocidade é que o buffer de índices é descarregado no disco somente uma vez, depois de todas instruções INSERT term sido completadas. Normalmente existiria tantas descargas do buffer de índices quanto instruções INSERT diferentes. O bloqueio não é necessário se você pode inserir todos registros com uma simples instrução.

  Para tabelas transacionais, você deve usar BEGIN/COMMIT em vez de LOCK TABLES para conseguir um aumento na velocidade.

  O bloqueio irá também diminuir o tempo total de testes de multi-conexões, mas o tempo máximo de espera para algumas threads irá aumentar (porque eles esperam pelos bloqueios). Por exemplo:

  thread 1 faz 1000 inserções thread 2, 3 e 4 faz 1 inserção thread 5 faz 1000 inserções
  

  Se você não estiver usando travas, 2, 3 e 4 irão terminar antes de 1 e 5, Se estiver utilizando travas, 2, 3 e 4 provavelmente não irão terminar antes de 1 ou 5, mas o tempo total deve ser cerca de 40% mais rápido.

  Como as operações INSERT, UPDATE e DELETE são muito rápidas no MySQL, você obterá melhor perfomance geral adicionando travas em tudo que fizer mais que cerca de 5 inserções ou atualizações em um registro. Se você fizer várias inserções em um registro, você pode utilizar LOCK TABLES seguido de um UNLOCK TABLES de vez em quando (em torno de 1000 registro) para permitr que outras threads acessem a tabela. Isto também continua mostrando um bom ganho de performance.

  Com certeza, LOAD DATA INFILE é muito mais rápido para carregar dados.

Para obter mais velocidade para LOAD DATA INFILE e INSERT, aumente o tamanho do buffer de chaves. Leia "Parâmetros de Sintonia do Servidor".

Retornar