Open4
MySQL で大量のダミーデータを作成
要件
- 最低 100 万レコード、可能なら 1 億レコード
- ランダムな値
- 可能なら値の範囲は指定
- 速度も重視
MySQL の機能
SELECT INSERT で倍々にしていく。(遅い)
1 => 2 => 4 => ... => 1,048,576 => ... => 134,217,728
20 回で 100 万、27 回で 1 億。
テーブル結合で 2 乗。
10 => 100 => 10,000 => 100,000,000
CSV (TSV) からインポート。
ツール
SELECT INSERT における AUTO_INCREMENT
innodb_autoinc_lock_mode = 0
以外だと連番にならない。
ダミーテーブル
レコード数を指定するためのダミーテーブル。
使い勝手がいいように 100 レコード生成。
100 レコード
mysql> CREATE TABLE `dummy` (
-> `id` TINYINT UNSIGNED NOT NULL AUTO_INCREMENT,
-> PRIMARY KEY (`id`)
-> );
Query OK, 0 rows affected (0.07 sec)
mysql> INSERT INTO `dummy` () VALUES (),(),(),(),(),(),(),(),(),();
Query OK, 10 rows affected (0.05 sec)
Records: 10 Duplicates: 0 Warnings: 0
mysql> INSERT INTO `dummy` () SELECT 0 FROM `dummy` `d1`, `dummy` `d2`;
Query OK, 100 rows affected (0.02 sec)
Records: 100 Duplicates: 0 Warnings: 0
mysql> DELETE FROM `dummy` LIMIT 10;
Query OK, 10 rows affected (0.01 sec)
mysql> SELECT COUNT(*) FROM `dummy`;
+----------+
| COUNT(*) |
+----------+
| 100 |
+----------+
1 row in set (0.00 sec)
実際のテーブルにダミーデータを作成
FROM
に指定したテーブルの数だけ 100^n されます。
1,000,000 レコード
mysql> CREATE TABLE `raid` (
-> `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
-> `player_id` INT UNSIGNED NOT NULL,
-> `raid_id` INT UNSIGNED NOT NULL,
-> `damage` MEDIUMINT UNSIGNED NOT NULL,
-> PRIMARY KEY (`id`),
-> KEY (`raid_id`, `damage`) -- covering index
-> );
Query OK, 0 rows affected (0.11 sec)
mysql> INSERT INTO `raid` (`player_id`, `raid_id`, `damage`)
-> SELECT CEIL(RAND() * 100), CEIL(RAND() * 10), CEIL(RAND() * 1000)
-> FROM `dummy` `d1`, `dummy` `d2`, `dummy` `d3`;
Query OK, 1000000 rows affected (12.32 sec)
Records: 1000000 Duplicates: 0 Warnings: 0
100^n にならないときは微調整します。
10,000,000 レコード
mysql> INSERT INTO `raid` (`player_id`, `raid_id`, `damage`)
-> SELECT CEIL(RAND() * 100), CEIL(RAND() * 10), CEIL(RAND() * 1000)
-> FROM `raid`, (SELECT * FROM `dummy` LIMIT 9) AS `d`;
Query OK, 9000000 rows affected (15 min 6.98 sec)
Records: 9000000 Duplicates: 0 Warnings: 0
0 から INSERT した方が速かった。
10,000,000 レコード
mysql> INSERT INTO `raid` (`player_id`, `raid_id`, `damage`)
-> SELECT CEIL(RAND() * 100), CEIL(RAND() * 10), CEIL(RAND() * 1000)
-> FROM `dummy` `d1`, `dummy` `d2`, `dummy` `d3`, (SELECT * FROM `dummy` LIMIT 10) AS `d4`;
Query OK, 10000000 rows affected (5 min 56.67 sec)
Records: 10000000 Duplicates: 0 Warnings: 0
1 億は流石にそこそこ時間かかる。
100,000,000 レコード
mysql> INSERT INTO `raid` (`player_id`, `raid_id`, `damage`)
-> SELECT CEIL(RAND() * 100), CEIL(RAND() * 10), CEIL(RAND() * 1000)
-> FROM `dummy` `d1`, `dummy` `d2`, `dummy` `d3`, `dummy` `d4`;
Query OK, 100000000 rows affected (1 hour 27 min 21.76 sec)
Records: 100000000 Duplicates: 0 Warnings: 0