Les clés primaires composites
S’il y a bien quelque chose que peu de monde connait en SQL c’est bien les clés composites. Moi même je ne savais pas ce que c’était avant d’en avoir besoin. Et pourtant c’est très utile et je vous conseille de vous y intéresser ! En voici une petite explication.
Une clé composite est une clé composée de plusieurs champs. (Tout simplement)
Une clé primaire composite est une clé primaire composée de plusieurs champs. (Une clé primaire n’a jamais été cantonnée à un seul champ, tout comme les clés uniques et les index)
Pour que ça soit plus parlant prenons un exemple :
On souhaite stocker des documents disponibles en plusieurs langues. Simplement on pourrait faire :
CREATE TABLE documents (
id TINYINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
lang CHAR(2) NOT NULL,
title VARCHAR(255) NOT NULL,
author VARCHAR(255) NOT NULL
)Ainsi, pour insérer des documents :
INSERT INTO documents (lang, title, author) VALUES ("fr", "Rapport", "Nicolas Le Gall");
INSERT INTO documents (lang, title, author) VALUES ("en", "Report", "Jens Meiert");L’inconvénient c’est que l’on obtient 2 id différents, et qu’il est donc quasiment impossible d’identifier un lien entre ces deux documents. Pourtant il s’agit du même document, mis à part qu’ils ne sont pas dans la même langue.
Une solution est de gérer l’id manuellement (en enlevant l’option AUTO_INCREMENT), mais il est impossible d’avoir 2 id identiques (puisqu’il s’agit d’une clé primaire, ce champ est par définition unique).
Passer le champ en unique ne changera rien, et le passer en index résultera à une perte de cardinalité (ce qui n’est pas bon, ou très déconseillé, pour un id).
Créer une table pour chaque langue n’est pas non plus la bonne solution, ça surchargerai le nombre de tables, rajouterai des jointures et ce n’est pas très maintenable (imaginez que vous trouvez un contributeur japonais, vous n’avez plus qu’à créer une nouvelle table…)
La solution réside dans une clé composite (vous ne vous y attendiez pas, hein ;-) ?).
Si nous créons notre table comme cela :
CREATE TABLE documents (
id TINYINT UNSIGNED NOT NULL,
lang CHAR(2) NOT NULL,
title VARCHAR(255) NOT NULL,
author VARCHAR(255) NOT NULL,
PRIMARY KEY (id, lang)
)Cette fois pour insérer les données on doit préciser l’id :
INSERT INTO documents (id, lang, title, author) VALUES ("1", "fr", "Rapport", "Nicolas Le Gall");
INSERT INTO documents (id, lang, title, author) VALUES ("1", "en", "Report", "Jens Meiert");
INSERT INTO documents (id, lang, title, author) VALUES ("1", "de", "Bericht", "Jens Meiert");
INSERT INTO documents (id, lang, title, author) VALUES ("2", "fr", "Tutoriel", "Eric Daspet");
INSERT INTO documents (id, lang, title, author) VALUES ("2", "en", "Tutorial", "Christian Heilman");
INSERT INTO documents (id, lang, title, author) VALUES ("3", "fr", "La pierre et le sabre", "Karl Dubost");
INSERT INTO documents (id, lang, title, author) VALUES ("3", "jp", "宮本武蔵", "Yoshikawa Eiji");
(oui, je me suis lâché)Maintenant on peut parfaitement sélectionner un document :
SELECT * FROM documents WHERE id = 1Et on obtient le document et toutes ses traductions. En examinant d’un peu plus près (avec un EXPLAIN) on peut voir que le SGBD (MySQL dans mon cas) utilise l’index de la table, donc ne la parcourt pas (ainsi sur une très grande table vous avez de très très bonne performances).
Vous me direz que ça ne change pas d’avant, mis à part le fait que l’on peut avoir des id identiques ? Et bien essayons d’insérer une langue qui existe pour le document 1 :
INSERT INTO documents (id, lang, title, author) VALUES ("1", "de", "Bericht", "Jens Meiert");Le SGBD va vous spécifier que la clé « 1-de » existe déjà. Nous venons donc de modifier le comportement de notre clé primaire (qui était « id » auparavant, maintenant la clé est le couple « id-lang ») pour y ajouter une contrainte supplémentaire.
Il y a une étrangeté tout de même ; si vous sélectionnez une langue :
SELECT * FROM documents WHERE lang = "fr"Vous obtenez bien vos documents, mais EXPLAIN nous indique que le SGBD n’utilise pas l’index. Si vous savez pourquoi merci de m’éclairer 
.
Si vous avez une table vraiment conséquente vous pouvez quand même rajouter le champ lang en index :
ALTER TABLE documents ADD INDEX (lang)Si vous sélectionnez l’id ET la langue vous n’aurez pas ce « problème ».
Astuce :
Voici deux requêtes vous permettant de trouver le prochain id disponible (puisqu’on ne peut plus utiliser d’AUTO_INCREMENT sur notre clé primaire) :
SELECT id AS last FROM documents ORDER BY id DESC LIMIT 1Celle ci vous permet de récupérer le dernier id attribué.
SELECT MAX(id)+1 AS next FROM documentsEt cette dernière vous donne directement l’id à utiliser (mais a l’inconvénient de ne pas utiliser l’index).
Commentaires
Ca fait du bien de te lire !
Bon par contre tu me déçois un peu sur la technique :
1) Les clefs primaires multi champs sont vitales dans tout projet, elles sont la clef (c'est le cas de le dire) de la cohésion des données (il sera vraiment impossible d'insérer un autre document 1 en fr, j'en connais certains qui utilisent un select avant pour vérifier...). N'en as-tu jamais eu besoin, vraiment ?
2) RTFM : Il t'indiquera que l'utilisation d'un index sur deux champs (ou comme ici d'une clef primaire sur 2 champs - c'est pareil) a des limites d'usage dans les requêtes. Ainsi l'index fonctionne dans l'ordre d'indexation des champs.
Pour reprendre ton exemple : chercher un id + la lang sera rapide. Cherche juste l'id sera rapide (premier champ indexé). Chercher la lang sera lent. Si tu indexes A +B + C, la recherche est rapide sur A, A+B, A+B+C mais lente sur B, C, B+C, A+C.
Rajouter un index sur la langue est un moyen de contourner le problème effectivement mais à utiliser avec parcimonie. Parfois des tests de performance mettront en évidence qu'il vaut mieux rapatrier les X lignes correspondant à l'ID puis ensuite filtrer la langue que d'utiliser le SGBD pour cela.
3) Pour la récupération de l'ID, es-tu sûr de ce que tu avances pour "SELECT MAX(id)+1 AS next FROM documents" ? A mon avis c'est performant et cela utilise l'index, mais j'avoue que tu m'as mis le doute.
Merci pour ton commentaire, ça me fait plaisir de lire quelque chose de constructif, j'avais un peu perdu l'habitude ici .
1) Non, je n'en avais jamais utilisé, mais je développe très peu :P !
2) Merci pour cette précision :] ! C'est un peu ce que je pensais. Je suis complètement d'accord avec toi en ce qui concerne le rapatriement des X lignes. Par contre de quel manuel parle-tu, j'évite de me focaliser sur celui de MySQL n'étant pas un manuel de SQL pur et dur (je ne sais pas si je suis clair, la fatigue tout ça...).
3) Pour écrire ce billet j'ai fait la table, rajouté les données et fait des EXPLAIN sur toute les requêtes SELECT. Sur celle avec MAX MySQL parcourt tous les enregistrements. C'est moins performant, mais c'est une question de microsecondes. Même sur une table avec des milliers d'enregistrements le SGBD répondra vite. Si vraiment on est pointilleux et qu'on préfère les meilleures performances la première sera plus efficace, mais demandera légèrement plus de code.
Par contre, je doute que beaucoup de codeurs connaissent les clés composites.
> Par contre, je doute que beaucoup de codeurs connaissent les clés composites.
Moi je connaissais, mais je ne savais pas que ça s'appelait comme ça. On l'a vu en src lorsqu'ils nous ont gavé de MCD,MLD puis (enfin) de SQL...
Pour récuperer l'id et être sur d'avoir le bon, tu ne peux pas faire un premier INSERT et choper l'id avec mysql_insert_id() ? J'dis ptet de la merde, mais il est 2h30 donc j'ai une dérogation
Dans un projet récent j'avais choisi les "clés composites" : une grosse table, une primaire sur serveur+date. Finalement c'était une mauvaise idée: pas scalable du tout, j'étais obligé de vider régulièrement la table (et archiver les vieilles données, donc les rendre quasiment inexploitables).
J'ai refait tout from scratch, et finalement j'ai une table par serveur et par an (c'est le temps qu'il faut pour que la table atteigne sa "taille critique", et ça prend N fois moins de temps, avec N = nombre de serveurs). L'inconvénient c'est qu'il faut créer les tables identiques, no problemo: le script fait ça tout seul comme un grand.
Maintenant ça trace, j'ai plus besoin de trop de WHERE, ni de trop de GROUP BY, juste un seul index 
Wordpress fait ça pour sa plateforme Mu, parce qu'il s'avère que MySQL gère mieux de nombreuses petites tables qu'une seule énormissime.
PS: les primaires composites doivent être connues par tous les devs qui ont déjà créé une relation n+n, non ?
@Palleas :
> Moi je connaissais, mais je ne savais pas que ça s'appelait comme ça. On l'a vu en src lorsqu'ils nous ont gavé de MCD,MLD puis (enfin) de SQL...
Tu as eu la chance d'avoir des cours de SQL
> Pour récuperer l'id et être sur d'avoir le bon, tu ne peux pas faire un premier INSERT et choper l'id avec mysql_insert_id() ?
Dans ma table j'ai supprimé l'option AUTO_INCREMENT, donc on est obligé de spécifier un id !
Tu peux utiliser cette option, mais il faudra penser à spécifier l'id lorsque l'on voudra rajouter une traduction. Par contre utiliser mysql_insert_id() te donnera l'id du dernier enregistrement (que tu viens d'effectuer), donc ça sera utilisable uniquement si tu fais plusieurs enregistrements d'un coup (et que, bien sûr, il s'agit du même document).
@Julien Tartarin :
Je n'ai pas dit que c'est à utiliser tout le temps (si ?), évidemment sur certain projets il faut penser sa base de données différemment (heureusement, on s'ennuirai sinon)
Je ne compte plus le nombre de dev que j'ai croisé et qui ne savent même pas ce que c'est qu'un index... D'ailleurs la plupart utilisent uniquement des champs INT et VARCHAR... Alors les clé primaires composites :D !
Comme je tiens à ce que mon nom ne soit pas écrasé dans cette db, il est important que j'en corrige les problèmes
En particulier, repérer le futur identifiant libre avec un SELECT MAX(id)+1 ou un SELECT id ... ORDER BY id DESC LIMIT 1 est une *très* mauvaise idée. Tu vas avoir des problèmes dès que tu auras un minimum de concurrence d'accès. Deux insertions peuvent très bien récupérer le même identifiant par erreur. Là tu joues à la roulette russe avec tes données.
Tu *dois* laisser ta colonne "id" en auto-increment, même si elle est dans une clé primaire composite. Les auto-incréments (ou les séquences dans d'autres SGBDR) sont fait justement pour gérer ces cas là. Ca ne t'empêchera pas de spécifier manuellement cet identifiant quand tu veux créer une traduction d'un document déjà existant.
Merci beaucoup Eric pour ce commentaire, et ça me fait très plaisir de te voir trainer ici
N'étant absolument pas un spécialiste, je vais te faire confiance (je ne sais surtout pas quoi ajouter ) !
>Par contre, je doute que beaucoup de codeurs connaissent les clés composites.
Pourtant c'est la base même de tout modèle de donné complexe. Après, il est probable que nombre de codeurs n'aient jamais été formés et donc manque de ce genre de connaissances.
Je connaissais aussi les clés primaires composites. C'est d'ailleurs très pratique.
Comme le dit Eric, avec ta solution on s'expose très vite avec des accès concurrents lorsque la base de données est fortement sollicitée. Un forum de discussion hyper actif par exemple. Pour éviter ce genre de problème, j'utilise toujours un identifiant auto incrémenté comme clé primaire de ma table mais je crée en plus une clé composite unique. Par exemple, pour une table Article et une table Article_Langue
Article : id, date_creation, statut
Article_Langue : id, article_id, langue, titre, corps, auteur_id
Je place les champs article_id et langue comme clé composite unique. Comme ça je m'assure que ce couple restera unique dans ma base de données. De plus, cela permet une suppression plus aisée. Admettons que nous voulions supprimer une traduction de l'article parmi plusieurs (fr, en, de, it). Je souhaite supprimer la traduction 'FR' dont l'identifiant primaire vaut 32. C'est plus simple de faire : delete_trad.php?id=32 que delete_trad.php?article_id=15&langue=fr
++
Ralalala, je savais bien que fallait que je me mettre à php cet été :P
Pour ma part je pense que le recours aux clées primaires composites est nécessaire quand on n'a pas fait le schéma correct de sa base de données :
au lieu d'une seule table, tu devrais avoir 2 tables :
une table ouvrage ( nom d'auteur, date de publication, etc ) chaque ouvrage identifié par une id unique auto-incrémenté
une table "version" ( langue, traducteur, etc ) reliée à la table ouvrage par la clé étrangère id_ouvrage
et voilà exit les constructions bizantines ...
Amicalement
Ce que dit Eric est très précisément ce que fait spip un peu partout. Voir par exemple les tables liées au versionnage et aux traductions.
(cela dit je ne suis pas développeur et je ne connaissais même pas la notion de clés composites : moi je fais tout en repassant des requêtes compliquées et lentes - ou plutôt je faisais, dans une autre vie).
super cet article
Merci pour cet article !!
bonnne contuniation frére un beau travail
Pourquoi ? Certainement parce ils stockent les index du genre
id;langue
exemple :
7;fr
Et que pour utiliser un index, il doit savoir par où commencer, donc par la première lettre, puis la deuxième, c'est comme chercher le "lo" de "stylo" dans un dico ...
Un jour, on m'a sorti qu'il était inutile de faire une clé primaire à part dans une table de jointure : on peut créer une clé primaire composite avec les deux clés étrangères. Bon, je trouve un peu chiant d'avoir pour les effacer par exemple : WHERE x = y AND z = a
Mais surtout, et je n'ai pas la réponse : comment représenter la table sur une clé étrangère d'une autre table ? Ah ...
Laissez le vôtre !
Les commentaires pour ce billet sont fermés.
Je pense que c'est même mieux dans une architecture REST, non ?
(Je ne suis pas spécialiste)