Um SELECT lento que antes terminava em 20 milissegundos começa subitamente a ficar pendurado durante 12 segundos. A base de dados cresceu de 50 mil para 5 milhões de linhas e cada consulta tornou-se uma lotaria. Parece-lhe familiar?

Os índices são algo de que toda a gente ouviu falar, mas que poucos configuram deliberadamente. Adicionou uns quantos, as coisas pareceram mais rápidas e seguiu em frente. Depois, seis meses mais tarde, o INSERT está mais lento do que um SELECT sem índice, porque cada inserção reconstrói cinco B-trees desnecessárias.

Vamos analisar como é que os índices realmente funcionam, que tipos existem e, mais importante, que regras seguir ao construí-los para não causar o caos em produção.

💡 Visão geral rápida:

• Compreender a mecânica dos índices B-tree, hash e compostos, porque escolher o tipo certo é impossível sem este conhecimento
• Dominar 7 práticas essenciais: desde indexar chaves estrangeiras até eliminar índices não utilizados
• Aprender a ler o EXPLAIN e a distinguir um índice útil de um inútil

O que é um índice de base de dados

Um índice de base de dados é uma estrutura separada que armazena valores ordenados de uma ou mais colunas da tabela, juntamente com apontadores para as linhas. Quando executa SELECT ... WHERE category_id = 42, o servidor sem índice percorre a tabela linha a linha (full table scan). Com um índice, encontra os registos necessários em O(log n), como procurar um nome numa lista telefónica.

Um índice é criado com CREATE INDEX:

1
-- Regular index
2
CREATE INDEX idx_category
3
ON products (category_id);
4
 
5
-- Unique index
6
CREATE UNIQUE INDEX idx_email
7
ON users (email);

Mas o preço das leituras mais rápidas são as escritas mais lentas. Cada INSERT, UPDATE e DELETE tem de atualizar não só a tabela, mas todos os índices associados. Três índices numa tabela com um milhão de linhas e as inserções em massa abrandam numa ordem de grandeza. O equilíbrio entre a velocidade de leitura e a velocidade de escrita é a questão central do design de índices.

Uma análise detalhada do tópico por Hussein Nasser: 497 mil subscritores, uma abordagem de engenharia sem palha. Usando o PostgreSQL como exemplo, ele mostra a mecânica interna dos índices, porque é que um CREATE INDEX acelera uma consulta em 100x enquanto outro não faz nada.

Tipos de índices: quando usar cada um

A escolha do tipo de índice determina a eficiência com que a base de dados processa a sua consulta. Diferentes SGBDs implementam-nos de forma diferente, mas os princípios são universais.

B-tree (árvore balanceada)

O tipo padrão na maioria dos SGBDs relacionais. PostgreSQL, MySQL, Oracle e SQL Server usam todos o B-tree como índice padrão. Armazena as chaves por ordem, suporta operações de comparação, intervalos (BETWEEN), pesquisa por prefixo (LIKE 'prefix%') e ordenação. É a melhor escolha para a grande maioria dos casos.

Índice hash

Funciona apenas para comparações de igualdade exata =. Extremamente rápido em consultas pontuais, mas inútil para intervalos e ordenação. No PostgreSQL, os índices hash tornaram-se robustos para produção a partir da versão 10; no MySQL não estão disponíveis no InnoDB (apenas no MEMORY).

Índice clustered

Define a ordem física das linhas no disco. No MySQL InnoDB, a chave primária é sempre clustered: as linhas são armazenadas por ordem da PRIMARY KEY. No SQL Server, existe um índice clustered por tabela. Escolher a chave clustered certa (monotonicamente crescente: BIGSERIAL, AUTO_INCREMENT ou UNIQUEIDENTIFIER com NEWSEQUENTIALID()) proporciona ganhos em consultas por intervalo e evita a fragmentação de páginas.

Índice composto

Um índice sobre duas ou mais colunas. Crítico para consultas que filtram e ordenam por várias colunas:

1
CREATE INDEX idx_order_date_status
2
ON orders (order_date, status);

