MySQL EXPLAIN ANALYZE Explicado: Leia Planos de Execução e Otimize Consultas (Guia 8.0)

1. Introdução

Planos de Execução: Essenciais para a Otimização de Desempenho de Banco de Dados

Em aplicações web e sistemas corporativos, o desempenho do banco de dados é um fator crítico que afeta diretamente o tempo de resposta geral. Ao usar o MySQL em particular, compreender o “plano de execução” é essencial para avaliar a eficiência das consultas. O comando tradicional EXPLAIN exibe o plano de execução antes de executar uma instrução SQL e há muito tempo fornece aos desenvolvedores insights valiosos.

“EXPLAIN ANALYZE” Introduzido no MySQL 8.0

Introduzido no MySQL 8.0.18, EXPLAIN ANALYZE é um aprimoramento poderoso do EXPLAIN tradicional. Enquanto o EXPLAIN fornecia apenas um “plano teórico”, o EXPLAIN ANALYZE realmente executa a consulta e devolve dados medidos, como tempo de execução e contagem de linhas processadas. Isso permite identificar gargalos com mais precisão e validar os resultados da otimização de consultas.

Por que o EXPLAIN ANALYZE é Importante

Por exemplo, a ordem dos JOINs, o uso de índices e as condições de filtragem afetam significativamente o tempo de execução. Ao usar o EXPLAIN ANALYZE, você pode confirmar visualmente como uma instrução SQL se comporta e determinar onde existem ineficiências e o que deve ser otimizado. Isso é especialmente indispensável ao trabalhar com grandes volumes de dados ou consultas complexas.

Propósito deste Artigo e Público‑Alvo

Este artigo explica tudo, desde os conceitos básicos do EXPLAIN ANALYZE do MySQL até a interpretação de sua saída e a aplicação de técnicas práticas de otimização. Destina‑se a desenvolvedores e engenheiros de infraestrutura que utilizam MySQL regularmente, bem como a engenheiros interessados em ajuste de desempenho. Para garantir clareza mesmo para iniciantes, incluímos explicações de terminologia e exemplos concretos ao longo do texto.

2. Diferenças entre EXPLAIN e EXPLAIN ANALYZE

O Papel e Uso Básico do EXPLAIN

O EXPLAIN do MySQL é uma ferramenta de análise usada para entender antecipadamente como uma instrução SQL (especialmente um SELECT) será executada. Ele permite confirmar planos de execução como uso de índices, ordem de joins e intervalos de busca.

Por exemplo:

EXPLAIN SELECT * FROM users WHERE age > 30;

Quando este comando é executado, o MySQL não executa realmente a consulta, mas exibe como ele planeja processá‑la em forma tabular. A saída inclui informações como o índice usado (key), método de acesso (type) e número estimado de linhas (rows).

O Papel e Recursos do EXPLAIN ANALYZE

Em contraste, o EXPLAIN ANALYZE, introduzido no MySQL 8.0.18, executa a consulta e exibe o plano de execução baseado em valores medidos realmente. Isso torna possível confirmar detalhes que não eram visíveis no EXPLAIN tradicional, como tempo de processamento real e número de linhas efetivamente processadas.

Exemplo:

EXPLAIN ANALYZE SELECT * FROM users WHERE age > 30;

Este comando executa a consulta e devolve uma saída que inclui:

• Tempo de execução para cada etapa do plano (ex.: 0.0022 sec)
• O número real de linhas lidas (rows)
• A estrutura de processamento (facilmente visualizada usando o formato TREE)

Resumo das Principais Diferenças

Qual Deles Você Deve Usar?

• Use EXPLAIN quando quiser verificar rapidamente a estrutura da consulta.
• Use EXPLAIN ANALYZE quando precisar de detalhes concretos sobre tempo de execução e custo da consulta.

Especialmente em cenários de ajuste de desempenho, o EXPLAIN ANALYZE permite otimizar com base em dados reais de execução, em vez de estimativas, tornando‑o uma ferramenta extremamente poderosa.

3. Formatos de Saída do EXPLAIN ANALYZE

Três Formatos de Saída: TRADITIONAL, JSON e TREE

O EXPLAIN ANALYZE do MySQL pode gerar resultados em diferentes formatos, dependendo do seu objetivo. No MySQL 8.0 e posteriores, os três formatos a seguir estão disponíveis.

Vamos analisar mais de perto as diferenças.

Formato TRADITIONAL (Padrão)

A saída TRADITIONAL é semelhante ao estilo clássico EXPLAIN e permite que você revise planos de execução em uma forma familiar. Se você executar EXPLAIN ANALYZE sem especificar um formato, o resultado geralmente é exibido neste formato.

