Índices SQL — Por que sua consulta é lenta e o que realmente fazer sobre isso

Sua consulta leva 2ms no localhost com 500 linhas. Na produção com 3 milhões de linhas, leva 9 segundos e seus usuários estão olhando para um spinner. A consulta em si provavelmente está boa — o esquema não. Você precisa de um índice e precisa entender porquê então você para adivinhar a próxima vez que isso acontecer.

O que o banco de dados faz sem um índice

Um índice B-tree é uma cópia ordenada de uma coluna (ou colunas), armazenada em uma árvore balanceada que o banco de dados pode buscar binariamente em tempo O(log n). Sem um índice, o banco de dados realiza escaneamento completo da tabela — ele lê cada linha para encontrar correspondências. Com 3 milhões de linhas, isso significa 3 milhões de leituras de linhas. Com um índice na coluna correta, é talvez 20 a 30 navegações em nós da árvore para encontrar as linhas correspondentes.

Pense nisso como um livro telefônico. Encontrar “Smith, John” em ordem alfabética leva segundos. Encontrar todas as pessoas chamadas John lendo cada entrada a partir da página 1 leva para sempre. Um índice é a ordenação alfabética — o banco de dados usa isso para pular diretamente para as linhas relevantes, em vez de escanear tudo.

Uma coisa importante: índices existem no disco ao lado da sua tabela. O banco de dados os mantém automaticamente durante as inserções, atualizações e exclusões. Isso é gratuito para leituras, mas custa algo em cada INSERT, UPDATE e DELETE — por isso você não pode simplesmente indexar tudo.

Identificar o problema com EXPLAIN

Antes de tocar em qualquer coisa, execute EXPLAIN na consulta lenta. Aqui está o que você está procurando no MySQL:

EXPLAIN SELECT * FROM orders WHERE customer_id = 12345;

+----+-------------+--------+------+---------------+------+---------+------+---------+-------------+
| id | select_type | table  | type | possible_keys | key  | key_len | ref  | rows    | Extra       |
+----+-------------+--------+------+---------------+------+---------+------+---------+-------------+
|  1 | SIMPLE      | orders | ALL  | NULL          | NULL | NULL    | NULL | 2847391 | Using where |
+----+-------------+--------+------+---------------+------+---------+------+---------+-------------+

Dois campos que importam:

• type: ALL — escaneamento completo da tabela. Quase sempre ruim uma vez que sua tabela tenha mais de ~100k linhas.
• rows: 2847391 — o otimizador estima que examinará 2,8 milhões de linhas para retornar seus resultados.

Agora adicione o índice e execute novamente:

CREATE INDEX idx_orders_customer_id ON orders (customer_id);

EXPLAIN SELECT * FROM orders WHERE customer_id = 12345;

+----+-------------+--------+------+------------------------+------------------------+---------+-------+------+-------+
| id | select_type | table  | type | possible_keys          | key                    | key_len | ref   | rows | Extra |
+----+-------------+--------+------+------------------------+------------------------+---------+-------+------+-------+
|  1 | SIMPLE      | orders | ref  | idx_orders_customer_id | idx_orders_customer_id | 4       | const |   12 |       |
+----+-------------+--------+------+------------------------+------------------------+---------+-------+------+-------+

type: ref e rows: 12. Isso é a solução. 2,8 milhões de linhas examinadas → 12.

No PostgreSQL, use EXPLAIN ANALYZE (que realmente executa a consulta) para obter números reais de tempo:

-- Before index
EXPLAIN ANALYZE SELECT * FROM orders WHERE customer_id = 12345;

Seq Scan on orders  (cost=0.00..89432.00 rows=14 width=156)
                    (actual time=142.341..8934.221 rows=12 loops=1)
  Filter: (customer_id = 12345)
  Rows Removed by Filter: 2847379
Planning Time: 0.4 ms
Execution Time: 8934.8 ms

-- After: CREATE INDEX idx_orders_customer_id ON orders (customer_id);
EXPLAIN ANALYZE SELECT * FROM orders WHERE customer_id = 12345;

