angle-up arrow-clockwise arrow-counterclockwise arrow-down-up arrow-left at calendar card-list chat check envelope folder house info-circle pencil people person person-plus phone plus question-circle search tag trash x

Verwendung von UUIDs anstelle von Integer Autoincrement Primary Keys mit SQLAlchemy und MariaDb

9 April 2020 durch Peter

UUID Primary Keys sind 'universell einzigartig' und haben viele Vorteile. Ziehen Sie in Betracht, sie in Ihrem nächsten Projekt zu verwenden.

post main image
https://unsplash.com/@skylergeraldphoto

Wir alle kennen das Problem Integer Autoincrement Primary Key. Funktioniert großartig, viele Software macht es auf diese Weise. Es ist schnell. Aber sie hat auch Grenzen.

Eine Alternative für den Integer Autoincrement Primary Key ist der UUID (Universally Unique IDentifier) Primary Key. Eine UUID ist eine 128-Bit-Zahl. Sie kann z.B. durch eine Zeichenfolge dargestellt werden:

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

oder eine hexadezimale Zeichenfolge (die gleiche wie oben, wobei das Strichzeichen entfernt wurde):

05390f2b48e845c78ae408272dc69122

oder einen binären Wert.

UUIDs sind eindeutig, es gibt keine Nummerierung. Wenn Sie ein UUID auf Ihrem Laptop in London erzeugen und Ihr Kollege in New York ein UUID auf seinem Laptop erzeugt, dann sind beide UUIDs eindeutig, sie können nie gleich sein. Es gibt mehrere Versionen von UUIDs, hier werden wir Version 4 verwenden, auch bekannt als "zufällige" UUID. Die Wahrscheinlichkeit einer Kollision ist fast keine.

Sind UUID Primary Keys eine gute Wahl?

Ich glaube, auch für kleine Projekte gibt es genügend Gründe. Jeder kennt das Problem beim Zusammenführen von Datenbankeinträgen oder Tabellen. Wenn Ihre Datenbanktabellen Integer Autoincrement Primary Keys verwenden, kann das chaotisch sein, aber mit UUIDs ist es ein Kinderspiel.

Example#1: Blog mit mehreren Offline-Autoren

Angenommen, Sie haben dieses CMS / diese Blog-Website und Sie haben zwei Personen, Blog-Post-Autoren, die jeweils an einer lokalen Kopie arbeiten, die auf ihren Laptops laufen. Wie übertragen Sie dann die neuen Datensätze in das Produktionssystem?

Wenn Ihre Datenbank Integer Autoincrement Primary Keys verwendet, müssen Sie äußerst vorsichtig sein. Wahrscheinlich tun Sie dies, indem Sie einige Datensätze exportieren und sie dann in Ihre Produktionsdatenbank importieren. Sie können die IDs der Datensätze nicht importieren, da beide Autoren unterschiedliche Beiträge mit identischen IDs haben können. Schwieriger wird es, wenn die Blog-Einträge auf (neue) Datensätze einer Tabelle verweisen, die die Links/Referenzen für einen Blog-Eintrag enthält. Wahrscheinlich werden Sie ein Skript schreiben, das sich damit befasst.

Wenn wir dagegen UUID verwenden würden, exportieren wir einfach die neuen Datensätze von beiden Laptops und importieren sie in die Produktionsdatenbank. Keine Konflikte, das geht ganz einfach.

Example#2: Webshop mit offline vorbereiteten Produktinformationen

Angenommen, Sie haben einen Webshop und Sie haben Lieferanten, die ihre vorbereiteten Produktinformationsdatensätze mit UUID Primary Keys liefern. Wenn Ihr Webshop bereits UUID Primary Keys verwendet, ist dies sehr einfach zu importieren, keine Konvertierungen.

Vor- und Nachteile der Verwendung einer UUID als Primärschlüssel

Die Vorteile:

  • Sie können UUIDs überall
    erzeugen Dies bedeutet, dass Sie Datensätze ohne Verbindung zu einer Datenbank erstellen können
  • Die UUIDs sind tabellen-, datenbank- und systemübergreifend
    eindeutig. Dies erleichtert das Zusammenführen von Datensätzen aus verschiedenen Tabellen, Datenbanken
  • UUIDs machen die Replikation einfacher

