MySQL - Como descobrir se a Query está muito pesada

Se você não é um gênio louco das galáxias e que tem absoluta convicção que sabe tudo sobre banco de dados e consultas, certamente, tem dúvidas sobre o comportamento e o custo que uma consulta representará para o banco de dados.

A forma mais tradicional de prever o custo de uma consulta no MySQL é através do comando EXPLAIN. E, ele ajuda muito. Ninguém deveria colocar uma consulta em produção sem antes analisar a saída de um comando EXPLAIN.

Outra forma, 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 índica o custo da última consulta da sessão. Não é um número mágico. Não resolve tudo. Mas, certamente, serve de lanterna para um túnel escuro. Pode e deve ser combinado com EXPLAIN e SHOW PROFILE. Ou, usado sozinho, para uma análise rápida.

Para visualizar o custo de uma consulta, basta: 1/ Executar uma consulta (SELECT), 2/ Show Status Like ‘Last_Query_Cost’ . Será retornado 0 (zero) caso nenhuma consulta tenha sido executada na presente sessão, ou, um número positivo. Quanto maior o número, pior é o custo da última consulta executada na presente sessão.

Irei utilizar o banco de dados star wars que criei para apresentações e treinamentos. Vamos brincar com duas tabelas, e, consultas simples. O objetivo é demostrar o uso do Last_Query_Cost.

Tabela 1: Transportion Log. Esta tabela registra todos os voos interplanetários em rotas da Federação. Ela contém quase 16 milhões de voos registrados (linhas).

Tabela 2: Planets. Tabela que contém os planetas cobertos pelas rotas da Federação. Com apenas 61 planetas (linhas).

O problema

O Senador Palpatine gostaria de saber quantos voos aconteceram, partindo de Alderaan para Cato Neimoidia, qual a finalidade do voo, e, quantos seres vivos havia a bordo.

A demanda traduzida em 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 = planttoID.planetID AND Planetfromid = 1 AND planttoID = 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.

Muito bem, vamos aos comandos SQL e uso do LAST_QUERY_ID.

Antes de executar a demanda do Senador Palpatine, vamos brincar com uma consulta bem mais simples.

 Uma consulta simples, em uma tabela pequena (61 linhas). Nos gerou um custo de apenas 6.349. Guardem este número, pois, ele servirá de guia de comparação para nosso estudo. E que a força esteja com você!

 Executei a consulta com LIMIT 10, apenas para termos o resultado e o SQL visíveis. Mas, o LIMIT não irá alterar o objetivo de nosso estudo.

 Ao solicitar o custo da última consulta através do comando SHOW STATUS LIKE ‘Last_Query_Cost’ tomamos um susto.

 O custo da consulta foi de nada menos que 5.381.145.018,726037. Será que houve alguma interferência da Força? Um custo de 5 bilhões é muito custo para pouco SQL. Existe uma lógica para entender este número, mas, para fins deste artigo; apenas aceite que quanto maior... pior.

 Vamos entender como o compilador do MySQL 8.0 executaria esta consulta:

Perceba que não há nenhum índice, evidenciado pela coluna Possible Keys = NULL. Claro que seria um desastre este tipo de consulta em tabelas sem índices.

Vamos melhorar isso, criando alguns índices, e, avaliando se houve melhoria no custo. 

Wow! O custo da consulta caiu de 5 bilhões para 223.494,664053. Em uma conta rápida de divisão, podemos assumir que o peso desta query ficou 24 mil vezes mais leve. É muita melhoria.

O custo da consulta não se traduz tão, somente, em tempo. Mas, em uso de recursos do servidor, tais como: memória, cpu, e, I/O de disco. Quanto menor o custo da consulta, mais rápida e menos recursos serão tomados.

Para melhorar esta consulta eu criei um índice com as colunas planetfromID e plantoID (planetas de origem e destino), através do comando:

CREATE INDEX idx_planet_ALL ON transportation_log (planetfromID, planettoID);

O EXPLAIN desta consulta ficou assim: 

CONCLUSÃO

O Last_Query_Cost é uma variável de estado valiosa para indicar-nos o peso de uma consulta. E, se combinada com EXPLAIN (e, eventualmente, com SHOW PROFILES) nos entrega uma boa plataforma de análise de melhoria de performance de consultas.