Otimizando Consultas MySQL: Um Guia Prático

Consultas MySQL lentas raramente são misteriosas quando você olha o plano de execução. A parte difícil não é saber que índices importam. A parte difícil é provar qual consulta é lenta, entender por que o MySQL escolheu um plano e alterar a consulta ou o índice sem piorar as escritas, o armazenamento ou outras consultas.

Comece com evidências. Use o log de consultas lentas, o Performance Schema, rastreamentos de aplicação ou uma ferramenta de monitoramento como o PMM para encontrar consultas que realmente prejudicam os usuários. Em seguida, use EXPLAIN e, quando seguro, EXPLAIN ANALYZE para ver o que o MySQL está fazendo.

Entendendo o Desempenho de Consultas

Causas comuns incluem:

Índices Ausentes ou Ineficazes: Sem índices apropriados, o MySQL precisa realizar varreduras completas de tabela, que são muito ineficientes para tabelas grandes.
SQL Mal Escrito: Filtros não sargáveis, SELECT * desnecessário, junções cruzadas acidentais e condições de junção ineficientes podem degradar o desempenho.
Grandes Conjuntos de Dados: Mais dados significam mais páginas para ler, ordenar, agrupar e armazenar em cache.
Hardware e Configuração: Configuração de servidor abaixo do ideal ou recursos de hardware insuficientes também podem desempenhar um papel, embora este guia se concentre na otimização no nível da consulta.

O Poder do `EXPLAIN`

EXPLAIN é a primeira ferramenta a ser usada quando você quer entender como o MySQL planeja uma consulta. Para um EXPLAIN SELECT simples, o MySQL mostra o plano escolhido pelo otimizador sem retornar o conjunto de resultados. EXPLAIN ANALYZE executa a consulta e relata o tempo real, portanto, use-o com cuidado em sistemas de produção.

Como Usar `EXPLAIN`

Para uma consulta de leitura, prefixe com EXPLAIN:

EXPLAIN SELECT * FROM usuarios WHERE nome_usuario = 'joao_silva';

Interpretando a Saída do `EXPLAIN`

A saída do EXPLAIN é uma tabela com várias colunas importantes:

id: O número de sequência do SELECT dentro da consulta. Números mais altos geralmente são executados primeiro.
select_type: O tipo de SELECT (por exemplo, SIMPLE, PRIMARY, SUBQUERY, DERIVED).
table: A tabela sendo acessada.
partitions: As partições usadas (se o particionamento estiver habilitado).
type: O tipo de junção. Esta é uma das colunas mais úteis. Procure por const, eq_ref, ref ou range quando a forma da consulta permitir. Desconfie de index e especialmente ALL em tabelas grandes.
possible_keys: Mostra quais índices o MySQL poderia usar.
key: O índice que o MySQL realmente escolheu usar.
key_len: O comprimento da parte do índice que o MySQL espera usar. Mais curto não é automaticamente melhor; depende da seletividade e da consulta.
ref: A coluna ou constante comparada ao índice (key).
rows: Uma estimativa do número de linhas que o MySQL espera examinar.
filtered: A porcentagem de linhas filtradas pela condição da tabela.
Extra: Contém informações adicionais sobre como o MySQL resolve a consulta. Valores-chave a serem observados incluem:
- Using where: Indica que o MySQL aplica uma condição ao processar linhas. É comum e nem sempre ruim.
- Using index: Significa que a consulta é coberta por um índice (todas as colunas necessárias estão no índice), o que é bom.
- Using temporary: O MySQL precisa criar uma tabela temporária, geralmente para operações GROUP BY ou ORDER BY. Isso pode ser lento.
- Using filesort: O MySQL precisa fazer uma ordenação externa (não usando um índice para ordenação). Isso geralmente é um sinal de uma cláusula ORDER BY ineficiente.

Identificando Gargalos com `EXPLAIN`

Vamos ver alguns cenários comuns e como o EXPLAIN ajuda a identificar problemas:

Cenário 1: Varredura Completa de Tabela

Considere uma consulta como:

SELECT * FROM pedidos WHERE data_pedido = '2023-10-26';

Se a coluna data_pedido não estiver indexada, o EXPLAIN pode mostrar:

+----+-------------+--------+------+---------------+------+---------+------+---------+-------------+
| id | select_type | table  | type | possible_keys | key  | key_len | ref  | rows    | Extra       |
+----+-------------+--------+------+---------------+------+---------+------+---------+-------------+
|  1 | SIMPLE      | pedidos| ALL  | NULL          | NULL | NULL    | NULL | 1000000 | Using where |
+----+-------------+--------+------+---------------+------+---------+------+---------+-------------+

Problema: type: ALL indica uma varredura completa de tabela. rows: 1000000 mostra que o MySQL precisa examinar cada linha na tabela pedidos. key: NULL significa que nenhum índice foi usado.

Solução: Adicione um índice à coluna data_pedido:

CREATE INDEX idx_data_pedido ON pedidos (data_pedido);

