Eine ausführliche Analyse, wie das Problem gelöst werden kann, dass MySQL nicht mehr über automatisch inkrementierte IDs verfügt

Dieser Artikel bringt Ihnen relevantes Wissen über MySQL, das hauptsächlich die damit verbundenen Probleme der selbsterhöhenden ID vorstellt. Da es eine Obergrenze gibt, wird es immer eine Zeit geben, in der sie aufgebraucht ist . Wenn die ID nicht mehr verwendet wird, werfen wir einen Blick darauf. Ich hoffe, dass sie für alle hilfreich ist.

Empfohlenes Lernen: MySQL-Video-Tutorial

Ich habe kürzlich eine Interviewfrage wie diese gesehen. MySQL hat keine automatisch inkrementierten IDs mehr. Was soll ich tun? Hier sind die Lösungen zu dieser Interviewfrage.

Wenn Sie MySQL verwendet oder verstanden haben, müssen Sie sich mit der automatischen Inkrementierung von Primärschlüsseln auskennen. Für jede automatisch inkrementierende ID ist ein Anfangswert definiert, der dann entsprechend der angegebenen Schrittgröße erhöht wird (die Standardschrittgröße ist 1). Obwohl es für natürliche Zahlen keine Obergrenze gibt, geben wir beim Entwerfen der Tabellenstruktur normalerweise die Feldlänge an, sodass die ID zu diesem Zeitpunkt eine Obergrenze hat. Da es eine Obergrenze gibt, wird es immer einen Zeitpunkt geben, an dem die ID aufgebraucht ist. Lasst uns heute gemeinsam lernen.

Auto-Inkrement-ID

Apropos Auto-Inkrement-ID: Ich glaube, Ihre erste Reaktion muss darin bestehen, beim Entwerfen der Tabellenstruktur ein Problem mit einem eindeutigen Primärschlüssel zu haben Beim Einfügen von Daten führen SQL-Transaktions-Rollback, Batch-Einfügung, Auto-Inkrementierungswert der Batch-Anwendung und andere Gründe dazu, dass die Auto-Inkrementierungs-ID diskontinuierlich ist.

Die Logik des in der Tabelle definierten, sich selbst erhöhenden Werts nach dem Onlinegehen lautet: Bei der Beantragung der nächsten ID wird der gleiche Wert (Maximalwert) erhalten. Sie können SQL einfügen, um die ID auf den Maximalwert festzulegen, und dann eine Anweisung einfügen, die die ID nicht aktiv festlegt, um diese Schlussfolgerung zu überprüfen. Wenn Sie zu diesem Zeitpunkt erneut einfügen, wird ein Primärschlüsselkonflikt gemeldet ~

Hier eine Erinnerung: 232-1 (4294967295) ist keine besonders große Zahl. Für eine Tabelle, in der häufig Daten eingefügt und gelöscht werden, kann sie verwendet werden hoch. Daher müssen Sie beim Erstellen einer Tabelle prüfen, ob Ihre Tabelle diese Obergrenze wahrscheinlich erreichen wird. Wenn möglich, sollten Sie sie als 8-Byte-Bigint ohne Vorzeichen erstellen.

Das InnoDB-System erhöht die row_id automatisch.

Wenn die von Ihnen erstellte InnoDB-Tabelle keinen Primärschlüssel angibt, erstellt InnoDB für Sie eine unsichtbare row_id mit einer Länge von 6 Bytes. InnoDB verwaltet einen globalen dict_sys.row_id-Wert. Für alle InnoDB-Tabellen ohne Primärschlüssel wird jedes Mal, wenn eine Datenzeile eingefügt wird, der aktuelle dict_sys.row_id-Wert als row_id der einzufügenden Daten und dann der Wert von verwendet dict_sys.row_id wird um 1 erhöht.

Tatsächlich ist row_id bei der Implementierung des Codes eine vorzeichenlose lange Ganzzahl (bigint unsigned) mit einer Länge von 8 Bytes. Beim Entwurf von InnoDB blieben jedoch nur 6 Bytes für row_id übrig. Auf diese Weise wurden beim Schreiben in die Datentabelle nur die letzten 6 Bytes abgelegt. Daher gibt es zwei row_id-Werte, die in die Datentabelle geschrieben werden können . Funktionen:

row_id Der in der Tabelle angegebene Wertebereich reicht von 0 bis 248-1; und hol es dir. Wenn du später die letzten 6 Bytes nimmst, ist es 0.

Obwohl die Zahl 2^48 bereits sehr groß ist, sollte jeder wissen, dass ein System lange laufen kann und daher möglicherweise immer noch die Obergrenze erreicht. Zu diesem Zeitpunkt wird bei einer erneuten Anwendung der ursprüngliche Datensatz überschrieben. Versuchen Sie daher, diese Option nicht zu wählen! Xid

Wenn Redo-Log und Binlog in MySQL kombiniert werden, haben sie ein gemeinsames Feld namens Xid. Es wird verwendet, um Transaktionen in MySQL zu entsprechen.

MySQL verwaltet intern eine globale Variable global_query_id. Jedes Mal, wenn eine Anweisung ausgeführt wird, wird sie Query_id zugewiesen und dann wird diese Variable um 1 erhöht. Wenn die aktuelle Anweisung die erste von dieser Transaktion ausgeführte Anweisung ist, weist MySQL auch Query_id der Xid dieser Transaktion zu. Global_query_id ist eine reine Speichervariable und wird nach dem Neustart gelöscht. Daher kann in derselben Datenbankinstanz die XID verschiedener Transaktionen gleich sein.