Index Scan using idx_orders_customer_id on orders
                    (cost=0.43..92.15 rows=12 width=156)
                    (actual time=0.042..0.198 rows=12 loops=1)
  Index Cond: (customer_id = 12345)
Planning Time: 0.5 ms
Execution Time: 0.3 ms

8934ms → 0,3ms. Isso não é um exemplo escolhido de forma arbitrária — é o impacto realista de um único índice faltando em uma tabela com alguns milhões de linhas.

Índices compostos e a regra do prefixo esquerdo

Uma vez que você tenha múltiplas colunas na cláusula WHERE, você precisará de índices compostos — e a ordem das colunas importa.

SELECT * FROM orders
WHERE customer_id = 12345
  AND status = 'pending'
  AND created_at > '2024-01-01';

O regra do prefixo esquerdo: o banco de dados usa um índice composto começando pela coluna mais à esquerda e avança para a direita, mas deixa de ser útil assim que atinge uma lacuna. Dado um índice em (a, b, c):

• WHERE a = 1 — usa o índice
• WHERE a = 1 AND b = 2 — usa ambas as colunas do índice
• WHERE a = 1 AND b = 2 AND c = 3 — usa todo o índice
• WHERE b = 2 — não usa o índice (coluna a ignorada)
• WHERE a = 1 AND c = 3 — usa apenas a parte a, ignora c

A regra prática de ordenação: condições de igualdade primeiro, condições de intervalo no final. Predicados de intervalo (>, <, BETWEEN, LIKE 'prefix%') quebram o prefixo útil para colunas subsequentes. Assim:

-- Good: equality columns first, range column last
CREATE INDEX idx_orders_compound ON orders (customer_id, status, created_at);

-- This query uses all three columns of the index:
SELECT * FROM orders
WHERE customer_id = 12345   -- equality, goes first
  AND status = 'pending'    -- equality, goes second
  AND created_at > '2024-01-01'; -- range, goes last

-- Bad ordering for this query: range column in the middle
-- CREATE INDEX idx_bad ON orders (customer_id, created_at, status);
-- The status filter won't use the index because created_at is a range predicate

Também pense sobre a seleção: coloque a coluna mais seletiva (menos valores duplicados) mais à frente no índice. Se customer_id tem 50.000 valores distintos e status tem 3, customer_id deveria vir primeiro — reduz o conjunto de resultados mais rapidamente.

Quando índices pioram as coisas

Índices não são gratuitos. Cada índice custa desempenho de escrita e espaço em disco. O banco de dados atualiza cada índice em cada INSERT, UPDATE e DELETE na tabela. Em tabelas com alto volume de escrita, excessos de índices prejudicam realmente o throughput. Aqui estão os casos em que índices falham ou se tornam prejudiciais:

• Colunas de baixa cardinalidade. Um índice em um booleano ou em uma status coluna com 3 valores distintos é muitas vezes ignorado pelo otimizador. Se uma consulta retornar mais de ~10 a 15% de linhas via índice, o banco de dados geralmente prefere um escaneamento completo da tabela — o custo de busca no índice não vale a pena. Verifique com EXPLAIN se o índice realmente está sendo usado.
• Tabelas com alto volume de escrita. Tabelas de log, tabelas de eventos, tabelas de auditoria — seja conservador. Uma tabela com 10 índices e milhões de escritas por hora paga 10x de sobrecarga de escrita nessas índices.
• Índices não utilizados. No PostgreSQL, pg_stat_user_indexes mostra idx_scan — quantas vezes cada índice foi usado desde a última atualização de estatísticas. Um índice com zero escaneios é apenas sobrecarga. O MySQL acompanha isso via Performance Schema (sys.schema_unused_indexes no MySQL 5.7+).
• O otimizador adivinhando errado. Os planejadores de consultas não estão sempre corretos. Às vezes, eles escolhem um índice mais lento do que um escaneamento completo. EXPLAIN ANALYZE irá capturar isso — compare o número estimado com o número real de linhas. No MySQL você pode indicar com USE INDEX (idx_name); no PostgreSQL você pode testar com SET enable_seqscan = off (não deixe isso em produção).

Índice ou reescrita? O verdadeiro trade-off

