Clé primaire composée > clustered ?

Bonjour,

je suis en train de créer mes tables pour une base sous SQL Server. J'ai des tables d'association du genre :

T_UTILISATEUR_GROUPE : {#ID_UTILISATEUR, #ID_GROUPE}

Lors de la création de ma PK, je peux spécifier si c'est clustered ou non. Dans l'aide, ils disent qu'en clustered, ça a une incidence sur l'ordre physique des données dans la table et que ça convient très bien aux auto incréments.

Au feeling, je dirais qu'une PK composée ne devrait pas être clustered mais j'aimerais un éclairage sur ce point.

Merci

Difficile de répondre sans connaître le fonctionnel derrière. Par exemple si les clefs sont ordonnées naturellement (monotonie), alors pas de problème.

Exemple :
Table COMMANDE => CMD_ID : clef auto
Table PRODUIT => PRD_ID : clef auto
TABLE COMMANE_PRODUIT => PRD_ID, CMD_ID : clef

Dans ce cas il vaudrait mieux avoir : CMD_ID, PRD_ID en clef car les commandes vont croissante.

A +

J'ai plusieurs tables d'associations dans mon modèle, j'ai présenté T_UTILISATEUR_GROUPE à titre d'exemple car elle ne figure pas dans mon modèle. Cependant j'aimerais vraiment bien déclarer mes clés composées car je comprends qu'en fonction des besoins, on pourra avoir des solutions physiques différentes.

Si je reprends T_UTILISATEUR_GROUPE, on peut se demander si on interroger cette table plutôt par utilisateur ou plutôt par groupe :

- "Tous les groupes auquel appartient un utilisateur" > servirait pour dire à quoi a accès un utilisateur par exemple
ou
- "Tous les utilisateurs d'un groupe" > avoir une vue d'ensemble de l'affectation à ce groupe

On peut aussi se poser la question pour l'insertion :

- "Ajout d'utilisateurs dans un groupe"
ou
- "Affectation de groupes à un utilisateur"

Dans l'application finale il y aura un de ces axe plus souvent utilisé mais l'autre , dans une moindre mesure, pourra aussi être utilisé.

Dans ce type de reflexion, quelles sont les bonnes questions à se poser ? Quelles sont les solutions physiques à mettre en place : ordre des champs dans l'index multicolonne ? index clustered ?


Ces questions de sens me font tourner en rond et j'aimerais bien avoir des éléments supplémentaires de réponse car je n'arrive à aucune conclusion tout seul.

Bonjour,

Je suis plus familier de DB2 que de SQL Server, mais je soupçonne ce dernier d’avoir recopié son grand cousin, qui permet la mise en oeuvre des index "cluster" depuis déjà 25 ans. Dans ce qui suit, je me situe dans un contexte DB2.

Un tel index mérite une attention toute particulière, puisque, tant qu’on n’est pas dans un univers quantique, il ne peut y en avoir qu’un par table. Là réside le secret de la performance des traitements transactionnels lourds (si je puis dire) et surtout batch.

En effet, considérez la composition de la clé d’un tel index : {Col1, Col2, ..., Coln}. Appelons XC cet index. Considérez encore que je réorganise la table T sur laquelle est branché XC : DB2 range physiquement les lignes de la table T selon la séquence des clés de XC.

Illustration. Considérez maintenant les deux tables suivantes des commandes et des lignes de commande (clés primaires soulignées) :

Commande {CdeId, NumeroCde, Date, CLientId, ...}

LigneCommande {LigneId, CdeId, ProduitId, Quantité, ...}

CdeId étant clé étrangère au sein de LigneCommande.

Supposons encore, au niveau physique cette fois-ci, qu’il y ait un index XLCdeCde branché sur la colonne CdeId de la table LigneCommande et que cet index soit déclaré cluster :

Si au cours d’une transaction on récupère les données de la commande NumeroCde = 1234, y-compris les lignes de commande correspondantes, DB2 ira chercher directement ces lignes dans la page physique où ces lignes ont été regroupées. Coût de l’opération relativement aux lignes de commande : de l’ordre de deux lectures "physiques" pour l’index XLCdeCde et une seule lecture pour récupérer l’ensemble les lignes de commande (en admettant qu’elles tiennent toutes dans une page physique).

Au contraire, si l’index XLCdeCde n’est pas cluster, vous pouvez en théorie avoir autant de lectures physiques que de lignes de commande, puisque celles-ci peuvent être éparpillées dans des pages distinctes. A 10 millisecondes la lecture de page, cela peut finir par coûter cher.

Maintenant, vous pouvez avoir intérêt (et je vous engage vivement à procéder ainsi) à identifier LigneCommande relativement à Commande, car au fond, la ligne de commande n’est jamais qu’une propriété (multivaluée) de la commande. Le jeu de clés devient le suivant :

Commande {CdeId, NumeroCde, Date, TauxTVA, ...}

LigneCommande {CdeId, LigneId, ProduitId, Quantité, ...}

La clé primaire de LigneCommande est alors la suivante : {CdeId, LigneId} et devient la clé de l’index XLCdeCde (toujours cluster).

Vous me direz : quelle est la valeur ajoutée par rapport à l’organisation précédente ? Elle existe et, à titre d’exercice, je vous demande de la découvrir, quand par exemple chaque ligne de commande se décompose elle-même en engagements sur ligne de commande et que transactions et batch doivent engendrer un minimum de lectures à 10 millisecondes.

Ce qui vaut pour les lectures vaut bien entendu pour les écritures (création, modification, suppression de commandes).

Si quelqu’un connaît bien SQL Server, merci de confirmer que le comportement des index cluster est comparable à celui que je viens de décrire.

Oui il s'agit bien de la même notion :

Source MSDN

Citation:

---

A clustered index determines the physical order of data in a table. A clustered index is analogous to a telephone directory, which arranges data by last name. Because the clustered index dictates the physical storage order of the data in the table, a table can contain only one clustered index. However, the index can comprise multiple columns (a composite index), like the way a telephone directory is organized by last name and first name.

---

Je comprends parfaitement l'intérêt dans le cadre d'une entité faible (si je m'exprime bien) avec l'exemple commande/lignecommande (que j'ai dans mon modèle).

Par contre, dans le cadre d'une table d'association telle utilisateur/groupe dans mon autre exemple, quelle serait votre manière de procéder ?

- Vous étudiez quel axe d'approche de cette table sera le plus fréquent et vous mettez un index cluster pour cet axe
- Vous n'utilisez pas d'index clustered
- Autre


De plus vous soulevez l'impact d'un index clustered sur les opération d'insertion/suppression. Etant donné que les données sont physiquement écrites dans l'ordre de la clé composée, dans le cas où on ajouterait un enregistrement qui ne serait pas dans la dernière séquence, il faut alors que le SGBD décalle physiquement tous les enregistrements suivants pour y mettre le nouveau ?

Si c'est vrai, alors il faut vraiment considérer l'ordre naturel des ajouts/suppression dans l'application ainsi que leur fréquence. Cette considération irait de paire avec l'axe de lecture le plus souvent utilisé.

Pas évident tout ça...

edit : cette discussion est très intéressante pour moi. Je vous remercie déjà de votre participation.

Après avoir donné le lien pour confirmer qu'il s'agissait bien de la même notion sur DB2 et SQL Server, j'ai lu l'article complet.

Et effectivement, il y a bien la problématique des insertions/suppressions :

Citation:

---

Clustered indexes are not a good choice for:

* Columns that undergo frequent changes

This results in the entire row moving (because SQL Server must keep the data values of a row in physical order). This is an important consideration in high-volume transaction processing systems where data tends to be volatile.

---

Pour les cas favorables, ça va totalement dans le sens que vous exprimiez. Je vais donc méditer sur mes cas pour faire mes choix.

Mais si vous voulez compléter, n'hésitez pas car c'est toujours très intéressant.

Citation:

---

Envoyé par vmolines

Par contre, dans le cadre d'une table d'association telle utilisateur/groupe dans mon autre exemple, quelle serait votre manière de procéder ?

---

Suite aux constats que j’ai pu faire dans pas mal de projets, j’en suis venu à me poser comme postulat que ce sont les traitements qui orientent nos choix, en relation avec le comportement du sous-système d’entrées/sorties. Permettez-moi de changer d’exemple. Supposons que l’univers du discours soit celui d’une Caisse de retraite. Régulièrement, on exécute des traitements très lourds, mais aussi très sensibles, consistant à récupérer les sous dont la Caisse a besoin pour vivre et faire vivre les retraités qui ont cotisé. Chercher les sous se traduit par un appel de cotisations auprès des entreprises dont les salariés cotisent actuellement à la Caisse. Ces traitements périodiques incitent à "clustériser" sur l’identifiant des entreprises clientes, puisque les données dont a besoin s’y rattachent et constituent le gros des tables. Ainsi, les traitements seront plutôt CPU bound (s’ils sont ralentis, c’est parce que la puissance en MIPS est insuffisante) qu’I/O bound (ralentissement dû à l’attente de fin des entrées/sorties sur les disques, pendant que la CPU se tourne les pouces). L’effet CPU bound peut-être amélioré par un accroissement de la puissance des processeurs, tandis que l’effet I/O bound ne se corrige pas aussi facilement. Et croyez-moi, il est très frustrant d’observer au gnagnascope que la CPU est au repos et qu’en contrepartie la durée des traitements est multipliée par un facteur 5 ou 6. En passant, il vaut mieux observer le phénomène lors des séances de prototypage que le découvrir lors de la mise en production, quand certaines tables comportent quelques centaines de millions de lignes et qu’il devient très délicat de procéder à des aménagements structurels. Pour en revenir à l’identifiant de l’entreprise, en admettant qu’il s’agit-là du choix le plus pertinent pour clustériser, en toute logique il doit de propager dans chaque nœud de l’arborescence, jusqu’au niveau feuille. Cela suppose la mise en œuvre systématique de l’identification relative : ceci correspond à ma stratégie et je m’en suis toujours bien porté (pas de réaction négative de la part des productions).
Maintenant, on peut avoir à broder sur le thème. Ainsi, quand on conserve les données sur quarante ans, celles des exercices antérieurs ne participent pas aux appels de cotisation et il est donc préférable de ne pas rendre les entreprises obèses : on peut concevoir un cluster sur le couple (dans l’ordre) {Date, Entreprise} : l’entreprise Tartempion se retrouvera dans 40 partitions distinctes (si Date est en fait l’exercice), ou 80 partitions (si Date est en fait le semestre), etc. Je ne sais pas à combien de partitions on a droit avec SQL Server, mais avec DB2 je n’ai pas de soucis.
Vous me direz que certains traitements journaliers et périodiques vont être pénalisés par mon choix de cluster : certes, mais, mais tout se mesure et se prévoit. Si le score final dit que mon choix est finalement mauvais, au moins aurais-je eu le mérite de prototyper pour avoir une connaissance objective de la performance de l’application, et pouvoir réagir avant d’atteindre un point de non-retour.
Dans la série les "clashs", considérez l’activité d’une banque de dépôts : dans la journée, les traitements transactionnels jouent à plein, parce que les clients sont en relation avec les attachés, tandis que la nuit, les mouvements bancaires sont exploités dans tous les sens. Et c’est la nuit que l’on traite des sous, en masse. Il faudra vraisemblablement choisir le cluster en sorte de pouvoir réduire les I/O bound nocturnes, alors que pendant la journée on peut envisager une dégradation dont l’utilisateur n’aura pas conscience.

=>

Quoi qu'on dise, le lien identification relative avec l'effet I/O bound montre que celui-ci a un impact fort du la modélisation conceptuelle. Telle est ma position après tant d'années à l'avoir vérifié. Tout du moins auprès des grands comptes.

Citation:

---

Envoyé par vmolines

De plus vous soulevez l'impact d'un index clustered sur les opération d'insertion/suppression. Etant donné que les données sont physiquement écrites dans l'ordre de la clé composée, dans le cas où on ajouterait un enregistrement qui ne serait pas dans la dernière séquence, il faut alors que le SGBD décalle physiquement tous les enregistrements suivants pour y mettre le nouveau ?

---

Ne connaissant pas les internes de SQL Server, j’aurai du mal à traiter du décalage des enregistrements...

Concernant DB2, je connais mieux et ne suis pas inquiet quant aux mises-à-jour, dans la mesure où celles-ci sont en nombre raisonnable.

En effet, quand je crée un table space (structure d’accueil d’une table) ou un index (cluster ou non), j’ai par exemple la possibilité de définir du free space : lors du chargement d’une table ou à l’occasion de sa réorganisation, je demande au SGBD de geler de la place, qui ne sera consommée que lors des INSERT et UPDATE. Ainsi, en fonction de la fréquence de réorganisation d’une table, convenue avec la Production, et ayant une connaissance à peu près pertinente de la fréquence des mises-à-jour (en générale régulières, mais avec des pics), je définirai un taux de free space qui devrait tourner autour de 10%, 20%. Tant qu’il y a de la place dans les pages, pas de problème. Si la page cible d’un insert (par exemple ajout d’une ligne de commande dans la table des lignes de commande) est pleine, je sais qu’avec DB2 les choses se passeront bien. Celui-ci cherchera une page libre, dans le voisinage de la page cible, car les rafales d’I/O dans les caches feront que la perte de temps sera minime (au moins pour les traitements de masse, tandis qu’en transactionnel cela peut finir par être sensible). Cette page sera considérée comme appendice de la précédente. Pour que cet effet de voisinage proche soit effectif, DB2 me permet de geler des pages complètes tout les tant de pages (effet free page), toujours par paramétrage au niveau table space ou index. Si la place gelée finit par être totalement consommée par les INSERT, il est temps de réorganiser la table en cause (les taux de désorganisation sont consultables dans les tables du catalogue relationnel). En tout cas, je sais que l’accès à une ligne d’une table ne sera jamais très pénalisant, car sous le capot, DB2 gère ses pointeurs de telle façon que pour accéder à une ligne, il y aura au plus deux entrées/sorties pour retrouver celle-ci, quand elle n’a pas trouvé de place dans sa page d’ancrage : pas de problème de décalage, ou si peu...

Concernant en particulier un index cluster, si vous jouez sur la stabilité, l’invariance des clés (l’identifiant de l’entreprise dans mon message précédent), les problèmes s’éloignent (au moins avec DB2), vous comprendrez pourquoi. Le prix à payer ? Vous l’aurez deviné : invariance des identifiants à prendre en compte lors de la modélisation, outre le mécanisme de l’identification relative.

Effectivement les index cluster sont à la manière de DB2.

Une autre considération doit aussi être prise en compte :
en l'absence d'inde cluster SQL Server utiliser une référence composée de 3 entiers pour retrouver toute ligne de table. Cette référence est composée comme suit :
1) le n° du fichier physique
2) le n° de la page dans le fichier
3) le n° de slot de la ligne dans la page.

