Como os índices SQL realmente funcionam (e por que sua query está lenta)

A pergunta certa não é "como escrever a query", é "como o banco vai executá-la"#

Existe um momento na carreira de todo desenvolvedor em que uma query que rodava em milissegundos com mil registros passa a demorar segundos com um milhão. O SQL não mudou. A lógica está correta. E ainda assim o sistema está de joelhos. Esse é o momento em que fica claro que escrever SQL que funciona e escrever SQL que escala são habilidades diferentes — e a ponte entre elas é entender o que o banco de dados faz por baixo quando recebe sua consulta.

O banco não executa seu SQL literalmente; ele o traduz num plano de execução — uma estratégia sobre como buscar os dados. Para uma mesma query, pode haver dezenas de planos possíveis, com custos que variam em ordens de grandeza. A diferença entre uma consulta rápida e uma lenta quase nunca está na sintaxe; está em qual plano o otimizador escolheu, e essa escolha depende, mais do que de qualquer outra coisa, dos índices disponíveis. Entender índices é entender o mecanismo que decide se sua aplicação responde em 5 milissegundos ou em 5 segundos.

O custo real: por que a busca sequencial não escala#

Comece pelo que acontece sem índice. Suponha a query mais simples do mundo:

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

Sem um índice sobre email, o banco não tem escolha a não ser fazer uma varredura sequencial (full table scan): ler a tabela inteira, linha por linha, comparando cada email com o valor procurado. Com mil linhas, isso é instantâneo. Com dez milhões, o banco lê dez milhões de linhas para encontrar uma. O custo cresce linearmente com o tamanho da tabela — dobrou a tabela, dobrou o tempo. É esse crescimento linear que explica por que a mesma query "de repente" ficou lenta: ela nunca foi rápida por mérito próprio, era rápida porque a tabela era pequena.

A intuição-chave aqui é pensar em custo de acesso a dados, não em custo de CPU. O gargalo raramente é a comparação em si; é a leitura das páginas de dados do disco (ou da memória). Um plano que precisa tocar em muitas páginas é lento; um plano que toca em poucas é rápido. Todo o jogo da otimização de consultas é reduzir a quantidade de dados que o banco precisa ler para responder. E é exatamente para isso que serve um índice.

A B-tree: a estrutura por trás de quase todo índice#

O índice mais comum em bancos relacionais é a B-tree (mais precisamente, uma B+tree), e entender sua forma explica quase todo o comportamento de performance que você vai encontrar. Pense num índice de livro, ou numa lista telefônica: os dados estão ordenados, o que permite pular direto para perto do que você procura em vez de ler página por página.

Uma B-tree é uma árvore balanceada que mantém as chaves ordenadas. Para encontrar um valor, o banco desce da raiz até a folha comparando a cada nível e descartando metade (ou mais) das possibilidades restantes. O número de passos cresce logaritmicamente com o tamanho — dobrar a tabela adiciona aproximadamente um único nível à árvore. É a diferença entre linear e logarítmico que transforma a performance: encontrar um registro entre dez milhões numa B-tree custa em torno de dois ou três acessos a páginas, contra dez milhões de leituras na varredura sequencial. Não é uma melhoria de porcentagem; é uma mudança de categoria.

``sql -- Cria a B-tree sobre email; agora a busca por igualdade é logarítmica CREATE INDEX idx_usuarios_email ON usuarios (email); ``

Essa estrutura ordenada explica quais consultas um índice acelera e quais não. Porque as chaves estão ordenadas, uma B-tree é excelente para:

• Igualdade: WHERE email = '...' — desce direto até a chave.
• Faixas: WHERE idade BETWEEN 18 AND 30 — encontra o início da faixa e percorre sequencialmente as folhas ordenadas.
• Prefixos: WHERE nome LIKE 'Ana%' — o prefixo fixo permite localizar o ponto de partida.
• Ordenação: ORDER BY data_criacao — os dados já saem ordenados do índice, evitando um passo de ordenação custoso.

