Í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

Armadilhas frequentes
ConsultaProblemaAlternativa
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 ordenadaBusca textual ou índice trigram (pg_trgm)
WHERE criado_em > … com índice (cliente_id, criado_em)Índice composto só serve a partir da primeira colunaFiltre por cliente_id também, ou crie outro índice
WHERE status = 'ativo' em 95% das linhasLer 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, UNIQUE e 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.

Referências

Trilhas relacionadas