So entwerfen Sie einen Primärschlüssel in MySQL

Dieser Artikel erläutert das Primärschlüsselproblem von MySQL, versteht das Wissen im Zusammenhang mit dem MySQL-Primärschlüssel aus der Perspektive des Warum und geht auf das Problem der Lösung zur Primärschlüsselgenerierung ein. Keine Angst mehr, nur CRUD zu kennen, wenn man nach MySQL fragt.

1. Warum ein Primärschlüssel benötigt wird

Datensätze müssen eindeutig sein (erste Normalform)

Daten müssen mit Join verknüpft sein

Der zugrunde liegende Index der Datenbank wird zum Abrufen von Daten verwendet

Das Folgende ist eine Menge Unsinn, Sie können direkt zum nächsten Abschnitt springen.

„Informationen werden verwendet, um zufällige Unsicherheiten zu beseitigen“ (Shannon). Menschen können die Welt verstehen und verändern, indem sie unterschiedliche Informationen aus der Natur und der Gesellschaft erhalten und identifizieren, um zwischen verschiedenen Dingen zu unterscheiden. Daten sind Aufzeichnungen, die die Eigenschaften objektiver Dinge widerspiegeln und eine spezifische Manifestation von Informationen darstellen. Nachdem Daten verarbeitet wurden, werden sie zu Informationen; Informationen müssen digital in Daten umgewandelt werden, bevor sie gespeichert und übertragen werden können. Zur Speicherung von Datensätzen dient eine Datenbank. Daher handelt es sich bei Datensätzen um deterministische (relative) Informationen, deren Sicherheit in der Einzigartigkeit liegt. Wir kommen zum ersten Grund:

1. Datensätze müssen einzigartig sein

Die Welt besteht aus objektiver Existenz und ihren Beziehungen. Daten liegen in digitalen und modellierten Beziehungen vor. Neben dem beschreibenden Wert der Daten selbst liegt ihr Wert in ihrer Vernetzung. Um die Genauigkeit der Zuordnung zu erreichen, müssen die Daten extern miteinander verknüpft werden. Daher ist die sekundäre Rolle des Primärschlüssels auch der zweite Existenzfaktor:

2. Daten müssen in Beziehung gesetzt werden

Daten werden zur Beschreibung der objektiven Realität verwendet es ist an sich bedeutungslos. Erst wenn man sich nach subjektiven Bedürfnissen organisiert und die Menschen auf eine bestimmte Art und Weise befriedigt, um Dinge zu verstehen, kann es einen Sinn haben. Daher müssen die Daten abgerufen und organisiert werden. Dann die dritte Rolle des Primärschlüssels:

3. Der zugrunde liegende Index der Datenbank wird zum Abrufen von Daten verwendet

2. Warum der Primärschlüssel nicht zu lang sein sollte

Das Problem ist die Länge. Welche Vorteile hat es also, kürzer als länger zu sein? (Hey hey hey, Konnotation) – kurz und nimmt keinen Platz ein. Aber so wenig Speicherplatz ist im Vergleich zum gesamten Datenvolumen unbedeutend, und wir verwenden die Primärschlüsselspalte im Allgemeinen nicht sehr häufig. Dann dürfte die Geschwindigkeit die Ursache sein und hat mit den Originaldaten wenig zu tun. Daraus lässt sich natürlich schließen, dass es mit dem Index und der Indexlesung zusammenhängt. Warum wirken sich lange Primärschlüssel auf die Leistung von Indizes aus?

Das Obige ist die Indexdatenstruktur von Innodb. Auf der linken Seite befindet sich der Clustered-Index, der Datensätze nach Primärschlüssel findet. Auf der rechten Seite befindet sich der Sekundärindex, der die Spaltendaten indiziert und anhand der Spaltendaten nach dem Primärschlüssel der Daten sucht. Wenn die Daten über den Sekundärindex abgefragt werden, ist der Vorgang wie in der Abbildung dargestellt. Zuerst wird der Primärschlüssel im Sekundärindexbaum gesucht und dann wird die Datenzeile über den Primärschlüssel im Clustered-Index durchsucht. Die Blattknoten des Sekundärindex sind direkt gespeicherte Primärschlüsselwerte und keine Primärschlüsselzeiger. Wenn der Primärschlüssel zu lang ist, verringert sich daher die Anzahl der Indexdatensätze, die in einem sekundären Indexbaum gespeichert werden können. Auf diese Weise erhöht sich die Anzahl der Festplattenlesevorgänge, sodass die Leistung abnimmt .

3. Warum wird die Verwendung der automatischen Inkrementierungs-ID empfohlen?

