Otimizando Queries SQL em Sistemas de Alto Volume: Índices, EXPLAIN e Paginação

Introdução

Em sistemas de manutenção e logística, é comum ter tabelas que crescem rápido: cada equipamento gera eventos, cada evento pode abrir uma ordem de serviço, cada ordem de serviço acumula um histórico. Uma query que funciona bem em ambiente de desenvolvimento, com algumas centenas de registros, pode se tornar o gargalo do sistema em produção assim que a tabela passa da casa dos milhões de linhas. A boa notícia é que a maior parte dos problemas de performance em SQL tem causas bem conhecidas — e soluções bem estabelecidas.

1. O primeiro passo: ler o plano de execução

Antes de otimizar qualquer coisa, é preciso entender o que o banco está realmente fazendo. O comando EXPLAIN ANALYZE (PostgreSQL, MySQL) mostra o plano de execução real da query, incluindo tempo gasto em cada etapa.

EXPLAIN ANALYZE
SELECT * FROM service_orders
WHERE asset_id = 'esteira-07'
  AND status = 'ABERTA';

Seq Scan on service_orders  (cost=0.00..48291.00 rows=1 width=214)
                             (actual time=182.441..182.442 rows=1 loops=1)
  Filter: (asset_id = 'esteira-07'::text AND status = 'ABERTA'::text)
  Rows Removed by Filter: 1199998
Planning Time: 0.112 ms
Execution Time: 182.487 ms

O termo Seq Scan (sequential scan) é o alerta vermelho: o banco leu a tabela inteira, linha por linha, para encontrar o único registro que interessava. Em uma tabela de 1,2 milhão de linhas, isso custou 182ms — e vai piorar conforme a tabela cresce.

2. Índices: dizendo ao banco por onde procurar

Um índice funciona como o índice remissivo de um livro: em vez de ler página por página, o banco vai direto ao ponto. Criar um índice composto nas colunas mais usadas em filtros resolve boa parte dos casos:

CREATE INDEX idx_service_orders_asset_status
    ON service_orders (asset_id, status);

Index Scan using idx_service_orders_asset_status on service_orders
                             (cost=0.42..8.44 rows=1 width=214)
                             (actual time=0.031..0.032 rows=1 loops=1)
Planning Time: 0.098 ms
Execution Time: 0.052 ms

De 182ms para 0,05ms. A regra prática: a ordem das colunas no índice composto importa — coloque primeiro a coluna mais seletiva (que mais reduz o conjunto de resultados) ou a que aparece sozinha com mais frequência em outras queries, para que o mesmo índice sirva a mais de um caso de uso.

Cuidado com o excesso: cada índice acelera leituras, mas tem custo em toda escrita (INSERT/UPDATE/DELETE precisam manter o índice atualizado) e ocupa espaço em disco. Índice não é algo para se criar "por garantia" em toda coluna — crie com base em queries reais que o sistema executa.

3. O problema de N+1 no Hibernate/JPA

Um dos vilões silenciosos de performance em aplicações Java é o N+1: buscar uma lista de N registros e, para cada um, disparar uma query adicional para carregar um relacionamento.

List<ServiceOrder> orders = repository.findAll(); // 1 query
for (ServiceOrder order : orders) {
    order.getTechnician().getName(); // + 1 query POR ordem de serviço
}

Para 500 ordens de serviço, isso são 501 queries em vez de uma ou duas. A correção mais direta é buscar os relacionamentos já na primeira query, com JOIN FETCH:

@Query("SELECT so FROM ServiceOrder so JOIN FETCH so.technician WHERE so.status = :status")
List<ServiceOrder> findByStatusWithTechnician(@Param("status") ServiceOrderStatus status);

Em projetos maiores, vale habilitar o log de estatísticas do Hibernate (spring.jpa.properties.hibernate.generate_statistics=true) em ambiente de teste para detectar esse padrão antes que ele chegue em produção.

4. Paginação: OFFSET não escala

A forma mais comum de paginar é com LIMIT/OFFSET:

SELECT * FROM service_orders
ORDER BY created_at DESC
LIMIT 20 OFFSET 100000;

O problema: para retornar a página 5001, o banco ainda precisa percorrer e descartar as 100.000 linhas anteriores antes de chegar às 20 que interessam. Quanto mais fundo se pagina, mais lenta a query fica.

A alternativa que escala é a paginação por keyset (cursor): em vez de pular N registros, filtra a partir do último valor visto.

SELECT * FROM service_orders
WHERE created_at < :last_seen_created_at
ORDER BY created_at DESC
LIMIT 20;

Combinada com um índice em created_at, essa consulta tem custo praticamente constante, independentemente de estarmos na página 2 ou na página 5000 — porque o banco vai direto ao ponto de corte usando o índice, sem varrer nada antes dele.

5. Boas práticas gerais

Selecione só o que precisa: evite SELECT * em tabelas largas — trafegar colunas que não serão usadas custa I/O e banda de rede à toa.

Meça em dados realistas: uma query "rápida" testada com 200 linhas locais pode se comportar de forma completamente diferente em produção com 10 milhões. Sempre que possível, valide performance com um volume de dados próximo do real.

Prefira EXISTS a COUNT quando só precisa saber "existe algo?": SELECT COUNT(*) conta todas as ocorrências; EXISTS para na primeira.

Reavalie índices periodicamente: queries mudam com o tempo. Um índice útil há um ano pode estar apenas atrapalhando escritas hoje, se ninguém mais faz aquele tipo de consulta.

Conclusão

Performance de banco de dados raramente é sobre "SQL mágico" — é sobre entender o que o banco está de fato executando (via EXPLAIN), dar a ele as estruturas certas para procurar informação rapidamente (índices bem pensados) e evitar padrões que degradam com a escala, como N+1 e paginação por OFFSET. Em sistemas que crescem — como os de manutenção e ordens de serviço, onde o histórico só aumenta — esses cuidados não são luxo: são o que garante que o sistema continue respondendo rápido no milionésimo registro tanto quanto respondia no centésimo.