Effizienzvergleich von vier Einfügemethoden in MySQL

In diesem Artikel wird anhand von Beispielen der Effizienzvergleich verschiedener Einfügemethoden in MySQL vorgestellt, darunter vier Methoden: Element-für-Element-Einfügung, transaktionsbasiertes Batch-Einfügen, einzelne Anweisung zum gleichzeitigen Einfügen mehrerer Datensätze und Importieren Im Gegensatz dazu stellt der Artikel es ausführlich anhand von Beispielcode vor. Freunde, die es benötigen, können es sich gemeinsam ansehen.

Vorwort

Aus Arbeitsgründen muss in letzter Zeit eine große Datenmenge von etwa 10 Millionen visuell in MySQL eingefügt werden Die Inspektion wird zeitaufwändiger sein. Jetzt geht es darum, zu testen, welche Methode zum Einfügen von Daten schneller und effizienter ist.

Im Folgenden wird die Einfügungseffizienz bei unterschiedlichen Datenmengen für jede Methode getestet.

Die Grundlagen und Operationen der Testdatenbank sind wie folgt:

mysql> create database test;
Query OK, 1 row affected (0.02 sec)
mysql> use test;
Database changed
mysql> create table mytable(id int primary key auto_increment ,value varchar(50));
Query OK, 0 rows affected (0.35 sec)
mysql> desc mytable;
+-------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra  |
+-------+-------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| value | varchar(50) | YES | | NULL |  |
+-------+-------------+------+-----+---------+----------------+
2 rows in set (0.02 sec)

Um das Testen zu erleichtern, wird hier eine Tabelle mit zwei Feldern erstellt, eines ist das Auto -inkrementierte ID und die andere ist eine Zeichenfolge, die den Inhalt darstellt.

Beim Testen müssen Sie mysql> truncate mytable nach jedem Experiment die vorhandene Tabelle leeren.

Methode 1: Fügen Sie

einen nach dem anderen ein. Testcode: (In der Mitte befinden sich 1000 Einfügeanweisungen. Das ist es bequemer zum Kopieren und Einfügen mit vim. Speichern Sie es nach dem Schreiben in a.sql und geben Sie dann die Quelle a.sql in die MySQL-Eingabeaufforderung ein)

set @start=(select current_timestamp(6));
insert into mytable values(null,"value");
......
insert into mytable values(null,"value");
set @end=(select current_timestamp(6));
select @start;
select @end;

Ausgabeergebnis:

Query OK, 1 row affected (0.03 sec)
......
Query OK, 1 row affected (0.03 sec)
Query OK, 0 rows affected (0.00 sec)
+----------------------------+
| @start   |
+----------------------------+
| 2016-05-05 23:06:51.267029 |
+----------------------------+
1 row in set (0.00 sec)
+----------------------------+
| @end   |
+----------------------------+
| 2016-05-05 23:07:22.831889 |
+----------------------------+
1 row in set (0.00 sec)

Es dauerte insgesamt 31,56486 Sekunden, tatsächlich dauerte fast jede Anweisung gleich lange, im Grunde 30 ms.

Auf diese Weise benötigen 1000 W Daten 87 Stunden.

Bei größeren Datenmengen werde ich diese Methode definitiv nicht ausprobieren.

Methode 2: Transaktionsbasierte Batch-Einfügung

Tatsächlich werden so viele Abfragen in einer Transaktion zusammengefasst. Tatsächlich öffnet jede Anweisung in Methode eins eine Transaktion und ist daher besonders langsam.

Testcode: (Im Grunde ähnlich wie Methode 1, hauptsächlich Hinzufügen von zwei Zeilen. Da es schneller ist, werden hier verschiedene Datenmengen getestet)

set @start=(select current_timestamp(6));
start transaction;
insert into mytable values(null,"value");
......
insert into mytable values(null,"value");
commit;
set @end=(select current_timestamp(6));
select @start;
select @end;

Testergebnisse:

数据量 时间(s)
1k  0.1458
1w  1.0793
10w 5.546006
100w 38.930997

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

Methode 3: Eine einzelne Anweisung fügt mehrere Datensätze gleichzeitig ein

bedeutet, dass eine Einfügung mehrere Werte gleichzeitig einfügt einmal.

Testcode:

insert into mytable values (null,"value"),
    (null,"value"),
    ......
    (null,"value");

Testergebnis:

数据量 时间(s)
1k  0.15
1w  0.80
10w 2.14
100w *

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

Methode 4: Datendatei importieren

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

Datendatei (a.dat):

null value
null value
.....
null value
null value

Testcode:

mysql> load data local infile "a.dat" into table mytable;

Testergebnis:

数据量 时间(s)
1k  0.13
1w  0.75
10w 1.97
100w 6.75
1000w 58.18

Schnellste Zeit, das ist er. . . .

Das obige ist der detaillierte Inhalt vonEffizienzvergleich von vier Einfügemethoden in MySQL. Für weitere Informationen folgen Sie bitte anderen verwandten Artikeln auf der PHP chinesischen Website!