Ausführliche Diskussion über die automatische Inkrementierung von Primärschlüsseln in MySQL

Der Primärschlüssel von MySQL kann automatisch inkrementiert werden. Wenn also der neu hinzugefügte Wert nach einem Stromausfall und Neustart den automatisch inkrementierten Wert vor dem Stromausfall fortsetzt? Der Standardwert der automatischen Inkrementierung ist 1. Kann er geändert werden? Lassen Sie uns über die automatische Inkrementierung von MySQL sprechen.

Merkmale

Erhaltungsstrategie

1. Wenn die Speicher-Engine MyISAM ist, wird der Auto-Increment-Wert in der Datendatei gespeichert.

2. Wenn es sich um eine InnoDB-Engine handelt, 1) vor 5.6, wird sie im Speicher gespeichert und nicht beibehalten. Nach dem Neustart wird der größte Schlüsselwert gefunden. Wenn beispielsweise die größte ID in der aktuellen Datenzeile einer Tabelle 10 ist, ist AUTO_INCREMENT = 11. Zu diesem Zeitpunkt löschen wir die Zeile mit der ID=10 und AUTO_INCREMENT ist immer noch 11. Wenn Sie die Instanz jedoch sofort neu starten, beträgt der AUTO_INCREMENT dieser Tabelle nach dem Neustart 10.

2) Ab 8.0 wird der Auto-Increment-Wert im Redo-Log gespeichert. Nach dem Neustart wird der zuvor gespeicherte Auto-Increment-Wert aus dem Redo-Log gelesen.

Bestimmung des Selbstinkrements

1. Wenn das ID-Feld beim Einfügen von Daten als 0, null oder nicht angegeben angegeben ist, wird der aktuelle AUTO_INCREMENT-Wert der Tabelle in das Auto-Increment-Feld eingetragen und auto_increment_offset wird als Anfangswert und auto_increment_increment wird als Schrittweite verwendet, um den ersten Wert, der größer als der aktuelle Auto-Increment-Wert ist, als neuen Auto-Increment-Wert zu finden.

2. Wenn das ID-Feld der eingefügten Daten einen bestimmten Wert angibt, verwenden Sie den Wert direkt in der Anweisung.

In manchen Szenarien werden nicht alle Standardwerte verwendet. Wenn beispielsweise duales Schreiben in der Dual-M-Master-Slave-Struktur erforderlich ist, können wir auto_increment_increment=2 festlegen, sodass die Auto-Increment-IDs der einen Datenbank alle ungerade Zahlen und die Auto-Increment-IDs der anderen Datenbank alle gerade Zahlen sind, um Konflikte in den von den beiden Datenbanken generierten Primärschlüsseln zu vermeiden.

Änderung des Auto-Inkrement-Wertes

Angenommen, der einzugebende Wert ist X und der aktuelle Auto-Inkrement-Wert ist Y. Also:

1. Wenn X<Y, bleibt der Auto-Increment-Wert dieser Tabelle unverändert;

2. Wenn X ≥ Y, dann ändern Sie den aktuellen Auto-Inkrement-Wert in den neuen Auto-Inkrement-Wert.

Ausführungsprozess

Angenommen, es gibt eine Tabelle t, id ist ein automatisch inkrementierter Primärschlüssel, und wenn (1,1,1) bereits vorhanden ist, fügen Sie eine Zeile (null,1,1) ein. Dann ist der Ausführungsprozess wie folgt:

1. Der Executor ruft die InnoDB-Engine-Schnittstelle auf, um eine Zeile zu schreiben. Der Wert der übergebenen Zeile ist (0,1,1);

2. InnoDB stellt fest, dass der Benutzer den Wert der Auto-Inkrement-ID nicht angegeben hat, und erhält den aktuellen Auto-Inkrement-Wert 2 der Tabelle t.

3. Ändern Sie den Wert der eingehenden Zeile in (2,1,1);

4. Ändern Sie den Auto-Inkrement-Wert der Tabelle auf 3;

5. Fahren Sie mit dem Einfügen der Daten fort. Da bereits ein Datensatz mit c=1 vorhanden ist, wird ein Fehler wegen doppeltem Schlüssel gemeldet und die Anweisung wird zurückgegeben.

Die Probleme

Aufgrund der oben genannten Eigenschaften können Primärschlüssel in einigen Szenarien diskontinuierlich sein.

Szenario 1: Eindeutiger Index wird beim Hinzufügen von Daten wiederholt

Nachdem der Index der Spalte c wiederholt wurde, wird der ursprünglich zugewiesene Primärschlüsselwert 2 verworfen und die nächste Einfügung beginnt bei 2, was zu 3 wird.