InnoDB verwendet einen Clustered-Index, wie in der Abbildung oben gezeigt, der Datensatz selbst wird im gespeichert Hauptindex (ein B+-Baum) auf den Blattknoten. Dies erfordert, dass jeder Datensatz im selben Blattknoten (die Größe einer Speicherseite oder Festplattenseite) in der Reihenfolge des Primärschlüssels gespeichert wird. Wenn also ein neuer Datensatz eingefügt wird, fügt MySQL ihn basierend auf seinem Primärschlüssel in den entsprechenden Knoten ein . und Position, wenn die Seite den Ladefaktor erreicht (InnoDB-Standard ist 15/16), wird eine neue Seite (Knoten) geöffnet.

Wenn die Tabelle einen automatisch inkrementierenden Primärschlüssel verwendet, werden die Datensätze jedes Mal, wenn ein neuer Datensatz eingefügt wird, nacheinander an der nachfolgenden Position des aktuellen Indexknotens hinzugefügt. Wenn eine Seite voll ist, wird ein neuer hinzugefügt Die Seite wird automatisch geöffnet. Dadurch entsteht eine kompakte Indexstruktur, die annähernd sequentiell gefüllt wird. Da vorhandene Daten nicht jedes Mal verschoben werden müssen, wenn sie eingefügt werden, ist dies sehr effizient und verursacht keinen großen Aufwand für die Indexpflege, wie auf der linken Seite der folgenden Abbildung dargestellt. Andernfalls muss jeder neue Datensatz irgendwo in der Mitte der vorhandenen Indexseite eingefügt werden, da der Wert des jedes Mal eingefügten Primärschlüssels ungefähr zufällig ist, und MySQL muss die Daten verschieben, um den neuen Datensatz an der entsprechenden Position einzufügen , wie auf der rechten Seite der Abbildung unten gezeigt, verursacht dies einen gewissen Mehraufwand. Aus diesem Grund muss MySQL möglicherweise häufig den Puffer aktualisieren, um den Index aufrechtzuerhalten, wodurch sich die Anzahl der E/As der Methodenfestplatte erhöht und die Indexstruktur häufig neu organisiert werden muss.

4. Geschäftsschlüssel vs. logischer Schlüssel

Geschäftsschlüssel, d. h. Verwendung einer ID mit geschäftlicher Bedeutung als Schlüssel, z. B. Verwendung der Seriennummer der Bestellung als Primärschlüssel Schlüssel der Auftragstabelle. Der logische Schlüssel, also der Schlüssel, der nichts mit dem Geschäft zu tun hat, generiert den Schlüssel nach bestimmten Regeln, z. B. einem automatisch inkrementierenden Schlüssel.

Vorteile von Business Key

Der Schlüssel hat geschäftliche Bedeutung und kann bei Abfragen direkt als Suchschlüsselwort verwendet werden

Es sind keine zusätzlichen Spalten und Indizes erforderlich Leerzeichen

kann einige Join-Vorgänge reduzieren.

Nachteile des Geschäftsschlüssels

Wenn sich das Geschäft ändert, ist es manchmal notwendig, den Primärschlüssel zu ändern

Bei mehreren ist die Bedienung schwieriger Schlüsselspalten sind beteiligt

Geschäftsschlüssel sind oft länger und nehmen mehr Platz ein, was zu größeren Festplatten-IOs führt

Daten können nicht beibehalten werden, bevor der Schlüssel ermittelt wurde. Manchmal möchten wir, wenn wir den Datenschlüssel nicht ermittelt haben, zuerst einen Datensatz hinzufügen und dann den Geschäftsschlüssel aktualisieren.

Entwerfen Sie eine Schlüsselgenerierung, die beides einfach ist zu verwenden und leistungsfähig Die Lösung ist schwieriger

Vorteile des logischen Schlüssels

Die Schlüssellogik muss aufgrund von Geschäftsänderungen nicht geändert werden

Einfach Bedienung und einfache Verwaltung

Logische Schlüssel sind tendenziell kleiner und haben eine bessere Leistung

Logische Schlüssel sind einfacher, um Eindeutigkeit sicherzustellen

Einfacher zu optimieren

Nachteile logischer Schlüssel

Das Abfragen von Primärschlüsselspalten und Primärschlüsselindizes erfordert zusätzlichen Speicherplatz

Zusätzliche E/A ist beim Einfügen und Aktualisieren von Daten erforderlich

Mehr Verknüpfungen möglich

Wenn es keine Eindeutigkeitsrichtlinieneinschränkungen gibt, ist es wahrscheinlich, dass doppelte Schlüssel auftauchen

Die Testumgebung und der offizielle Umgebungsschlüssel sind inkonsistent, was der Fehlerbehebung nicht förderlich ist

Die Der Wert des Schlüssels ist nicht mit den Daten verknüpft und entspricht nicht den drei Paradigmen

kann nicht zur Suche nach Schlüsselwörtern verwendet werden

