Efficiency comparison of four insertion methods in Mysql

This article introduces to you the efficiency comparison of several insertion methods in Mysql through examples, including four methods: item-by-item insertion, transaction-based batch insertion, single statement inserting multiple sets of data at a time, and importing data files. In comparison, the article introduces it in detail through example code. Friends who need it can come down and take a look together.

Preface

Recently, due to work needs, a large amount of data of about 10 million has to be inserted into Mysql, and visual inspection will be time-consuming. So now it's like testing which method to insert data is faster and more efficient.

The following will test the insertion efficiency under different data amounts for each method.

The basics and operations of the test database are as follows:

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)

To facilitate testing, a table is built here with two fields, one is the auto-incremented id, and the other is The string represents the content.

When testing, you must mysql> truncate mytable at the end of each experiment to clear the existing table.

Method 1: Insert one by one

Test code: (There are 1000 insert statements in the middle. It is more convenient to copy and paste with vim. After writing, save it to a.sql, and then enter source a.sql in the mysql prompt)

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;

Output result:

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)

It takes a total of 31.56486s, in fact almost every statement The time it takes is about the same, basically 30ms.

In this way, 1000w of data will take 87h.

As for the larger amount of data, I will not try it. This method is definitely not advisable.

Method 2: Transaction-based batch insertion

In fact, it means putting so many queries in one transaction. In fact, every statement in method one opens a transaction, so it is particularly slow.

Test code: (Basically similar to method 1, mainly adding two lines. Because it is faster, a variety of data volumes are tested here)

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;

Test results:

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

It can be seen that it is basically logarithmic time, and the efficiency is relatively high.

Method 3: A single statement inserts multiple sets of data at once

means one insert inserts multiple values ​​at once.

Test code:

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

Test result:

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

It also seems to be logarithmic time, and it is slightly faster than method 2. However, the problem is that there is a buffer size limit for a single SQL statement. Although the configuration can be modified to make it larger, it cannot be too large. Therefore, it cannot be used when inserting large amounts of data.

Method 4: Import data file

Write the numerical data into a data file and import it directly (refer to the previous section).

Data file (a.dat):

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

Test code:

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

Test result:

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

The one with the fastest time is him. . . .

The above is the detailed content of Efficiency comparison of four insertion methods in Mysql. For more information, please follow other related articles on the PHP Chinese website!