ごきげんよう。毎週金曜日は欠かさずランチにカレーを食べています、サーバー担当のgonzoです😺
今回はRDB（リレーショナルデータベース）にテスト用の大量ダミーデータを流し込む方法についてご紹介します。
さて、世の中のWebサービスはリリースされる際、事前に意図したとおりのシステムになっているか、稼働時の負荷に耐えられるかどうか等をテストするのが常です。
そのテストでは実際にユーザーの行動によって生成される内容を模したダミーのユーザーデータを、データベースにあらかじめ投入して動かすという事が一般的となっています。
テストの度にデータを初期化したり、複数の試験環境で使いまわしたりなど、データの入れ直しは日常茶飯事です。
そのデータ投入に関して、規模の小さいサービスならまだしも、ターゲットユーザー数が多い場合はとても時間がかかってしまうという問題があります。
今回はそのダミーデータの投入にかかる時間をいかにして短くできるのか、という工夫についていくつか挙げてみます。
前提
MySQLデータベースに100万レコードのユーザーデータを投入します。
各方法における実行時間の比較結果は記事の最後の方に記載しています。
DBサーバーとクライアント
今回は社内LANにある下記スペックのPCにDockerを使用して、MySQLサーバーを構築しました。
クライアントにはWindows用のGUIツールであるHeidiSQLを使用しています。
- CPU: Core i7-4790 @ 3.60GHz
- RAM: 16GB
- OS: AlmaLinux 9.1
- DB: MySQL 8.0.31
- クライアント: HeidiSQL 12.4.0.6659
テーブル構造
対象とするテーブルは下記の定義で構築したものを使います。
非常にシンプルな例ですが、もっとカラムが多いテーブルやレコードあたりの容量が大きい場合は投入に必要な時間が増加します。
CREATE TABLE `users` (
`id` BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT 'ユーザーID',
`name` VARCHAR(64) NOT NULL COMMENT '名前',
`last_login_at` DATETIME NOT NULL COMMENT '最終ログイン日時',
`status` TINYINT(3) UNSIGNED NOT NULL DEFAULT '1' COMMENT 'アカウントの状態',
`created_at` DATETIME NOT NULL COMMENT '作成日時',
`updated_at` DATETIME NOT NULL COMMENT '更新日時',
PRIMARY KEY (`id`) USING BTREE
)
(1) CSVインポート
一番わかりやすい方法だと思います。
あらかじめExcelなどを使い、100万件のデータが入ったCSVファイルを作成してサーバーに流し込みます。
レコードが大きいとCSVファイルも巨大になる可能性があるので、取り回しが困難になりがちです。
また、CSVファイル中身を転送する必要があるのでネットワークの速度にも依存しやすいです。
本記事で使用したCSVファイルは約95MBになりました。
"id","name","last_login_at","status","created_at","updated_at"
"1","dummy-name-1","2022-11-20 07:28:55","1","2022-11-14 00:50:44","2022-11-14 00:50:44"
"2","dummy-name-2","2022-11-15 20:20:21","1","2022-11-13 22:55:53","2022-11-13 22:55:53"
"3","dummy-name-3","2022-11-14 07:06:26","1","2022-11-13 20:05:09","2022-11-13 20:05:09"
"4","dummy-name-4","2022-11-17 23:59:13","1","2022-11-12 07:14:56","2022-11-12 07:14:56"
...
(2) ストアドプロシージャを使う
クリックしてコードを見る
SET @userNum = 1000000;
SET @base_datetime = '2022-11-10 00:00:00';
SET @deltaSec = 60*60*24*7;
DROP PROCEDURE IF EXISTS insert_users_data;
delimiter //
CREATE PROCEDURE insert_users_data()
BEGIN
SET @query = CONCAT('INSERT INTO users VALUES (?, ?, ?, ?, ?, ?)');
PREPARE stmt FROM @query;
/* ランダムデータの作成・挿入 */
SET @loopCount = 0;
WHILE @loopCount < @userNum DO
SET @id = @loopCount + 1;
SET @`name` = CONCAT('dummy-name-', @id);
SET @`status` = 1;
SET @created_at = ADDTIME(@base_datetime, SEC_TO_TIME(FLOOR(RAND() * @deltaSec)));
SET @updated_at = @created_at;
SET @last_login_at = ADDTIME(@created_at, SEC_TO_TIME(FLOOR(RAND() * @deltaSec)));
EXECUTE stmt USING @id, @`name`, @last_login_at, @`status`, @created_at, @updated_at;
SET @loopCount = @loopCount + 1;
END WHILE;
DEALLOCATE PREPARE stmt;
END;
//
delimiter ;
CALL insert_users_data;
DROP PROCEDURE IF EXISTS insert_users_data;
ストアドプロシージャを使って、ランダムデータを含むレコード生成しながら書き込んでいきます。
プログラムで1レコード毎に異なるランダムデータを生成することができますが、このまま愚直にやるととても遅いので、おすすめできません。
(3) バルクインサートで高速化
クリックしてコードを見る
SET @userNum = 1000000;
SET @bulkNum = 1000;
SET @base_datetime = '2022-11-10 00:00:00';
SET @deltaSec = 60*60*24*7;
DROP PROCEDURE IF EXISTS insert_users_data;
delimiter //
CREATE PROCEDURE insert_users_data()
BEGIN
SET @loopCount = 0;
WHILE @loopCount < @userNum DO
SET @query = CONCAT('INSERT INTO users VALUES ');
SET @delimiter = '';
/* ランダムデータの作成・連結 */
SET @bulkLoopCount = 0;
WHILE @bulkLoopCount < @bulkNum DO
SET @id = @loopCount + @bulkLoopCount + 1;
SET @`name` = CONCAT('dummy-name-', @id);
SET @`status` = 1;
SET @created_at = ADDTIME(@base_datetime, SEC_TO_TIME(FLOOR(RAND() * @deltaSec)));
SET @updated_at = @created_at;
SET @last_login_at = ADDTIME(@created_at, SEC_TO_TIME(FLOOR(RAND() * @deltaSec)));
SET @`value` = CONCAT_WS(',', @id, QUOTE(@`name`), QUOTE(@last_login_at), @`status`, QUOTE(@created_at), QUOTE(@updated_at));
SET @query = CONCAT(@query, @delimiter, '(', @`value`, ')');
SET @delimiter = ',';
SET @bulkLoopCount = @bulkLoopCount + 1;
END WHILE;
/* バルクインサート */
PREPARE stmt FROM @query;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
SET @loopCount = @loopCount + @bulkNum;
END WHILE;
END;
//
delimiter ;
CALL insert_users_data;
DROP PROCEDURE IF EXISTS insert_users_data;
前述の方法を効率よくしたものです。
INSERTでは1回で複数のレコードをまとめて書き込めるので、1000レコードずつ書き込むようにします。
これは『バルクインサート』と呼ばれたりします。
INSERT INTO users VALUES
(1,"dummy-name-1","2022-11-20 07:28:55",1,"2022-11-14 00:50:44","2022-11-14 00:50:44"),
(2,"dummy-name-2","2022-11-15 20:20:21",1,"2022-11-13 22:55:53","2022-11-13 22:55:53")...
クエリの連結処理がやや面倒な半面、速度が圧倒的に改善されます。
この方法ならば幅広く対応しつつ高速に処理できるので、多くのケースにおいて有用です。
(4) クロスジョイン(CROSS JOIN)を使った増殖
クリックしてコードを見る
SET @bulkNum = 1000;
SET @ratioNum = 1000;
SET @base_datetime = '2022-11-10 00:00:00';
SET @deltaSec = 60*60*24*7;
DROP PROCEDURE IF EXISTS insert_users_data;
delimiter //
CREATE PROCEDURE insert_users_data()
BEGIN
/* クロスジョイン用のテンポラリテーブル作成 */
DROP TEMPORARY TABLE IF EXISTS temp_nums;
CREATE TEMPORARY TABLE temp_nums
WITH RECURSIVE cte (n) AS
(
SELECT 0
UNION ALL
SELECT n + @bulkNum FROM cte WHERE n < (@ratioNum - 1) * @bulkNum
)
SELECT * FROM cte;
/* ランダムデータの作成 */
DROP TEMPORARY TABLE IF EXISTS temp_users;
CREATE TEMPORARY TABLE temp_users LIKE users;
SET @query = CONCAT('INSERT INTO temp_users VALUES ');
SET @delimiter = '';
SET @loopCount = 0;
WHILE @loopCount < @bulkNum DO
SET @id = @loopCount + 1;
SET @`name` = 'dummy-name-';
SET @`status` = 1;
SET @created_at = ADDTIME(@base_datetime, SEC_TO_TIME(FLOOR(RAND() * @deltaSec)));
SET @updated_at = @created_at;
SET @last_login_at = ADDTIME(@created_at, SEC_TO_TIME(FLOOR(RAND() * @deltaSec)));
SET @`value` = CONCAT_WS(',', @id, QUOTE(@`name`), QUOTE(@last_login_at), @`status`, QUOTE(@created_at), QUOTE(@updated_at));
SET @query = CONCAT(@query, @delimiter, '(', @`value`, ')');
SET @loopCount = @loopCount + 1;
SET @delimiter = ',';
END WHILE;
PREPARE stmt FROM @query;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
/* クロスジョインによる増殖 */
INSERT INTO users
SELECT id + n, CONCAT(`name`, id + n), last_login_at, `status`, created_at, updated_at
FROM temp_users, temp_nums;
DROP TEMPORARY TABLE temp_nums;
DROP TEMPORARY TABLE temp_users;
END;
//
delimiter ;
CALL insert_users_data;
DROP PROCEDURE IF EXISTS insert_users_data;
とにかく件数重視で、ある程度レコードの内容が重複するのを許容する『そこそこランダムなデータ』で構わないのであれば、最も高速であろう方法です。
普段SQLを書いていると、INNER JOINやLEFT(RIGHT) OUTER JOINなどは多用されると思いますが、CROSS JOINを使う機会は稀ではないでしょうか。
CROSS JOINはテーブル同士の直積を行うもので、所謂総当たりの結合を行います。
その事から誤った使い方をすると、膨大な行数のレコードセットが返る場合があるので注意が必要です。
しかし、今回はレコード数を増やすのが目的ですから、とても都合が良いのです。
コード例ではMySQL8.0で導入された共通テーブル式(CTE)による再帰処理(WITH RECURSIVE)を用いて、増殖用のテンポラリテーブルを高速で作成しています。
CREATE TEMPORARY TABLE temp_nums
WITH RECURSIVE cte (n) AS
(
SELECT 0
UNION ALL
SELECT n + @bulkNum FROM cte WHERE n < (@ratioNum - 1) * @bulkNum
)
SELECT * FROM cte;
上記クエリでは以下のようなテーブルが生成されます。
+--------+
| n |
+--------+
| 0 |
| 1000 |
| 2000 |
| 3000 |
| 4000 |
| (中略) |
| 998000 |
| 999000 |
+--------+これに別途生成した少量のランダムデータ(temp_users)をクロスジョインすれば簡単にレコードを増やす事ができます。
（FROM句においてカンマ区切りで2つのテーブルを指定しているのがクロスジョインです）
INSERT INTO users
SELECT id + n, CONCAT(`name`, id + n), last_login_at, `status`, created_at, updated_at
FROM temp_users, temp_nums
今回の例では1000パターンのランダムデータに共通テーブル式で生成した1000レコードで直積を行っているので、1000 * 1000 = 1,000,000レコードが生成されます。
ちなみに1000件分のデータを複製しているので、内容が同じレコードが増殖しているわけですが、最低限idとnameはユニークになるように工夫しています。
比較結果まとめ
それぞれの方法で掛かった平均時間を比較します。
| 方法 | 所要時間 |
|---|---|
| (1) CSVインポート | 1分10秒 |
| (2) ストアドプロシージャ | 25分41秒 |
| (3) バルクインサート | 44.516秒 |
| (4) クロスジョイン | 11.078秒 |
速さだけで見ればクロスジョインを使った方法が最も高速であることがわかります。
ですが、完全な一意性が必要な場合や、複雑なロジックに基づいたデータの生成が必要な場合はストアドプロシージャをバルクインサートで高速化した方法をおすすめします。
なお、ストアドプロシージャはあくまでランダムデータの生成を容易にするために用いただけなので必須ではありません。
このことから複雑なデータはCSVで用意して、SQLでクロスジョインしてデータ量を水増しするといったハイブリッドなアプローチも可能です。
以上！皆様のお役に立てれば幸いです😸
