MySQL-Dateneinfügungsoptimierungsmethode concurrent_insert

Wenn ein Thread eine DELAYED-Anweisung für eine Tabelle ausführt und kein solcher Handler vorhanden ist, wird ein Handler-Thread erstellt, um alle DELAYED-Anweisungen für die Tabelle zu verarbeiten.

Im Allgemeinen sind Lese- und Schreibvorgänge in MyISAM seriell, aber beim Abfragen und Einfügen in dieselbe Tabelle kann MyISAM Abfragen und Einfügungen entsprechend der Einstellung von concurrent_insert parallel verarbeiten, um die Häufigkeit von Sperrenwettbewerben zu verringern:

Wenn concurrent_insert=0, ist die gleichzeitige Einfügefunktion nicht zulässig.
Wenn concurrent_insert=1, sind gleichzeitige Einfügungen für Tabellen ohne Lücken zulässig, mit neuen Daten am Ende der Datendatei (Standard).
Wenn concurrent_insert=2, ist das gleichzeitige Einfügen am Ende der Datendatei zulässig, unabhängig davon, ob die Tabelle Lücken aufweist oder nicht.

Es scheint, dass das Setzen von concurrent_insert auf 2 sehr kosteneffizient ist. Was die resultierende Dateifragmentierung betrifft, können Sie die Syntax OPTIMIZE TABLE verwenden, um sie regelmäßig zu optimieren.

maximale Anzahl Schreibsperren:

Standardmäßig ist die Priorität von Schreibvorgängen höher als die von Lesevorgängen. Auch wenn zuerst eine Leseanforderung und später eine Schreibanforderung gesendet wird, wird zuerst die Schreibanforderung und dann die Leseanforderung verarbeitet. Dadurch entsteht ein Problem: Sobald ich mehrere Schreibanforderungen gebe, werden alle Leseanforderungen blockiert, bis alle Schreibanforderungen verarbeitet sind und dann die Möglichkeit besteht, Leseanforderungen zu verarbeiten. Zu diesem Zeitpunkt können Sie die Verwendung von max_write_lock_count in Betracht ziehen:

max_write_lock_count=1

Mit dieser Einstellung wird der Schreibvorgang vom System angehalten, um dem Lesevorgang die Möglichkeit zu geben, ausgeführt zu werden.

Updates mit niedriger Priorität:

Wir können es auch direkter handhaben und die Priorität von Schreibvorgängen herabsetzen und Lesevorgängen eine höhere Priorität geben.

Updates mit niedriger Priorität = 1

Zusammenfassend ist concurrent_insert=2 unbedingt zu empfehlen. Was max_write_lock_count=1 und low-priority-updates=1 betrifft, kommt es auf die Situation an. Wenn die Priorität von Schreibvorgängen gesenkt werden kann, verwenden Sie low-priority-updates=1, andernfalls verwenden Sie max_write_lock_count=1.

Setze-Variable = max_zulässiges_Paket=1M
Setze-Variable = Nettopufferlänge = 2K

Unter der MyISAM-Engine

1. Versuchen Sie, zum Einfügen von Daten „insert into table_name values ​​​​(…), (…..), (…..)“ zu verwenden, und vermeiden Sie die Verwendung von „inset into table_name values ​​​​(); inset into table_name values ​​​​(); inset into table_name values ​​​​();

2 Erhöhen Sie die bulk_insert_buffer_size (Standard 8M)

3. Wenn die Tabelle nicht leer ist, verwenden Sie „alter table table_name disable keys“, laden Sie dann die Daten in die Datei und führen Sie nach dem Importieren der Daten Folgendes aus:

alter table table_name enable keys. Wenn es sich um eine leere Tabelle handelt, ist dieser Vorgang nicht erforderlich, da eine MyISAM-Tabelle beim Importieren von Daten in eine leere Tabelle zuerst die Daten importiert und dann Indizes erstellt.

4 Erwägen Sie beim Einfügen von Daten die Verwendung von „insert delay…“. Dieser Vorgang stellt den Einfügevorgang tatsächlich in eine Warteschlange für ein relativ konzentriertes Einfügen, was schneller ist.

5. Die Verwendung der Funktion „Daten in Datei laden“ ist fast 20-mal schneller als die Verwendung der Funktion „Einfügen“. Versuchen Sie, diese Funktion zu verwenden.

Unter der InnoDB-Engine

1. Führen Sie vor dem Importieren der Daten „set unique_checks=0“ aus, um die Prüfung eindeutiger Indizes zu deaktivieren. Führen Sie nach dem Importieren der Daten „set unique_checks=1“ aus.

