Vergleich mehrerer Beispiele zur Einfügungseffizienz in MySQL

Vorwort

Aus beruflichen Gründen musste ich kürzlich eine große Menge an Daten (ca. 1.000 W) in MySQL einfügen, was schätzungsweise zeitaufwändig ist. Daher möchte ich nun testen, welche Methode zum Einfügen von Daten schneller und effizienter ist.

Im Folgenden wird die Einfügungseffizienz jeder Methode bei unterschiedlichen Datenmengen getestet.

Die Grundlagen und Funktionen der Testdatenbank sind wie folgt:

mysql> Datenbanktest erstellen;
Abfrage OK, 1 Zeile betroffen (0,02 Sek.)
mysql> Test verwenden;
Datenbank geändert
mysql> Tabelle erstellen mytable(id int Primärschlüssel auto_increment, Wert varchar(50));
Abfrage OK, 0 Zeilen betroffen (0,35 Sek.)
mysql> Beschreibung meineTabelle;
+----------+----------+------+-----+---------+----------------+
| Feld | Typ | Null | Schlüssel | Standard | Extra |
+----------+----------+------+-----+---------+----------------+
| id | int(11) | NEIN | PRI | NULL | auto_increment |
| Wert | varchar(50) | JA | | NULL | |
+----------+----------+------+-----+---------+----------------+
2 Zeilen im Satz (0,02 Sek.)

Zur Vereinfachung des Tests wird hier eine Tabelle mit zwei Feldern erstellt, eines ist die automatisch inkrementierende ID und das andere ist eine Zeichenfolge, die den Inhalt darstellt.

Während des Tests müssen Sie am Ende jedes Experiments mysql> truncate mytable verwenden, um die vorhandene Tabelle zu löschen.

Methode 1: Einfügen einzeln

Testcode: (Dazwischen befinden sich 1.000 Einfügeanweisungen. Das Kopieren und Einfügen mit vim ist bequemer. Speichern Sie es nach dem Schreiben in a.sql und geben Sie dann die Quelle a.sql in die MySQL-Eingabeaufforderung ein.)

setze @start=(wähle aktuellen_Zeitstempel(6));
in meine Tabelle einfügen: Werte (null, „Wert“);
......
in meine Tabelle einfügen: Werte (null, „Wert“);
setze @end=(wähle aktuellen_Zeitstempel(6));
wähle @start;
wähle @end;

Ausgabe:

Abfrage OK, 1 Zeile betroffen (0,03 Sek.)
......
Abfrage OK, 1 Zeile betroffen (0,03 Sek.)
Abfrage OK, 0 Zeilen betroffen (0,00 Sek.)
+----------------------------+
| @Anfang |
+----------------------------+
| 05.05.2016 23:06:51.267029 |
+----------------------------+
1 Zeile im Satz (0,00 Sek.)
+----------------------------+
| @Ende |
+----------------------------+
| 05.05.2016 23:07:22.831889 |
+----------------------------+
1 Zeile im Satz (0,00 Sek.)

Die Gesamtzeit beträgt 31,56486 s. Tatsächlich dauert fast jede Anweisung ungefähr gleich lange, nämlich im Wesentlichen 30 ms.

Auf diese Weise werden für 10 Millionen Daten 87 Stunden benötigt.

Bei größeren Datenmengen verzichten wir allerdings auf den Versuch, da von dieser Methode definitiv abzuraten ist.

Methode 2: Transaktionsbasierte Batcheinfügung

Tatsächlich geht es darum, so viele Abfragen in eine Transaktion zu packen. Tatsächlich öffnet jede Anweisung in Methode 1 eine Transaktion, daher ist es sehr langsam.

Testcode: (Grundsätzlich ähnlich wie Methode 1, hauptsächlich werden zwei Zeilen hinzugefügt. Da es relativ schnell ist, werden hier verschiedene Datenmengen getestet.)

setze @start=(wähle aktuellen_Zeitstempel(6));
Transaktion starten;
in meine Tabelle einfügen: Werte (null, „Wert“);
......
in meine Tabelle einfügen: Werte (null, „Wert“);
begehen;
setze @end=(wähle aktuellen_Zeitstempel(6));
wähle @start;
wähle @end;

Testergebnisse:

Datenvolumen Zeit (s)
1K 0,1458
1W 1,0793
10W 5,546006
100 W 38,930997

Es ist ersichtlich, dass es sich grundsätzlich um eine logarithmische Zeit handelt und die Effizienz relativ hoch ist.

Methode 3: Mehrere Datensätze gleichzeitig mit einer einzigen Anweisung einfügen

Das heißt, ein Insert fügt mehrere Werte gleichzeitig ein.

Testcode:

in mytable-Werte einfügen (null, "Wert"),
    (null,"Wert"),
    ......
    (null,"Wert");

Testergebnisse:

Datenvolumen Zeit (s)
1k 0,15
1W 0,80
10 W 2,14
Leistung: 100 W *

Dies sieht auch wie eine logarithmische Zeit aus und ist etwas schneller als Methode 2. Das Problem besteht jedoch darin, dass es für eine einzelne SQL-Anweisung eine Puffergrößenbeschränkung gibt. Sie können die Konfiguration zwar ändern, um den Puffer zu vergrößern, er darf jedoch nicht zu groß sein. Daher kann es beim Einfügen großer Datenmengen nicht verwendet werden.

Methode 4: Datendateien importieren

Schreiben Sie die numerischen Daten in eine Datendatei und importieren Sie diese direkt (siehe vorherigen Abschnitt).

Datendatei (a.dat):

Nullwert
Nullwert
.....
Nullwert
Nullwert

Testcode:

mysql> lade Daten der lokalen Infile "a.dat" in die Tabelle mytable;

Testergebnisse:

Datenvolumen Zeit (s)
1K 0,13
1W 0,75
10 W 1,97
100 W 6,75
1000 W 58,18

Er ist der Schnellste. . . .

Zusammenfassen

Das Obige ist der vollständige Inhalt dieses Artikels. Ich hoffe, dass der Inhalt dieses Artikels Ihnen bei Ihrem Studium oder Ihrer Arbeit helfen kann. Wenn Sie Fragen haben, können Sie eine Nachricht hinterlassen. Vielen Dank für Ihre Unterstützung von 123WORDPRESS.COM.