Após adicionar o índice, execute novamente o EXPLAIN. Você deve ver um tipo de acesso mais seletivo, como ref ou range, e a contagem estimada de linhas deve cair se o filtro de data for seletivo.

Cenário 2: `ORDER BY` ou `GROUP BY` Ineficiente

SELECT cliente_id, COUNT(*) FROM pedidos GROUP BY cliente_id ORDER BY cliente_id;

Se cliente_id não estiver indexado, o EXPLAIN pode mostrar:

+----+-------------+--------+-------+---------------+------+---------+------+--------+----------------------------------+
| id | select_type | table  | type  | possible_keys | key  | key_len | ref  | rows   | Extra                            |
+----+-------------+--------+-------+---------------+------+---------+------+--------+----------------------------------+
|  1 | SIMPLE      | pedidos| index | NULL          | NULL | NULL    | NULL | 100000 | Using temporary; Using filesort |
+----+-------------+--------+-------+---------------+------+---------+------+--------+----------------------------------+

Problema: Using temporary e Using filesort indicam que o MySQL está realizando operações custosas para ordenar e agrupar os dados. Isso geralmente ocorre porque nenhum índice pode satisfazer tanto os requisitos de agrupamento quanto de ordenação de forma eficiente.

Solução: Para esta consulta específica, um índice em (cliente_id) pode permitir que o MySQL varra as linhas na ordem de agrupamento. Se a consulta real filtrar primeiro por data, status ou locatário, um índice composto pode ser melhor, como (locatario_id, status, cliente_id).

CREATE INDEX idx_cliente_id ON pedidos (cliente_id);

Cenário 3: Usando `SELECT *` Desnecessariamente

Quando você seleciona todas as colunas (*) mas precisa apenas de algumas, você transfere mais dados e pode impedir que um índice de cobertura seja útil. Isso é especialmente perceptível em tabelas largas com colunas JSON, blobs de texto ou muitos campos anuláveis.

-- Suponha um índice em 'status'
SELECT * FROM tarefas WHERE status = 'pendente';

EXPLAIN pode mostrar Using where, mas se a consulta exigir colunas que não estão no índice usado para filtragem, ela ainda precisará acessar os dados da tabela.

Solução: Especifique apenas as colunas necessárias:

SELECT tarefa_id, descricao FROM tarefas WHERE status = 'pendente';

Se você consultar frequentemente esta forma exata, considere um índice de cobertura que inclua a coluna de filtro e as colunas retornadas:

CREATE INDEX idx_tarefas_status_id_descricao
  ON tarefas (status, tarefa_id, descricao);

Não crie índices de cobertura para cada consulta. Eles aceleram as leituras ao custo de armazenamento e sobrecarga de escrita.

Reescrevendo Consultas Lentas

Além da indexação, a forma como você estrutura o SQL pode alterar a quantidade de trabalho que o MySQL precisa fazer.

Evite Subconsultas Correlacionadas

Subconsultas correlacionadas podem ser executadas uma vez para cada linha processada pela consulta externa. O MySQL pode otimizar algumas delas, mas se EXPLAIN mostrar buscas dependentes repetidas, uma junção ou tabela derivada geralmente é mais clara e rápida.

Frequentemente ineficiente:

SELECT p.pedido_id, p.data_pedido
FROM pedidos p
WHERE p.cliente_id IN (
    SELECT c.cliente_id
    FROM clientes c
    WHERE c.pais = 'Brasil'
);

Frequentemente melhor como uma junção:

SELECT p.pedido_id, p.data_pedido
FROM pedidos p
JOIN clientes c ON p.cliente_id = c.cliente_id
WHERE c.pais = 'Brasil';

Use EXPLAIN em ambas as versões. A junção não é automaticamente mais rápida em todos os esquemas, mas é mais fácil para muitas equipes raciocinarem e indexarem.

Otimize Cláusulas `LIKE`

Curingas iniciais (%) em cláusulas LIKE geralmente impedem que um índice B-tree normal seja usado para uma busca por intervalo.

Ineficiente:

SELECT * FROM produtos WHERE nome_produto LIKE '%widget';

Melhor (se possível):

SELECT * FROM produtos WHERE nome_produto LIKE 'widget%';

Se você precisar de correspondência do tipo "contém", considere índices de texto completo do MySQL para pesquisa de texto adequada, abordagens n-gram para idiomas específicos ou um mecanismo de busca quando relevância e correspondência flexível forem importantes.

Use `UNION ALL` em Vez de `UNION` Quando Possível

UNION remove linhas duplicadas, o que requer uma etapa extra de ordenação e deduplicação. Se você sabe que não há duplicatas ou não precisa removê-las, UNION ALL é mais rápido.

Lento:

SELECT nome FROM tabela1
UNION
SELECT nome FROM tabela2;

Rápido:

SELECT nome FROM tabela1
UNION ALL
SELECT nome FROM tabela2;

Outras Dicas de Otimização

