Detailed Explanation of Example Code of mysql Circular Batch Insertion

Background

A few days ago, when doing paging on MySql, I saw a blog post saying that there would be data loss when using limit 0 and 10 paging, and some people said no, so I wanted to test it myself. When there was no data during the test, an MySql was installed and a table was built. When an while cycle was built to insert 10W test data in batches, the execution time was unbearable, so I checked the data to find the optimization method of batch insertion. Take a note here.

Data structure

Thinking about paging, the standard column is divided into three scenarios: primary key column, index column and ordinary column. Therefore, the test table needs to include these three scenarios. The syntax for building the table is as follows:

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

Create table `test`.`t_model`( 
 `id` bigint NOT NULL AUTO_INCREMENT COMMENT ' Self-increasing primary key ',
 `uid` bigint COMMENT ' Business primary key ',
 `modelid` varchar(50) COMMENT ' Character primary key ',
 `modelname` varchar(50) COMMENT ' Name ',
 `desc` varchar(50) COMMENT ' Describe ',
 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 stored procedures to insert ordered data through an while loop, without verifying the performance of other modes of operation or loop.

Execution process

1. Insert 1W directly in a single loop in the simplest way. 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 ;

Insert 1W data, and the execution time is about 6m7s. At this speed, it is estimated that it will take several days to insert 1000W data.

2. So, can you speed up the performance by adding a transaction commit? Test every 1000 pieces under commit1, and 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 min 16 sec, which is not much different from that without commit. It seems that the performance of batch insertion in this way is very low.

3. Use stored procedures to generate batch insert statements to execute batch insert insert 1W. 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 is 3.308 s.

Summarize

In batch insertion, values batch insertion of insert is used, and the execution speed is greatly improved.