Padrões de Otimização de SQL

Transforme consultas lentas de banco de dados em operações extremamente rápidas através de otimização sistemática, indexação adequada e análise de plano de consulta.

Quando Usar Esta Habilidade

• Depuração de consultas de execução lenta
• Projeto de esquemas de banco de dados de alto desempenho
• Otimização de tempos de resposta de aplicativos
• Redução de carga e custos do banco de dados
• Melhoria da escalabilidade para conjuntos de dados em crescimento
• Análise de planos de consulta EXPLAIN
• Implementação de índices eficientes
• Resolução de problemas de consulta N+1

Conceitos Principais

1. Planos de Execução de Consulta (EXPLAIN)

Entender a saída do EXPLAIN é fundamental para a otimização.

EXPLAIN do PostgreSQL:

-- Basic explain
EXPLAIN SELECT * FROM users WHERE email = 'user@example.com';

-- With actual execution stats
EXPLAIN ANALYZE
SELECT * FROM users WHERE email = 'user@example.com';

-- Verbose output with more details
EXPLAIN (ANALYZE, BUFFERS, VERBOSE)
SELECT u.*, o.order_total
FROM users u
JOIN orders o ON u.id = o.user_id
WHERE u.created_at > NOW() - INTERVAL '30 days';

Métricas Chave a Observar:

• Seq Scan: Varredura completa da tabela (geralmente lenta para tabelas grandes)
• Index Scan: Uso de índice (bom)
• Index Only Scan: Uso de índice sem acessar a tabela (melhor)
• Nested Loop: Método de junção (ok para pequenos conjuntos de dados)
• Hash Join: Método de junção (bom para conjuntos de dados maiores)
• Merge Join: Método de junção (bom para dados ordenados)
• Cost: Custo estimado da consulta (menor é melhor)
• Rows: Linhas estimadas retornadas
• Actual Time: Tempo real de execução

2. Estratégias de Indexação

Índices são a ferramenta de otimização mais poderosa.

Tipos de Índice:

• B-Tree: Padrão, bom para consultas de igualdade e de intervalo
• Hash: Apenas para comparações de igualdade (=)
• GIN: Busca de texto completo, consultas de array, JSONB
• GiST: Dados geométricos, busca de texto completo
• BRIN: Block Range INdex para tabelas muito grandes com correlação

-- Standard B-Tree index
CREATE INDEX idx_users_email ON users(email);

-- Composite index (order matters!)
CREATE INDEX idx_orders_user_status ON orders(user_id, status);

-- Partial index (index subset of rows)
CREATE INDEX idx_active_users ON users(email)
WHERE status = 'active';

-- Expression index
CREATE INDEX idx_users_lower_email ON users(LOWER(email));

-- Covering index (include additional columns)
CREATE INDEX idx_users_email_covering ON users(email)
INCLUDE (name, created_at);

-- Full-text search index
CREATE INDEX idx_posts_search ON posts
USING GIN(to_tsvector('english', title || ' ' || body));

-- JSONB index
CREATE INDEX idx_metadata ON events USING GIN(metadata);

3. Padrões de Otimização de Consulta

Evite SELECT *:

-- Bad: Fetches unnecessary columns
SELECT * FROM users WHERE id = 123;

-- Good: Fetch only what you need
SELECT id, email, name FROM users WHERE id = 123;

Use a Cláusula WHERE de Forma Eficiente:

-- Bad: Function prevents index usage
SELECT * FROM users WHERE LOWER(email) = 'user@example.com';

-- Good: Create functional index or use exact match
CREATE INDEX idx_users_email_lower ON users(LOWER(email));
-- Then:
SELECT * FROM users WHERE LOWER(email) = 'user@example.com';

-- Or store normalized data
SELECT * FROM users WHERE email = 'user@example.com';

Otimize JOINs:

-- Bad: Cartesian product then filter
SELECT u.name, o.total
FROM users u, orders o
WHERE u.id = o.user_id AND u.created_at > '2024-01-01';

-- Good: Filter before join
SELECT u.name, o.total
FROM users u
JOIN orders o ON u.id = o.user_id
WHERE u.created_at > '2024-01-01';

-- Better: Filter both tables
SELECT u.name, o.total
FROM (SELECT * FROM users WHERE created_at > '2024-01-01') u
JOIN orders o ON u.id = o.user_id;

Padrões Detalhados e Exemplos Práticos

A documentação detalhada dos padrões está em references/details.md. Leia esse arquivo quando o nível de navegação acima for insuficiente.

Melhores Práticas

1. Indexe Seletivamente: Muitos índices tornam as escritas lentas
2. Monitore o Desempenho das Consultas: Use logs de consultas lentas
3. Mantenha as Estatísticas Atualizadas: Execute ANALYZE regularmente
4. Use Tipos de Dados Apropriados: Tipos menores = melhor desempenho
5. Normalize com Cuidado: Equilibre normalização vs. desempenho
6. Cacheie Dados Acessados Frequentemente: Use cache em nível de aplicativo
7. Pool de Conexões: Reutilize conexões de banco de dados
8. Manutenção Regular: VACUUM, ANALYZE, reconstrua índices

-- Atualiza estatísticas
ANALYZE users;
ANALYZE VERBOSE orders;

-- Vacuum (PostgreSQL)
VACUUM ANALYZE users;

…