[MPD] Identifiant auto incrémenté

[MacFly58]

Bonsoir,

J'ouvre une discussion pour savoir dans quels cas vous n'utilisez pas un identifiant auto-incrémenté pour vos tables.

Il me semble que la liste que nous produirons sera très limitée, car l'auto-incrément doit bien souvent être le plus performant.

Le principal cas est bien sur la table de jointure (issue d'une association-type pour les Merisiens), mais j'espère que nous recenserons tous les cas, pour optimiser nos BDD.

Merci pour vos contributions.

[CinePhil]

Il y a aussi de la table d'héritage dont l'identifiant fait référence à celui de la table mère.

Une voiture est un véhicule et un véhicule peut être une voiture.

voiture -(1,1)----être----0,1- vehicule

te_vehicule_veh (veh_id, [colonnes communes à tous les véhicules])
th_voiture_vtr (vtr_id_vehicule, [colonnes spécifiques aux voitures])

Je vois aussi les tables d'archivage dans ce cas puisque toutes les lignes de la table source ne sont pas archivées et elles conservent leur identifiant qui ne peut donc pas être auto-incrémenté dans la table d'archivage.

[MacFly58]

Plutôt que de parler d'identifiant, j'aurai du parler de clé primaire pour être plus précis (dans le contexte SQL).

L'horodatage me semble efficace également (clé de type date ou date et heure).

On peut écarter le type VARCHAR qui n'est pas recommandé habituellement.

Une colonne de type CHAR pourrait faire l'affaire aussi je pense.

Avez-vous déjà eu des problèmes de rapidité sur vos BDD avec certains de ces types en clé primaire ?

Bien sur, toutes les sortes de clés primaires dont nous parlons ici sont non significatives et invariantes. C'est hors sujet, mais c'est juste un rappel pour les lecteurs.

[CinePhil]

Si la clé primaire est un horodatage ou même une simple date, elle devient signifiante.

Il me semble que le type date ou datetime est quand même plus complexe que le type entier et donc probablement plus lent à gérer pour le sgbd quand le volume de données devient conséquent.

Quant au CHAR, il est fort probable que son emploi soit généralement signifiant et susceptible d'être modifié. Et pour rester compétitif avec le type entier, il ne faut pas dépasser le CHAR(4), un entier étant généralement codé sur 4 octets.

[fsmrel]

Bonjour MacFly et Cinephil,

C'est un plaisir de vous revoir MacFly !

A mon sens, on peut toujours trouver une signification à une valeur, tout type est significatif. Si on utilise un simple auto-incrément (type entier) pour la clé, alors on peut inférer que telle ligne a été créée avant telle autre : la ligne dont la clé héberge la valeur 1 a été créée a priori avant la ligne qui héberge la valeur 314159265. En fait, l’absence de signification devient effective quand on utilise des valeurs aléatoires, quand on hache les clés avec un algorithme ad-hoc. Pour ma part j’ai eu à le faire dans bien des entreprises, à chaque fois que l’auto-incrémentation provoquait des phénomènes de contention, par exemple lors de la prise de commandes (INSERT nombreux dans la table CLIENT en TP). Le hachage fonctionne évidemment avec des clés dont les attributs peuvent être de tout type, par exemple TIMESTAMP (avec en l'occurrence une précision suffisante pour éviter les doublons, disons à la µseconde ou plus fin s’il le faut).

[MacFly58]

Merci pour vos contributions

C'est un plaisir de vous revoir MacFly !

Tout le plaisir est pour moi

Comme d'habitude, la contribution de fsmrel amène plus de questions que de réponses, mais c'est ça qui est bien^^

INSERT nombreux dans la table CLIENT en TP

C'est quoi TP ?

l’auto-incrémentation provoquait des phénomènes de contention

Je n'ai pas compris ce qu'est un "phénomène de contention".
Donc je n'ai pas compris en quoi l'auto-incrément peut poser problème.

Si on utilise un simple auto-incrément (type entier) pour la clé, alors on peut inférer que telle ligne a été créée avant telle autre : la ligne dont la clé héberge la valeur 1 a été créée a priori avant la ligne qui héberge la valeur 314159265.

Vous voulez dire que certains informaticiens feraient des requêtes en prenant par exemple en compte dans leur logique le fait qu'un numéro auto-incrémenté a été créé antérieurement à un autre ? Par exemple, pour éditer une liste chronologique, il trieraient sur la colonne clé auto-incrémentée ?

En fait, l’absence de signification devient effective quand on utilise des valeurs aléatoires, quand on hache les clés avec un algorithme ad-hoc.

Où peut-on voir un bon algorithme svp ?


Bon allé j'arrête la mitrailleuse à questions

A bientôt.

[fsmrel]

Bonjour,

Envoyé par MacFly58

C'est quoi TP ?

Je suis désolé, j’ai omis de préciser qu’il s’agit d’un jargon utilisé dans les années soixante-dix. "TP" ne veut pas dire Travaux Pratiques, mais TeleProcessing : aujourd’hui on dit plutôt télétraitement ou transactionnel, ce que l’on symbolise métaphoriquement par Joe Transaction dont les réactions sont immédiates, par opposition à Bill Batch et Jane Query qui par vocation ont des réactions beaucoup plus lentes.

Envoyé par MacFly58

Je n'ai pas compris ce qu'est un "phénomène de contention".
Donc je n'ai pas compris en quoi l'auto-incrément peut poser problème.

Il y a contention entre deux tâches T1 et T2 quand toutes deux veulent mettre à jour la même ressource R en même temps : si T1 est la 1re à demander la mise à jour de R, le système mettra T2 en attente de libération de R par T1 pour y avoir accès à son tour, c'est-à-dire après un COMMIT ou un ROLLBACK (termes SQL) déclenché par T1 (ou par le système pour le compte de T1).

Là où ça devient drôle c’est quand T1 détient la ressource R1 et cherche aussi à accéder à une ressource R2 laquelle comme hasard est détenue dans le même temps par T2 qui cherche bien entendu à mettre à jour aussi la ressource R1... Le système détecte alors une situation d’inter-blocage qu’il va devoir dénouer en tuant une des tâches, par exemple celle qui a consommé le moins de ressources (le choix est SGBD dépendant : en l’occurrence je me base sur le comportement qu’avait le système IMS d’IBM, tel que je le chahutais et l’enseignais dans les années soixante-dix). Pour la petite histoire, on dit que le système a détecté une étreinte fatale (verrou mortel, deadlock, ...)

Envoyé par MacFly58

Vous voulez dire que certains informaticiens feraient des requêtes en prenant par exemple en compte dans leur logique le fait qu'un numéro auto-incrémenté a été créé antérieurement à un autre ? Par exemple, pour éditer une liste chronologique, il trieraient sur la colonne clé auto-incrémentée ?

Comme disait Fernand Naudin, « Les c... ça ose tout »... Mais peut-on leur jeter la première pierre ?

Envoyé par MacFly58

Où peut-on voir un bon algorithme svp ?

Personnellement j’utilisais la routine DFSHDC40 d’IBM, mais peut-être qu’une fonction SQL fournie par le SGBD pourrait faire l’affaire (RAND par exemple, avec DB2, SQL Server, MySQL, ou RANDOM avec PostgreSQL, voire ORA_HASH avec Oracle, mais sous toute réserve car je n’ai rien essayé de tout cela). J’ai aussi beaucoup utilisé le hachage de l’heure à la résolution de la µseconde (voire plus fin) dans le monde mainframe IBM (z/OS), mais en assembleur, et je ne sais pas si je saurais aujourd’hui faire la même chose hors z/OS. Le forum DVP dédié aux algorithmes contient peut-être les meilleures routines de hachage, à fouiller.

Je sens que j'ai encore suscité plus d'interrogations que de réponses ^^

[MacFly58]

Bonjour,

Je sens que j'ai encore suscité plus d'interrogations que de réponses ^^

Ouf non, vous m'avez épargné^^

Bon allé une dernière petite question et on referme cette intéressante parenthèse.

Là où ça devient drôle c’est quand T1 détient la ressource R1 et cherche aussi à accéder à une ressource R2 laquelle comme hasard est détenue dans le même temps par T2 qui cherche bien entendu à mettre à jour aussi la ressource R1

Ce problème doit être rare non ? Il ne doit pouvoir survenir que si le SGBD utilise le verrouillage pessimiste, or les SGBD C/S fonctionnent généralement en verrouillage optimiste.

[fsmrel]

C'est une question à poser aux DBA responsables des SGBD en production, afin qu'ils analysent les logs avant qu'ils puissent vous répondre objectivement. Quand vous avez 2000 utilisateurs qui cartonnent aux heures de pointe, ça doit encore vraisemblablement arriver (j'ai connu ça du temps où je surveillais de près les bases de données en production, mais ça n’est plus de mon âge ^^).

[pachot]

Bonjour,

Pour moi il y a 4 types de clés:

1. l'objet est identifié entièrement par les objets qu'il référence (tables d'association, ou l'exemple de CinePhil).
-> la question ne se pose pas, on a déjà la clé. Connue dès le début du cycle de vie de l'objet. Immuable. Aucune raison d'en rajouter une. Sauf si elle devient très très grosse (concaténation de 5 ou 6 colonnes) et qu'elle est référencée par d'autres tables. Dans ce cas on va dans le cas 4.

2. L'objet est identifié par une valeur définie par un système externe, comme le code ISO d'une devise par exemple.
-> le type de clé est imposé par les spécifications.

3. C'est le système qui va générer l'identifiant de l'objet lors de sa création, cette clé devenant significative ensuite car exposée à l'extérieur du système (un numéro de commande par exemple)
-> son type est imposé par les spécifications.

4. Il n'y a pas d'identifiant immuable connu dès la création de l'objet. Si on en a besoin pour le référencer à l’intérieur du système uniquement (foreign keys), sans jamais l'exposer à l'extérieur, alors on peut en générer un à notre guise. Exemple: identifiant d'un appel dans un système de facturation télécom.

Dans ce cas, vu que la clé n'est pas significative, elle ne peut servir qu'à être comparée à une autre avec une égalité (=). Une comparaison (> ou <) ou toute autre fonction spécifique à un type donné (like,...) n'a pas de sens. On doit seulement pouvoir vérifier l'égalité et assurer l'unicité.

Du coup, le type de donnée (numérique, date, caractères) n'a pas d'influence sur la performances: ce sont des bits qui seront comparés, quel que soit le stockage. Seule la longueur (en octets) va influer sur quelques cycles de cpu en plus.

L'incrémentation d'un nombre est souvent utilisée car très pratique pour générer des numéros uniques. De plus, elle est très utile si on veut avoir un index bien tassé: l'ordre physique dans l'index va suivre l'ordre d'insertion.

Mais en cas de nombreux inserts concurrents ça peut provoquer une contention sur le dernier bloc d'index -> on peut alors utiliser n'importe quelle fonction qui distribue les valeurs tout en gardant l'unicité (comme les index REVERSE d'Oracle).