Szenario 2: Transaktions-Rollback

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

Der ihm nach dem Zurücksetzen der zweiten Anweisung zugewiesene Primärschlüssel 2 wird ebenfalls verworfen.

Szenario 3: Spezielle Batch-Insert-Optimierung verursacht

Der hier erwähnte spezielle Batch-Eintrag bezieht sich auf die Anweisungen Einfügen, Auswählen, Ersetzen, Auswählen und Daten laden. Wozu könnten diese Aussagen führen? Es handelt sich hier um eine Selbstinkrementsperre. Zunächst dient die Autoinkrementsperre dazu, Konflikte zwischen mehreren Threads zu vermeiden. Denn wenn beim Multithreading mehrere Threads gleichzeitig den Autoinkrementwert erhalten, kann dies dazu führen, dass mehreren Datensätzen derselbe Autoinkrementwert zugewiesen wird, was zu allmählichen Konflikten führt. Daher ist eine Selbstinkrementsperre erforderlich. Die Gründe, warum die oben genannten Batch-Insert-Anweisungen zu Diskontinuitäten des Primärschlüssels führen, werden im folgenden Abschnitt zur Selbstinkrementsperre erläutert.

Frage: Bevor wir über Auto-Inkrement-Sperren sprechen, denken wir zunächst über eine Frage nach: Warum sind die Auto-Inkrement-Primärschlüsselwerte für die ersten beiden Szenarien nicht so eingestellt, dass sie rollierbar sind? Würde dies nicht Diskontinuitäten vermeiden?

Antwort: Weil das Design rückgängig gemacht werden kann, was zu Leistungseinbußen führt. Siehe das folgende Szenario.

1. Angenommen, Transaktion A gilt für ID=2 und Transaktion B gilt für ID=3. Dann ist der Auto-Increment-Wert der Tabelle t 4 und die Ausführung wird fortgesetzt.

2. Transaktion B wird korrekt übermittelt, aber bei Transaktion A tritt ein eindeutiger Schlüsselkonflikt auf.

3. Wenn Transaktion A ein Rollback der Auto-Increment-ID erlaubt wird, d. h. den aktuellen Auto-Increment-Wert der Tabelle t zurück auf 2 ändert, tritt die folgende Situation ein: In der Tabelle gibt es bereits eine Zeile mit der ID = 3 und der aktuelle Auto-Increment-ID-Wert ist 2.

4. Als Nächstes gelten andere weiterhin ausgeführte Transaktionen für die ID=2 und dann für die ID=3. Zu diesem Zeitpunkt meldet die Insert-Anweisung den Fehler „Primärschlüsselkonflikt“.

Um das obige Problem zu lösen, müssen Sie eine der folgenden beiden Methoden auswählen.

Methode 1: Bevor Sie jedes Mal eine ID beantragen, stellen Sie zunächst fest, ob die ID bereits in der Tabelle vorhanden ist. Falls vorhanden, überspringen Sie diese ID. Allerdings ist diese Methode sehr kostenintensiv. Denn die Beantragung einer ID ist eine sehr schnelle Operation, nun müssen wir aber in den Primärschlüssel-Indexbaum gehen, um zu ermitteln, ob die ID existiert.

Methode 2: Erweitern Sie den Sperrbereich der Auto-Increment-ID. Die nächste Transaktion kann erst dann für die Auto-Increment-ID gelten, wenn eine Transaktion abgeschlossen und festgeschrieben wurde. Das Problem bei dieser Methode besteht darin, dass die Sperrgranularität zu groß ist, was die Parallelitätsfähigkeit des Systems erheblich reduziert.

So können wir beispielsweise die Funktion des Rollbacks selbstbewerteter Werte insgesamt abbrechen.

Auto-Inkrement-Sperre

Der Zweck der Auto-Inkrement-Sperre besteht darin, zu verhindern, dass mehrere Threads in einer Multithread-Umgebung denselben Primärschlüsselwert erhalten, was zu Primärschlüsselkonflikten führen kann.

Sperrstrategie

Version 5.0: Der Gültigkeitsbereich ist die Anweisung und wird erst freigegeben, wenn die Anweisung ausgeführt wird.

Ab 5.1.22: Ein innodb_autoinc_lock_mode-Parameter wird eingeführt und je nach unterschiedlichen Parameterwerten werden unterschiedliche Strategien implementiert. Der Standardwert ist 1.

1. Wenn der Parameter gleich 0 ist, bedeutet dies, dass die vorherige Strategie übernommen wird, dh die Anweisung wird nach der Ausführung freigegeben.

