Clés dans la base de données

5.1 Présentation

Internet regorge de préceptes dogmatiques sur la manière dont les clés doivent être choisies et utilisées dans les bases de données relationnelles. Parfois, les litiges se transforment même en holivars : faut-il utiliser des clés naturelles ou artificielles ? Entiers à incrémentation automatique ou UUID ?

Après avoir lu soixante-quatre articles, parcouru des sections de cinq livres et posé des tonnes de questions sur IRC et StackOverflow, je (Joe "begriffs" Nelson, l'auteur de l'article original) semble avoir assemblé les pièces du puzzle et peut maintenant réconcilier les adversaires. De nombreux conflits clés découlent en fait d'une mauvaise compréhension du point de vue de quelqu'un d'autre.

Démontons le problème et reconstituons-le à la fin. D'abord, posons la question - qu'est-ce qu'une "clé" ?

Oublions un instant les clés primaires, nous nous intéressons à une idée plus générale. Une clé est une colonne (colonne) ou des colonnes qui n'ont pas de valeurs en double dans les lignes . De plus, les colonnes doivent être irréductiblement uniques, c'est-à-dire qu'aucun sous-ensemble des colonnes n'a cette unicité.

Mais avant, un peu de théorie :

clé primaire

Clé primairedirectement utilisé pour identifier les lignes d'une table. Il doit respecter les restrictions suivantes :

• La clé primaire doit être unique à tout moment.
• Il doit toujours être présent dans la table et avoir une valeur.
• Il ne devrait pas changer sa valeur fréquemment. Idéalement, cela ne devrait pas changer la valeur du tout .

En règle générale, une clé primaire représente une seule colonne d'une table, mais il peut également s'agir d'une clé composite composée de plusieurs colonnes.

Clé composée

Clé personnalisée- une combinaison d'attributs (colonnes) qui identifient de manière unique chaque ligne du tableau. Il peut s'agir de toutes les colonnes, de plusieurs et d'une. Dans ce cas, les lignes qui contiennent les valeurs de ces attributs ne doivent pas être répétées.

Clé potentielle

clé candidate- représente la clé composée minimale de la relation (table), c'est-à-dire un ensemble d'attributs remplissant un certain nombre de conditions :

• Irréductibilité : Il ne peut pas être réduit, il contient le minimum d'attributs possible.
• Unicité : Il doit avoir des valeurs uniques quel que soit le changement de ligne.
• Présence d'une valeur : Elle ne doit pas avoir une valeur nulle, c'est-à-dire qu'elle doit avoir une valeur.

5.2 Le cas curieux des clés primaires

Ce que nous venons d'appeler « clés » dans la section précédente sont communément appelées « clés candidates ». Le terme "candidat" implique que toutes ces clés se disputent le rôle honorifique de "clé primaire" (clé primaire), et que les autres se voient attribuer des "clés alternatives" (clés alternatives).

Il a fallu un certain temps aux implémentations SQL pour surmonter l'inadéquation entre les clés et le modèle relationnel, et les premières bases de données étaient orientées vers le concept de bas niveau d'une clé primaire. Les clés primaires de ces bases de données étaient nécessaires pour identifier l'emplacement physique d'une ligne sur un support de stockage séquentiel. Voici comment Joe Celko l'explique :

Le terme "clé" signifiait une clé de tri de fichier, qui était nécessaire pour effectuer toutes les opérations de traitement sur un système de fichiers séquentiel. Un jeu de cartes perforées a été lu en une et une seule commande ; il était impossible de revenir en arrière. Les premiers lecteurs de bande imitaient le même comportement et ne permettaient pas l'accès bidirectionnel. C'est-à-dire que le serveur Sybase SQL d'origine devait « rembobiner » la table jusqu'au début pour lire la ligne précédente.

Dans le SQL moderne, vous n'avez pas besoin de vous concentrer sur la représentation physique des informations, les relations des modèles de tables et l'ordre interne des lignes n'est pas du tout important. Cependant, même maintenant, SQL Server crée par défaut un index clusterisé pour les clés primaires et, selon l'ancienne tradition, organise physiquement l'ordre des lignes.

Dans la plupart des bases de données, les clés primaires appartiennent au passé et ne fournissent guère plus qu'un reflet ou un emplacement physique. Par exemple, dans une table PostgreSQL, la déclaration d'une clé primaire applique automatiquement une contrainte NOT NULLet définit une clé étrangère par défaut. De plus, les clés primaires sont les colonnes préférées de l'opérateur JOIN.

