Index : ordre des colonnes

Bonjour,

Je viens vers vous car depuis hier j'ai une question qui trotte dans ma tête...

Chez un client, nous avons une base de données assez volumineuse, qui offre des performances très médiocres.

La faute avant tout à la modélisation, mais c'est un modèle éditeur, donc faut se faire une raison : faut faire avec.

En revanche, j'ai lu il y a quelques jours un article de SQLpro qui date un peu (à l'époque où 4 Go de RAM pour un serveur était le bout du monde) et orienté SQL Server, sur l'optimisation des bases de données, et notamment sur les index.

Il dit que l'ordre des colonnes doit être du plus discriminant vers le moins discriminant.

Hors, dans la base, les colonnes dans les index proposés par l'éditeur sont dans l'ordre... "fonctionnel".

A savoir, par exemple pour la table des "tiers" :

1/ Code société
2/ Type de tiers
3/ Code du tiers

Selon le contexte, on a entre 1 et 100 code sociétés différents, une dizaine de type de tiers différentes, et quelques centaines de milliers de codes différentes.

Pour un même numéro de société, on retrouve plus ou moins l'ensemble des types de tiers, et chaque type de tiers dans chaque société à sa numérotation propre, donc avec des doublons.

Si j'écoute l'arcicle de SQLpro, je suis tenté de recréer l'article avec les colonnes suivantes :

1/ Code du tiers
2/ Code société
3/ Type du tiers

Est-ce que je vais vraiment y gagner quelque chose, ou si c'est vraiment qu'une optimisation valable (à l'époque) pour SQL Server ?

J'ai d'autres tables où c'est plus gros encore :

Colonne1 : 100 valeurs uniques
Colonne2 : 10 valeurs uniques
Colonne3 : Plusieurs centaines de millions (milliards ?) de valeurs uniques
Colonne4 : 100 valeurs uniques
Colonne5 : 1 unique valeur (fonctionnalité pas utilisée)

=> Est-ce que si je passe colonne3 en premier je peux m'attendre à un miracle ? Au moins une petite amélioration ?

Bonjour,

Je n'avais pas entendu parler de cette règle du plus discriminant au moins discriminant, donc je veux bien moi aussi des avis d'expert :)

Ceci dit, attention à l'ordre des colonnes dans les index: si vous remplacez votre index comme vous l'indiquez:
1/ Code du tiers
2/ Code société
3/ Type du tiers

Alors le résultat peut être catastrophique selon l'appli. En effet, si l'appli a besoin de faire disons un select code_societe from table WHERE type_du_tiers=123 alors le moteur sera incapable d'utiliser l'index. Pour qu'un tel index soit utilisable, il faut une requête qui se base sur:
- seulement le Code du tiers, ou
- le code du tiers ET le Code société, ou
- le Code du tiers ET le Code société ET le type du tiers

Bref, ca dépend vraiment comment l'appli est codée.

L'index est l'index unique de la clé.
Donc on fait systématiquement les trois.
Ou alors tous sauf le code (tiens, faudra faire un second index en effet :D)

Mais on ne requête jamais société et type indépendamment.

Bonjour,

L'ordre des colonnes dépend de l'utilisation qui en est faite (donc les requêtes) et aucune règle arbitraire n'est bonne si elle ne tient pas compte des requêtes.
L'idée, c'est que un index sur A,B,C set à des critères sur A, ou sur A,B, ou sur A,B,C.
Il peut aussi servir pour B ou B,C dans le cas où il y a peu de valeurs distinctes sur A, grâce à l'index skip scan. C'est donc le contraire de la règle.

Citation:

---

Donc on fait systématiquement les trois.
Ou alors tous sauf le code (tiens, faudra faire un second index en effet :D)

---

Donc il faut mettre ce code à la fin et l'index sera utilisé dans tous les cas.

Il y a autre chose à voir. Si cest colonnes sont des foreign key vers des tables qui peuvent avoir des delete, il faut avoir un index qui commence par la foreign key, pour des raisons de verrous. Mais ici je suppose qu'on ne supprime pas des code tiers ou des societes.