E explica onde ela não ajuda: WHERE email LIKE '%exemplo.com' (o curinga no início destrói a ordenação — não há prefixo por onde começar), ou aplicar uma função à coluna, como WHERE LOWER(email) = '...', que impede o banco de usar o índice sobre email cru. Essas são as armadilhas mais comuns: o índice existe, mas a forma da query o torna inútil, e o banco cai de volta na varredura sequencial sem avisar.

Índices compostos e a regra do prefixo à esquerda#

Quando as consultas filtram por mais de uma coluna, entram os índices compostos — índices sobre várias colunas ao mesmo tempo. E aqui mora uma das regras mais importantes e menos compreendidas: a ordem das colunas no índice importa, por causa do princípio do prefixo à esquerda.

Um índice composto sobre (a, b, c) ordena os dados primeiro por a, depois por b dentro de cada valor de a, depois por c. Pense de novo na lista telefônica, ordenada por sobrenome e depois por nome. Você consegue buscar eficientemente por "sobrenome = Silva" e por "sobrenome = Silva e nome = Ana". Mas não consegue buscar eficientemente só por "nome = Ana", porque os "Ana" estão espalhados por toda a lista, sob todos os sobrenomes. A ordenação secundária só é útil depois que a primária foi fixada.

A consequência prática: um índice sobre (loja_id, status, data) serve consultas que filtram por loja_id, ou por loja_id e status, ou pelas três — sempre começando pela esquerda. Mas ele não ajuda uma consulta que filtra só por status, ou só por data. Errar a ordem das colunas é criar um índice que parece cobrir a consulta mas não é usado.

``sql -- Serve: WHERE loja_id = ? / WHERE loja_id = ? AND status = ? -- Não serve bem: WHERE status = ? (pula o prefixo à esquerda) CREATE INDEX idx_pedidos_loja_status_data ON pedidos (loja_id, status, data_criacao); ``

A regra prática para ordenar colunas: coloque primeiro as colunas usadas em condições de igualdade, e por último a coluna usada em faixa ou ordenação. Uma condição de faixa "consome" a utilidade das colunas seguintes no índice, então ela deve vir no fim. Ordenar o índice segundo os padrões reais de consulta da aplicação é o que separa um índice que o otimizador usa de um que ele ignora.

Índices não são de graça#

Diante do poder dos índices, a tentação é indexar tudo. É um erro, porque índices têm um custo que não aparece nas consultas de leitura, mas cobra caro nas de escrita. Cada índice é uma estrutura de dados adicional que o banco precisa manter em sincronia: toda vez que você insere, atualiza ou deleta uma linha, todos os índices daquela tabela precisam ser atualizados também. Uma tabela com dez índices paga o custo de dez atualizações de árvore a cada escrita.

Além do custo de escrita, índices ocupam espaço em disco e em memória — e memória usada por índices desnecessários é memória que não está disponível para cachear os dados que realmente importam. Índices redundantes (um índice sobre (a) quando já existe um sobre (a, b), que cobre o mesmo prefixo) são puro desperdício. E índices sobre colunas de baixa cardinalidade — uma coluna booleana, um status com três valores possíveis — muitas vezes não valem a pena, porque não conseguem descartar dados o suficiente para vencer a varredura sequencial.

A disciplina, portanto, é indexar deliberadamente: identifique as consultas críticas — as frequentes e as lentas — e crie os índices que elas precisam, não os índices que você imagina que podem ser úteis um dia. Índice é resposta a um padrão de acesso real, não uma decoração preventiva.

Índices cobertos: quando o índice já tem a resposta#

Existe uma otimização que parece mágica quando você a descobre e que decorre naturalmente de entender a B-tree: o índice coberto (covering index). A ideia é que, se um índice contém todas as colunas de que uma consulta precisa, o banco pode responder a consulta lendo apenas o índice, sem nunca tocar na tabela em si.

