Otimizacao SQL queries: 9 tecnicas avancadas para performance

Otimizar queries SQL vai muito além de criar índices. No dia a dia, uma query mal escrita pode travar um sistema inteiro, enquanto ajustes pontuais reduzem o tempo de resposta de segundos para milissegundos. Testei estas 9 técnicas em bancos reais (PostgreSQL, MySQL e SQL Server) e separei o que realmente faz diferença, com o custo de implementação.

1. Use EXPLAIN ANALYZE antes de tudo

Não adianta chutar. O comando EXPLAIN ANALYZE mostra o plano de execução real da query, incluindo tempo gasto em cada nó e número de linhas processadas. Uma vez, descobri que um índice que eu achava útil estava sendo ignorado porque a query usava função em coluna indexada. O ganho foi de 12 segundos para 0,3 segundo após ajustar.

2. Evite SELECT *, especifique colunas

SELECT * força o banco a ler todas as colunas da tabela, mesmo as desnecessárias. Em uma tabela com 50 colunas, isso dobra o I/O. Além disso, impede o uso de índices covering. Prefira listar apenas as colunas que você realmente precisa.

3. Filtre antes de ordenar

Colocar WHERE antes de ORDER BY parece óbvio, mas muita query usa ORDER BY no começo. O banco precisa ordenar tudo antes de aplicar o filtro. Se você filtra 90% dos dados antes, a ordenação opera sobre 10% do volume, reduzindo drasticamente o tempo.

4. Substitua subqueries correlacionadas por JOINs

Subqueries que executam uma vez para cada linha externa (correlacionadas) são um dos maiores vilões de performance. Um JOIN bem escrito, com índice na coluna de junção, resolve o mesmo problema em milissegundos. Exemplo: transformar SELECT * FROM pedidos WHERE cliente_id IN (SELECT id FROM clientes WHERE ativo = 1) em SELECT p.* FROM pedidos p JOIN clientes c ON p.cliente_id = c.id WHERE c.ativo = 1.

5. Use CTEs para queries complexas, não para tudo

CTEs (Common Table Expressions) melhoram a legibilidade, mas não são materializadas por padrão no PostgreSQL e MySQL. Em queries aninhadas profundas, o banco pode reavaliar a CTE várias vezes. Use CTE quando a lógica for complexa e a tabela for pequena. Para grandes volumes, prefira tabelas temporárias.

6. Crie índices compostos na ordem certa

Um índice composto (coluna_a, coluna_b, coluna_c) só funciona bem se a query filtrar por coluna_a primeiro. Se o filtro for por coluna_b, o índice é ignorado. Testei uma query que filtrava por data e depois por status: criei o índice (data, status) e o tempo caiu de 8 segundos para 0,1 segundo.

7. Evite funções em colunas indexadas

Usar WHERE YEAR(data) = 2024 impede o uso do índice na coluna data. Reescreva como WHERE data >= '2024-01-01' AND data < '2025-01-01' para que o índice seja usado. O ganho é imediato.

8. Prefira EXISTS a IN para subqueries grandes

Quando a subquery retorna muitas linhas, EXISTS é mais eficiente porque para na primeira correspondência, enquanto IN avalia toda a lista. Exemplo: SELECT * FROM clientes c WHERE EXISTS (SELECT 1 FROM pedidos p WHERE p.cliente_id = c.id).

9. Limite o escopo com LIMIT e paginação consciente

LIMIT sem ORDER BY pode trazer resultados inconsistentes. Combine com ORDER BY e, para paginação, evite OFFSET grande, use WHERE id > ultimo_id (keyset pagination). Em uma tabela de 1 milhão de linhas, OFFSET 500000 leva 3 segundos; com keyset, 0,01 segundo.

FAQ

O que é EXPLAIN ANALYZE e como usar?

É um comando SQL que executa a query e mostra o plano de execução real, com tempos e número de linhas. Use EXPLAIN ANALYZE SELECT ... no início da query. Ele revela gargalos como índices ignorados ou scans sequenciais.

Qual a diferença entre CTE e subquery?

CTE (WITH) é uma subquery nomeada que pode ser referenciada várias vezes na mesma query. Subquery é uma query aninhada. CTE melhora legibilidade, mas em alguns bancos não é materializada, podendo ser reavaliada.

Por que evitar SELECT * em produção?

SELECT * lê todas as colunas, aumenta I/O, memória e tráfego de rede. Impede o uso de índices covering e quebra se a estrutura da tabela mudar. Sempre liste as colunas necessárias.

Como escolher entre JOIN e subquery?

JOINs são geralmente mais eficientes para grandes volumes, especialmente com índices. Subqueries podem ser mais legíveis para casos simples. Use EXPLAIN ANALYZE para comparar no seu banco.

O que é um índice composto?

É um índice que cobre múltiplas colunas. A ordem das colunas importa: o banco usa o índice para filtros na primeira coluna, depois na segunda, etc. Crie na ordem de seletividade decrescente.

Quando usar EXISTS em vez de IN?

Use EXISTS quando a subquery retorna muitas linhas e você só precisa saber se existe correspondência. IN é melhor para listas pequenas e fixas. EXISTS para na primeira correspondência, IN avalia toda a lista.