Missing Index: Quando o Banco de Dados Lê a Tabela Inteira para Encontrar Uma Linha
Existe uma categoria de problema de performance que tem uma característica cruel: ele não aparece durante o desenvolvimento, não aparece nos primeiros meses de produção, e quando aparece, a aplicação já está sob pressão de usuários reais. O Missing Index é esse problema. A query funcionava perfeitamente com 10.000 registros. Com 2.000.000, ela trava.
A causa é quase sempre a mesma: o banco de dados não tem como encontrar os registros que você quer sem ler a tabela inteira. Sem índice, ele faz isso toda vez que a query roda.
O que um Índice Faz
Um índice é uma estrutura de dados separada — na maioria dos bancos relacionais, uma B-tree — que mantém os valores de uma ou mais colunas ordenados, com ponteiros para as linhas correspondentes na tabela.
Sem índice, uma query WHERE email = 'user@example.com' precisa ler cada linha da tabela, comparar o valor de email, e verificar se é o que você quer. Isso é chamado de full table scan ou sequential scan. Para uma tabela com 5.000.000 de linhas, o banco lê 5.000.000 de linhas para retornar 1.
Com um índice em email, o banco navega pela B-tree em O(log N) e chega diretamente às linhas relevantes. Para 5.000.000 de linhas, são aproximadamente 23 comparações em vez de 5.000.000.
A diferença de performance entre esses dois caminhos é a diferença entre uma query que retorna em 2ms e uma que retorna em 8 segundos — com os mesmos dados, o mesmo servidor, o mesmo SQL.
Por que o Problema Aparece Tarde
Volume de dados muda o plano de execução
O query planner do banco de dados (o componente que decide como executar uma query) toma decisões baseadas em estatísticas do volume e distribuição dos dados. Com uma tabela pequena, o planner frequentemente escolhe um sequential scan mesmo que exista um índice — porque para poucos registros, ler tudo é mais rápido do que navegar pelo índice e depois buscar as linhas.
Quando a tabela cresce, o mesmo planner recalcula e percebe que o sequential scan agora é proibitivo. Se não há índice, ele não tem escolha — usa o scan. Se havia um índice que estava sendo ignorado antes, agora ele passa a ser usado.
O resultado: a ausência de índice era invisível com 50.000 linhas. Com 5.000.000, se torna crítica.
Ambientes de desenvolvimento não replicam volume
Bancos de dados de desenvolvimento têm dezenas ou centenas de registros por tabela, criados manualmente ou via fixtures. Queries sem índice em tabelas pequenas retornam em menos de 1ms — imperceptível. Nenhum alerta, nenhum sinal.
Índices têm custo de escrita
Cada índice que existe em uma tabela precisa ser atualizado a cada INSERT, UPDATE e DELETE nas colunas indexadas. Em tabelas com alto volume de escrita, índices desnecessários degradam a escrita. Isso cria uma tensão real: você não quer indexar tudo, mas precisa indexar o certo.
Identificando o Problema
EXPLAIN ANALYZE
A ferramenta fundamental é o EXPLAIN ANALYZE (PostgreSQL) ou EXPLAIN (MySQL/MariaDB). Ele mostra o plano de execução real da query — o que o banco de dados fez, não apenas o que poderia fazer.
PostgreSQL:
sql
EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT * FROM orders WHERE customer_id = 42;Saída sem índice:
Seq Scan on orders (cost=0.00..48920.00 rows=847 width=156)
(actual time=0.043..312.847 rows=847 loops=1)
Filter: (customer_id = 42)
Rows Removed by Filter: 1999153
Buffers: shared hit=22105 read=4317
Planning Time: 0.082 ms
Execution Time: 312.921 msOs sinais de alerta aqui são Seq Scan (leu a tabela inteira), Rows Removed by Filter: 1999153 (descartou 2 milhões de linhas para encontrar 847) e Execution Time: 312.921 ms.
Saída com índice:
Index Scan using idx_orders_customer_id on orders
(cost=0.43..2847.12 rows=847 width=156)
(actual time=0.021..1.847 rows=847 loops=1)
Index Cond: (customer_id = 42)
Buffers: shared hit=854
Planning Time: 0.091 ms
Execution Time: 1.923 msIndex Scan, Buffers: shared hit=854 (leu 854 páginas em vez de 26.422), Execution Time: 1.923 ms. Mesma query, mesmos dados: 162x mais rápido.
MySQL/MariaDB:
sql
EXPLAIN SELECT * FROM orders WHERE customer_id = 42;Procure na coluna type:
ALL= full table scan = problemaindex= full index scan = melhor, mas ainda problemático em tabelas grandesref,eq_ref,const= uso eficiente de índice = correto
Coluna rows: estimativa de linhas que o banco vai examinar. Se for próximo do total da tabela, é sequential scan.
Slow Query Log
Habilite o slow query log para capturar queries que ultrapassam um threshold de tempo em produção:
PostgreSQL via pg_stat_statements:
sql
-- Habilitar a extensão (requer superuser)
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;
-- Queries mais lentas, ordenadas por tempo total
SELECT
query,
calls,
total_exec_time,
mean_exec_time,
rows
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 20;MySQL:
ini
# my.cnf
slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 0.1 -- queries acima de 100ms
log_queries_not_using_indexes = 1 -- captura queries sem índice independente do tempolog_queries_not_using_indexes é especialmente valioso: captura queries que rodam rápido hoje (tabela pequena) mas que vão degradar quando crescer.
Verificando índices existentes
PostgreSQL:
sql
-- Índices de uma tabela
SELECT
indexname,
indexdef
FROM pg_indexes
WHERE tablename = 'orders';
-- Índices não utilizados (candidatos para remoção)
SELECT
schemaname,
tablename,
indexname,
idx_scan, -- número de vezes que o índice foi usado
idx_tup_read,
idx_tup_fetch
FROM pg_stat_user_indexes
WHERE idx_scan = 0 -- nunca usado
ORDER BY schemaname, tablename;MySQL:
sql
SHOW INDEX FROM orders;
-- Índices não utilizados (via performance_schema)
SELECT
object_schema,
object_name,
index_name
FROM performance_schema.table_io_waits_summary_by_index_usage
WHERE index_name IS NOT NULL
AND count_star = 0
AND object_schema NOT IN ('mysql', 'performance_schema', 'information_schema');No ecossistema Java: Hibernate e o Plano de Execução
O Hibernate não expõe o plano de execução diretamente, mas você pode obter o SQL gerado e executar o EXPLAIN manualmente:
properties
# application.properties
spring.jpa.show-sql=true
spring.jpa.properties.hibernate.format_sql=true
logging.level.org.hibernate.orm.jdbc.bind=TRACEPara automatizar o diagnóstico em testes, use o p6spy para capturar as queries e um executor de SQL para rodar o EXPLAIN:
java
@Test
void queryDeveUsarIndice() {
// Captura o SQL gerado
String sql = "SELECT * FROM orders WHERE customer_id = ?";
// Executa EXPLAIN e verifica
String explainResult = jdbcTemplate.queryForObject(
"EXPLAIN " + sql,
new Object[]{42L},
String.class
);
assertThat(explainResult)
.doesNotContain("Seq Scan")
.contains("Index");
}Criando Índices: O Básico e os Casos Não Óbvios
Índice simples
sql
-- Coluna usada em WHERE, JOIN ON, ou ORDER BY frequentemente
CREATE INDEX idx_orders_customer_id ON orders(customer_id);
-- Em Java via Hibernate (anotação na entidade)
@Entity
@Table(name = "orders", indexes = {
@Index(name = "idx_orders_customer_id", columnList = "customer_id")
})
public class Order {
// ...
}Índice composto: ordem das colunas importa
Um índice composto (a, b, c) pode satisfazer queries que filtram por a, por a, b, ou por a, b, c. Ele não satisfaz queries que filtram apenas por b ou apenas por c. A ordem define o prefixo utilizável.
sql
-- Índice em (status, created_at)
CREATE INDEX idx_orders_status_created ON orders(status, created_at);
-- Usa o índice: filtra pelo prefixo 'status'
SELECT * FROM orders WHERE status = 'PENDING';
-- Usa o índice: filtra por ambas as colunas
SELECT * FROM orders WHERE status = 'PENDING' AND created_at > '2024-01-01';
-- NÃO usa o índice: 'status' não está no filtro
SELECT * FROM orders WHERE created_at > '2024-01-01';Regra prática para ordem das colunas em índice composto: coluna com maior seletividade primeiro (a que filtra mais registros), seguida das colunas usadas para range queries (>, <, BETWEEN).
java
// Hibernate: múltiplas colunas no índice
@Table(name = "orders", indexes = {
@Index(name = "idx_orders_status_created", columnList = "status, created_at")
})Índice parcial (PostgreSQL)
Quando você consulta frequentemente apenas um subconjunto dos dados, um índice parcial indexa apenas esse subconjunto — menor, mais rápido, menos custo de manutenção:
sql
-- 99% das queries filtram por pedidos não finalizados
-- Indexar apenas esses ao invés de todos os 10 milhões de registros
CREATE INDEX idx_orders_pending ON orders(customer_id)
WHERE status IN ('PENDING', 'PROCESSING');O índice parcial é usado automaticamente pelo planner quando a WHERE clause da query é compatível com a condição do índice.
Índice de cobertura (Covering Index)
Um índice que contém todas as colunas necessárias para satisfazer uma query — sem precisar acessar a tabela principal. O banco de dados responde inteiramente a partir do índice:
sql
-- Query frequente
SELECT id, status, total_amount FROM orders WHERE customer_id = 42;
-- Índice de cobertura: inclui as colunas do SELECT além da coluna do WHERE
CREATE INDEX idx_orders_customer_covering
ON orders(customer_id)
INCLUDE (status, total_amount); -- PostgreSQL 11+
-- MySQL: colunas do SELECT incluídas diretamente
CREATE INDEX idx_orders_customer_covering
ON orders(customer_id, status, total_amount);No plano de execução, você vê Index Only Scan (PostgreSQL) ou Using index (MySQL) — o banco nem chegou na tabela.
Índice para ORDER BY e paginação
Queries com ORDER BY sem índice na coluna de ordenação fazem um sort em memória (ou em disco) após o scan:
sql
-- Sem índice em created_at: scan + sort
SELECT * FROM orders WHERE customer_id = 42 ORDER BY created_at DESC LIMIT 20;
-- Índice composto cobre o filtro E a ordenação
CREATE INDEX idx_orders_customer_created ON orders(customer_id, created_at DESC);Isso é especialmente importante em paginação. Sem índice adequado, OFFSET 10000 LIMIT 20 lê e descarta 10.020 linhas a cada chamada.
Índice em colunas de FK (frequentemente esquecido)
Colunas de chave estrangeira são usadas constantemente em JOINs, mas muitos desenvolvedores assumem que a FK cria o índice automaticamente. No PostgreSQL, não cria. No MySQL, cria. Verifique.
sql
-- PostgreSQL: FK não cria índice automaticamente
ALTER TABLE order_items ADD CONSTRAINT fk_order FOREIGN KEY (order_id) REFERENCES orders(id);
-- O índice em order_id NÃO existe ainda
-- Crie explicitamente
CREATE INDEX idx_order_items_order_id ON order_items(order_id);java
// Hibernate: a anotação @JoinColumn não cria índice no PostgreSQL
// Adicione explicitamente:
@Entity
@Table(name = "order_items", indexes = {
@Index(name = "idx_order_items_order_id", columnList = "order_id")
})
public class OrderItem {
@ManyToOne(fetch = FetchType.LAZY)
@JoinColumn(name = "order_id")
private Order order;
}Casos Onde o Índice Existe mas Não é Usado
Criar o índice não é suficiente se a query for escrita de forma que o planner não consiga utilizá-lo. Esses são os casos mais frustrantes porque você acha que resolveu e não resolveu.
Função aplicada sobre a coluna indexada
sql
-- Índice existe em email
CREATE INDEX idx_users_email ON users(email);
-- NÃO usa o índice: função aplicada sobre a coluna
SELECT * FROM users WHERE LOWER(email) = 'user@example.com';
SELECT * FROM users WHERE DATE(created_at) = '2024-01-01';
-- Solução 1: Índice funcional
CREATE INDEX idx_users_email_lower ON users(LOWER(email));
-- Solução 2: Reescrever a query sem função na coluna indexada
SELECT * FROM users WHERE email = 'user@example.com';
-- (assumindo que emails são armazenados em lowercase)
-- Para datas:
SELECT * FROM orders WHERE created_at >= '2024-01-01' AND created_at < '2024-01-02';Em Java com Hibernate/JPQL, isso se manifesta assim:
java
// NÃO usa índice: LOWER() sobre a coluna
List<User> users = entityManager
.createQuery("""
SELECT u FROM User u
WHERE LOWER(u.email) = LOWER(:email)
""", User.class)
.setParameter("email", email)
.getResultList();
// Melhor: normalizar o dado na entrada e comparar diretamente
List<User> users = entityManager
.createQuery("SELECT u FROM User u WHERE u.email = :email", User.class)
.setParameter("email", email.toLowerCase())
.getResultList();Implicit type cast
sql
-- Coluna definida como VARCHAR, parâmetro passado como INTEGER
-- O banco precisa converter todos os valores para comparar
SELECT * FROM users WHERE phone_number = 11999887766;
-- ^ integer, coluna é varchar
-- Correto: tipo compatível
SELECT * FROM users WHERE phone_number = '11999887766';Em Java com JDBC ou JPA, type mismatches silenciosos podem acontecer se o tipo Java não mapeia diretamente para o tipo SQL da coluna.
Seletividade baixa
O planner pode optar por sequential scan mesmo com índice disponível quando a coluna tem baixa seletividade — ou seja, quando o filtro retorna uma fração grande da tabela. Se WHERE status = 'ACTIVE' retorna 90% das linhas, ler o índice e depois buscar cada linha individualmente é mais lento do que ler a tabela sequencialmente.
Isso não é um bug — é o planner tomando a decisão certa. A solução é um índice parcial (indexar apenas os casos menos comuns) ou um índice composto com uma coluna de alta seletividade.
Estatísticas desatualizadas
O planner toma decisões baseadas em estatísticas que são atualizadas periodicamente pelo autovacuum (PostgreSQL) ou pelo ANALYZE automático. Em tabelas que cresceram muito rapidamente, as estatísticas podem estar desatualizadas e o planner pode fazer escolhas ruins.
sql
-- PostgreSQL: atualizar estatísticas manualmente
ANALYZE orders;
-- Verificar quando foi o último ANALYZE
SELECT
relname,
last_analyze,
last_autoanalyze
FROM pg_stat_user_tables
WHERE relname = 'orders';Índices e Hibernate: Migrações com Flyway/Liquibase
A forma correta de gerenciar índices em projetos Java é via migration scripts versionados, não via DDL automático do Hibernate.
Flyway:
sql
-- V20240315__add_orders_customer_index.sql
CREATE INDEX CONCURRENTLY idx_orders_customer_id ON orders(customer_id);
CREATE INDEX CONCURRENTLY idx_orders_status_created ON orders(status, created_at DESC);O CONCURRENTLY no PostgreSQL cria o índice sem bloquear a tabela para leitura e escrita — essencial para criar índices em tabelas com dados em produção.
Liquibase:
xml
<changeSet id="20240315-add-orders-indexes" author="dev">
<sql>
CREATE INDEX CONCURRENTLY idx_orders_customer_id
ON orders(customer_id);
</sql>
<rollback>
DROP INDEX IF EXISTS idx_orders_customer_id;
</rollback>
</changeSet>Atenção ao hbm2ddl.auto: nunca use create, create-drop ou update em produção. O validate é seguro. Para criação de índices em produção, sempre use migrations — o Hibernate não gerencia índices adequadamente via DDL automático.
properties
# Produção: apenas valida o schema, nunca altera
spring.jpa.hibernate.ddl-auto=validateO Custo dos Índices: Quando Não Criar
Índices não são gratuitos. Cada índice adicionado em uma tabela tem custos permanentes:
Custo de escrita: cada INSERT precisa atualizar todos os índices da tabela. Cada UPDATE em uma coluna indexada precisa atualizar o índice. Em tabelas com alto volume de escrita (logs, eventos, métricas), índices desnecessários podem reduzir o throughput de escrita em 30-50%.
Custo de espaço: um índice B-tree tipicamente ocupa 20-30% do tamanho dos dados indexados. Uma tabela de 10GB com 5 índices pode ter 15-25GB adicionais de índices.
Custo de manutenção: o autovacuum precisa processar os índices além da tabela. Índices fragmentados (bloat) por alto volume de updates/deletes precisam de REINDEX periódico.
Sinais de que um índice não vale a pena: idx_scan = 0 em pg_stat_user_indexes por semanas, tabela com muito mais escrita do que leitura, coluna com cardinalidade muito baixa (booleanos, status com 2-3 valores) sem condição de índice parcial.
Quantificando o Ganho
O impacto de adicionar o índice certo é frequentemente a maior melhoria de performance que você vai encontrar em uma aplicação madura — porque é uma melhoria de complexidade algorítmica, não apenas de constante.
| Tamanho da tabela | Sem índice (Seq Scan) | Com índice (Index Scan) | Fator de melhoria |
|---|---|---|---|
| 100.000 linhas | ~8ms | ~0.3ms | 27x |
| 1.000.000 linhas | ~85ms | ~0.5ms | 170x |
| 10.000.000 linhas | ~900ms | ~0.8ms | 1.125x |
| 100.000.000 linhas | ~9.000ms | ~1.2ms | 7.500x |
Os números variam com hardware, configuração de memória e tamanho das linhas, mas o padrão é consistente: a diferença cresce com o volume porque o sequential scan é O(N) e o índice é O(log N).
Estratégia de Prevenção
Revise as FKs em PostgreSQL. Toda coluna @JoinColumn em Hibernate mapeada para PostgreSQL precisa de índice explícito. Automatize essa verificação no pipeline.
Habilite log_queries_not_using_indexes em staging. Capture queries sem índice antes de chegarem em produção com volume real.
Use EXPLAIN ANALYZE em toda query nova que envolva filtragem ou ordenação em tabelas que vão crescer. Torne isso parte do processo de code review para mudanças que tocam o banco.
Monitore pg_stat_statements em produção. Configure alertas para queries com mean_exec_time acima de 100ms ou total_exec_time crescendo de sprint para sprint.
Remova índices não utilizados regularmente. Todo trimestre, verifique pg_stat_user_indexes e remova índices com idx_scan = 0. Eles custam escrita sem beneficiar leitura.
Prefira CREATE INDEX CONCURRENTLY em produção. Um CREATE INDEX comum bloqueia a tabela. Em tabelas com tráfego, isso pode causar timeout em cascata durante o deploy.
Conclusão
Missing Index é o problema de performance mais impactante por unidade de esforço de correção. Um CREATE INDEX de 30 segundos pode transformar uma query de 8 segundos em uma de 2ms — sem mudar uma linha de código Java.
A dificuldade não é técnica, é de timing: o problema só se manifesta com volume de dados que raramente existe em desenvolvimento. A solução é antecipar: revisar FKs sem índice, habilitar log de queries sem índice em staging, e usar EXPLAIN ANALYZE como hábito antes de colocar queries novas em produção.
O EXPLAIN é o instrumento mais subutilizado no ecossistema Java/banco de dados. Desenvolvedores olham o SQL gerado pelo Hibernate e assumem que está correto porque retorna os dados certos. Mas correto e eficiente são perguntas diferentes, e o EXPLAIN ANALYZE é a única forma de responder a segunda.
Próximo da série: Connection Pool Exhaustion — quando as conexões com o banco viram o gargalo e a aplicação trava sob carga.