Como você otimiza consultas SQL lentas?

Question

Accepted Answer

A otimização de consultas lentas começa com **analisar o plano de execução** (`EXPLAIN`) para encontrar o gargalo, depois aplicar correções direcionadas — mais comumente **adicionar índices**, reescrever a consulta e evitar anti-padrões comuns. O maior ganho geralmente é transformar varreduras completas de tabelas em buscas por índice.

## Passo 1: analisar com EXPLAIN

```sql
EXPLAIN ANALYZE
SELECT * FROM orders WHERE customer_id = 5;
-- shows the EXECUTION PLAN: scan type (Seq Scan = full scan, Index Scan = using an index),
-- estimated/actual rows, cost, and timing → reveals WHERE the time goes
```

`EXPLAIN` (ou `EXPLAIN ANALYZE`) mostra como o banco de dados executa a consulta — crucialmente, se está fazendo uma **varredura completa de tabela** (lenta) ou usando um **índice** (rápido). Sempre faça o perfil antes de otimizar.

## O maior ganho: adicionar índices

```sql
-- if EXPLAIN shows a Seq Scan on a filtered/joined column → add an index
CREATE INDEX idx_orders_customer ON orders(customer_id);
-- turns a full scan into a fast index lookup
```

A correção mais comum e impactante: **adicionar índices** em colunas usadas em `WHERE`, `JOIN` e `ORDER BY`. Isso transforma varreduras completas de tabelas em buscas rápidas.

## Anti-padrões comuns para corrigir

```sql
-- ❌ functions on a column prevent index use
WHERE UPPER(email) = 'X@Y.COM'        -- can't use an index on email
WHERE email = 'x@y.com'               -- ✅ can use the index

-- ❌ leading wildcard LIKE can't use an index
WHERE name LIKE '%smith'              -- ✅ 'smith%' (prefix) can use an index

-- ❌ SELECT * fetches unneeded columns
SELECT * → SELECT only_needed_columns  -- less data, may use a covering index

-- ❌ N+1 queries (querying in a loop) → use a single JOIN instead
```

## Outras técnicas de otimização

```text
✓ Fix N+1 queries (one query per row) → use JOINs / batch into one query
✓ Add appropriate indexes (including COMPOSITE for multi-column filters)
✓ Reduce data scanned — filter early, select only needed columns, paginate
✓ Rewrite subqueries as JOINs where more efficient
✓ Use EXISTS instead of IN for existence checks on large subqueries
✓ Keep statistics updated (ANALYZE) so the planner makes good choices
✓ Consider denormalization / materialized views for expensive repeated queries
✓ Watch for missing indexes on FOREIGN KEYS (joins scan without them)
```

## Por que isso importa

A otimização de consultas é conhecimento crítico para o desempenho do banco de dados — consultas lentas são uma das principais causas de desempenho deficiente da aplicação, e entender como encontrá-las e corrigi-las é uma habilidade essencial de nível sênior para qualquer pessoa que trabalhe com bancos de dados em escala.

A disciplina fundamental é **analisar com `EXPLAIN`** (examinando o plano de execução para encontrar o gargalo real — crucialmente se a consulta faz uma varredura completa de tabela lenta ou uma busca rápida por índice) antes de otimizar, em vez de adivinhar — essa abordagem orientada por medição é o que torna a otimização eficaz.

O **ganho único maior e mais comum é adicionar índices** em colunas filtradas, unidas e ordenadas (transformando varreduras completas de tabelas em buscas rápidas — frequentemente melhorando o tempo de consulta por ordens de magnitude), tornando o conhecimento de índices central para a otimização.

Compreender **anti-padrões comuns** é igualmente importante: funções em colunas e `LIKE` com curinga no início impedem o uso de índice, `SELECT *` busca dados desnecessários, e especialmente **consultas N+1** (consultar em um loop em vez de um único `JOIN` — um assassino de desempenho frequente e grave no código da aplicação) — reconhecer e corrigir esses problemas aborda as causas mais comuns de consultas lentas.

Outras técnicas (índices compostos, redução de dados verificados, reescrever subconsultas como `JOIN`s, manter estatísticas atualizadas, visualizações materializadas para consultas caras) completam o conjunto de ferramentas.

Como o desempenho de consulta afeta diretamente a responsividade e escalabilidade da aplicação, e como a abordagem sistemática (analisar com `EXPLAIN`, depois corrigir — geralmente adicionando índices e eliminando anti-padrões como N+1) é o que mantém os bancos de dados rápidos sob carga, dominar a otimização de consultas é conhecimento sênior importante e de alto valor que distingue desenvolvedores que podem construir aplicações com bancos de dados de desempenho escalável — uma habilidade crítica, frequentemente relevante e um tópico de entrevista comum para desempenho de banco de dados.