2. Führen Sie vor dem Importieren der Daten „set foreign_key_checks=0“ aus, um die Fremdschlüsselprüfungen zu deaktivieren. Führen Sie nach dem Importieren der Daten „set foreign_key_checks=1“ aus.

3. Führen Sie vor dem Importieren von Daten „set autocommit=0“ aus, um das automatische Festschreiben automatischer Transaktionen zu deaktivieren. Führen Sie nach Abschluss des Datenimports „set autocommit=1“ aus, um den automatischen Festschreibvorgang wiederherzustellen.

Bei Tabellen, die die InnoDB-Engine verwenden, wird der physische Speicher in PK-Reihenfolge gespeichert. Sie können keine Deaktivierungsschlüssel wie MyISAM verwenden.

Eine Verbesserung des Festplatten-E/A der Hardware wirkt sich sehr positiv auf die Einfügegeschwindigkeit aus (wenn Sie also große Datenmengen importieren oder exportieren, versuchen Sie dies auf relativ guter Hardware zu tun, um die Fertigstellungszeit zu verkürzen und Problemen vorzubeugen).

Wenn ein Thread eine DELAYED-Anweisung für eine Tabelle ausführt und kein solcher Handler vorhanden ist, wird ein Handler-Thread erstellt, um alle DELAYED-Anweisungen für die Tabelle zu verarbeiten.

Der Thread überprüft, ob der Handler bereits eine verzögerte Sperre erworben hat. Wenn nicht, weist er den Handler an, eine zu erwerben. Eine verzögerte Sperre kann auch dann erworben werden, wenn ein anderer Thread eine Lese- oder Schreibsperre für die Tabelle hat. Der Handler wartet jedoch auf alle ALTER TABLE-Sperren oder FLUSH TABLES, um sicherzustellen, dass die Tabellenstruktur auf dem neuesten Stand ist.

Der Thread führt die INSERT-Anweisung aus, aber anstatt die Zeile in die Tabelle zu schreiben, platziert er eine Kopie der letzten Zeile in einer vom Prozessor-Thread verwalteten Warteschlange. Eventuelle Syntaxfehler können vom Thread erkannt und an das Client-Programm gemeldet werden.

Der Client kann die Anzahl der Wiederholungen oder AUTO_INCREMENT-Werte für die resultierenden Zeilen nicht melden; er kann sie nicht vom Server abrufen, da INSERT zurückkehrt, bevor der Einfügevorgang abgeschlossen ist. Wenn Sie die C-API verwenden, gibt die Funktion mysql_info() aus demselben Grund nichts Sinnvolles zurück.

Wenn Zeilen in die Tabelle eingefügt werden, wird das Aktualisierungsprotokoll vom Prozessor-Thread aktualisiert. Beim Einfügen mehrerer Zeilen wird das Aktualisierungsprotokoll aktualisiert, wenn die erste Zeile eingefügt wird.
Nach dem Schreiben jeder delay_insert_limit-Zeile prüft der Prozessor, ob noch SELECT-Anweisungen ausstehen und lässt, falls dies der Fall ist, deren Ausführung zu, bevor er fortfährt.

Wenn der Prozessor keine Zeilen mehr in seiner Warteschlange hat, wird die Tabelle entsperrt. Wenn innerhalb von delay_insert_timeout Sekunden kein neuer INSERT DELAYED-Befehl empfangen wird, wird der Handler beendet.

Wenn in der Warteschlange eines bestimmten Prozessors bereits mehr Zeilen als „delayed_queue_size“ ausstehen, wartet der Thread, bis in der Warteschlange Platz ist. Dadurch wird sichergestellt, dass der MySQLD-Server nicht seinen gesamten Speicher für verzögerte Speicherwarteschlangen verwendet.

Der Prozessor-Thread zeigt „delayed_insert“ in der MySQL-Prozesstabelle in der Spalte „Befehl“ an. Wenn Sie einen FLUSH TABLES-Befehl ausführen oder ihn mit KILL thread_id beenden, wird er beendet, speichert jedoch vor dem Beenden zunächst alle in die Warteschlange gestellten Zeilen in der Tabelle. Während dieser Zeit werden keine neuen INSERT-Befehle von anderen Threads akzeptiert. Wenn Sie danach ein INSERT DELAYED ausführen, wird ein neuer Prozessor-Thread erstellt.