Às vezes o índice é a solução. Às vezes a consulta é o problema e o índice apenas oculta isso. Alguns padrões em que você deve reescrever a consulta em vez de (ou em adição a) adicionar um índice:

Funções em colunas indexadas

-- Will NOT use an index on created_at — the function result isn't indexed:
SELECT * FROM orders WHERE YEAR(created_at) = 2024;

-- Rewrite to use the index:
SELECT * FROM orders
WHERE created_at >= '2024-01-01'
  AND created_at < '2025-01-01';

Envolver uma coluna em qualquer função impede o uso padrão de índices. O índice armazena valores de created_at, não valores de YEAR(created_at) — essas são coisas diferentes. O PostgreSQL suporta índices de expressão (CREATE INDEX ON orders (YEAR(created_at))) como solução alternativa, mas a reescrita da consulta é mais limpa e funciona em todos os lugares.

Índices cobrindo rotas de leitura intensas

A índice cobrindo inclui todas as colunas que a consulta precisa, então o banco de dados nunca precisa ler as linhas reais da tabela — ele responde inteiramente a partir do índice. Isso pode ser significativamente mais rápido do que um índice regular em tabelas com alto volume de leitura:

-- Query that only needs id, status, total:
SELECT id, status, total FROM orders WHERE customer_id = 12345;

-- Regular index: database finds matching rows via index, then reads each row from table
CREATE INDEX idx_orders_customer ON orders (customer_id);

-- Covering index: database reads everything it needs from the index alone
CREATE INDEX idx_orders_cover ON orders (customer_id, id, status, total);
-- Note: customer_id first (the WHERE column), then the SELECT columns

Padrões LIKE

-- Uses the index (prefix match — database knows where to start):
SELECT * FROM users WHERE email LIKE 'john%';

-- Does NOT use the index (leading wildcard — database doesn't know where to start):
SELECT * FROM users WHERE email LIKE '%@gmail.com';

Os sufixos e substrings não podem ser corrigidos com índices B-tree. Se você precisa de busca por sufixo ou substring, você precisa de um índice de texto completo (FULLTEXT no MySQL, GIN com pg_trgm no PostgreSQL) ou de um sistema de busca externo como Elasticsearch.

N+1 queries

Nenhum índice corrige um problema de N+1. Se seu ORM faz uma consulta para buscar 200 pedidos, seguida por uma consulta separada para cada pedido para obter o nome do cliente, você tem 201 consultas. Cada uma pode ser rápida. O problema é o volume. Corrija com um JOIN ou uma consulta em lote — nenhum número de índices muda o número de consultas.

Uma coisa mais: limpe a consulta antes de analisá-la

Antes de executar EXPLAIN em uma consulta lenta, ajuda ter a SQL formatada claramente. Uma linha de 200 caracteres sem indentação é mais difícil de entender do que uma consulta estruturada. O SQL Formatter em IO Tools trata MySQL, PostgreSQL, SQL Server e outros dialectos — útil quando você copia uma consulta de logs ou de saída de ORM e precisa ver sua estrutura de forma clara.

A lista prática de verificação

• Execute EXPLAIN antes de adicionar qualquer índice. Confirme primeiro o problema.
• Procure por type: ALL (MySQL) ou Seq Scan (PostgreSQL) — geralmente onde o problema está.
• Verifique a rows estimativa em EXPLAIN. Milhões de linhas lidas para retornar dezenas = índice faltando.
• Em índices compostos: condições de igualdade primeiro, condições de intervalo no final.
• Colunas de maior seleção vêm mais à frente em um índice composto.
• Não indexe colunas de baixa cardinalidade sem verificar se o otimizador realmente usa o índice.
• Audite índices não utilizados regularmente: pg_stat_user_indexes (PostgreSQL), sys.schema_unused_indexes (MySQL).
• Se uma função envolve sua coluna em WHERE, reescreva a consulta em vez de indexar a função.
• Para rotas de leitura intensas com um pequeno conjunto de colunas, considere índices cobrindo.
• N+1 é um problema de arquitetura de consulta, não de índice.

Você também pode gostar