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.
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/
Lees meer
Alembic MariaDB SQLAlchemy UUID
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.
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.
Recent
- Database UUID primaire sleutels van je webapplicatie verbergen
- Don't Repeat Yourself (DRY) met Jinja2
- SQLAlchemy, PostgreSQL, maximum aantal rijen per user
- Toon de waarden in SQLAlchemy dynamische filters
- Veilige gegevensoverdracht met Public Key versleuteling en pyNaCl
- rqlite: een alternatief voor SQLite met hoge beschikbaarheid en distributed
Meest bekeken
- Met behulp van Python's pyOpenSSL om SSL-certificaten die van een host zijn gedownload te controleren
- Gebruik van UUIDs in plaats van Integer Autoincrement Primary Keys met SQLAlchemy en MariaDb
- PyInstaller en Cython gebruiken om een Python executable te maken
- Maak verbinding met een dienst op een Docker host vanaf een Docker container
- SQLAlchemy: Gebruik van Cascade Deletes om verwante objecten te verwijderen
- Flask RESTful API verzoekparametervalidatie met Marshmallow-schema's