はじめまして。しょーりです。みなさん、こう思ったことはないですか？
「なにかしらで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, 'データサンプル１', '2012-07-26 00:00:00', CURRENT_TIMESTAMP),
(NULL, 'データサンプル２', '2012-07-26 00:00:00', CURRENT_TIMESTAMP),
(NULL, 'データサンプル３', '2012-07-26 00:00:00', CURRENT_TIMESTAMP),
(NULL, 'データサンプル４', '2012-07-26 00:00:00', CURRENT_TIMESTAMP),
(NULL, 'データサンプル５', '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;
６回単純結合で、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件
--
INSERT INTO `sample` (`id`, `data`, `create_date`, `update_date`) VALUES
(NULL, 'データサンプル６', '2012-07-26 00:00:00', CURRENT_TIMESTAMP),
(NULL, 'データサンプル７', '2012-07-26 00:00:00', CURRENT_TIMESTAMP),
(NULL, 'データサンプル８', '2012-07-26 00:00:00', CURRENT_TIMESTAMP),
(NULL, 'データサンプル９', '2012-07-26 00:00:00', CURRENT_TIMESTAMP),
(NULL, 'データサンプル１０', '2012-07-26 00:00:00', CURRENT_TIMESTAMP);
これで元データが10件になるので簡単ですね。
10 * 10 * 10 * 10 * 10 * 10 * 10 * 10 = 100000000
なので、10の８乗ですね。
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処理等は特に考えていないので、そんな時は各々取り組んでください。
LIGはWebサイト制作を支援しています。ご興味のある方は事業ぺージをぜひご覧ください。
