Detaillierte Erläuterung der Leistungsoptimierung für MySQL-Batch-SQL-Einfügungen

Bei einigen Systemen mit großen Datenmengen bestehen die Probleme der Datenbank in einer geringen Abfrageeffizienz und einer langen Datenspeicherzeit. Insbesondere bei einem Berichtssystem kann der Zeitaufwand für den Datenimport mehrere Stunden oder sogar mehr als zehn Stunden pro Tag betragen. Daher ist es sinnvoll, die Einfügeleistung der Datenbank zu optimieren.

Nach einigen Leistungstests mit MySQL InnoDB habe ich einige Methoden gefunden, die die Einfügeeffizienz verbessern können. Diese dienen Ihnen als Referenz.

1. Eine SQL-Anweisung fügt mehrere Datensätze ein.

Zu den häufig verwendeten Insert-Anweisungen gehören:

INSERT INTO `insert_table` (`Datum/Uhrzeit`, `UID`, `Inhalt`, `Typ`) 
  WERTE ('0', 'Benutzer-ID_0', 'Inhalt_0', 0); 
INSERT INTO `insert_table` (`Datum/Uhrzeit`, `UID`, `Inhalt`, `Typ`) 
  WERTE ('1', 'Benutzer-ID_1', 'Inhalt_1', 1); 

Geändert zu:

INSERT INTO `insert_table` (`Datum/Uhrzeit`, `UID`, `Inhalt`, `Typ`) 
  WERTE ('0', 'Benutzer-ID_0', 'Inhalt_0', 0), ('1', 'Benutzer-ID_1', 'Inhalt_1', 1); 

Der geänderte Einfügevorgang kann die Einfügeeffizienz des Programms verbessern. Der Hauptgrund für die hohe Ausführungseffizienz des zweiten SQL besteht hier darin, dass die Menge der Protokolle nach dem Zusammenführen (MySQL-Binlog- und InnoDB-Transaktionen erstellen Protokolle) reduziert wird, wodurch die Menge und Häufigkeit der Protokolllöschung verringert und somit die Effizienz verbessert wird. Durch das Zusammenführen von SQL-Anweisungen kann die Anzahl der SQL-Anweisungsanalysen reduziert und die E/A der Netzwerkübertragung verringert werden.

Nachfolgend sind einige Testvergleichsdaten aufgeführt. Dabei handelt es sich um den Import einzelner Daten und die Konvertierung in eine SQL-Anweisung für den Import. Dabei werden jeweils 100, 1.000 und 10.000 Datensätze getestet.

2. Führen Sie den Einfügevorgang in einer Transaktion durch.

Ändern Sie den Einsatz in:

TRANSAKTION STARTEN; 
INSERT INTO `insert_table` (`Datum/Uhrzeit`, `UID`, `Inhalt`, `Typ`) 
  WERTE ('0', 'Benutzer-ID_0', 'Inhalt_0', 0); 
INSERT INTO `insert_table` (`Datum/Uhrzeit`, `UID`, `Inhalt`, `Typ`) 
  WERTE ('1', 'Benutzer-ID_1', 'Inhalt_1', 1); 
... 
BEGEHEN; 

Die Verwendung von Transaktionen kann die Effizienz der Dateneinfügung verbessern. Dies liegt daran, dass MySQL beim Ausführen einer INSERT-Operation intern eine Transaktion einrichtet und die eigentliche Einfügeverarbeitungsoperation innerhalb der Transaktion ausgeführt wird. Durch die Verwendung von Transaktionen können die Kosten zum Erstellen von Transaktionen reduziert werden und alle Einfügungen werden nach der Ausführung festgeschrieben.

Hier wird auch ein Testvergleich bereitgestellt, der den Fall der Nichtverwendung von Transaktionen und der Verwendung von Transaktionen bei einer Datensatzanzahl von 100, 1.000 und 10.000 abdeckt.

3. Die Daten werden der Reihe nach eingefügt.

Geordnete Dateneinfügung bedeutet, dass die eingefügten Datensätze basierend auf dem Primärschlüssel in der richtigen Reihenfolge angeordnet werden. Beispielsweise ist datetime der Primärschlüssel des Datensatzes:

INSERT INTO `insert_table` (`Datum/Uhrzeit`, `UID`, `Inhalt`, `Typ`) 
  WERTE ('1', 'Benutzer-ID_1', 'Inhalt_1', 1); 
