EXPLAIN ANALYZE e EXPLAIN FORMAT=TREE no MySQL

Por muito tempo, entender como o MySQL executa uma consulta significava ler uma tabela cheia de siglas com EXPLAIN: type, key, rows, Extra. Esses números eram só estimativas do otimizador — nunca o que realmente aconteceu ao rodar a consulta. Desde o MySQL 8.0.18, o comando EXPLAIN ANALYZE resolve isso: ele executa a consulta de verdade e devolve o plano de execução junto com tempos e contagens reais, em um formato de árvore muito mais legível que a tabela tradicional.

Do EXPLAIN tabular ao FORMAT=TREE

O EXPLAIN clássico só estima custo e não roda a consulta:

EXPLAIN SELECT * FROM pedidos WHERE cliente_id = 42;

A saída em formato de árvore, disponível desde o MySQL 8.0.16 com EXPLAIN FORMAT=TREE, representa a mesma estimativa de um jeito mais próximo de como as operações se encaixam:

EXPLAIN FORMAT=TREE
SELECT * FROM pedidos WHERE cliente_id = 42;

-> Filter: (pedidos.cliente_id = 42)  (cost=105.00 rows=10)
    -> Table scan on pedidos  (cost=105.00 rows=1000)

A partir do MySQL 8.0.32, a variável explain_format permite tornar TREE o formato padrão de qualquer EXPLAIN, sem precisar repetir a cláusula em toda consulta:

SET explain_format = 'tree';
EXPLAIN SELECT * FROM pedidos WHERE cliente_id = 42;

EXPLAIN ANALYZE: o plano de verdade

Trocando EXPLAIN por EXPLAIN ANALYZE, o MySQL executa a consulta e anota, em cada nó da árvore, o custo estimado ao lado do tempo e das linhas medidos de fato:

EXPLAIN ANALYZE
SELECT * FROM pedidos WHERE cliente_id = 42;

-> Filter: (pedidos.cliente_id = 42)
    (cost=105.00 rows=10) (actual time=0.045..2.310 rows=8 loops=1)
    -> Table scan on pedidos
        (cost=105.00 rows=1000) (actual time=0.020..2.100 rows=1000 loops=1)

actual time=0.045..2.310 significa que a primeira linha desse passo saiu em 0,045 ms e todas as linhas foram lidas até 2,310 ms. rows=8 é a contagem real de linhas produzidas, e loops=1 indica quantas vezes esse passo foi executado — em consultas com JOIN, um passo interno pode rodar em várias iterações, e o tempo total gasto ali é aproximadamente tempo médio × loops.

Lendo a árvore: de dentro para fora

Os nós mais indentados são executados primeiro; o nó raiz, no topo, é o último a terminar e representa o resultado final. No exemplo acima, o table scan roda por completo (1000 linhas, 2,1 ms) antes do Filter descartar a maioria delas e sobrar só 8. Isso já denuncia o problema: falta um índice em cliente_id, forçando uma varredura completa da tabela.

CREATE INDEX idx_pedidos_cliente ON pedidos (cliente_id);

EXPLAIN ANALYZE
SELECT * FROM pedidos WHERE cliente_id = 42;

-> Index lookup on pedidos using idx_pedidos_cliente (cliente_id=42)
    (cost=2.51 rows=8) (actual time=0.012..0.028 rows=8 loops=1)

Com o índice, o mesmo resultado sai em uma fração do tempo, sem varrer a tabela inteira.

Comparando estimativa e realidade em um JOIN

Em consultas com múltiplas tabelas, comparar o rows estimado com o rows real de cada nó ajuda a achar estatísticas desatualizadas — quando o otimizador escolhe um plano ruim porque acha que uma tabela tem poucas linhas quando na verdade tem milhares:

EXPLAIN ANALYZE
SELECT p.id, c.nome
FROM pedidos p
JOIN clientes c ON c.id = p.cliente_id
WHERE p.status = 'pendente';

-> Nested loop inner join  (cost=245.30 rows=50) (actual time=0.05..8.40 rows=1200 loops=1)
    -> Filter: (p.status = 'pendente')  (cost=105.00 rows=50) (actual time=0.03..3.10 rows=1200 loops=1)
        -> Table scan on p  (cost=105.00 rows=1000) (actual time=0.02..2.80 rows=1000 loops=1)
    -> Single-row index lookup on c using PRIMARY (id=p.cliente_id)
        (cost=0.25 rows=1) (actual time=0.003..0.003 rows=1 loops=1200)

A estimativa era de 50 linhas (rows=50), mas o resultado real foi 1200 (actual ... rows=1200). Esse tipo de discrepância grande é sinal para rodar ANALYZE TABLE pedidos e atualizar as estatísticas usadas pelo otimizador.

Quando usar

Diagnosticar consultas lentas em produção (ou em uma cópia dos dados), quando o EXPLAIN tradicional não deixa claro onde o tempo é gasto.
Confirmar se um índice novo está realmente sendo usado e reduzindo linhas examinadas.
Detectar estimativas desatualizadas do otimizador antes que causem planos de execução ruins.
Comparar duas formas de escrever a mesma consulta com números reais, em vez de achismo.

Cuidados

EXPLAIN ANALYZE executa a consulta de verdade — diferente do EXPLAIN simples. Para SELECT isso é inofensivo, mas o comando também aceita UPDATE e DELETE com múltiplas tabelas (desde o MySQL 8.0.19), e nesse caso as alterações são aplicadas de verdade. A documentação oficial recomenda envolver o comando em uma transação e dar ROLLBACK depois, caso não se queira persistir a mudança:

START TRANSACTION;
EXPLAIN ANALYZE
UPDATE pedidos SET status = 'processado' WHERE cliente_id = 42;
ROLLBACK;

Compatibilidade

EXPLAIN FORMAT=TREE está disponível desde o MySQL 8.0.16 e EXPLAIN ANALYZE desde o MySQL 8.0.18. A variável explain_format, que permite tornar TREE o formato padrão do EXPLAIN sem repetir a cláusula, foi adicionada no MySQL 8.0.32 (janeiro de 2023) e segue disponível no MySQL 8.4 LTS e nas versões 9.x.

documentação oficial

Conclusão

EXPLAIN ANALYZE e o formato de árvore tornaram a análise de plano de execução no MySQL muito mais direta: em vez de decifrar uma tabela de estimativas, dá para ver exatamente onde o tempo é gasto e comparar isso com o que o otimizador esperava. Vale trocar o EXPLAIN tradicional por ele sempre que uma consulta estiver lenta e a causa não for óbvia.