Fehlerbehebung und Lösungen für das Problem der MySQL-Autoinkrement-ID-Übergröße

Einführung

Xiao A war gerade dabei, Code zu schreiben, und plötzlich schickte DBA Xiao B eine Nachricht: „Beeil dich und sieh dir deine benutzerspezifische Informationstabelle T an. Der Primärschlüssel, also die Auto-Inkrement-ID, hat 1,6 Milliarden erreicht. Das ist erst seit kurzer Zeit so. Wenn es so weitergeht, ist es bald außerhalb des Bereichs und das Einfügen schlägt fehl, balabala …“

Ich kann mich nicht erinnern, dass es so viel war, wahrscheinlich höchstens über 1.000. Ich habe es gezählt und es waren 11 Millionen. Es stellt sich heraus, dass der Betrieb und die Wartung den auto_increment-Wert betrachten, d. h. es gibt eine große Anzahl von Lösch- und Einfügevorgängen in der Tabelle, aber ich aktualisiere die meiste Zeit. Warum passiert das?

Werfen wir einen Blick auf die ausführliche Einführung.

Fehlerbehebung

Diese Tabelle ist ein einfacher verwendeter Schnittstellendienst. Jeden Tag werden durch Big Data große Mengen an Informationen gesammelt und dann an Xiao A weitergeleitet. Xiao A aktualisiert die Informationen in der Datenbank. Wenn es sich um neue Daten handelt, werden sie eingefügt. Wenn es sich um alte Daten handelt, werden die vorherigen Daten aktualisiert. Die einzige externe Schnittstelle ist die Abfrage.

Bald überprüfte Xiao A seinen eigenen Code. Es gab weder eine Löschung noch eine aktive Einfügung oder Aktualisierung der ID. Wie konnte das passieren? Könnte es Xiao Bs Schuld sein? Das ist unwahrscheinlich. Der DBA verwaltet viele Tabellen. Wenn es ein Problem gäbe, wäre es schon vor langer Zeit aufgedeckt worden, aber ich habe keine Ahnung, was das Problem ist.

Xiao A beobachtete die über 10 Millionen vorhandenen Daten sorgfältig und betrachtete dabei Einfügezeit und ID als wichtigste Beobachtungsfelder. Bald entdeckte er ein Problem. Die ersten Daten, die jeden Tag eingefügt wurden, waren immer mehr als 10 Millionen höher als am Vortag. Manchmal war der Anstieg größer und manchmal kleiner. Xiao A zeigte mit dem Finger auf DBA Xiao B und beschrieb Xiao B das Problem erneut.

Little B fragte Little A: „Hast du die Anweisung REPLACE INTO ... verwendet?“ Was ist los? Es stellte sich heraus, dass REPLACE INTO ... den Primärschlüssel beeinflusst.

Auswirkung von REPLACE INTO ... auf Primärschlüssel

Angenommen, es gibt eine Tabelle t1:

