índices compostos em tabelas com milhões de linhas: erros que geram falhas, lentidão ou retrabalho

Um roteiro para identificar índices mal planejados, validar consultas e reduzir riscos antes de alterar tabelas grandes.

Criar um índice em uma tabela com milhões de linhas pode consumir recursos, disputar espaço com outras operações e, ao final, deixar a consulta praticamente tão lenta quanto antes. O problema costuma estar na relação entre o índice escolhido, o SQL executado e a distribuição dos dados.

Índices compostos no MySQL precisam responder a padrões de consulta concretos. A sequência das colunas, os filtros, a ordenação e o volume percorrido determinam o trabalho que o banco consegue evitar.

Neste tutorial, usaremos uma tabela fictícia de pedidos com 30 milhões de linhas. Nos exemplos, considere InnoDB, id como chave primária, cliente_id numérico e criado_em do tipo DATETIME, sem valores nulos. Os comandos são candidatos para ensaio; nenhum ganho de desempenho apresentado deve ser presumido sem medição.

Erro 1: criar índices sem mapear as consultas que precisam melhorar

Antes de propor uma alteração, identifique qual consulta precisa melhorar e por quê. Uma busca executada milhares de vezes pode causar mais impacto acumulado que um relatório lento executado ocasionalmente. A importância do fluxo para a aplicação também entra nessa decisão.

Reúna evidências pelo slow query log e pelas ferramentas de observação disponíveis no ambiente. Registre o SQL completo, os parâmetros representativos, a frequência, os filtros, o ORDER BY, o LIMIT e as colunas retornadas.

• Meça a latência observada pela aplicação e pelo banco, quando possível.
• Compare linhas examinadas com linhas efetivamente retornadas.
• Separe clientes com poucos pedidos daqueles que concentram grande volume.
• Registre esperas por bloqueios e pressão de CPU, memória e I/O.

Na tabela do exemplo, uma listagem dos pedidos recentes de um cliente busca uma pequena parcela dos registros. Já um relatório que agrega pedidos de todos os clientes durante vários anos pode precisar percorrer uma parcela extensa da tabela. Não há motivo para esperar que o mesmo índice beneficie ambos na mesma proporção.

Se a demora vier de uma transação aguardando outra, o índice pode não resolver a causa principal. O diagnóstico precisa distinguir o tempo de processamento do tempo de espera.

Para conectar esse levantamento às etapas de alteração da tabela, consulte o guia de implementação de índices compostos em tabelas com milhões de linhas.

Erro 2: escolher a ordem das colunas por uma regra simplista

A ordem das colunas no índice composto define como seus valores ficam organizados. Um índice (cliente_id, status, criado_em) permite buscas pelos prefixos iniciais (cliente_id), (cliente_id, status) e pelo conjunto completo. Esse é o princípio do prefixo à esquerda.

Filtrar somente por status não oferece o mesmo acesso direto por esses prefixos. O otimizador pode escolher outras estratégias, incluindo varreduras e, em casos específicos, skip scan; essas possibilidades não substituem o planejamento pela consulta predominante.

Considere a listagem:

SELECT id, criado_em, total
FROM pedidos
WHERE cliente_id = 123
  AND status = 'pago'
  AND criado_em >= '2026-10-01 00:00:00'
ORDER BY criado_em DESC
LIMIT 50;

O candidato (cliente_id, status, criado_em) reúne primeiro as igualdades e depois o intervalo temporal. Com os dois primeiros valores fixos, a sequência de datas também pode atender à ordenação, conforme o plano escolhido.

Já (status, criado_em, cliente_id) organiza o acesso inicialmente pelo status e pelo período. Nesse desenho, o banco pode percorrer pedidos de muitos clientes antes de selecionar os registros do cliente desejado.

Isso não significa que cliente_id deixe de ter qualquer utilidade depois do intervalo. Uma coluna posterior pode participar da filtragem pelo próprio índice, por exemplo com Index Condition Pushdown quando aplicável, ou da cobertura. Reduzir a faixa percorrida e filtrar dentro dessa faixa são benefícios diferentes.

A regra de colocar sempre a coluna mais seletiva primeiro é insuficiente. Quando várias colunas têm igualdade, a escolha também depende dos prefixos necessários em outras consultas. Avalie a distribuição dos valores, as combinações frequentes de filtros e a ordenação esperada.

Erro 3: esperar que o índice resolva filtros e ordenações incompatíveis

Um índice bem desenhado pode ser pouco aproveitado quando a expressão da consulta dificulta o acesso aos valores indexados. Um caso comum é aplicar uma função à coluna de data:

SELECT id, criado_em
FROM pedidos
WHERE cliente_id = 123
  AND DATE(criado_em) = '2026-10-01';

Para buscar esse dia, uma alternativa é comparar diretamente a coluna com um intervalo:

SELECT id, criado_em
FROM pedidos
WHERE cliente_id = 123
  AND criado_em >= '2026-10-01 00:00:00'
  AND criado_em < '2026-10-02 00:00:00';