2. Wenn der Parameter gleich 1 ist, wird bei einer allgemeinen Insert-Anweisung die Auto-Increment-Sperre unmittelbar nach der Anwendung aufgehoben.

Bei Anweisungen, die Daten stapelweise einfügen, wie etwa „insert...select“, werden die Daten erst freigegeben, wenn die Anweisung ausgeführt wird. Der Sperrbereich ist der Bereich und die Lücke, die bei der Auswahl berücksichtigt werden.

3. Wenn der Parameter gleich 3 ist, wird bei allen Aktionen, die für automatisch inkrementierte Primärschlüssel gelten, die Sperre nach der Anwendung aufgehoben.

Frage: Warum verwenden Batchoperationen wie „insert...select“ standardmäßig Sperren auf Anweisungsebene? Warum ist der Standardparameter nicht 2?

Antwort: Weil Anweisungen wie „insert...select“, die Daten stapelweise einfügen, zu Inkonsistenzen zwischen dem Master und dem Slave führen können.

Nachdem Sitzung B „create table t2 like t“ ausgeführt hat, führen Sitzung A und Sitzung B t2 gleichzeitig aus. Wenn keine Sperre vorhanden ist, kann der Ausführungsvorgang wie folgt sein.

Sitzung B fügt zunächst zwei Datensätze ein, (1,1,1) und (2,2,2). Dann beantragt Sitzung A eine Auto-Increment-ID, erhält id=3 und fügt (3,5,5) ein. Danach wird Sitzung B weiter ausgeführt und fügt zwei Datensätze ein, (4,3,3) und (5,4,4).

Obwohl dies kein Problem zu sein scheint, kann die Ausführungsreihenfolge des Slaves inkonsistent mit der des Masters sein, wenn es sich in einem Cluster befindet und der Master auf diese Weise ausführt und angibt, dass das Binärprotokoll im Anweisungsformat vorliegt. Dies führt letztendlich zu Inkonsistenzen zwischen dem Master und dem Slave. Daher ist beim Stapeleinfügen eine Sperre erforderlich. Wenn der Wert auf 2 gesetzt ist und das Binärprotokoll keine Zeilen enthält, kommt es zu Inkonsistenzen zwischen den Master- und Slave-Daten.

Um Datenkonsistenz und Systemparallelität sicherzustellen, gibt es daher zwei Lösungen:

Lösung 1: Setzen Sie das Binlog-Format auf Anweisung und innodb_autoinc_lock_mode auf 1.

Lösung 2: Stellen Sie das Binärlog-Format auf „Zeile“ und „innodb_autoinc_lock_mode“ auf „2“ ein. Um die hohe Verfügbarkeit von MySQL sicherzustellen, setzen wir Binlog im Allgemeinen auf Zeile, daher wählen wir normalerweise die zweite Option.

Batch-Einfügungsoptimierung

Beim Einfügen in Stapeln wissen wir nicht, wie viele Anweisungen auf einmal eingefügt werden. Wenn es zig Millionen oder sogar hundert Millionen Datensätze sind, muss jeder Einfügung ein Autoinkrementwert zugewiesen werden, was sehr ineffizient wäre. Daher optimiert MySQL Stapeloperationen:

1. Wenn Sie während der Anweisungsausführung zum ersten Mal eine Auto-Increment-ID beantragen, wird 1 zugewiesen.

2. Nachdem 1 aufgebraucht ist, gilt diese Anweisung zum zweiten Mal für die Auto-Increment-ID und 2 wird zugewiesen.

3. Nachdem die 2 aufgebraucht sind, wird dieselbe Anweisung verwendet, um die dritte selbstinkrementierende ID zu beantragen, und 4 wird zugewiesen.

4. Wenn dieselbe Anweisung zum Beantragen einer Auto-Increment-ID verwendet wird, ist die Anzahl der jedes Mal angewendeten Auto-Increment-IDs doppelt so hoch wie die Anzahl der vorherigen.

Führen Sie beispielsweise den folgenden Code aus

in t-Werte einfügen (null, 1,1);
in t-Werte einfügen (null, 2,2);
in t-Werte einfügen (null, 3,3);
in t-Werte einfügen (null, 4,4);
Erstelle Tabelle t2 wie t;
in t2(c,d) einfügen, c,d aus t auswählen;
in t2 Werte (null, 5,5) einfügen;

