MySQLで大量のテストデータを用意したい - それが僕には楽しかったんです。

はじめに
環境構築
大量のデータをどうにかして用意する
おわりに

はじめに

どうも、最近みたハクソーリッジという映画が最高に琴線に響いて何度も見ているけんつです

最近、データベース周りの勉強をしてみようと思ったのですが何かが足りないことに気が付きました。
そう大量のテストデータです。

なので今回は作ります。

環境構築

まずは環境を構築する。
基本的にはMySQLの話になるけども、インフラの勉強をするとき用の環境を後に整える予定なのでdocker-composeを使う。

version: '3'
services:
    mysql:
        image: mysql:5.7
        environment:
            - MYSQL_ROOT_PASSWORD=root
            - MYSQL_USER=sample
            - MYSQL_PASSWORD=sample
            - MYSQL_DATABASE=sample
        volumes:
            - ./docker/mysql:/var/lib/mysql
        ports:
            - "3306:3306"

こんな感じでハイパー雑にymlを用意する。

$ docker-compose up -d
$ mysql -h 127.0.0.1 -u sample -D sample -p
Enter password: 
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 5.7.20 MySQL Community Server (GPL)

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MySQL [sample]>

これでログインできた。
次にinit.sqlという名前でマイグレーションを用意する

CREATE TABLE tweets (
    uid int default 0,
    user varchar(255) not null,
    body varchar(255) not null,
    liked int default 0,
    retweet int default 0,
    include_reply boolean default false,
    created_at datetime default current_timestamp
);

ツイートに見立てて簡略的に作成してみた。

ここまでが環境構築

大量のデータをどうにかして用意する

ここではPHPを使ってスクリプトを組んでしまう。

じゃあ早速用意する

<?php
declare(strict_types=1);

//user name list
$names = ['Jack', 'John', 'Smith', 'Noah', 'Harry', 
    'Sophia', 'Emma', 'Olivia', 'Ava', 'Isabella'];

//generate random body
function generateRandBody() : string {
    $result = '';
    $limit = rand(1, 100);
    for($i = 0; $i < $limit; $i++) {
        $result .= chr(mt_rand(65, 90));
    }
    return $result;
}

$limits = 10;
$count = 0;
for (; $count < $limits; $count+=1) {
     $user = $names[$count%10];
     $body = generateRandBody();
     $liked = rand(0, 1000000000);
     $retweet = rand(0, 1000000000);
     $include_reply = ($count%2 == 0) ? 0 : 1;

     $sql = "INSERT INTO tweets (`user`, `body`, `liked`, `retweet`, `include_reply`) value ('".$user."','".$body."',".$liked.",".$retweet.",".$include_reply.");";
     echo $sql . "\n";
        
}

こんなスクリプトを組んでいて思ったのだが、当初はこのスクリプトから挿入させようと思ったがまともに1000万件とか挿入するとめちゃくちゃ遅い。
というわけで、↑はもうすでに変更したものだが最初の10件だけ登録してそれをひたすら単純結合させて増加させてしまおうという作戦に出た。

init.sql↓

