1、創(chuàng)建測試表
CREATE TABLE `mysql_genarate` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`uuid` varchar(50) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=5999001 DEFAULT CHARSET=utf8;
2、創(chuàng)建一個(gè)循環(huán)插入的存儲過程
CREATE DEFINER=`root`@`localhost` 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
調(diào)用測試call test_two1(), 測試10000條數(shù)據(jù)耗時(shí)幾分鐘，如果是千萬級數(shù)據(jù)，這個(gè)速度將無法忍受。
3、優(yōu)化存儲過程
使用批量插入的sql語句
CREATE DEFINER=`root`@`localhost` PROCEDURE `insertPro`( IN sum INT )
BEGIN
DECLARE 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
調(diào)用 call insertPro(10000) ，耗時(shí)零點(diǎn)幾秒，這個(gè)速度可以接受。
以上就是MySQL循環(huán)插入千萬級數(shù)據(jù)的詳細(xì)內(nèi)容，更多關(guān)于MySQL循環(huán)插入的資料請關(guān)注腳本之家其它相關(guān)文章！
您可能感興趣的文章:- .Net Core導(dǎo)入千萬級數(shù)據(jù)至Mysql的步驟
- .Net Core導(dǎo)入千萬級數(shù)據(jù)至Mysql數(shù)據(jù)庫的實(shí)現(xiàn)方法
- 詳解MySQL數(shù)據(jù)庫千萬級數(shù)據(jù)查詢和存儲
- mysql千萬級數(shù)據(jù)量根據(jù)索引優(yōu)化查詢速度的實(shí)現(xiàn)
- MySQL 千萬級數(shù)據(jù)量如何快速分頁
- mysql千萬級數(shù)據(jù)分頁查詢性能優(yōu)化
- mysql千萬級數(shù)據(jù)大表該如何優(yōu)化?
- MySQL單表千萬級數(shù)據(jù)處理的思路分享
