Índices e o Otimizador

Conceito

O otimizador do MySQL é mais simples que o do Postgres — historicamente muito mais. O 8.0 fechou grande parte dessa distância (hash join, histogramas, CTE, window functions, EXPLAIN ANALYZE), mas o modelo mental continua diferente: no MySQL, você projeta o índice para a query; no Postgres, você confia mais no planejador.

O que é um índice bom aqui: a regra de ouro

Todo índice do InnoDB é uma árvore B+ ordenada. Uma consulta só usa o índice enquanto ela consegue percorrer o prefixo da ordenação. Daí a regra prática, na ordem:

1. colunas de IGUALDADE  (=, IN)
2. coluna de ORDENAÇÃO   (ORDER BY / GROUP BY)
3. coluna de FAIXA       (>, <, BETWEEN, LIKE 'x%')
4. colunas só para COBRIR (SELECT), se valer a pena

A regra crítica é a 4: assim que o índice encontra uma faixa, ele para de poder usar as colunas seguintes para filtro ou para ordenação.

-- índice (status, criado_em)
WHERE status = 'pago' ORDER BY criado_em DESC     -- ✅ igualdade + ordem: perfeito
WHERE status = 'pago' AND criado_em > '2026-01-01'-- ✅ igualdade + faixa: perfeito
WHERE criado_em > '2026-01-01' AND status = 'pago'-- ✅ mesma coisa (a ordem no WHERE não importa)

-- índice (criado_em, status)  ← ordem invertida
WHERE criado_em > '2026-01-01' AND status = 'pago'-- ⚠️ a faixa vem primeiro:
                                                  --    `status` NÃO filtra no índice,
                                                  --    só descarta depois (ICP)

Covering index: eliminar o segundo percurso

Como o índice secundário guarda a PK (2. InnoDB por Dentro), buscar colunas extras custa um percurso adicional na árvore da PK. Se todas as colunas da query estiverem no índice, esse percurso desaparece:

-- 2 percursos: idx → PK → linha
CREATE INDEX idx1 ON pedidos (cliente_id);
SELECT total, status FROM pedidos WHERE cliente_id = 42;

-- 1 percurso: "Using index" no EXPLAIN
CREATE INDEX idx2 ON pedidos (cliente_id, total, status);
SELECT total, status FROM pedidos WHERE cliente_id = 42;

Using index no Extra do EXPLAIN é o selo de covering index. É o ganho mais fácil de obter em MySQL — e o MySQL não tem INCLUDE como o Postgres, então as colunas de cobertura entram na própria chave.

As otimizações que o 8.0+ trouxe

Ler EXPLAIN

EXPLAIN SELECT ... ;

As colunas que importam, em ordem:

Valores de Extra que você precisa reconhecer:

Using index                → covering index. Ótimo.
Using index condition      → ICP ativo. Bom.
Using where                → filtro aplicado após ler a linha. Aceitável.
Using filesort             → ordenação fora do índice. Ruim em volume.
Using temporary            → tabela temporária (GROUP BY/DISTINCT). Ruim em volume.
Using join buffer (hash join) → join sem índice; ok em analítico, ruim em OLTP.

Trade-offs

Índice composto resolve mais que vários índices simples. O MySQL raramente combina dois índices (existe index merge, mas é limitado e frequentemente pior). Um índice (a, b, c) serve a, (a,b) e (a,b,c) — três índices separados servem cada um só a si. Menos índices, mais bem ordenados.

Covering index engorda o índice. Cada coluna de cobertura é copiada. Um (cliente_id, total, status, criado_em, payload) pode ficar quase do tamanho da tabela — aí você duplicou os dados e o custo de escrita.

ORDER BY ... LIMIT é onde o otimizador mais erra. Ele pode escolher o índice da ordenação (para evitar filesort) e varrer meio índice porque o filtro elimina quase tudo. O sintoma é uma query que fica mais lenta com LIMIT menor. Remédios: FORCE INDEX, reescrever com subquery, ou índice que atenda filtro e ordem juntos.

Histograma serve onde índice não cabe. Coluna de baixa cardinalidade (status, tipo) não merece índice, mas o otimizador precisa saber que 98% das linhas são 'concluido'. Histograma resolve isso de graça na escrita.

