Retour au Blog

Prendre INT, UUI ou ULID pour un index de base de Données ?

Prendre INT, UUI ou ULID pour un index de base de Données ?

Introduction

Il existe plusieurs façons de définir les ID ou index incrémentaux dans les bases de données.

La méthode traditionnelle utilise id INT et est incrémentée par le SGBD, mais il existe d'autres moyens, avec leurs avantages et leurs inconvénients. Cela va dépendre des besoins.

Définition

Le type de données le plus courant est pour id est INT. Dans votre gestionnaire de base, vous pouvez le définir comme

CREATE TABLE table_name(id SERIAL);

Il existe trois pseudo-types en série.

  • PETIT SÉRIE: 2 octets (1 à 32 767)
  • EN SÉRIE: 4 octets (1 à 2 147 483 647)
  • BIGSÉRIE: 8 octets (1 à 922,337,2036,854,775,807)

UUID signifie Universal Unique Identifier défini par la RFC 4122 et d'autres normes connexes. Les UUID sont des identifiants de 16 octets / 32 chiffres hexadécimaux (avec 4 s pour la séparation) générés par un algorithme qui le rend unique dans l'univers connu en utilisant le même algorithme. Certains systèmes font référence à l'UUID en tant que GUID (identificateur global unique).

Voici quelques exemples de valeurs UUID:

40e6215d-b5c6-4896-987c-f30f3678f608
6ecd8c99-4036-403d-bf84-cf8400f67836
3f333df6-90a4-4fda-8dd3-9485d27cee36

Vous pouvez voir son hexadécimal 32 bits séparés par des tirets. Pour stocker id en tant que type de données uuid, c'est uuid qui a été introduit dans PostgreSQL 9.4. Il existe plusieurs algorithmes pour le générer. Vous devez également installer certaines extensions telles queuuid-oospou pgcrypto.

PostgreSQL 9.5 recommande d'utiliser pgcrypto. gen_random_uuid() . La fonction est utilisée pour générer uuid.

CREATE EXTENSION pgcrypto;
CREATE TABLE table_name (
   id UUID PRIMARY KEY DEFAULT gen_random_uuid()
);

ULID quand à lui signifie Identificateur Tri Lexicographique Unique Universel,

Il est composé de deux nombres codés en base32, d'un horodatage UNIX suivi d'un nombre aléatoire.

   01AN4Z07BY      79KA1307SR9X4MV3
   |-------------|    |----------------|
     Timestamp          Randomness
         48bits                   80bits

Pour obtenir cette extension sur PostgreSQL, essayez pg_ulid. Il existe également d'autres packages qui peuvent être étudiés.

   SELECT ulid(); -- 01C1P15MBWYBWDG2WYRG08VCFR

Le type de données peut être utilisé en binaire ou en texte.

   CREATE EXTENSION pg_ulid;
   CREATE TABLE table_name (
       id binary PRIMARY KEY DEFAULT ulid()
       );

Les valeurs par défaut

Serial / Cela génère des identifiants en séquence par défaut, incrémentales. Par exemple, si vous avez une table user insérez des valeurs.

   INSERT INTO users(name) VALUES('Toto');
   INSERT INTO users(id, name) VALUES(DEFAULT, 'Bob');

Il insérera deux enregistrements avec les valeurs 1 et 2

   id |  name
   ----+--------
    1 | Alice
    2 | Bob
    (2 rows)

Maintenant, si nous utilisons uuid à la place, cela générera un hexadécimal aléatoire.

id                                                                        |  name
---------------------------------------+--------
0d60a85e-0b90-4482-a14c-108aea2557aa | Alice
39240e9f-ae09-4e95-9fd0-a712035c8ad7  | Bob

Notez que ces UUID ne sont pas du texte, mais sont des types natifs.

Vient ensuite ULID qui est un texte ou un binaire

  id                                                          |  name
  ---------------------------+-----------------
  01DGTGVPBFE12HMKC0667JSWFM | Alice
  01DGTGXRS35VGTW5KXFN157HCV | Bob

Problème avec SERIAL

  • Lorsque vous utilisez un système distribué dans une solution scalée, vous allez bien sûr rencontrer des problèmes lorsque les mêmes identifiants seront générés par deux instances.
  • Pour les modèles jeunes, il est pratique, mais peu évolutif.
  • Cela peut nécessiter un aller-retour pour une demande d'écriture et les performances sont donc lentes.
  • Un ID est facile à deviner pour une ressource donnée, et peut-être que celle-ci ne lui appartient pas, il faudra revérifier la sécurité d'accès à la donnée.
  • Cela peut également desservir le système, car en créant une ressource, vous pourriez voir le nombre d'éléments dans cette table. En créant un compte utilisateur, vous apercevez /profile/75, cela signifie que vous donnez l'information qu'il n'y a QUE 75 entrées dans la base. Cela peut renseigner des concurrents, et/ou être une faille de sécurité.

