Quel index composite est optimal pour les requêtes de plage : colonnes à cardinalité élevée ou faible ?

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 ext et de filetime. La question se pose : quel index est optimal pour une telle requête ?

Analyse

Pour déterminer l'index optimal, on peut 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' + INTERVAL 1 MONTH;

-- Force low-cardinality ext first
FORCE INDEX(ef) SELECT COUNT(*), AVG(fsize)
FROM files
WHERE ext = 'gif'
  AND filetime >= '2015-01-01'
  AND filetime < '2015-01-01' + INTERVAL 1 MONTH;

Le résultat montre que INDEX(ext, filetime) (ef) a un nombre de lignes nettement inférieur, indiquant un système plus efficace scan.

Trace de l'optimiseur

Pour analyser plus en détail le comportement de l'optimiseur, nous pouvons utiliser la trace de l'optimiseur :

SELECT explain_format = 'JSON';

SELECT COUNT(*), AVG(fsize)
FROM files
WHERE ext = 'gif'
  AND filetime >= '2015-01-01'
  AND filetime < '2015-01-01' + INTERVAL 1 MONTH;

La trace révèle que le 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é en premier dans la définition de l'index.
• Les performances des requêtes sont améliorées lorsque les colonnes d'un index sont classées en fonction de l'ordre dans lequel elles sont utilisées dans la clause WHERE.
• La cardinalité seule est pas un facteur décisif pour déterminer l’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.

Ce qui précède est le contenu détaillé de. pour plus d'informations, suivez d'autres articles connexes sur le site Web de PHP en chinois!