Como otimizar consultas MySQL

A otimização é um aspecto importante da administração de bancos de dados. Torna-se mais importante ainda quando este banco de dados é pesadamente utilizado, isto é, há grandes quantidades de inserções/deleções ou recuperação de dados. O servidor de banco de dados MySQL fornece muitos meios de se otimizar as consultas; alguns destes recursos são discutidos neste artigo.

Se as consultas estiverem demorando muito tempo, e as conexões ao banco de dados e filas para a execução de operações estão crescendo, é hora de otimizar seu banco de dados. Em MySQL, você pode usar o comando mysqlreport para gerar relatórios sobre bancos de dados que estão atingindo o limite de sua capacidade de uso, e estabelecer um patamar para operações e usos adicionais. Talvez também você se veja obrigado a modificar arquivo de configuração (my.cnf) para registrar o log de consultas lentas. Identifique as consultas que estão processando muito devagar, e as possíveis causas. Normalmente, as causas estão relacionadas a problemas de índices, como nenhum índice ou índices inadequados. Este artigo apresenta uma descrição detalhada sobre quais são os comandos e conceitos que podemos utilizar para otimizar o MySQL.

  • Usando índices

    O uso de índices torna a consulta mais rápida, da mesma forma que o índice de um livro auxilia na procura de uma determinada palavra.

    Para saber como e quais índices são usados em uma determinada pesquisa, utilize comando EXPLAIN como um prefixo para a consulta SELECT.

    EXPLAIN SELECT emp_id ORGN_DATA WHERE dept_code FROM = "TI";

    Esse comando retorna as seguintes informações:

    Tabela - Quais tabelas são usadas na consulta
    Tipo – Tipo JOIN. Os valores podem ser system, const, eq_ref, ref, range, index, all
    Possíveis Chaves - Todas as chaves que podem ser utilizados para índices
    Chave - A chave efetivamente utilizado para o índice
    Tamanho da chave – As chaves mais curtas são melhores
    Ref - Outras colunas utilizadas com a chave para obter os resultados
    Linhas - Número de linhas de onde virão os dados
    Extra – Informação extra. Alguns possíveis valores que possam estar usando índices, utilizando onde, utilizando temporário, utilizando filesort

    Você deve reconsiderar a estrutura de sua tabela se Possíveis chaves contiver o valor NULL. Um valor NULL no campo Chave indica que nenhum índice está sendo usado. A não utilização de índices irá deteriorar o desempenho se houver muitos registros na tabela, porque para qualquer consulta todos os registros serão pesquisado para a condição dada.

    Use o comando ALTER para adicionar um índice, como mostrado abaixo:

    ALTER TABLE table_name ADD INDEX (column_name);

    Você pode fazer indexação em múltiplas colunas usando:

    ALTER TABLE table_name ADD INDEX (column_name1, .., column_nameN);

    O MySQL usa prefixos à esquerda, então automaticamente serão adicionados índices para column_name1, (column_name1, column_name2 ),...( column_name1, .., column_NameN-1). Isto é bastante útil em situações como pesquisas baseadas no sobrenome; assim, um índice com base em (sobrenome, primeiro nome, nome do meio) irá automaticamente adicionar vários índices usados em buscas mais rápidas.

    Pontos a lembrar:


    • Escolha com muito critério a coluna (s) para indexação.
    • Os campos indexados devem ser utilizados para as operações de pesquisa, e estas pesauisas devem ser reformatadas se forem executados cálculos sobre campos indexados.
    • O próprio índice ocupa espaço de armazenamento.
    • Cada operação de atualização de dados requer também uma atualização dos índices.
    • Um Key Length mais curto, reportado pelo comando EXPLAIN, é melhor. Menor comprimento de chave significa que é necessário menos espaço de armazenamento para o arquivo do índice, e também que o tempo para pesquisar o índice é menor. Por isso, os índices podem ser definidos para apenas parte das colunas. Por exemplo, INDEX (col1 (10), col2 (20)). Novamente, você tem que decidir judiciosamente qual o comprimento das colunas a ser utilizado para a indexação.

  • Usando o comando ANALYZE

    Use o comando ANALYZE para gerar uma distribuição de chaves, para a tabela a ser utilizada pelo otimizador de consultas do MySQL, para decidir quais índices serão melhores se utilizados em uma consulta.

    ANALYZE TABLE table_name;

  • Usando o comando OPTIMIZE

    Se há grandes quantidades de inserções/deleções em uma tabela, então o comando OPTIMIZE deve ser usado com freqüência para otimizar a movimentação da cabeça de leitura e gravação do disco durante a recuperação de dados. Isto é particularmente útil se são utilizados tamanhos variáveis de colunas, como varchar, texto, blob, etc. O comando OPTIMIZE faz a desfragmentação da fragmentação causada por campos de comprimento variável, como campos VARCHAR.

    OPTIMIZE TABLE table_name;

  • Usando funções especiais de carregamento de dados

    Para carregar os dados de um arquivo em uma tabela de dados, o uso de LOAD DATA INFILE é muito mais rápido do que ler dados de arquivo iterativamente e utilizar o comando INSERT. A sintaxe para este comando é:

    LOAD DATA INFILE 'filedata.dat' INTO TABLE table_name (col1,col2,...,colM) FIELDS TERMINATED BY '|'");

  • Configurando a PRIORIDADE dos comandos:

    Se há mais consultas do que inserções de dados, você pode ter uma prioridade mais baixa do comando INSERT utilizando:

    INSERT LOW_PRIORITY ou SELECT HIGH_PRIORITY


    Se o cliente não está interessado nos resultados do comando INSERT, então esta operação já pode imediatamente utilizar menos os recursos do sistema, utilizando-se o seguinte comando:

    INSERT DELAYED.

    Isso torna o sistema mais rápido porque acumula as inserções em lotes.

  • DELETE vs TRUNCATE

    DELETE deleta linha por linha, enquanto TRUNCATE deleta todas as linhas de uma só vez. Portanto, se você não estiver interessado no número de linhas suprimidas de uma tabela, como resultado do comando DELETE, utilize o comando TRUNCATE com a seguinte sintaxe:

    TRUNCATE TABLE table_name;

  • Baixando o overhead das verificações das permissões

    Permissões simples reduzem o overhead das verificações das pemissões.. Use o comando GRANT para configurar as permissões. A sintaxe geral deste comando é:

    GRANT Oper1,...,OperN ON db_name.tb_name TO user_name@computer_name IDENTIFIED BY password


    Por exemplo:

    GRANT SELECT, UPDATE ON EMP_DET.SALARY_DET TO feroz @ localhost IDENTIFIED BY 'abc123';

    Se a conta de usuário 'feroz' não estiver presente, será criada em localhost (embora os usuários possam acessar dados de qualquer computador) e será atribuída a senha 'abc123' à conta.

  • Use a função BENCHMARK ()

    Para saber quanto tempo uma determinada função ou expressão MySQL está demorando, use a função MySQL embutida:

    BENCHMARK(loop_count, expression)


    Este comando sempre retorna o valor 0, mas também imprime o tempo que demorou para executar.

  • Sincronizando tipos de dados

    Se colunas em tabelas contêm informações idênticas, defina o mesmo tipo de dados para estas colunas, de modo que as operações JOINT sejam realizadas mais rapidamente.

  • Diversos
    • Insira múltiplas linhas com uma única instrução SQL.
    • Remova parênteses desnecessários na cláusula WHERE.
    • Se a opção SQL_SMALL_RESULT for utilizada, o MySQL usa tabelas temporárias em memória, que proporcionam um acesso mais rápido.


A otimização de consultas não é tarefa para ser feita uma única vez. Se a sua base de dados é pesadamente utilizada, talvez você tenha de repetir o processo de otimização a cada 5-6 meses.