Nachteile:

  • Benötigt mehr Speicher und Plattenplatz, Index-Tabellen werden größer.
    Aber wen kümmert das heute noch?
  • Langsamer für SELECT bei großen Tabellen
    Aber Optimierungen sind möglich
  • Kann viel langsamer für INSERT
    sein Es dauert länger, den Index neu zu berechnen, kann ein ernstes Problem sein
  • Schwieriger zu debuggen wegen fehlender Einfügereihenfolge
    Aber Sie können eine DATETIME (Mikrosekunden) 'created_on'-Spalte hinzufügen und diese zum Sortieren verwenden.

UUIDs verhindern die Preisgabe von Geschäftsinformationen (Geheimnisse)

Ein weiterer wichtiger Vorteil der Verwendung eines UUID Primärschlüssels anstelle eines Integer Autoincrement Primary Key besteht darin, dass Sie sich weniger Sorgen über die Offenlegung von Geschäftsinformationen machen müssen. Beispiel: Kontakte werden in einer Kontakttabelle gespeichert. Wenn diese Tabelle eine Integer Autoincrement Primary Key hat, könnte eine Bearbeitungsansicht die user_id in der URL aufdecken:

    /contact/edit/24

Das bedeutet, dass Sie die Anzahl der users einer Website erraten können. Wenn Sie einen Primärschlüssel UUID verwenden, gibt die user_id in der URL nichts preis:

    /contact/edit/5489b4d7abd9416f9ba31d48fbb0f5be

Natürlich ist es immer noch besser, niemals irgendeine Art von Primary Key zu belichten. Das mag trivial aussehen, kann aber eine Menge zusätzlicher Arbeit verursachen.

Beispiel: Ein Kontaktdatensatz enthält einen Fremdschlüssel für eine Stadt. Der Fremdschlüssel des Kontakts verweist auf die Stadt Primary Key. Wenn wir die Spalte Primary Keys nicht offen legen wollen, können wir jeder Tabelle eine Spalte Surrogate Key hinzufügen und für jeden Datensatz, den wir erstellen, eine Spalte Surrogate Key generieren. Wenn wir den Kontakt bearbeiten, müssen wir eine Auswahl-/Dropdown-Liste mit den verfügbaren Städten anzeigen. Wir möchten nicht, dass die Primary Keys der Städte angezeigt werden, und wählen stattdessen die Surrogate Keys aus. Wenn dann das Kontaktformular eingereicht wird, müssen wir zuerst den Wert Primary Key der Stadt nachschlagen, wobei wir den eingereichten Wert Surrogate Key verwenden. Sobald wir den Wert Primary Key der Stadt abgerufen haben, können wir den Kontakt aktualisieren.

Python und UUIDs: Python

Die Erstellung von UUIDs in Python ist einfach:

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

Wir können das Hexadezimalzeichen UUID auch wieder in eine Zeichenfolge UUID umwandeln:

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

Reihenfolge der Einsätze

Wenn wir UUID Primärschlüssel verwenden, verlieren wir die Reihenfolge der Einfügungen. In meinem Modell hat jede Tabelle eine MariaDb DATETIME 'created_on'-Spalte, was bedeutet, dass die Mindestauflösung 1 Sekunde beträgt. Wenn die Reihenfolge der Einfügungen wichtig ist, können wir die Auflösung der 'created_on'-Spalte auf Mikrosekunden erhöhen, indem wir DATETIME(6) verwenden. Wenn Sie die Einfügereihenfolge wirklich benötigen, können Sie eine zusätzliche Autoincrement-Spalte hinzufügen.

Leistung

Die Verwendung von UUIDs für Primärschlüssel beschleunigt die Abfragen nicht wirklich. SELECT -Abfragen sind langsamer, weil der String-Vergleich statt des Integer-Vergleichs durchgeführt wird. INSERT -Abfragen können wirklich langsam sein, weil für jede Einfügung der Index mit Zufallszahlen neu aufgebaut werden muss. Es sind Optimierungen möglich, aber sie haben auch Nachteile, wie zum Beispiel, dass die UUIDs nicht mehr 'zufällig' sind.

SQLAlchemy, MariaDb, UUIDs und Alembic

