t100のプログラミング脱出作戦

開発をしていて、大量のテストデータが欲しいなーということが時々あります。

先日の、「MySQLの using temporary; using filesort が何故遅いのか」の内容も、実際に検証してみるには、sort_buffer_size を超える量のテストデータが必要になります。


しかし、これが意外と面倒くさいっ
使い慣れたスクリプト言語で、チョチョイとコードを書くのもOKなのですが、でもやっぱり面倒くさいっ
（ですよね？）


ということで、今回はSQLだけで大量データを作る方法を考えて見ます。
といっても、ストアドプロシージャでループとかじゃないです！

「単純結合」を使って、SQLで頑張るのです！

結合とは、SQLで複数の表を結合して一つの表を作る方法です。
JOINであります。

このうち「単純結合」とは、JOINの際に結合条件を指定しないで繋げる方法です。
つまり、結合する表にあるレコードの全ての組み合わせを、とにかくひたすら結合することになります。


実業務でこれを使うケースはほとんど無いのですが、実は意外と使えます！
(｀・ω・´)9m ビシッ

※都道府県別・性別 統計を作るときに、都道府県データと性別データを単純結合して元表を作ったりできます。


では、さっそく MySQLで試してみましょう！



まず、単純結合に使う元データのテーブルを作ります。
さすがに、ここは実体のテーブルが必要になります。

本当は、テンポラリテーブルで処理したいところですが、MySQLはテンポラリテーブルの自己結合が出来ないので
実際のテーブルを作ります。

mysql> create table a select 1 union select 2 union select 3 union select 4;

これで１～４の４レコードを持ったテーブル a が出来ます。
１～４の数字に深い意味はありません。

この４レコードが作成するデータの種データになります。
（後で説明しますが、レコード数はもっと多くても少なくてもOKです）


あとは、作成したテーブルを単純結合してみるのみです！

mysql> select count(*) from a, a a2, a a3, a a4;
+----------+
| count(*) |
+----------+
| 256 |
+----------+
1 row in set (0.01 sec)

mysql> select count(*) from a, a a2, a a3, a a4, a a5, a a6;
+----------+
| count(*) |
+----------+
| 4096 |
+----------+
1 row in set (0.00 sec)

一瞬で、4096 件！！


今回、最初に用意したデータが４件だったので

４回単純結合で、4 * 4 * 4 * 4 = 256 件
６回単純結合で、4 * 4 * 4 * 4 * 4 * 4 = 4096 件

となっています。


仕組みとしては、非常に単純ですね！
なので、最初のレコード数を多めに作れば一気に大量データの生成も可能になります。

最初のデータ自体も、同じ仕組みで増やすことができます。

mysql> drop table a; /* 一旦、テーブル消します */
Query OK, 0 rows affected (0.03 sec)

mysql> create table a select 1 union select 2; /* ２件データを作ります。 */
Query OK, 2 rows affected (0.02 sec)
Records: 2 Duplicates: 0 Warnings: 0

mysql> insert into a (select a.1 from a, a a1, a a2); /* 2 * 2 * 2 = 8 件追加 */
Query OK, 8 rows affected (0.03 sec)
Records: 8 Duplicates: 0 Warnings: 0

mysql> select count(*) from a;
+----------+
| count(*) |
+----------+
| 10 |
+----------+
1 row in set (0.00 sec)

元データが１０件になりました！
このデータを７回結合すれば、なんとデータは１０００万件です！

mysql> select count(*) from a, a a2, a a3, a a4, a a5, a a6, a a7;
+----------+
| count(*) |
+----------+
| 10000000 |
+----------+
1 row in set (0.00 sec)

∩(´･∀･`)∩＜わーい



あとは、このデータに連番をつけたり、適度に分散された数字をつけられれば
かなりの応用ができそうです。


試しに、１～６の数字をランダムに振って見ましょう。


１～６のランダムな数字は、rand 関数を使います。

一般式としては、こんな感じになります。
（@range_from, @range_to の値を変えれば、範囲を調整できます）

SET @range_from := 1;
SET @range_to := 6;

select floor(rand() * (@range_to - @range_from + 1) + @range_from) ;

１～６の場合、展開するとこうなります

select floor(rand() * 6 + 1) ;

先ほどの１０００万件に、これを組み合わせてデータ分布をみてみましょう。

mysql> select num, count(*), count(*) / 10000000 * 100 from
-> (select floor(rand() * 6 + 1) num from a, a a2, a a3, a a4, a a5, a a6, a a7 ) dat
-> group by 1
-> ;
+------+----------+---------------------------+
| num | count(*) | count(*) / 10000000 * 100 |
+------+----------+---------------------------+
| 1 | 1665735 | 16.6574 |
| 2 | 1667785 | 16.6779 |
| 3 | 1666653 | 16.6665 |
| 4 | 1665520 | 16.6552 |
| 5 | 1668201 | 16.6820 |
| 6 | 1666106 | 16.6611 |
+------+----------+---------------------------+
6 rows in set (7.49 sec)

さすがに１０００万件となると、ちょっと時間がりますが、ほぼ均等に分布したデータが作れました。
すばらし！



あとは、連番をつけてテーブルに保存したりすればいい感じなんじゃないでしょうか！

例えば、こんな感じです。

SET @id := 0;

create table data
select @id := @id + 1 id , num from
(select floor(rand() * 6 + 1) num from a, a a2, a a3, a a4, a a5, a a6, a a7 ) dat

mysql> select * from data limit 10;
+------+------+
| id | num |
+------+------+
| 1 | 6 |
| 2 | 1 |
| 3 | 2 |
| 4 | 2 |
| 5 | 5 |
| 6 | 6 |
| 7 | 6 |
| 8 | 2 |
| 9 | 3 |
| 10 | 4 |
+------+------+
10 rows in set (0.02 sec)

いかがでしょうか？！


num の部分の作り方を変えれば、不均等な分布のデータも作れると思います。
例えば、１～６の数字を、さらに４で割った余りにしたりすると偏りが作れます。
（後から update で調整してもいいですね）



よーし！これを使って、MySQLのパフォーマンス実験だっ！（多分）

テーマ: プログラミング - ジャンル: コンピュータ

このコメントは管理者の承認待ちです