Detaillierte Erläuterung der Implementierung des MySQL-Autoinkrements des Primärschlüssels

1. Wo wird der selbstinkrementierte Wert gespeichert?

Verschiedene Engines haben unterschiedliche Strategien zum Speichern von Auto-Increment-Werten.

1. Der Selbstinkrementwert der MyISAM-Engine wird in der Datendatei gespeichert

2. Der Selbstinkrementwert der InnoDB-Engine wird im Speicher gespeichert und ist in MySQL 5.7 und früheren Versionen nicht persistent. Nach jedem Neustart wird beim ersten Öffnen der Tabelle der Maximalwert des Auto-Inkrement-Werts max(id) ermittelt und dann max(id) + Schrittweite als aktueller Auto-Inkrement-Wert der Tabelle verwendet.

Wählen Sie max(ai_col) aus Tabellenname zur Aktualisierung;

In MySQL 8.0 werden die Änderungen des Auto-Increment-Wertes im Redo-Log aufgezeichnet. Beim Neustart wird das Redo-Log verwendet, um den Wert vor dem Neustart wiederherzustellen.

2. Mechanismus zur Änderung des Selbstwerts

Wenn das ID-Feld als AUTO_INCREMENT definiert ist, verhält sich der Auto-Inkrement-Wert beim Einfügen einer Datenzeile wie folgt:

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

2. Wenn beim Einfügen von Daten ein bestimmter Wert für das ID-Feld angegeben wird, wird der in der Anweisung angegebene Wert direkt verwendet

Angenommen, der einzufügende Wert ist X und der aktuelle Auto-Increment-Wert ist Y

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

2. Wenn X>=Y, müssen Sie den aktuellen Auto-Increment-Wert auf den neuen Auto-Increment-Wert ändern

Der neue Algorithmus zur Generierung von Auto-Inkrementen lautet: Beginnen Sie mit auto_increment_offset (Anfangswert) und verwenden Sie auto_increment_increment (Schrittlänge) als Schrittlänge. Fügen Sie so lange hinzu, bis der erste Wert größer als X als neuer Auto-Inkrement-Wert gefunden wird.

3. Wann muss der Auto-Inkrement-Wert geändert werden?

Erstellen Sie eine Tabelle t, wobei id das automatisch inkrementierte Primärschlüsselfeld und c der eindeutige Index ist. Die Anweisung zur Tabellenerstellung lautet wie folgt:

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;

Angenommen, die Tabelle t enthält bereits den Datensatz (1,1,1). Führen Sie nun einen weiteren Befehl aus, um Daten einzufügen:

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

Der Ausführungsablauf ist wie folgt:

1. Der Executor ruft die Schnittstelle der InnoDB-Engine 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 angibt, und erhält den aktuellen Auto-Inkrement-Wert der Tabelle t 2

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

4. Ändern Sie den Auto-Increment-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 gibt zurück

Das entsprechende Ausführungsflussdiagramm sieht wie folgt aus:

Wenn danach eine neue Datenzeile eingefügt wird, beträgt die erhaltene Auto-Inkrement-ID 3. Der Auto-Increment-Primärschlüssel ist diskontinuierlich

Eindeutige Schlüsselkonflikte und Transaktions-Rollbacks können zu Diskontinuitäten in der automatisch inkrementierten Primärschlüssel-ID führen.

4. Optimierung der Selbstinkrementsperre

Die Auto-Increment-ID-Sperre ist keine Transaktionssperre, sondern wird direkt nach jeder Anwendung aufgehoben, um das erneute Anwenden weiterer Transaktionen zu ermöglichen.

In MySQL Version 5.0 beschränkt sich der Gültigkeitsbereich der Auto-Inkrement-Sperre jedoch auf Anweisungsebene. Das heißt, wenn eine Anweisung eine Auto-Inkrement-Sperre für eine Tabelle anwendet, wird die Sperre erst aufgehoben, wenn die Anweisung ausgeführt wird.

MySQL Version 5.1.22 führt eine neue Strategie ein, einen neuen Parameter innodb_autoinc_lock_mode, der Standardwert ist 1

1. Wenn Sie diesen Parameter auf 0 setzen, bedeutet dies, dass die Strategie der vorherigen MySQL 5.0-Version übernommen wird, d. h. die Sperre wird erst nach Ausführung der Anweisung aufgehoben.

2. Dieser Parameter ist auf 1 gesetzt

• Bei gewöhnlichen Einfügeanweisungen wird die Auto-Inkrement-Sperre unmittelbar nach ihrer Anwendung aufgehoben.
• Bei Anweisungen, die Daten stapelweise einfügen, wie etwa „insert ... select“, wird die Auto-Inkrement-Sperre dennoch aufgehoben, nachdem die Anweisung abgeschlossen ist.

3. Wenn dieser Parameter auf 2 gesetzt ist, wird bei allen Aktionen, die für Auto-Inkrement-Primärschlüssel gelten, die Sperre nach der Anwendung aufgehoben.

Aus Gründen der Datenkonsistenz ist die Standardeinstellung 1

Wenn Sitzung B die Auto-Inkrement-Sperre unmittelbar nach der Beantragung des Auto-Inkrements freigibt, kann die folgende Situation eintreten:

• SessionB fügt zunächst zwei Datenzeilen (1,1,1) und (2,2,2) ein.
• SessionA beantragt eine Auto-Increment-ID, erhält id=3 und fügt (3,5,5) ein.
• Danach wird die Ausführung von Sitzung B fortgesetzt und es werden zwei Datensätze (4,3,3) und (5,4,4) eingefügt.

Wenn binlog_format=statement ist, führen die beiden Sitzungen den Befehl zum Einfügen von Daten gleichzeitig aus, sodass es für das Aktualisierungsprotokoll der Tabelle t2 im Binlog nur zwei Situationen gibt: Entweder wird SitzungA zuerst aufgezeichnet oder SitzungB wird zuerst aufgezeichnet. Unabhängig von der Methode wird dieses Binärprotokoll zur Ausführung in die Slave-Datenbank übertragen oder zum Wiederherstellen einer temporären Instanz verwendet. In der Slave-Datenbank und der temporären Instanz wird die Anweisung sessionB ausgeführt und die IDs in den generierten Ergebnissen sind fortlaufend. Zu diesem Zeitpunkt treten in dieser Bibliothek Dateninkonsistenzen auf

Ideen zur Lösung dieses Problems:

1) Sorgen Sie dafür, dass die Batch-Einfügeanweisungen der Originaldatenbank kontinuierliche ID-Werte generieren. Daher wird die Selbstinkrementsperre erst freigegeben, wenn die Anweisung ausgeführt wird, um diesen Zweck zu erreichen.

2) Alle Vorgänge zum Einfügen von Daten werden wahrheitsgetreu im Binärprotokoll aufgezeichnet. Wenn die Standby-Datenbank ausgeführt wird, ist sie nicht mehr auf den automatisch inkrementierten Primärschlüssel angewiesen, um Daten zu generieren. Das heißt, setzen Sie innodb_autoinc_lock_mode auf 2 und binlog_format auf Zeile

Wenn Daten stapelweise eingefügt werden (Einfügen...Auswählen, Ersetzen...Auswählen und Laden von Daten), wird aus Sicht der Leistung beim gleichzeitigen Einfügen von Daten empfohlen, innodb_autoinc_lock_mode auf 2 und binlog_format auf Zeile zu setzen. Dadurch kann Parallelität erreicht werden, ohne dass Probleme mit der Datenkonsistenz auftreten.

Für Anweisungen, die Daten in Stapeln einfügen, verfügt MySQL über eine Strategie zum Beantragen von Auto-Increment-IDs in Stapeln:

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

2. Nachdem die erste aufgebraucht ist, wird diese Anweisung, sofern sie für die zweite Auto-Increment-ID gilt, zwei weitere IDs zuordnen.

3. Nachdem die 2 aufgebraucht sind, wird dieselbe Anweisung erneut verwendet. Beim dritten Mal, wenn die Auto-Increment-ID angefordert wird, werden 4 zugewiesen

4. Analog dazu wird dieselbe Anweisung verwendet, um eine Auto-Increment-ID zu beantragen. Die Anzahl der Auto-Increment-IDs, die jedes Mal beantragt werden, ist doppelt so hoch wie die vorherige.

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;

Einfügen ... Auswählen fügt tatsächlich 4 Datenzeilen in Tabelle t2 ein. Diese vier Datenzeilen wurden jedoch dreimal für Auto-Increment-IDs angewendet. Beim ersten Mal wurden sie für ID=1 angewendet, beim zweiten Mal wurden ihnen ID=2 und ID=3 zugewiesen und beim dritten Mal wurden ihnen ID=4 bis ID=7 zugewiesen.

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. Die tatsächlich eingefügten Daten sind (8,5,5).

Dies ist der dritte Grund, warum die Primärschlüssel-ID nicht kontinuierlich ist.

5. Der Auto-Increment-Primärschlüssel ist aufgebraucht

Wenn Sie eine weitere Datensatzzeile einfügen, nachdem das automatisch inkrementierte Primärschlüsselfeld die Obergrenze des definierten Typs erreicht hat, wird ein Primärschlüsselkonfliktfehler gemeldet.

Nehmen wir als Beispiel eine vorzeichenlose Ganzzahl (4 Bytes, die Obergrenze ist 2 32 − 1 2^{32}-1 232−1) und überprüfen sie mit der folgenden Anweisungsfolge:

TABELLE ERSTELLEN t (id INT UNSIGNED auto_increment PRIMARY KEY) auto_increment = 4294967295;
IN t-WERTE EINFÜGEN (NULL);
IN t-WERTE EINFÜGEN (NULL);

Nachdem die erste Insert-Anweisung erfolgreich Daten eingefügt hat, ändert sich das AUTO_INCREMENT dieser Tabelle nicht (es ist immer noch 4294967295), was dazu führt, dass die zweite Insert-Anweisung denselben Auto-Increment-ID-Wert erhält. Wenn die Insert-Anweisung erneut versucht wird, wird ein Primärschlüsselkonfliktfehler gemeldet.

Empfohlene Materialien :

https://time.geekbang.org/column/article/80531

Dies ist das Ende dieses Artikels mit der detaillierten Erklärung der Implementierung des MySQL-Autoinkrements des Primärschlüssels. Weitere relevante Inhalte zum MySQL-Autoinkrement des Primärschlüssels finden Sie in früheren Artikeln auf 123WORDPRESS.COM oder in den folgenden verwandten Artikeln. Ich hoffe, dass jeder 123WORDPRESS.COM in Zukunft unterstützen wird!