A ordem das colunas é importante: coloque em primeiro lugar a coluna com maior seletividade usada no WHERE. O princípio do prefixo mais à esquerda: um índice (A, B, C) funciona para WHERE A e WHERE A AND B, mas não para WHERE B AND C.

Índice covering

Contém todas as colunas de que a consulta precisa, tanto para filtragem como para output. A base de dados obtém tudo do índice sem tocar na tabela. No PostgreSQL, isto é feito com colunas INCLUDE; no MySQL InnoDB, o covering implícito acontece através do índice clustered.

7 Práticas essenciais de indexação

1. Indexar colunas usadas no WHERE

A primeira regra: todas as colunas usadas regularmente em WHERE, JOIN ... ON e HAVING devem ser indexadas. Estas são as operações que mais beneficiam dos índices.

Antes de criar um índice, verifique a seletividade da coluna. Se a coluna status tiver apenas três valores ('novo', 'em processamento', 'concluído') e houver 2 milhões de linhas, um índice em status é quase inútil: o planeador escolherá um full table scan como a opção mais barata. Um índice faz sentido quando o número de valores únicos é suficientemente grande em relação ao tamanho da tabela.

2. Indexar colunas usadas para ordenação

ORDER BY sem um índice significa filesort (MySQL) ou ordenação explícita (PostgreSQL): o servidor recolhe todas as linhas e ordena-as em memória (ou em disco se work_mem/sort_buffer_size for demasiado pequeno). Um índice nas mesmas colunas do ORDER BY é gratuito, uma vez que os dados já estão ordenados na B-tree.

1
-- No index on created_at: filesort on millions of rows
2
SELECT * FROM posts ORDER BY created_at DESC LIMIT 20;
3
 
4
-- Index solves the problem
5
CREATE INDEX idx_posts_created ON posts (created_at);

3. Indexar colunas usadas em GROUP BY e agregações

Agrupar sem um índice requer um varrimento completo e a construção de uma tabela hash. Um índice nas colunas do GROUP BY transforma a operação numa agregação em fluxo: as linhas já estão agrupadas por ordem de chave.

4. Indexar todas as chaves estrangeiras

Uma chave estrangeira não indexada é uma bomba-relógio. DELETE FROM users WHERE id = 5 com uma FOREIGN KEY (user_id) REFERENCES users(id) na tabela orders, mas sem índice em user_id, significa um full table scan da orders por cada eliminação. Todos os SGBDs populares exigem um índice na chave estrangeira ou criam um implicitamente (o MySQL InnoDB fá-lo automaticamente, o PostgreSQL não).

5. Indexar colunas únicas e chaves primárias

A chave primária é indexada automaticamente (frequentemente como índice clustered). Um UNIQUE INDEX explícito protege contra duplicados e também acelera as pesquisas. Qualquer coluna com uma restrição de unicidade de negócio (por exemplo email, slug ou external_id) deve ter um índice único, tanto para integridade como para desempenho.

6. Usar o índice clustered deliberadamente

Para tabelas grandes (dezenas de milhões de linhas), a chave clustered certa é crítica. Uma boa escolha é um valor monotonicamente crescente: AUTO_INCREMENT, BIGSERIAL ou UUID v7. Um UUID aleatório como chave clustered causa fragmentação de páginas: cada inserção cai num ponto aleatório da B-tree, dividindo páginas cheias em duas semi-preenchidas.

7. Eliminar índices não utilizados

Um índice que nenhuma consulta usa é uma perda pura. Abranda as escritas, ocupa espaço em disco e memória da buffer pool e induz em erro o planeador de consultas. No PostgreSQL, a tabela de sistema pg_stat_user_indexes fornece uma lista de índices não utilizados:

1
SELECT schemaname, relname, indexrelname, idx_scan
2
FROM pg_stat_user_indexes
3
WHERE idx_scan = 0
4
ORDER BY relname;

No MySQL, informação semelhante está disponível em sys.schema_unused_indexes (a partir da versão 5.7). Agende uma auditoria mensal e elimine os índices que não foram usados nem uma vez durante o período em análise.

Como verificar se os seus índices estão a funcionar

Depois de criar um índice, verifique se ele está realmente a ser usado. O comando EXPLAIN (ou EXPLAIN ANALYZE) mostra o plano de execução da consulta e a utilização real do índice:

