Web事業部実績紹介
Web事業部実績紹介
2012.08.03

なんとなくやりたかった。MySQLのクエリのみで簡単に1億ものデータを作成する方法

勝利

はじめまして。しょーりです。みなさん、こう思ったことはないですか?

なにかしらで1億したい
 
ということで今回はMySQLのクエリだけで、1億データ作ってみました。作ってなにができるかというと、単純にデータ量に対しての処理速度やデータ容量の計測、または1億データ作成してドヤ顔する際に使用できます。

 

それではさっそくではありますが、はじめさせていただきます。ちなみに今回実行したMySQLのVerは5.5.16です。

 
まず今回の実験となるテーブル作成。

--
-- テーブルの構造 `sample`
--

CREATE TABLE IF NOT EXISTS `sample` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`data` varchar(500) COLLATE utf8_unicode_ci NOT NULL,
`create_date` datetime NOT NULL,
`update_date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci COMMENT='sampleテーブル' AUTO_INCREMENT=1 ;

 
そして今回の肝となる元データを少しだけ登録。

--
-- 元データインサート 5件
--

INSERT INTO `sample` (`id`, `data`, `create_date`, `update_date`) VALUES
(NULL, 'データサンプル1', '2012-07-26 00:00:00', CURRENT_TIMESTAMP),
(NULL, 'データサンプル2', '2012-07-26 00:00:00', CURRENT_TIMESTAMP),
(NULL, 'データサンプル3', '2012-07-26 00:00:00', CURRENT_TIMESTAMP),
(NULL, 'データサンプル4', '2012-07-26 00:00:00', CURRENT_TIMESTAMP),
(NULL, 'データサンプル5', '2012-07-26 00:00:00', CURRENT_TIMESTAMP);

 
データが5件登録されましたね?そうです。もちろんこれでは終わりません。目的は1億件ですから。

 
以下からは1億レコード作成するために、参考となるクエリとなります。早く結論が見たいという方は下部にあるINSERT処理のところまで、自分でスクロールしてください。

 

 

まず1億データの元となるデータを単純結合で増やします。(増やしたように見せます。)

 

--
-- 単純結合
--

select count(*) from sample, sample sample2, sample sample3, sample sample4, sample sample5, sample sample6;

 
6回単純結合で、5 * 5 * 5 * 5 * 5 * 5 = 15625 レコード(0.00 sec)

まぁこんな感じでやりますね。適当に。

 

select count(*) from sample, sample sample2, sample sample3, sample sample4, sample sample5, sample sample6, sample sample7, sample sample8, sample sample9, sample sample10, sample sample11, sample sample12, sample sample13;

 
あっ、やっべ。
1220703125 レコードになっちった。(26.71 sec)

 

俺、落ち着け。
12億じゃなくて、1億だ。

 

再度結合を減らしてトライ。

 

select count(*) from sample, sample sample2, sample sample3, sample sample4, sample sample5, sample sample6, sample sample7, sample sample8, sample sample9, sample sample10, sample sample11, sample sample12;

 
今度は 244140625 レコードになっちった。(5.30 sec)

あー、1億ちょうどとか繊細なレコード数は僕には作れないようだ。

 
5のX乗 ≒ 100000000

とか訳わかんないから単純に元データを5件増やして調整っと。これで元データが10件になります。

 

--
-- 元データ追加インサート 5件
--

INSERT INTO `sample` (`id`, `data`, `create_date`, `update_date`) VALUES
(NULL, 'データサンプル6', '2012-07-26 00:00:00', CURRENT_TIMESTAMP),
(NULL, 'データサンプル7', '2012-07-26 00:00:00', CURRENT_TIMESTAMP),
(NULL, 'データサンプル8', '2012-07-26 00:00:00', CURRENT_TIMESTAMP),
(NULL, 'データサンプル9', '2012-07-26 00:00:00', CURRENT_TIMESTAMP),
(NULL, 'データサンプル10', '2012-07-26 00:00:00', CURRENT_TIMESTAMP);

 
これで元データが10件になるので簡単ですね。

 

10 * 10 * 10 * 10 * 10 * 10 * 10 * 10 = 100000000

なので、10の8乗ですね。

 

select count(*) from sample, sample sample2, sample sample3, sample sample4, sample sample5, sample sample6, sample sample7, sample sample8;

 
これで100000000 レコード。(2.01 sec)

 

 

( ´,_ゝ`)プッ

 

 

じゃあ最後にINSERTします。INSERTは、このSELECTすべてをINSERTすれば良いので、おのずとこうなります。

insert into sample(
select
sample.id = NULL,
sample.data,
sample.create_date,
sample.update_date
from sample, sample sample2, sample sample3, sample sample4, sample sample5, sample sample6, sample sample7, sample sample8
);

 

これで大量データがINSERTされるはずです。途中以下のようなエラーが出る可能性がおおいにあります。
The total number of locks exceeds the lock table size

 
内容的には、
「innodbのテーブルに対して、大量データのINSERT/UPDATE/DELETEを行うと発生」
みたいなことのようですので、

 
そんな時は、
「my.cnf の innodb_buffer_pool_size の値を大きくする」
といった対応が効果的なようです。(僕もその対応をしましたよ!)
 
※これは1億データを作成して、できるだけ簡単にドヤ顔するためのものです。INSERTの際の処理時間や、ランダムなデータのINSERT処理等は特に考えていないので、そんな時は各々取り組んでください。