Hängt von der spezifischen Implementierung verschiedener Datenbanksysteme ab, was nicht der Fall ist förderlich für den Ersatz der zugrunde liegenden Datenbank

5. Primärschlüsselgenerierung

Unter normalen Umständen verwenden wir alle die automatische Inkrementierungs-ID von MySQL als Primärschlüssel der Tabelle Es ist so einfach und nach dem, was oben gesagt wurde, ist auch die Leistung die beste. Bei Unterdatenbanken und Untertabellen können automatisch inkrementierende IDs die Anforderungen jedoch nicht erfüllen. Wir können einen Blick darauf werfen, wie verschiedene Datenbanken IDs generieren, und uns auch einige Lösungen zur verteilten ID-Generierung ansehen. Für uns ist es hilfreich, über einen eigenen verteilten ID-Generierungsdienst nachzudenken und ihn sogar zu implementieren.

Datenbankimplementierung

Automatische Mysql-Inkrementierung

Mysql verwaltet einen Auto-Inkrement-Zähler im Speicher und auf Auto wird zugegriffen Jedes Mal, wenn der Zähler erhöht wird, fügt InnoDB eine Sperre mit dem Namen AUTO-INC bis zum Ende der Anweisung hinzu (beachten Sie, dass die Sperre nur bis zum Ende der Anweisung und nicht bis zum Ende der Transaktion aufrechterhalten wird). Die AUTO-INC-Sperre ist eine spezielle Sperre auf Tabellenebene, die dazu dient, die gleichzeitige Einfügbarkeit von Spalten zu verbessern, die auto_increment enthalten.

In einer verteilten Situation können Sie tatsächlich einen separaten Dienst und eine separate Datenbank zum Generieren von IDs verwenden und sich dennoch auf die Funktion zur automatischen Inkrementierung von Tabellen-IDs von MySQL verlassen, um IDs für Dienste von Drittanbietern einheitlich zu generieren. Aus Performancegründen können für verschiedene Unternehmen unterschiedliche Tabellen verwendet werden.

Mongodb ObjectId

Um Primärschlüsselkonflikte zu verhindern, entwirft Mongodb eine ObjectId als Primärschlüssel-ID. Es besteht aus einer 12-Byte-Hexadezimalzahl mit folgenden Teilen:

Zeit: Zeitstempel. 4 Bytes. Sekunden.

Maschine: Maschinenidentifikation. 3 Bytes. Im Allgemeinen handelt es sich um den Hash-Wert des Maschinen-Hostnamens. Dadurch wird sichergestellt, dass verschiedene Hosts unterschiedliche Maschinen-Hash-Werte generieren, wodurch sichergestellt wird, dass es bei der Verteilung nicht zu Konflikten kommt und dass dieselbe Maschine denselben Wert hat.

PID: Prozess-ID. 2 Bytes. Die obige Maschine soll sicherstellen, dass die auf verschiedenen Maschinen generierte Objekt-ID nicht in Konflikt gerät, und die PID soll sicherstellen, dass die von verschiedenen Mongodb-Prozessen auf derselben Maschine generierte Objekt-ID nicht in Konflikt gerät.

INC: selbsterhöhender Zähler. 3 Bytes. Die ersten neun Bytes stellen sicher, dass von verschiedenen Prozessen auf verschiedenen Maschinen innerhalb einer Sekunde generierte Objekt-IDs nicht in Konflikt geraten. Der selbsterhöhende Zähler wird verwendet, um sicherzustellen, dass innerhalb derselben Sekunde generierte Objekt-IDs nicht in Konflikt geraten bis 16777216 Einträge.

Cassandra TimeUUID

Cassandra verwendet die folgenden Regeln, um eine eindeutige ID zu generieren: Zeit + MAC + Sequenz

Schema

Zookeeper seit Erhöhung: realisiert durch den Selbstinkrementierungsmechanismus von zk.

Auto-Inkrementierung von Redis: realisiert durch den Auto-Inkrement-Mechanismus von Redis.

UUID: UUID-Zeichenfolge als Schlüssel verwenden.

Schneeflockenalgorithmus: ähnlich der Mongodb-Implementierung, 1 Vorzeichenbit + 41-Bit-Zeitstempel (Millisekundenebene) + 10-Bit-Datenmaschinenbits + 12-Bit-Sequenz innerhalb von Millisekunden.

Open-Source-Implementierung

Baidu UidGenerator: basierend auf dem Snowflake-Algorithmus.

Meituan Leaf: Es implementiert auch Mechanismen, die auf der automatischen Inkrementierung (Optimierung) von MySQL und dem Schneeflockenalgorithmus basieren.

Das obige ist der detaillierte Inhalt vonSo entwerfen Sie einen Primärschlüssel in MySQL. Für weitere Informationen folgen Sie bitte anderen verwandten Artikeln auf der PHP chinesischen Website!