Was tun, wenn der Auto-Increment-Primärschlüssel in MySQL aufgebraucht ist?

Im Vorstellungsgespräch sollten Sie folgende Szenarien erlebt haben

Interviewer: „Sie haben MySQL schon einmal verwendet, richtig? Verwenden Sie automatisch inkrementierte Primärschlüssel oder UUIDs?“

Sie: „Ich habe einen automatisch inkrementierenden Primärschlüssel verwendet“

Interviewer: „Warum ist es ein automatisch inkrementierter Primärschlüssel?“

Sie: „Da der automatisch inkrementierte Primärschlüssel verwendet wird, werden die Daten sequenziell in der physischen Struktur gespeichert und die Leistung ist am besten, blabla …“

Interviewer: „Was passiert, wenn der Auto-Increment-Primärschlüssel den Maximalwert erreicht und aufgebraucht ist?“

Sie: „Was? Ich habe keine Bewertung abgegeben!!“ (Dann können Sie zurückgehen und auf die Benachrichtigung warten!)

Diese Frage wurde mir von einem Fan gestellt und ich finde意(KENG)思(B)!
Heute sprechen wir also darüber, was zu tun ist, wenn dieser automatisch inkrementierte Primärschlüssel aufgebraucht ist!

Text

Einfache Version

Lassen Sie uns zunächst verstehen, dass in MySQL der Bereich der Int-Ganzzahlen wie folgt ist

Nehmen wir als Beispiel eine vorzeichenlose Ganzzahl. Der Speicherbereich reicht von 0 bis 4294967295, also etwa 4,3 Milliarden! Nehmen wir zunächst an, dass, sobald die Auto-Inkrement-ID den Maximalwert erreicht, bei fortgesetzter Dateneinfügung eine Ausnahme wegen eines Primärschlüsselkonflikts wie folgt gemeldet wird

//Duplikateintrag „4294967295“ für Schlüssel „PRIMARY“

Die Lösung ist auch sehr einfach: Ändern Sie den Int-Typ in den BigInt-Typ. Der Bereich von BigInt ist wie folgt

Selbst wenn Sie 10.000 Datenelemente pro Sekunde haben und es 100 Jahre lang ausführen, werden die Daten in einer einzelnen Tabelle nur
10000*24*3600*365*100=31536000000000
Diese Zahl liegt noch weit von der Obergrenze von BigInt entfernt. Wenn Sie also die Auto-Inkrement-ID auf den Typ BigInt setzen, müssen Sie sich keine Sorgen machen, dass die Auto-Inkrement-ID den Maximalwert erreicht!
Wenn Ihre Antwort im Interview jedoch lautet:

Sie: „Das ist ganz einfach. Ändern Sie einfach den Typ des Auto-Inkrement-Primärschlüssels in BigInt!“

Als Nächstes kann der Interviewer Ihnen eine schwierigere Frage stellen!

Interviewer: „Wie ändern Sie den Datentyp einer Spalte online?“

Sie: „Was! Ich warte einfach auf die Benachrichtigung!“

So ändern Sie

Derzeit gibt es in der Branche drei Lösungen für die Online-Änderung der Tabellenstruktur. Soweit ich weiß, gibt es im Allgemeinen die folgenden drei

Methode 1: Verwenden Sie die Online-Änderungsfunktion von MySQL 5.6+

Die sogenannten von MySQL selbst bereitgestellten Funktionen sind MySQLs eigene native Anweisungen. Wenn wir beispielsweise den ursprünglichen Feldnamen und -typ ändern möchten.

mysql> ALTER TABLE Tabellenname CHANGE alter Feldname neuer Feldname Feldtyp;

Vor MySQL 5.5 wurde dies durch das Kopieren temporärer Tabellen erreicht. Nach der Ausführung der ALTER -Anweisung wird eine temporäre Tabelle mit einer neuen Struktur erstellt, alle Daten in der Originaltabelle werden in die temporäre Tabelle kopiert und anschließend wird eine Umbenennung durchgeführt, um den Erstellungsvorgang abzuschließen. Die Originaltabelle ist dabei lesbar, jedoch nicht beschreibbar.
Ab 5.6+ unterstützt MySQL die Online-Änderung von Datenbanktabellen. Während der Tabellenänderung kann bei den meisten Vorgängen die Originaltabelle gelesen und geschrieben werden.
Kann bei Vorgängen wie dem Ändern des Datentyps einer Spalte weiterhin in die ursprüngliche Tabelle geschrieben werden? Komm schon, ich bin auf die offizielle Website gegangen, um ein Bild der MySQL 8.0-Version zu finden

Wie in der Abbildung gezeigt, werden gleichzeitige DML-Operationen für Vorgänge wie das Ändern von Datentypen nicht unterstützt! Mit anderen Worten: Wenn Sie Anweisungen wie ALTER direkt verwenden, um die Datenstruktur der Tabelle online zu ändern, können für diese Tabelle keine Aktualisierungsvorgänge ( DELETE , UPDATE , DELETE ) mehr durchgeführt werden.
Daher ist direktes ALTER nicht zulässig!

Dann können wir nur Methode 2 oder Methode 3 verwenden

Methode 2: Verwenden von Tools von Drittanbietern

Es gibt einige Tools von Drittanbietern in der Branche, die die Online-Änderung der Tabellenstruktur unterstützen können. Mit diesen Tools von Drittanbietern wird die Tabelle nicht blockiert, wenn Sie ALTER -Operationen ausführen! Es gibt noch zwei weitere berühmte

• pt-online-schema-change , kurz pt-osc
• GitHub hat offiziell die Veröffentlichung des Tools im Open-Source-Format namens gh-ost angekündigt