„insert…select“ fügt tatsächlich 4 Datenzeilen in Tabelle t2 ein. Für diese vier Datenzeilen wurde jedoch dreimal die automatische Inkrementierung der IDs angewendet. Beim ersten Mal wurde ihnen die ID=1 zugewiesen, beim zweiten Mal die ID=2 und ID=3 und beim dritten Mal die ID=4 bis ID=7. Da diese Anweisung tatsächlich nur 4 IDs verwendet, sind ID=5 bis ID=7 verschwendet. Führen Sie danach „insert into t2 values(null, 5,5)“ aus und die tatsächlich eingefügten Daten sind (8,5,5). Dies ist der dritte zuvor erwähnte Fall, in dem der Primärschlüssel diskontinuierlich ist.

„Einfügen...auswählen“ vor und nach derselben Tabelle verwendet eine temporäre Tabelle

Angenommen, es gibt eine Tabellenstruktur

TABELLE ERSTELLEN `t` (
 `id` int(11) NICHT NULL AUTO_INCREMENT,
 `c` int(11) DEFAULT NULL,
 `d` int(11) DEFAULT NULL,
 Primärschlüssel (`id`),
 EINZIGARTIGER SCHLÜSSEL `c` (`c`)
)ENGINE=InnoDB;

in t-Werte einfügen (null, 1,1);
in t-Werte einfügen (null, 2,2);
in t-Werte einfügen (null, 3,3);
in t-Werte einfügen (null, 4,4);

Tabelle t2 wie t erstellen

Wenn die ausgeführte Anweisung lautet:

einfügen in t2(c,d) (wähle c+1, d aus t, erzwinge Index(c), sortiere nach c, Abstiegsgrenze 1);

Wenn wir das langsame Protokoll abfragen, finden wir

Die Anzahl der gescannten Zeilen beträgt 1, was bedeutet, dass der Datensatz über den Index direkt auf t gefunden und dann in die Tabelle t2 eingefügt wird.

Wenn Sie diese Anweisung ändern in

in t(c,d) einfügen (c+1, d aus t auswählen, Index(c) erzwingen, nach c sortieren, Abstiegsgrenze 1);

Wenn Sie zu diesem Zeitpunkt das langsame Protokoll überprüfen, werden Sie feststellen, dass es 5 geworden ist. Warum ist das so? Auch wenn alle markiert sind, sind es nur 4. Zu diesem Zeitpunkt überprüfen wir die Änderungen in der Anzahl der Scanzeilen.

Es wird festgestellt, dass die Änderung davor und danach 4 Zeilen beträgt. Daher wird festgestellt, dass eine temporäre Tabelle verwendet wird. Anschließend kann der Vorgang wie folgt bestimmt werden:

1. Erstellen Sie eine temporäre Tabelle mit zwei Feldern c und d.

2. Scannen Sie die Tabelle t gemäß dem Index c, nehmen Sie nacheinander c = 4, 3, 2, 1, kehren Sie dann zur Tabelle zurück, lesen Sie die Werte von c und d und schreiben Sie sie in die temporäre Tabelle. Zu diesem Zeitpunkt ist Rows_examined=4.

3. Da es in der Semantik die Grenze 1 gibt, wird nur die erste Zeile der temporären Tabelle genommen und dann in die Tabelle t eingefügt. Zu diesem Zeitpunkt erhöht sich der Wert von Rows_examined um 1 und wird 5.

Der Grund, warum eine temporäre Tabelle erforderlich ist, besteht darin, zu verhindern, dass beim Lesen der gerade eingefügte Wert gelesen wird.

Optimierung

Da die Anzahl der von „Select“ zurückgegebenen Datensätze gering ist, kann zur Optimierung eine temporäre Speichertabelle verwendet werden.

Erstellen Sie eine temporäre Tabelle temp_t (c int, d int) engine=memory.
in temp_t einfügen (wähle c+1, d aus t, erzwinge Index(c), Sortierung nach c, Abstiegsgrenze 1);
in t einfügen, auswählen * aus temp_t;
Tabelle temp_t löschen;

Die Gesamtzahl der gescannten Zeilen beträgt nur 1 aus der Auswahl plus 1 aus der temporären Tabelle.

endlich

Bei eindeutigen Indexkonflikten können Sie insert into ... on duplicate key update verwenden, um eine Aktualisierungsverarbeitung nach dem Konflikt durchzuführen. Angenommen, es gibt zwei Datensätze (1,1,1) und (2,2,2) in Tabelle t, dann führen Sie Folgendes aus:

Wenn beim Einfügen ein Konflikt festgestellt wird, wird der widersprüchliche Datensatz geändert.

Zusammenfassen

Dies ist das Ende dieses Artikels über die automatische Inkrementierung von Primärschlüsseln in MySQL. Weitere Informationen zu automatischen Inkrementierung von Primärschlüsseln in MySQL 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!