データベース

【ざっくり解説】大量ダミーデータ投入

大量ダミーデータ投入 データベース
この記事は約6分で読めます。

ごきげんよう。毎週金曜日は欠かさずランチにカレーを食べています、サーバー担当のgonzoです😺
今回はRDB(リレーショナルデータベース)にテスト用の大量ダミーデータを流し込む方法についてご紹介します。

さて、世の中のWebサービスはリリースされる際、事前に意図したとおりのシステムになっているか、稼働時の負荷に耐えられるかどうか等をテストするのが常です。
そのテストでは実際にユーザーの行動によって生成される内容を模したダミーのユーザーデータを、データベースにあらかじめ投入して動かすという事が一般的となっています。

テストの度にデータを初期化したり、複数の試験環境で使いまわしたりなど、データの入れ直しは日常茶飯事です。
そのデータ投入に関して、規模の小さいサービスならまだしも、ターゲットユーザー数が多い場合はとても時間がかかってしまうという問題があります。

今回はそのダミーデータの投入にかかる時間をいかにして短くできるのか、という工夫についていくつか挙げてみます。

前提

MySQLデータベースに100万レコードのユーザーデータを投入します。
各方法における実行時間の比較結果は記事の最後の方に記載しています。

DBサーバーとクライアント

今回は社内LANにある下記スペックのPCにDockerを使用して、MySQLサーバーを構築しました。
クライアントにはWindows用のGUIツールであるHeidiSQLを使用しています。

  • CPU: Core i7-4790 @ 3.60GHz
  • RAM: 16GB
  • OS: AlmaLinux 9.1
  • DB: MySQL 8.0.31
  • クライアント: HeidiSQL 12.4.0.6659

テーブル構造

対象とするテーブルは下記の定義で構築したものを使います。
非常にシンプルな例ですが、もっとカラムが多いテーブルやレコードあたりの容量が大きい場合は投入に必要な時間が増加します。

(1) CSVインポート

一番わかりやすい方法だと思います。
あらかじめExcelなどを使い、100万件のデータが入ったCSVファイルを作成してサーバーに流し込みます。
レコードが大きいとCSVファイルも巨大になる可能性があるので、取り回しが困難になりがちです。
また、CSVファイル中身を転送する必要があるのでネットワークの速度にも依存しやすいです。
本記事で使用したCSVファイルは約95MBになりました。

gonzo
gonzo
Excelで迂闊に関数を利用して100万行生成しようとすると、メモリが枯渇する恐れがあるので注意だニャー

(2) ストアドプロシージャを使う

クリックしてコードを見る

ストアドプロシージャを使って、ランダムデータを含むレコード生成しながら書き込んでいきます。
プログラムで1レコード毎に異なるランダムデータを生成することができますが、このまま愚直にやるととても遅いので、おすすめできません。

gonzo
gonzo
ただでさえ遅いけど、プリペアドステートメントを使ってなかったら、さらに時間がかかるニャー

(3) バルクインサートで高速化

クリックしてコードを見る

前述の方法を効率よくしたものです。
INSERTでは1回で複数のレコードをまとめて書き込めるので、1000レコードずつ書き込むようにします。
これは『バルクインサート』と呼ばれたりします。

クエリの連結処理がやや面倒な半面、速度が圧倒的に改善されます。
この方法ならば幅広く対応しつつ高速に処理できるので、多くのケースにおいて有用です。

gonzo
gonzo
1度に送信できるクエリの長さはサーバーの設定に依存するから注意してニャー

(4) クロスジョイン(CROSS JOIN)を使った増殖

クリックしてコードを見る

とにかく件数重視で、ある程度レコードの内容が重複するのを許容する『そこそこランダムなデータ』で構わないのであれば、最も高速であろう方法です。

普段SQLを書いていると、INNER JOINLEFT(RIGHT) OUTER JOINなどは多用されると思いますが、CROSS JOINを使う機会は稀ではないでしょうか。
CROSS JOINはテーブル同士の直積を行うもので、所謂総当たりの結合を行います。
その事から誤った使い方をすると、膨大な行数のレコードセットが返る場合があるので注意が必要です。
しかし、今回はレコード数を増やすのが目的ですから、とても都合が良いのです。

コード例ではMySQL8.0で導入された共通テーブル式(CTE)による再帰処理(WITH RECURSIVE)を用いて、増殖用のテンポラリテーブルを高速で作成しています。

ソフィアちゃん
ソフィアちゃん
SQLで再帰処理???難しいですね!
gonzo
gonzo
他のRDBMSでは結構前から再帰は使えていて、MySQLは大分遅れて登場したんだニャー

上記クエリでは以下のようなテーブルが生成されます。

これに別途生成した少量のランダムデータ(temp_users)をクロスジョインすれば簡単にレコードを増やす事ができます。
(FROM句においてカンマ区切りで2つのテーブルを指定しているのがクロスジョインです)

今回の例では1000パターンのランダムデータに共通テーブル式で生成した1000レコードで直積を行っているので、1000 * 1000 = 1,000,000レコードが生成されます。
ちなみに1000件分のデータを複製しているので、内容が同じレコードが増殖しているわけですが、最低限idnameはユニークになるように工夫しています。

比較結果まとめ

それぞれの方法で掛かった平均時間を比較します。

方法 所要時間
(1) CSVインポート 1分10秒
(2) ストアドプロシージャ 25分41秒
(3) バルクインサート 44.516秒
(4) クロスジョイン 11.078秒

速さだけで見ればクロスジョインを使った方法が最も高速であることがわかります。
ですが、完全な一意性が必要な場合や、複雑なロジックに基づいたデータの生成が必要な場合はストアドプロシージャをバルクインサートで高速化した方法をおすすめします。

なお、ストアドプロシージャはあくまでランダムデータの生成を容易にするために用いただけなので必須ではありません。
このことから複雑なデータはCSVで用意して、SQLでクロスジョインしてデータ量を水増しするといったハイブリッドなアプローチも可能です。

ソフィアちゃん
ソフィアちゃん
要求に応じて使い分けてくださいね!

以上!皆様のお役に立てれば幸いです😸

タイトルとURLをコピーしました