Utiliser UUIDs au lieu de Integer Autoincrement Primary Keys avec SQLAlchemy et MariaDb

Nous connaissons tous le Integer Autoincrement Primary Key. Il fonctionne très bien, beaucoup de logiciels le font de cette façon. Il est rapide. Mais il a aussi ses limites.

Une alternative pour le Integer Autoincrement Primary Key est le UUID (Universally Unique IDentifier) Primary Key. Un UUID est un nombre de 128 bits. Il peut être représenté par une chaîne de caractères, par exemple :

05390f2b-48e8-45c7-8ae4-08272dc69122

ou une chaîne hexadécimale (comme ci-dessus avec le caractère tiret supprimé) :

05390f2b48e845c78ae408272dc69122

ou une valeur binaire.

UUIDs sont uniques, il n'y a pas de numérotation. Si vous générez un UUID sur votre ordinateur portable à Londres et que votre collègue à New York génère un UUID sur son ordinateur portable, alors les deux UUID sont uniques, ils ne peuvent jamais être identiques. Il existe plusieurs versions de UUID, ici nous allons utiliser la version 4, également appelée UUID "aléatoire". La probabilité d'une collision est presque nulle.

UUID Primary Keys sont-ils un bon choix ?

Je crois qu'il y a suffisamment de raisons, même pour les petits projets. Tout le monde connaît le problème lorsqu'il s'agit de fusionner des enregistrements de base de données ou des tables. Si vos tables de base de données utilisent Integer Autoincrement Primary Keys , cela peut être compliqué, mais avec UUIDs, c'est un jeu d'enfant.

Exemple n°1 : Blog avec plusieurs auteurs hors ligne

Supposons que vous ayez ce site web de CMS / blog et que vous ayez deux personnes, auteurs de billets de blog, travaillant chacune sur une copie locale, fonctionnant sur leur ordinateur portable. Alors comment transférer les nouveaux enregistrements dans le système de production ?

Si votre base de données utilise Integer Autoincrement Primary Keys , vous devez être extrêmement prudent. Vous le faites probablement en exportant certains enregistrements et en les important ensuite dans votre base de données de production. Vous ne pouvez pas importer les ID des enregistrements, les deux auteurs peuvent avoir des postes différents avec des ID identiques. Cela devient plus difficile lorsque les articles de blog font référence aux (nouveaux) enregistrements d'une table contenant les liens/références d'un article de blog. Il est probable que vous écriviez un script qui traite de ce problème.

Si nous utilisions UUID Primary Keys , nous exporterions simplement les nouveaux enregistrements des deux ordinateurs portables et les importerions dans la base de données de production. Pas de conflits, c'est facile.

Exemple n° 2 : Boutique en ligne avec des informations sur des produits préparés hors ligne

Supposons que vous ayez une boutique en ligne et que vous ayez des fournisseurs qui livrent leurs dossiers d'information sur les produits préparés avec UUID Primary Keys. Si votre boutique en ligne utilise déjà UUID Primary Keys , il est très facile de l'importer, sans conversion.

Avantages et inconvénients de l'utilisation d'un UUID comme clé primaire

Avantages :

• Vous pouvez générer des UUID partout
  Cela signifie que vous pouvez créer des enregistrements sans vous connecter à une base de données
• Les UUIDs sont uniques dans les tables, les bases de données, les systèmes
  . Cela permet de fusionner facilement les enregistrements de différentes tables, bases de données
• UUIDs rend la réplication plus facile

Inconvénients :

• Utilise plus de mémoire et d'espace disque, les tables d'index s'agrandissent
  Mais qui s'en soucie aujourd'hui ?
• Plus lent pour SELECT avec de grandes tables
  Mais des optimisations sont possibles
• Peut être beaucoup plus lent pour INSERT
  Cela prend plus de temps pour recalculer l'index, peut être un problème sérieux
• 
  Mais vous pouvez ajouter une colonne DATETIME (microsecondes) "created_on" et l'utiliser pour le tri.

UUIDs empêchent la divulgation d'informations commerciales (secrets)

Un autre avantage important de l'utilisation d'une clé primaire UUID au lieu d'une clé Integer Autoincrement Primary Key est que vous pouvez moins vous soucier de la divulgation d'informations commerciales. Exemple : les contacts sont stockés dans une table de contacts. Si cette table a un Integer Autoincrement Primary Key , une vue d'édition pourrait exposer le user_id dans l'URL :

    /contact/edit/24

