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

自分のプログラミング脳をプログラムにして、いつかプログラミングから脱出してやるぞっ!とか夢見ながら、日々プログラム作っていく 百野 貴博 の日記です!今は、屋号『百蔵。』として、Silverlight・WPFを追跡中です! (2007/09/30)
スポンサーサイト
上記の広告は1ヶ月以上更新のないブログに表示されています。
新しい記事を書く事で広告が消せます。
【--/--/-- --:--】 | スポンサー広告 | トラックバック(-) | コメント(-) top↑
SQLで大量データを一気に作るでござる(MySQL編)
開発をしていて、大量のテストデータが欲しいなーということが時々あります。

先日の、「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;



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

この4レコードが作成するデータの種データになります。
(後で説明しますが、レコード数はもっと多くても少なくても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 * 4 * 4 = 256 件
6回単純結合で、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; /* 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)



元データが10件になりました!
このデータを7回結合すれば、なんとデータは1000万件です!


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)




∩(´・∀・`)∩<わーい



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


試しに、1~6の数字をランダムに振って見ましょう。


1~6のランダムな数字は、rand 関数を使います。

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

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

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



1~6の場合、展開するとこうなります

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




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


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)



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



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

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


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 の部分の作り方を変えれば、不均等な分布のデータも作れると思います。
例えば、1~6の数字を、さらに4で割った余りにしたりすると偏りが作れます。
(後から update で調整してもいいですね)



よーし!これを使って、MySQLのパフォーマンス実験だっ!(多分)

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

このコメントは管理者の承認待ちです
2013/11/09(土) 17:59:19 | | [ 編集]












管理者にだけ表示を許可する


トラックバックURL:
これは賢い! SQLで大量データを一気に作るでござる(MySQL編) http://t100life.blog121.fc2.com/blog-entry-205.html 見習ってやってみた。 1.テンポラリテーブルを作るところは、元テーブルのコピーにした。 drop table if exists tmp_foo; create table tmp_foo like fo...
2011/04/22(金) 20:25:41 | はらっぱー
これは賢い! SQLで大量データを一気に作るでござる(MySQL編) http://t100life.blog121.fc2.com/blog-entry-205.html 見習ってやってみた。 1.テンポラリテーブルを作るところは、元テーブルのコピーにした。 drop table if exists tmp_foo; create table tmp_foo like fo...
2011/04/25(月) 09:34:53 | はらっぱー
これは賢い! SQLで大量データを一気に作るでござる(MySQL編) http://t100life.blog121.fc2.com/blog-entry-205.html 見習ってやってみた。 1.テンポラリテーブルを作るところは、元テーブルのコピーにした。 drop table if exists tmp_foo; create table tmp_foo like fo...
2011/05/15(日) 02:08:55 | はらっぱー
上記広告は1ヶ月以上更新のないブログに表示されています。新しい記事を書くことで広告を消せます。