Ich verwende SQLAlchemy und MariaDb, also verwenden wir einen 32-stelligen Hex-Wert UUID. PostgreSQL unterstützt bereits standardmäßig UUIDs. Es gibt ein Rezept auf der Website SQLAlchemy : Backend-Diagnostik GUID Typ", siehe Links unten. Ich habe dieses Rezept (Skript) mit einer kleinen Änderung in meine Datei models.py aufgenommen. Dann schreibe ich mein UUIDCity-Modell als:

class  UUIDCity(Base):

    __tablename__ = 'uuid_city'

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

Damit wird die Erzeugung eines UUID für einen neuen Datensatz erledigt. Ich habe das Skript ein wenig geändert, um sicherzustellen, dass es einen Zeichenfolgenwert und kein GUID-Objekt zurückgibt. Dadurch ist es in Abfragen viel einfacher zu verwenden. (Aber ich könnte spezifische Vorteile von SQLAlchemy verlieren?)

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

Es gibt hier ein Problem mit Alembic . Wenn Sie Alembic für das Modell UUIDCity ausführen, liegt hier ein Problem vor:

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

Alembic erzeugt eine Versionsdatei mit:

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)

Ich habe einen Ordner namens 'shared' und in diesem Ordner befindet sich die Datei models.py. Dadurch wird der Upgrade-Befehl fehlschlagen. Das Ergebnis des Befehls:

alembic upgrade head

ist:

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

Das ist etwas verwirrend, aber es hat mit der Tatsache zu tun, dass es keinen Datentyp shared.models.GUID() gibt. Sie können env.py und script.py.mako modifizieren. Es gibt einige Informationen in den Links unten. Ich habe dies noch nicht getan. Für den Moment habe ich die Versionsdatei bearbeitet und geändert:

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

in:

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

Jetzt läuft der Upgrade-Befehl ohne Fehler. Dieses Verfahren müssen wir auch bei Fremdschlüsseln befolgen. Natürlich ist es besser, die oben erwähnten Skripte zu modifizieren, aber diese Bearbeitung ist nur einmal bei der Erstellung des Modells erforderlich.

Zusammenfassung

Wie bin ich dazu gekommen, diesen Beitrag zu schreiben? Ich glaube, ich habe nach Möglichkeiten gesucht, einzigartige Zufallscodes zu generieren. In Python ist die Verwendung von UUIDs einfach. Ich habe vielleicht einen zufälligen einzigartigen Code + MySQL eingegeben. Dann haben Sie auch den UUID Primary Keys getroffen. Ich habe in der Vergangenheit darüber gelesen, aber diesmal habe ich mir die Zeit genommen, dieses Thema zu recherchieren.

Wenn Sie eine Datenbank mit Integer Autoincrement Primary Keys betreiben, lohnt es sich, auf die Verwendung von UUID Primary Keys umzustellen? Ich glaube nicht. Es kann viel Zeit in Anspruch nehmen und Sie können viel Ärger bekommen.

Aber für neue Datenbanken, auch für sehr kleine, empfehle ich die Verwendung von UUID Primary Keys. Man weiß nie, ob Sie jemals in eine Situation kommen werden, in der Sie Tabellen zusammenführen, Datensätze mit UUID -Schlüsseln importieren und Personen offline mit Ihrer Datenbank arbeiten lassen müssen.

Natürlich gibt es Ausnahmen. Nehmen wir an, Sie probieren Webcam-Bilder aus und speichern ihre Referenzen in einem Datenbankeintrag. In diesem Fall möchten Sie die maximale Leistung von INSERT erreichen, also verwenden Sie hier nicht UUID Primary Keys !

Eine hochleistungsfähige Website mit einer großen Datenbank muss normalerweise nicht unter langsamer UUID Primary Keys leiden. SELECT ist nur geringfügig langsamer, und wenn richtig konzipiert, wird die Mehrzahl der (SELECT) Anfragen aus dem Cache bedient.

Für diese CMS/Blog-Website habe ich beschlossen, bestehende Integer Autoincrement Primary Keys (sehr) langsam in UUID Primary Keys umzuwandeln.

Links / Impressum

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/

Einen Kommentar hinterlassen

Kommentieren Sie anonym oder melden Sie sich zum Kommentieren an.

Kommentare

Eine Antwort hinterlassen

Antworten Sie anonym oder melden Sie sich an, um zu antworten.