INSERT INTO `insert_table` (`Datum/Uhrzeit`, `UID`, `Inhalt`, `Typ`) 
  WERTE ('0', 'Benutzer-ID_0', 'Inhalt_0', 0); 
INSERT INTO `insert_table` (`Datum/Uhrzeit`, `UID`, `Inhalt`, `Typ`) 
  WERTE ('2', 'Benutzer-ID_2', 'Inhalt_2', 2); 

Geändert zu:

INSERT INTO `insert_table` (`Datum/Uhrzeit`, `UID`, `Inhalt`, `Typ`) 
  WERTE ('0', 'Benutzer-ID_0', 'Inhalt_0', 0); 
INSERT INTO `insert_table` (`Datum/Uhrzeit`, `UID`, `Inhalt`, `Typ`) 
  WERTE ('1', 'Benutzer-ID_1', 'Inhalt_1', 1); 
INSERT INTO `insert_table` (`Datum/Uhrzeit`, `UID`, `Inhalt`, `Typ`) 
  WERTE ('2', 'Benutzer-ID_2', 'Inhalt_2', 2); 

Da die Datenbank beim Einfügen von Daten Indexdaten pflegen muss, erhöhen Datensätze in der falschen Reihenfolge den Aufwand für die Indexpflege. Wir können uns auf den von innodb verwendeten B+Tree-Index beziehen. Wenn jeder Datensatz am Ende des Index eingefügt wird, ist die Indexpositionierungseffizienz sehr hoch und die Indexanpassung gering. Wenn sich der eingefügte Datensatz in der Mitte des Index befindet, muss der B+Tree aufgeteilt und zusammengeführt werden, was mehr Rechenressourcen verbraucht und die Indexpositionierungseffizienz des eingefügten Datensatzes verringert. Bei einem großen Datenvolumen werden häufige Festplattenvorgänge durchgeführt.

Nachfolgend finden Sie einen Leistungsvergleich von Zufalls- und sequentiellen Daten mit Datensätzen von 100, 1.000, 10.000, 100.000 und 1 Million.

Aus den Testergebnissen geht hervor, dass die Leistung der Optimierungsmethode verbessert wurde, die Verbesserung ist jedoch nicht sehr offensichtlich.

Umfassender Leistungstest:

Hier ist ein Test zur Verwendung der oben genannten drei Methoden zur Optimierung der INSERT-Effizienz.

Aus den Testergebnissen können wir ersehen, dass die Leistungsverbesserung der Methode zum Zusammenführen von Daten + Transaktionen bei kleinen Datenmengen offensichtlich ist. Bei großen Datenmengen (mehr als 10 Millionen) sinkt die Leistung stark. Dies liegt daran, dass die Datenmenge zu diesem Zeitpunkt die Kapazität von innodb_buffer überschreitet. Jede Indexpositionierung erfordert mehr Lese- und Schreibvorgänge auf der Festplatte, und die Leistung sinkt schnell. Die Methode zum Zusammenführen von Daten + Transaktionen + geordneten Daten funktioniert auch dann noch gut, wenn das Datenvolumen mehrere zehn Millionen oder mehr erreicht. Bei großen Datenmengen ist die geordnete Datenindexpositionierung bequemer und erfordert keine häufigen Lese- und Schreibvorgänge auf der Festplatte, sodass eine höhere Leistung aufrechterhalten werden kann.

Notiz:

1. SQL-Anweisungen haben eine Längenbeschränkung. Beim Zusammenführen von Daten im selben SQL darf die Länge der SQL-Anweisung die SQL-Längenbeschränkung nicht überschreiten. Dies kann über die max_allowed_packet-Konfiguration geändert werden. Der Standardwert ist 1M, der während des Tests auf 8M geändert wurde.

2. Die Transaktionsgröße muss kontrolliert werden. Wenn die Transaktion zu groß ist, kann dies die Ausführungseffizienz beeinträchtigen. MySQL verfügt über ein Konfigurationselement innodb_log_buffer_size. Wenn dieser Wert überschritten wird, werden die InnoDB-Daten auf die Festplatte geschrieben und die Effizienz nimmt ab. Deshalb ist es besser, die Transaktion festzuschreiben, bevor die Daten diesen Wert erreichen.

Das Obige ist der vollständige Inhalt dieses Artikels. Ich hoffe, er wird für jedermanns Studium hilfreich sein. Ich hoffe auch, dass jeder 123WORDPRESS.COM unterstützen wird.