Utiliser un index couvrant pour optimiser les performances

On parle d'un index couvrant lorsqu'il couvre les besoins de la requête.

Le problème : le Bookmark Lookup

Avec une requête comme :

SELECT *
FROM contact
WHERE nom = 'Ferragotto';

SQL Server peut d’abord rechercher Ferragotto dans l’index sur nom, puis retourner dans la table pour récupérer les autres colonnes.

Cette seconde opération est appelée Bookmark Lookup.

Elle peut devenir coûteuse lorsque beaucoup de lignes sont trouvées.

Par exemple, si nom = 'Lacroix' retourne 93 lignes, SQL Server devrait potentiellement effectuer 93 recherches supplémentaires dans la table.

Dans certains cas, il est donc moins coûteux pour SQL Server de faire directement un Table Scan.

La couverture

Si la requête demande uniquement une colonne déjà présente dans l’index :

SELECT nom
FROM contact
WHERE nom = 'Lacroix';

SQL Server peut répondre directement avec l’index.

Mais avec :

SELECT nom, prenom
FROM contact
WHERE nom = 'Lacroix';

SQL Server doit également récupérer prenom.

On peut alors créer un index couvrant :

CREATE INDEX idx_contact_nom
ON contact(nom)
INCLUDE (prenom);

L’index contient alors :

• nom → clé de l’index, utilisée pour rechercher ;
• prenom → colonne incluse, utilisée pour retourner le résultat.

SQL Server peut ainsi récupérer toutes les données nécessaires sans accéder à la table.

ON ou INCLUDE ?

Une règle simple :

• Les colonnes utilisées pour rechercher, filtrer ou joindre doivent généralement être des clés de l’index.
• Les colonnes utilisées uniquement pour retourner les résultats du SELECT peuvent être placées dans INCLUDE.

Exemple :

CREATE INDEX idx_contact_nom
ON contact(nom)
INCLUDE (prenom, email);

Ici :

• nom sert à rechercher ;
• prenom et email servent à couvrir le résultat.

Les avantages

Un index couvrant peut :

• éviter les Bookmark Lookups ;
• réduire fortement les accès à la table ;
• améliorer les performances des requêtes ;
• réduire certains accès et verrous sur la table ;
• permettre à SQL Server de récupérer toutes les données directement depuis l’index.

À retenir

Un index couvrant contient toutes les colonnes nécessaires à une requête afin que SQL Server puisse répondre directement depuis l’index.

Il ne faut cependant pas chercher à couvrir toutes les requêtes.

Il est préférable de cibler les requêtes :

• fréquentes ;
• critiques ;
• coûteuses ;
• fortement sollicitées.

Chaque index supplémentaire consomme de l’espace disque et augmente également le coût des opérations d’écriture (INSERT, UPDATE, DELETE).

Règle pratique

WHERE / JOIN / recherche
        ↓
   Clé de l'index

SELECT uniquement
        ↓
      INCLUDE

L’objectif est donc de construire des index qui couvrent les requêtes importantes, plutôt que de multiplier les index inutilement.