[SQL Server] Index/Query optimization : index couvrant/index INCLUDE, ou comment faire face aux key lookups ?

Ce billet présente les index couvrants et les index de type INCLUDE et leurs principales différences. Une étude de cas sera également effectué afin de mettre en avant leurs avantages dans le traitement de problèmes de performances liés aux key lookup (ou bookmark lookup avant SQL Server 2005 SP1, ou RID lookup pour les tables sans index en cluster).


Pour plus de détails sur l’architecture d’un index (qu’il s’agisse d’un index en cluster ou d’un index non-cluster) allez ici. Et vous pouvez également jeter un coup d’œil ici pour comprendre comment un plan d’exécution peut être examiné, ainsi que les statistiques.

Notions d’index couvrant et d’index de type INCLUDE

Les indexes couvrants (ou composites) et ceux de type INCLUDE (apparus à partir de SQL Server 2005) sont des indexes permettant de couvrir des colonnes utilisées au sein d’une ou plusieurs requêtes. La syntaxe simple de création d’un index INCLUDE est la suivante :

CREATE NONCLUSTERED INDEX <Nom_Index_INCLUDE>
   ON MaTable(Col3)
      INCLUDE (Col1, Col2)
GO

Le mot-clé NONCLUSTERED est facultatif. Et les colonnes dites « non-clés » sont Col1 et Col2, tandis que Col3 est la colonne de clé.

Pour ce qui est de l’index couvrant :

CREATE INDEX <Nom_Index_Couvrant>
   ON MaTable (Col3, Col1, Col2)
GO

Les colonnes Col1, Col2 et Col3 sont les colonnes de clé.

Les principales différences entre ces 2 types d’indexes sont les suivantes :

  • L’index couvrant stocke les données de ses colonnes clés aux niveaux feuille et intermédiaire de sa structure. L’index INCLUDE, quant à lui, stocke ses colonnes non-clés au niveau feuille, le niveau intermédiaire étant réservé à sa colonne de clé.
  • La répartition des colonnes au sein de sa structure (cf : point précédent) permet à l’index INCLUDE de consommer moins de ressources en termes de volumétrie que l’index couvrant. Pour information, un index ne peut accepter que jusqu’à 900 octets de colonnes de clé.
  • L’index couvrant stockant ses colonnes à différents niveaux, l’impact relatif aux mises-à-jour peut être significatif comparé à l’index INCLUDE, où seul le niveau feuille est utilisé pour l’inclusion des colonnes.
  • L’index INCLUDE n’est pas pris en compte par le moteur SQL lors du recalcul du nombre de colonnes de clé d’index ou de la taille des clés d’index.
  • Jusqu’à 1023 colonnes non-clés peuvent être utilisées au sein d’un index INCLUDE, tandis que l’index couvrant ne prend en compte que jusqu’à 16 colonnes de clé.

Utilisation d’un index couvrant ou d’un index INCLUDE pour l’amélioration des performances d’une requête

    Avant de commencer…

Si ce n’est pas déjà fait, récupérez la base de données de test AdventureWorks ici. Seule la table Person.Person nous intéresse.

Maintenant, créons une table Personne (et un index en cluster) et insérons-y les ~20k enregistrements de Person.Person :

CREATE TABLE
Personne(
Personne_ID INT NOT NULL IDENTITY(1,1) PRIMARY KEY CLUSTERED,
Personne_Nom NVARCHAR(30) NULL,
Personne_Prenom NVARCHAR(50) NULL,
Age INT NULL DEFAULT ABS(CHECKSUM(NEWID()))% 47 + 18
)
GO
INSERT INTO Personne(Personne_Nom, Personne_Prenom)
   SELECT LastName, FirstName FROM AdventureWorks.Person.Person
GO
SELECT * FROM Personne
GO

L’âge sera généré aléatoirement (pour chaque ligne, une valeur comprise entre 18 et 65 ans sera affectée). Mais cela est anecdotique dans notre billet.

