Índices: acelerar as buscas

O problema: a varredura de tabela completa

Quando você executa uma consulta com um filtro, por padrão o banco de dados faz uma varredura de tabela completa (full table scan): percorre todas as linhas uma a uma para verificar quais cumprem a condição.

SELECT * FROM pedidos WHERE cliente_id = 11;

Com 10 linhas não dá para notar. Com 10 milhões de linhas, percorrê-las todas a cada consulta é inviável. O custo cresce de forma linear: O(n).

A solução: um índice

Um índice é uma estrutura de dados auxiliar, ordenada, que o banco de dados mantém à parte da tabela. Funciona como o índice alfabético de um livro: em vez de ler o livro inteiro procurando uma palavra, você vai ao índice (ordenado) e salta direto para a página.

Internamente, a maioria dos motores (SQLite, PostgreSQL, MySQL) usa uma árvore B+ (B-tree): uma árvore balanceada onde cada nível divide o espaço de busca. Buscar um valor custa O(log n): com 10 milhões de linhas, em vez de 10.000.000 de comparações, bastam ~24.

Criar um índice

CREATE INDEX idx_pedidos_cliente ON pedidos (cliente_id);

A partir desse momento, as consultas que filtram (WHERE), ordenam (ORDER BY) ou agrupam por cliente_id podem usar o índice. A chave primária (PRIMARY KEY) é indexada automaticamente; por isso buscar por id já é rápido sem fazer nada.

Um índice pode abranger várias colunas (índice composto). A ordem importa: idx (pais, estado) serve para filtrar por pais ou por pais AND estado, mas não para filtrar apenas por estado.

CREATE INDEX idx_pedidos_pais_estado ON pedidos (pais, estado);

Quando criar um índice?

Crie índices nas colunas que aparecem com frequência em:

• WHERE (filtros), especialmente sobre tabelas grandes.
• JOIN ... ON (as chaves estrangeiras quase sempre merecem um índice).
• ORDER BY e GROUP BY.

O custo: não são de graça

Os índices não são mágica gratuita. Eles têm um preço:

• Escritas mais lentas. Cada INSERT, UPDATE ou DELETE deve atualizar também todos os índices afetados. Uma tabela com muitos índices escreve mais devagar.
• Espaço em disco. O índice é uma cópia ordenada das colunas; ocupa memória e disco adicionais.
• Seletividade. Um índice sobre uma coluna com poucos valores distintos (p. ex. estado com apenas 3 valores) traz pouco: o motor poderia preferir uma varredura mesmo assim.

Regra prática: indexe o que você consulta muito, não tudo. Um índice não usado é puro custo sem benefício.

Ver o que o planejador faz: EXPLAIN QUERY PLAN

Para saber se uma consulta usa um índice ou varre a tabela, anteponha EXPLAIN QUERY PLAN (sintaxe do SQLite; no PostgreSQL/MySQL é EXPLAIN):

EXPLAIN QUERY PLAN
SELECT * FROM pedidos WHERE cliente_id = 11;

• SCAN pedidos → varredura completa (sem índice útil).
• SEARCH pedidos USING INDEX idx_pedidos_cliente → usa o índice!

É a ferramenta nº 1 para diagnosticar consultas lentas: primeiro meça, não adivinhe.

Exemplos

Criar um índice e consultar usando-o

CREATE INDEX idx_pedidos_cliente ON pedidos (cliente_id);

SELECT id, total
FROM pedidos
WHERE cliente_id = 11
ORDER BY id;

Diagnosticar o plano de execução

EXPLAIN QUERY PLAN
SELECT * FROM pedidos WHERE cliente_id = 11;