Mantenha as Estatísticas Atualizadas: Certifique-se de que as estatísticas da tabela estejam atualizadas para que o otimizador de consultas possa tomar decisões informadas. Isso geralmente é tratado automaticamente, mas pode ser atualizado manualmente com ANALYZE TABLE.
Configuração do Servidor: O ajuste de consultas não compensará um pool de buffer InnoDB minúsculo ou discos sobrecarregados. No MySQL 8.0, o cache de consultas antigo foi removido, portanto, não planeje novos ajustes em torno de query_cache_size.
Monitoramento Regular: Use ferramentas como MySQL Enterprise Monitor, Percona Monitoring and Management (PMM) ou visualizações integradas do performance schema para rastrear consultas lentas e identificar tendências.

Um Fluxo de Trabalho Prático de Ajuste

Para sistemas de produção, ajuste a partir da consulta lenta para fora:

Capture o SQL exato, valores vinculados, contagens de linhas e tempo.
Execute EXPLAIN FORMAT=TREE ou EXPLAIN FORMAT=JSON se sua versão do MySQL suportar.
Verifique se o índice selecionado corresponde ao padrão de filtro e junção.
Teste uma reescrita de consulta ou alteração de índice em dados realistas.
Compare linhas examinadas, tabelas temporárias, comportamento de ordenação e latência de relógio de parede.

Isso evita que você adicione índices porque uma consulta "parece lenta". Índices têm um custo. Cada inserção, atualização e exclusão deve mantê-los. Uma tabela com dez índices sobrepostos pode se tornar mais lenta no geral, mesmo que uma consulta de leitura melhore.

Para uma consulta comum de aplicação multilocatário, a ordem do índice geralmente importa mais do que o número de colunas indexadas:

SELECT id, criado_em, total
FROM pedidos
WHERE locatario_id = 42
  AND status = 'pago'
  AND criado_em >= '2025-01-01'
ORDER BY criado_em DESC
LIMIT 50;

Um índice útil pode ser:

CREATE INDEX idx_pedidos_locatario_status_criado
  ON pedidos (locatario_id, status, criado_em DESC);

Esse índice começa com filtros de igualdade, depois suporta o intervalo de data e a ordenação. Se você colocar criado_em primeiro, o MySQL pode varrer muitos locatários antes de encontrar o correto. Se você omitir status, a consulta ainda pode funcionar, mas examinará muitas linhas extras.

Cuidado com Filtros Não Sargáveis

Uma condição é sargável quando o MySQL pode usar um índice para pesquisar linhas correspondentes. Encapsular uma coluna indexada em uma função geralmente quebra isso:

-- Mais difícil usar um índice em criado_em
SELECT * FROM pedidos
WHERE DATE(criado_em) = '2025-01-15';

Reescreva como um intervalo:

SELECT *
FROM pedidos
WHERE criado_em >= '2025-01-15'
  AND criado_em <  '2025-01-16';

A segunda versão permite que o MySQL busque em um índice em criado_em. A mesma ideia se aplica a LOWER(email), matemática em colunas numéricas e conversões implícitas de tipo. Se a coluna estiver indexada, mantenha o lado da coluna da comparação limpo quando possível.

Tenha Cuidado com Paginação

A paginação por deslocamento fica cara em páginas profundas:

SELECT id, titulo
FROM posts
WHERE status = 'publicado'
ORDER BY publicado_em DESC
LIMIT 20 OFFSET 200000;

O MySQL ainda precisa percorrer as linhas anteriores antes de retornar a página solicitada. Para feeds, logs de auditoria e tabelas administrativas, a paginação por conjunto de chaves geralmente é melhor:

SELECT id, titulo, publicado_em
FROM posts
WHERE status = 'publicado'
  AND (publicado_em, id) < ('2025-05-01 12:00:00', 987654)
ORDER BY publicado_em DESC, id DESC
LIMIT 20;

Combine com um índice como (status, publicado_em, id). Isso altera um pouco o comportamento do produto porque os usuários se movem através de um cursor em vez de pular para a página 10.000, mas pode transformar uma consulta dolorosa em uma previsível.

Valide com Dados Reais

Pequenos bancos de dados de teste mentem. Uma consulta instantânea em 20.000 linhas pode ser terrível em 200 milhões de linhas, especialmente quando a distribuição de dados é distorcida. Teste contra volume e cardinalidade semelhantes aos de produção quando possível. Se você não puder copiar os dados de produção, pelo menos gere dados com tamanhos de locatário, distribuição de status e intervalos de datas semelhantes.

Um último hábito ajuda: mantenha o plano antigo e o novo plano no ticket. O seu eu futuro vai querer saber por que um índice existe.

O melhor hábito de ajuste do MySQL é fazer cada mudança ganhar seu lugar. Capture a consulta lenta, inspecione o plano, altere uma consulta ou índice e, em seguida, compare a latência e as linhas examinadas. Um plano EXPLAIN limpo é útil, mas a verdadeira vitória é a latência de produção mais baixa sem criar nova pressão de escrita ou inchaço de armazenamento.