onemuri.space

MySQL でダミーデータの作り方

MySQL でダミーデータを作る

結論から書いていきます。

まずはデータベースとテーブルを作成します。

CREATE DATABASE `sample`;

CREATE TABLE `sample`.`parent`
(
  `id` INT NOT NULL AUTO_INCREMENT,
  `count` INT NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE `sample`.`child`
(
  `id` INT NOT NULL AUTO_INCREMENT,
  `parent_id` INT NOT NULL,
  `age` INT NOT NULL,
  PRIMARY KEY (`id`, `parent_id`),
  INDEX `idx_parent_id` (`parent_id` ASC),
  CONSTRAINT `fk_parent_id`
    FOREIGN KEY (`parent_id`)
    REFERENCES `sample`.`parent` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

別にどんなテーブルでもいいですが、child は parent に外部参照するような基本的な構成です。

では、作成したテーブルを用いて、ダミーデータを作成しましょう!

-- INSERTするテーブルをselectすることで指数関数的にデータを増やします
-- 今回は3万件を超えるデータを作成しています
INSERT INTO `sample`.`parent` (`id`,`count`) VALUES (1, 0);
INSERT INTO `sample`.`parent` (`count`) select 0 from `sample`.`parent`;
INSERT INTO `sample`.`parent` (`count`) select 0 from `sample`.`parent`;
INSERT INTO `sample`.`parent` (`count`) select 0 from `sample`.`parent`;
INSERT INTO `sample`.`parent` (`count`) select 0 from `sample`.`parent`;
INSERT INTO `sample`.`parent` (`count`) select 0 from `sample`.`parent`;
INSERT INTO `sample`.`parent` (`count`) select 0 from `sample`.`parent`;
INSERT INTO `sample`.`parent` (`count`) select 0 from `sample`.`parent`;
INSERT INTO `sample`.`parent` (`count`) select 0 from `sample`.`parent`;
INSERT INTO `sample`.`parent` (`count`) select 0 from `sample`.`parent`;
INSERT INTO `sample`.`parent` (`count`) select 0 from `sample`.`parent`;
INSERT INTO `sample`.`parent` (`count`) select 0 from `sample`.`parent`;
INSERT INTO `sample`.`parent` (`count`) select 0 from `sample`.`parent`;
INSERT INTO `sample`.`parent` (`count`) select 0 from `sample`.`parent`;
INSERT INTO `sample`.`parent` (`count`) select 0 from `sample`.`parent`;
INSERT INTO `sample`.`parent` (`count`) select 0 from `sample`.`parent`;

-- INSERT child
INSERT INTO `sample`.`child` (`parent_id`, `age`) select id, 10 from `sample`.`parent` ORDER BY RAND();

これだけです。

ダミーデータ増やし方の説明

肝の部分を説明します

2 箇所大事なところがあります

  1. データを増やすこと
    → INSERT 時の VALUES 部分に適当なテーブルを指定して SELECT すること

  2. ランダムなデータを増やすこと
    → INSERT 時の VALUES 部分に適当なテーブルを指定して ランダムな順番になるように SELECT すること

データを増やすこと

まずはダミーデータを増やすところです

INSERT INTO `sample`.`parent` (`count`) select 0 from `sample`.`parent`;

1 件以上データが入っているテーブルを SELECT して返ってくるカラム数と同等のデータを作成する処理を SQL で表現しています

この SQL は叩けば叩くほど指数関数的にデータが増えていきます

通常 INSERT 文を作成する時は以下のようになっています。 INSERT する順番と INSERT する値を書いているのですね。

INSERT INTO `sample`.`parent` (`count`) VALUES (??)

この INSERT 文の ?? 部分に入る値を SELECT 文を使って選択しているのが今回用いているダミーデータ作成の肝です。これにより、VALUES の中には SELECT 文で返ってきた行数分のデータを INSERT できるようになるのです。

例えば、 sample.parent の中身が 1 件であれば1件分。10000 件であれば、10000 件のレコードを INSERT できるような計算になります。また、このクエリは叩けば叩くほどに、指数関数的に INSERT 文が発行される仕組みなります。

INSERT INTO `sample`.`parent` (`id`,`count`) VALUES (1, 0);
-- 合計1件のレコードが生成されている

---------------- 以下データを増やす処理 ------------------

INSERT INTO `sample`.`parent` (`count`) VALUES (??);
-- 合計2件のレコードが生成されている状態

INSERT INTO `sample`.`parent` (`count`) VALUES (??);
-- 合計4件のレコードが生成されている状態

INSERT INTO `sample`.`parent` (`count`) VALUES (??);
-- 合計8件のレコードが生成されている状態

INSERT INTO `sample`.`parent` (`count`) VALUES (??);
-- 合計16件のレコードが生成されている状態

INSERT INTO `sample`.`parent` (`count`) VALUES (??);
-- 合計32件のレコードが生成されている状態

INSERT INTO `sample`.`parent` (`count`) VALUES (??);
-- 計64件のレコードが生成されている状態

こんな流れになっています

INSERT でデータを増やす → 増やしたデータを持つテーブルで INSERT してデータを増やす

ランダムなデータを増やすこと

次にランダムなデータを生成します。

今回はテーブルにあるデータを使って生成しています。

INSERT INTO `sample`.`child` (`parent_id`, `age`) select id, 10 from `sample`.`parent` ORDER BY RAND();

データを増やすことでやった手法にランダム要素を加えただけです。RAND() 関数を使って、あるテーブルのデータをランダムな順番で引っ張ってくるだけです。また、今回の場合には sample.parent のダミーデータを増やした後だったので、そのテーブルに対して SELECT をすれば一発で大量のダミーデータを生成することができました。

sample.parentにあるデータ(select id ←これです from sample.parent)を SELECT しましたが、別にランダムな値は自作で生成しても大丈夫です。

SUBSTRING(MD5(RAND()), 1, 10) -- ランダムな文字列の生成
CEIL(RAND() * 100) -- ランダムな数値の生成

まとめ

開発しているとデータが増えた場合の挙動や負荷対策をする状況が発生しますが、今回の記事を参考に何百万とあるダミーデータテーブルを作成してスロークエリが発生しないかを調査してみてはいかがでしょうか。

自己紹介用画像

Riki Akagi

2019年からDeNAで働いています。GCP(CloudSQL・GAE・Cloud Function etc)とGoでAPI開発に勤んでいます。睡眠やエンジニアリングに関することに興味を持って過ごしているのでその情報を皆さんに共有していけたらなと思っています。

自己紹介の詳細