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

Bei einigen Systemen mit großen Datenmengen ist die Datenbank nicht nur mit einer geringen Abfrageeffizienz, sondern auch mit einer langen Datenspeicherzeit konfrontiert. Insbesondere bei Berichtssystemen kann der Zeitaufwand für den Datenimport mehrere Stunden oder mehr als zehn Stunden pro Tag betragen. Daher ist es sinnvoll, die Leistung beim Einfügen in die Datenbank zu optimieren.

Empfohlen: „MySQL-Tutorial“

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

1. Eine SQL-Anweisung zum Einfügen mehrerer Datenelemente

Häufig verwendete Einfügeanweisungen wie:

INSERT INTO `insert_table` (`datetime`, `uid`, `content`, `type`) <br/>    VALUES (&#39;0&#39;, &#39;userid_0&#39;, &#39;content_0&#39;, 0);<br/>INSERT INTO `insert_table` (`datetime`, `uid`, `content`, `type`) <br/>    VALUES (&#39;1&#39;, &#39;userid_1&#39;, &#39;content_1&#39;, 1);<br/>

wird geändert in:

INSERT INTO `insert_table` (`datetime`, `uid`, `content`, `type`) <br/>    VALUES (&#39;0&#39;, &#39;userid_0&#39;, &#39;content_0&#39;, 0), (&#39;1&#39;, &#39;userid_1&#39;, &#39;content_1&#39;, 1);<br/>

Der geänderte Einfügevorgang kann die Einfügeeffizienz des Programms verbessern. Der Hauptgrund, warum die zweite SQL-Ausführungseffizienz hier hoch ist, besteht darin, dass die Anzahl der Protokolle nach dem Zusammenführen (Binlog von MySQL und Transaktionsprotokolle von Innodb) reduziert wird, was das Datenvolumen und die Häufigkeit des Protokolllöschens verringert und dadurch die Effizienz verbessert. Durch das Zusammenführen von SQL-Anweisungen kann auch die Anzahl der SQL-Anweisungsanalysen reduziert und die Netzwerkübertragungs-E/A reduziert werden.

Hier sind einige Testvergleichsdaten, die dazu dienen, ein einzelnes Datenelement zu importieren und es für den Import in eine SQL-Anweisung umzuwandeln und jeweils 100, 1.000 und 10.000 Datensätze zu testen.

2. Führen Sie die Einfügungsverarbeitung in der Transaktion durch.

Ändern Sie die Einfügung in:

START TRANSACTION;
INSERT INTO `insert_table` (`datetime`, `uid`, `content`, `type`) <br/>    VALUES (&#39;0&#39;, &#39;userid_0&#39;, &#39;content_0&#39;, 0);<br/>INSERT INTO `insert_table` (`datetime`, `uid`, `content`, `type`) <br/>    VALUES (&#39;1&#39;, &#39;userid_1&#39;, &#39;content_1&#39;, 1);<br/>...
COMMIT;

Die Verwendung von Transaktionen kann die Effizienz der Dateneinfügung verbessern. Dies liegt daran, dass bei der Ausführung einer INSERT-Operation intern eine Transaktion in MySQL eingerichtet wird. und nur innerhalb der Transaktion. Führen Sie tatsächliche Einfügungsverarbeitungsvorgänge durch. Durch die Verwendung von Transaktionen können Sie die Kosten für die Erstellung von Transaktionen reduzieren. Alle Einfügungen werden vor dem Festschreiben ausgeführt.

Hier wird auch ein Testvergleich bereitgestellt. Dies gilt für den Fall, dass keine Transaktionen verwendet werden und Transaktionen verwendet werden, wenn die Anzahl der Datensätze 100, 1000 und 10000 beträgt.

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

Ordentliches Einfügen von Daten bedeutet, dass die eingefügten Datensätze in der Reihenfolge des Primärschlüssels angeordnet sind. Datum/Uhrzeit ist beispielsweise der Primärschlüssel des Datensatzes:

INSERT INTO `insert_table` (`datetime`, `uid`, `content`, `type`) <br/>    VALUES (&#39;1&#39;, &#39;userid_1&#39;, &#39;content_1&#39;, 1);<br/>INSERT INTO `insert_table` (`datetime`, `uid`, `content`, `type`) <br/>    VALUES (&#39;0&#39;, &#39;userid_0&#39;, &#39;content_0&#39;, 0);<br/>INSERT INTO `insert_table` (`datetime`, `uid`, `content`, `type`) <br/>    VALUES (&#39;2&#39;, &#39;userid_2&#39;, &#39;content_2&#39;,2);<br/>

wird geändert in:

INSERT INTO `insert_table` (`datetime`, `uid`, `content`, `type`) <br/>    VALUES (&#39;0&#39;, &#39;userid_0&#39;, &#39;content_0&#39;, 0);<br/>INSERT INTO `insert_table` (`datetime`, `uid`, `content`, `type`) <br/>    VALUES (&#39;1&#39;, &#39;userid_1&#39;, &#39;content_1&#39;, 1);<br/>INSERT INTO `insert_table` (`datetime`, `uid`, `content`, `type`) 
    VALUES ('2', 'userid_2', 'content_2',2);

Da die Datenbank beim Einfügen Indexdaten verwalten muss, erhöhen ungeordnete Datensätze die Kosten für die Indexverwaltung. Wir können uns auf den von InnoDB verwendeten B+Tree-Index beziehen. Wenn sich jeder eingefügte Datensatz am Ende des Index befindet, ist die Indexpositionierungseffizienz sehr hoch, und die Indexanpassung ist gering Index, B+Tree erfordert mehr Rechenressourcen und die Indexpositionierungseffizienz der eingefügten Datensätze nimmt ab. Wenn die Datenmenge groß ist, kommt es zu häufigen Festplattenvorgängen.

Der Leistungsvergleich von Zufallsdaten und sequentiellen Daten ist unten aufgeführt und wird als 100, 1000, 10000, 100000 bzw. 1 Million aufgezeichnet.

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

4. Umfassender Leistungstest

Hier ist ein Test, der die oben genannten drei Methoden gleichzeitig verwendet, um die INSERT-Effizienz zu optimieren.

Wie aus den Testergebnissen hervorgeht, ist die Leistungsverbesserung der Methode zum Zusammenführen von Daten + Transaktionen offensichtlich, wenn die Datenmenge klein ist ist groß (1.000 Zehntausend oder mehr), die Leistung sinkt stark. Dies liegt daran, dass die Datenmenge zu diesem Zeitpunkt die Kapazität von innodb_buffer übersteigt. Jede Indexpositionierung erfordert mehr Lese- und Schreibvorgänge auf der Festplatte und die Leistung sinkt schnell. Die Methode der Verwendung zusammengeführter Daten + Transaktionen + geordneter Daten funktioniert immer noch gut, wenn das Datenvolumen mehrere zehn Millionen erreicht. Wenn das Datenvolumen groß ist, ist die Positionierung des geordneten Datenindex bequemer und erfordert keine häufigen Lese- und Schreibvorgänge auf der Festplatte Daher kann eine hohe Leistung aufrechterhalten werden.

Hinweise:

1. Beim Zusammenführen von Daten in derselben SQL darf die SQL-Längenbegrenzung nicht überschritten werden Die max_allowed_packet-Konfiguration ist standardmäßig 1 MB und wurde während des Tests auf 8 MB geändert.

2. Transaktionen müssen in ihrer Größe kontrolliert werden. Wenn eine Transaktion zu groß ist, kann dies die Ausführungseffizienz beeinträchtigen. MySQL verfügt über das Konfigurationselement innodb_log_buffer_size. Wenn dieser Wert überschritten wird, werden die Innodb-Daten auf die Festplatte geleert. Ein besserer Ansatz besteht daher darin, die Transaktion festzuschreiben, bevor die Daten diesen Wert erreichen.

Das obige ist der detaillierte Inhalt vonAusführliche Erläuterung der Leistungsoptimierung für MySQL-Batch-SQL-Einfügungen. Für weitere Informationen folgen Sie bitte anderen verwandten Artikeln auf der PHP chinesischen Website!