Et c'est aussi assez compact: un million de valeurs pourront probablement être stockées sur 4 octets en moyenne.
C'est aussi très efficace à générer car on peut garder en 'cache' toute une plage de valeurs pour éviter la contention.

Le principal inconvénient à mon avis: la tentation d'y voir quelque chose de significatif (genre trier sur cette clé en pensant que c'est lié à l'ordre d'arrivée, râler de voir des gaps de nombres non utilisés,...).
On peut le stocker dans un type de donnée binaire (RAW,BINARY,... suivant le SGBD) pour éviter cette tentation.

Autre inconvénient: si on veut merger deux systèmes en un, il va falloir tout renuméroter, ou ajouter une colonne à chaque clé.
On peut le prévoir en générant des numéros sur des plages différentes. Mais qui prévoit ça ?

Une autre solution est un GUID qui a l'avantage d'être universel: il restera une clé même s'il est exposé à l'extérieur du système (cas 3. ci-dessus) ou simplement si on veut merger deux systèmes en un seul. Mais il prend toujours 16 octets: même en générant un million de nombres incrémentés par seconde il faudra des siècles pour arriver à cette taille.

Donc pour moi si on doit générer une clé non significative, c'est presque toujours un nombre auto-incrémenté, éventuellement avec une fonction REVERSE (inversion des bits de gauche à droite) si on préfère les disperser.

L'horodatage me semble efficace également (clé de type date ou date et heure).

Sauf au moment du passage à l'heure d'hiver Et de toute façon, si on espère un jour faire plus d'un insert par seconde, il faudra bien incrémenter un numéro de séquence en plus. Sans parler du coût de l'appel système à la fonction gettime de l'OS...

On peut écarter le type VARCHAR qui n'est pas recommandé habituellement.
Une colonne de type CHAR pourrait faire l'affaire aussi je pense.

Je ne vois pas de grosse différence entre les deux, mais ça dépend du SGBD. La comparaison peut être très rapide (c'est du binaire qui est comparé de toute façon). Mais comment générer des valeurs uniques ? Par contre, pour une clé métier immuable (les cas 2. ou 3. ci-dessus) c'est très bien si ce n'est pas trop large (quelques octets). L'avantage de pouvoir merger 2 systèmes en un. D'éviter des jointures lorsqu'on recherche sur cette clé métier. Possibilité de partitionner sur cette clé métier, etc.


Cordialement,
Franck.