Innodb trx_id

InnoDB verwaltet intern eine globale Variable max_trx_id. Jedes Mal, wenn eine neue trx_id beantragt werden muss, wird der aktuelle Wert von max_trx_id abgerufen und dann max_trx_id um 1 erhöht.

Die Kernidee der InnoDB-Datensichtbarkeit ist: Jede Datenzeile zeichnet die trx_id auf, die sie aktualisiert. Wenn eine Transaktion eine Datenzeile liest, können Sie mithilfe der Konsistenzansicht feststellen, ob die Daten sichtbar sind Transaktion und Zeile Vergleichen Sie die trx_id der Daten. Bei diesem Vorgang kommt es jedoch zu einem fehlerhaften Lesen, sodass die ID nicht atomar ist und die Möglichkeit einer Duplizierung besteht.

thread_id

Tatsächlich ist die Thread-ID die häufigste selbsterhöhende ID in MySQL. Wenn wir verschiedene Szenen überprüfen, ist die erste Spalte in der Show-Prozessliste normalerweise thread_id.

Die Logik von thread_id ist leicht zu verstehen: Das System speichert eine globale Variable thread_id_counter. Jedes Mal, wenn eine neue Verbindung erstellt wird, wird thread_id_counter der Thread-Variablen dieser neuen Verbindung zugewiesen.

thread_id_counter hat eine definierte Größe von 4 Bytes. Sobald er also 232-1 erreicht, wird er auf 0 zurückgesetzt und erhöht sich weiter. Das Ergebnis ist dasselbe wie row_id und der ursprüngliche Datensatz wird überschrieben.

Das Obige stellt einige der automatisch inkrementierenden IDs von MySQL vor. Tatsächlich können wir in tatsächlichen Anwendungen auch externe automatisch inkrementierende Primärschlüssel auswählen und diese dann in der Datenbank beibehalten, um die eigenen automatisch inkrementierenden IDs der Datenbank zu ersetzen. Lassen Sie uns weiter unten darüber sprechen.

Redis selbsterhöhender Primärschlüssel

Tatsächlich gibt es viele Möglichkeiten, externe selbsterhöhende Primärschlüssel zu generieren. Warum sollte ich Redis einführen? Weil ich in der praktischen Anwendung viele Vorteile davon festgestellt habe.

Redis selbst ist atomar, daher ist eine hohe Parallelität auch threadsicher. Unter der Annahme, dass die Feldlänge des Primärschlüssels 20 beträgt, verwenden wir Zeit + Nummer für die automatische Inkrementierung, um den Primärschlüssel zu bilden, zum Beispiel: 8-stelliges Datum + 12 Nummer für die automatische Inkrementierung. Je nach Art des Geschäfts kann die Zeit dann auf der Ebene des Jahres, des Monats, des Tages oder der Millisekunde bestimmt werden. Dann ist die Wahrscheinlichkeit, dass die Anzahl der automatischen Inkremente zwischen Millisekunden wiederholt wird, äußerst gering und kann auf sie angewendet werden Grundgeschäfte.

Zusammenfassung

Oben werden mehrere Arten von Auto-Inkrement-IDs vorgestellt. Jede Auto-Inkrement-ID hat ihre eigenen Anwendungsszenarien, und ihre Leistung nach Erreichen der Obergrenze ist ebenfalls unterschiedlich:

1, Nach der Auto-Inkrementierung Die ID der Tabelle erreicht die Obergrenze. Ihr Wert ändert sich nicht, wenn Sie sie erneut anwenden. Dies führt zu dem Fehler eines Primärschlüsselkonflikts, wenn Sie weiterhin Daten einfügen.
2, Nachdem die Zeilen-ID die Obergrenze erreicht hat kehrt auf 0 zurück und erhöht sich dann erneut. Wenn dieselbe row_id angezeigt wird, werden die später geschriebenen Daten verwendet. Um die vorherigen Daten zu überschreiben
3, muss Xid nur keine doppelten Werte in derselben Binlog-Datei haben. Obwohl theoretisch doppelte Werte auftreten, ist die Wahrscheinlichkeit äußerst gering und kann ignoriert werden
4 Der Inkrementwert max_trx_id wird bei jedem Neustart von MySQL gespeichert, daher ist das in unserem Artikel erwähnte Dirty-Read-Beispiel ein Muss Fehler, zum Glück haben wir noch viel Zeit
5, thread_id ist die am häufigsten verwendete, und es ist auch die am besten zu handhabende Logik für die automatische Inkrementierung der ID
6, Redis externe automatische Inkrementierung, Millisekundenebene, Theoretisch wird es doppelte Werte geben, aber die Wahrscheinlichkeit ist äußerst gering und kann ignoriert werden
7, Tatsächlich hat jede sich selbst erhöhende ID ihre eigenen anwendbaren Szenarien, und Sie können entsprechend den spezifischen Szenarien im täglichen Gebrauch auswählen. Seien Sie jedoch auf einen regnerischen Tag vorbereitet, da die Laufzeit des Systems und die Speicherung von Daten umfassend berücksichtigt werden müssen. Wählen Sie einen, der sich während des Betriebs des Systems nicht sofort wiederholt. Hast du es gelernt?

Empfohlenes Lernen: MySQL-Video-Tutorial

Das obige ist der detaillierte Inhalt vonEine ausführliche Analyse, wie das Problem gelöst werden kann, dass MySQL nicht mehr über automatisch inkrementierte IDs verfügt. Für weitere Informationen folgen Sie bitte anderen verwandten Artikeln auf der PHP chinesischen Website!