mysql利用預存程序批量插入資料

標籤：mysql   資料庫   

最近需要測試一下mysql單表資料達到1000W條以上時增刪改查的效能。由於沒有現成的資料，因此自己構造，本文只是執行個體，以及簡單的介紹。

首先當然是建表：

CREATE TABLE `fortest` (  `ID` INT(30) UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,  `IP` VARCHAR(32) NOT NULL,  `OID` VARCHAR(15) DEFAULT NULL)

其次，構建預存程序：

DELIMITER $$USE `插入表所在的資料庫名字`$$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)      DO        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 ;

上述預存程序指定了兩個輸入參數：IP_NUM  OID_NUM，兩個參數分別指定了有多少台機器，以及每台機器有多少OID。

之後調用預存程序就可以了：

call autoinsert 1000 50

意思是，有100台機器，每個機器有50個參數。

這樣，我們就構建了50000條資料，如果按上述預存程序，想達到1000W的資料，還是要花點時間的。可以採用如下方法，進一步提高速度：

首先建立具有同樣表結構的表：

CREATE TABLE fortest_2 LIKE fortest;

然後根據fortest表插入5W條資料

INSERT INTOfortest_2(IP,OID) SELECT IP,ROUND(RAND() * 100) + 1) FROM fortest;

上述一條語句執行速度特別快，瞬間就插入了5W條資料。可以寫個指令碼執行：

#!/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() * 100) + 1) FROM fortest;"    echo "INSERT $i "        i=$(($i+1))#    sleep 0.05doneexit 0

 

 

mysql利用預存程序批量插入資料