高速にテーブルの移動を行いたい

Question

DROP PROCEDURE IF EXISTS p1;

CREATE PROCEDURE p1()
BEGIN
	DECLARE done INT DEFAULT 0;
	DECLARE item_id INT;
	DECLARE cur CURSOR FOR SELECT item_id FROM table1 WHERE category=1;
	DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
	OPEN cur;

	WHILE done = 0 DO
		FETCH cur INTO inviteCode;
		
		# table2に挿入する
		INSERT INTO table2 (SELECT * FROM table1 WHERE table1.item_id = item_id);
		# table1から消す
		DELETE FROM table1 WHERE table1.item_id = item_id;

	END WHILE;

	CLOSE cur;
END;

CALL p1();

このようなプロシージャでcategory=1のデータをtable1からtable2に移動させています。
データが1,000行/秒ほどで移動されているのですが、移動対象のデータが5,000万行あります。

できるだけ早くテーブルの移動を行いたいのですがどこを改善すればよろしいでしょうか。

Answer 1

table1を2回SELECTしているのを1回にまとめてみては？

DECLARE cur CURSOR FOR SELECT item_id FROM table1 WHERE category=1;

                              ↓

DECLARE cur CURSOR FOR SELECT ***** FROM table1 WHERE category=1;

 INSERT INTO table2 (SELECT * FROM table1 WHERE table1.item_id = item_id);

                              ↓
      INSERT INTO table2 **VALUES(....)** ;

table1.item_id が AUTOINCREMENT のように現在より大きな値が設定されることが保障されているなら、

SELECT MAX(item_id) FROM table1 WHERE category=1);
でitem_idの最大値を求めて、

INSERT INTO table2 (SELECT * FROM table1 WHERE category=1 AND item_id <= item_idの最大値);

DELETE FROM table1 WHERE category=1 AND item_id <= item_idの最大値;
で対応できるかもしれません。

Answer 2

category=1のデータを入れるならwhileせずに一気にやった方が早そうな気がします。(大して効果ないかもしれないけど)
x INSERT INTO table2 (SELECT * FROM table1 WHERE table1.item_id = item_id);
○ INSERT INTO table2 (SELECT * FROM table1 WHERE table1.category=1);

insertとdeleteどちらが時間かかってるかも気になりますね。
＃DELETE FROM table1 WHERE table1.item_id = item_id;
をwhile毎にやらずに、CLOSE cur;後に一気にdeleteしてもいいような気がします。
ただ、件数が多いとdeleteは非常に重いので、table1.categoryにパーティションを付けて最後に対象のパーティションをDROPするという事が出来る条件なら試してみてもいいかもしれません。

まあ、whileする必要あるのかな？って。

処理前にauto commitをoff、最後にcommitすれば高速化するかもしれないですがPROCEDUREで試した事はないです。
用途はよくわからないですが、table2にindex付けないとか。

Comments

[aglkjggg]

回答ありがとうございます。

`table1`のデータは常に増え続けているため`INSERT`した後に`DELETE`すると`table2`に移っていないデータまで消えてしまう恐れがあるためwhileで処理しました。

AUTO COMMIT OFFの件試してみます。