La clé primaire ne remplace pas la possibilité de déclarer d'autres clés. Dans le même temps, si aucune clé n'est attribuée comme clé principale, la table fonctionnera toujours correctement. La foudre, dans tous les cas, ne vous frappera pas.

5.3 Recherche de clés naturelles

Les clés évoquées ci-dessus sont dites "naturelles" car ce sont des propriétés de l'objet modélisé qui sont intéressantes en elles-mêmes, même si personne ne veut en faire une clé.

La première chose à retenir lors de l'examen d'une table pour les clés naturelles possibles est d'essayer de ne pas être trop intelligent. L'utilisateur sqlvogel sur StackExchange donne les conseils suivants :

Certaines personnes ont du mal à choisir une clé "naturelle" car elles proposent des situations hypothétiques dans lesquelles une clé particulière peut ne pas être unique. Ils ne comprennent pas le sens même de la tâche. La signification de la clé est de définir la règle selon laquelle les attributs à un moment donné doivent être et seront toujours uniques dans une table particulière. Le tableau contient des données dans un contexte spécifique et bien compris (dans le "domaine du sujet" ou "le domaine du discours"), et la seule signification est l'application de la restriction dans ce domaine particulier.

La pratique montre qu'il est nécessaire d'introduire une contrainte de clé lorsque la colonne est unique avec les valeurs disponibles et le restera dans les scénarios probables. Et si nécessaire, la restriction peut être supprimée (si cela vous dérange, nous parlerons ci-dessous de la stabilité des clés.)

Par exemple, une base de données des membres d'un club de loisirs peut avoir un caractère unique dans deux colonnes - first_name, last_name. Avec une petite quantité de données, les doublons sont peu probables, et avant qu'un véritable conflit ne survienne, il est tout à fait raisonnable d'utiliser une telle clé.

À mesure que la base de données se développe et que le volume d'informations augmente, le choix d'une clé naturelle peut devenir plus difficile. Les données que nous stockons sont une simplification de la réalité externe et ne contiennent pas certains aspects qui distinguent les objets dans le monde, tels que leurs coordonnées qui changent avec le temps. Si un objet n'a pas de code, comment différencier deux canettes de boisson ou deux boîtes de flocons d'avoine de leur disposition spatiale ou de légères différences de poids ou d'emballage ?

C'est pourquoi les organismes de normalisation créent et appliquent des marques distinctives aux produits. Les véhicules sont estampillés d' un numéro d'identification de véhicule (VIN) , les livres sont imprimés avec des ISBN et les emballages alimentaires ont des UPC . Vous pouvez objecter que ces chiffres ne semblent pas naturels. Alors pourquoi est-ce que je les appelle clés naturelles ?

Le caractère naturel ou artificiel des propriétés uniques dans une base de données est relatif au monde extérieur. Une clé qui était artificielle lorsqu'elle a été créée dans un organisme de normalisation ou une agence gouvernementale nous devient naturelle, car elle devient une norme dans le monde entier et/ou est imprimée sur des objets.

Il existe de nombreuses normes industrielles, publiques et internationales pour une variété de sujets, y compris les devises, les langues, les instruments financiers, les produits chimiques et les diagnostics médicaux. Voici quelques-unes des valeurs souvent utilisées comme clés naturelles :

• Codes pays ISO 3166
• Codes de langue ISO 639
• Codes devises selon ISO 4217
• Symboles boursiers ISIN
• CUP/EAN, NIV, GTIN, ISBN
• noms de connexion
• adresses mail
• numéros de chambre
• adresse mac réseau
• latitude, longitude pour les points à la surface de la Terre

Je recommande de déclarer les clés chaque fois que cela est possible et raisonnable, peut-être même plusieurs clés par table. Mais rappelez-vous que tout ce qui précède peut avoir des exceptions.

