Detailed example code of mysql batch insert loop

background

A few days ago, when I was doing paging on MySql, I saw a blog post saying that using limit 0,10 for paging would cause data loss, while someone else said that would not happen, so I wanted to test it myself. There was no data during the test, so I installed MySql and created a table. When I created a while loop to batch insert 100,000 test data, the execution time was unbearable, so I looked for information to find a way to optimize batch insertion. I made a note here.

Data Structure

Considering that standard columns are divided into three scenarios during paging: primary key column, index column, and common column, the test table needs to include these three scenarios. The table creation syntax is as follows:

drop table if exists `test`.`t_model`;

Create table `test`.`t_model`( 
 `id` bigint NOT NULL AUTO_INCREMENT COMMENT 'Auto-increment primary key',
 `uid` bigint COMMENT 'Business primary key',
 `modelid` varchar(50) COMMENT 'Character primary key',
 `modelname` varchar(50) COMMENT 'name',
 `desc` varchar(50) COMMENT 'Description',
 primary key (`id`),
 UNIQUE index `uid_unique` (`uid`),
 key `modelid_index` (`modelid`) USING BTREE
)ENGINE=InnoDB charset=utf8 collate=utf8_bin;

For ease of operation, the insert operation uses a stored procedure to insert ordered data through a while loop, and the performance of other operation methods or loop methods is not verified.

Execution process

1. Use the simplest method to directly loop and insert 1W items. The syntax is as follows:

drop procedure if exists my_procedure; 
delimiter //
create procedure my_procedure()
begin
 DECLARE n int DEFAULT 1;
 WHILE n < 10001 DO
 insert into t_model (uid,modelid,modelname,`desc`) value (n,CONCAT('id20170831',n),CONCAT('name',n),'desc'); 
 set n = n + 1;
 END WHILE;
end
//　
              delimiter ;

The execution time for inserting 10,000 data items is about 6m7s. At this speed, it will take several days to insert 10 million data items.

2. So, let's consider adding a transaction commit. Can it speed up the performance? The test commits every 1000 records. The syntax is as follows:

delimiter //
create procedure u_head_and_low_pro()
begin
 DECLARE n int DEFAULT 17541;
 WHILE n < 10001 DO
   insert into t_model (uid,modelid,modelname,`desc`) value (n,CONCAT('id20170831',n),CONCAT('name',n),'desc'); 
   set n = n + 1;
   if n % 1000 = 0 
   then
    commit;
   end if;
 END WHILE;
end
//
delimiter ;

The execution time is 6 minutes and 16 seconds, which is not much different from the execution without commit. It seems that the performance of batch insert in this way is very low.

3. Use the stored procedure to generate a batch insert statement to execute batch insert to insert 10,000 records. The syntax is as follows:

drop procedure IF EXISTS u_head_and_low_pro;
delimiter $$
create procedure u_head_and_low_pro()
begin
 DECLARE n int DEFAULT 1;
 set @exesql = 'insert into t_model (uid,modelid,modelname,`desc`) values ​​';
 set @exedata = '';
 WHILE n < 10001 DO
 set @exedata = concat(@exedata,"(",n,",","'id20170831",n,"','","name",n,"','","desc'",")");
 if n % 1000 = 0 
 then
  set @exesql = concat(@exesql,@exedata,";");
  prepare stmt from @exesql;
  execute stmt;
  DEALLOCATE prepare stmt;
  commit; 
  set @exesql = 'insert into t_model (uid,modelid,modelname,`desc`) values ​​';
  set @exedata = "";
 else
  set @exedata = concat(@exedata,',');
 end if;
 set n = n + 1;
 END WHILE;
end;$$ 
delimiter ;

Execution time 3.308s.

Summarize

When inserting in batches, use the insert values ​​batch method to insert, which greatly improves the execution speed.

The above is the detailed example code of MySQL batch insert in loop introduced by the editor. I hope it will be helpful to everyone. If you have any questions, please leave me a message and the editor will reply to you in time. I would also like to thank everyone for their support of the 123WORDPRESS.COM website!
If you find this article helpful, please feel free to reprint it and please indicate the source. Thank you!