So implementieren Sie „insert if none“ und „update if yes“ in MySql

Zusammenfassung

In einigen Szenarien kann eine solche Anforderung bestehen: Einfügen, wenn kein Datensatz vorhanden ist, und Aktualisieren, wenn ein Datensatz vorhanden ist. Wenn Sie beispielsweise beim Hinzufügen eines neuen Benutzers die ID-Nummer als eindeutige Kennung verwenden und zunächst prüfen, ob der Datensatz vorhanden ist, bevor Sie entscheiden, ob er eingefügt oder aktualisiert werden soll, treten unter Bedingungen hoher Parallelität zwangsläufig Probleme auf. Dieser Artikel bietet drei Lösungen.

Lösung 1: Sperren

Dieses Problem kann durch die Verwendung synchronisierter Sperren, ReentranLock-Sperren oder verteilter Sperren gelöst werden. Der Nachteil besteht darin, dass durch das Sperren die Leistung beeinträchtigt wird. Bei den Methoden 2 und 3 handelt es sich beides um Lösungen auf Datenbankebene, und ich persönlich bin der Meinung, dass sie besser sind als Methode 1.

Lösung 2: Eindeutig und Ersetzen durch ... SELECT ...

Fügen Sie zunächst dem eindeutigen Feld einen eindeutigen Index hinzu: ALTER TABLE tb_name ADD UNIQUE (col1, col2...). Der eindeutige Index kann die Eindeutigkeit der Daten sicherstellen.

Nach dem Hinzufügen eines eindeutigen Indexes führt das Einfügen derselben Daten über INSERT INTO zu einem Fehler. In diesem Fall müssen Sie zum Einfügen der Daten REPLACE INTO verwenden. Die Verwendung ist dieselbe. Beim Einfügen von Daten über REPLACE INTO wird, sofern die gleichen Daten bereits vorhanden sind, der vorherige Datensatz gelöscht und die Daten erneut eingefügt. Der Nachteil besteht darin, dass zuerst gelöscht und dann eingefügt wird und SQL alle Datenspalten berücksichtigen muss, da sonst die Daten einiger Spalten verloren gehen. Der Nachteil besteht darin, dass die Erstellung eines eindeutigen Indexes die Einfügeeffizienz beeinträchtigt. Im Folgenden finden Sie konkrete Beispiele.

# Index erstellen ALTER TABLE user ADD UNIQUE (id_card);
# Angenommen, die Benutzertabelle hat nur drei Felder: ID, Name und ID-Karte, und das ID-Feld wird automatisch erhöht.
# Jetzt müssen Sie einen Datensatz mit name=ly, id_card=142733 einfügen.
# Wenn jedoch ein Datensatz mit id_card=142733 vorhanden ist, ändern Sie einfach name=ly.
Ersetzen Sie in Benutzer (ID, Name, ID-Karte) 
SELECT id,'ly',142733 FROM Benutzer RIGHT JOIN (SELECT 1) AS Registerkarte 
EIN Benutzer-ID_Karte = 142733;

Wenn durch RIGHT JOIN (SELECT 1) ein Datensatz mit id_card=142733 vorhanden ist, wird nach der Ausführung von SQL die ursprüngliche ID im temporären Ergebnissatz gespeichert und zusammen mit Name und id_card eingefügt. Wenn der Datensatz nicht existiert, wird neben Name und Ausweis „null“ als ID eingefügt. Endgültige Implementierung

Lösung 3: Verwenden Sie Pre-Insert-Anweisungen, um festzustellen, ob Datensätze vorhanden sind

Verwenden Sie die Anweisung „pre-insert“, um zu versuchen, einzufügen und zu bestimmen, ob der geänderte Datensatz größer als 0 ist. Wenn er größer als 0 ist, bedeutet dies, dass die Einfügung erfolgreich war. Wenn er 0 ist, bedeutet dies, dass der Datensatz bereits vorhanden ist und aktualisiert werden muss.

# Vorab einfügen: INSERT INTO user (name,id_card)
Wählen Sie "ly", 142733 von Dual 
WO NICHT EXISTIERT (SELECT id_card FROM user WHERE id_card = 142733);
# Wenn die Pre-Insert-Anweisung erfolgreich eingefügt wurde (Anzahl der geänderten Datensätze = 1), sind keine nachfolgenden Vorgänge erforderlich. Andernfalls führen Sie einen Aktualisierungsvorgang durch.
UPDATE Benutzer SET Name = "ly" WHERE id_card = 142733;

Wenn aufgrund der Bedingung NOT EXISTE ein Datensatz mit id_card=142733 vorhanden ist, ist der Datensatz in der Pseudotabelle DUAL leer und die Anweisung vor dem Einfügen ändert den Datensatz auf 0. Zu diesem Zeitpunkt muss ein Aktualisierungsvorgang ausgeführt werden.

