Indications pour la conception d'index cluster
Les index cluster trient et stockent les lignes de données de la table en fonction de leurs valeurs de clé. Il n'y a qu'un index cluster par table car les lignes de données ne peuvent être triées que dans un seul ordre. Pour plus d'informations sur l'architecture des index cluster, consultez Structures des index cluster.
À quelques exceptions près, toutes les tables doivent avoir un index cluster défini sur la ou les colonnes présentant les caractéristiques suivantes :
utilisables pour les requêtes fréquemment utilisées ;
assurant un niveau élevé d'unicité ;
Notes
Lorsque vous créez une contrainte PRIMARY KEY, un index unique sur la ou les colonnes est automatiquement créé. Par défaut, cet index est cluster ; toutefois, vous pouvez spécifier un index non-cluster lorsque vous créez la contrainte.
utilisables dans les requêtes de plage.
Si l'index cluster n'est pas créé avec la propriété UNIQUE, le Moteur de base de données ajoute automatiquement une colonne uniqueifier de 4 octets à la table. Si nécessaire, le Moteur de base de données ajoute automatiquement une valeur uniqueifier à une ligne pour que chaque clé soit unique. Cette colonne et ses valeurs sont utilisées en interne et ne sont ni affichables, ni accessibles par les utilisateurs.
Remarques sur les requêtes
Avant de créer des index cluster, il est important de comprendre le mode d'accès aux données. Envisagez l'emploi d'un index cluster pour les requêtes qui :
retournent une plage de valeurs utilisant des opérateurs tels que BETWEEN, >, >=, < et <= ;
Dès que la ligne comportant la première valeur est trouvée à l'aide de l'index cluster, les lignes présentant les valeurs indexées suivantes sont garanties comme étant adjacentes physiquement. Par exemple, si une requête extrait des enregistrements compris dans une plage de numéros de commandes, un index cluster sur la colonne SalesOrderNumber permet de localiser rapidement la ligne qui contient le premier numéro de commande, puis d'extraire toutes les lignes successives de la table jusqu'à ce que le dernier numéro de commande soit atteint.
retournent des jeux de résultats volumineux ;
utilisent des clauses JOIN ; ce sont en général des colonnes clés étrangères ;
utilisent des clauses ORDER BY ou GROUP BY.
Si un index est présent sur les colonnes spécifiées dans la clause ORDER BY ou GROUP BY, le Moteur de base de données n'a plus besoin de trier les données car les lignes le sont déjà. Les requêtes présentent dès lors des performances accrues.
Remarques sur les colonnes
En général, vous devez définir la clé d'index cluster avec le moins de colonnes possible. Envisagez les colonnes présentant un ou plusieurs des attributs suivants :
Colonnes uniques ou qui contiennent de nombreuses valeurs distinctes
Par exemple, l'ID d'un salarié l'identifie de manière unique. Un index cluster ou une contrainte PRIMARY KEY sur la colonne EmployeeID améliore les performances des requêtes qui recherchent des informations sur les salariés en fonction de leur ID. D'une autre manière, un index cluster peut être créé sur LastName, FirstName, MiddleName car les enregistrements de salariés sont fréquemment groupés et interrogés de cette façon et l'association de ces colonnes présentes toujours un niveau élevé de différenciation.
Accès séquentiel des colonnes
Par exemple, l'ID d'un produit l'identifie de manière unique dans la table Production.Product de la base de données AdventureWorks2008R2. Les requêtes dans lesquelles une recherche séquentielle est spécifiée, telles que WHERE ProductID BETWEEN 980 and 999, tireront parti d'un index cluster sur ProductID, car les lignes sont stockées dans l'ordre sur cette colonne clé.
Colonnes définies en tant que IDENTITY du fait qu'elles sont garanties comme uniques dans la table
Colonnes fréquemment utilisées pour trier les données extraites d'une table
Il peut être judicieux de mettre en cluster, c'est-à-dire de trier physiquement, la table sur cette colonne pour économiser le coup d'une opération de tri à chaque fois que la colonne est interrogée.
Les index cluster sont déconseillés pour les colonnes présentant les attributs suivants :
Les colonnes sujettes à des modifications fréquentes.
La ligne tout entière est ainsi déplacée, car le Moteur de base de données doit conserver les valeurs des données de la ligne dans l'ordre physique. Cette observation est importante dans les systèmes de traitement transactionnel à haut volume où les données sont en général éphémères.
Les clés étendues.
Les clés étendues sont composées de plusieurs colonnes ou plusieurs colonnes de grande taille. Les valeurs de clé de l'index cluster sont utilisées par tous les index non-cluster comme clés de recherche. Tous les index non-cluster définis sur la même table sont considérablement plus grands car leurs entrées contiennent la clé de cluster et aussi les colonnes clés définies pour cet index non-cluster.
Options d'index
Plusieurs options d'index peuvent être spécifiées lors de la création d'un index cluster. Comme les index cluster sont en général de très grande taille, vous devez envisager plus particulièrement les options suivantes :
SORT_IN_TEMPDB
DROP_EXISTING
FILLFACTOR
ONLINE
Pour plus d'informations, consultez Configuration des options d'index.