Tradução do Artigo de Sean Hull

Introdução

A otimização de consultas é uma parte frequentemente esquecida das aplicações. As agendas de desenvolvimento são o que elas são. Obter os corretos resultados e fazer a aplicação funcionar são as principais prioridades. Então, fazer um teste completo e benchmarking de consultas são frequentemente deixados de lado.

Com essa pequena introdução à otimização de consultas no MySQL, esperamos encorajar a dar ao menos alguma atenção a estas questões. Também vamos ajudá-lo a identificar algumas das otimizações mais comum que você podssa utilizar.

1. Otimizando Tipos de Dados

   Há uma grande quantidade de tipos de dados diferentes para escolher em MySQL, e às vezes isso pode ser algo esmagador. Se você é um DBA você estará olhando para os tipos de dados estritamente em termos de otimizações, e esse será seu critério para a escolha. Mas os desenvolvedores olharão para eles em termos de soluções elegantes, a velocidade de codificação, ou simplesmente a escolha mais óbvia. Então como um DBA você gostaria de ser confrontado com as opções não-otimizadas aqui. Quando puder, tente influenciar as decisões usando algumas orientações gerais.

   • Tente evitar NULLs

     NULLs são um caso especial, na maioria dos bancos de dados. O MySQL não é exceção a esta regra. Eles exigem mais codificação para serem manipulados internamente, mais controle, lógica de índice especial, e assim por diante. Alguns desenvolvedores simplesmente não estão cientes disso, e como NULL é o padrão, esta é a forma como as tabelas são criadas. No entanto, é melhor usar NOT NULL na maioria dos casos, e até mesmo usar um valor especial, como 0 ou -1 para o seu valor padrão.

   • Usar campos menores, sempre que possível

     Quando o MySQL lê os dados do disco, ele armazena na memória, e usa ciclos de CPU e E/S de disco para lê-los. Isso significa que os tipos de dados menores, que ocupam menos espaço serão lidos do disco e empacotados em memória de forma mais eficiente. Dito isto, não vá tão afoito com a criação de elementos com esses tipos de dados de forma que você não tenha espaço para mudanças inesperadas na aplicação posteriormente. Alterações da tabela exigirão reestruturação, que é uma sobrecarga, e potencialmente mudanças de código, que é também uma dor de cabeça. Portanto, procure um equilíbrio, não fazendo campos maiores do que eles precisem ser.

2. Tome cuidado com Conversões de Conjunto de Caracteres

   O conjunto de caracteres que o seu cliente ou aplicativo está usando pode ser diferente do da própria tabela. Isso vai exigir que o MySQL converta implicitamente em tempo de execução. Além disso, certos conjuntos de caracteres, tais como UTF8, suportam caracteres multi-byte, então ele torna os requisitos de armazenamento maior.

3. Otimizando COUNT(my_col) e COUNT(*)

   Se você estiver usando tabelas MyISAM, count(*) sem a cláusula WHERE executa muito rápido porque as estatísticas sobre a contagem das linhas serão exatas. Assim, o MySQL não tem que olhar para a tabela como um todo para começar a contagem. O mesmo pode ser dito para a COUNT(my_col) se essa coluna é NOT NULL.

   Se você estiver fazendo COUNT() com uma cláusula WHERE, não há muito que você pode fazer para otimizá-lo, além do óbvio de indexar as colunad que estiverem presentes na cláusula WHERE. Pode ser possível que um índice de cobertura ajude-o neste caso, para uma clausula WHERE mais complexa.

   Resumindo as sugestões acima, você deve trabalhar com as tabelas de resumo. Estas podem permitir-lhe manter atualizado a informação sobre o conteúdo da sua tabela. Você pode utilizar triggers, ou lógica de aplicativo para manter a tabela de resumo sempre atualizada, ou você pode executar um batch periodicamente para preenchê-lo com informações atualizadas. Se você fizer este último, a sua informação estará próxima, mas não exata, dependendo de qual a frequencia o batch executa. Pese a necessidade de seu aplicativo de obter informações precisas contra a sobrecarga de manter esses dados atualizados, e estabeleça um equilíbrio.

4. Otimizando Sub-consultas

   O engine de otimização do MySQL nem sempre é o mais eficiente quando se trata de subconsultas. É por isso que muitas vezes é uma boa idéia converter uma subconsulta para um join. Joins são tratados adequadamente pelo otimizador. Claro, certifique-se de que a coluna que está realizando o join esteja indexado na outra tabela. Na primeira tabela, o MySQL normalmente faz uma varredura completa confrontando com um subconjunto de linhas da segunda tabela. Isto é parte do algoritmo de loops aninhados, que o MySQL frequentemente usa para realizar operações de junção.

5. Otimizando UNION

   UNION tem uma otimização interessante que existe em umas poucas bases diferentes. É óbvio que quando você pensou sobre como ele funciona. UNION lhe traz as linhas de duas tabelas que não existem uma na outra. Assim, implicitamente, você estará removendo duplicidade. Para fazer isso, o banco de dados MySQL deve retornar linhas distintas e, portanto, deve classificar os dados. Ordenação, como sabemos, é custoso, especialmente para as tabelas de grandes dimensões.

   UNION ALL pode muito bem ser um grande aumento de velocidade para você. E se você já souber que seus dados não contêm duplicatas em qualquer linha, ou se você não se importar com duplicatas? Em qualquer caso, UNION ALL é para você. Além disso, pode haver outras maneiras que você pode evitar a duplicação de suas linhas usando alguma lógica de aplicação, então você sabe que UNION ALL irá fornecer os resultados que deseja, sem a pesada sobrecarga de classificar os dados.

   Conclusão

   Esta lista de otimizações de consulta não não esgotam o assunto, mas deve ajustá-lo no caminho certo para otimizar o SQL em sua aplicação. Sempre que possível, tente algumas soluções diferentes, olhe para os planos de consulta, teste em grandes conjuntos de dados, avalie os resultados e veja o que funciona no mundo real. Além disso, seja pró-ativo, mantendo um olho em seu log de consultas lentas, e identifique consultas adicionais que podem necessitar de ajuste o mais cedo possível.