angle-uparrow-clockwisearrow-counterclockwisearrow-down-uparrow-leftatcalendarcard-listchatcheckenvelopefolderhouseinfo-circlepencilpeoplepersonperson-fillperson-plusphoneplusquestion-circlesearchtagtrashx

Gebruik van UUIDs in plaats van Integer Autoincrement Primary Keys met SQLAlchemy en MariaDb

UUID Primary Keys zijn 'universum uniek' en hebben vele voordelen. Overweeg ze te gebruiken in uw volgende project.

9 april 2020
post main image
https://unsplash.com/@skylergeraldphoto

We kennen allemaal de Integer Autoincrement Primary Key. Werkt geweldig, veel software doet het op deze manier. Het is snel. Maar het heeft ook beperkingen.

Een alternatief voor de Integer Autoincrement Primary Key is de UUID (Universally Unique IDentifier) Primary Key. Een UUID is een 128-bit getal. Het kan bijvoorbeeld worden gerepresenteerd door een string:

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

of een hex-string (hetzelfde als hierboven met het streepje verwijderd):

05390f2b48e845c78ae408272dc69122

of een binaire waarde.

UUIDs zijn uniek, er is geen nummering aan verbonden. Als u een UUID genereert op uw laptop in Londen en uw collega in New York genereert een UUID op zijn laptop, dan zijn beide UUID's uniek, ze kunnen nooit hetzelfde zijn. Er zijn verschillende versies van UUIDs, hier gaan we versie 4 gebruiken, ook bekend als 'random' UUID. De kans op een botsing is bijna nihil.

Is UUID Primary Keys een goede keuze?

Ik denk dat er genoeg redenen zijn, zelfs voor kleine projecten. Iedereen kent het probleem bij het samenvoegen van databaserecords of tabellen. Als uw databasetabellen Integer Autoincrement Primary Keys gebruiken kan dit een rommeltje zijn, maar met UUIDs is het een makkie.

Voorbeeld#1: Blog met meerdere offline auteurs

Stel dat je deze CMS / blog website hebt en je hebt twee personen, blog postschrijvers, die elk aan een lokale kopie werken, die op hun laptop draaien. Hoe breng je dan de nieuwe records over naar het productiesysteem?

Als uw database gebruik maakt van Integer Autoincrement Primary Keys dan moet u uiterst voorzichtig zijn. Waarschijnlijk doet u dit door enkele records te exporteren en deze vervolgens te importeren in uw productiedatabase. U kunt de ID's van de records niet importeren, beide schrijvers kunnen verschillende berichten hebben met identieke ID's. Het wordt moeilijker als de blogberichten verwijzen naar (nieuwe) records van een tabel met de links/referenties voor een Blogbericht. Waarschijnlijk zult u een script schrijven waarin dit wordt behandeld.

Als we daarentegen UUID zouden gebruiken, dan exporteren we gewoon de nieuwe records van beide laptops en importeren we ze in de productiedatabase. Geen conflicten, dat doet het gemakkelijk.

Voorbeeld#2: Webshop met offline geprepareerde productinformatie

Stel dat u een webshop heeft en u heeft leveranciers die hun geprepareerde productinformatie aanleveren met UUID Primary Keys. Als uw webshop al gebruik maakt van UUID Primary Keys is dit zeer eenvoudig te importeren, geen conversies.

Voor- en nadelen van het gebruik van een UUID als primaire sleutel

Voordelen:

  • U kunt overal
    UUIDs genereren Dit betekent dat u records kunt aanmaken zonder verbinding te maken met een database.
  • De UUIDs zijn uniek in tabellen, databases, systemen
    Dit maakt het eenvoudig om records uit verschillende tabellen, databases samen te voegen.
  • UUIDs maken replicatie makkelijker

Nadelen:

  • Gebruikt meer geheugen en schijfruimte, indextabellen worden groter
    Maar wie geeft daar nu om?
  • Langzamer voor SELECT met grote tabellen
    Maar optimalisaties zijn mogelijk.
  • Kan veel langzamer voor INSERT
    Het kost meer tijd om de index te herberekenen, kan een ernstig probleem zijn.
  • Moeilijker te debuggen vanwege ontbrekende invoegvolgorde
    Maar u kunt een DATETIME (microseconden) 'create_on' kolom toevoegen en deze gebruiken om te sorteren.

UUID voorkomt blootstelling van bedrijfsinformatie (geheimen)

Een bijkomend belangrijk voordeel van het gebruik van een UUID primaire sleutel in plaats van een Integer Autoincrement Primary Key is dat u zich minder zorgen hoeft te maken over het blootleggen van bedrijfsinformatie. Voorbeeld: contacten worden opgeslagen in een contactentabel. Als deze tabel een Integer Autoincrement Primary Key heeft, kan een bewerkingsweergave de user_id in de URL blootleggen:

    /contact/edit/24

Dit betekent dat je het aantal users van een website kunt raden. Als u een UUID primaire sleutel gebruikt dan geeft de user_id in de URL niets te zien:

    /contact/edit/5489b4d7abd9416f9ba31d48fbb0f5be

Natuurlijk is het nog steeds beter om geen enkel type van Primary Key bloot te leggen. Dit ziet er misschien triviaal uit, maar kan veel extra werk veroorzaken.