O limite superior exclusivo inclui os horários do dia sem depender da precisão das frações de segundo. Para essa consulta, que não filtra por status, avalie (cliente_id, criado_em): a coluna status entre cliente e data no candidato anterior pode dificultar o acesso temporal pretendido.

Preserve a semântica da aplicação ao reescrever o SQL. Em colunas TIMESTAMP, confira o fuso da sessão e os limites enviados. Se a coluna aceitar nulos, considere explicitamente como a consulta deve tratá-los.

Verifique também os tipos dos parâmetros. Comparações que provocam conversões implícitas podem mudar resultados ou limitar o uso do índice; envie valores compatíveis com os tipos das colunas.

Filtros e ordenação precisam de análises próprias. Um acesso eficiente por cliente e data não garante que ORDER BY total seja atendido pelo mesmo índice. A indicação Using filesort sinaliza uma etapa de ordenação, mas não implica, por si só, uso de disco.

Da mesma forma, um índice B-tree convencional em texto não transforma LIKE '%termo%' em uma busca direta por prefixo. Busca por substring, busca por prefixo e pesquisa textual têm requisitos diferentes. Escolha a estratégia conforme o significado da busca para o usuário.

Erro 4: validar o índice apenas porque ele aparece no EXPLAIN

O campo key preenchido no EXPLAIN no MySQL mostra um índice escolhido, mas não comprova eficiência. Uma varredura extensa de índice ainda pode examinar muitos registros para devolver poucas linhas.

Comece pelo plano estimado da consulta e registre o acesso escolhido, as estimativas de linhas e as operações de ordenação. Depois, quando disponível, use EXPLAIN ANALYZE para observar a execução:

EXPLAIN ANALYZE
SELECT id, criado_em, total
FROM pedidos
WHERE cliente_id = 123
  AND status = 'pago'
  AND criado_em >= '2026-10-01 00:00:00'
ORDER BY criado_em DESC
LIMIT 50;

EXPLAIN ANALYZE executa a consulta. Faça a avaliação em ambiente controlado ou com carga operacional previamente avaliada. Nos resultados, observe tempos, linhas e repetições de cada etapa; esses valores não são todos um único total de linhas examinadas.

Para comparar antes e depois, combine o plano com a latência da aplicação e as métricas de linhas examinadas disponíveis no log ou no monitoramento. Verifique se o ganho vem de percorrer menos registros, evitar acessos adicionais à tabela ou reduzir a ordenação.

Teste clientes de baixo e alto volume, períodos curtos e extensos e diferentes status. Repita medições em condições comparáveis: cache aquecido, concorrência e recursos disponíveis podem mudar o resultado.

Uma tabela pequena de desenvolvimento raramente reproduz o custo de índices em tabelas grandes. Preserve no ensaio tanto o volume quanto a concentração de pedidos entre clientes.

Se estimativas e execução divergirem muito, investigue as estatísticas do otimizador. Avalie a atualização por ANALYZE TABLE conforme a versão e os efeitos operacionais; não trate esse comando como correção automática para qualquer plano ruim.

Erro 5: adicionar colunas demais e manter índices redundantes

Um índice de cobertura contém os valores necessários para atender uma consulta sem buscar outras colunas na estrutura principal da tabela. Isso pode reduzir acessos adicionais, mas ampliar o índice tem custos.

Na listagem inicial, acrescentar total ao candidato produziria (cliente_id, status, criado_em, total). No InnoDB, os índices secundários também armazenam a chave primária; com id como chave primária, a listagem pode encontrar seus campos nessa estrutura.

Esse benefício precisa ser medido. Uma listagem limitada a 50 registros pode economizar poucos acessos adicionais, enquanto milhões de entradas mais largas aumentam armazenamento e uso de memória. Alterações em total também passam a exigir manutenção desse índice.

Cada índice adicional tem custo em inserções e exclusões. Atualizações exigem manutenção dos índices cujos valores são afetados. Por isso, a otimização de consultas MySQL deve considerar o desempenho das escritas, além das leituras.

Antes de criar outro índice, faça o inventário:

SHOW CREATE TABLE pedidos;
SHOW INDEX FROM pedidos;

Os índices (cliente_id) e (cliente_id, status, criado_em) têm sobreposição, mas isso não autoriza remover o primeiro automaticamente. A estrutura mais estreita pode beneficiar determinados acessos, e o comportamento precisa ser confirmado na carga real.

Para identificar índices redundantes, confira ordem das colunas, prefixos textuais, unicidade e requisitos de chaves estrangeiras. Um índice UNIQUE também impõe uma regra aos dados: substituí-lo por outro com colunas adicionais pode mudar essa regra.

Quando a versão e o tipo de índice permitirem, índices invisíveis ajudam a avaliar efeitos sobre planos antes da remoção. Eles continuam ocupando espaço e sendo mantidos nas escritas; o teste não mede a economia de excluí-los.

