MySQL bulk inserts data using stored procedures

Recently need to test MySQL single table data up to 1000W or more when adding or removing the performance of the changes. Because there is no ready-made data, so build yourself, this article is just an example, as well as a simple introduction.

First of all, of course, to build tables:

CREATE TABLE ' fortest ' (' ID ' INT () UNSIGNED not NULL auto_increment PRIMARY KEY, ' IP ' VARCHAR (+) NOT null, ' OID ' VA Rchar () DEFAULT NULL)

Second, build the stored procedure:

DELIMITER $ $USE ' Insert Table database name ' $ $DROP PROCEDURE IF EXISTS ' autoinsert ' $ $CREATE definer= ' root ' @ ' 192.168.137.10 ' PROCEDURE ' AutoInsert ' (in Ip_num int, in oid_num int) BEGIN  DECLARE IIP int DEFAULT 0;  DECLARE ioid INT DEFAULT 0;      while (IIP < ip_num)    do      SET ioid = 0;      while (Ioid<oid_num)      does        SET @mySql =concat ("INSERT into Fortest (IP, OID) VALUES (CONCAT ((ROUND (RAND () * 255) + 1), '. ', (ROUND (rand () * 255) + 1), '. ', (ROUND (rand () * 255) + 1), '. ', (ROUND (rand () * 255) + 1)), ROUND (rand () *100) +1); ");                    PREPARE stmt from @mySql;        EXECUTE stmt;          Deallocate PREPARE stmt;        SET IIP = iip+1;      END while;        SET IPC = ipc+1;    END while;    end$ $DELIMITER;

The stored procedure above specifies two input parameters: Ip_num oid_num, two parameters specify how many machines are, and how many OIDs each machine has.

You can then call the stored procedure:

Call AutoInsert 1000 50

It means that there are 100 machines, each with 50 parameters.

In this way, we build 50,000 of data, if the above stored procedures, to achieve 1000W of data, it will take some time. The following methods can be used to further improve the speed:

First create a table with the same table structure:

CREATE TABLE fortest_2 like fortest;

Then insert 5W data according to the Fortest table

INSERT intofortest_2 (ip,oid) SELECT Ip,round (RAND () * + 1) from Fortest;

The above statement performs exceptionally fast and inserts 5W of data in an instant. You can write a script execution:

#!/bin/bashi=1; Max_insert_row_count=$1;j=0;while [$i-le $MAX _insert_row_count]do time    mysql-h192.168.137.1-uroot-p123456 Fortest-e "INSERT intofortest_2 (ip,oid) Select Ip,round (RAND () *) + 1) from fortest;"    echo "INSERT $i"        i=$ (($i + 1)) #    sleep 0.05doneexit 0

MySQL bulk inserts data using stored procedures