O comando EXPLAIN é a ferramenta fundamental para qualquer desenvolvedor que deseja entender como o otimizador do MySQL executa uma consulta. Ele funciona como um diagnóstico detalhado, revelando se o banco de dados está utilizando índices de forma eficiente ou se está recorrendo a métodos custosos, como varreduras completas de tabela.

-- Exemplo básico de uso
EXPLAIN SELECT * FROM colaboradores WHERE departamento_id = 5;

Interpretando as Colunas Principais do EXPLAIN

Ao executar o EXPLAIN, o MySQL retorna uma tabela com diversas métricas. Abaixo, detalhamos os indicadores mais críticos para a performance:

1. type (Tipo de Junção)

Esta é possivelmente a coluna mais importante. Ela indica como as tabelas são lidas. A ordem de performance, do melhor para o pior, é:

• system/const: A tabela tem apenas uma linha correspondente (ex: busca por Chave Primária).
• eq_ref: Para cada combinação de linhas das tabelas anteriores, uma linha é lida desta tabela (comum em JOINs com chaves únicas).
• ref: Todas as linhas com valores de índice correspondentes são lidas (índices não únicos).
• range: Recupera apenas linhas que estão em um determinado intervalo, usando um índice.
• index: Varredura completa no índice (melhor que ALL, mas ainda custosa).
• ALL: Varredura completa na tabela (deve ser evitada em tabelas grandes).

-- Ótima performance (const)
EXPLAIN SELECT * FROM produtos WHERE id_produto = 500;

-- Performance ruim (ALL)
EXPLAIN SELECT * FROM produtos WHERE descricao LIKE '%promocao%';

2. key (Índice Utilizado)

Indica qual índice o MySQL decidiu usar para executar a query. Se o valor for NULL, nenhum índice está sendo aproveitado. É comum comparar com a coluna possible_keys, que lista os candidatos que o MySQL considerou.

3. rows (Estimativa de Linhas)

Representa o número de linhas que o MySQL acredita que deve examinar para encontrar o resultado. Quanto menor este número, mais rápida será a execução.

4. Extra (Informações Adicionais)

Contém detalhes cruciais sobre o processamento interno:

• Using index: Indica que a consulta é resolvida apenas usando o índice (Index Covering), sem acessar a tabela física. Excelente para performance.
• Using filesort: O MySQL precisará fazer um passo extra para ordenar os dados. Sinal de que o ORDER BY não está otimizado.
• Using temporary: O MySQL cria uma tabela temporária para armazenar o resultado (comum em GROUP BY ou DISTINCT ineficientes).

Cenários Práticos de Otimização

Caso 1: Identificando a falta de índices

Imagine uma consulta que busca pedidos por data:

-- Consulta original
EXPLAIN SELECT * FROM vendas WHERE data_venda = '2023-10-25';

-- Resultado hipotético: type=ALL, rows=50000
-- Solução: Criar índice na coluna data_venda
CREATE INDEX idx_data_venda ON vendas(data_venda);

-- Resultado após índice: type=ref, rows=15

Caso 2: Problemas com ordenação (Filesort)

Se você ordena por uma coluna que não está indexada, o desempenho cai drasticamente.

-- Gerando filesort
EXPLAIN SELECT * FROM clientes ORDER BY sobrenome;

-- Solução: Índice na coluna de ordenação
CREATE INDEX idx_sobrenome ON clientes(sobrenome);

Caso 3: O perigo de funções em colunas indexadas

Aplicar funções diretamente na coluna de busca invalida o uso do índice.

-- Ineficiente: o índice em 'criado_em' não será usado
EXPLAIN SELECT * FROM contas WHERE YEAR(criado_em) = 2023;

-- Eficiente: reescrevendo para usar o índice
EXPLAIN SELECT * FROM contas WHERE criado_em >= '2023-01-01' AND criado_em <= '2023-12-31';

Recursos Avançados no MySQL 8.0+

EXPLAIN FORMAT=JSON

Fornece uma visão muito mais rica, incluindo o custo estimado da consulta (query_cost), o que ajuda a entender por que o otimizador escolheu um caminho em vez de outro.

EXPLAIN FORMAT=JSON SELECT * FROM estoque WHERE quantidade < 10;

EXPLAIN ANALYZE

Diferente do EXPLAIN tradicional que apenas estima, o ANALYZE executa a consulta e mede o tempo real gasto em cada etapa do plano de execução.

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

Checklist de Diagnóstico Rápido

1. A coluna type é "ALL"? Se sim, verifique se a coluna no WHERE possui índice.
2. A coluna key é NULL? O MySQL não encontrou índices úteis.
3. O rows é muito alto? Filtre melhor seus dados ou melhore a seletividade do índice.
4. A coluna Extra contém "Using filesort" ou "Using temporary"? Revise seus ORDER BY e GROUP BY.