The method of inserting tens of millions of data into mysql Database

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

1. Build 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

two。 Create stored procedures that are inserted in a loop

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 #

Using call test_two1 (); testing, 3000 pieces of data took 74 seconds, which would be unbearable for tens of millions of data.

So I looked for an optimization method on the Internet and found that the sql statements inserted in batches could be spliced together, and the speed was greatly improved.

3. Optimized stored procedure

CREATE PROCEDURE insertPro (in sum INT) BEGINDECLARE count INT DEFAULT 0TracterDECLARE i INT DEFAULT 0Shiset @ exesql = concat ("insert into mysql_genarate (uuid) values"); set @ exedata = ""; set count=0;set iDeposit while count0 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 an acceptable speed.

Then call call insertPro (30000000); test 30 million data inserts, and the result takes 1824.203s (30 minutes). The speed of the personal computer is enough.

In addition, java multithread is used to assemble sql at the same time, and every 10000 threads are submitted once. In the case of 8 threads running at the same time, 30 million data inserts take 336 seconds, and 100 million data inserts take 1087 seconds.

These are the details of how mysql implements circular insertion of tens of millions of levels of data, please pay attention to other related articles!