Preface
Recently, due to work needs, it is time-consuming to insert a large amount of data into Mysql. So now it's like testing what method to insert data is faster and more efficient.
The following is a test of the insertion efficiency under different data volumes for each method.
The basic and operation 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 convenient testing, a table is built here with two fields, one is the self-incremented id and the other is the string to represent the content.
During the test, every experiment is overmysql> truncate mytable, to clear the existing table.
Method 1: Insert one by one
Test code: (There are 1000 insert statements in the middle, which is more convenient to copy and paste with vim. Save it after writing, and then enter source in 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)
The total time takes 31.56486s. In fact, almost every statement takes about the same time, basically 30ms.
In this way, 1000w data will take 87h.
As for a larger amount of data, I won’t try it, this method is definitely not advisable.
Method 2: Transaction-based batch insertion
In fact, it's just putting so many queries in one transaction. In fact, no statement in the method opens a transaction, so it is particularly slow.
Test code: (It is basically similar to Method 1, mainly add two lines. Because it is relatively fast, 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:
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 in a single statement
It is to insert multiple values at a time with an insert.
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 *
It also looks like logarithmic time, and it is slightly faster than method two. However, the problem is that a single SQL statement has a buffer size limit. Although the configuration can be modified to make it bigger, it cannot be too big. Therefore, it is not used when inserting large batches of data.
Method 4: Import data files
Write the number data into a data file and import it directly (see the previous section).
Data File ():
null value null value ..... null value null value
Test code:
mysql> load data local infile "" into table mytable;
Test results:
Data volume time(s) 1k 0.13 1w 0.75 10w 1.97 100w 6.75 1000w 58.18
The fastest time is him. . . .
Summarize
The above is the entire content of this article. I hope the content of this article will be of some help to your study or work. If you have any questions, you can leave a message to communicate. Thank you for your support.