Comprendre l'indexation SQL Server

Précédent ◈ Suivant

Avec tant d'aspects de SQL Server pour couvrir et écrire, certains des principes de base sont souvent négligés. Il y a eu plusieurs personnes qui ont posé des questions au sujet de l'indexation avec un aperçu général des différences d'index non ordonnés en clusters et en cluster. En fonction du nombre de questions que nous avons reçues, cette astuce discutera des différences d'indices et des lignes directrices générales autour de l'indexation.

D'un simple point de vue SQL Server propose deux types d'index ordonnés en clusters et non ordonnés en clusters. Dans sa définition la plus simple un index ordonné en clusters est un indice qui stocke les données réelles et un index non cluster est juste un pointeur vers les données. Une table ne peut avoir qu'un seul index en cluster et jusqu'à 999 index non cluster (selon la version SQL). Si une table ne dispose pas d'un index ordonné en clusters, il est appelé Heap. Alors, qu'est-ce que cela signifie réellement?

Pour clarifier davantage, permet de jeter un oeil à ce que les indices font et pourquoi ils sont importants.

1 - Table sans index

Lorsque la requête est exécutée, car il n'y a pas d'index, SQL Server effectue un balayage de table sur la table pour regarder à travers chaque ligne pour déterminer si des enregistrements ont un nom de « Adams ». Cette requête a un coût estimé de la sous-arborescence 0,437103. Ceci est le coût de SQL Server pour exécuter la requête. Plus le nombre est moins gourmand en ressources SQL Server.

2- Table avec index non cluster sur colonne nom

Lorsque cette requête est exécutée, SQL Server utilise l'index pour faire un Index Seek et il doit faire un RID Lookup pour obtenir les données réelles. Vous pouvez voir le coût Subtree estimé de 0,263888 que c'est plus rapide que la requête ci-dessus.

3- Table avec index cluster sur colonne nom

Lorsque cette requête est exécutée, SQL Server ne l'Index Seek et que les points d'index aux pages de données réelles, le coût estimé est Subtree seulement 0,0044572. Ceci est de loin la méthode la plus rapide d'accès pour ce type de requête.

4- Table avec index non cluster sur colonne nom

Dans cette requête, nous ne sollicitons la colonne lastname. Étant donné que cette requête peut être gérée par seulement l'index non ordonnés en clusters (couvrant la requête), SQL Server n'a pas besoin d'accéder aux pages de données réelles. Sur la base de cette requête le coût de la sous-arborescence est estimée seulement 0,0033766. Comme vous pouvez le voir même mieux que l'exemple n ° 3.

Pour prendre un peu plus loin, la sortie ci-dessous est basée sur ayant un indice de cluster sur nom et aucun index non cluster. Vous pouvez voir que le coût de sous-arbre est toujours le même que le retour de toutes les colonnes, même si nous ne sélectionnons une colonne. Ainsi, l'indice non-cluster fonctionne mieux.

Pour cette requête, nous avons maintenant deux indices. A regroupés et non regroupés. La requête est exécutée dans le même que l'exemple 2. A partir de cette sortie, vous pouvez voir que la recherche du RID a été remplacé par un index en cluster de recherche. Dans l'ensemble, il est le même type d'opérations, sauf en utilisant l'index cluster. Le coût subtree 0,264017. Ceci est presque le même que l'exemple 2.

Donc, à partir de ces exemples que vous pouvez voir les avantages de l'utilisation des index. Ce tableau d'exemple seulement avait 20.000 lignes de données, de sorte que cela est assez faible par rapport à la plupart des tables de base de données. Vous pouvez probablement imaginer l'impact que cela aurait sur de très grandes tables.

Une autre chose que vous pouvez voir à partir de ces exemples est la capacité d'utiliser des index couvrant non-cluster où l'indice satisfait l'ensemble des résultats. Ceci est également plus rapide alors devoir aller dans les pages de données du segment de mémoire ou index en cluster.

Prochaines étapes

SQL Server dispose de deux types d'index de bases. Ils sont regroupés et index non-cluster. Il y a quelques différences fondamentales aux deux qui sont essentiels à la compréhension avant de pouvoir maîtriser le réglage de l'index.

D'abord les choses qu'ils ont en commun.

Les deux index clusterisés et nonclustered peut être composé de plus d'une colonne. Les colonnes sont placées dans l'index dans l'ordre que vous indiquez dans l'instruction CREATE INDEX (ou l'ordre dans lequel ils sont présentés dans l'interface utilisateur). Ils sont également classés dans cet ordre aussi bien. Les index sont d'abord triés sur la première colonne dans l'index, puis les doublons de la première colonne et triées par la deuxième colonne, etc. Vous pouvez avoir jusqu'à 16 colonnes spécifiées comme colonnes indexées.

Ni clusterisé ou les index non-cluster garantissent l'ordre de tri des données lorsqu'il est retourné. Si l'ordre des questions de données à vous, vous devriez toujours trier les données avec la clause ORDER BY dans votre instruction select.

Les deux index clusterisés et les index non ordonnés en clusters occupent de l'espace disque supplémentaire. La quantité d'espace dont ils ont besoin dépendra des colonnes de l'index, et le nombre de lignes dans le tableau. L'index cluster va également croître que vous ajoutez des colonnes à la table (continuez à lire, ça va donner un sens plus tard).

Les colonnes des types de données TEXT, NTEXT et image ne peut pas être indexés selon les indices normaux. Les colonnes de ces types de données ne peuvent être indexés avec des index de texte intégral.

Si vous souhaitez reconstruire vos index en ligne (sans verrouiller la table) et ont l'édition Enterprise ne indexent TEXT, NTEXT, IMAGE, VARCHAR (MAX), NVARCHAR (MAX), les types de données VARBINARY (MAX) comme incluant des colonnes avec ces types de données il faudra que vous reconstruisez hors ligne d'index.