Comment UUID résout ces défis ?

  • Avec un système distribué, l'algorithme génère un hexadécimal 32 bits aléatoires qui aura une très faible probabilité de conflit avec une autre instance.
  • Il est évolutif. Ce sont des nombres de 128 bits, généralement exprimés en 32 hexadécimaux.
  • Un aller-retour vers un hit Db peut être évité, car l'identifiant peut être déterminé avant la création réelle de l'enregistrement. Cela améliore les performances pour les demandes d'écriture.
  • En raison du caractère aléatoire, il est difficile de deviner d'autres identifiants et le nombre maximal d'enregistrements dans le système.

Problèmes avec l'UUID

  • Cet index n'est pas triable, car les chiffres et lettres sont aléatoires. Si vous ne stockez pas la création d'un enregistrement, il est difficile de deviner quel enregistrement est créé en premier.
  • Vous allez devoir effectuer une indexation sur une colonne supplémentaire, et vous perdez l'utilité de cet index, en tout cas pour le tri.
  • Lorsque vous allez utiliser une LIMIT en SQL ou pour de la pagination, vous allez avoir des soucis. Il est probable qu'il affiche un enregistrement plusieurs fois sur différentes pages, ou même qu'un enregistrement ne soit pas du tout affiché. Il se comporte en fait un peu comme lorsque les enregistrements sont récupérés dans un ordre random.

ULID: le meilleur des deux saveurs ensemble

  • Étant donné que la première partie de uuid est un horodatage, il peut être facilement trié.
  • La deuxième partie aléatoire le rend idéal pour le système distribué sur plusieurs instances.
  • Aucune indexation supplémentaire n'est requise et aucune sortie originale lorsque vous souhaitez utiliser la pagination.
  • Toujours évolutif comme UUID.
  • Aucun coup DB aller-retour n'est requis.
  • Difficile de deviner, voire impossible les identifiants d'une ressource. (Au moins manuellement).

Quand ne pas utiliser ULID?

  • Lorsque vous ne souhaitez pas afficher l'horodatage. (Bien qu'encodé)
  • Le tri peut ne pas fonctionner qu'en quelques millisecondes.

Et comment migrer vers UUID ou vers ULID ?

Imaginons que nous ayons une entité Enfant, et une entité Parent, et que dans la base de données, l'enfant a le parent_id dans ses colonnes.

Dans les annotations Doctrine, vous prenez comme dans la doc, pour créer une colonne new_id dans la table enfant. Appelons la new_parent_id et new_id

/**    

* @ORM\Id    

* @ORM\Column(type="ulid", unique=true)    

* @ORM\GeneratedValue(strategy="CUSTOM")    

* @ORM\CustomIdGenerator(class="doctrine.ulid_generator")    

*/    

private $id;    

public function getId(): ?Ulid {

    return $this->id;  

}

  1. Ensuite, vous pourrez avec une commande générer pour chaque ligne Parent les nouveaux ulid, avec l'objet new Ulid(); et placer cette même valeur chez les enfants dans new_parent_id
  2. Puis vous pourrez ajouter une clé externe sql entre new_parent_id et new _id.
  3. Et une fois fait, vous pourrez supprimer les id des parents et les parent_id des enfants, en ayant au préalable supprimé la clé externe liant les deux, et la clé primaire sur l'id Parent
  4. Ensuite vous pourrez ajouter la clé primaire sur le new_id
  5. Et enfin, vous pourrez renommer vos colonnes pour que tout revienne dans l'ordre dans votre application

Conclusion

En fait, cela dépend surtout de vos besoins en termes de développement. La migration d'un système à un autre est faisable donc vous gardez toujours cette possibilité si vous souhaitez changer de système.

Symfony quant à lui, a décidé de faire le pas, d'intégrer uuid et ulid au sein de ses types doctrine dont le composant est ici, et disponible également en contrainte.

Selon vos besoins, Efficience IT sait intégrer un système ou un autre, n'hésitez pas à nous contacter en cas de besoin d'accompagnement ou d'expertise sur ces sujets.

S'inscrire à la Newsletter

Profitez des news, des nouveaux articles, et des nouveaux posts.

Thanks for joining our newsletter.
Oops! Something went wrong.