Como implementar índices compostos em tabelas com milhões de linhas: guia prático para produção

Em tabelas com milhões de registros, um índice criado sem critério pode consumir espaço, aumentar o custo de escrita e ainda não resolver a consulta lenta. Por outro lado, um índice composto alinhado ao padrão real de filtros, junções e ordenação reduz drasticamente as linhas examinadas pelo MySQL.

Este guia mostra como decidir quando usar índices compostos MySQL, definir a ordem das colunas, validar o plano de execução e implantar a alteração com segurança em produção.

Por que índices compostos se tornam críticos em tabelas com milhões de linhas

Uma consulta que percorre uma tabela inteira pode parecer aceitável em uma base pequena, mas se torna cara quando a tabela cresce. O MySQL precisa ler páginas de dados, aplicar filtros e possivelmente ordenar resultados antes de devolver poucas linhas ao aplicativo.

Isso é comum em consultas com vários critérios. Considere uma tabela orders com pedidos de diversos clientes:

SELECT id, total, created_at
FROM orders
WHERE tenant_id = 42
  AND status = 'paid'
ORDER BY created_at DESC
LIMIT 50;

Um índice simples apenas em tenant_id pode reduzir parte do trabalho, mas ainda deixa o banco filtrar status e ordenar muitos pedidos. Um índice composto em (tenant_id, status, created_at) fornece ao otimizador uma estrutura mais próxima da forma como a consulta é executada.

O benefício não é automático: cada índice adicional ocupa disco e precisa ser atualizado em INSERT, UPDATE e DELETE. O objetivo é acelerar consultas relevantes sem transformar a tabela em uma coleção de índices redundantes.

Como identificar as consultas que realmente precisam de um índice composto

Não comece pela estrutura da tabela; comece pelas consultas que causam impacto mensurável. O melhor candidato costuma combinar alta frequência, tempo elevado, grande quantidade de linhas examinadas e importância direta para uma funcionalidade crítica.

Ative e analise o slow query log para localizar comandos recorrentes. Em seguida, agrupe consultas com a mesma forma, compare o tempo médio e os percentis de latência e observe quantas linhas são lidas para retornar o resultado esperado.

O monitoramento do banco de dados ajuda a relacionar esses sinais com picos de CPU, I/O, conexões e degradação percebida pelo usuário. Uma consulta de relatório executada uma vez ao dia não deve necessariamente ter a mesma prioridade de uma consulta chamada milhares de vezes por hora.

Liste, para cada candidata, os predicados de WHERE, as colunas de JOIN, a ordenação, o agrupamento e as colunas retornadas. Esse mapa evita criar índices apenas porque uma coluna aparece em uma consulta.

SELECT id, created_at, amount
FROM transactions
WHERE account_id = ?
  AND status = ?
  AND created_at >= ?
ORDER BY created_at DESC
LIMIT 100;

Nesse caso, há um padrão claro de igualdade em account_id e status, seguido por intervalo e ordenação em created_at. A consulta é uma candidata forte a um índice composto, desde que o plano e os testes confirmem a hipótese.

A regra da coluna mais à esquerda e a ordem correta do índice

Índices B-tree compostos são organizados pela sequência de colunas definida na criação. Um índice em (tenant_id, status, created_at) é eficiente para buscas que começam por tenant_id, e pode avançar para as colunas seguintes quando o predicado permitir.

Essa é a regra do prefixo à esquerda: o banco normalmente aproveita melhor os prefixos iniciais do índice. O índice anterior atende bem a filtros por tenant_id, por tenant_id e status, ou pelos três campos. Em geral, não é uma boa solução para uma consulta filtrada somente por status.

Uma regra prática útil é posicionar primeiro as colunas usadas com igualdade e, depois, as usadas em intervalos, como >, <, BETWEEN ou datas a partir de um ponto. Para a consulta abaixo, a ordem costuma ser adequada:

CREATE INDEX idx_orders_tenant_status_created
ON orders (tenant_id, status, created_at);

Não transforme essa regra em fórmula rígida. A seletividade dos filtros, a frequência das consultas e a necessidade de evitar ordenação também influenciam a decisão. Uma coluna de baixa cardinalidade, como um status com poucos valores, pode ser pouco útil sozinha, mas bastante valiosa quando vem após um identificador de conta ou tenant.

Também observe o ORDER BY. Quando os filtros anteriores são compatíveis com o índice, o MySQL pode percorrer os registros já ordenados e evitar uma etapa extra de ordenação. Se houver intervalo em uma coluna anterior, porém, a capacidade de usar as partes posteriores do índice para ordenar pode ser limitada.

Como criar índices compostos sem interromper a produção

Criar um índice em uma tabela grande é uma operação de capacidade, não apenas uma instrução SQL. Antes da mudança, confirme a versão do MySQL, o engine utilizado, o comportamento de DDL online suportado e o espaço livre necessário para a nova estrutura.

Em InnoDB, muitas alterações podem ser feitas online, mas “online” não significa ausência total de impacto. A criação ainda pode gerar I/O intenso, aumentar a pressão sobre o buffer pool, competir com gravações e exigir locks curtos em etapas específicas. O comportamento exato depende da versão e da alteração executada.

Teste primeiro em um ambiente de homologação com volume, distribuição de dados e carga próximos da produção. Registre a duração, consumo de disco, atividade de escrita, impacto nas réplicas e plano de reversão.

ALTER TABLE orders
  ADD INDEX idx_orders_tenant_status_created (tenant_id, status, created_at);

Planeje a execução para um período de menor risco e mantenha observabilidade ativa. Acompanhe locks, tempo das transações, uso de CPU, latência das consultas e atraso de replicação, quando houver. Um backup validado é parte da preparação operacional, embora remover um índice criado indevidamente normalmente seja uma reversão direta:

