[SQL]1000万件のデータを即作成する方法

この記事では、システム開発における性能試験や、導入済みシステムで発生した性能劣化の対策を行う方向けに、1000万件規模の大量データをすぐに作成する方法をご紹介します。

私も常駐先の業務で、「システムを導入した客先で性能劣化が発生している。すぐに現象の再現を行いたいので、検証環境に本番と同規模の1000万件のデータを用意してくれないか?」と依頼されたことが調査のきっかけでした。

直積を用いたINSERT文

さすがにINSERT文を1000万回実行するわけにもいきません。
どうしたものかとネットで調べていたところ、「直積」という方法を使って大量のデータをINSERTする方法を見つけました。

直積とは「クロス結合(cross join)」とも呼ばれますが、要は2つのテーブル同士の全通りの組合せを取得する方法です。
詳しいことは実際にSQLを実行して試してみたほうが理解が早いかと思います。

大量データ作成の元となるテーブルを用意

まず最初に、直積を行うためのテーブルを用意します。
ここでは、以下のようにcross_join_tableを作成してみます。
idとvalueの2カラムしか無い、シンプルなテーブルです。
※以下、PostgreSQLでの実行サンプルとなります。

--cross_join_table作成
CREATE TABLE cross_join_table(
  id SERIAL,
  value integer NOT NULL DEFAULT 0,
  PRIMARY KEY (id)
);


次に、このcross_join_tableに10件のデータを挿入します。
単純に、valueカラムに1~10の値をセットするのみです。

--cross_join_tableにデータを10件作成
INSERT INTO cross_join_table(value)
VALUES (1), (2), (3), (4), (5), (6), (7), (8), (9), (10);


cross_join_tableには、以下のようなデータが作成されています。
※本記事ではA5SQL Mk-2を使用してデータを閲覧

  

CROSS JOIN句を使ったSELECT文

ここで、作成したテーブルを使用して、以下のSQLを実行してみます。
同じテーブルを別名で宣言し、CROSS JOIN句で連結したものの件数をカウントします。

SELECT count(*) 
FROM cross_join_table AS t1 
CROSS JOIN cross_join_table AS t2
;


上記の結果は、100件が返されます。
もともとcross_join_tableにはデータが10件しかありませんが、10件ずつ2個のテーブルをCROSS JOIN句で結ぶことで、10 × 10 の総当たりを求めることになります。なので、結果は100件が返されます。

つまり、CROSS JOIN句でどんどんテーブルを連結することで、データが10倍ずつ増えていきます。ここまでくると、もうお分かりですね。
上記の要領で7つのテーブルを連結することで、1000万件が返されることになるのです。

ちなみに、CROSS JOIN句は省略が可能です。
なので、以下のように記述してもOKです。

SELECT 
    count(*) 
FROM
    cross_join_table AS t1 --10
   ,cross_join_table AS t2 --100
   ,cross_join_table AS t3 --1000
   ,cross_join_table AS t4 --10000
   ,cross_join_table AS t5 --100000
   ,cross_join_table AS t6 --1000000
   ,cross_join_table AS t7 --10000000
;

  

1000万件を追加するINSERT文の例

さて、直積を利用して1000万件のデータを抽出できることはわかりました。
あとは、実際に1000万件のデータを追加するにはどうしましょう?

察しの良い方は既にお分かりかと思いますが、INSERT文にそのまま組み込んでしまえば良いのです。
ここでは、例えば以下のテーブルが存在したとして、このテーブルへ1000万件のデータを追加する方法をご紹介します。

ここではまず、users_tableという名前のテーブルを生成しておきます。

CREATE TABLE users_table(
  id SERIAL,
  user_name character varying(50) NOT NULL,
  created_at timestamp with time zone DEFAULT NOW(),
  updated_at timestamp with time zone DEFAULT NOW(),
  PRIMARY KEY (id)
);


idにはデータ追加のたびに連番がセットされて、created_atとupdated_atにはデータ追加時の現在日時を自動でセットされます。
(本来ならcreated_atは追加時のみ、updated_atは更新時に上書きなんですが)

で、このテーブルへのINSERT文を以下のように記述し実行してみてください。
ここでは、user_nameカラムに”user_XX”(XXの部分にはROW_NUMBER()を付与)という一意の名前をセットするSQL文としています。
で、こいつを実行することで、1000万件のデータがセットされます。

INSERT INTO users_table(user_name)
SELECT
  'user_' || ROW_NUMBER() OVER ()
FROM
  cross_join_table AS t1, --10
  cross_join_table AS t2, --100
  cross_join_table AS t3, --1000
  cross_join_table AS t4, --10000
  cross_join_table AS t5, --100000
  cross_join_table AS t6, --1000000
  cross_join_table AS t7 --10000000
;

   

1000万件のデータも数分で作成できた

ただ、さすがにいきなり1000万件のデータを作るのも危険かと思います。
どれだけ時間がかかるか分からなかったので、とりあえずCROSS JOINさせるテーブルの数を限定して、10万件までのINSERTを試してみましたが、いずれも数秒で完了しました。

これなら、1000万件のINSERTを実行したとしても、せいぜい数分~十数分で終わるだろう、と見込みを持ったところで、いよいよ1000万件データのINSERTを実行!

結果、私のローカルPCでは、1000万件のデータ追加するのに約10分ほどかかりました。SQLのみで作業が完結して、なおかつ10分程度で1000万件のデータが作れてしまうので便利です。

もちろん他にも方法はあるかと思いますが、私は実際に業務でやってみて、この方法が一番お手軽だと感じました。

急ぎで大量のデータをテーブルに追加する必要がある方は、ぜひ本記事の内容を参考にしてみてください。
ただし、いきなり大量データのINSERTを実行したりせず、まずは少量のデータの追加でどのくらい時間がかかるかを計測してから、じょじょに投入するデータ量を増やしてみることをオススメします。

シェアする

  • このエントリーをはてなブックマークに追加

フォローする