Index multiples vs index multi-colonnes

Question

Je viens d'ajouter un index à une table dans SQL Server 2005 et cela m'a fait réfléchir. Quelle est la différence entre créer un index et définir plusieurs colonnes plutôt que d'avoir un index par colonne que l'on veut indexer.

Y a-t-il des raisons particulières pour lesquelles l'un devrait être utilisé plutôt que l'autre ?

Par exemple

Create NonClustered Index IX_IndexName On TableName
(Column1 Asc, Column2 Asc, Column3 Asc)

Versus

Create NonClustered Index IX_IndexName1 On TableName
(Column1 Asc)

Create NonClustered Index IX_IndexName2 On TableName
(Column2 Asc)

Create NonClustered Index IX_IndexName3 On TableName
(Column3 Asc)

Answer 1

Je suis d'accord avec Cade Roux .

Cet article devrait vous mettre sur la bonne voie :

• Index dans SQL Server 2005/2008 - Meilleures pratiques, partie 1
• Index dans SQL Server 2005/2008 - Partie 2 - Internes

Une chose à noter, les index en grappe doivent avoir une clé unique (une colonne d'identité, je le recommande) comme première colonne. En fait, cela permet à vos données d'être insérées à la fin de l'index et de ne pas causer beaucoup d'entrées-sorties sur le disque et de divisions de pages.

Deuxièmement, si vous créez d'autres index sur vos données et qu'ils sont construits intelligemment, ils seront réutilisés.

par exemple, imaginez que vous recherchez un tableau sur trois colonnes

état, comté, code postal.

• vous pouvez parfois effectuer une recherche par État uniquement.
• vous pouvez parfois effectuer une recherche par état et par comté.
• vous pouvez fréquemment effectuer des recherches par état, comté, code postal.

Ensuite, un index avec l'état, le comté, le zip. sera utilisé dans ces trois recherches.

Si vous effectuez souvent des recherches uniquement par zip, l'index ci-dessus ne sera pas utilisé (par le serveur SQL de toute façon) car zip est la troisième partie de cet index et l'optimiseur de requêtes ne considérera pas cet index comme utile.

Vous pourriez alors créer un index sur Zip seul qui serait utilisé dans ce cas.

D'ailleurs Nous pouvons tirer parti du fait qu'avec l'indexation multi-colonnes, la première colonne d'index est toujours utilisable pour la recherche. et lorsque vous recherchez uniquement par 'état', c'est efficace mais pas aussi efficace qu'un index à colonne unique sur 'état'.

Je suppose que la réponse que vous cherchez est que cela dépend des clauses where de vos requêtes les plus fréquemment utilisées et également de vos group by.

Cet article sera d'une grande aide :-)

Answer 2

Oui. Je vous recommande de vérifier Articles de Kimberly Tripp sur l'indexation .

Si un index est "couvrant", alors il n'est pas nécessaire d'utiliser autre chose que l'index. Dans SQL Server 2005, vous pouvez également ajouter des colonnes supplémentaires à l'index qui ne font pas partie de la clé, ce qui peut éliminer les déplacements vers le reste de la ligne.

Le fait d'avoir plusieurs index, chacun sur une seule colonne, peut signifier qu'un seul index est utilisé - vous devrez vous référer au plan d'exécution pour voir les effets des différents schémas d'indexation.

Vous pouvez également utiliser l'assistant de réglage pour vous aider à déterminer quels index permettraient d'obtenir les meilleures performances pour une requête ou une charge de travail donnée.

Answer 3

L'index multi-colonnes peut être utilisé pour les requêtes faisant référence à tous les colonnes :

SELECT *
FROM TableName
WHERE Column1=1 AND Column2=2 AND Column3=3

Il peut être recherché directement à l'aide de l'index multi-colonnes. En revanche, on ne peut utiliser qu'un seul des index à une seule colonne (il faudrait rechercher tous les enregistrements ayant Column1=1, puis vérifier Column2 et Column3 dans chacun d'eux).

Answer 4

Un élément qui semble avoir été oublié est la transformation des étoiles. Index Intersection Les opérateurs résolvent le prédicat en calculant l'ensemble des lignes touchées par chacun des prédicats avant que toute E/S ne soit effectuée sur la table de faits. Dans un schéma en étoile, vous indexez chaque clé de dimension individuelle et l'optimiseur de requêtes peut déterminer les lignes à sélectionner par le calcul d'intersection d'index. Les index sur les colonnes individuelles offrent la meilleure flexibilité à cet égard.

Answer 5

Si vous avez des requêtes qui utiliseront fréquemment un ensemble relativement statique de colonnes, la création d'un seul index couvrant qui les inclut toutes améliorera considérablement les performances.

En plaçant plusieurs colonnes dans votre index, l'optimiseur ne devra accéder directement à la table que si une colonne ne figure pas dans l'index. J'utilise souvent ce type d'index dans l'entreposage de données. L'inconvénient est que cela peut coûter beaucoup de frais généraux, surtout si les données sont très volatiles.

La création d'index sur des colonnes uniques est utile pour les opérations de consultation que l'on trouve fréquemment dans les systèmes OLTP.

Vous devez vous demander pourquoi vous indexez les colonnes et comment elles seront utilisées. Exécutez quelques plans de requêtes et voyez quand elles sont accédées. L'optimisation des index est autant une question d'instinct que de science.