Am Beispiel von pt-osc lautet das Prinzip wie folgt

1. Erstellen Sie eine neue Tabelle mit der geänderten Datentabellenstruktur, die zum Importieren von Daten aus der Quelldatentabelle in die neue Tabelle verwendet wird.

2. Erstellen Sie einen Trigger, um die Vorgänge zum weiteren Ändern der Daten in der Quelldatentabelle nach dem Kopieren der Daten aufzuzeichnen. Führen Sie diese Vorgänge nach dem Kopieren der Daten aus, um sicherzustellen, dass die Daten nicht verloren gehen.

3. Kopieren Sie Daten aus der Quelldatentabelle in die neue Tabelle.

4. Benennen Sie die Quelldatentabelle in die alte Tabelle um, benennen Sie die neue Tabelle in den Namen der Quelltabelle und löschen Sie die alte Tabelle.

5. Löschen Sie den Trigger.

Allerdings sind das tatsächlich zwei意(KENG)思(B) Tools. . . Überraschenderweise. . . Also! Wenn Ihre Tabelle Trigger und Fremdschlüssel hat, funktionieren diese beiden Tools nicht!
Wenn Sie tatsächlich auf Trigger und Fremdschlüssel in der Datenbank stoßen, können Sie nur Gewalt anwenden. Bitte beachten Sie Methode 3.
Methode 3: Ändern Sie die Slave-Tabellenstruktur und wechseln Sie dann zwischen Master und Slave <br /> Diese Methode ist äußerst mühsam und erfordert professionelle Spieler für die Bedienung. Da es sich bei unserer MySQL-Architektur grundsätzlich um eine Lese-/Schreibtrennungsarchitektur handelt, wird der Slave zum Lesen verwendet. Wir ändern die Tabellenstruktur direkt auf der Slave-Datenbank, ohne den Lesevorgang der Slave-Datenbank zu blockieren. Nach der Änderung können Sie zwischen Master und Slave wechseln. Das einzige, worauf Sie achten müssen, ist, dass es bei der Master-Slave-Umschaltung zu Datenverlust kommen kann!

Erweiterte Version

Tatsächlich ist dieser Artikel nach Beantwortung der obigen Fragen fast fertig. Aber denken Sie daran, was ich am Anfang gesagt habe. Das ist eine sehr意(KENG)思(B) : Warum?
Angenommen, das Auto-Increment-Feld in Ihrer Tabelle ist vom Typ „Signed Int“, das heißt, Ihr Feldbereich reicht von -2147483648 bis 2147483648.
Alles läuft einwandfrei. Ihre Auto-Inkrement-ID beginnt bei 0, was bedeutet, dass Ihr verfügbarer Bereich jetzt 0 bis 2147483648 beträgt.
Es sollte klar sein, dass die tatsächlichen Daten-IDs in der Tabelle mit Sicherheit einige Überraschungen bereithalten und dass die IDs nicht unbedingt fortlaufend sind. Beispielsweise tritt folgende Situation ein

TABELLE ERSTELLEN `t` (
 `id` int(11) NICHT NULL AUTO_INCREMENT,
 Primärschlüssel (`id`),
) DE

Führen Sie den folgenden SQL-Befehl aus

in t-Werte einfügen (null);
// Die eingefügte Zeile ist (1)
beginnen;
in t-Werte einfügen (null);
Rolllack;
in t-Werte einfügen (null);
// Die eingefügte Zeile ist (3)

Daher wird die tatsächliche ID in der Tabelle zwangsläufig diskontinuierlich sein.
OK, jetzt reicht der Datenbereich Ihrer automatisch inkrementierten Primärschlüssel-ID von 0 bis 2147483648, was 2,1 Milliarden Datensätze in einer einzigen Tabelle bedeutet! Berücksichtigt man die Diskontinuität der IDs, liegt die tatsächliche Datenmenge wahrscheinlich bei höchstens 1,8 Milliarden.
Bruder, in einer einzigen Tabelle sind bereits 1,8 Milliarden Datensätze. Warum teilst du sie nicht auf verschiedene Datenbanken und Tabellen auf? Nachdem Sie die Datenbank und die Tabellen aufgeteilt haben, können Sie sich nicht mehr auf die Auto-Increment-ID jeder Tabelle verlassen, um die Daten global eindeutig zu identifizieren. An diesem Punkt müssen wir eine Strategie zur Generierung global eindeutiger ID-Nummern bereitstellen, um die Umgebung der Sharding-Bibliotheken und -Tabellen zu unterstützen.

Daher kann in der Praxis nie gewartet werden, bis der Auto-Increment-Primärschlüssel aufgebraucht ist! Daher lautet die professionelle Antwort wie folgt:

Interviewer: „Was passiert, wenn der Auto-Increment-Primärschlüssel den Maximalwert erreicht und aufgebraucht ist?“

Sie: „Ich bin nie auf dieses Problem gestoßen, weil wir für den automatisch inkrementierten Primärschlüssel den Typ int verwenden, der normalerweise nicht den Maximalwert erreichen kann. Deshalb teilen wir die Datenbank und die Tabelle auf, sodass ich nie auf dieses Problem gestoßen bin!“

Dies ist das Ende dieses Artikels darüber, was zu tun ist, wenn der automatisch inkrementierte Primärschlüssel in MySQL aufgebraucht ist. Weitere Informationen zum Aufbrauchen der automatisch inkrementierten MySQL-Primärschlüssel finden Sie in früheren Artikeln auf 123WORDPRESS.COM oder in den folgenden verwandten Artikeln. Ich hoffe, Sie werden 123WORDPRESS.COM auch in Zukunft unterstützen!