ALTER TABLE orders
  DROP INDEX idx_orders_tenant_status_created;

Não use opções de algoritmo ou lock sem confirmar que são compatíveis com sua versão e seu ambiente. Em bases críticas, ferramentas de migração online ou processos de alteração gradual podem ser necessários quando a janela de manutenção é insuficiente.

Validando o ganho com EXPLAIN, EXPLAIN ANALYZE e métricas reais

Um índice só é bem-sucedido quando melhora uma consulta real sem criar custo operacional desproporcional. Compare o plano antes e depois da alteração usando os mesmos parâmetros representativos.

EXPLAIN
SELECT id, total, created_at
FROM orders
WHERE tenant_id = 42
  AND status = 'paid'
ORDER BY created_at DESC
LIMIT 50;

Verifique principalmente possible_keys, key, rows e Extra. O campo key indica o índice escolhido; rows é uma estimativa de linhas examinadas. Uma queda expressiva nessa estimativa é um bom sinal, mas precisa ser confirmada pela execução.

Quando disponível na sua versão, EXPLAIN ANALYZE acrescenta métricas observadas durante a execução. Use-o com cuidado em consultas custosas, pois ele executa o comando. Compare também o tempo total, as linhas retornadas e a consistência do resultado.

Uma análise de performance SQL deve ir além de uma única execução local. Após o deploy, acompanhe p95 e p99, throughput, linhas examinadas, uso de CPU, I/O e possíveis regressões nas operações de escrita.

Se o MySQL não escolher o novo índice, não force seu uso imediatamente. Investigue estatísticas desatualizadas, filtros pouco seletivos, diferenças entre parâmetros testados e reais, conversões implícitas de tipo e condições que impedem o aproveitamento do índice.

Índices covering: quando incluir colunas para evitar leituras na tabela

Um índice covering contém todas as colunas necessárias para filtrar e retornar uma consulta. Assim, o MySQL pode responder diretamente a partir das páginas do índice, reduzindo leituras adicionais na tabela.

SELECT id, created_at
FROM orders
WHERE tenant_id = ?
  AND status = ?;

Para esse padrão, um índice como (tenant_id, status, id, created_at) pode cobrir a consulta. Em determinados planos, o campo Extra exibirá Using index, sinalizando que os dados necessários foram atendidos pelo índice.

Esse recurso é especialmente útil em leituras muito frequentes e seletivas. Porém, incluir colunas apenas para cobrir consultas aumenta o tamanho do índice e o custo de manutenção em cada gravação. Evite usar um covering index como solução genérica para SELECT * ou para colunas grandes e pouco utilizadas.

Comece com o menor índice que resolva o acesso. Só acrescente colunas de retorno após medir que o acesso à tabela ainda é o gargalo e que o ganho compensa o custo de armazenamento e escrita.

Erros comuns ao criar índices compostos em bases grandes

O erro mais comum é definir a ordem das colunas pela intuição ou pela ordem em que aparecem no modelo de dados. O índice deve refletir os predicados e a forma de acesso das consultas prioritárias.

Outro problema recorrente é manter índices redundantes. Depois de criar (tenant_id, status), o índice isolado em (tenant_id) pode se tornar dispensável para algumas cargas, pois o composto já possui esse prefixo. Antes de removê-lo, confirme todos os padrões de consulta e valide em ambiente controlado.

Funções e transformações sobre a coluna filtrada também podem impedir o uso eficiente do índice. Por exemplo, aplicar uma função em created_at no WHERE pode dificultar uma busca por intervalo. Sempre que possível, converta o valor de comparação e preserve a coluna indexada sem transformação.

Também é inadequado testar com poucas linhas e concluir que o desenho funcionará em produção. Cardinalidade, distribuição desigual de valores, concorrência e cache mudam o comportamento do plano. Use dados e parâmetros que representem os casos mais frequentes e os mais caros.

Por fim, não ignore o lado da escrita. Cada índice adicional precisa ser mantido. Em tabelas com alto volume de inserções ou atualizações, o ganho de leitura deve justificar a latência e o I/O extras introduzidos nas gravações.

Checklist de implementação para índices compostos em produção

1. Mapeie a consulta completa: filtros, joins, ordenação, agrupamento e colunas retornadas.
2. Priorize consultas por frequência, latência, linhas examinadas e impacto no negócio.
3. Capture um baseline com plano de execução e métricas antes da mudança.
4. Projete a ordem do índice com base em igualdade, intervalos, seletividade e ordenação.
5. Verifique índices existentes para evitar duplicidade ou sobreposição desnecessária.
6. Teste em um ambiente representativo, incluindo volume de dados e carga de escrita.
7. Confirme espaço em disco, compatibilidade de DDL e estratégia de implantação.
8. Execute com monitoramento de locks, I/O, CPU, latência e replicação.
9. Compare métricas após a implantação e mantenha um plano de reversão documentado.
10. Revise a necessidade de índices antigos depois da estabilização, sem removê-los por suposição.

Índices compostos não são um ajuste isolado: são parte de uma prática contínua de observação, desenho de consultas e gestão de capacidade. Quando baseados em evidências e validados em condições realistas, eles tornam tabelas grandes mais previsíveis e sustentáveis.

Conclusão

Para melhorar consultas em tabelas com milhões de registros, identifique primeiro os gargalos reais. Em seguida, projete o índice conforme a ordem efetiva dos filtros e valide o resultado com EXPLAIN, métricas de execução e acompanhamento após o deploy.

Sua aplicação sofre com consultas lentas em tabelas grandes? Avalie as consultas críticas, valide os índices compostos com dados reais e conte com uma análise especializada para implementar melhorias seguras no MySQL em produção.