Exemplo de saída (trecho):

-> Filter: (age > 30)  (cost=0.35 rows=10) (actual time=0.002..0.004 rows=8 loops=1)

• cost : custo estimado
• actual time : tempo medido
• rows : número estimado de linhas processadas (antes da execução)
• loops : contagem de loops (especialmente importante para JOIN)

O formato TRADITIONAL é fácil para humanos lerem e entenderem, tornando-o adequado para iniciantes e verificações rápidas.

Formato JSON

O formato JSON é mais detalhado e mais fácil de manipular programaticamente. A saída é estruturada, com cada nó representado como um objeto aninhado.

Comando:

EXPLAIN ANALYZE FORMAT=JSON SELECT * FROM users WHERE age > 30;

Parte da saída (formatada):

{
  "query_block": {
    "table": {
      "table_name": "users",
      "access_type": "range",
      "rows_examined_per_scan": 100,
      "actual_rows": 80,
      "filtered": 100,
      "cost_info": {
        "query_cost": "0.35"
      },
      "timing": {
        "start_time": 0.001,
        "end_time": 0.004
      }
    }
  }
}

Este formato é menos legível visualmente, mas é extremamente conveniente quando você deseja analisar os dados e alimentá-los em ferramentas de análise ou painéis.

Formato ÁRVORE (Legível e Ótimo para Visualizar Estrutura)

O formato ÁRVORE exibe a estrutura de execução da consulta como uma árvore, facilitando a compreensão da ordem de processamento de JOINs e subconsultas.

Comando:

EXPLAIN ANALYZE FORMAT=TREE SELECT * FROM users WHERE age > 30;

Exemplo de saída (simplificado):

-> Table scan on users  (actual time=0.002..0.004 rows=8 loops=1)

Para consultas complexas, o aninhamento pode aparecer assim:

-> Nested loop join
    -> Table scan on users
    -> Index lookup on orders using idx_user_id

O formato ÁRVORE é especialmente útil para consultas com muitos JOINs ou aninhamento complexo, onde você precisa compreender o fluxo de processamento.

Qual Formato Você Deve Usar?

Escolha o formato que melhor se adapta ao seu objetivo e revise o plano de execução no estilo mais legível e analisável.

4. Como Interpretar Planos de Execução

Por Que Você Precisa Ler Planos de Execução

O desempenho de consultas MySQL pode variar muito dependendo do volume de dados e da disponibilidade de índices. Ao interpretar corretamente a saída do plano de execução de EXPLAIN ANALYZE, você pode identificar objetivamente onde o trabalho está sendo desperdiçado e o que deve ser melhorado. Essa habilidade é um alicerce da otimização de desempenho, especialmente para consultas que lidam com grandes conjuntos de dados ou joins complexos.

Estrutura Básica de um Plano de Execução

A saída de EXPLAIN ANALYZE inclui informações como as seguintes (explicadas aqui com base na saída no estilo TRADITIONAL):

-> Filter: (age > 30)  (cost=0.35 rows=10) (actual time=0.002..0.004 rows=8 loops=1)

Esta única linha contém vários campos importantes.

Como Ler Campos Chave

1. cost vs. actual time

• cost é uma estimativa interna calculada pelo MySQL e é usada para avaliação relativa.
• actual time reflete o tempo real decorrido e é mais importante para a análise de desempenho.

Por exemplo:

(cost=0.35 rows=100) (actual time=0.002..0.004 rows=100)

Se as estimativas e medições coincidirem de perto, o plano de execução provavelmente está preciso. Se a diferença for grande, as estatísticas da tabela podem estar imprecisas.

2. rows vs. actual rows

• rows é o número de linhas que o MySQL prevê que lerá.
• actual rows é o número de linhas realmente lidas (incluído entre parênteses na saída no estilo TRADITIONAL).

Se houver uma grande discrepância, pode ser necessário atualizar as estatísticas ou reconsiderar o design dos índices.

3. loops

If loops=1, a etapa é executada uma vez. Com JOINs ou subconsultas, você pode ver loops=10 ou loops=1000. Quanto maior o valor, mais provável é que loops aninhados estejam causando processamento pesado.

Entenda a Estrutura Aninhada dos Planos de Execução

Quando várias tabelas são juntadas, o plano de execução é exibido como uma árvore (especialmente claro no formato ÁRVORE).

Exemplo:

-> Nested loop join
    -> Table scan on users
    -> Table scan on orders

Problema

• Ambas as tabelas são totalmente escaneadas, resultando em um alto custo de junção.

