No desenvolvimento de sistemas escaláveis, a recuperação eficiente de dados muitas vezes exige a filtragem por múltiplas colunas simultaneamente. Para otimizar esse processo, utilizamos índices compostos (ou conjuntos). Um conceito fundamental para dominar essa ferramenta é a Regra do Prefixo à Esquerda (Leftmost Prefix Rule).

Em um índice composto definido como (coluna_A, coluna_B, coluna_C), o MySQL só conseguirá utilizar a coluna_C se a coluna_A e a coluna_B estiverem presentes na cláusula de busca. Da mesma forma, a coluna_B depende da presença da coluna_A. A ordem de definição do índice dita como os dados são organizados e, consequentemente, como podem ser buscados.

Estrutura de Teste e Preparação

Para ilustrar esses conceitos, vamos criar uma tabela de logística simulando o registro de movimentação de produtos.

CREATE TABLE `logistica_vendas` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `unidade_id` TINYINT DEFAULT NULL,
  `setor_id` SMALLINT DEFAULT NULL,
  `produto_id` MEDIUMINT DEFAULT NULL,
  `quantidade` INT DEFAULT NULL,
  `valor_total` DECIMAL(10,2) DEFAULT NULL,
  `data_registro` DATETIME DEFAULT CURRENT_TIMESTAMP,
  `observacao` VARCHAR(100) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB;

-- Criação do índice composto
CREATE INDEX idx_unidade_setor_produto ON logistica_vendas(`unidade_id`, `setor_id`, `produto_id`);

A ordenação interna deste índice funciona de forma hierárquica: os dados são ordenados primeiro por unidade_id. Para valores iguais de unidade_id, os dados são ordenados por setor_id, e assim suecssivamente.

Cenário 1: Uso Total do Índice

Quando fornecemos todas as colunas do índice na cláusula WHERE, o otimizador do MySQL consegue identificar e aplicar o índice completo, independentemente da ordem em que os campos aparecem no SQL, pois ele reorganiza a consulta internamente.

-- O otimizador utiliza as três colunas do índice
EXPLAIN SELECT * FROM logistica_vendas 
WHERE setor_id = 45 AND produto_id = 1020 AND unidade_id = 2;

-- Resultado esperado no EXPLAIN:
-- key: idx_unidade_setor_produto
-- key_len: 9 (soma do tamanho de tinyint, smallint e mediumint)

Cenário 2: Impacto da Ordenação (ORDER BY)

O uso de índices compostos é extremamente eficaz para evitar operações de filesort, que consomem muito processamento.

-- Utiliza o índice para filtragem e para a ordenação de produto_id
EXPLAIN SELECT * FROM logistica_vendas 
WHERE unidade_id = 2 AND setor_id = 45 
ORDER BY produto_id;

-- Se tentarmos ordenar por uma coluna fora do índice, o MySQL usará "Using filesort"
EXPLAIN SELECT * FROM logistica_vendas 
WHERE unidade_id = 2 AND setor_id = 45 
ORDER BY valor_total;

Cenário 3: Quebra da Regra do Prefixo

Se a consulta pular a primeira coluna do índice, o motor de busca não conseguirá utilizar a estrutura de árvore do índice de forma eficiente, resultando geralmente em um Full Table Scan.

-- O índice NÃO será utilizado porque unidade_id (o prefixo) está ausente
EXPLAIN SELECT * FROM logistica_vendas 
WHERE setor_id = 45 AND produto_id = 1020;

-- Tipo de busca: ALL

Cenário 4: Consultas de Intervalo (Range Scans)

O uso de operadores de intervalo como >, < ou BETWEEN em uma coluna do meio do índice pode impedir que as colunas subsequentes sejam filtradas pelo índice.

-- Unidade_id e setor_id usam o índice, mas produto_id pode ser afetado 
-- pela desordem causada pelo range em setor_id
EXPLAIN SELECT * FROM logistica_vendas 
WHERE unidade_id = 2 AND setor_id > 10 AND produto_id = 1020;

Em versões modernas do MySQL, recursos como Index Condition Pushdown (ICP) mitigam esse problema, filtrando os dados diretamente na camada do motor de armazenamento (storage engine) antes de retornar os resultados para o servidor SQL.

Índice de Cobertura (Covering Index)

Um índice de cobertura ocorre quando todas as colunas solicitadas no SELECT estão presentes no próprio índice. Isso elimina a necessidade de acessar a tabela original (operação de bookmark lookup ou re-table), reduzindo drasticamente o I/O de disco.

-- Selecionando apenas as colunas que compõem o índice
EXPLAIN SELECT unidade_id, setor_id, produto_id 
FROM logistica_vendas 
ORDER BY unidade_id, setor_id, produto_id;

-- Extra: Using index (indica que não houve acesso à tabela física, apenas ao índice)

Diretrizes para Criação de Índices Compostos

• Cardinalidade: Posicione as colunas com maior seletividade (maior número de valores únicos) à esquerda do índice para filtrar mais dados rapidamente.
• Economia: Um índice composto (A, B, C) substitui a necessidade de índices individuais para (A) e (A, B).
• Evite o excesso: Índices ocupam espaço em disco e aumentam o tempo de processamento em operações de INSERT, UPDATE e DELETE.
• Seletividade: Evite usar colunas de baixa cardinalidade (como 'status' ou 'sexo') sozinhas no início de um índice composto, a menos que façam parte frequente de buscas combinadas.