Dans notre exemple, la table Personne est créée au sein de la base AdventureWorks2012, mais vous pouvez (plus conventionnellement) la créer sur une autre base de test dédiée, ou directement utiliser la table Person.Person.

Entrée dans le vif du sujet

La requête ci-dessous permet d’afficher toutes les personnes (et leur âge) dont le nom est Abercrombie :

SELECT Personne_Nom, Personne_Prenom, Age
FROM Personne
WHERE Personne_Nom=N'Abercrombie'
GO

Voici son plan d’exécution :


Et ce que donnent ses statistiques I/O :

Table ‘Personne’. Scan count 1, reads 116, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

On peut noter que le scan de l’index en cluster pour le traitement de la requête génère 516 lectures logiques.

Créons un index non-cluster IX_Personne_Nom sur la colonne Personne_Nom afin d’améliorer les traitements :

CREATE NONCLUSTERED INDEX IX_Personne_Nom
   ON Personne (Personne_Nom)
GO

Après avoir relancé notre requête, voici ce que donnent les statistiques I/O :

Table ‘Personne’. Scan count 1, logical reads 8, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

On peut noter une baisse significative du nombre de lectures logiques, ce qui contribue à l’amélioration des temps I/O.

Jetons, maintenant, un coup d’œil au plan d’exécution de la requête :


Ce qu’on peut déduire, c’est que l’optimiseur de requête a utilisé l’index non-cluster IX_Personne_Nom pour identifier chaque ligne qui correspond aux critères de la requête. Et une fois l’index non-cluster utilisé, il a procédé à la recherche des informations desdites lignes de la colonne indexée en utilisant l’index en cluster. On parle alors de recherche sur clé, ou key lookup (ou bookmark lookup, avant SQL Server 2005). Dans le cas d’une table sans index en cluster, on parle de RID lookup (ou recherche sur identifiant de lignes).

Dans la mesure où les nœuds au niveau feuille de l’index non-cluster contiennent des valeurs de clé pour chaque ligne de la colonne indexée, SQL Server doit naviguer à travers l’index en cluster pour retrouver les informations associées à chaque ligne du jeu de résultats. Dans notre cas, c’est une jointure de type nested loop (boucle imbriquée) qui a été utilisée pour le parcours inter-indexes.

Voici, ci-dessous, le détail des informations statistiques des opérateurs Key Lookup et Index Seek utilisés par l’optimiseur pour traiter la requête :


Bien que le key lookup permette à SQL Server de retrouver des données non-clé à partir de pages de données quand un index non-cluster est choisi pour traiter une requête, mais manque d’informations subsidiaires, elle est avant tout une opération inutilement coûteuse en ressources (57% des temps de traitement, dans notre cas).

Il est important de procéder à une optimisation de la requête, et pour ce faire, l’utilisation d’un index couvrant ou d’un index de type INCLUDE permet de résoudre cette situation, en couvrant ou incluant les colonnes Personne_Prenom et Age.

A partir des informations du plan d’exécution, on peut voir que le key lookup a été réalisé pour retrouver les informations manquantes de l’index non-cluster, à savoir : le prénom et l’âge (output list de l’opérateur key lookup) de chaque nom d’IX_Personne_Nom.

Maintenant, supprimons l’index non-cluster IX_Personne_Nom :

DROP INDEX IX_Personne_Nom
   ON Personne
GO

… puis créons :

  • Un index couvrant :
CREATE NONCLUSTERED INDEX IX_PersonneNom_PersonnePrenom_Age
   ON Personne(Personne_Nom, Personne_Prenom, Age)
GO

OU

  • Un index INCLUDE :
CREATE NONCLUSTERED INDEX IX_PersonneNom_INC_PersonnePrenom_Age
  ON Personne (Personne_Nom)
    INCLUDE (Personne_Prenom, Age)
GO

Voici le plan d’exécution, les statistiques I/O et le détail de l’opérateur Index Seek faisant référence à l’index de type INCLUDE utilisé :


Table ‘Personne’. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.