Beachten Sie, dass das Obige bedeutet, dass, wenn bereits ein INSERT DELAYED-Handler ausgeführt wird, der INSERT DELAYED-Befehl eine höhere Priorität hat als ein normales INSERT! Andere Aktualisierungsbefehle müssen warten, bis die INSERT DELAY-Warteschlange leer ist, den Prozessor-Thread beenden (mit KILL thread_id) oder FLUSH TABLES ausführen.

Die folgenden Statusvariablen liefern Informationen zum Befehl INSERT DELAYED: Delayed_insert_threads Die Anzahl der Prozessor-Threads.

Delayed_writes Die Anzahl der mit INSERT DELAYED geschriebenen Zeilen
Not_flushed_delayed_rows Anzahl der Zeilen, die darauf warten, geschrieben zu werden

Lösung für viele gleichzeitige Einfügeanweisungen

Vorwort

1. Mehrfache Änderungen an Daten verhindern

1.1. Lösung einfügen

1. Fügen Sie uniqpue hinzu, um das Problem zu lösen (aktualisieren Sie es bei Wiederholung)

Das Einfügen ist im Allgemeinen kein Problem. Achten Sie nur auf die Eindeutigkeit, sodass keine doppelten Einfügungen vorgenommen werden (wenn Duplikate erstellt werden, wird ein Aktualisierungsvorgang ausgeführt).

2. Plan aktualisieren

1. Redis verteilte Sperre, Nachrichtenwarteschlange (es wird immer nur eine gleichzeitig eingefügt)

2. MySQL-Sperre (optimistische Sperre kann für Aktualisierungen verwendet werden)

2. Sicherheit bei hoher Parallelität

1. Führen Sie eine große DELETE- oder INSERT-Abfrage auf einer Online-Website aus, um zu vermeiden, dass Ihre gesamte Website aufgrund Ihrer Operation nicht mehr reagiert. Da diese beiden Vorgänge die Tabelle sperren (Update sperrt die Tabelle auch, wenn kein eindeutiger Primärschlüssel oder Index angegeben ist), können keine anderen Vorgänge ausgeführt werden, sobald die Tabelle gesperrt ist. Seien Sie also sehr vorsichtig

2. Wenn Sie Ihre Tabelle für einen bestimmten Zeitraum, z. B. 30 Sekunden, sperren, kann dies bei einer Site mit einer hohen Anzahl von Besuchen dazu führen, dass die in diesen 30 Sekunden angesammelte Anzahl von Zugriffsprozessen/Threads, Datenbanklinks und geöffneten Dateien nicht nur zum Absturz Ihres Webdienstes führt, sondern auch dazu, dass Ihr gesamter Server sofort hängt. >

2.1 Lösung

2.1.1. Tabellenanpassung

Durch die Aufteilung einer Tabelle in mehrere Tabellen nach Spalten können die Komplexität der Tabelle und die Anzahl der Felder verringert und so der Optimierungszweck erreicht werden. (Es wäre beängstigend, wenn es mehr als hundert Felder gäbe)

Beispiel 1:

In der Tabelle „Benutzer“ gibt es ein Feld mit der Bezeichnung „Privatadresse“. Dieses Feld ist optional. Abgesehen von persönlichen Informationen müssen Sie dieses Feld beim Arbeiten mit der Datenbank nicht häufig lesen oder neu schreiben. Warum also nicht in eine andere Tabelle einfügen? Dadurch wird die Leistung Ihrer Tabelle verbessert. Bedenken Sie, dass für die Benutzertabelle meistens nur Benutzer-ID, Benutzername, Passwort, Benutzerrolle usw. häufig verwendet werden. Kleinere Tabellen haben immer eine bessere Leistung.

Beispiel 2:

Sie haben ein Feld namens „last_login“, das jedes Mal aktualisiert wird, wenn sich ein Benutzer anmeldet. Allerdings führt jede Aktualisierung dazu, dass der Abfragecache für die Tabelle geleert wird. Sie können dieses Feld daher in eine andere Tabelle einfügen, sodass das ständige Lesen der Benutzer-ID, des Benutzernamens und der Benutzerrolle nicht beeinträchtigt wird, da der Abfragecache Ihnen dabei hilft, die Leistung deutlich zu steigern. Startseite für HP-Programmierer

Darüber hinaus ist zu beachten, dass wir nicht davon ausgehen, dass die aus diesen getrennten Feldern gebildete Tabelle häufig verbunden wird. Andernfalls ist die Leistung schlechter als bei einer nicht geteilten Tabelle und es kommt zu einem extremen Rückgang.