Warum ist der MySQL-Autoinkrement-Primärschlüssel nicht kontinuierlich?

1. Einleitung

Der Grund für diese Frage ist, dass ich bei der Arbeit festgestellt habe, dass die ID der Benutzertabelle in MySQL standardmäßig automatisch erhöht wird, die in der Datenbank gespeicherten Ergebnisse jedoch nicht kontinuierlich sind.

Benutzertabellenstruktur:

CREATE TABLE `Benutzer` ( 
	`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT 'ID erhöhen', 
	`Name` varchar(20),
	`create_time` datetime DEFAULT CURRENT_TIMESTAMP COMMENT 'Erstellungszeit', 
	`update_time` datetime DEFAULT CURRENT_TIMESTAMP COMMENT 'Aktualisierungszeitpunkt', 
	PRIMÄRSCHLÜSSEL (`id`), EINZIGARTIGER SCHLÜSSEL `idx_name` (`name`)) 
ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COMMENT='Benutzertabelle'

In der Benutzertabelle wird Folgendes gespeichert:

2. Beschreibung des selbstinkrementierenden Speichers

1.1 Der Auto-Inkrement-Wert der MyISAM-Engine wird in der Datendatei gespeichert.

1.2 Der Selbstinkrementwert der InnoDB-Engine wird tatsächlich im Speicher gespeichert. Erst mit MySQL 8.0 wurde die Fähigkeit der „Selbstinkrementpersistenz“ erreicht, d. h. „wenn ein Neustart erfolgt, kann der Selbstinkrementwert der Tabelle auf den Wert vor dem Neustart von MySQL zurückgesetzt werden“. Die konkrete Situation ist:

• In MySQL 5.7 und früheren Versionen werden Auto-Increment-Werte im Speicher abgelegt. Nach jedem Neustart wird beim ersten Öffnen der Tabelle der Maximalwert des Auto-Inkrement-Werts max(id) ermittelt und anschließend max(id) + 1 als aktueller Auto-Inkrement-Wert der Tabelle verwendet.
• 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.

Drei Mechanismen zur Änderung des Selbstwerts

Wenn in MySQL die Feld-ID als AUTO_INCREMENT definiert ist, verhält sich die automatische Inkrementierung beim Einfügen einer Datenzeile wie folgt:

• Wenn das ID-Feld als 0 oder null angegeben ist oder beim Einfügen von Daten kein Wert angegeben wird, wird der aktuelle AUTO_INCREMENT-Wert der Tabelle in das Auto-Increment-Feld eingetragen.
• 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.

Das Ergebnis der Änderung des Auto-Inkrement-Werts variiert je nach Verhältnis zwischen dem einzufügenden Wert und dem aktuellen Auto-Inkrement-Wert. Angenommen, der einzufügende Wert ist X und der aktuelle Auto-Inkrement-Wert ist Y.

• Wenn X<Y, bleibt der Auto-Inkrement-Wert der Tabelle unverändert;
• Wenn X≥Y, muss der aktuelle Auto-Inkrement-Wert auf den neuen Auto-Inkrement-Wert geändert werden.

Der neue Algorithmus zur Generierung des Auto-Inkrements lautet: Beginnen Sie bei auto_increment_offset und verwenden Sie auto_increment_increment als Schrittlänge. Fahren Sie mit dem Addieren fort, bis der erste Wert größer als X als neues Auto-Inkrement gefunden wird. Unter diesen sind auto_increment_offset und auto_increment_increment zwei Systemparameter, die verwendet werden, um den Anfangswert bzw. die Schrittgröße der automatischen Inkrementierung darzustellen, und der Standardwert ist 1.

4. Zeit, den selbstbewerteten Wert zu ändern

in Benutzerwerte einfügen (null, „null“);

1 Wenn das obige SQL ausgeführt wird, ruft der Executor die Schnittstelle der InnoDB-Engine auf, um eine Zeile zu schreiben. Der übergebene Wert dieser Zeile ist (0,"张三");

2 InnoDB stellt fest, dass SQL den Wert der Auto-Inkrement-ID nicht angibt, und erhält den aktuellen Auto-Inkrement-Wert 2 der Benutzertabelle.

3 Ändern Sie den Wert der eingehenden Zeile in (2,"张三");

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

5 Fahren Sie mit der Dateneingabe fort.

5. Gründe für diskontinuierliche Selbsterhöhung

5.1 Eindeutiger Schlüsselkonflikt

Angenommen, wenn SQL ausgeführt wird, ist die Benutzertabellen-ID = 10 und die Autoinkrement-ID im Speicher ist 11. Es tritt ein eindeutiger Schlüsselkonflikt auf und der Schreibvorgang in die Datenbank schlägt fehl. Die Benutzertabelle hat keinen Datensatz mit der ID = 10. Danach werden die IDs beginnend bei 11 geschrieben, sodass die IDs diskontinuierlich sind.

5.2 Transaktions-Rollback

Angenommen, die Benutzer- und Personaltabellen müssen gleichzeitig in die Datenbank geschrieben werden. Wenn SQL ausgeführt wird, ist die Benutzertabellen-ID = 10 und die Autoinkrement-ID im Speicher ist 11; die Personaltabellen-ID = 20 und die Autoinkrement-ID im Speicher ist 21. Sobald die Transaktion fehlschlägt, wird die Transaktion zurückgesetzt und der Schreibvorgang schlägt fehl. Die Benutzertabelle hat den Datensatz mit der ID = 10 nicht und die Personaltabelle hat den Datensatz mit der ID = 20 nicht. Die Benutzertabelle beginnt mit dem Schreiben bei 11 und die Personaltabelle beginnt mit dem Schreiben bei 21, was zu diskontinuierlichen IDs führt.

5.3 Stapelschreibvorgang

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 1 aufgebraucht ist, gilt diese Anweisung zum zweiten Mal für die Auto-Increment-ID und 2 wird zugewiesen.

3. Nachdem die beiden aufgebraucht sind, wird dieselbe Anweisung verwendet, um die dritte Selbstinkrement-ID zu beantragen, und 4 wird zugewiesen.

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

Angenommen, vier Datensätze werden stapelweise in die Benutzertabelle geschrieben, dann werden diese vier Datensätze in drei Anwendungs-IDs aufgeteilt.

Beim ersten Mal wird ihr die ID = 1 zugewiesen, beim zweiten Mal die ID = 2, 3 und beim dritten Mal die ID = 4, 5, 6, 7. Nachdem vier Datensätze stapelweise geschrieben wurden, werden die ID = 1, 2, 3, 4 gespeichert, aber die ID = 5, 6, 7 wird verworfen und die nächste ID beginnt bei 8.

6. Referenzdokumente

https://time.geekbang.org/column/intro/139

Dies ist das Ende dieses Artikels darüber, warum MySQL-Autoinkrement-Primärschlüssel nicht kontinuierlich sind. Weitere relevante Inhalte zu MySQL-Autoinkrement-Primärschlüsseln 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!