On peut noter la réduction de la consommation des ressources I/O (passage de 8 à 2 lectures logiques). En effet, en utilisant un seul index contenant toutes les informations qu’il recherche, l’optimiseur gagne beaucoup de temps lors du traitement de la requête.

Concernant l’index couvrant, les résultats sont les mêmes, qu’il s’agisse du plan d’exécution ou des statistiques I/O :

Table ‘Personne’. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Index couvrant ou index INCLUDE… UFC Que Choisir ?

En reprenant tout ce qui a été fait jusqu’alors, rajoutons près de 20k enregistrements supplémentaires :

INSERT INTO Personne(Personne_Nom, Personne_Prenom)
   SELECT LastName, FirstName FROM AdventureWorks.Person.Person
GO

Si on relance notre requête relative à l’affichage des personnes ayant le nom d’Abercrombie, on obtient sensiblement le même plan d’exécution, que ce soit pour l’index INCLUDE ou l’index couvrant… A une exception près : les statistiques I/O détaillées ne sont plus les mêmes au niveau des lectures logiques :

  • Index couvrant :
Table ‘Personne’. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
  • Index INCLUDE :
Table ‘Personne’. Scan count 1, logical reads 3, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Cette légère différence au niveau des performances est due à la structure des indexes en question, et rejoint tout ce qui a été dit dans la section Notions d’index couvrant et d’index INCLUDE sur les principales différences entre l’index couvrant et l’index INCLUDE qui deviennent plus visibles au fur et à mesure que le nombre d’enregistrements à traiter croît.

Pour valider ces assertions, utilisons la DMV sys.dm_index_physical_stats pour récupérer certaines informations relatives aux indexes (i.e., profondeur, niveau,…) :

SELECT object_name([object_id]) AS 'Table',
alloc_unit_type_desc, index_depth,index_level,
page_count,record_count,avg_record_size_in_bytes
FROM sys.dm_db_index_physical_stats
(DB_ID(N'AdventureWorks2012'),
OBJECT_ID(N'Personne'),
NULL,
NULL,
'DETAILED')
GO
  • Index couvrant :


  • Index INCLUDE :


Des informations ci-dessus, on peut retenir que l’index couvrant a une profondeur de 3 niveaux, là où l’index INCLUDE en a 2. La valeur 0 d’index_level renvoie 0 lorsqu’il s’agit du niveau feuille (le plus bas), et une valeur supérieure à 0 le cas échéant, sachant que la valeur la plus élevée (dans le cas de l’index couvrant, par exemple, 2) est la racine.

Au-delà de la longueur du parcours de l’index couvrant comparé à l’index INCLUDE, on peut également constater que la volumétrie n’est pas la peine, l’index couvrant étant plus volumineux. Au vu des nombreux avantages offerts par l’index INCLUDE comparé à l’index couvrant, la tendance serait de privilégier le premier.

En conclusion…

Ce billet a permis d’apprécier l’essentiel des bénéfices de l’utilisation d’un index couvrant ou de type INCLUDE.

Bien que ces indexes permettent de faire face à diverses situations associées aux problèmes de performances d’une requête (scan de table trop coûteux, key lookup, notamment), il est de bon ton de noter qu’une sur-indexation peut paradoxalement impacter les performances des requêtes d’insertion, de mise-à-jour ou de suppression

Pour consulter d’autres billets relatifs aux indexes, gardez notamment un oeil ici et ..

Publicités

Laisser un commentaire

Entrez vos coordonnées ci-dessous ou cliquez sur une icône pour vous connecter:

Logo WordPress.com

Vous commentez à l'aide de votre compte WordPress.com. Déconnexion / Changer )

Image Twitter

Vous commentez à l'aide de votre compte Twitter. Déconnexion / Changer )

Photo Facebook

Vous commentez à l'aide de votre compte Facebook. Déconnexion / Changer )

Photo Google+

Vous commentez à l'aide de votre compte Google+. Déconnexion / Changer )

Connexion à %s