MySQL

Otimização de consultas em em MySQL

A utilização eficiente de um SGBD é um fator crucial para que se tenha uma aplicação estável, ágil e capaz de escalar em termos de números de acessos e volume de dados. Considerando o MySQL, existem vários aspectos que devem ser considerados para que ele possa funcionar de forma otimizada, proporcionando à aplicação um tempo de resposta adequado aos seus propósitos. Dentre os vários fatores, podemos citar os ajustes no sistema operacional, escolha de equipamentos e hardwares com bom desempenho, ajustes de parâmetros do MySQL, e por último mais não menos importante, a otimização de consultas.

O uso prático de um SGBD se resume em armazenar informações e disponibilizá-las aos seus usuários através de consultas SQL (Structured Query Language). Neste contexto, a maior parte do trabalho realizado por este sistema é exatamente atender a estas requisições tentando processá-las no menor tempo possível, retornando os dados para o usuário que iniciou a consulta. Sendo assim, em sistemas que geram um volume elevado de requisições, se as mesmas não são executadas no menor tempo possível, não é possível manter o desempenho global do sistema em um patamar que satisfaça aos requisitos de tempo do usuário final.

Portanto, para executar uma consulta SQL, o MySQL necessita percorrer várias etapas até gerar o resultado para o usuário que a submeteu. O esforço para a resolução desta consulta dependerá de diversos fatores, tais como o uso de índices, as operações utilizadas, JOINs e sub-selects, por exemplo, além da estrutura dos dados existentes.

Este artigo tem como objetivo apresentar o processo de execução de consultas no MySQL e as principais técnicas para a otimização destes comandos. Além disto, é preciso compreender o plano de execução de uma consulta e saber como intervir para melhorar a estratégia de execução adotada pelo otimizador. Inicialmente serão apresentadas as etapas de execução de uma consulta e em seguida como otimizar cada uma destas etapas. Posteriormente será apresentado o comando EXPLAIN, bem como todos os seus aspectos relevantes. E finalmente, serão discutidos exemplos práticos de comandos e suas respectivas otimizações, salientando a importância do uso de índices e as boas práticas para a sua criação.

Etapas de execução de uma consulta

Todas as vezes que uma consulta SQL é submetida ao MySQL, é iniciada uma série de etapas para a geração de um conjunto resultante de informações que serão enviadas de volta ao usuário que iniciou a requisição de dados. Portanto, seria conveniente buscar incrementos de desempenho em cada etapa de forma a economizar esforços, possibilitando a geração de um resultado no menor tempo possível. A Figura 1 ilustra cada uma das etapas percorridas pelo MySQL a fim de produzir um resultado a partir de um comando SQL.

Figura 1. Processo de execução de consultas em MySQL

Conforme ilustra a Figura 1, a execução da consulta exige a repetição de procedimentos, em alguns casos complexos, todas as vezes que uma consulta é submetida. Em especial, se a mesma consulta é executada várias vezes, os mesmos passos devem ser seguidos para cada execução, o que onera ainda mais este custo de execução.

A seguir estão descritas cada uma das etapas, bem como as estratégias de otimização que podem ser empregadas a fim de se agilizar a execução das mesmas.

Parser

O Parser é o responsável por interpretar o comando SQL submetido ao MySQL, verificando se o mesmo está sintaticamente correto. O objetivo do parser é converter o comando SQL para um formato binário interno, para que então possa ser submetido ao otimizador de consultas.

É notório neste caso que o parser gera uma sobrecarga no processo de execução de uma consulta, uma vez que esta transformação deve ser aplicada a todo e qualquer comando que venha a ser submetido ao MySQL. O pior caso é aquele onde o mesmo comando é executado repetidas vezes, acumulando assim esta sobrecarga a cada requisição. Neste cenário, o ideal seria que a etapa de parser fosse realizada somente a primeira vez em que o comando fosse submetido ao servidor, e que nas execuções posteriores esta etapa fosse suprimida, uma vez que já existiria o seu correspondente binário dentro do SGBD.

O MySQL dispõe de duas alternativas úteis para a aceleração do processo de parser, que são os prepared statement e stored procedures. Estes mecanismos possibilitam a execução de um mesmo comando, ou de uma seqüência de comandos, realizando a etapa de parser somente uma vez. Neste caso, o ganho de desempenho é visível, uma vez que a etapa de parser será totalmente desprezada para execuções sucessivas de um mesmo comando. A Listagem 1 ilustra a utilização dos prepared statements em MySQL.

Listagem 1. Utilização de prepared statements em MySQL.

mysql> PREPARE comando FROM "SELECT nome FROM Pessoas WHERE id = ?";

Query OK, 0 rows affected (0.09 sec)

mysql> SET @codigo = 10;

Query OK, 0 rows affected (0.00 sec)

mysql> EXECUTE comando USING @codigo;

+---------+

| name    |

+---------+

| Carla   |

+---------+

1 row in set (0.03 sec)

mysql> SET @codigo = 25;

Query OK, 0 rows affected (0.00 sec)

mysql> EXECUTE comando USING @codigo;

+---------+

| name    |

+---------+

| Paulo   |

+---------+

1 row in set (0.03 sec)

mysql> DEALLOCATE PREPARE comando;

Query OK, 0 rows affected (0.00 sec)

A Listagem 1 fornece um exemplo de um prepared statement utilizado para listar o nome de uma pessoa cujo código será informado a cada execução deste comando. O comando PREPARE realiza a transformação do comando para um formato binário interno e o deixa disponível para ser invocado posteriormente.

Para utilizar o comando preparado anteriormente, utilizou-se de uma variável para informar o código da pessoa a ser exibida. Para isto, utiliza-se o comando EXECUTE informando o nome do prepared statement a ser executado e os argumentos que substituirão os sinais de interrogação ('?') utilizados no preparo do comando. Vale ressaltar que havendo mais de uma interrogação, deve-se informar na cláusula USING um valor para cada interrogação utilizada na ordem em que as mesmas foram definidas no prepared statements. Vale destacar também que as variáveis devem ser separadas por vírgulas dentro da cláusula USING do comando EXECUTE."