Indications pour la conception d'index non-cluster
Un index non-cluster contient les valeurs de clé d'index et les localisateurs de ligne qui pointent vers l'emplacement de stockage des données de table. Pour plus d'informations sur l'architecture des index non-cluster, consultez Structures d'index non-cluster.
Vous pouvez créer plusieurs index non-cluster sur une table ou une vue indexée. Les index non-cluster doivent, en principe, améliorer les performances des requêtes fréquemment utilisées qui ne sont pas couvertes par l'index cluster.
De la même manière que vous utilisez un index dans un livre, l'optimiseur de requête recherche une valeur de données en examinant l'index non-cluster afin de trouver l'emplacement qu'occupe la valeur dans la table, puis récupère directement les données à partir de cet emplacement. C'est pour cette raison que les index non cluster constituent une solution idéale pour les requêtes à correspondance exacte ; l'index contient en effet des entrées décrivant l'emplacement exact qu'occupent dans la table les valeurs de données recherchées dans les requêtes. Par exemple, l'optimiseur de requête peut utiliser l'index non cluster IX_Person_LastName_FirstName_MiddleName pour interroger la table Person.Person pour les personnes qui ont un certain nom ; cet index a LastName comme l'une de ses colonnes clés. L'optimiseur de requête recherche rapidement toutes les entrées de l'index qui correspondent à la valeur LastName spécifiée. Chaque entrée d'index pointe vers la page et la ligne exactes de la table ou de l'index cluster contenant les données correspondantes. Après avoir trouvé toutes les entrées dans l'index, l'optimiseur de requête peut accéder directement à la page et à la ligne exactes pour récupérer les données.
Remarques sur les bases de données
Les caractéristiques de la base de données sont importantes lors de la conception d'index non-cluster.
Les bases de données ou les tables dont les mises à jour sont faibles, mais qui contiennent des volumes importants de données peuvent tirer parti de nombreux index non-cluster en vue d'améliorer les performances des requêtes. Envisagez de créer des index filtrés pour les sous-ensembles de données bien définis afin d'améliorer les performances des requêtes, réduire les coûts de stockage d'index et réduire les coûts de maintenance d'index par rapport aux index non cluster de table entière.
Les applications et bases de données d'aide à la décision contenant principalement des données en lecture seule peuvent tirer parti de nombreux index non-cluster. L'optimiseur de requête doit choisir parmi davantage d'index pour déterminer la méthode d'accès la plus rapide ; les caractéristiques de mise à jour faible de la base de données sont synonymes d'une maintenance d'index qui n'entravera pas les performances.
Les applications et bases de données OLTP (traitement transactionnel en ligne) qui contiennent des tables largement mises à jour doivent éviter la sur-indexation. Les index doivent en outre être réduits, c'est-à-dire contenir le moins de colonnes possible.
La définition de nombreux index sur une table affecte les performances des instructions INSERT, UPDATE, DELETE et MERGE, car à mesure que les données de la table changent, tous les index doivent être mis à jour en conséquence.
Remarques sur les requêtes
Avant de créer des index non-cluster, vous devez comprendre comment se déroulera l'accès aux données. Il est conseillé d'utiliser un index non-cluster pour les requêtes avec les attributs suivants :
Requêtes qui utilisent des clauses JOIN ou GROUP BY.
Créez plusieurs index non-cluster sur des colonnes impliquées dans les opérations de jointure et de regroupement, ainsi qu'un index cluster sur les colonnes de clé étrangère éventuelles.
Requêtes qui ne retournent pas des ensembles de résultats volumineux.
Créez des index filtrés pour couvrir les requêtes qui retournent un sous-ensemble bien défini de lignes d'une grande table.
Requêtes qui contiennent des colonnes souvent impliquées dans les conditions de recherche d'une requête (clause WHERE) qui retournent des correspondances exactes.
Remarques sur les colonnes
Il est conseillé d'utiliser des colonnes qui possèdent un ou plusieurs de ces attributs :
Couvrent la requête.
Performances accrues lorsque l'index contient toutes les colonnes de la requête. L'optimiseur de requête peut localiser toutes les valeurs de colonnes dans l'index ; les données de table ou d'index cluster ne sont pas accédées, avec pour conséquence une réduction des opérations d'E/S disque. Utilisez un index avec colonnes incluses pour ajouter des colonnes de couverture au lieu de créer une clé d'index de grande taille. Pour plus d'informations, consultez Index avec colonnes incluses.
Si la table a un index cluster, la ou les colonnes définies dans cet index sont automatiquement ajoutées à la fin de chaque index non-cluster de la table. Ceci peut produire une requête couverte sans spécifier les colonnes de l'index cluster dans la définition de l'index non-cluster. Par exemple, si une table a un index cluster sur la colonne C, un index non cluster sur les colonnes B et A aura comme valeurs de clé les colonnes B, A et C.
Un nombre élevé de valeurs distinctes, comme une combinaison de nom et prénom, si un index cluster est utilisé pour d'autres colonnes.
Lorsqu'il existe très peu de valeurs distinctes (1 et 0 uniquement, par exemple), la plupart des requêtes utiliseront une analyse de table, généralement plus efficace, au lieu de l'index. Pour ce type de données, envisagez de créer un index filtré sur une valeur distincte qui se produit uniquement dans un petit nombre de lignes. Par exemple, si la plupart des valeurs sont 0, l'optimiseur de requête peut utiliser un index filtré pour les lignes de données qui contiennent 1.
Options d'index
Lors de la création d'un index non-cluster, plusieurs options d'index peuvent être spécifiées. Faites particulièrement attention aux options suivantes :
Facteur de remplissage
ONLINE
Pour plus d'informations, consultez Configuration des options d'index.