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.
Na Listagem 1, o mesmo comando foi executado para a busca da Pessoa com o id igual a 10 e 25, sendo que nenhum parser foi realizado para estas execuções, uma vez que o comando já havia sido previamente preparado. Esta abordagem gera um ganho de desempenho considerável, pois uma etapa do processo é totalmente descartada no momento da execução do comando. Além disto, vale lembrar que o mesmo raciocínio se aplica às stored procedures. Finalmente, o comando DEALLOCATE PREPARE exclui um prepared statement, liberando os recursos alocados para ele.
Entretanto, é preciso salientar que o prepared statement é criado por conexão (thread), ou seja, dois usuários que utilizam conexões distintas não compartilham os seus prepared statements. Assim, este recurso é útil para as aplicações que submetem o mesmo comando repetidas vezes dentro de uma mesma conexão. No caso de aplicações Web, este comportamento não é observado, uma vez que não utilizam conexões persistentes, submetendo assim os comandos sempre em conexões distintas, o que minimiza os benefícios da utilização deste recurso. Neste caso, apesar de os caches serem por conexão, o que limita parcialmente os ganhos de desempenho, o mais aconselhado seria a utilização de stored procedures, pois as mesmas são compartilhadas entre todos os usuários.
Otimizador
O otimizador é o responsável por encontrar o caminho mais curto para a busca das informações desejadas pelo usuário. Este realiza transformações nas consultas de forma a encontrar comandos equivalentes que possam ser executados no menor tempo. Além disto, o otimizador procura detectar qual a melhor ordem de leitura das tabelas em um JOIN a fim de retornar os dados no menor tempo, e ainda opta pela utilização de um índice, caso exista, para agilizar a execução do comando.
O resultado gerado pelo otimizador é o plano de execução que deve ser seguido pela etapa de execução a fim de se obter os resultados esperados pelo usuário. Todas as decisões tomadas pelo otimizador para a geração deste plano de execução são baseadas em estatísticas de dados existentes no sistema, tais como cardinalidade de índices, tamanho de registros e tabelas, dentre outras. Por se basear em metadados, que podem não estar atualizados ou não refletirem situações inerentes ao modelo de dados, este plano de execução pode não espelhar uma situação ótima de busca das informações. Portanto, é preciso compreender a composição deste plano para que se possa intervir de forma a conduzir o MySQL a encontrar o menor caminho para a execução de uma consulta específica.
O comando EXPLAIN permite visualizar este plano de execução e está descrito com detalhes na seção a seguir.
Explicando o EXPLAIN
O comando EXPLAIN permite a visualização do plano de execução de um comando SELECT. Para isto, basta adicionar a palavra EXPLAIN ao comando que se deseja avaliar. A Listagem 2 ilustra a execução do comando EXPLAIN em uma consulta que exibe o nome de todos os países situados na América do Sul e o nome de suas respectivas capitais.
Listagem 2. EXPLAIN da consulta que lista o nome dos países da América do Sul e o nome de suas capitais.
mysql> EXPLAIN SELECT co.name, ci.name
-> FROM City ci, Country co
-> WHERE co.capital = ci.id AND region LIKE 'South am%'\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: co
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
...