Erro 6: tentar corrigir paginação profunda apenas com um novo índice

Um índice compatível com filtros e ordenação pode melhorar a listagem, mas um grande OFFSET ainda exige avançar pelos resultados descartados:

SELECT id, criado_em, total
FROM pedidos
WHERE cliente_id = 123
  AND status = 'pago'
ORDER BY criado_em DESC, id DESC
LIMIT 50 OFFSET 500000;

Meça o tempo em páginas progressivamente mais distantes. Se o custo cresce com o deslocamento, avalie paginação por cursor para fluxos que permitem avançar a partir do último resultado recebido.

A consulta seguinte usa o último par de data e identificador da página anterior. Os valores abaixo são ilustrativos:

SELECT id, criado_em, total
FROM pedidos
WHERE cliente_id = 123
  AND status = 'pago'
  AND (
    criado_em < '2026-10-01 15:30:00'
    OR (
      criado_em = '2026-10-01 15:30:00'
      AND id < 987654
    )
  )
ORDER BY criado_em DESC, id DESC
LIMIT 50;

O id desempata pedidos com a mesma data e torna a ordenação determinística. Preserve no cursor a precisão completa de criado_em e mantenha os mesmos filtros entre as páginas.

Avalie um índice compatível com (cliente_id, status, criado_em, id). Como a chave primária já integra os índices secundários do InnoDB, confira o índice existente e o plano antes de criar uma estrutura sobreposta.

O cursor evita o descarte por deslocamento, mas não oferece salto direto para uma página arbitrária sem mecanismos adicionais. Mudanças concorrentes em datas ou status também podem causar omissões ou repetições entre requisições. Defina a consistência necessária para o produto.

Erro 7: criar o índice em produção sem estimar o impacto da operação

A criação de índices em produção é uma operação sobre dados existentes. Na tabela fictícia de 30 milhões de pedidos, é preciso estimar duração, espaço adicional e consumo de CPU e I/O com um ensaio representativo.

Confirme a versão do MySQL, o mecanismo de armazenamento e o suporte ao algoritmo pretendido. Para adicionar um índice secundário convencional em InnoDB, este é um exemplo a validar primeiro no ensaio:

ALTER TABLE pedidos
  ADD INDEX idx_pedidos_cliente_status_data
    (cliente_id, status, criado_em),
  ALGORITHM=INPLACE,
  LOCK=NONE;

As opções explícitas exigem o algoritmo e o nível de concorrência solicitados; se a operação não oferecer esse suporte, o comando falha. Isso permite descobrir incompatibilidades durante o ensaio. INPLACE não significa criação instantânea nem ausência de trabalho sobre os registros.

DDL online ainda pode envolver bloqueios de metadados. Transações longas podem atrasar a operação, e a espera pode afetar outras sessões. Examine transações abertas e monitore os bloqueios durante a implantação.

• Reserve margem para o índice novo, arquivos temporários e crescimento normal dos dados.
• Meça os efeitos da construção sob escritas concorrentes.
• Defina limites de latência da aplicação e saturação de recursos.
• Acompanhe atraso e carga das réplicas, quando houver replicação.
• Escolha uma janela com responsáveis e critérios de interrupção definidos.

A reversão também precisa ser planejada. Cancelar uma construção pode exigir tempo de limpeza; remover um índice pronto é outra operação de DDL. Se a mudança incluir a retirada de índices anteriores, preserve suas definições e considere o custo de reconstrução.

Não remova um índice antigo apenas porque o novo melhorou uma consulta. Primeiro confirme que os demais acessos relevantes continuam atendidos.

Um roteiro para testar, implantar e revisar índices compostos

Um processo documentado ajuda desenvolvimento e operação a avaliar a mesma hipótese. Para o exemplo de pedidos, a hipótese é que igualdades por cliente e status, seguidas pela data, reduzam o trabalho da listagem recente.

1. Selecione a consulta: registre SQL, parâmetros, frequência e importância do fluxo.
2. Estabeleça a situação inicial: salve plano, latências, linhas examinadas e condições do teste.
3. Inventarie os índices: confira sobreposições, restrições e possíveis candidatos existentes.
4. Teste a hipótese: use dados representativos e varie volume por cliente, status e período.
5. Avalie o custo completo: compare leituras, escritas, armazenamento e pressão de recursos.
6. Prepare a implantação: documente janela, limites de sucesso, interrupção e reversão.
7. Acompanhe o resultado: compare a carga real após a mudança e revise quando as consultas evoluírem.

Use uma ficha de decisão sem preencher resultados que ainda não foram medidos:

Registre também quais consultas justificam manter cada índice. Essa informação reduz retrabalho quando uma listagem muda, um relatório desaparece ou o volume de um cliente cresce.

Escolha uma consulta crítica e aplique o roteiro: registre o plano atual, teste um índice candidato e compare o desempenho com dados representativos antes da implantação. Para aprofundar o diagnóstico, explore os conteúdos relacionados do blog Max Alex.