Contramedida

• Adicione um índice em users.age e filtre mais cedo para reduzir a carga da junção.

Como Identificar Gargalos de Desempenho

Focar nos seguintes pontos facilita a identificação de gargalos:

• Nós com tempo real longo e muitas linhas : Consomem a maior parte do tempo de execução
• Locais onde ocorre uma varredura completa da tabela : Provavelmente índices ausentes ou não utilizados
• Etapas com muitos loops : Indicam ordem de JOIN ineficiente ou aninhamento
• Grandes diferenças entre linhas estimadas e reais : Sugere estatísticas imprecisas ou acesso excessivo a dados

Use esses insights como base para as técnicas de “Otimização de Consultas” apresentadas na próxima seção.

5. Exemplos Práticos de Otimização de Consultas

O Que é Otimização de Consultas?

Otimização de consultas refere-se à revisão e melhoria de instruções SQL para que possam ser executadas de forma mais eficiente. Com base em como o MySQL processa consultas internamente (planos de execução), você aplica melhorias como adicionar índices, ajustar a ordem dos joins e eliminar processamento desnecessário.

Aqui, demonstramos como melhorar consultas usando EXPLAIN ANALYZE com exemplos concretos.

Exemplo 1: Melhoria de Velocidade Usando Índices

Antes da Otimização

SELECT * FROM users WHERE email = 'example@example.com';

Plano de Execução (Extrato)

-> Table scan on users  (cost=10.5 rows=100000) (actual time=0.001..0.230 rows=1 loops=1)

Problema

• A saída mostra Table scan, o que significa que uma varredura completa da tabela está sendo realizada. Com grandes conjuntos de dados, isso leva a atrasos significativos.

Solução: Adicionar um Índice

CREATE INDEX idx_email ON users(email);

Plano de Execução Após a Otimização

-> Index lookup on users using idx_email  (cost=0.1 rows=1) (actual time=0.001..0.002 rows=1 loops=1)

Resultado

• Tempo de execução significativamente reduzido.
• Varredura completa da tabela evitada ao usar o índice.

Exemplo 2: Otimizando a Ordem dos Joins

Antes da Otimização

SELECT * FROM orders
JOIN users ON orders.user_id = users.id
WHERE users.age > 30;

Plano de Execução (Extrato)

-> Nested loop join
    -> Table scan on orders
    -> Table scan on users

Problema

• Ambas as tabelas são totalmente escaneadas, resultando em altos custos de junção.

Solução

• Adicione um índice em users.age e filtre primeiro para reduzir o tamanho do alvo da junção.

  CREATE INDEX idx_age ON users(age);
  

Plano de Execução Após a Otimização

-> Nested loop join
    -> Index range scan on users using idx_age
    -> Index lookup on orders using idx_user_id

Resultado

• Os alvos do JOIN são filtrados primeiro, reduzindo a carga de processamento geral.

Exemplo 3: Revisando uma Subconsulta

Antes da Otimização

SELECT * FROM users WHERE id IN (SELECT user_id FROM orders WHERE total > 1000);

Problema

• A subconsulta pode ser avaliada repetidamente, degradando o desempenho.

Solução: Reescrever como um JOIN

SELECT DISTINCT users.*
FROM users
JOIN orders ON users.id = orders.user_id
WHERE orders.total > 1000;

Resultado

• O plano de execução é otimizado para processamento de JOIN, e os índices têm maior probabilidade de serem usados.

A Importância da Comparação Antes/Depois

Usando EXPLAIN ANALYZE, você pode verificar os resultados da otimização com valores medidos realmente. Ao comparar o tempo de execução e a contagem de linhas antes e depois das melhorias, garante que os esforços de ajuste sejam baseados em ganhos de desempenho reais, e não em suposições.

Considerações Importantes na Otimização

• Adicionar índices demais pode ser contraproducente (desempenho mais lento em INSERT/UPDATE).
• Planos de execução dependem do volume de dados e das estatísticas, portanto a validação é necessária em cada ambiente.
• Uma otimização raramente resolve tudo. A análise de gargalos vem primeiro.

6. Precauções e Melhores Práticas

Observações Importantes ao Usar EXPLAIN ANALYZE

Embora EXPLAIN ANALYZE seja extremamente poderoso, o uso inadequado pode gerar mal‑entendidos ou até riscos operacionais. Manter os pontos a seguir em mente garante uma análise de consultas segura e eficaz.

1. Evite Executar Descuidado em Produção