Wenn kein Datensatz mit id_card=142733 vorhanden ist, zeichnet die Pseudotabelle DUAL eine Zeile mit dem Inhalt „ly“,142733 auf. Die Anweisung vor dem Einfügen ändert den Datensatz auf 1 und die Aktualisierungsanweisung muss nicht ausgeführt werden.

Leistungsoptimierung für MySQL-Batch-Einfügungen und -Updates

Das Einfügen und Aktualisieren großer Datenmengen nimmt aufgrund von Leistungsengpässen wie E/A/CPU viel Zeit in Anspruch. Die derzeit gängigen Optimierungen umfassen hauptsächlich Vorkompilierung, Einfügen mehrerer Daten mit einer einzigen SQL-Anweisung und Transaktionseinfügung. Im Folgenden finden Sie eine ausführliche Einführung:

Einzelner Einsatz (Mybatis)

INSERT INTO SYS_CITY (CITY_CODE, CITY_NAME, PROVINCE_NAME, ALIAS, ABBRE_PY) WERTE
(${cityCode}, ${cityName}, ${provinceName}, ${alias}, ${abbrePy})

Einzelner vorkompilierter Insert (Mybatis)

Durch Vorkompilierung kann die Analysezeit des MySQL-Dienstes verkürzt werden. Mytatis verwendet #variable

INSERT INTO SYS_CITY (CITY_CODE, CITY_NAME, PROVINCE_NAME, ALIAS, ABBRE_PY) WERTE
(#{Stadtcode}, #{Stadtname}, #{Provinzname}, #{Alias}, #{Abkürzung})

Einfügen mehrerer Datensätze in eine einzige SQL-Anweisung

Das bedeutet, SQL zu spleißen und mehrere Daten in einem SQL einzufügen oder zu aktualisieren.

INSERT INTO SYS_CITY (CITY_CODE, CITY_NAME, PROVINCE_NAME, ALIAS, ABBRE_PY) WERTE
("Stadtcode1", "Stadtname1", "Provinzname1" "Alias1", "AbkürzungPy1"), ("Stadtcode2", "Stadtname2", "Provinzname2" "Alias2", "AbkürzungPy2")

Gründe für schnelle

1. Die Menge der Protokolle nach dem Zusammenführen (MySQL-Binlog- und InnoDB-Transaktionsprotokolle) wird reduziert, wodurch die Menge und Häufigkeit des Protokolllöschens verringert und somit die Effizienz verbessert wird.

2. Reduzieren Sie die Netzwerkübertragungs-E/A durch Zusammenführen von SQL-Anweisungen.

3. Reduzieren Sie die Anzahl der zu analysierenden SQL-Anweisungen durch Zusammenführen von SQL-Anweisungen.

Vorsichtsmaßnahmen

1. Die Länge des Datenbank-SQL ist begrenzt. Überschreiten Sie die SQL-Länge nicht, sonst wird ein Fehler gemeldet.

2. Beim Einfügen in der falschen Reihenfolge überschreitet die Geschwindigkeit die Kapazität von innodb_buffer. Jede Indexpositionierung erfordert mehr Lese- und Schreibvorgänge auf der Festplatte und die Leistung nimmt rapide ab.

Transaktion einfügen

Unter dem Einfügen einer Transaktion versteht man das Öffnen einer Transaktion vor dem Einfügen und das Schließen und Festschreiben der Transaktion nach dem Einfügen.

Gründe für schnelle

1. Wenn Sie einen INSERT-Vorgang ausführen, erstellt MySQL intern eine Transaktion und der eigentliche Einfügeverarbeitungsvorgang wird innerhalb der Transaktion ausgeführt. Durch die Verwendung von Transaktionen können die Kosten für die Erstellung von Transaktionen gesenkt werden.

Vorsichtsmaßnahmen

1. Die Transaktion darf nicht zu groß sein. MySQL verfügt über ein Konfigurationselement innodb_log_buffer_size. Wenn die Transaktion diesen Wert überschreitet, wird die Festplatte geleert, was zu Leistungseinbußen führt.

2. Beim Einfügen in der falschen Reihenfolge überschreitet die Geschwindigkeit die Kapazität von innodb_buffer. Jede Indexpositionierung erfordert mehr Lese- und Schreibvorgänge auf der Festplatte und die Leistung nimmt rapide ab.

Testergebnisse

Umgebung: i5-4200U 1,6 GHz, 12 GB Speicher, Solid-State-Laufwerk

Zusammenfassen

Die Kombination aus zusammengeführtem SQL und Transaktionseinfügung ist am effizientesten. Beim Einfügen außerhalb der Reihenfolge übersteigt die Geschwindigkeit die Kapazität von innodb_buffer. Jede Indexpositionierung erfordert mehr Lese- und Schreibvorgänge auf der Festplatte und die Leistung nimmt schnell ab. Versuchen Sie, Methoden zu verwenden, die nicht außerhalb der Reihenfolge erfolgen. Das Obige ist meine persönliche Erfahrung. Ich hoffe, es kann Ihnen als Referenz dienen. Ich hoffe auch, dass Sie 123WORDPRESS.COM unterstützen werden.