Comparison of several examples of insertion efficiency in Mysql

Preface

Recently, due to work needs, I need to insert a large amount of data into MySQL, about 1000w, which is estimated to be time-consuming. So now I want to test which method of inserting data is faster and more efficient.

The following tests the insertion efficiency of each method under different data amounts.

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)

For the convenience of testing, a table is created here with two fields, one is the auto-incrementing id and the other is a string representing the content.

During testing, at the end of each experiment, you need to use mysql> truncate mytable 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:

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)

The total time consumed is 31.56486s. In fact, almost every statement takes about the same amount of time, which is basically 30ms.

This way, 10 million data will take 87 hours.

As for larger amounts of data, we won't try it, as this method is definitely not advisable.

Method 2: Transaction-based batch insertion

In fact, it is to put so many queries into one transaction. In fact, each statement in method 1 opens a transaction, so it is very slow.

Test code: (Basically similar to method 1, mainly adding two lines. Since it is relatively fast, various 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:

Data volume time (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: Insert multiple sets of data at a time with a single statement

That is, an insert inserts multiple values ​​at a time.

Test code:

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

Test results:

Data volume time (s)
1k 0.15
1w 0.80
10w 2.14
100w *

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

Method 4: Import data files

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 results:

Data volume time (s)
1k 0.13
1w 0.75
10w 1.97
100w 6.75
1000w 58.18

He is the fastest one. . . .

Summarize

The above is the full content of this article. I hope that the content of this article can bring some help to your study or work. If you have any questions, you can leave a message to communicate. Thank you for your support of 123WORDPRESS.COM.