Placement de colonnes à cardinalité élevée dans les index composites avec requêtes de plage

Lors de l'interrogation d'une table avec un index composite impliquant une condition de plage, le placement de Les colonnes de l'index peuvent avoir un impact significatif sur les performances.

Considérez les fichiers de table avec une clé primaire (did, nom de fichier) et deux index composites : INDEX(filetime, ext) et INDEX (ext, filetime). Les deux index contiennent la colonne filetime, qui a une cardinalité plus élevée que ext.

La requête :

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

nécessite l'accès aux données en fonction à la fois de l'ext et du filetime. La question se pose : quel index est optimal pour une telle requête ?

Analyse

Pour déterminer l'index optimal, nous pouvons utiliser FORCE INDEX et examiner les plans d'exécution :

-- 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 
La sortie montre que INDEX(ext, filetime) (ef) a un nombre de lignes nettement inférieur, indiquant une analyse plus efficace.
Optimizer Trace
Pour analyser plus en détail le comportement de l'optimiseur, nous pouvons utiliser l'optimiseur trace :
SELECT explain_format = 'JSON';

SELECT COUNT(*), AVG(fsize)
FROM files
WHERE ext = 'gif'
  AND filetime >= '2015-01-01'
  AND filetime 
La trace révèle que l'optimiseur choisit INDEX(ext, filetime) car il peut utiliser les deux colonnes de l'index pour filtrer et récupérer des données. En revanche, INDEX(filetime, ext) ne peut utiliser que la première colonne (filetime) pour le filtrage.
Conclusions
Sur la base de l'analyse, les conclusions suivantes peuvent être draw :

• Pour les index composites utilisés dans les requêtes par plage, la colonne impliquée dans le prédicat d'égalité (ext dans ce cas) doit être placée en premier dans l'index définition.
• Les performances des requêtes sont améliorées lorsque les colonnes d'un index sont classées selon l'ordre dans lequel elles sont utilisées dans la clause WHERE.
• La cardinalité à elle seule n'est pas un facteur décisif pour déterminer la indice optimal. Dans les scénarios où la colonne de plage a une cardinalité plus élevée mais où la colonne d'égalité est impliquée dans un prédicat d'égalité, placer la colonne d'égalité en premier donne de meilleures performances.