CREATE TABLE tweets (
    uid int primary key auto_increment,
    user varchar(255) not null,
    body varchar(255) not null,
    liked int default 0,
    retweet int default 0,
    include_reply boolean default false,
    created_at datetime default current_timestamp
);
INSERT INTO tweets (`user`, `body`, `liked`, `retweet`, `include_reply`) value ('Jack','CSHMFFHXYIAY',101262607,788244618,0);
INSERT INTO tweets (`user`, `body`, `liked`, `retweet`, `include_reply`) value ('John','BNYPLLSXIFXZQWZSYISTIVUDXICZZJDDTTBHUO',455425608,660218612,1);
INSERT INTO tweets (`user`, `body`, `liked`, `retweet`, `include_reply`) value ('Smith','ZVMAHSWKBYYVNDJYVCIINSTYUELEUNVDJKETJGZIAITPJJSCEPLFRTDJFDVMYKVG',155967009,720719955,0);
INSERT INTO tweets (`user`, `body`, `liked`, `retweet`, `include_reply`) value ('Noah','KIDUVZVHTQEIEHJAWRFOTXMERFASBVFNPHSSTSEMAUDPVRGHCSOJFMQHRLEFGAYPOQPKYBKBHEOXOFUERGOK',717252756,32221742,1);
INSERT INTO tweets (`user`, `body`, `liked`, `retweet`, `include_reply`) value ('Harry','NEZXVIAVARL',361169251,640449508,0);
INSERT INTO tweets (`user`, `body`, `liked`, `retweet`, `include_reply`) value ('Sophia','NBPOFOVUXPFYWGTLKIYFUUOPUGJMVWKENTEKJDKAKJLDHMFEHSYSXOLWCKLWJ',587239726,530171748,1);
INSERT INTO tweets (`user`, `body`, `liked`, `retweet`, `include_reply`) value ('Emma','IIHSNNHUQIPPXLNHEKLSLN',334524943,69008302,0);
INSERT INTO tweets (`user`, `body`, `liked`, `retweet`, `include_reply`) value ('Olivia','BHLZISBQELBLJVFZBTHBJUKAHILWJXKRHLAPYBMHXBPTSPFGVFGGLXTAEKUHMHAFHYAWQKYZ',779212237,102552681,1);
INSERT INTO tweets (`user`, `body`, `liked`, `retweet`, `include_reply`) value ('Ava','BUPFCEDTZEQQEBUFEUVDZLWKTNWRKZBLTWLXIWGXWNYEYNEHHRDLYWFAMFMEZQXCZXVSAAJWRTNFXRHYUKWPWAQCXM',40163778,286463918,0);
INSERT INTO tweets (`user`, `body`, `liked`, `retweet`, `include_reply`) value ('Isabella','OQOUHLPDOWJNRIKOYXOOYUEPEVQQENEKZFYZJBEKQPO',763927989,187392711,1);
SOURCE increase.sql;

increase.sql ↓

insert into tweets (
    select
    tweets.uid = null,
    tweets.user, 
    tweets.body, 
    tweets.liked, 
    tweets.retweet, 
    tweets.include_reply,
    tweets.created_at
    from tweets, tweets tweets2, tweets tweets3, tweets tweets4, tweets tweets5, tweets tweets6);

このようにあらかじめ用意した10個のレコードを単純結合させることで膨大なデータを用意する

そしてこれらのファイルを実行するとこうなる。

MySQL [sample]> source init.sql;
Query OK, 0 rows affected (0.51 sec)

Query OK, 1 row affected (0.09 sec)

Query OK, 1 row affected (0.06 sec)

Query OK, 1 row affected (0.11 sec)

Query OK, 1 row affected (0.11 sec)

Query OK, 1 row affected (0.06 sec)

Query OK, 1 row affected (0.06 sec)

Query OK, 1 row affected (0.05 sec)

Query OK, 1 row affected (0.08 sec)

Query OK, 1 row affected (0.05 sec)

Query OK, 1 row affected (0.06 sec)

Query OK, 10000000 rows affected (5 min 13.20 sec)
Records: 10000000  Duplicates: 0  Warnings: 0


MySQL [sample]> source datasize.sql
+------------+--------+----------+------+--------+---------+----------+
| table_name | engine | tbl_rows | rlen | all_mb | data_mb | index_mb |
+------------+--------+----------+------+--------+---------+----------+
| tweets     | InnoDB |  9706337 |  100 |    929 |     929 |        0 |
+------------+--------+----------+------+--------+---------+----------+
1 row in set (0.00 sec)

1000万行程ある、900MB以上の容量を持つテーブルが作成できた。
何故か、30万レコード程損失があるとなっているがselect * from では1000万行あることになっている理由は謎これから調べてみる

おわりに

とりあえず、アホみたいにデカイDBは作れたのでこれを元にいろいろやっていく