Cela signifie que vous pouvez deviner le nombre de users d'un site web. Si vous utilisez une clé primaire UUID , alors l'identifiant user_id dans l'URL n'expose rien :

    /contact/edit/5489b4d7abd9416f9ba31d48fbb0f5be

Bien sûr, il est toujours préférable de ne jamais exposer aucun type de Primary Key. Cela peut sembler trivial mais peut entraîner un travail supplémentaire important.

Exemple : considérons qu'une fiche de contact contient une clé étrangère pour une ville. La clé étrangère du contact fait référence à la ville Primary Key. Si nous ne voulons pas exposer la Primary Keys , nous pouvons ajouter une colonne Surrogate Key à chaque table et générer une Surrogate Key pour chaque enregistrement que nous créons. Lors de l'édition du contact, nous devons afficher une liste déroulante avec les villes disponibles. Nous ne voulons pas que la colonne Primary Keys des villes soit exposée et sélectionnons plutôt la colonne Surrogate Keys . Ensuite, lorsque le formulaire de contact est soumis, nous devons d'abord rechercher le Primary Key de la ville en utilisant la valeur Surrogate Key soumise. Une fois que nous avons récupéré la valeur Primary Key de la ville, nous pouvons mettre à jour le contact.

Python et UUIDs

Il est facile de générer des UUID dans les Python :

>>> import uuid
>>> uuid_obj = uuid.uuid4()
>>> uuid_obj
UUID('05390f2b-48e8-45c7-8ae4-08272dc69122')
>>> str(uuid_obj)
'05390f2b-48e8-45c7-8ae4-08272dc69122'
>>> uuid_obj.hex
'05390f2b48e845c78ae408272dc69122'

Nous pouvons également convertir l'hexadécimal UUID en une chaîne de caractères UUID :

>>> uuid_obj2 = uuid.UUID(hex='05390f2b48e845c78ae408272dc69122', version=4)
>>> str(uuid_obj2)
'05390f2b-48e8-45c7-8ae4-08272dc69122'

Ordre des inserts

Lorsque nous utilisons les clés primaires UUID , nous perdons l'ordre des insertions. Dans mon modèle, chaque table a une colonne MariaDb DATETIME "created_on", ce qui signifie que la résolution minimale est de 1 seconde. Lorsque l'ordre des insertions est important, nous pouvons augmenter la résolution de la colonne "created_on" à quelques microsecondes en utilisant DATETIME(6). Si vous avez vraiment besoin de l'ordre d'insertion, vous pouvez ajouter une colonne Autoincrement supplémentaire.

Performance

L'utilisation de UUIDs pour les clés primaires n'accélère pas vraiment les requêtes. Les requêtes SELECT fonctionnent plus lentement en raison de la comparaison de chaînes de caractères au lieu de la comparaison d'entiers. Les requêtes INSERT peuvent être vraiment lentes car pour chaque insertion, l'index doit être reconstruit avec des nombres aléatoires. Des optimisations sont possibles, mais elles présentent également des inconvénients, comme le fait que les UUID ne sont plus "aléatoires".

SQLAlchemy, MariaDb, UUIDs et Alembic

J'utilise SQLAlchemy et MariaDb, alors utilisons une valeur hexagonale de 32 caractères UUID. PostgreSQL prend déjà en charge les UUID prêts à l'emploi. Il y a une recette sur le site de SQLAlchemy : Backend-agnostic GUID Type", voir les liens ci-dessous. J'ai inclus cette recette (script), avec une petite modification, dans mon fichier models.py. Ensuite, j'écris mon modèle UUIDCity comme :

class  UUIDCity(Base):

    __tablename__ = 'uuid_city'

    id = Column(GUID(), primary_key=True, default=uuid.uuid4)
    ...

Cela permet de générer un UUID pour un nouveau record. J'ai un peu modifié le script pour m'assurer qu'il renvoie une valeur de chaîne et non un objet GUID. Cela rend son utilisation beaucoup plus facile dans les requêtes. (Mais je risque de perdre certains avantages de SQLAlchemy D)

    ...
    def process_result_value(self, value, dialect):
        if value is  None:
            return value
        else:
            # 20200328/PPM commented the next two lines to return the db id as string 
            # if not isinstance(value, uuid.UUID):
            #     value = uuid.UUID(value)
            return value

Il y a un problème de Alembic ici. Lorsque vous exécutez Alembic pour le modèle UUIDCity :

alembic revision --autogenerate -m "some version text"

Alembic génère un fichier de versions avec :

