selectした大量データをinsertしたい

mySQLでselectした100万件近くをinsertしたいのですが
insert selectは遅いので別の方法を考えています。

プロシージャを作成してselectした結果で
バルクinsertをしようと思うのですがどう書けばいいか、よくわからず。

どなたか教えていただけないでしょうか。

mySQL初心者です。
よろしくお願いします。

質問者からの補足コメント

• 

  回答ありがとうございます。
  
  プロシージャ内で、大量データをselectして
  数件ずつバルクinsertしたいのですがどう書いていいかわからず・・・。
  
  もしくは、大量にselectした結果を高速にinsertできる別の方法があれば
  教えていただきたいです・・・。

    補足日時：2016/05/08 02:18

No.4 回答者： yambejp 回答日時： 2016/05/11 15:25

補足、プロシージャを使って10のn乗件単位でやるならこうかな

（思ったより速くなったけど、まだ改善の余地あり）
一度バルクSQLをつくってから流し込むので
あまり長すぎるSQLを書くとオーバーフローする可能性があります。
要素数やレコード長によりますが1000件単位くらいが妥当かなと

DROP PROCEDURE IF EXISTS MYPROCEDURE4;
DELIMITER //
CREATE PROCEDURE MYPROCEDURE4()
BEGIN
DECLARE a BLOB;
DECLARE done INT DEFAULT 0;
DECLARE CUR CURSOR FOR
SELECT
CONCAT('INSERT IGNORE INTO `tbl2` VALUES('
,GROUP_CONCAT('\'',id,'\',\'',data,'\',\'',val,'\'' separator '),(')
,')') as x
FROM `tbl1`
GROUP BY TRUNCATE(id,-2);/* PER1=0, PER10=-1, PER100=-2, PER1000=-3*/
DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
SET group_concat_max_len = 10000000;
OPEN CUR;
REPEAT
FETCH CUR INTO a;
IF NOT done THEN
SET @sql=a;
PREPARE stmt from @sql;
EXECUTE stmt;
END IF;
UNTIL done END REPEAT;
CLOSE CUR;
END
//
DELIMITER ;
TRUNCATE `tbl2`;
CALL MYPROCEDURE4;

No.3 回答者： yambejp 回答日時： 2016/05/09 17:28

試しにプロシージャをつかってテストをしてみました

パターンは３つ
(1)1レコードごとに投入を繰り返す
(2)複数レコードをまとめて投入を繰り返す
(3)INSERT ・・・ SELECT で投入する

質問者さんは(3)は遅いとのことでしたが
実際やってみたところ早い順 (3)>(1)>(2)でした
実際圧倒的に(3)が速いようなので、プロシージャではあきらめて#2で提案したように
mysqldumpなどで処理する方がよいかもしれませんね。
(プロシージャだとCONCATで同じデータをくりかえし処理するので無駄がおおい？)

テスト
CREATE TABLE `tbl1`(`id` INT NOT NULL UNIQUE,`data` VARCHAR(100),`val` DOUBLE);
CREATE TABLE `tbl2`(`id` INT NOT NULL UNIQUE,`data` VARCHAR(100),`val` DOUBLE);

※1000データをつくる
DROP PROCEDURE IF EXISTS MYPROCEDURE1;
DELIMITER //
CREATE PROCEDURE MYPROCEDURE1()
BEGIN
DECLARE i INT DEFAULT 0;
WHILE i<1000 DO
INSERT IGNORE INTO tbl1 VALUES(i,'x',RAND());
SET i=i+1;
END WHILE;
END
//
DELIMITER ;
TRUNCATE `tbl2`;
CALL MYPROCEDURE1;

(1)1レコード毎投入：平均して1秒前後
DROP PROCEDURE IF EXISTS MYPROCEDURE2;
DELIMITER //
CREATE PROCEDURE MYPROCEDURE2()
BEGIN
DECLARE a INT;
DECLARE b VARCHAR(100);
DECLARE c DOUBLE;
DECLARE done INT DEFAULT 0;
DECLARE CUR CURSOR FOR
SELECT id,data,val FROM tbl1;
DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
OPEN CUR;
REPEAT
FETCH CUR INTO a,b,c;
SET @sql='INSERT IGNORE INTO `tbl2` VALUES ';
SET @sql=CONCAT(@sql,'(\'',a,'\',\'',b,'\',\'',c,'\')');
PREPARE stmt from @sql;
EXECUTE stmt;
UNTIL done END REPEAT;
CLOSE CUR;
END
//
DELIMITER ;
TRUNCATE `tbl2`;
CALL MYPROCEDURE2;

(2)100レコードをまとめて投入を繰り返す：平均して1.4秒くらい
DROP PROCEDURE IF EXISTS MYPROCEDURE3;
DELIMITER //
CREATE PROCEDURE MYPROCEDURE3()
BEGIN
DECLARE a INT;
DECLARE b VARCHAR(100);
DECLARE c DOUBLE;
DECLARE count INT DEFAULT 0;
DECLARE done INT DEFAULT 0;
DECLARE CUR CURSOR FOR
SELECT id,data,val FROM tbl1;
DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
OPEN CUR;
REPEAT
FETCH CUR INTO a,b,c;
IF count>100 THEN
SET count=0;
END IF;
IF count=0 THEN
SET @sql='INSERT IGNORE INTO `tbl2` VALUES ';
ELSE
SET @sql=CONCAT(@sql,',');
END IF;
SET @sql=CONCAT(@sql,'(\'',a,'\',\'',b,'\',\'',c,'\')');
PREPARE stmt from @sql;
EXECUTE stmt;
SET count=count+1;
UNTIL done END REPEAT;
CLOSE CUR;
END
//
DELIMITER ;
CALL MYPROCEDURE3;

(3)INSERT ・・・ SELECT で投入：平均0.01秒くらい
TRUNCATE `tbl2`;
INSERT tbl2 SELECT * FROM tbl1;

No.2 回答者： yambejp 回答日時： 2016/05/08 10:26

ああ、ごめんなさい

プロシージャからでしたね

ちょっと違うけどmysqldumpで抽出してリダイレクトで
投入した方がいいかなぁ

No.1 回答者： yambejp 回答日時： 2016/05/06 21:41

＞プロシージャを作成してselectした結果で

＞バルクinsertをしようと思うのですが

その通りやればいいのでは？
ちなみに100万件のデータをどうやって取ってくるつもりか
書いていないので、書きようがないのですが
たぶん１発でやるといろいろオーバーフローしそうなので、
1万件ずつくらいで分割しながらやるとよいです

INSERT INTO テーブル VALUESに対して(?,?,・・・・)という受け皿をつくって

$stmt = $pdo->prepare( $query);
$stmt->execute($data);
のようにすればよいでしょう。