たまたま目に入った記事で、「mysqlでuserテーブルにダミーデータを10万件ほど入れる方法メモ」というものを拝読しました。
zenn.dev
そちらでは、ストアド・プロシジャを作成して10万回のループでINSERTを実施する方法を採っていましたが、折角の機会なのでお伝えしたいテクニックがあり、これを書きしたためる次第。
プロシジャってあまり使いたくない
「実現できない」より「実現できる」ほうが100万倍エラいので、プロシジャを使った解法にたどり着いたことは立派なのですが、「実現できる」から「もっと良く実現できる」に進むと更に楽しい世界が待っています。ということで「もっと良く」を紹介します。
私はこの手の処理にあまりプロシジャ使用を選択したくないのは：
- 使い捨ての処理なのに、プロシジャというオブジェクトをサーバ上に生成したり、消し忘れてゴミオブジェクトとして残ったりするのがイヤ
- そもそもプロシジャ作るのが面倒（普段あまり作らないので）。デリミタ一時的に変えるとかからして、無駄な作業をしている感がキライ
- ぐるぐる系の処理はヤだ（INSERTを 10万回もやるなんて全然DB的発想ではない）
といった理由があります。実際、このプロシジャ版を手元で動かしたところ、4分近くかかりました。
テーブル定義とプロシジャおよび実行方法を https://zenn.dev/mesi/scraps/48b6479d21e00d から引用します：
CREATE TABLE `user` ( `id` INT AUTO_INCREMENT PRIMARY KEY, `name` VARCHAR(50), `email` VARCHAR(100), `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP );
DELIMITER $$
CREATE PROCEDURE InsertDummyData()
BEGIN
DECLARE i INT DEFAULT 1;
WHILE i <= 100000 DO
INSERT INTO `user` (`name`, `email`)
VALUES (CONCAT('User ', i), CONCAT('user', i, '@example.com'));
SET i = i + 1;
END WHILE;
END$$
DELIMITER ;CALL InsertDummyData();
それ、CTEでできるよ！
こういう処理を行いたいときに便利なのが、CTEです。
白状すると、私もこの構文の書き方をすぐに忘れてしまうので、書く前に毎度検索しています(笑)。
set @@cte_max_recursion_depth=100000
INSERT INTO user (name, email)
WITH RECURSIVE num(n) AS (SELECT 1 UNION ALL SELECT n+1 FROM num WHERE n<100000)
SELECT CONCAT('User ',n), concat('user',n,'pexample.com') FROM num;最初の set文は、cteの実行可能段数を緩和するための指定です。
笑っちゃうと思うんですけど（というか盛大に笑っちゃってほしいんですけど）、先ほど4分かかったこの処理が、どれくらいの時間でできると思います？
手元の環境では、 0.6秒程度でした。
私が若手の頃に、1時間半近くかかっていたクエリ（PL/SQL）が、ちょっと考えて処理の仕方を工夫したらヒトケタ分になったこと（その後更に改善して1分以内になったはず）という、工夫次第でダイナミックに効果が出る体験をしたことがきっかけで、DBMSの世界って面白いなと感じたので、これはそれに匹敵するくらいの差と言えるんじゃないかなと思います。
まとめ
ということで、
- ぐるぐる系よりガッツリ系
- CTE知ってると便利
- 構文覚えてさらさら書けると格好良いけど私は覚えてないｗ
でした。
