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

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

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

1 直積を用いたINSERT文
2 1000万件のデータも数分で作成できた

直積を用いたINSERT文

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

直積とは「クロス結合（cross join）」とも呼ばれますが、要は２つのテーブル同士の全通りの組合せを取得する方法です。
詳しいことは実際に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倍ずつ増えていきます。ここまでくると、もうお分かりですね。
上記の要領で７つのテーブルを連結することで、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
;