1
EXPLAIN ANALYZE
2
SELECT * FROM orders
3
WHERE customer_id = 12345
4
ORDER BY order_date DESC;

No output, procure por Index Scan ou Index Only Scan (PostgreSQL) / Using index (MySQL). Se vir Seq Scan (PostgreSQL) ou Using where; Using filesort (MySQL), o índice não está a ser usado. Razões possíveis: baixa seletividade, tipo de índice errado, ordem de colunas incorreta num índice composto ou estatísticas desatualizadas (ANALYZE table_name;).

Monitorize as métricas regularmente: pg_stat_user_indexes.idx_scan no PostgreSQL, sys.schema_index_statistics no MySQL. Um índice com zero scans durante um mês é candidato a remoção.

⁉️🤔 Perguntas frequentes

Quantos índices deve ter uma tabela?

Idealmente, 2 a 6 índices por tabela usada ativamente. Menos de dois significa quase de certeza que algumas consultas estão subótimas. Mais de seis, e deve verificar cuidadosamente se todos são realmente necessários: cada índice extra abranda as escritas. Para tabelas de lookup (escritas raras, leituras frequentes), justificam-se mais índices. Para tabelas operacionais de alto tráfego (muitos INSERT/UPDATE), mantenha o número no mínimo.

Como é que um índice composto é melhor do que vários índices de coluna única?

Um índice composto (A, B) é UMA estrutura. O servidor percorre-a uma vez. Três índices separados (A), (B) e (C) para WHERE A=1 AND B=2 obrigam o servidor a escolher um índice (e filtrar o resto) ou a realizar um bitmap index scan (fundindo bitmaps). Um índice composto é quase sempre mais eficiente, desde que a ordem das colunas corresponda às suas consultas.

Quando é que um índice prejudica em vez de ajudar?

Três cenários típicos. Primeiro: a tabela é pequena (até alguns milhares de linhas) e um full table scan é mais rápido do que ler o índice mais ir buscar as linhas. Segundo: um índice numa coluna com baixa seletividade (is_deleted, status com três valores). Terceiro: inserções em massa durante ETL/importação, em que os índices são reconstruídos a cada lote. Elimine-os antes de carregar e recrie-os depois.

Deve indexar colunas usadas em JOIN?

Absolutamente. Cada JOIN sem um índice na coluna de junção da tabela externa é um nested loop com um varrimento completo. Para LEFT JOIN orders ON users.id = orders.user_id, um índice em orders.user_id transforma o nested loop numa pesquisa por índice. Indexe sempre as colunas em que faz join.

B-tree ou hash: qual escolher para pesquisas exatas?

Para =, o hash é mais rápido: uma pesquisa hash demora tempo constante, enquanto a B-tree percorre a árvore num número logarítmico de passos. Mas o hash não suporta intervalos, ordenação ou UNIQUE. Na prática, a B-tree cobre a grande maioria dos cenários; o hash é uma ferramenta de nicho para pesquisas pontuais por chave em sistemas de alta carga (sessões, caches). No PostgreSQL, os índices hash são robustos para produção desde a versão 10 e ocupam menos espaço do que a B-tree.

Deve indexar "por precaução"?

Não. Cada índice é um compromisso. Acelera as leituras à custa de escritas mais lentas e espaço em disco adicional. Não indexe "por precaução"; indexe para consultas específicas que realmente são executadas na sua aplicação. Analise as consultas lentas (pg_stat_statements, slow_query_log), adicione índices cirurgicamente e verifique o EXPLAIN antes e depois.

A principal conclusão é simples: os índices são uma ferramenta, não um objetivo em si mesmos. Um índice composto bem desenhado pode substituir três índices de coluna única e poupar gigabytes de espaço em disco. E um índice não utilizado numa tabela com muitas escritas pode abrandar toda a aplicação.

Se quiser aprofundar, comece com o guia oficial de design de índices do SQL Server e a documentação do PostgreSQL sobre tipos de índices. E se encontrar uma consulta que os índices não conseguem resolver, o problema pode estar no próprio modelo de dados.