En regard de l'auto incrément sur un seul entier, le coût de stockage des référence de ligne dans une table non cluster est donc... triple !

C'est pourquoi beuacoup d'auteurs, dont je fais partie, considérent que TOUTE TABLE DEVRAIT AVOIR UN INDEX CLUSTER.... Le problème est donc de bien le choisir.
Pour ma part, l'expérience m'a montrée que le mieux à tout niveau est de le limiter à une seule colonne de type entier dont la longueur est celle du mot du proceseur.
Si certaines tables sont sensiblement impactées, alors casser le modèle pour n'avoir plus qu'un auto incrément.

Ainsi dans l'exemple de fsmrel, une solution consiste à passer à :
LigneCommande {LicId (A+), CdeId, LigneId, ProduitId, Quantité, ...}
C'est à dire une clef auto incrémentée "artificielle" de ligne de commande (LicId), tout en conservant une contrainte d'unicité sur (CdeId, LigneId).

Mais tout ceci n'est à décider qu'en fonction :
du volume attendu des données (si quelques Go... inutile !)
du volume des transactions à effectuer...

De plus, il faut faire attention à éviter d'utiliser sustématiquement du VARCHAR, car cela provoque des décalage de lignes à l'update et donc de la lecture en zigzag qu'il convient de remédier par une sévère maintenance des index...

