Posicionamento de coluna de alta cardinalidade em índices compostos com consultas de intervalo

Ao consultar uma tabela com um índice composto envolvendo uma condição de intervalo, o posicionamento de colunas dentro do índice podem impactar significativamente o desempenho.

Considere os arquivos da tabela com uma chave primária (did, nome do arquivo) e dois índices compostos: INDEX(filetime, ext) e INDEX(ext, tempo de arquivo). Ambos os índices contêm a coluna filetime, que tem cardinalidade maior que ext.

A consulta:

WHERE ext = '...'
  AND filetime BETWEEN ... AND ...

requer acesso a dados com base em ext e filetime. Surge a pergunta: qual índice é ideal para tal consulta?

Análise

Para determinar o índice ideal, podemos usar FORCE INDEX e examinar os planos de execução:

-- Force range on filetime first
FORCE INDEX(fe) SELECT COUNT(*), AVG(fsize)
FROM files
WHERE ext = 'gif'
  AND filetime >= '2015-01-01'
  AND filetime = '2015-01-01'
  AND filetime 
A saída mostra que INDEX(ext, filetime) (ef) tem uma contagem de linhas significativamente menor, indicando uma verificação mais eficiente.
Optimizer Trace
Para analisar melhor o comportamento do otimizador, podemos usar o otimizador trace:
SELECT explain_format = 'JSON';

SELECT COUNT(*), AVG(fsize)
FROM files
WHERE ext = 'gif'
  AND filetime >= '2015-01-01'
  AND filetime 
O rastreamento revela que o otimizador escolhe INDEX(ext, filetime) porque pode usar ambas as colunas do índice para filtrar e buscar dados. Por outro lado, INDEX(filetime, ext) só pode usar a primeira coluna (filetime) para filtragem.
Conclusões
Com base na análise, as seguintes conclusões podem ser desenhado:

• Para índices compostos usados ​​em consultas de intervalo, a coluna envolvida no predicado de igualdade (ext neste caso) deve ser colocada primeiro no índice definição.
• O desempenho da consulta é melhorado quando as colunas em um índice são ordenadas de acordo com a ordem em que são usadas na cláusula WHERE.
• A cardinalidade por si só não é um fator decisivo na determinação da índice ideal. Em cenários em que a coluna de intervalo tem cardinalidade mais alta, mas a coluna de igualdade está envolvida em um predicado de igualdade, colocar a coluna de igualdade em primeiro lugar produz um melhor desempenho.