Cardinalité élevée ou cardinalité faible : quelle colonne doit figurer en premier dans un index de plage ?

Ordre des colonnes à cardinalité élevée dans un index de plage

Dans la conception de bases de données, lors de la création d'un index sur plusieurs colonnes, l'ordre de ces colonnes peut un impact significatif sur les performances des requêtes de plage. En particulier, pour les requêtes impliquant à la fois une colonne à cardinalité élevée et une colonne à cardinalité faible, l'ordre optimal des colonnes dans l'index peut être contre-intuitif.

Scénario :

Envisagez un table avec le schéma suivant :

CREATE TABLE `files` (
  `did` int(10) unsigned NOT NULL DEFAULT '0',
  `filename` varbinary(200) NOT NULL,
  `ext` varbinary(5) DEFAULT NULL,
  `fsize` double DEFAULT NULL,
  `filetime` datetime DEFAULT NULL,
  PRIMARY KEY (`did`,`filename`),
  KEY `fe` (`filetime`,`ext`),          -- This?
  KEY `ef` (`ext`,`filetime`)           -- or This?
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ;

Cette table stocke les métadonnées des fichiers, avec environ 1 million de lignes. La colonne filetime a pour la plupart des valeurs distinctes, indiquant une cardinalité élevée, tandis que la colonne ext a un nombre fini de valeurs, indiquant une cardinalité faible.

Requête :

La requête suivante est utilisé pour récupérer des informations sur les fichiers en fonction à la fois de l'ext et de l'heure du fichier :

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

Meilleur index Choix :

La question se pose de savoir quel index, fe ou ef, serait le meilleur pour cette requête. Intuitivement, on pourrait supposer que l’index avec la colonne à cardinalité élevée (filetime) serait plus efficace. Cependant, l'analyse montre que le contraire est vrai.

Explication :

À l'aide de la commande EXPLAIN, nous pouvons examiner le plan de requête pour différents choix d'index :

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

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

Les résultats montrent que l'index ef, avec la colonne ext à faible cardinalité en premier, fonctionne nettement mieux que fe.

Une analyse plus approfondie à l'aide de la trace de l'optimiseur révèle qu'avec l'indice fe, l'optimiseur a estimé l'analyse d'un grand nombre de lignes (16 684) pour filtrer sur la valeur ext. Avec l'index ef, il pourrait utiliser efficacement les deux colonnes d'index et récupérer uniquement les lignes pertinentes (538).

Conclusion :

Lors de la création d'un index composite pour prendre en charge la plage requêtes, il est conseillé de placer en premier la colonne impliquée dans le test d’égalité (en l’occurrence ext), quelle que soit sa cardinalité. Cela permet à l'index d'être utilisé plus efficacement dans le plan d'exécution des requêtes, ce qui entraîne des performances améliorées.

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!