O trade-off central: o otimizador do MySQL premia quem projeta índice para a query. Isso dá mais controle e mais trabalho: quase todo problema de performance no MySQL se resolve com o índice composto correto — e quase todo excesso de índice se paga na escrita e na perda de concorrência (3. Redo, Undo e Locks).

Exemplo prático

Do problema ao índice, passo a passo

-- A query da tela
SELECT id, total, status FROM pedidos
 WHERE cliente_id = 4711 AND status IN ('pago','enviado')
 ORDER BY criado_em DESC LIMIT 20;

EXPLAIN ANALYZE SELECT ... ;
-- -> Limit: 20 row(s)  (actual time=812..812 rows=20 loops=1)
--     -> Sort: pedidos.criado_em DESC  (actual time=812..812 rows=20)
--         -> Filter: (pedidos.status in ('pago','enviado'))  (actual rows=38412)
--             -> Index lookup on pedidos using idx_cliente (cliente_id=4711)
--                (actual time=0.2..640 rows=41277)

Diagnóstico linha por linha: leu 41 mil linhas pelo índice de cliente, descartou 3 mil no filtro, ordenou 38 mil em memória e devolveu 20. O trabalho útil é 20 linhas.

-- O índice que atende igualdade + ordem + cobertura
CREATE INDEX idx_cli_status_criado ON pedidos (cliente_id, status, criado_em, total);

-- -> Limit: 20 row(s)  (actual time=0.05..0.09 rows=20 loops=1)
--     -> Index lookup on pedidos using idx_cli_status_criado
--        (cliente_id=4711, status in ('pago','enviado'))  ← Using index
--        (actual rows=20)

De 812 ms para 0,09 ms — sem Sort, sem Filter, sem acesso à PK. O ganho não veio de hardware nem de parâmetro: veio de colocar as colunas na ordem igualdade → ordenação → cobertura.

Higiene e diagnóstico

-- índices redundantes e não usados (sys schema faz o trabalho)
SELECT * FROM sys.schema_redundant_indexes;
SELECT * FROM sys.schema_unused_indexes;

-- as queries mais custosas (equivalente ao pg_stat_statements)
SELECT digest_text, count_star,
       ROUND(sum_timer_wait/1e12, 1)  AS total_s,
       ROUND(avg_timer_wait/1e9, 2)   AS media_ms,
       sum_rows_examined / NULLIF(sum_rows_sent, 0) AS examinadas_por_retornada
  FROM performance_schema.events_statements_summary_by_digest
 ORDER BY sum_timer_wait DESC LIMIT 15;

-- histograma para coluna de baixa cardinalidade sem índice
ANALYZE TABLE pedidos UPDATE HISTOGRAM ON status WITH 16 BUCKETS;

-- testar remoção de índice sem remover
ALTER TABLE pedidos ALTER INDEX idx_antigo INVISIBLE;

Como isso falha em produção

Aprofundamento aplicado

Índice composto e ordem das perguntas

Para WHERE cliente_id = ? AND criado_em >= ? ORDER BY criado_em, o índice (cliente_id, criado_em) aproxima os pedidos do mesmo cliente e ordena o intervalo. Acrescentar total pode cobrir uma projeção, mas aumenta armazenamento e custo das escritas.

Um filtro por função, conversão implícita ou collation incompatível pode impedir o caminho esperado. Antes de acrescentar outro índice, verifique os tipos dos parâmetros que o driver envia e a expressão realmente executada.

Experimento: compare a consulta que retorna id, criado_em com uma que retorna um campo grande não coberto. Resultado esperado: reconheça quando foi necessário buscar a linha no índice clusterizado, além de percorrer o secundário.

Não use um número fixo de linhas como regra universal para escolher índice. Seletividade, distribuição, cache e custo de retornar os dados mudam a decisão. A hipótese precisa ser testada com dados que representem clientes pequenos e grandes.

Referência: índices de múltiplas colunas.

Relacionado

• 2. InnoDB por Dentro
• 3. Redo, Undo e Locks
• 6. DDL Online e Operação
• 5. Índices e o Planejador
• 5. Índices
• SQL Tuning
• 7. Problema N+1

---

Parte de MOC MySQL · Optimization (docs)