Bref, l'optimisation par "design" est une des meilleurs choses possible, mais il faut penser à tout !

A +

Bonjour,

Merci encore pour vos interventions.

Vous allez rire et me dire que je me casse la tête pour rien. La base de données de tarification/facturation que je suis en train de designer aura une volumétrie de 40mo par an :roll:. Cependant je suis un extrêmiste d'un bon design et même si c'est "superflu" dans le cas présent, je me dis qu'il vaut mieux partir sur de bonnes bases quand on a la chance de pouvoir designer de zéro mais aussi pour avoir les bons réflexes dans des cas futurs.

Toujours est il que j'ai à peu près tous les exemples cités :

- facture/lignefacture de client

A l'heure actuelle j'ai T_LigneFacture avec en PK ID_LigneFacture en auto incrément (cluster donc). J'ai ID_Facture en clé étrangère indexée mais il ne fait pas partie de la PK.

Il est vraiment difficile de trancher entre :

. {ID_LigneFacture int} (factice) PK autoincrément cluster + {ID_Facture int, IDLigneFactureRelatif smallint} FK indexé
. {ID_Facture int, IDLigneFacture autoincrément} PK indexé cluster

Sachant que je ne sais pas trop comment géré l'identification relative. Je suppose qu'il faut gérer ça avec une table des clés qui tient compte de la première partie de la clé (ID_Facture). Ceci dit ça amène pas mal de complexité juste pour la beauté de la chose...

