Qu’est-ce que la cardinalité d’un index ?
Auteur : Rédaction Domeny.ForSale Publié le
En bref : La cardinalité d’un index est le nombre de valeurs de clé distinctes présentes dans cet index, souvent fourni sous forme d’estimation par la base de données. Elle aide l’optimiseur à évaluer combien de lignes peuvent satisfaire une condition de requête et s’il est avantageux d’utiliser l’index. Une cardinalité élevée favorise généralement la recherche d’enregistrements précis, mais ne garantit pas à elle seule de meilleures performances.
Ce que signifie exactement la cardinalité d’un index
Un index est une structure de données supplémentaire qui facilite la recherche de lignes sans parcourir toute la table. Sa clé correspond à la valeur d’une colonne ou à un ensemble de valeurs de plusieurs colonnes. La cardinalité indique combien de clés distinctes figurent dans les données, et non le nombre total d’entrées dans l’index.
Supposons que la table clients contienne 100 000 lignes. Si chaque client possède un identifiant différent, la cardinalité de l’index sur la colonne id est de 100 000. Si la colonne status ne contient que les valeurs actif, inactif et bloque, sa cardinalité est de 3.
Dans les outils d’administration, le nombre affiché ne correspond pas toujours à un décompte exact des données. Le moteur peut le calculer à partir d’un échantillon et le conserver dans ses statistiques, c’est-à-dire les informations sur le contenu des tables utilisées pour planifier les requêtes. Consultez la documentation du moteur concerné pour savoir comment interpréter les valeurs NULL et comprendre les détails de présentation de la cardinalité.
Cardinalité et sélectivité d’une requête
La cardinalité est une caractéristique des données de l’index. La sélectivité d’une condition décrit, quant à elle, à quel point cette condition réduit l’ensemble des résultats. En pratique, une condition qui ne sélectionne qu’une petite partie de la table est dite très sélective. Pour comparer des indicateurs chiffrés, il faut toutefois vérifier leur définition, car les conventions utilisées peuvent varier.
Lors d’une recherche sur un identifiant unique, la base peut trouver une seule ligne. Pour la condition status = 'actif', elle peut devoir récupérer des milliers, voire des dizaines de milliers de lignes. Dans ce second cas, lire l’index puis récupérer les enregistrements dans la table peut coûter plus cher que parcourir entièrement celle-ci.
Le nombre de valeurs distinctes ne dit toutefois pas à quelle fréquence chacune apparaît. Si 99 000 clients sont actifs et seulement 20 sont bloqués, deux conditions portant sur la même colonne peuvent tirer un bénéfice très différent de l’index. La cardinalité reste de 3, mais les données sont réparties de façon inégale.
C’est pourquoi l’optimiseur — la partie du moteur qui choisit comment exécuter la requête — peut aussi utiliser d’autres statistiques, par exemple des histogrammes décrivant la distribution des valeurs. Leur disponibilité et leur utilisation dépendent de la base de données.
Comprendre la cardinalité d’un index composite
Un index composite porte sur plusieurs colonnes. Pour un index sur (pays, ville), ce qui compte est le nombre de paires distinctes, et non simplement le nombre de pays ou de villes considérés séparément.
Par exemple, une table peut contenir 10 pays et 500 paires pays–ville distinctes. La cardinalité de la première partie de la clé est alors de 10, et celle de la clé complète de 500. Il ne faut pas multiplier automatiquement le nombre de pays distincts par le nombre de villes distinctes, car toutes les combinaisons possibles ne figurent pas nécessairement dans la table.
Certains outils affichent des estimations séparées pour les préfixes successifs de l’index. Dans MySQL, le champ Cardinality du résultat de SHOW INDEX doit être interprété en tenant compte de la position de la colonne indiquée par Seq_in_index : l’estimation concerne le préfixe de l’index qui se termine à cette position.
L’ordre des colonnes influe également sur l’utilisation de l’index. Dans un index B-tree classique, c’est-à-dire un index organisé en structure arborescente ordonnée, (pays, ville) facilite naturellement les recherches par pays, ainsi que par pays et ville. Une recherche portant uniquement sur la ville peut nécessiter un autre mode d’accès ; les optimisations disponibles dépendent du moteur.
Pourquoi une cardinalité élevée ne suffit pas
Un index sur une colonne dont les valeurs sont presque toutes uniques peut être un bon choix pour une comparaison avec une valeur précise, mais être moins utile pour une plage étendue. Une requête qui récupère la majorité des enregistrements selon leur date peut ne pas utiliser l’index, même si les dates présentent de nombreuses valeurs distinctes.
La manière d’écrire la condition compte aussi. Appliquer une fonction à la colonne indexée, par exemple pour extraire uniquement l’année d’une date, peut rendre l’utilisation d’un index ordinaire plus difficile. Il faut vérifier, pour le moteur concerné, s’il est possible d’utiliser un index fonctionnel ou de réécrire la requête de manière appropriée.
Un index peut aussi faciliter le tri, les jointures entre tables ou la lecture des données sans accéder à la table, s’il contient toutes les colonnes nécessaires. Une faible cardinalité ne justifie donc pas, à elle seule, la suppression d’un index. Il faut également tenir compte de son coût : l’espace occupé et le travail supplémentaire lors de l’écriture et de la mise à jour des données.
Comment vérifier si un index est utile
Évaluez l’index à partir d’une requête réelle, et non du seul nombre affiché dans l’interface d’administration :
- Choisissez une requête courante et des valeurs de paramètres représentatives, en incluant des valeurs fréquentes et rares.
- Examinez le plan d’exécution à l’aide de
EXPLAINou de son équivalent dans votre moteur. - Observez l’index choisi, le nombre estimé de lignes et le mode d’accès à la table.
- Si cela peut être fait sans risque, comparez les estimations à l’exécution réelle. Gardez à l’esprit que les commandes d’analyse de l’exécution peuvent lancer la requête.
- En cas d’écarts importants, vérifiez si les statistiques sont à jour et consultez les règles de leur actualisation dans la documentation de la base.
Après un import massif, la suppression d’enregistrements ou un changement dans la distribution des valeurs, les statistiques peuvent ne plus refléter les données actuelles. Avant d’ajouter un nouvel index, examinez le plan de la requête problématique et l’état des statistiques : vous pourrez ainsi distinguer l’absence d’un index adapté d’une mauvaise estimation des coûts.
À lire aussi : Qu’est-ce que FireFox ?
À lire aussi
