Verhindern, dass InnoDB automatisch die DOPPELTASTE erhöht

Ich habe derzeit Probleme mit einer Primärschlüssel- ID die auf auto increment . Es erhöht den ON DUPLICATE KEY .

Beispielsweise:

 ID | field1 | field2 1 | user | value 5 | secondUser | value 86 | thirdUser | value 

Aus der obigen Beschreibung werden Sie feststellen, dass ich 3 Eingänge in dieser Tabelle habe, aber aufgrund der automatischen Erhöhung bei jedem Update hat die ID 86 für die dritte Eingabe.

Gibt es das überhaupt, um das zu vermeiden?

So sieht meine mySQL-Abfrage aus:

 INSERT INTO table ( field1, field2 ) VALUES (:value1, :value2) ON DUPLICATE KEY UPDATE field1 = :value1, field2 = :value2 

Und so sieht mein Tisch aus;

 CREATE TABLE IF NOT EXISTS `table` ( `id` int(11) NOT NULL AUTO_INCREMENT, `field1` varchar(200) NOT NULL, `field2` varchar(255) NOT NULL, PRIMARY KEY (`id`), UNIQUE KEY `field1` (`field1`), KEY `id` (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ; 

Sie können die innodb_autoinc_lock_mode für den “traditionellen” innodb_autoinc_lock_mode auf "0" , wodurch garantiert wird, dass alle INSERT statementen fortlaufende Werte für AUTO_INCREMENT Spalten zuweisen.

Allerdings sollten Sie nicht davon ausgehen, dass die Auto-Inkrement-IDs in Ihrer Anwendung fortlaufend sind. Ihr Zweck besteht darin, eindeutige Kennungen bereitzustellen.

Ich habe derzeit Probleme mit einer Primärschlüssel- ID die auf auto increment . Es erhöht den ON DUPLICATE KEY

Einer von uns muss das Problem falsch verstehen oder es falsch darstellen. ON DUPLICATE KEY UPDATE erstellt niemals eine neue Zeile und kann daher nicht inkrementiert werden. Aus den Dokumenten :

Wenn Sie ON DUPLICATE KEY UPDATE angeben und eine Zeile eingefügt wird, die einen doppelten Wert in einem UNIQUE-Index oder PRIMARY KEY verursachen würde, führt MySQL ein UPDATE der alten Zeile aus.

Nun ist es wahrscheinlich der Fall, dass beim Einfügen automatisches Inkrementieren auftritt und kein doppelter Schlüssel gefunden wird. Wenn ich annehme, dass dies passiert, wäre meine Frage: Warum ist das ein Problem?

Wenn Sie den Wert Ihres Primärschlüssels unbedingt steuern möchten, ändern Sie Ihre Tabellenstruktur, um das auto-increment Flag zu entfernen, aber behalten Sie ein erforderliches Nicht-Null-Feld bei. Es wird Sie zwingen, die Schlüssel selbst zu geben, aber ich würde wetten, dass dies zu größeren Kopfschmerzen für Sie werden wird.

Ich bin wirklich neugierig: Warum müssen Sie alle Löcher in den ID Werten stecken?

Dieses Verhalten wird nachfolgend innodb_autoinc_lock_mode der Standardeinstellung für innodb_autoinc_lock_mode = 1 (“konsekutiver” innodb_autoinc_lock_mode ) deutlich. Bitte verweisen Sie auch auf die feine Handbuchseite AUTO_INCREMENT Handling in InnoDB . Wenn Sie diesen Wert ändern, verringert sich die concurrency und performance mit der Einstellung = 0 für den Sperrmodus “Tranditional”, da eine AUTO-INC-Sperre auf Tabellenebene verwendet wird.

Das heißt, das Folgende ist mit der Standardeinstellung = 1.

Ich möchte Ihnen vier Beispiele zeigen, wie einfach es ist, Lücken zu schaffen.

Beispiel 1:

 create table x ( id int auto_increment primary key, someOtherUniqueKey varchar(50) not null, touched int not null, unique key(someOtherUniqueKey) ); insert x(touched,someOtherUniqueKey) values (1,'dog') on duplicate key update touched=touched+1; insert x(touched,someOtherUniqueKey) values (1,'dog') on duplicate key update touched=touched+1; insert x(touched,someOtherUniqueKey) values (1,'cat') on duplicate key update touched=touched+1; select * from x; +----+--------------------+---------+ | id | someOtherUniqueKey | touched | +----+--------------------+---------+ | 1 | dog | 2 | | 3 | cat | 1 | +----+--------------------+---------+ 

Die Lücke (id = 2 wird übersprungen) ist auf eine Handvoll von Operationen und Macken und nervösen Zuckungen der INNODB Engine zurückzuführen. In seinem standardmäßigen Hochleistungsmodus der Parallelität führt er Bereichslückenzuordnungen für verschiedene Abfragen durch, die an ihn gesendet werden. Man sollte gute Gründe haben, diese Einstellung zu ändern, da sich dies auf die performance auswirkt. Die Art von Dingen, die zu einem späteren Zeitpunkt MySQL-Versionen liefern, und Sie schalten aufgrund von Hyper-Fokussierung auf Lücken in Ausdruckblättern (und Bosse, die sagen “Warum haben wir Lücken”).

Im Fall einer Insert-on-Duplicate-Schlüsselaktualisierung ( IODKU ) wird eine neue Zeile angenommen und ein Slot dafür zugewiesen. Denken Sie daran, concurrency und Gleichaltrige, die die gleichen Operationen durchführen, vielleicht Hunderte gleichzeitig. Wenn sich die IODKU in ein Update verwandelt, dann wird die verwaiste und nie eingefügte Zeile mit id = 2 für Ihre Verbindung und für alle anderen verwendet.

Beispiel 2:

Dasselbe passiert während des Insert ... Select From wie in dieser Antwort zu sehen ist. In diesem verwende ich absichtlich MyISAM aufgrund der Berichterstattung über Zählungen, min, max, sonst würde die Bereichslücken-Eigenart zuweisen und nicht alle füllen. Und die Zahlen würden komisch aussehen, da diese Antwort sich auf die tatsächlichen Zahlen bezog. So funktionierte der ältere Motor ( MyISAM ) gut für enge Lücken. Beachten Sie, dass ich in dieser Antwort versucht habe, etwas schnell und sicher zu tun, und dass diese Tabelle mit ALTER TABLE nach INNODB konvertiert werden könnte. Hätte ich dieses Beispiel zuerst in INNODB , hätte es (im Standardmodus) viele Lücken gegeben. Der Grund, warum die Insert ... Select From Lücken in der Antwort hätte, hätte ich INNODB lag an der Unsicherheit der Zählung, dem Mechanismus, den die Engine für sichere (unsichere) Bereichszuweisungen auswählt. Die INNODB Engine kennt die Operation natürlich, INNODB in muss einen sicheren Pool von AUTO_INCREMENT INNODB erstellen, hat Nebenläufigkeit (andere Benutzer zum Nachdenken), und Lücken gedeihen. Es ist eine Tatsache. Versuchen Sie Beispiel 2 mit der INNODB Engine und sehen Sie, was Sie für min, max und count bekommen. Max wird nicht gleich gezählt.

Beispiele 3 und 4:

Es gibt verschiedene Situationen, in denen INNODB Gaps auf der Percona-Website dokumentiert werden, wenn sie in weitere Dokumente stolpern und sie dokumentieren. Zum Beispiel tritt es bei fehlgeschlagenen Einfügungen aufgrund von Fremdschlüsseleinschränkungen auf, die in diesem Fehlerbild 1452 zu sehen sind . Oder ein Primärschlüsselerrors in diesem 1062 Fehlerbild .

Denken Sie daran, dass die INNODB Lücken als Nebeneffekt der Systemleistung und ein sicherer Motor sind. Ist das etwas, was man wirklich ausschalten möchte (performance, höhere Benutzerzufriedenheit, höhere Parallelität, fehlende Tabellensperren), um engere ID-Bereiche zu erreichen? Bereiche, die Löcher enthalten, werden trotzdem gelöscht. Ich würde nicht für meine Implementierungen vorschlagen, und die Standardeinstellung mit performance ist in Ordnung.