Índices de banco de dados: acelere queries sem adivinhar

Já mostrei aqui como escrever SQL do zero e como versionar schema com migration, mas ficou faltando um assunto que separa quem só escreve query de quem entende de verdade o que acontece quando ela roda: índice. É comum ouvir "cria um índice ali que resolve", sem entender por que resolve, nem quando não resolve nada.

O que um índice realmente é

Sem índice, buscar um registro numa tabela obriga o banco a olhar linha por linha, do início ao fim, até achar (ou não achar) o que foi pedido. Isso se chama table scan, e o custo cresce junto com o tamanho da tabela: numa tabela com cem linhas, isso nem se percebe, numa com dez milhões, cada consulta vira segundos de espera.

Um índice é uma estrutura de dado separada, guardada ao lado da tabela, organizada de um jeito que permite achar um valor rapidamente sem precisar olhar linha por linha. A comparação mais direta é o índice remissivo no final de um livro técnico: em vez de ler o livro inteiro procurando onde um termo aparece, você vai direto no índice, que já aponta pra página certa.

CREATE INDEX idx_usuarios_email ON usuarios (email);

Depois disso, uma busca por email deixa de varrer a tabela inteira e passa a consultar essa estrutura auxiliar, que devolve a localização exata da linha buscada de forma muito mais rápida.

Lendo um EXPLAIN

A forma de saber se uma query está realmente usando um índice, em vez de supor, é pedir pro próprio banco explicar o plano de execução dela:

EXPLAIN SELECT * FROM usuarios WHERE email = 'ana@exemplo.com';

Sem índice, o retorno mostra algo como Seq Scan on usuarios (no Postgres), indicando que o banco está varrendo a tabela inteira, linha por linha. Com o índice criado, o mesmo comando mostra Index Scan using idx_usuarios_email, confirmando que o banco encontrou um caminho mais direto até o dado.

Esse hábito de rodar EXPLAIN antes de sair criando índice às cegas é o que separa uma decisão baseada em dado real de um palpite. Às vezes a query já é rápida o suficiente sem índice nenhum, e criar um sem necessidade só adiciona custo sem trazer ganho perceptível.

O custo que ninguém enxerga na hora de criar

Índice não é de graça. Toda vez que uma linha é inserida, atualizada ou apagada, o banco também precisa atualizar cada índice daquela tabela, pra manter essa estrutura auxiliar sincronizada com o dado real. Numa tabela com muita escrita (inserção constante de linha nova, por exemplo um log de eventos), cada índice a mais deixa cada INSERT um pouco mais lento.

O equilíbrio certo é indexar as colunas que aparecem com frequência em WHERE, JOIN ou ORDER BY, e evitar criar índice em toda coluna só porque parece prudente. Índice em coluna que quase nunca é usada pra filtro é custo puro, sem contrapartida de ganho.

Índice simples x composto

Um índice pode cobrir uma coluna só, ou várias juntas, na ordem em que aparecem:

CREATE INDEX idx_pedidos_usuario_status
ON pedidos (usuario_id, status);

Esse índice composto ajuda bastante uma consulta que filtra pelas duas colunas juntas:

SELECT * FROM pedidos WHERE usuario_id = 123 AND status = 'pendente';

Mas a ordem das colunas no índice importa. Esse mesmo índice ainda ajuda uma consulta que filtra só por usuario_id (porque é a primeira coluna do índice), mas não ajuda uma consulta que filtra só por status sozinho, porque o banco não consegue "pular" a primeira coluna do índice composto e ir direto na segunda. Pra esse segundo caso, seria necessário um índice separado, só pra status.

Índice único, pra além de performance

Índice também serve pra garantir uma regra, não só pra acelerar busca. Um índice único impede que dois registros tenham o mesmo valor numa coluna:

CREATE UNIQUE INDEX idx_usuarios_email_unico ON usuarios (email);

Isso garante, no nível do banco, que dois usuários nunca vão ter o mesmo e-mail cadastrado, mesmo que a validação da aplicação falhe por algum motivo (uma corrida entre duas requisições simultâneas, por exemplo). É uma segunda linha de defesa, mais confiável do que só confiar na checagem feita no código da aplicação.

Fechando

Índice não é sobre criar em tudo por precaução, é sobre acelerar exatamente as consultas que a aplicação realmente faz com frequência, entendendo o custo de escrita que cada índice adiciona em troca. O EXPLAIN é a ferramenta que tira a decisão do campo do palpite: antes de criar índice esperando que ele resolva alguma lentidão, vale confirmar primeiro que o problema realmente está ali, e depois confirmar que o índice criado está sendo usado de verdade.