Ensinando índices de banco de dados a um desenvolvedor backend júnior

Entendendo Índices de Banco de Dados: Um Guia Prático

O que é um índice

Um índice de banco de dados é uma estrutura de dados separada que o banco de dados mantém ao lado de uma tabela. Ele armazena os valores de uma ou mais colunas em um formato ordenado e pesquisável, juntamente com ponteiros para as linhas reais. A tabela em si armazena seus dados; o índice é uma estrutura extra cujo único trabalho é ajudar o banco de dados a encontrar linhas mais rapidamente.

Aqui está uma analogia, e é a única que você precisa: um índice é como o índice no final de um livro didático. Se você quiser todas as páginas que mencionam "transações", você não lê o livro inteiro página por página; você procura "transações" no índice ordenado alfabeticamente, obtém uma lista curta de números de página e salta diretamente para elas. Sem esse índice, sua única opção é escanear todas as páginas. Um banco de dados enfrenta exatamente a mesma escolha: usar um índice para saltar para as linhas correspondentes ou escanear a tabela inteira.

Agora o comportamento real, sem a analogia. Quando você executa uma consulta como SELECT * FROM orders WHERE customer_id = 42, o banco de dados tem duas estratégias básicas. Um escaneamento completo da tabela lê cada linha e verifica a condição, o que custa tempo proporcional ao tamanho da tabela. Uma busca de índice, em vez disso, pesquisa a estrutura de índice ordenada para customer_id = 42, encontra as entradas correspondentes rapidamente e segue os ponteiros armazenados para buscar apenas essas linhas. Para uma tabela grande onde apenas algumas linhas correspondem, o caminho do índice pode ser milhares de vezes mais barato.

Como um índice B-tree funciona, em um nível alto

O tipo de índice mais comum é uma B-tree. É uma estrutura de árvore balanceada onde as chaves são mantidas em ordem classificada. O nó superior divide o espaço de chaves em intervalos, cada nó filho subdivide ainda mais, e o nível inferior (as folhas) contém os valores indexados reais com ponteiros para as linhas da tabela. Como a árvore é balanceada e cada nó contém muitas chaves, mesmo uma tabela com centenas de milhões de linhas geralmente precisa de apenas três a cinco leituras de nó para encontrar qualquer valor específico.

Como uma B-tree mantém os valores em ordem classificada, ela suporta mais do que correspondências exatas. Ela lida eficientemente com condições de intervalo (WHERE created_at >= '2024-01-01'), correspondências de prefixo em strings (WHERE email LIKE 'anna%') e pode retornar linhas já classificadas, o que permite que o banco de dados pule uma etapa de classificação separada para cláusulas ORDER BY correspondentes.

Por que os índices custam algo

Os índices não são gratuitos, e este é o trade-off que você deve internalizar.

As gravações ficam mais lentas. Cada INSERT deve adicionar uma entrada a cada índice da tabela. Cada DELETE deve remover entradas. Cada UPDATE que altera uma coluna indexada deve atualizar as entradas de índice correspondentes. Uma tabela com seis índices efetivamente faz até sete gravações para cada inserção de linha lógica. Em tabelas com muitas gravações, a indexação descuidada prejudica mensuravelmente o throughput.

O armazenamento cresce. Cada índice é uma cópia completa dos valores das colunas indexadas mais ponteiros e estrutura de árvore. Índices em tabelas grandes podem rivalizar ou exceder o tamanho da própria tabela, o que também afeta backups e cache de memória.

Portanto, o princípio orientador é: índices trocam custo de gravação e armazenamento por velocidade de leitura. Você os adiciona onde as leituras se beneficiam claramente, não em todos os lugares.

Seletividade: o conceito chave para decidir o valor

Seletividade descreve o quão bem uma condição restringe as linhas. Uma coluna altamente seletiva tem muitos valores distintos em relação à contagem de linhas. Um e-mail ou ID de pedido é altamente seletivo: filtrar nele retorna uma ou um punhado de linhas de milhões, e um índice brilha. Uma coluna como status com três valores ('pendente', 'enviado', 'cancelado') ou um booleano is_active flag tem baixa seletividade: filtrar pode ainda corresponder a 40% da tabela.