Citation:

---

Envoyé par SQLpro

Ainsi dans l'exemple de fsmrel, une solution consiste à passer à :
LigneCommande {LicId (A+), CdeId, LigneId, ProduitId, Quantité, ...}
C'est à dire une clef auto incrémentée "artificielle" de ligne de commande (LicId), tout en conservant une contrainte d'unicité sur (CdeId, LigneId).

---

Euh... Si la clé de l’index cluster de la table LigneCommande a pour seul composant LicId, les lignes de commande d’une commande donnée Cx vont, a priori, être physiquement éparpillées dans des pages distinctes et on est partis pour subir le phénomène d’I/O bound, à l’occasion de la recherche de l’ensemble des lignes de commande de la commande Cx (phénomène pouvant se révéler calamiteux pour la performance des applications, décrit dans un précédent message).

Dans ces conditions, Je préfère composer ainsi la clé de l’index cluster : {CdeId, LigneId}. Pour valoriser LigneId, on peut procéder, par exemple, par incrémentation relative à CdeId : Max(LigneId) +1. Évidemment, n’étant pas spécialiste de SQL Server, je ne prétends pas qu’une technique parfaitement rodée depuis 25 ans avec DB2 soit reconductible telle quelle vers d’autres SGBD, mais en l’occurrence il faut s’attacher à l’esprit plus qu’à la lettre. Il reste à vérifier que Max(LigneId) +1 est sargable et indexable —ce qui est vrai pour DB2 (alors que si j’écris Max(LigneId +1) ça ne l'est plus, mais cela relève du réglage des requêtes). Je dirai surtout que chacun doit réaliser un prototype de performances afin de pouvoir comparer objectivement, dans le cadre de son projet, le rendement des différentes stratégies et techniques et au final choisir en toute connaissance de cause.

Par ailleurs, quant à perdre quelques octets par enregistrement, je considère cela comme très secondaire. Bien sûr, si le perds systématiquement 32 octets, je ne suis pas bon et m’acharnerai à trouver mieux, mais s’il s’agit de deux ou quatre octets, il n’y a quand même pas péril en la demeure (ce qui était déjà vrai en 1983...)

Vous me direz : mais si la ligne de commande est elle-même déclinée en engagements, alors pour éviter l’effet I/O bound, la clé de l’index cluster de la table Engagement devra ressembler à ceci : {CdeId, LigneId, EngtId}. Si à son tour un engagement se décline en autre chose, la clé de cette autre chose va finir par devenir obèse. A cela je rétorque : le but de la manœuvre est bien d’éviter l’I/O bound, autrement dit, le nombre de colonnes composant la clé de l’index cluster (et en fait de la clé primaire) ne doit pas dépasser deux voire trois (un attribut ça va, deux attributs... etc.) Ainsi, la clé de la table Engagement sera ainsi composée : {CdeId, EngtId}, c'est-à-dire que la colonne EngtId est directement relative à CdeId, colonne dont la présence est indispensable. Cela ne pénalise en rien l’intégrité référentielle : la table Engagement comporte bien entendu la clé étrangère {CdeId, LigneId}.

Citation:

---

Envoyé par SQLpro

Bref, l'optimisation par "design" est une des meilleurs choses possible, mais il faut penser à tout !

---

Il est clair que tout est à vérifier (notamment par prototypage des performances), mais si on a raté son coup au niveau de la modélisation, c’est l’échec pratiquement assuré, quoi qu’on fasse ensuite. Depuis plus de vingt ans, j’ai pu l’observer avec bien des applications sensibles chez de très grands comptes, devant finalement faire appel à des SQLpro et fsmrel pour faire du curatif, tant qu'il est encore temps, avant d'avoir à envisager de reprendre à zéro ou presque...

Citation:

---

Envoyé par vmolines

Il est vraiment difficile de trancher entre :

. {ID_LigneFacture int} (factice) PK autoincrément cluster + {ID_Facture int, IDLigneFactureRelatif smallint} FK indexé
. {ID_Facture int, IDLigneFacture autoincrément} PK indexé cluster

Sachant que je ne sais pas trop comment géré l'identification relative. Je suppose qu'il faut gérer ça avec une table des clés qui tient compte de la première partie de la clé (ID_Facture). Ceci dit ça amène pas mal de complexité juste pour la beauté de la chose...

---

Vous ai-je apporté quelques arguments et éléments de réponse dans mon précédent message, ou bien dois-je reprendre à zéro l’alternative CPU bound vs I/O bound ? Croyez-moi, je suis sensible à la beauté des choses, mais en matière d’engagement auprès de mes clients quant à la performance des applications, je suis d’une extrême vigilance. Je touche du bois, mais j’ai réussi à ne jamais emmener mon entreprise au bain (une SSII), tant en ce qui concerne le succès des applications que, plus prosaïquement les clauses de pénalités. Et l’identification relative conjuguée à l’utilisation des index clusters, validées par le prototypage des performances ont été des éléments décisifs (évidemment pas les seuls, ne rêvons pas).

Ecoutez je pense qu'on a fait le tour. J'ai bien saisi les impacts des différentes solutions même si mon manque d'expérience ne me permet pas de les visualiser aussi bien que vous.

Je pense que je vais opter pour une solution à base de {IdFacture, IdLigneFacture} PK clustered car les lignes de factures sont quasi exclusivement attaquées par la facture. Par contre je vais m'économiser la complexité de la génération d'identifiants relatifs et gaspiller un peu de volume. En effet je n'ai pas énormément de temps et les volumes de cette base seront vraiment léger.

Pour cette partie Facture/LigneFacture, on a fait le tour.


Avec tous les éléments que vous m'avez apportés, je pense être en mesure de bien choisir mes PK et mes index pour les tables d'association n:m bien que le choix me semble bien moins évident que pour une entité faible.

Je pense que ces types de table, malheureusement, on peut avoir des axes d'interrogation sur l'une ou l'autre partie de la clé et qu'il y aura forcément un choix du moindre mal (de la plus commune utilisation) à faire pour décider du premier élément de l'index clustered sur la PK.