Planos de consulta

Uma consulta pode usar um índice e ainda examinar milhares de linhas, ou ficar lenta com um caminho bom porque está esperando outra conexão. O plano de consulta mostra o caminho que o MySQL escolheu, e a leitura correta desse plano separa um caminho ruim das outras causas de demora.

O plano e o EXPLAIN

O plano é a estratégia que o otimizador escolhe para encontrar, juntar, filtrar e ordenar as linhas. O comando EXPLAIN, escrito antes de um SELECT, mostra esse plano sem executar a consulta:

• EXPLAIN SELECT * FROM produtos WHERE codigo >= '7891234567890' ORDER BY codigo, id LIMIT 20;

O plano vale para aquela consulta, com aqueles valores. Uma investigação usa o SQL real da tela, com os mesmos filtros e valores, obtido no registro de SQL da aplicação. Uma consulta parecida, sem um dos filtros, pode ter outro plano.

As colunas do EXPLAIN

O EXPLAIN devolve uma linha para cada tabela da consulta. As colunas principais são estas:

• type: a forma de acesso à tabela. O valor ALL indica a leitura da tabela inteira, e index indica a leitura do índice inteiro. O valor range indica uma faixa do índice, ref e eq_ref indicam a busca por um valor, e const indica uma única linha, encontrada pela chave primária ou por um índice de valores únicos.
• possible_keys: os índices que poderiam servir à consulta.
• key: o índice escolhido. Quando a coluna vem vazia, nenhum índice foi usado.
• rows: a estimativa de quantas linhas serão examinadas.
• filtered: a estimativa da porcentagem dessas linhas que atende às condições.
• Extra: informações adicionais sobre o filtro e a ordenação.

Entre os valores de type, ALL é o acesso mais caro e const é o mais barato. O número em rows é uma estimativa e, em consultas com LIMIT, pode ser bem maior do que as linhas realmente lidas, porque a estimativa não considera a parada antecipada. Em uma consulta com junção, uma linha do plano com ALL em uma tabela grande costuma ser a parte mais cara.

As versões antigas têm um EXPLAIN mais limitado. Antes do MySQL 5.7, a coluna filtered só aparece com o comando EXPLAIN EXTENDED, e, até o MySQL 5.5, o EXPLAIN funciona apenas com SELECT.

As indicações do Extra

• Using where: o MySQL aplica uma condição às linhas lidas.
• Using index: a consulta foi atendida apenas com o índice, sem buscar a linha completa. Esse índice é chamado de índice de cobertura para aquela consulta.
• Using filesort: o MySQL ordena as linhas em uma etapa extra, porque nenhum índice usado entrega a ordem pedida. A indicação não significa necessariamente uma gravação em disco, nem um defeito.
• Using temporary: o MySQL monta uma tabela temporária para resolver a consulta, o que é comum em agrupamentos sem um índice adequado.

Um plano lido por partes

Na consulta de exemplo, com um índice na coluna codigo, o plano mostra range em type e o nome desse índice em key, sem Using filesort no Extra, porque o índice já entrega as linhas na ordem pedida. Sem esse índice, o plano mostra ALL em type, key vazio, rows próximo da quantidade de produtos da tabela e Using filesort no Extra. Nesse caso, o MySQL lê a tabela inteira e ordena todas as linhas para devolver apenas vinte.

Estimativa e execução real

O EXPLAIN mostra estimativas. A partir do MySQL 8.0.18, o comando EXPLAIN ANALYZE executa a consulta de verdade e mostra os tempos e as quantidades de linhas observados em cada etapa:

• EXPLAIN ANALYZE SELECT * FROM produtos WHERE codigo >= '7891234567890' ORDER BY codigo, id LIMIT 20;

Como executa a consulta, o EXPLAIN ANALYZE tem o mesmo custo dela, e uma consulta pesada sobrecarrega o servidor da mesma forma durante uma ocorrência. Esse recurso não existe nas versões 5.x.

O plano também não explica uma consulta que espera outra conexão. Nesse caso, o comando SHOW PROCESSLIST; mostra a conexão parada em um estado de espera, e nenhum índice resolve a demora.

As estatísticas

O otimizador escolhe o plano com base em estatísticas sobre os dados, como a cardinalidade dos índices. Estatísticas desatualizadas podem levar a um plano ruim, principalmente depois de uma carga grande de dados. No InnoDB, o servidor recalcula as estatísticas sozinho depois de uma quantidade grande de alterações. No MyISAM, elas não acompanham as gravações e só mudam com o ANALYZE TABLE ou com manutenções como o OPTIMIZE TABLE. Um comando recalcula as estatísticas de uma tabela:

• ANALYZE TABLE produtos;

Em algumas versões, um ANALYZE TABLE executado enquanto uma consulta longa usa a tabela faz as consultas seguintes esperarem o fim dessa consulta. Por isso, a análise fica para um momento de pouco movimento.

Validar uma mudança

Depois da criação de um índice ou da reescrita de uma consulta, a validação compara a mesma consulta, com os mesmos valores, antes e depois da mudança: o plano, as linhas examinadas e o tempo. Uma execução rápida em um servidor de testes pode ter menos dados, mais memória livre e nenhuma concorrência, e o resultado nem sempre se repete no servidor da loja.