TABELLE ERSTELLEN `t1` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT 'ID, Auto-Inkrement',
`uid` bigint(20) unsigned NOT NULL DEFAULT '0' COMMENT 'Benutzer-UID',
`name` varchar(20) NOT NULL DEFAULT '' COMMENT 'Benutzer-Spitzname',
Primärschlüssel (`id`),
EINZIGARTIGER SCHLÜSSEL `u_idx_uid` (`uid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='Testen Sie das Ersetzen in';

Wenn wir diese Tabelle erstellen und die folgende Anweisung ausführen, wie sieht der endgültige Datensatz aus?

in t1-Werte einfügen (NULL, 100, "test1"), (NULL, 101, "test2");
Ersetzen durch t1-Werte (NULL, 100, "test3"); 

Es stellt sich heraus, dass bei jeder Verwendung von REPLACE INTO ..., wenn eine Einfügung vorgenommen wird, die ursprünglichen Daten gelöscht werden, wenn die dem eindeutigen Index entsprechenden Daten bereits vorhanden sind, und die neuen Daten anschließend erneut eingefügt werden, was zu einer Erhöhung der ID führt, die eigentliche Erwartung jedoch möglicherweise darin besteht, die Daten zu aktualisieren.

Xiao A sagte: „Ich weiß, dass Ersetzen so ist, also habe ich es nicht verwendet.“ Aber er überprüfte es noch einmal und stellte fest, dass es nicht sein Problem war. Er hat REPLACE INTO nicht verwendet …

Xiao A prüfte es noch einmal sorgfältig, konnte aber immer noch kein Problem finden. Deshalb bat er Xiao B, das Binlog-Protokoll zu prüfen, um festzustellen, ob etwas Ungewöhnliches auftrat. Nach der Prüfung wurde kein Problem gefunden. Es gab zwar einen Sprung, aber kein wesentliches Problem.

In der folgenden Abbildung entspricht der Wert von @1 der automatisch inkrementierten Primärschlüssel-ID und (@2, @3) wird als eindeutiger Index verwendet.

Nach langer Zeit wies Xiao B Xiao A in eine Richtung. Xiao A begann, an seiner Insert-Update-Anweisung INSERT ... ON DUPLICATE KEY UPDATE ... zu zweifeln. Nach langer Prüfung stellte er fest, dass hier tatsächlich ein Problem vorlag.

Auswirkung von INSERT ... ON DUPLICATE KEY UPDATE ... auf Primärschlüssel

Diese Anweisung ähnelt REPLACE INTO ..., ändert aber nicht den Primärschlüssel des Datensatzes. Es handelt sich immer noch um die obige Tabelle t1. Was ist das Ergebnis der Ausführung der folgenden Anweisung?

bei doppeltem Schlüssel in t1-Werte (NULL, 100, „test4“) einfügen, Name aktualisieren = Werte (Name); 

Ja, genau wie Xiao A erwartet hatte, wurde der Primärschlüssel nicht hinzugefügt und das Namensfeld wurde auf den gewünschten Wert aktualisiert, aber es gab eine Eingabeaufforderung im Ausführungsergebnis, die die Aufmerksamkeit von Xiao A erregte.

Keine Fehler; 2 Zeilen betroffen, dauert 10,7 ms

Offensichtlich wurde ein Datenelement aktualisiert. Warum beträgt die Anzahl der betroffenen Datensätze dann 2? Xiao A schaute sich das auto_increment in der aktuellen Tabelle noch einmal an

TABELLE ERSTELLEN `t1` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT 'ID, Auto-Inkrement',
`uid` bigint(20) unsigned NOT NULL DEFAULT '0' COMMENT 'Benutzer-UID',
`name` varchar(20) NOT NULL DEFAULT '' COMMENT 'Benutzer-Spitzname',
Primärschlüssel (`id`),
EINZIGARTIGER SCHLÜSSEL `u_idx_uid` (`uid`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8 COMMENT='Testen Sie das Ersetzen in';

Eigentlich sind es 5’, es sollten 4 sein.

Mit anderen Worten, die obige Anweisung erhöht die Auto-Increment-ID um 1, ähnlich wie REPLACE INTO ..., aber der eigentliche Datensatz wird nicht hinzugefügt. Warum ist das so?

Nach Überprüfung der Informationen fand Xiao A heraus, dass es einen Parameter innodb_autoinc_lock_mode für die automatische Inkrementierung des MySQL-Primärschlüssels gibt. Es gibt drei Möglichkeiten: 0, 1 und 2. Er wurde nach MySQL 5.1 hinzugefügt. Der Standardwert ist 1. Frühere Versionen können als 0 betrachtet werden.

Mit der folgenden Anweisung können Sie sehen, welcher Modus aktuell

wählen Sie @@innodb_autoinc_lock_mode;

Der Standardwert der von Xiao A verwendeten Datenbank ist ebenfalls 1. Bei einem einfachen Einfügen (die Anzahl der eingefügten Zeilen kann bestimmt werden) wird auto_increment direkt um 1 erhöht, ohne die Tabelle zu sperren, was die Leistung verbessert. Beim Einfügen komplexer Anweisungen wie insert into select ... ist die Anzahl der einzufügenden Zeilen im Voraus nicht bekannt. In diesem Fall muss die Tabelle gesperrt werden (eine spezielle Tabellensperre namens AUTO_INC), damit auto_increment genau ist und die Sperre erst freigegeben wird, wenn die Anweisung endet. Es gibt auch einen Einfügungstyp, der als Mixed-Mode-Einfügung bezeichnet wird, z. B. INSERT INTO t1 (c1,c2) ​​​​VALUES (1,'a'), (NULL,'b'), (5,'c'), (NULL,'d'), bei dem einige der Einfügungen den automatisch inkrementierten Primärschlüsselwert explizit angeben und andere nicht. Das INSERT ... ON DUPLICATE KEY UPDATE ..., das wir hier besprechen, gehört ebenfalls zu diesem Typ. Zu diesem Zeitpunkt wird die Anweisung analysiert und dann wird auto_incrementid entsprechend so vielen Situationen wie möglich zugewiesen. Wie ist das zu verstehen? Lassen Sie mich das folgende Beispiel betrachten:

Tabelle t1 abschneiden;
in t1-Werte einfügen (NULL, 100, "test1"), (NULL, 101, "test2"), (NULL, 102, "test2"), (NULL, 103, "test2"), (NULL, 104, "test2"), (NULL, 105, "test2");

-- Zu diesem Zeitpunkt ist die nächste Auto-Increment-ID in der Datentabelle 7

Löschen Sie aus t1, wobei die ID in (2,3,4) ist.

-- Zu diesem Zeitpunkt sind nur noch 1, 5 und 6 in der Datentabelle übrig und die Auto-Increment-ID ist immer noch 7

in t1 einfügen: Werte (2, 106, "test1"), (NULL, 107, "test2"), (3, 108, "test2");

-- Was ist hier die Auto-Inkrement-ID?

Nachdem das obige Beispiel ausgeführt wurde, ist die nächste Auto-Inkrement-ID der Tabelle 10. Verstehen Sie das richtig? Da die letzte ausgeführte Anweisung eine Mixed-Mode-Insert-Anweisung ist, analysiert InnoDB die Anweisung und weist dann drei IDs zu. Zu diesem Zeitpunkt ist die nächste ID 10, aber nicht alle drei zugewiesenen IDs werden verwendet. @Always late danke für den Hinweis, dass ich die offizielle Dokumentation missverstanden habe

Modus 0 bedeutet, dass die Tabellensperre unabhängig von der Situation hinzugefügt und bei Ausführung der Anweisung freigegeben wird. Wenn tatsächlich ein Datensatz hinzugefügt wird, wird auto_increment um 1 erhöht.

Was Modus 2 betrifft, wird die AUTO_INC-Sperre unter keinen Umständen hinzugefügt, was ein Sicherheitsproblem darstellt. Wenn das Binlog-Format auf den Anweisungsmodus eingestellt ist, kann das Ausführungsergebnis beim Synchronisieren der Slave-Datenbank inkonsistent mit der Master-Datenbank sein, was ein großes Problem darstellt. Denn es kann zu einer komplexen Einfügung kommen und während der Ausführung kommt noch eine weitere Einfügung. Beim Wiederherstellen wird sie einzeln ausgeführt, und dieses Parallelitätsproblem kann nicht reproduziert werden, was dazu führen kann, dass die Datensatz-ID nicht übereinstimmt.

An diesem Punkt wurde das Problem des ID-Springens analysiert. Da der Wert von innodb_autoinc_lock_mode 1 ist, ist INSERT ... ON DUPLICATE KEY UPDATE ... eine einfache Anweisung, und die Anzahl der betroffenen Zeilen kann im Voraus berechnet werden. Daher wird auto_increment unabhängig davon, ob aktualisiert wird oder nicht, um 1 erhöht (größer als 1, wenn mehrere Zeilen vorhanden sind).

Wenn Sie den Wert von innodb_autoinc_lock_mode auf 0 ändern und INSERT ... ON DUPLICATE KEY UPDATE ... erneut ausführen, werden Sie feststellen, dass auto_increment nicht zunimmt, da dieser Modus die AUTO_INC-Sperre direkt hinzufügt und sie freigibt, wenn die Anweisung ausgeführt wird. Wenn festgestellt wird, dass die Anzahl der Zeilen nicht zugenommen hat, wird die Auto-Increment-ID nicht erhöht.

INSERT ... ON DUPLICATE KEY UPDATE ... gibt 2 zurück, wenn die Anzahl der betroffenen Zeilen 1 ist?

Warum passiert das? Logischerweise ist die Anzahl der betroffenen Zeilen 1. Weitere Einzelheiten finden Sie in der offiziellen Dokumentation.

Bei ON DUPLICATE KEY UPDATE beträgt der Wert für die betroffenen Zeilen 1, wenn die Zeile als neue Zeile eingefügt wird, 2, wenn eine vorhandene Zeile aktualisiert wird, und 0, wenn eine vorhandene Zeile auf ihre aktuellen Werte gesetzt wird.

In der offiziellen Erklärung heißt es eindeutig, dass das Einfügen 1 Zeile betrifft und das Aktualisieren 2 Zeilen. Wenn der Wert 0 ist, bedeutet dies, dass der Wert vorhanden ist und der Wert vor und nach dem Aktualisieren derselbe ist. Ist es schwer zu verstehen?

Eigentlich sollten Sie so denken. Dies dient zur Unterscheidung, ob es eingefügt oder aktualisiert wird. Die Rückgabe von 1 bedeutet erfolgreiches Einfügen und 2 bedeutet erfolgreiches Aktualisieren.

Lösung

Das Setzen von innodb_autoinc_lock_mode auf 0 kann das Problem definitiv lösen, aber in diesem Fall kann die Parallelität der Einfügung stark beeinträchtigt werden, sodass Xiao A glaubt, dass der DBA nicht zustimmen wird. Nach Abwägung werden derzeit zwei mögliche Lösungen vorbereitet:

Ändern der Geschäftslogik

Ändern Sie die Geschäftslogik und trennen Sie die Anweisungen INSERT ... ON DUPLICATE KEY UPDATE .... Erst abfragen und dann aktualisieren. Dadurch wird sichergestellt, dass der Primärschlüssel nicht unkontrolliert ansteigt, die Komplexität jedoch zunimmt. Aus der ursprünglichen einen Anfrage können zwei Anfragen werden: erst abfragen und dann aktualisieren.

Löschen Sie den Auto-Increment-Primärschlüssel der Tabelle

Löschen Sie den Autoinkrement-Primärschlüssel und verwenden Sie den eindeutigen Index als Primärschlüssel. Auf diese Weise sind grundsätzlich keine Änderungen erforderlich. Stellen Sie einfach sicher, dass der aktuelle Autoinkrement-Primärschlüssel keinen praktischen Nutzen hat. In diesem Fall kann die Effizienz beim Einfügen und Löschen beeinträchtigt werden. In Situationen mit vielen Abfragen zieht Xiao A jedoch nach einem Vergleich der beiden letzteren vor.

Abschluss

Tatsächlich beträgt die Anzahl der von INSERT ... ON DUPLICATE KEY UPDATE ... betroffenen Zeilen 2. Xiao A hat dies sehr früh entdeckt, aber seine Neugierde nicht bewahrt und es nicht ernst genommen. Er hat sich nicht eingehend mit dem Problem befasst. Wenn er es getan hätte, wären viele neue Erkenntnisse ans Licht gekommen. Es scheint, dass Xiao A seine Neugier und Sensibilität gegenüber der Außenwelt bewahren sollte, damit er Fortschritte machen kann.

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. Wenn Sie Fragen haben, können Sie eine Nachricht hinterlassen. Vielen Dank für Ihre Unterstützung von 123WORDPRESS.COM.