Diese Dinge wissen Sie vielleicht nicht über die automatische ID-Inkrementierung in MySQL

Einführung:

Wenn wir MySQL zum Erstellen einer Tabelle verwenden, erstellen wir normalerweise ein Auto-Inkrement-Feld (AUTO_INCREMENT) und verwenden dieses Feld als Primärschlüssel. In diesem Artikel erfahren Sie alles zum Thema Auto-Increment-ID in Form von Fragen und Antworten.

Hinweis: Dieser Artikel basiert vollständig auf der Innodb-Speicher-Engine.

Reden wir nicht zu viel, schauen wir uns gemeinsam die ausführliche Einführung an.

1. Warum empfiehlt MySQL, die Auto-Increment-Spalten-ID als Primärschlüssel festzulegen?

• Wenn wir einen Primärschlüssel (PRIMARY KEY) definieren, wählt InnoDB den Primärschlüssel als gruppierten Index aus. Wenn der Primärschlüssel nicht explizit definiert ist, wählt InnoDB den ersten eindeutigen Index, der keine NULL-Werte enthält, als Primärschlüsselindex aus. Wenn es keinen solchen eindeutigen Index gibt, wählt InnoDB die integrierte 6-Byte-ROWID als impliziten gruppierten Index aus (ROWID erhöht sich, wenn Zeilendatensätze geschrieben werden. Diese ROWID ist nicht wie die ROWID von ORACLE referenzierbar, sondern implizit).
• Die Datensätze selbst werden in den Blattknoten des Primärindex (einem B+Baum) gespeichert. Dies erfordert, dass jeder Datensatz im selben Blattknoten (in der Größe einer Speicherseite oder Datenträgerseite) in der Reihenfolge des Primärschlüssels gespeichert wird. Wenn also ein neuer Datensatz eingefügt wird, fügt MySQL ihn in den entsprechenden Knoten und an die entsprechende Position entsprechend seinem Primärschlüssel ein. Wenn die Seite den Ladefaktor erreicht (InnoDB hat standardmäßig 15/16), wird eine neue Seite (Knoten) geöffnet.
• Wenn die Tabelle einen automatisch inkrementierten Primärschlüssel verwendet, wird jedes Mal, wenn ein neuer Datensatz eingefügt wird, der Datensatz sequenziell an der nächsten Position des aktuellen Indexknotens hinzugefügt. Wenn eine Seite voll ist, wird automatisch eine neue Seite geöffnet.
• Wenn ein nicht automatisch inkrementierender Primärschlüssel verwendet wird (z. B. ID-Nummer oder Studentenausweisnummer usw.), wird jeder neue Datensatz in die Mitte der vorhandenen Indexseite eingefügt, da der Wert des Primärschlüssels nahezu zufällig ist. Zu diesem Zeitpunkt muss MySQL Daten verschieben, um den neuen Datensatz an der entsprechenden Position einzufügen. Die Zielseite wurde möglicherweise auf die Festplatte zurückgeschrieben und aus dem Cache gelöscht und muss dann von der Festplatte zurückgelesen werden. Dies verursacht einen erheblichen Mehraufwand. Gleichzeitig verursachen häufige Verschiebungs- und Paging-Vorgänge eine starke Fragmentierung, was zu einer Indexstruktur führt, die nicht kompakt genug ist. OPTIMIZE TABLE muss verwendet werden, um die Tabelle neu zu erstellen und die Füllseite zu optimieren.

Zusammenfassend: Wenn wir die Auto-Increment-Spalte als Primärschlüssel verwenden, ist die Zugriffseffizienz am höchsten.

2. Sind die automatisch inkrementierten Spalten-IDs notwendigerweise fortlaufend?

Die Erhöhung der Auto-Inkrement-ID erfolgt nicht notwendigerweise kontinuierlich.

Schauen wir uns zunächst die Speicherstrategie von MySQL für Auto-Increment-Werte an:

Der Selbstinkrementwert der InnoDB-Engine wird tatsächlich im Speicher gespeichert. Erst in MySQL 8.0 wurde die Fähigkeit der „Selbstinkrementpersistenz“ realisiert, 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 wird der Auto-Increment-Wert im Speicher gespeichert und bleibt nicht dauerhaft bestehen. 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.
Wenn beispielsweise die größte ID in der aktuellen Datenzeile einer Tabelle 10 ist, gilt 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.
Das heißt, ein MySQL-Neustart kann den AUTO_INCREMENT-Wert einer Tabelle ändern.
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.

Die folgenden Situationen können zu einer Unterbrechung der Auto-Inkrement-ID führen:

1. Eindeutiger Schlüsselkonflikt

2. Transaktions-Rollback

3. Einfügen ... Select-Anweisung zur Batch-Anwendung für die automatische ID-Inkrementierung

3. Gibt es eine Obergrenze für die Auto-Increment-ID?

Die Auto-Inkrement-ID ist ein ganzzahliges Feld. Wir verwenden häufig den int-Typ, um die Wachstums-ID zu definieren, und der int-Typ hat eine Obergrenze, was bedeutet, dass die Wachstums-ID auch eine Obergrenze hat.
In der folgenden Tabelle sind die Bereiche der Int- und Bigint-Feldtypen aufgeführt:

Aus der obigen Tabelle ist ersichtlich, dass der Maximalwert 2147483647 erreichen kann, wenn das Auto-Increment-Feld den vorzeichenbehafteten Typ int verwendet, was mehr als 2,1 Milliarden sind; wenn der vorzeichenlose Typ int verwendet wird, kann der Maximalwert 4294967295 erreichen, was mehr als 4,2 Milliarden sind. Natürlich kann Bigint einen größeren Bereich darstellen.

Als nächstes testen wir, was passiert, wenn die Auto-Increment-ID das Maximum erreicht und fügen erneut Daten ein:

Tabelle erstellen t(id int unsigned auto_increment Primärschlüssel) auto_increment=4294967295;
in t-Werte einfügen (null);
// Erfolgreich eingefügte Zeile von 4294967295
zeige, erstelle Tabelle t;
/* TABELLE ERSTELLEN `t` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
PRIMÄRSCHLÜSSEL (`id`)
)ENGINE=InnoDB AUTO_INCREMENT=4294967295;
*/

in t-Werte einfügen (null);
//Duplikateintrag „4294967295“ für Schlüssel „PRIMARY“

Aus dem Experiment können wir ersehen, dass die Auto-Increment-ID nicht erweitert werden kann, wenn sie das Maximum erreicht. Nachdem die erste Insert-Anweisung die Daten erfolgreich 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. Beim erneuten Versuch, die Insert-Anweisung auszuführen, wird ein Primärschlüsselkonfliktfehler gemeldet.

4. Wie sollten wir die Auto-Increment-Spalte pflegen?

Zur Wartung werden folgende zwei Vorschläge gemacht:

1. Feldtypauswahl: Es wird empfohlen, den vorzeichenlosen Typ int zu verwenden. Wenn vorhergesagt wird, dass die Datenmenge in der Tabelle sehr groß sein wird, kann stattdessen der vorzeichenlose Typ bigint verwendet werden.

2. Achten Sie bei großen Tabellen mehr auf den Auto-Inkrement-Wert, um einen Überlauf des Primärschlüssels zu verhindern.

Zusammenfassen

Das Obige ist der vollständige Inhalt dieses Artikels. Ich hoffe, dass der Inhalt dieses Artikels einen gewissen Lernwert für Ihr Studium oder Ihre Arbeit hat. Vielen Dank für Ihre Unterstützung von 123WORDPRESS.COM.