Como EXPLAIN ANALYZE realmente executa a consulta, usá‑lo por engano com instruções de modificação (INSERT/UPDATE/DELETE) pode alterar dados.

• Em geral, use‑o apenas com instruções SELECT.
• Prefira executá‑lo em um ambiente de staging ou teste, e não em produção.

2. Considere o Impacto do Cache

O MySQL pode devolver resultados do cache se a mesma consulta for executada repetidamente. Como consequência, o tempo de execução relatado por EXPLAIN ANALYZE pode diferir do comportamento real em produção.

Contramedidas:

• Limpe o cache antes da execução (RESET QUERY CACHE;).
• Execute várias vezes e avalie com base nos valores médios.

3. Mantenha as Estatísticas Atualizadas

O MySQL gera planos de execução com base nas estatísticas de tabelas e índices. Se as estatísticas estiverem desatualizadas, tanto EXPLAIN quanto EXPLAIN ANALYZE podem fornecer informações enganosas.

Após grandes operações de INSERT ou DELETE, atualize as estatísticas usando ANALYZE TABLE.

ANALYZE TABLE users;

4. Índices Não São Solução Mágica

Embora os índices frequentemente melhorem o desempenho, índices demais retardam as operações de gravação.

Escolher entre índices compostos e índices de coluna única também é importante. Projete os índices cuidadosamente com base nos padrões de consulta e na frequência de uso.

5. Não Julgue Apenas pelo Tempo de Execução

Os resultados de EXPLAIN ANALYZE refletem apenas o desempenho de uma única consulta. Em aplicações reais, a latência de rede ou o processamento no back‑end podem ser o verdadeiro gargalo.

Portanto, analise as consultas dentro do contexto da arquitetura completa do sistema.

Resumo das Melhores Práticas

7. Perguntas Frequentes (FAQ)

Q1. A partir de qual versão o EXPLAIN ANALYZE está disponível?

A.
O EXPLAIN ANALYZE do MySQL foi introduzido na versão 8.0.18 ou posterior. Não é suportado em versões anteriores à 8.0, portanto verifique a versão do seu MySQL antes de utilizá‑lo.

Q2. Executar EXPLAIN ANALYZE pode modificar dados?

A.
EXPLAIN ANALYZE executa a consulta internamente.
Quando usado com uma instrução SELECT, não modifica dados.

Portanto, quando usado com SELECT, não modifica dados.

Entretanto, se for usado por engano com INSERT, UPDATE ou DELETE, os dados serão modificados assim como em uma consulta normal.

Por segurança, recomenda‑se executar as análises em um banco de teste ou staging, e não em produção.

Q3. O EXPLAIN sozinho não seria suficiente?

A.
EXPLAIN basta para revisar o plano de execução “estimado”. Contudo, ele não fornece valores medidos, como tempo de execução real ou contagem real de linhas.

Se precisar de ajuste sério de consultas ou quiser validar os efeitos da otimização, EXPLAIN ANALYZE é mais útil.

Q4. Quão precisos são valores como “loops” e “actual time”?

A.
Valores como actual time e loops são métricas reais de execução medidas internamente pelo MySQL. Contudo, podem variar ligeiramente dependendo das condições do SO, do estado do cache e da carga do servidor.

Por esse motivo, não confie em uma única medição. Em vez disso, execute a consulta várias vezes e avalie as tendências.

Q5. O que exatamente representa o “custo”?

A.
cost é um valor estimado calculado pelo modelo interno de custo do MySQL. Ele reflete uma avaliação relativa dos custos de CPU e I/O. Não é expresso em segundos.

Por exemplo, se você vir (cost=0.3) e (cost=2.5), este último é estimado como mais caro em termos relativos.

Q6. Quais são os benefícios de usar o formato JSON ou TREE?

A.

• Formato JSON: Saída estruturada que é fácil de analisar programaticamente. Útil para ferramentas de automação e painéis.
• Formato TREE: Torna o fluxo de execução e o aninhamento visualmente claros. Ideal para entender consultas complexas e a ordem de JOINs.

Escolha o formato que melhor se adapta ao seu objetivo.

Q7. O que devo fazer se não conseguir melhorar o desempenho após revisar o plano de execução?

A.
Considere abordagens adicionais, como:

• Redesenhar índices (índices compostos ou índices de cobertura)
• Reescrever consultas (subconsultas → JOINs, removendo colunas SELECT desnecessárias)
• Usar views ou tabelas temporárias
• Revisar a configuração do MySQL (tamanhos de buffers, alocação de memória, etc.)

A otimização de desempenho raramente tem sucesso com uma única técnica. Uma abordagem abrangente e iterativa é essencial.