Share tips on optimizing insert into statements

MYSQL batch insert database to achieve statement performance analysis

Assume that our table structure is as follows

The code is as follows

CREATE TABLE example (
example_id INT NOT NULL,
name VARCHAR( 50 ) NOT NULL,
value VARCHAR( 50 ) NOT NULL,
other_value VARCHAR( 50 ) NOT NULL
)

Normally we will write a single inserted SQL statement like this :

The code is as follows

INSERT INTO example
(example_id, name, value, other_value)
VALUES
(100, 'Name 1', 'Value 1', 'Other 1');

Mysql allows us to insert data in batches in a sql statement, as follows sql statement:

The code is as follows

INSERT INTO example
(example_id, name, value, other_value)
VALUES
(100, 'Name 1', 'Value 1', 'Other 1'),
(101, 'Name 2', 'Value 2', 'Other 2'),
(102, 'Name 3', 'Value 3', 'Other 3'),
(103, 'Name 4', 'Value 4', 'Other 4');

If we insert If the order of the columns is consistent with the order of the columns in the table, you can also omit the definition of the column names. The following sql

code is as follows

INSERT INTO example
VALUES
(100, 'Name 1', 'Value 1', 'Other 1'),
(101, 'Name 2', 'Value 2', 'Other 2'),
(102, 'Name 3', 'Value 3', 'Other 3'),
(103, 'Name 4', 'Value 4', 'Other 4');

The above does not seem to be a problem, let me use the sql statement below The optimization tips will be tested separately below. The goal is to insert 2 million pieces of data into an empty data table

The first method: Use insert into, the code is as follows:

$params = array('value'=>'50');
set_time_limit(0);
echo date("H:i:s");
for($i=0;$i<2000000;$i++){
$connect_mysql->insert($params);
};
echo date("H:i:s");

The final display is: 23:25:05 01:32:05, which means it took more than 2 hours!

Second method: Use transaction submission and batch insert The last displayed time consumed by the database (submitted every 10W items) is: 22:56:13 23:04:00, a total of 8 minutes and 13 seconds. The code is as follows:

echo date("H:i:s");
$connect_mysql->query('BEGIN');
$params = array('value'=>'50');
for($i=0;$i<2000000;$i++){ 
$connect_mysql->insert($params);
if($i%100000==0){
$connect_mysql->query(&#39;COMMIT&#39;);
$connect_mysql->query(&#39;BEGIN&#39;);
}
}
$connect_mysql->query(&#39;COMMIT&#39;);
echo date("H:i:s");

The third method : Use optimized SQL statements: splice the SQL statements, use insert into table () values ​​(),(),(),() and then insert them all at once. If the string is too long,

Then you need to configure MYSQL and run it in the mysql command line: set global max_allowed_packet = 2*1024*1024*10; The consumption time is: 11:24:06 11:25:06;

Insert 200W records It only took 1 minute to test the data! The code is as follows:

$sql= "insert into twenty_million (value) values";
for($i=0;$i<2000000;$i++){
$sql.="(&#39;50&#39;),";
};
$sql = substr($sql,0,strlen($sql)-1);
$connect_mysql->query($sql);

Finally, to summarize, the first method is undoubtedly the worst when inserting large batches of data, while the second method is worse in practical applications. Widely, the third method is more suitable when inserting test data or other low requirements, and it is really fast.

【Related Recommendations】

1. Share a tutorial for inserting multiple records in batches using an insert statement

2. Commonly used in parsing mysql Three insert statements and their differences

The above is the detailed content of Share tips on optimizing insert into statements. For more information, please follow other related articles on the PHP Chinese website!