def upgrade():
    # ### commands auto generated by  Alembic  - please adjust! ###
    op.create_table('uuid_demo_city',
    sa.Column('id', shared.models.GUID(), nullable=False),
    sa.Column('name', sa.String(length=250), server_default='', nullable=True),
    sa.PrimaryKeyConstraint('id')
    )
    op.create_index(op.f('ix_uuid_demo_city_name'), 'uuid_demo_city', ['name'], unique=False)

J'ai un dossier appelé "shared" et dans ce dossier se trouve le fichier models.py. Cela fera échouer la commande de mise à niveau. Le résultat de la commande :

alembic upgrade head

est :

  File "...", line 17, in upgrade
    sa.Column('id', shared.models.GUID(), nullable=False),
NameError: name 'shared' is not defined

C'est un peu déroutant, mais cela tient au fait qu'il n'y a pas de type de données shared.models.GUID(). Vous pouvez modifier env.py et script.py.mako. Vous trouverez quelques informations dans les liens ci-dessous. Je n'ai pas encore fait cela. Pour l'instant, j'ai édité le fichier des versions et j'ai modifié :

    sa.Column('id', shared.models.GUID(), nullable=False),

en :

    sa.Column('id', sa.CHAR(32), nullable=False),

Maintenant, la commande de mise à jour fonctionne sans erreur. Nous devons également suivre cette procédure pour les clés étrangères. Bien sûr, il est préférable de modifier les scripts mentionnés ci-dessus, mais cette modification n'a lieu qu'une seule fois lors de la création du modèle.

Résumé

Comment en suis-je venu à écrire ce billet ? Je pense que je cherchais des moyens de générer des codes aléatoires uniques. Dans Python , l'utilisation de UUIDs est facile. J'ai peut-être tapé le code aléatoire unique + MySQL. Ensuite, vous avez également tapé le code UUID Primary Keys. J'ai déjà lu des articles à ce sujet dans le passé, mais cette fois-ci, j'ai pris le temps de faire des recherches.

Si vous utilisez une base de données avec Integer Autoincrement Primary Keys , vaut-il la peine de passer à l'utilisation de UUID Primary Keys ? Je ne pense pas. Cela peut prendre beaucoup de temps et vous pouvez vous attirer beaucoup d'ennuis.

Mais pour les nouvelles bases de données, même très petites, je recommande d'utiliser UUID Primary Keys. Vous ne savez jamais si vous vous retrouverez un jour dans une situation où vous devrez fusionner des tables, importer des enregistrements avec des clés UUID , faire travailler des personnes hors ligne avec votre base de données.

Bien sûr, il y a des exceptions. Supposons que vous échantillonniez des images de webcam et que vous stockiez leurs références dans un enregistrement de la base de données. Dans ce cas, vous voulez une performance maximale de INSERT , alors n'utilisez pas UUID Primary Keys ici !

Un site web performant avec une grande base de données ne doit généralement pas souffrir d'un ralentissement UUID Primary Keys. SELECT est seulement un peu plus lent et, s'il est conçu correctement, la majorité des requêtes (SELECT) sont servies à partir du cache.

Pour ce site web de CMS/ blog, j'ai décidé de convertir (très) lentement les Integer Autoincrement existantes en UUID Primary Keys.

Liens / crédits

Alembic: How to migrate custom type in a model?
https://stackoverflow.com/questions/15668115/alembic-how-to-migrate-custom-type-in-a-model

Backend-agnostic GUID Type
https://docs.sqlalchemy.org/en/13/core/custom_types.html#backend-agnostic-guid-type

Best UUID data type?
https://www.memsql.com/forum/t/best-uuid-data-type/182

Configuring MySQL to allow generation of Primary Key values
https://stackoverflow.com/questions/21669815/configuring-mysql-to-allow-generation-of-primary-key-values

GUID/UUID Performance
https://mariadb.com/kb/en/guiduuid-performance/

How can I bind a list to a parameter in a custom query in sqlalchemy?
https://stackoverflow.com/questions/13190392/how-can-i-bind-a-list-to-a-parameter-in-a-custom-query-in-sqlalchemy

How can I use UUIDs in SQLAlchemy?
https://stackoverflow.com/questions/183042/how-can-i-use-uuids-in-sqlalchemy

Microseconds in MariaDB
https://mariadb.com/kb/en/microseconds-in-mariadb/

UUIDs are Popular, but Bad for Performance — Let’s Discuss
https://www.percona.com/blog/2019/11/22/uuids-are-popular-but-bad-for-performance-lets-discuss/