• Tout le monde n'a pas d'adresse e-mail, bien que cela puisse être acceptable dans certaines conditions de base de données. De plus, les gens changent leur adresse e-mail de temps en temps. (Plus d'informations sur la stabilité des clés plus tard.)
• Les symboles boursiers ISIN changent de temps en temps, par exemple, les symboles GOOG et GOOGL ne décrivent pas avec précision la réorganisation de l'entreprise de Google à Alphabet. Parfois, une confusion peut survenir, comme avec TWTR et TWTRQ, certains investisseurs ont acheté ce dernier par erreur lors de l'introduction en bourse de Twitter.
• Les numéros de sécurité sociale ne sont utilisés que par les citoyens américains, ont des restrictions de confidentialité et sont réutilisés après le décès. De plus, après le vol de documents, les gens peuvent obtenir de nouveaux numéros. Enfin, le même numéro peut identifier à la fois une personne et un identifiant fiscal.
• Les codes postaux sont un mauvais choix pour les villes. Certaines villes ont un index commun, ou vice versa, il y a plusieurs index dans une même ville.

5.4 Clés artificielles

Étant donné que la clé est une colonne avec des valeurs uniques dans chaque ligne, une façon de la créer consiste à tricher - vous pouvez écrire des valeurs uniques fictives dans chaque ligne. Ce sont des clés artificielles : code inventé utilisé pour faire référence à des données ou des objets.

Il est très important que le code soit généré à partir de la base de données elle-même et qu'il soit inconnu de tous sauf des utilisateurs de la base de données. C'est ce qui distingue les clés artificielles des clés naturelles standardisées.

Alors que les clés naturelles ont l'avantage de protéger contre les lignes en double ou incohérentes dans une table, les clés artificielles sont utiles car elles permettent aux humains ou à d'autres systèmes de se référer plus facilement à la ligne, et elles accélèrent les recherches et les jointures car elles n'utilisent pas comparaisons de chaînes (ou multi-colonnes).

Substituts

Les clés artificielles sont utilisées comme ancres - peu importe la façon dont les règles et les colonnes changent, une ligne peut toujours être identifiée de la même manière. La clé artificielle utilisée à cet effet est appelée "clé de substitution" et nécessite une attention particulière. Nous examinerons les substituts ci-dessous.

Les clés artificielles non substitutives sont utiles pour référencer une ligne depuis l'extérieur de la base de données. Une clé artificielle identifie brièvement une donnée ou un objet : elle peut être spécifiée sous forme d'URL, jointe à une facture, dictée par téléphone, obtenue auprès d'une banque ou imprimée sur une plaque d'immatriculation. (La plaque d'immatriculation d'une voiture est une clé naturelle pour nous, mais conçue par le gouvernement comme une clé artificielle.)

Les clés synthétiques doivent être choisies en tenant compte des moyens de transmission possibles afin de minimiser les fautes de frappe et les erreurs. Il convient de noter que la clé peut être parlée, lue imprimée, envoyée par SMS, lue manuscrite, tapée au clavier et intégrée dans une URL. De plus, certaines clés artificielles, telles que les numéros de carte de crédit, contiennent une somme de contrôle afin que si certaines erreurs se produisent, elles puissent au moins être reconnues.

Exemples:

• Pour les plaques d'immatriculation américaines, il existe des règles concernant l'utilisation de caractères ambigus, tels que O et 0.
• Les hôpitaux et les pharmacies doivent être particulièrement prudents, compte tenu de l'écriture manuscrite des médecins.
• Envoyez-vous un code de confirmation par SMS ? N'allez pas au-delà du jeu de caractères GSM 03.38.
• Contrairement à Base64, qui encode des données d'octets arbitraires, Base32 utilise un jeu de caractères limité qui est pratique pour les humains à utiliser et à gérer sur des systèmes informatiques plus anciens.
• Les proquints sont des identifiants lisibles, inscriptibles et prononçables. Ce sont des QUINT-uplets PRO-nommables de consonnes et de voyelles comprises sans ambiguïté.

Gardez à l'esprit que dès que vous présenterez votre clé artificielle au monde, les gens commenceront étrangement à lui accorder une attention particulière. Il suffit de regarder les plaques d'immatriculation "voleurs" ou le système de création d'identifiants prononçables, devenu le fameux générateur automatisé de malédictions.

Même si on se limite aux touches numériques, il y a des tabous comme le treizième étage. Alors que les proquints ont une densité d'informations plus élevée par syllabe parlée, les nombres conviennent également à bien des égards : dans les URL, les claviers à broches et les notes manuscrites, tant que le destinataire sait que la clé n'est que des chiffres.

Cependant, veuillez noter que vous ne devez pas utiliser l'ordre séquentiel dans les clés numériques publiques, car cela vous permet de fouiller dans les ressources (/videos/1.mpeg, /videos/2.mpeg, etc.) et également de divulguer des informations sur le numéro données. Superposez un filet de Feistel sur une séquence de nombres et préservez l'unicité tout en masquant l'ordre des nombres.

Le seul argument contre la déclaration de clés supplémentaires est que chaque nouvelle clé apporte avec elle un autre index unique et augmente le coût d'écriture dans la table. Bien sûr, cela dépend de l'importance que vous accordez à l'exactitude des données, mais, très probablement, les clés doivent toujours être déclarées.

Il convient également de déclarer plusieurs clés artificielles, le cas échéant. Par exemple, une organisation a des candidats (Candidats) et des employés (Employés). Chaque employé a déjà été candidat et fait référence aux candidats par son propre identifiant, qui devrait également être la clé de l'employé. Autre exemple, vous pouvez définir l'identifiant de l'employé et le nom de connexion comme deux clés dans Employés.

5.5 Clés de substitution

Comme déjà mentionné, un type important de clé artificielle est appelé "clé de substitution". Il n'a pas besoin d'être concis et passable comme les autres clés artificielles, mais est utilisé comme une étiquette interne qui identifie toujours la chaîne. Il est utilisé dans SQL, mais l'application n'y accède pas explicitement.

Si vous êtes familier avec les colonnes système de PostgreSQL, vous pouvez considérer les substituts presque comme un paramètre d'implémentation de base de données (comme ctid), qui, cependant, ne change jamais. La valeur de substitution est sélectionnée une fois par ligne et n'est jamais modifiée par la suite.

Les clés de substitution sont excellentes en tant que clés étrangères, et les contraintes en cascade doivent être spécifiées ON UPDATE RESTRICTpour correspondre à l'immuabilité du substitut.

D'autre part, les clés étrangères des clés partagées publiquement doivent être marquées par ON UPDATE CASCADE, pour offrir une flexibilité maximale. Une mise à jour en cascade s'exécute au même niveau d'isolement que la transaction environnante, donc ne vous inquiétez pas des problèmes de concurrence - la base de données ira bien si vous choisissez un niveau d'isolement strict.

Ne rendez pas les clés de substitution "naturelles". Une fois que vous montrez la valeur de la clé de substitution aux utilisateurs finaux, ou pire, que vous les laissez travailler avec cette valeur (en particulier via une recherche), vous donnez effectivement une valeur à la clé. Ensuite, la clé affichée de votre base de données peut devenir une clé naturelle dans la base de données de quelqu'un d'autre.

Forcer les systèmes externes à utiliser d'autres clés artificielles spécialement conçues pour la transmission nous permet de modifier ces clés au besoin pour répondre à l'évolution des besoins, tout en maintenant l'intégrité référentielle interne avec des substituts.

Auto-incrémentation INT/BIGINT

L'utilisation la plus courante des clés de substitution est la colonne "bigserial" à incrémentation automatique , également appelée IDENTITY . (En fait, PostgreSQL 10 prend désormais en charge la construction IDENTITY, tout comme Oracle, voir CREATE TABLE.)

Cependant, je pense qu'un entier à incrémentation automatique est un mauvais choix pour les clés de substitution. Cette opinion est impopulaire, alors laissez-moi vous expliquer.

Inconvénients des clés de série :

• Si toutes les séquences commencent à 1 et augmentent progressivement, les lignes de différentes tables auront les mêmes valeurs de clé. Cette option n'est pas idéale, il est tout de même préférable d'utiliser des ensembles de clés disjoints dans les tables, afin que, par exemple, les requêtes ne puissent pas accidentellement confondre les constantes JOINet renvoyer des résultats inattendus. (Alternativement, pour s'assurer qu'il n'y a pas d'intersections, on pourrait construire chaque séquence à partir de multiples de nombres premiers différents, mais ce serait plutôt laborieux.)
• L'appel nextval() à générer une séquence dans le SQL distribué d'aujourd'hui entraîne une mauvaise mise à l'échelle de l'ensemble du système.
• La consommation de données d'une base de données qui utilisait également des clés séquentielles entraînera des conflits car les valeurs séquentielles ne seront pas uniques sur tous les systèmes.
• D'un point de vue philosophique, l'augmentation séquentielle des nombres est associée aux anciens systèmes dans lesquels l'ordre des lignes était implicite. Si vous souhaitez maintenant ordonner les lignes, faites-le explicitement avec une colonne d'horodatage ou quelque chose qui a du sens dans vos données. Sinon, la première forme normale est violée.
• Raison faible, mais ces identifiants courts sont tentants à dire à quelqu'un.

UUID

Voyons une autre option : utiliser de grands entiers (128 bits) générés selon un modèle aléatoire. Les algorithmes de génération de ces identifiants universels uniques (UUID) ont une probabilité extrêmement faible de choisir deux fois la même valeur, même lorsqu'ils s'exécutent sur deux processeurs différents en même temps.

Dans ce cas, les UUID semblent être un choix naturel à utiliser comme clés de substitution, n'est-ce pas ? Si vous souhaitez étiqueter les lignes de manière unique, rien ne vaut une étiquette unique !

Alors pourquoi tout le monde ne les utilise-t-il pas dans PostgreSQL ? Il y a plusieurs raisons artificielles à cela et une raison logique qui peut être contournée, et je présenterai des repères pour illustrer mon propos.

Tout d'abord, je vais parler des raisons farfelues. Certaines personnes pensent que les UUID sont des chaînes car ils sont écrits en notation hexadécimale traditionnelle avec un tiret : 5bd68e64-ff52-4f54-ace4-3cd9161c8b7f. En effet, certaines bases de données n'ont pas de type uuid compact (128 bits), mais PostgreSQL en a une et a une taille de deux bigint, c'est-à-dire que par rapport à la quantité d'autres informations dans la base de données, la surcharge est négligeable.

Les UUID sont aussi injustement accusés d'être encombrants, mais qui va les prononcer, les taper ou les lire ? Nous avons dit qu'il était logique d'afficher des clés artificielles, mais personne (par définition) ne devrait voir l'UUID de substitution. Il est possible que l'UUID soit traité par un développeur exécutant des commandes SQL dans psql pour déboguer le système, mais c'est à peu près tout. Et le développeur peut également faire référence à des chaînes en utilisant des clés plus pratiques, si elles sont fournies.

Le vrai problème avec les UUID est que les valeurs hautement aléatoires entraînent une amplification des écritures en raison des écritures pleine page dans le journal d'écriture anticipée (WAL) . Cependant, la dégradation des performances dépend en réalité de l'algorithme de génération de l'UUID.

Mesurons l'amplification d'écriture . En vérité, le problème se situe dans les anciens systèmes de fichiers. Lorsque PostgreSQL écrit sur le disque, il modifie la "page" sur le disque. Si vous coupez l'alimentation de l'ordinateur, la plupart des systèmes de fichiers signaleront toujours une écriture réussie avant que les données ne soient stockées en toute sécurité sur le disque. Si PostgreSQL perçoit naïvement une telle action comme terminée, la base de données sera corrompue lors du prochain démarrage du système.

Étant donné que PostgreSQL ne peut pas faire confiance à la plupart des systèmes d'exploitation/systèmes de fichiers/configurations de disque pour assurer la continuité, la base de données enregistre l'état complet de la page de disque modifiée dans un journal à écriture anticipée qui peut être utilisé pour récupérer d'un éventuel plantage. L'indexation de valeurs hautement aléatoires comme les UUID implique généralement un tas de pages de disque différentes et entraîne l'écriture de la taille de page complète (généralement 4 ou 8 Ko) dans le WAL pour chaque nouvelle entrée. Il s'agit de l'écriture dite pleine page (écriture pleine page, FPW).

Certains algorithmes de génération d'UUID (tels que "snowflake" de Twitter ou uuid_generate_v1() dans l'extension uuid-ossp de PostgreSQL) génèrent des valeurs croissantes de façon monotone sur chaque machine. Cette approche consolide les écritures dans moins de pages de disque et réduit le FPW.

