MySQL de 100 minutos para 40 ms com uma linha de SQL

O engenheiro Ruslan Tolkachev relata o diagnóstico de uma consulta de relatório contra inventory.stock_counts, tabela com contador AUTO_INCREMENT em 3.252.568.638 linhas (cerca de 3,2 bilhões). A consulta levava 1h43m e foi reduzida a 40 ms (cache quente) com o acréscimo de uma linha, sem mudança de schema.

Ler a consulta antes do plano

A consulta agrupa linhas de contagem de estoque por tipo de variância e resolução para a cadeia 2047 e o fornecedor 713. Uma tabela derivada (latest) calcula o MAX(counted_at) por loja usando o idioma clássico de "última linha por grupo". Lendo a consulta na ordem de execução do SQL (FROM, JOINs, WHERE, GROUP BY, SELECT, ORDER BY), o autor percebe o problema lógico: a questão se restringe a uma cadeia com poucas centenas de lojas (603), mas a tabela derivada calcula o timestamp mais recente para praticamente todas as lojas do fornecedor 713, sem conhecer a cadeia. Otimizar exige um parâmetro: o que a resposta deveria custar, o que o plano sozinho não diz.

EXPLAIN ANALYZE em réplica

O EXPLAIN simples mostra estimativas; o EXPLAIN ANALYZE executa e anota tempos reais, mas leva tanto quanto a consulta - o diagnóstico custou 1h43m29s. Deve rodar em réplica, nunca no primário, e um snapshot de leitura consistente mantido aberto atrasa o purge do InnoDB. O autor descarta timeouts no diagnóstico (inútil) e reserva SET max_execution_time = 60000; para as validações da correção, com KILL QUERY pronto em outra sessão.

No plano, uma linha concentra o tempo: um "covering index skip scan for grouping" no índice store_supplier_counted_at percorreu 491 milhões de entradas (6,14e+6 ms, cerca de 1,7 h) para materializar a tabela derivada com 6,57 milhões de lojas, das quais o join externo descartou quase tudo, ficando com 552 contagens. O resto da árvore era saudável: as buscas por chain_id, sc e cl custavam frações de milissegundo.

Por que o otimizador não corrigiu

O MySQL 8.0.22 tem "derived condition pushdown", mas ele só move condições que referenciam colunas da própria tabela derivada e constantes. Aqui o escopo da cadeia vive numa junção com outra tabela (latest.store_id = st.id, constante em st.chain_id), então não há predicado empurrável. O otimizador fez o que a consulta pedia.

Diagnóstico em duas partes

Abrindo o SHOW CREATE TABLE, o autor separa dois problemas independentes:

1. Desperdício lógico: calcular 6,57 milhões de lojas quando a pergunta precisa de 603.
2. Descasamento físico: o filtro usa supplier_id, que não lidera nenhum índice confiável. Pela regra do prefixo mais à esquerda, um índice composto só ajuda buscas que restringem suas colunas iniciais; como só supplier_id (segunda coluna) era restringido, o MySQL fez um skip scan, percorrendo cada store_id distinto e sondando o fornecedor em cada um.

Existe um índice chain_supplier_counted_at (chain_id, supplier_id, counted_at) aparentemente ideal, mas é uma armadilha: a coluna chain_id em stock_counts é DEFAULT NULL e não é populada de forma confiável. O autor recomenda tratar essa coluna fora do incidente - o índice consome disco e overhead de escrita sem servir a leituras.

A correção e a verificação

O princípio: calcular o conjunto pequeno primeiro e entregá-lo à tabela grande, restringindo a coluna líder do índice. A correção mínima (V2) adiciona uma linha à subconsulta: AND store_id IN (SELECT id FROM retail.stores WHERE chain_id = 2047). Isso transforma o skip scan em cerca de 603 buscas de índice.

A verificação segue duas perguntas em ordem: correto primeiro, rápido depois. Um candidato que filtra sc.chain_id diretamente roda em 0,08 ms, mas retorna zero linhas (coluna não populada) - rápido não implica correto. O candidato V2 roda em 79 ms e bate com a linha de base (552 contagens). A versão que entrou em produção é uma subconsulta correlacionada que parte de retail.stores e calcula o MAX(counted_at) de cada loja sob demanda, sem tabela derivada: 40 ms quente, 0,22 s a frio, cerca de 38 mil entradas de índice em vez de 491 milhões.

Quando só índice resolve e sinais de alerta

A reescrita funciona porque existe um conjunto pequeno para filtrar. Se a pergunta fosse genuinamente por fornecedor (todas as 6,57 milhões de lojas), a correção teria de ser física: um índice liderado por supplier_id cairia de ~491 milhões para ~6,6 milhões de leituras. Isso, porém, não é grátis nem reversível: horas de DDL online (gh-ost ou pt-online-schema-change), dezenas de gigabytes, overhead de escrita e risco de atraso de réplica. Reescritas de consulta são gratuitas e reversíveis; índices em tabelas de bilhões de linhas não são. Uma terceira via de longo prazo seria popular chain_id, projeto de qualidade de dados.

O autor encerra com uma tabela de sinais de alerta (tempos em notação científica, Materialize com milhões de linhas, skip scan, estimativas 100x fora, never executed) e o método condensado: entender o tamanho esperado da resposta, rodar EXPLAIN ANALYZE em réplica, achar a linha dona do tempo, diagnosticar desperdício lógico e descasamento físico separadamente, e validar correção contra uma linha de base conhecida antes de confiar na velocidade.