Por que isso importa? Se uma condição corresponde a uma grande fração da tabela, saltar para frente e para trás entre o índice e a tabela para milhões de linhas é frequentemente mais lento do que simplesmente escanear a tabela sequencialmente. Planejadores de consulta sabem disso e ignorarão um índice quando a fração de correspondência estimada for muito alta. Como uma intuição aproximada, se uma consulta típica usando o índice retornasse mais de alguns por cento das linhas, o índice pode não ser usado de forma alguma, e é puro overhead.

Índices compostos e a regra do prefixo mais à esquerda

Um índice pode cobrir várias colunas, em uma ordem específica. Por exemplo:

CREATE INDEX idx_orders_customer_date ON orders (customer_id, created_at);

Pense nisso como classificar as entradas primeiro por customer_id, depois por created_at dentro de cada cliente, como uma lista telefônica classificada por sobrenome, depois nome.

A ideia do prefixo mais à esquerda segue diretamente dessa ordem de classificação. Este índice pode servir eficientemente:

• WHERE customer_id = 42
• WHERE customer_id = 42 AND created_at >= '2024-01-01'

Mas ele não pode servir eficientemente WHERE created_at >= '2024-01-01' sozinho, porque as entradas para um determinado intervalo de datas estão espalhadas por todos os clientes; você não pode usar uma lista telefônica classificada por sobrenome para encontrar todos chamados "Ana". O índice só é utilizável quando suas condições restringem um prefixo de sua lista de colunas, começando pela coluna mais à esquerda. Isso significa que (customer_id, created_at) e (created_at, customer_id) são índices diferentes servindo consultas diferentes, e a ordem das colunas deve seguir seus padrões de consulta mais importantes. Uma regra geral comum: coloque colunas filtradas por igualdade primeiro, depois a coluna de intervalo ou de classificação.

Quando um índice não ajuda

• Baixa seletividade: filtrar WHERE is_active = true em uma tabela onde 90% das linhas estão ativas. O planejador fará um escaneamento em vez disso.
• Funções ou expressões na coluna: WHERE LOWER(email) = 'x@y.com' não pode usar um índice simples em email, porque o índice armazena valores brutos, não transformados. (Alguns bancos de dados suportam índices de expressão, mas o índice simples não será usado.)
• Wildcards iniciais: WHERE name LIKE '%son' não pode usar uma B-tree, porque a ordem classificada só ajuda quando o prefixo é conhecido.
• Pular a coluna mais à esquerda de um índice composto, como descrito acima.
• Tabelas minúsculas: para algumas centenas de linhas, um escaneamento já é rápido; o índice adiciona custo de gravação sem benefício.
• Incompatibilidades de tipo ou conversões implícitas na coluna indexada também podem impedir o uso do índice.

Dois pequenos exemplos

Um índice útil. Suponha que sua aplicação execute constantemente:

SELECT id, total, created_at
FROM orders
WHERE customer_id = 42
ORDER BY created_at DESC
LIMIT 20;

Então este índice se encaixa perfeitamente:

CREATE INDEX idx_orders_customer_date ON orders (customer_id, created_at);

O banco de dados salta para as entradas do cliente 42, que já estão classificadas por created_at, lê as 20 mais recentes e para. É rápido em uma tabela de qualquer tamanho, e também serve buscas simples por customer_id graças ao prefixo mais à esquerda.

Um índice problemático. Suponha que você crie em vez disso:

CREATE INDEX idx_orders_status ON orders (status);

onde status tem três valores possíveis e a maioria das linhas são 'enviado'. Uma consulta como SELECT * FROM orders WHERE status = 'enviado' corresponde à maior parte da tabela, então o planejador fará um escaneamento da tabela de qualquer maneira. O índice raramente ou nunca é usado, mas cada inserção e cada atualização de status paga para mantê-lo. Isso é uma perda líquida. (Uma exceção que vale a pena saber: indexar uma coluna de baixa cardinalidade pode valer a pena quando um valor é raro e consultado intensamente, como o pequeno conjunto de pedidos 'pendentes', mas a versão genérica acima é um erro clássico.)