5.6 Conclusions et recommandations

Maintenant que nous avons vu les différents types de clés et leurs utilisations, je souhaite lister mes recommandations pour les utiliser dans vos bases de données.

Pour chaque tableau :

• Définissez et déclarez toutes les clés naturelles.
• Créez une clé de substitution <table_name>_idde type UUID avec une valeur par défaut de uuid_generate_v1(). Vous pouvez même le marquer comme clé primaire. Si vous ajoutez le nom de la table à cet identifiant, cela simplifiera JOIN, c'est-à-dire recevoir JOIN foo USING (bar_id)au lieu de JOIN foo ON (foo.bar_id = bar.id). Ne transmettez pas cette clé aux clients et ne l'exposez pas du tout en dehors de la base de données.
• Pour les tables intermédiaires qui passent par JOIN, déclarez toutes les colonnes de clé étrangère en tant que clé primaire composite unique.
• Ajoutez éventuellement une clé artificielle pouvant être utilisée dans l'URL ou d'autres indications de référence de chaîne. Utilisez une grille Feistel ou pg_hashids pour masquer les entiers à incrémentation automatique.
• Spécifiez une contrainte en cascade ON UPDATE RESTRICTà l'aide d'UUID de substitution comme clés étrangères et pour les clés étrangères artificielles ON UPDATE CASCADE. Choisissez des clés naturelles en fonction de votre propre logique.

Cette approche assure la stabilité des clés internes tout en autorisant et même en protégeant les clés naturelles. De plus, les clés artificielles visibles ne s'attachent à rien. Après avoir tout compris correctement, vous ne pouvez pas vous accrocher uniquement aux «clés primaires» et utiliser toutes les possibilités d'utilisation des clés.