不要在RDBMS中特别使用这种规模的循环。
尝试使用查询快速填充1m行的表
INSERT INTO `entity_versionable` (fk_entity, str1, str2, bool1, double1, date)SELECt 1, 'a1', 100, 1, 500000, '2013-06-14 12:40:45' FROM(select a.N + b.N * 10 + c.N * 100 + d.N * 1000 + e.N * 10000 + f.N * 100000 + 1 Nfrom (select 0 as N union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) a , (select 0 as N union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) b , (select 0 as N union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) c , (select 0 as N union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) d , (select 0 as N union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) e , (select 0 as N union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) f) t
我的机器（MacBook Pro 16GB RAM，2.6Ghz Intel Core i7）花了大约8秒的时间才能完成
查询正常，受影响的1000000行（7.63秒）记录：1000000重复项：0警告：0
UPDATE1 现在使用预备语句的存储过程版本
DELIMITER $$CREATE PROCEDURE `inputRowsNoRandom`(IN NumRows INT)BEGIN DECLARE i INT DEFAULT 0; PREPARE stmt FROM 'INSERT INTO `entity_versionable` (fk_entity, str1, str2, bool1, double1, date) VALUES(?, ?, ?, ?, ?, ?)'; SET @v1 = 1, @v2 = 'a1', @v3 = 100, @v4 = 1, @v5 = 500000, @v6 = '2013-06-14 12:40:45'; WHILE i < NumRows DO EXECUTE stmt USING @v1, @v2, @v3, @v4, @v5, @v6; SET i = i + 1; END WHILE; DEALLOCATE PREPARE stmt;END$$DELIMITER ;
在约3分钟内完成：
mysql> CALL inputRowsNoRandom（1000000）;查询正常，受影响的0行（2分钟51.57秒）
感觉差异8秒vs 3分钟
UPDATe2 为了加快速度，我们可以显式使用事务并批量提交插入。因此，这里是SP的改进版本。
DELIMITER $$CREATE PROCEDURE inputRowsNoRandom1(IN NumRows BIGINT, IN BatchSize INT)BEGIN DECLARE i INT DEFAULT 0; PREPARE stmt FROM 'INSERT INTO `entity_versionable` (fk_entity, str1, str2, bool1, double1, date) VALUES(?, ?, ?, ?, ?, ?)'; SET @v1 = 1, @v2 = 'a1', @v3 = 100, @v4 = 1, @v5 = 500000, @v6 = '2013-06-14 12:40:45'; START TRANSACTION; WHILE i < NumRows DO EXECUTE stmt USING @v1, @v2, @v3, @v4, @v5, @v6; SET i = i + 1; IF i % BatchSize = 0 THEN COMMIT; START TRANSACTION; END IF; END WHILE; COMMIT; DEALLOCATE PREPARE stmt;END$$DELIMITER ;
不同批次大小的结果：
mysql> CALL inputRowsNoRandom1（1000000,1000）;查询正常，受影响的0行（27.25秒）mysql> CALL inputRowsNoRandom1（1000000,10000）;查询正常，受影响的0行（26.76秒）mysql> CALL inputRowsNoRandom1（1000000,100000）;查询正常，受影响的0行（26.43秒）
您自己会看到差异 。仍然比交叉联接差3倍以上。