Para entender por que isso importa, é preciso saber que um índice comum guarda apenas as colunas indexadas mais um ponteiro para a linha completa na tabela. Quando sua consulta pede colunas que não estão no índice, o banco faz a busca no índice e depois precisa "voltar" à tabela para buscar o resto dos dados de cada linha encontrada — um passo extra chamado, em alguns bancos, de heap fetch. Se a consulta retorna muitas linhas, esses acessos adicionais à tabela podem dominar o custo, mesmo com o índice fazendo sua parte.

Um índice coberto elimina esse segundo passo. Considere uma consulta que só precisa do status e da data de pedidos de uma loja:

```sql -- Índice que cobre a consulta: loja_id filtra, status e data são retornados CREATE INDEX idx_pedidos_cobertura ON pedidos (loja_id) INCLUDE (status, data_criacao);

SELECT status, data_criacao FROM pedidos WHERE loja_id = 42; ```

Como o índice já carrega status e data_criacao, o banco responde inteiramente a partir dele, sem tocar na tabela. Em consultas quentes que rodam milhares de vezes por segundo, essa diferença é enorme. O custo, coerente com o que já vimos, é que o índice fica maior e mais caro de manter a cada escrita — mais uma vez, a otimização de leitura se paga em escrita, e a decisão depende de medir o padrão real de uso.

Leia o plano de execução, não adivinhe#

Toda essa teoria converge num único hábito prático que vale mais que qualquer regra decorada: pergunte ao banco o que ele vai fazer. Praticamente todo banco relacional oferece o comando EXPLAIN (e sua variante EXPLAIN ANALYZE, que executa de verdade e mede), que revela o plano de execução escolhido para uma consulta.

``sql EXPLAIN ANALYZE SELECT * FROM pedidos WHERE loja_id = 42 AND status = 'pago' ORDER BY data_criacao DESC LIMIT 20; ``

O plano mostra se o banco vai usar um índice ou fazer varredura sequencial, quantas linhas ele estima ler, quantas ele realmente leu, e onde está gastando tempo. Aprender a ler esse relatório é a habilidade mais transformadora em otimização de banco de dados, porque substitui palpite por evidência. "Acho que essa query é lenta porque falta índice" vira "o plano mostra uma varredura sequencial de dois milhões de linhas onde eu esperava uma busca indexada — falta um índice sobre (loja_id, status)".

O EXPLAIN também expõe as armadilhas silenciosas: o índice que existe mas não é usado porque a query aplica uma função à coluna; a estimativa de linhas absurdamente errada, sinal de que as estatísticas da tabela estão desatualizadas e o otimizador está tomando decisões com base em dados velhos; a ordenação custosa que um índice bem ordenado eliminaria.

Do banco à aplicação: o desempenho é de ponta a ponta#

Otimizar índices raramente é um exercício isolado. A forma como a aplicação consulta o banco — quantas queries ela dispara por requisição, se pagina os resultados, se filtra no banco ou na aplicação — molda tanto a performance quanto os índices. Uma API que retorna listas gigantes sem paginação vai sofrer mesmo com índices perfeitos, e uma que dispara uma query por item numa lista (o clássico problema N+1) vai multiplicar até a busca indexada mais rápida por centenas. Por isso o design de consultas anda junto com o design da interface que as expõe, tema que exploramos em design de APIs REST.

No fim, a mensagem é uma só: o banco de dados não é uma caixa-preta que fica lenta por mágica. Ele executa estratégias concretas sobre estruturas de dados concretas, e cada uma delas é observável e explicável. Trocar a fé ("adicionei um índice, deve melhorar") pela medição ("o plano confirma que a busca indexada substituiu a varredura, e o tempo caiu de 800 ms para 3 ms") é o que transforma otimização de banco de superstição em engenharia.