Inscrivez-vous gratuitement
pour pouvoir participer, suivre les réponses en temps réel, voter pour les messages, poser vos propres questions et recevoir la newsletter
[SQL 2008] Indexation de table
Bonjour,
Juste un petite question, pour les indexs sur des tables, je lis que l'ordre des champs dans un index doit être du plus précis au moins précis, j'ai un peu de mal à comprendre cette définition, si quelqu'un pouvait éclairer ma lanterne.
Merci.
Bonjour,
Vous pouvez prendre l'image d'un annuaire téléphonique qui est un bel index.
Il est composé d'un index: (Pays, région,) Ville, Nom.
Ville est plus "filtrant", plus précis que nom.
C'est pareil pour SQL Server.
J'espère que cela à pu vous éclaircir.
Bonne journée
donc si j'ai bien compris, si je veux faire un idex avec pays,dpt et ville jet le mets dans cet ordre : Ville/Dpt/Pays.
C'est bien ça ?
Dans l'autre sens:
Pays/Dpt/Ville
Ok et dans la clause WHERE il faut que ça soit dans le même ordre ?
Non l'optimiseur de requêtes se débrouille tout seul pour pouvoir utiliser votre index.
++
Ca n'a pas d'importance, l'optimiseur devrait s'en sortir comme un grand.
Cependant, si vous avez l'occasion de l'écrire dans l'ordre, pourquoi ne pas en profiter ?
Merci beaucoup
En lisant le cour de sqlpro sur developpez : http://sqlpro.developpez.com/cours/quoi-indexer/ je viens de lire ça :
Si je suis ce qui est dit, pour l'exemple de l'annuaire ça donnerait comme index Ville/Département/Pays et non l'inverse, qu'est-ce que je n'ai pas compris ?Comme nous l'avons déjà vu, un index multicolonne ne sera efficace que pour des recherches dans le sens du vecteur constitué par les colonnes dans l'ordre positionnel. Il est donc inefficace de créer un index multicolonne pour des requêtes qui filtrent alternativement sur l'une ou l'autre colonne exclusivement. Néanmoins lorsque l'on est en mesure de tirer bénéfice d'un index multicolonne, il est intéressant de bien choisir l'ordre des colonnes dans le vecteur.
En effet, on obtiendra une efficacité plus grande en posant en premier les colonnes ayant la plus forte dispersion.
Par exemple s'il faut créer un index sur le sexe, le prénom et le nom, il y a fort à parier que la meilleure combinaison sera nom + prenom + sexe (dans cet ordre précis).
En fait, la dispersion des données est plus forte pour un nom qu'un prénom (il y a moins de prénoms différents que de noms de famille différents) et bien plus encore par rapport au sexe qui ne présente généralement que deux valeurs.
Effectivement, dans ce cas l'ordre dans lequel vous suggériez les colonnes est plus interessant.
Dans BOL:
Sur le site SQL Server Performances:Consider the order of the columns if the index will contain multiple columns. The column that is used in the WHERE clause in an equal to (=), greater than (>), less than (<), or BETWEEN search condition, or participates in a join, should be placed first. Additional columns should be ordered based on their level of distinctness, that is, from the most distinct to the least distinct.
Cependant, dans le livre MVP Deep dives, chapitre 41, en regard des performances sur les covering indexes, section "some rules of thumb about indexes aren't true for covering indexes":When you create an index with a composite key, the order of the columns of the key is important. Try to order the columns in the key to enhance selectivity, with the most selective columns to the leftmost of the key. If you don't do this, and put a non-selective column as the first part of the key, you risk having the Query Optimizer not use the index at all. Generally, a column should be at least 95% unique in order for it to be considered selective. [6.5, 7.0, 2000] Updated 10-02-2006
De plus, plus loin dans le chapitre on peut y lire la création d'index tels que:The rule that you need to have the most selective column first in your index isn't true for covering indexes, either. For instance, even though ContactID is more selective than FirstName, the following query runs faster against an index on (FirstName, ContactID) than against an index on (ContactID,FirstName):
Select distinct [ContactID]
from [Person].[Contact]
Where [FirstName] = N'Jane'
Ou l'on peut remarquer que l'adresse email se situe en dernière position.Create nonclustered index [IX_Contact_Covering] on [Person].[Contact]
(
[LastName], [FirstName], [Phone], [EmailAddress]
)
Donc ma question est:
Est ce que votre index est utilisé comme covering index ?
Ma suggestion est:
Effectuer des tests avec les 2 index différents pour voir lequel est le plus efficace dans votre cas, en fonction des queries que vous tournez.
Je ne sais pas ce qu'est un covering index.
Dans mon cas je cherche une règle pour pouvoir créer des index, gérant un grand nombre de table et ne connaissant pas forcément les applications je ne peux pas me permettre de tester chaque requête pour voir dans quelle ordre l'index sera le plus performant
Sans connaitre les queries exécutées sur vos tables, difficile de prédire quel index sera le mieux optimisé.
Il doit bien y avoir une règle pour que ça marche dans la plupart des cas.
Parce que là, pour les deux exemples, les index sont créés à l'inverse.
Ordonne tes colonnes dans un ordre de porbabilité.
En 1 : la colonne, parmi celles de ton index, sur laquelle il y a le plus de chance que tu fasses une recherches.
En ....
En ....
En ....
En dernier : la colonne, parmi celles de ton index, sur laquelle il y a le moins de chance que tu fasses une recherches.
Demande toi comment tu vas interroger ta base de donner, cela devrait te permettre de déterminer ta ou tes indexes.
S'il y a des liens entre table, pense bien à partir de quelles tables devraient commencer les recherches.
Si tes queries seront "Qui a un pull vert ?" et "Qui a un pull rouge ?", la recherche devrait partir de la table des pulls (qui aura un index commençant par les couleurs) pour aller vers la table des personnes qui ne changent pas de pull (qui aura un index commençant par l'identifiant du pull de la personne).
Comme le précise SQLPro l'index ne sera efficace que dans le sens de la recherche.Si je suis ce qui est dit, pour l'exemple de l'annuaire ça donnerait comme index Ville/Département/Pays et non l'inverse, qu'est-ce que je n'ai pas compris ?
Si votre recherche se fait par ville / département / pays ou ville / département ou seulement la ville l'index sera efficace. Il faut noter également que la colonne ville a la plus forte dispersion.
Cependant si votre recherche se fait uniquement sur un critère pays ou pays / département alors l'index ne sera pas utilisé car le sens de la recheche ne se fait pas sur le vecteur formé par l'index ...
Personellement je ne suis pas sûr qu'il existe une règle générale .. les index doivent être posés et décidés en fonction des requêtes et en fonction des données de la table et de leur selectivité.
Si je prends l'exmple donné par SergeJack :
Que se passe t'il si je cherche les personnes portant un pull vert et que tous les pulls portés sont verts ? Il y a de fort à parier qu'aucun index ne sera utilisé dans ce cas dans la table des pulls .... vous aurez donc un index inutile en terme de recherche, de place disque et de consommation de ressource I/O.Si tes queries seront "Qui a un pull vert ?" et "Qui a un pull rouge ?", la recherche devrait partir de la table des pulls (qui aura un index commençant par les couleurs) pour aller vers la table des personnes qui ne changent pas de pull (qui aura un index commençant par l'identifiant du pull de la personne).
++
Cette remarque s'appliquerait à n'importe quel index.Envoyé par mikedavem
"Si on a une valeur non unique et toujours identique dans une table, un index sur cette colonne n'aiderait en rien", ce cas de figure n'ayant aucun intérêt vu que si on a une valeur à jamais constante, il n'y a aucun intérêt à la conserver en DB surtout si on ne parle pas de tables normalisées.
Nous sommes d'accord ...
C'est bien pour cela que cette phrase :
qui répond à celle-ci :la colonne, parmi celles de ton index, sur laquelle il y a le plus de chance que tu fasses une recherches
n'est pas forcement juste à mon avis. Ce n'est pas une règle générale à appliquer dans tous les cas sans avoir pris la peine de regarder les données sous jacentes de la table.Dans mon cas je cherche une règle pour pouvoir créer des index, gérant un grand nombre de table et ne connaissant pas forcément les applications je ne peux pas me permettre de tester chaque requête pour voir dans quelle ordre l'index sera le plus performant
++
Bonjour,
Cette question je l'ai retournée un peu dans tous les sens cette nuit en réfléchissant à ce qui change entre un ordre de colonne et l'autre:
Soit si l'on a une colonne selective en premier, d'après moi, se sera plus efficace pour la recherche d'éléments distincts.
Si l'on choisis l'autre ordre d'index, avec les colonnes les plus selectives à la fin, d'après moi toujours, se sera plus efficace pour des requètes d'aggrégation car l'on pourra prendre directement un "range" de l'index, sans avoir à parcourir chaque clef selective jusque la fin de l'index.
Ce qui me ferait dire que sur une DB OLTP, il serait préférable, à priori, d'utiliser un index avec les colonnes sélectives en premier si les recherches sont ciblées sur des éléments bien particuliers alors que sur des DB destinées à du reporting, il serait à priori plus interessant de mettre les clef les plus selectives à la fin de l'index.
Cependant, je reste convaincu qu'il n'y a pas une règle out-of-the-box magique à appliquer pour que tous les index soient optimums sans connaitre les différents accès à la table.
Bonne journée
Qu'entends tu par "regarder les données sous jacentes de la table" ?
J'entends par là vérifier que la pose d'un index sur une colonne est judicieux en fonction d'une éventuelle requête ...
Je dis cela parce que j'ai souvent vu là où je suis actuellement des gens mettre des index sur des colonnes pour essayer d'accéler les recherches (ce qui est plutôt une bonne intention) et de s'apercevoir par la suite que cet index n'était pas pris en compte ... (Un exemple au hasard : index posé sur une colonne nommée sexe ayant pour valeur H/F). Je sais l'exemple paraît bête mais cela reflète une réalité certaine).
En fait j'attire juste l'attention d'être extrêmement prudent avec la généralisation des règles pour la pose d'un index car cela me paraît dangereux sans avoir pris un minimum de précaution comme la connaissance des requêtes existantes sur une table et les données qu'elles comportent.
Par contre je trouve plus judicieux d'utiliser les vues systèmes offertes par SQL Server pour une aide à la décision (sys.dm_db_index_usage_stats, sys.dm_db_missing_index_details, sys.dm_db_missing_index_groups et sys.dm_db_missing_index_group_stats) et éventuellement l'outil de performance DTA dans ce cas précis.
++
Répondre avec citation
Partager