Voorbeeld: een contactrecord bevat een Vreemde Sleutel voor een stad. De contactpersoon Vreemde Sleutel verwijst naar de stad Primary Key. Als we de Primary Keys niet willen blootleggen, kunnen we aan elke tabel een Surrogate Key kolom toevoegen en een Surrogate Key genereren voor elk record dat we aanmaken. Bij het bewerken van het contact moeten we een select / dropdown lijst tonen met de beschikbare steden. We willen niet dat de Primary Keys van de steden wordt getoond en selecteren in plaats daarvan de Surrogate Keys . Wanneer het Contactformulier wordt ingediend, moeten we eerst de Primary Key van de Stad opzoeken met behulp van de ingediende Surrogate Key -waarde. Zodra we de Stad Primary Key hebben opgehaald, kunnen we het Contactformulier bijwerken.

Python en UUIDs

Het genereren van UUIDs in Python is eenvoudig:

>>> 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'

We kunnen ook de hex UUID terug converteren naar een string UUID:

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

Volgorde van de inzetstukken

Bij gebruik van UUID primaire toetsen verliezen we de volgorde van de inserts. In mijn model heeft elke tabel een MariaDb DATETIME 'create_on' kolom wat betekent dat de minimale resolutie 1 seconde is. Wanneer de volgorde van de inserts belangrijk is kunnen we de resolutie van de 'create_on' kolom verhogen tot microseconden door gebruik te maken van DATETIME(6). Als u de insert order echt nodig heeft kunt u een extra Autoincrement kolom toevoegen.

Prestaties

Het gebruik van UUIDs voor de primaire toetsen versnelt de vragen niet echt. SELECT queries presteren langzamer vanwege de string vergelijking in plaats van de gehele vergelijking. INSERT queries kunnen erg langzaam zijn omdat voor elke invoegtoepassing de index moet worden opgebouwd met willekeurige getallen. Er zijn optimalisaties mogelijk, maar ze hebben ook nadelen, zoals dat de UUIDs niet meer 'random' zijn.

SQLAlchemy, MariaDb, UUIDs en Alembic.

Ik gebruik SQLAlchemy en MariaDb, dus laten we een 32-karakter hex-waarde UUID gebruiken. PostgreSQL ondersteunt al UUIDs out-of-the-box. Er staat een recept op de site van SQLAlchemy : 'Achtergrondkenmerk GUID Type', zie onderstaande links. Ik heb dit recept (script), met een kleine aanpassing, opgenomen in mijn models.py bestand. Vervolgens schrijf ik mijn UUIDCity model als:

class  UUIDCity(Base):

    __tablename__ = 'uuid_city'

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

Dit zorgt voor het genereren van een UUID voor een nieuw record. Ik heb het script een beetje aangepast om er zeker van te zijn dat het een stringwaarde oplevert en niet een GUID object. Dit maakt het veel gemakkelijker te gebruiken in queries. (Maar ik kan specifieke SQLAlchemy voordelen verliezen?)

    ...
    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

Er is hier een Alembic probleem. Wanneer u Alembic voor het model UUIDCity uitvoert:

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

Alembic genereert een versiebestand met:

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)

Ik heb een map genaamd 'shared' en in deze map staat het models.py bestand. Dit zal het upgrade commando doen mislukken. Het resultaat van het commando:

alembic upgrade head

is:

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

Dit is een beetje verwarrend, maar het heeft te maken met het feit dat er geen datatype shared.models.GUID() is. U kunt env.py en script.py.mako aanpassen. Er staat wat informatie in de links hieronder. Ik heb dit nog niet gedaan. Op dit moment heb ik het versiebestand bewerkt en gewijzigd:

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

in:

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

Nu draait het upgrade commando zonder fout. We moeten deze procedure ook volgen voor buitenlandse toetsen. Natuurlijk is het beter om de bovenstaande scripts te wijzigen, maar deze bewerking is slechts één keer nodig bij het maken van het model.

Samenvatting

Hoe kwam ik aan dit bericht? Ik denk dat ik op zoek was naar manieren om unieke willekeurige codes te genereren. In Python is het gebruik van UUIDs eenvoudig. Ik heb misschien willekeurige unieke code + getypt. Dan drukt u ook op de UUID Primary Keys. Ik heb er in het verleden over gelezen, maar deze keer heb ik de tijd genomen om dit onderwerp te onderzoeken.

Als u een database met Integer Autoincrement gebruikt, is het dan de moeite waard om UUID om te zetten in Primary Keys? Ik denk het niet. Het kan veel tijd in beslag nemen en je kunt er veel moeite mee doen.

Maar voor nieuwe databases, zelfs zeer kleine, raad ik aan om UUID Primary Keys te gebruiken. Je weet nooit of je ooit in een situatie komt waarin je tabellen moet samenvoegen, records moet importeren met UUID sleutels, personen offline moet laten werken met je database.

Natuurlijk zijn er uitzonderingen. Ga ervan uit dat u een voorbeeld neemt van de webcambeelden en hun referenties opslaat in een database record. In dit geval wilt u maximale INSERT prestaties, dus gebruik hier geen UUID Primary Keys !

Een high performance website met een grote database hoeft doorgaans niet te lijden onder een tragere UUID Primary Keys. SELECT is alleen iets langzamer en als het goed ontworpen is, wordt het merendeel van de (SELECT) verzoeken vanuit de cache bediend.

Voor deze CMS/blog website heb ik besloten om bestaande Integer Autoincrement Primary Keys (zeer) langzaam om te zetten naar UUID Primary Keys.

Links / credits

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/

Laat een reactie achter

Reageer anoniem of log in om commentaar te geven.

Opmerkingen (1)

Laat een antwoord achter

Antwoord anoniem of log in om te antwoorden.

avatar

Thanks for your post! I finally got my challenge solved by also commenting the 2 lines in the GUID class. It was a pain to get UUID objects back instead of plain UUID strings.