Índices e transações em SQL
Como um índice acelera consultas, como ler o EXPLAIN e quando agrupar comandos em uma transação.
8 min de leitura · Intermediário · Revisado em
O que é um índice
Sem índice, o banco lê todas as linhas da tabela para achar as que você pediu. Um índice é uma estrutura ordenada, normalmente uma árvore B, que leva direto às linhas certas, como o índice remissivo de um livro. A busca passa de O(n) para O(log n).
Criando um índice para a busca mais comum
-- A consulta da tela "meus pedidos"
SELECT id, total, criado_em
FROM pedidos
WHERE cliente_id = 42
ORDER BY criado_em DESC
LIMIT 20;
-- Um índice que atende ao filtro e à ordenação
CREATE INDEX pedidos_cliente_criado_idx
ON pedidos (cliente_id, criado_em DESC);Leia o plano com EXPLAIN
PostgreSQL: executa a consulta e mostra o caminho escolhido
EXPLAIN ANALYZE
SELECT * FROM pedidos WHERE cliente_id = 42;Index Scan using pedidos_cliente_criado_idx on pedidos
(actual time=0.031..0.054 rows=18 loops=1)
Execution Time: 0.081 ms- Seq Scan
- Leu a tabela inteira. Normal em tabelas pequenas; em tabelas grandes, sinal de que falta índice.
- Index Scan
- Usou o índice para achar as linhas e depois buscou cada uma na tabela.
- Index Only Scan
- Tudo que a consulta pede está no índice. Nem precisou abrir a tabela.
- rows
- Compare o estimado com o real. Uma diferença grande indica estatísticas desatualizadas: rode
ANALYZE.
Quando o índice não é usado
| Consulta | Problema | Alternativa |
|---|---|---|
WHERE lower(email) = … | Função na coluna esconde o valor indexado | Índice na expressão: ON users (lower(email)) |
WHERE nome LIKE '%silva' | Curinga no começo impede a busca ordenada | Busca textual ou índice trigram (pg_trgm) |
WHERE criado_em > … com índice (cliente_id, criado_em) | Índice composto só serve a partir da primeira coluna | Filtre por cliente_id também, ou crie outro índice |
WHERE status = 'ativo' em 95% das linhas | Ler a tabela é mais barato que pular pelo índice | Índice parcial para o caso raro: WHERE status = 'cancelado' |
Transações: tudo ou nada
Uma transação agrupa comandos que precisam acontecer juntos. Se algo falha no meio, o ROLLBACK desfaz tudo e o banco nunca fica num estado pela metade.
Transferência: debitar e creditar, ou nenhum dos dois
BEGIN;
UPDATE contas SET saldo = saldo - 100
WHERE id = 1 AND saldo >= 100;
UPDATE contas SET saldo = saldo + 100
WHERE id = 2;
COMMIT; -- ou ROLLBACK se algo deu errado- Atomicidade
- Todos os comandos acontecem, ou nenhum.
- Consistência
- Restrições como
NOT NULL,UNIQUEe chaves estrangeiras valem no final. - Isolamento
- Transações simultâneas não veem o trabalho incompleto umas das outras.
- Durabilidade
- Depois do
COMMIT, sobrevive a uma queda do servidor.
- As consultas mais frequentes passaram por
EXPLAIN ANALYZE. - Colunas de chave estrangeira usadas em JOINs têm índice.
- Não há índices duplicados ou que nenhuma consulta usa.
- Operações que mudam várias tabelas estão em uma transação.
- Migrações que criam índices em tabelas grandes usam
CREATE INDEX CONCURRENTLY.