A maneira mais tradicional de prever o custo de uma query no MySQL é através do comando EXPLAIN. E isso é muito útil. Ninguém deveria colocar uma consulta em produção sem primeiro analisar a saída de um comando EXPLAIN.
Outra maneira, não muito tradicional, de analisar o comportamento de uma consulta é através de seu perfil (SHOW PROFILE).
No MySQL 8.0, foi introduzida uma métrica de custo de consulta. É uma variável de estado (LAST_QUERY_COST), que indica o custo da última consulta na sessão. Não é um número mágico. Não resolve tudo. Mas certamente serve como uma lanterna para um túnel escuro. Pode e deve ser combinado com EXPLAIN e SHOW PROFILE. Ou usado sozinho, para análises rápidas.
Para visualizar o custo de uma consulta, basta: 1/ Executar uma consulta (SELECT), 2/ Mostrar Status Like ‘Last_Query_Cost’. Será retornado 0 (zero) se nenhuma consulta foi executada na sessão atual, ou um número positivo. Quanto maior o número, pior é o custo da última consulta executada na sessão atual.
Vou usar o banco de dados Star Wars que criei para apresentações e treinamentos. Vamos brincar com duas tabelas e consultas simples. O objetivo é demonstrar o uso do Last_Query_Cost.
Tabela 1: Log de Transporte. Esta tabela registra todos os voos interplanetários nas rotas da Federação. Contém quase 16 milhões de voos registrados (linhas).
Tabela 2: Planetas. Uma tabela contendo os planetas cobertos pelas rotas da Federação. Com apenas 61 planetas (linhas).
O PROBLEMA COM A QUERY
O Senador Palpatine gostaria de saber quantos voos ocorreram de Alderaan para Cato Neimoidia, o objetivo do voo e quantos seres vivos estavam a bordo.
A demanda traduzida para SQL:
SELECT logID, pfrom.planet_name, pto.planet_name, flightTYPE, pob species_on_board
FROM transportation_log, planet pfrom, planet pto
WHERE pfrom.planetID = planetfromID AND pto.planetID = plantoID.planetID AND Planetfromid = 1 AND plantoID = 5;
No total, serão encontrados 6.298 voos (linhas). A consulta levou 0,11 segundos. E não há nenhum tipo de índice nesta tabela.
Ok, vamos aos comandos SQL e usar o LAST_QUERY_ID.
Antes de executar a demanda do Senador Palpatine, vamos brincar com uma consulta muito mais simples.
Uma consulta simples, em uma tabela pequena (61 linhas). Nos deu um custo de apenas 6.349. Guarde este número, pois servirá como um guia de comparação para nosso estudo. E que a força esteja com você!
Executei a consulta com LIMIT 10 para ter o resultado e o SQL visíveis. Mas o LIMIT não mudará o propósito do nosso estudo.
Tomamos um susto ao solicitar o custo da última consulta através do comando SHOW STATUS LIKE ‘Last_Query_Cost’.
O custo da consulta foi nada menos que 5.381.145.018,726037. Houve alguma interferência da Força? Um custo de 5 bilhões é muito custo para pouco SQL. Há uma lógica para entender esse número, mas para este artigo; apenas aceite que quanto maior… pior.
Vamos entender como o compilador MySQL 8.0 executaria esta consulta.
Observe que não há índice, evidenciado pela coluna Possible Keys = NULL. Claro, esse tipo de consulta em tabelas sem índices seria um desastre.
Vamos melhorar isso, criando alguns índices e avaliando se houve melhoria no custo.
Uau! O custo da consulta caiu de 5 bilhões para 223.494,664053. Em uma conta rápida, podemos assumir que o peso desta consulta se tornou 24.000 vezes mais leve. É muita melhoria.
O custo da consulta não se traduz apenas em tempo. Mas, no uso de recursos do servidor, como memória, CPU e E/S de disco. Quanto menor o custo da consulta, mais rápido e menos recursos serão consumidos.
Para melhorar esta consulta, criei um índice com as colunas planetfromID e planettoID (planetas de origem e destino), através do comando:
CREATE INDEX idx_planet_ALL ON transportation_log(planetfromID, planettoID);
O EXPLAIN desta consulta parece assim.
A CONCLUSÃO
Last_Query_Cost é uma variável de estado valiosa para nos dizer o peso de uma consulta. E, se combinada com EXPLAIN (e, eventualmente, com SHOW PROFILE), nos fornece uma boa plataforma de análise para melhorar o desempenho da consulta.
Para mais artigos como esse, confira nosso blog!
