[SQL Server] Index : différences entre un index en cluster et un index non-cluster

Ce billet présente brièvement, sous forme de rappel, ce qui différencie un index en cluster d’un index non-cluster. Il donne également quelques conseils d’utilisation.

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.

Qu’est-ce qu’un index en cluster et un index non-cluster ?

Il existe de nombreux types d’indexes, dont les plus connus et utilisés sont l’index en cluster (clustered index) et l’index non-cluster (non-clustered index). Et la différence entre les 2 est la suivante :

  • L’index en cluster (ou ordonné) est un index au sein duquel les enregistrements sont physiquement triés, et ses pages de données au niveau feuille et les numéros d’enregistrements au niveau intermédiaire. C’est ainsi qu’une table ne peut avoir qu’un index en cluster à la fois dans la mesure où les lignes ne peuvent être triées qu’en un seul ordre physique.
  • L’index non-cluster (ou non-ordonné) est un index au sein duquel le tri logique des enregistrements (i.e., tri des pages d’indexes) ne correspond pas au tri physique des enregistrements (sur le disque). De plus, au niveau structurel, seuls les numéros d’enregistrements sont placés au niveau feuille, ce qui permet donc, à une table, de pouvoir disposer de plusieurs indexes non-clusters. Par contre, il ne peut y avoir qu’un maximum de 249 indexes non-clusters par table.

Pour créer un index en cluster ou non-cluster, cela est très simple en T-SQL :

  • Index en cluster sur une colonne MaTable_ID d’une table appelée MaTable :
CREATE CLUSTERED INDEX IDX_MaTable_ID
   ON dbo.MaTable (MaTable_ID)
GO
  • Index non-cluster sur une colonne Nom d’une table appelée MaTable :
CREATE NONCLUSTERED INDEX IDX_Nom
   ON dbo.MaTable(Nom)
GO

Conseils d’utilisation

De manière générale, du point de vue des performances :

  • Un index en cluster est utile :
    • Sur les colonnes contenant un très grand nombre de valeurs différentes (souvent le cas des primary keys).
    • Sur les tables régulièrement accédées séquentiellement en lecture.
    • Sur les colonnes concernées par la spécification d’un intervalle de valeurs au sein d’une requête (via BETWEEN ou les opérateurs de comparaison).
    • Sur les colonnes concernées par des requêtes retournant un très grand nombre de résultats.
    • Sur les colonnes concernées par des jointures (typiquement souvent le cas des foreign keys).
    • Sur les colonnes spécifiées dans des ORDER BY ou GROUP BY, ce qui élimine la nécessité, pour l’optimiseur SQL, de trier les lignes d’enregistrements, l’index en cluster le faisant déjà.
  • Un index non-cluster est utile :
    • Sur les colonnes régulièrement mises-à-jour.
    • Sur les colonnes concernées par une clause WHERE de sélectivité.
    • Sur les colonnes retournant peu de résultats.

Pour aller plus loin…

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