Guide de conception Index SQL Server
Un index est une structure associée à une table ou une vue sur le disque qui accélère la récupération des lignes de la table ou de la vue. Un index contient des clés construites à partir d'une ou plusieurs colonnes dans la table ou la vue. Ces clés sont stockées dans une structure (B-tree) qui permet à SQL Server pour trouver la ou les lignes associées aux valeurs clés rapidement et efficacement.
N'est pas toujours synonyme l'utilisation des index avec de bonnes performances et une bonne performance avec l'utilisation d'index efficace. Si vous utilisez un index toujours aidé à produire les meilleures performances, le travail de l'optimiseur de requêtes serait simple. En réalité, un choix d'index incorrect peut causer moins de performances optimales. Par conséquent, la tâche de l'optimiseur de requête est de sélectionner un index, ou une combinaison des index, seulement quand il améliorera les performances, et d'éviter la récupération indexée quand il nuire à la performance.
Index des tâches de conception
Les tâches de suivi font partie de notre stratégie recommandée pour la conception des index:
Comprendre les caractéristiques des requêtes les plus fréquemment utilisés. Par exemple, sachant qu'une requête fréquemment utilisée se joint à deux tables ou plus vous aider à déterminer le meilleur type d'index à utiliser.
Déterminer les options d'index peut améliorer les performances lors de la création ou le maintien de l'indice. Par exemple, la création d'un index ordonné en clusters sur une grande table existante bénéficierait de l'option d'index en ligne. L'option en ligne permet à l'activité simultanée sur les données sous-jacentes de continuer tout est créé ou reconstruit l'index. Pour plus d'informations, voir Définir les options Index.
Déterminer l'emplacement de stockage optimal pour l'indice. Un index non cluster peut être stocké dans le même groupe de fichiers que la table sous-jacente ou sur un autre groupe de fichiers. L'emplacement de stockage des index peut améliorer les performances des requêtes en augmentant les performances d'E / S disque. Par exemple, le stockage d'un index non cluster sur un groupe de fichiers qui est sur un autre disque que la table filegroup peut améliorer les performances, car plusieurs disques peuvent être lus en même temps.
Sinon, les index en cluster et non-cluster peuvent utiliser un schéma de partition sur plusieurs groupes de fichiers. Partitionnement fait de grandes tables ou des index plus faciles à gérer en vous permettant d'accéder ou de gérer des sous-ensembles de données rapidement et efficacement, tout en maintenant l'intégrité de la collection globale. Pour plus d'informations, voir les tableaux et les index partitionnés. Lorsque l'on considère le partitionnement, déterminer si l'indice devrait être aligné, c'est divisé essentiellement de la même manière que la table, ou partitionnés indépendamment.
Peut être utilisé dans les requêtes de gamme.
Si l'index cluster n'est pas créé avec la propriété UNIQUE, le moteur de base de données ajoute automatiquement une colonne de uniqueifier 4 octets à la table. Lorsqu'il est nécessaire, le moteur de base de données ajoute automatiquement une valeur uniqueifier à une ligne pour faire de chaque clé unique. Cette colonne et ses valeurs sont utilisées en interne et ne peuvent pas être vus ou accessibles par les utilisateurs.
Architecture en cluster Index
Dans SQL Server, les index sont organisés sous forme B arbres. Chaque page dans un B-arbre d'index est appelé un nœud d'index. Le nœud supérieur du B-arbre est appelé le nœud racine. Les noeuds du bas de l'indice sont appelés les nœuds feuilles. Tous les niveaux d'indice entre la racine et les nœuds feuilles sont collectivement connus comme les niveaux intermédiaires. Dans un index ordonné en clusters, les nœuds feuilles contiennent les pages de données de la table sous-jacente. Les noeuds de racine et de niveau intermédiaire contiennent des pages d'index de maintien lignes d'index. Chaque ligne d'index contient une valeur de clé et un pointeur vers une page soit de niveau intermédiaire dans l'arbre B, ou une rangée de données dans le niveau de la feuille de l'index. Les pages de chaque niveau de l'indice sont liés dans une liste doublement liée.
index cluster ont une ligne de sys.partitions. avec index_id = 1 pour chaque partition utilisée par l'index. Par défaut, un index ordonné en clusters a une seule partition. Quand un index cluster comporte plusieurs partitions, chaque partition a une structure arbre B qui contient les données pour cette partition spécifique. Par exemple, si un index cluster a quatre partitions, il y a quatre structures B-tree; une dans chaque partition.
En fonction des types de données dans l'index ordonné en clusters, chaque structure d'index cluster aura une ou plusieurs unités d'allocation dans laquelle pour stocker et gérer les données d'une partition spécifique. Au minimum, chaque index cluster aura une unité d'allocation IN_ROW_DATA par partition. L'index cluster aura également une unité d'allocation de LOB_DATA par partition si elle contient de grandes colonnes objet (LOB). Il aura également une unité d'allocation de ROW_OVERFLOW_DATA par partition si elle contient des colonnes de longueur variable qui dépassent la limite de taille de ligne 8060 octets.
Les pages de la chaîne de données et les lignes sont les commandés sur la valeur de la clé d'index ordonné en clusters. Tous les inserts sont réalisés au point où la valeur de clé dans la ligne insérée correspond à la séquence de commande entre les lignes existantes.
Cette illustration montre la structure d'un index cluster dans une seule partition.
Considérations de requête
Avant de créer des index en cluster, comprendre comment vos données seront accessibles. Pensez à utiliser un index ordonné en clusters pour les requêtes qui effectuez les opérations suivantes:
Retour une plage de valeurs en utilisant des opérateurs comme BETWEEN,>,> =, <, and <=.
Après la ligne avec la première valeur est trouvée en utilisant l'index cluster, lignes avec des valeurs indexées ultérieures sont garantis d'être physiquement adjacent. Par exemple, si une requête récupère les enregistrements entre une plage de numéros d'ordre de vente, un index ordonné en clusters sur la colonne SalesOrderNumber permet de localiser rapidement la ligne qui contient le numéro de commande de vente de départ, puis récupérer toutes les lignes successives de la table jusqu'à ce que le dernier ordre de vente nombre est atteint.
Retour de grands résultats.
Utiliser les clauses JOIN; en général ce sont des colonnes de clé étrangère.
ORDER BY ou GROUP BY clauses.
Considérations sur la colonne
En règle générale, vous devez définir la clé d'index ordonné en clusters avec aussi peu de colonnes que possible. Tenez compte des colonnes qui ont une ou plusieurs des caractéristiques suivantes:
Sont uniques ou contiennent de nombreuses valeurs distinctes
Accès séquentiel
Défini comme IDENTITÉ.
Utilisé fréquemment pour trier les données récupérées à partir d'une table.
Il peut être une bonne idée de regrouper, c'est un peu physiquement, la table sur cette colonne pour économiser le coût d'une opération de tri à chaque fois que la colonne est interrogé.
index cluster ne sont pas un bon choix pour les attributs suivants:
Les colonnes qui subissent des changements fréquents
Cela provoque dans toute la ligne de se déplacer, car le moteur de base de données doit conserver les valeurs de données d'une ligne dans l'ordre physique. Ceci est une considération importante dans les systèmes de traitement des transactions à haut volume dans lequel les données sont généralement volatiles.
Avec index multi-colonnes uniques, l'indice garantit que chaque combinaison de valeurs dans la clé d'index est unique. Par exemple, si un index unique est créé sur une combinaison de LastName. Prénom. et les colonnes middleName, deux lignes dans la table pourraient avoir la même combinaison de valeurs de ces colonnes.
Les deux index clusterisés et nonclustered peut être unique. A condition que les données dans la colonne est unique, vous pouvez créer à la fois un index ordonné en clusters unique et plusieurs index non ordonnés en clusters uniques sur la même table.
Les avantages des indices uniques comprennent les éléments suivants:
L'intégrité des données des colonnes définies est assurée.
Des informations supplémentaires utiles à l'optimiseur de requête est fourni.
considérations
Si les données est unique et que vous voulez l'unicité forcée, la création d'un index unique au lieu d'un index non sur la même combinaison de colonnes fournit des informations supplémentaires pour l'optimiseur de requêtes qui peut produire des plans d'exécution plus efficaces. Création d'un index unique (de préférence en créant une contrainte UNIQUE) est recommandée dans ce cas.
Un indice unique nonclustered peut contenir inclus des colonnes non-clés. Pour plus d'informations, voir Index avec colonnes incluses.
Pour plus d'informations sur la façon de créer des index filtrés et comment définir l'index filtré l'expression sous-jacente, voir Créer des index Filtré.
Index Filtré pour données Heterogeneous
Lorsqu'une table comporte des lignes de données hétérogènes, vous pouvez créer un index filtré pour une ou plusieurs catégories de données.
Par exemple, les produits énumérés dans le tableau Production.Product sont affectés chacun à un ProductSubcategoryID. qui sont à leur tour associés aux catégories de produits Bikes, Composants, vêtements ou accessoires. Ces catégories sont hétérogènes parce que leurs valeurs de colonne dans la table Production.Product ne sont pas étroitement corrélées. Par exemple, les colonnes de couleur. ReorderPoint. Liste des prix. Poids. Classe. et le style ont des caractéristiques uniques pour chaque catégorie de produits. Supposons qu'il y ait des requêtes fréquentes pour les accessoires qui ont sous-catégories entre 27 et 36 inclus. Vous pouvez améliorer les performances des requêtes pour les accessoires en créant un index filtré sur les sous-catégories accessoires comme le montre l'exemple suivant.
Les FIProductAccessories d'index filtrés couvre la requête suivante parce que la requête
les résultats sont contenus dans l'index et le plan de requête ne comprend pas une table de base recherche. Par exemple, l'expression de prédicat de la requête ProductSubcategoryID = 33 est un sous-ensemble de l'index filtré prédicat ProductSubcategoryID> = 27 et ProductSubcategoryID <= 36. the ProductSubcategoryID and ListPrice columns in the query predicate are both key columns in the index, and name is stored in the leaf level of the index as an included column.
Colonnes clés
Il est recommandé d'inclure un petit nombre de colonnes clés ou inclus dans la définition d'index filtré, et d'incorporer uniquement les colonnes qui sont nécessaires pour l'optimiseur de requête pour choisir l'index filtré pour le plan d'exécution de la requête. L'optimiseur de requête peut choisir un index filtré pour la requête, peu importe si elle ou ne couvre pas la requête. Cependant, l'optimiseur de requêtes est plus susceptible de choisir un index filtré si elle couvre la requête.
Dans certains cas, un index filtré couvre la requête sans inclure les colonnes dans l'expression d'index filtré sous forme de colonnes clés ou inclus dans la définition d'index filtré. Les directives suivantes expliquent une colonne dans l'expression d'index filtré doit être une clé ou d'une colonne incluse dans la définition d'index filtré. Les exemples se rapportent à l'index filtré, FIBillOfMaterialsWithEndDate qui a été créé précédemment.
Une colonne dans l'expression d'index filtré n'a pas besoin d'être une colonne clé ou incluse dans la définition d'index filtré si l'expression d'index filtré est équivalent au prédicat de la requête et la requête ne retourne pas la colonne dans l'expression d'index filtré avec les résultats de la requête . Par exemple, FIBillOfMaterialsWithEndDate couvre la requête suivante car le prédicat de requête est équivalente à l'expression de filtre, et EndDate n'est pas retourné avec les résultats de la requête. FIBillOfMaterialsWithEndDate n'a pas besoin EndDate comme une clé ou d'une colonne incluse dans la définition d'index filtré.
Une colonne dans l'expression d'index filtré doit être une colonne clé ou incluse dans la définition d'index filtré si la colonne est dans le jeu de résultats de la requête. Par exemple, FIBillOfMaterialsWithEndDate ne couvre pas la requête suivante car elle retourne la colonne EndDate dans les résultats de la requête. Par conséquent, EndDate devrait être une clé ou d'une colonne incluse dans la définition d'index filtré.