Comparaison de l'efficacité de quatre méthodes d'insertion dans Mysql

Cet article vous présente la comparaison de l'efficacité de plusieurs méthodes d'insertion dans Mysql à travers des exemples, dont quatre méthodes : l'insertion élément par élément, l'insertion par lots basée sur les transactions, l'instruction unique insérant plusieurs ensembles de données à la fois et l'importation. fichiers de données. En comparaison, l'article le présente en détail à travers un exemple de code. Les amis qui en ont besoin peuvent venir y jeter un œil ensemble.

Avant-propos

Récemment, en raison des besoins professionnels, une grande quantité de données d'environ 10 millions a dû être insérée dans MySQL, et visuel l’inspection prendra plus de temps. Alors maintenant, c'est comme tester quelle méthode d'insertion de données est la plus rapide et la plus efficace.

Ce qui suit testera l'efficacité de l'insertion sous différentes quantités de données pour chaque méthode.

Les bases et les opérations de la base de données de test sont les suivantes :

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)

Pour faciliter les tests, une table est construite ici avec deux champs, l'un est l'auto -identifiant incrémenté, et l'autre est One est une chaîne représentant le contenu.

Lors des tests, vous devez mysql> truncate mytable effacer le tableau existant après chaque expérience.

Méthode 1 : Insérez

une par une Code de test : (Il y a 1000 instructions d'insertion au milieu. C'est le cas. plus pratique à copier et coller avec vim Après l'écriture, enregistrez-le dans a.sql, puis entrez source a.sql dans l'invite mysql)

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;

Résultat de sortie :

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)

Cela a pris un total de 31,56486 s, en fait presque chaque instruction prend le même temps, en gros 30 ms.

De cette façon, 1000w de données prendront 87h.

En ce qui concerne le volume de données plus important, je ne l'essaierai pas. Cette méthode n'est certainement pas recommandée.

Méthode 2 : Insertion par lots basée sur les transactions

En fait, tant de requêtes sont regroupées en une seule transaction. En fait, chaque instruction de la première méthode ouvre une transaction, elle est donc extrêmement lente.

Code de test : (fondamentalement similaire à la méthode 1, ajoutant principalement deux lignes. Parce qu'il est plus rapide, une variété de volumes de données sont testés ici)

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;

Résultats du test :

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

On peut voir qu'il s'agit essentiellement d'un temps logarithmique et que l'efficacité est relativement élevée.

Méthode 3 : Une seule instruction insère plusieurs ensembles de données à la fois

signifie qu'une insertion insère plusieurs valeurs à une fois.

Code du test :

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

Résultat du test :

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

Il ressemble également à un temps logarithmique et est légèrement plus rapide que la méthode 2. Cependant, le problème est qu'il existe une limite de taille de tampon pour une seule instruction SQL. Bien que la configuration puisse être modifiée pour l'agrandir, elle ne peut pas être trop grande. Par conséquent, il ne peut pas être utilisé lors de l’insertion de grandes quantités de données.

Méthode 4 : Importer un fichier de données

Écrire les données numériques dans un fichier de données et l'importer directement (se référer au précédent section).

Fichier de données (a.dat) :

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

Code de test :

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

Résultat du test :

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

Temps le plus rapide, c'est lui. . . .

Ce qui précède est le contenu détaillé de. pour plus d'informations, suivez d'autres articles connexes sur le site Web de PHP en chinois!