How to insert tens of millions of data in mysql loop

#How does mysql implement circular insertion of tens of millions of data?

1. Create a table:

CREATE TABLE `mysql_genarate` (  
`id` int(11) NOT NULL AUTO_INCREMENT,  
`uuid` varchar(50) DEFAULT NULL,  
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=5990001 DEFAULT CHARSET=utf8

2. Create a stored procedure for loop insertion

create procedure test_two1()
     begin
        declare i int default 0;       while i < 3000 do
            INSERT into mysql_genarate(uuid) VALUES(UUID());            set i = i + 1;
        end while;
    end #

Use call test_two1(); to test, 3000 data consumption It takes 74 seconds. If there is tens of millions of data, this speed will be unbearable.

So I looked for optimization methods on the Internet and found that I can splice batch-inserted SQL statements, and the speed is greatly improved;

3. Optimized stored procedures

CREATE PROCEDURE insertPro(in sum INT)BEGINDECLARE count INT DEFAULT 0;DECLARE i INT DEFAULT 0;
set @exesql = concat("insert into mysql_genarate(uuid) values");
set @exedata = "";
set count=0;
set i=0;while count<sum do 
    set @exedata = concat(@exedata, ",(UUID())");
    set count=count+1;
    set i=i+1;    if i%1000=0
    then 
        set @exedata = SUBSTRING(@exedata, 2);
        set @exesql = concat("insert into mysql_genarate(uuid) values ", @exedata);
        prepare stmt from @exesql;
        execute stmt;        DEALLOCATE prepare stmt;
        set @exedata = "";    end if;end while;if length(@exedata)>0 then 
    set @exedata = SUBSTRING(@exedata, 2);
    set @exesql = concat("insert into mysql_genarate(uuid) values ", @exedata);
    prepare stmt from @exesql;
    execute stmt;    DEALLOCATE prepare stmt;end if;end;

Calling call insertPro(3000) takes a few tenths of a second, which is acceptable.

Then call call insertPro(30000000);Test 30 million pieces of data insertion, the result is 1824.203s (30 minutes). This speed of a personal computer is fine.

In addition, using Java multi-threading to splice SQL simultaneously and submitting every 10,000 items, when 8 threads are running at the same time, it takes 336 seconds to insert 30 million pieces of data and 336 seconds to insert 100 million pieces of data. 1087s.

Related references:MySQL Tutorial

The above is the detailed content of How to insert tens of millions of data in mysql loop. For more information, please follow other related articles on the PHP Chinese website!