Sinon, la meilleure ressource que je conaisse sur les index c'est: http://use-the-index-luke.com/fr/sql...dex-concatenes

Cordialement,
Franck.

D'accord, donc soit j'ai mal compris l'article de SQLpro, soit il s'agissait d'une spécificité de SQL Server.

Bon, fausse piste pour les mauvaises performances :)

Sinon, la base n'a pas de relation entre les tables (pas de FK explicites). C'est à mon avis la raison principale des mauvaises performances, même si c'est loin de tout expliquer ;) Mais bon, base éditeur, on peut rien changer à la structure sans risquer des bugs du programme...

Bonjour,

Comme l’a si bien indiqué Franck, ce sont primordialement les clauses where de vos requêtes qui dictent le ‘design’ de vos indexes. Par exemple on s’efforcera à mettre les colonnes sur lesquelles une égalité est appliquée en première position car c’est grâce aux premières colonnes de l’index que la partie scannée (de cet index) par Oracle est déterminée (index range scan). Ensuite il faudrait contextualiser la création d’un index servant à couvrir une requête avec la possibilité que cet index soit capable de couvrir une autre requête : faire d’une pierre deux coups :
Par exemple trois requêtes distinctes:

Q1 : where clause (a,b,c)
Q2 : where clause (b,c)
Q3: where clause (c)

En me limitant strictement à ce qui précède je créerai l’index suivant: Index (b,c,a)

Qui aura l’avantage de couvrir Q1 et Q2. Et si b est souvent dupliquée je pourrai voir ma requête Q3 honorée par un index skip scan qui en passant n’est pas un accès très performant et trop souvent représente une indication d’un manque d’un index précis.

https://community.oracle.com/message/12615758#12615758

Souvent aussi (comme signalé par Frank) lors de la création d’un indexe je pense à la possibilité (d’une pierre deux coups également) de couvrir la menace du lock et deadlock introduite par une foreign key non indexée lorsqu’on supprime des données de la table mère.

Par exemple, si ma FK du modèle qui précède est FK(c,b) alors dans ce cas l’index que j’ai créé plus haut index (b,c,a) couvre aussi cette FK parce qu’il commence par les deux colonnes de la FK (même si ce n’est pas dans le bon ordre de la FK)

http://www.toadworld.com/platforms/o...gy-part-i.aspx

Enfin, j’arrive à votre question. Vous devriez déjà comprendre par cet ‘Enfin’ que la décision de placer les colonnes de l’indexe selon leur critères de discrimination (leur nombre de distinct values) arrive en dernière position (du point de vue Oracle au moins). Par exemple, lorsque mes autres critères me garantissent l’efficacité et la bonne utilisation d’un index (a,b,c) aussi bien que l’index (b,a,c) ou peut être aussi (c,b,a) alors uniquement dans ce cas, je commencerai à analyser la colonne la mieux adaptée pour être en première position. Et le critère le plus important pour moi dans ce cas concerne la compressibilité de l’index et la possible utilisation de l’index skip scan (quoique cet accès est moins performant et souvent à éviter). Si vous compressez votre index (a,b,c) vous avez beaucoup de chance d’avoir un taux de compression optimal si vous positionnez la colonne qui contient le moins de valeur distinctes (la plus souvent répétée) en première position.

Conclusion

Lors de la création d’un index il faudrait dans l’ordre selon moi:

1. Essayer de couvrir le plus de requêtes possibles
2. Mettre les colonnes sur lesquelles une égalité est appliquée en première(s) position(s)
3. Pensez à la possibilité de couvrir en même temps la contrainte d’intégrité FK (au besoin)
4. Enfin, pour une compression optimale pensez à mettre les colonnes les plus souvent répétées en première position

Ca date un peu mais ... http://searchoracle.techtarget.com/t...hould-be-first

Citation:

---

Envoyé par Mohamed.Houri

..faire d’une pierre deux coups ...

---

Belle antistrophe rabelaisienne !