Checklist prático antes de adicionar um índice

1. Identifique a consulta lenta real primeiro; não indexe especulativamente. Olhe para os padrões de consulta reais e use EXPLAIN para ver o plano atual.
2. Verifique a seletividade: consultas típicas usando este índice retornarão uma pequena fração da tabela? Se não, reconsidere.
3. Para filtros e classificações de várias colunas, projete um índice composto com a ordem correta das colunas (colunas de igualdade primeiro, depois colunas de intervalo/classificação) em vez de vários índices de coluna única.
4. Confirme a regra do prefixo mais à esquerda: sua consulta mais comum restringe a primeira coluna do índice?
5. Certifique-se de que a consulta não anule o índice com funções, wildcards iniciais ou conversões de tipo na coluna indexada.
6. Pondere o tráfego de gravação: em tabelas com muitas gravações, cada índice extra tem um custo real. Remova índices que se sobrepõem ou são prefixos de outros.
7. Verifique se um índice existente já cobre a consulta antes de criar um novo.
8. Após criar o índice, verifique com EXPLAIN se o planejador realmente o usa e meça o tempo da consulta antes e depois.
9. Revise periodicamente e remova índices não utilizados; eles custam gravações e armazenamento para sempre.

O modelo mental central a manter: um índice é uma estrutura de busca classificada pela qual você paga em cada gravação para que leituras específicas e seletivas se tornem baratas. Adicione um quando você puder nomear a consulta que ele atende e mostrar que ele ajuda.

Vamos falar sobre índices de banco de dados. Você já escreveu consultas SQL que provavelmente funcionam muito bem em sua máquina de desenvolvimento com algumas centenas de linhas. Mas quando uma tabela cresce para milhões de linhas, algumas dessas consultas podem se tornar incrivelmente lentas. Um índice é a ferramenta mais comum que usamos para resolver esse problema.

O que é um Índice de Banco de Dados? Uma Analogia

Pense em um índice como o índice no final de um livro didático. Se você quiser encontrar todas as páginas que mencionam "B-trees", você não lê o livro inteiro de capa a capa. Em vez disso, você vai ao índice, encontra a entrada para "B-trees" e vê uma lista classificada de números de página (por exemplo, 112, 254, 256). Isso é muito mais rápido.

Um índice de banco de dados funciona da mesma maneira. É uma estrutura de dados separada que o banco de dados mantém. Ele contém uma cópia dos dados de uma ou mais colunas em ordem classificada, juntamente com um ponteiro de volta para a linha original na tabela. Quando você consulta dados nessa coluna, o banco de dados pode usar esse índice classificado para encontrar rapidamente os ponteiros para as linhas que você precisa, em vez de escanear a tabela inteira linha por linha (um "full table scan").

Como Funciona: A B-Tree

A maioria dos bancos de dados relacionais usa uma B-tree para seus índices padrão. Você não precisa conhecer os detalhes profundos da ciência da computação, mas em um nível geral, uma B-tree é uma estrutura de árvore auto-balanceada. Imagine uma hierarquia ramificada. Para encontrar um valor, o banco de dados começa no nó raiz e segue um caminho pelos ramos. Cada etapa reduz drasticamente a pesquisa. Isso permite que o banco de dados encontre qualquer valor em um conjunto de dados massivo com muito poucos passos, tornando as pesquisas incrivelmente eficientes (complexidade de tempo logarítmica, ou O(log n)) em comparação com um full table scan (complexidade de tempo linear, ou O(n)).

As Compensações: Velocidade de Gravação e Armazenamento

Os índices não são gratuitos. Eles vêm com dois custos principais:

1. Armazenamento: Um índice é uma estrutura de dados física armazenada em disco. Ele duplica os dados da sua tabela, portanto, consome espaço de armazenamento extra.
2. Gravações Mais Lentas: Esta é a compensação mais importante. Quando você INSERT, UPDATE ou DELETE uma linha, o banco de dados não apenas altera a tabela. Ele também deve atualizar todos os índices que contêm as colunas afetadas para mantê-los sincronizados e classificados. Se você tem uma tabela com cinco índices, uma instrução INSERT na verdade resulta em seis gravações: uma para a tabela e uma para cada índice. Isso pode desacelerar significativamente aplicações com muitas gravações.

Conceitos Chave para Indexação Prática

1. Seletividade
Seletividade refere-se a quantos valores únicos existem em uma coluna em relação ao número total de linhas.

• Alta Seletividade: Uma coluna onde a maioria dos valores é única, como user_id ou email_address. Índices nessas colunas são muito eficazes porque podem reduzir uma pesquisa a apenas uma ou poucas linhas muito rapidamente.
• Baixa Seletividade: Uma coluna com muito poucos valores únicos, como um booleano is_active (verdadeiro/falso) ou uma coluna status ('pendente', 'ativo', 'inativo'). Um índice em uma coluna de baixa seletividade é frequentemente inútil. Se você pesquisar por todos os usuários ativos, isso pode ser 80% da sua tabela. É mais rápido para o banco de dados ler a tabela inteira do que ir e voltar entre um índice e os dados da tabela.

2. Índices Compostos e a Regra do Prefixo Mais à Esquerda
Um índice composto é um índice em duas ou mais colunas, como CREATE INDEX idx_name ON users (last_name, first_name);.

A ordem das colunas é crítica. Esta é a regra do prefixo mais à esquerda. Um índice em (A, B, C) pode ser usado pelo banco de dados para acelerar consultas que filtram em:

• A
• A e B
• A, B e C

No entanto, ele não pode ser usado para consultas que filtram apenas em B, C ou B e C. O banco de dados precisa das primeiras colunas no índice para iniciar sua pesquisa.

Exemplos SQL

Vamos supor uma tabela products com milhões de linhas.

Bom Exemplo de Índice:
Muitos usuários estão procurando produtos por um código de produto exclusivo.

-- A Consulta
SELECT name, price FROM products WHERE product_code = 'ABC-12345';

-- A Solução: Um índice em uma coluna de alta seletividade
CREATE INDEX idx_products_product_code ON products (product_code);

Este índice é altamente eficaz porque product_code é único (alta seletividade), e o índice permite que o banco de dados encontre a linha do produto exata quase instantaneamente.

Exemplo de Índice Menos Útil:
Queremos encontrar todos os produtos que estão atualmente disponíveis para venda.

-- A Consulta
SELECT * FROM products WHERE is_available = TRUE;

-- O Índice Problemático
CREATE INDEX idx_products_is_available ON products (is_available);

Este índice não é muito útil. A coluna is_available tem baixa seletividade (apenas dois valores: verdadeiro e falso). Se 90% dos produtos estiverem disponíveis, o banco de dados provavelmente ignorará este índice e realizará um full table scan, pois é mais eficiente.

Checklist: Quando Adicionar um Índice

Antes de adicionar um índice, pergunte a si mesmo:

1. A tabela é grande? (Índices em tabelas pequenas têm pouco ou nenhum benefício).
2. Uma consulta específica está lenta? (Não adicione índices cegamente; adicione-os para corrigir um problema de desempenho conhecido).
3. Estou filtrando nesta coluna com frequência em uma cláusula WHERE ou usando-a em um JOIN? (Esses são os principais candidatos para indexação).
4. A coluna tem alta seletividade? (O índice reduzirá significativamente o número de linhas a serem verificadas?).
5. Para um índice composto, minha consulta usa as colunas mais à esquerda do índice?
6. Esta é uma tabela com muitas leituras? (Se a tabela tiver gravações muito frequentes, mas poucas leituras, o custo de manutenção do índice pode superar os benefícios de leitura).