MySQLのCREATE TABLE文からサンプルデータを自動生成してみる
MySQLのCREATE TABLE文から自動でサンプルデータを生成するようなプログラムを書いてみました。
動機
実運用前のサービスでは、当然ながら使用するテーブルには実際のデータが入っていません。その為、実運用時のパフォーマンスをある程度予測したい時に、サンプルデータを入れようと思った時には自力で用意する必要があるので一苦労する可能性があります。対象となるテーブルが、外部キー制約やユニークキー制約が無かったりするシンプルなスキーマでない場合にはさらに苦労が増えるかもしれません。
そこで、依存しているテーブルのCREATE TABLE文をまとめて突っ込むことで、依存関係のあるテーブル間で制約を満たすようなデータをまとめて生成するようなツールがあったらいいなと思った次第です。あと、EXPLAINで実行計画をみる時に、実際にある程度データが入ってないと実行計画が変わったりするので、それを確認するためにサクッとデータを作れたりすると良さそうです。
ではどのようにすれば制約を考慮してデータを生成できるでしょうか。今回のプログラムにおける外部キー制約の解決についてみていきます。
外部キー制約の解決
外部キー制約の元では、参照先のテーブルのカラムで使われているデータのみをそれを参照するテーブルのカラムのデータとして使用しなければなりません。例えば、外部キー制約があるTable1のcolumn1がTable2のcolumn1に参照している場合には、Table2のcolumn1に存在しないデータを、Table1のcolumn1のデータとして使用することはできません。
となると、「参照されるテーブル」から「参照するテーブル」の順にデータを生成しなければなりません。 テーブル間には相互に参照する関係は許されていません。もし相互に参照する関係があれば、テーブルがお互い空の場合に新しくデータを追加することができなくなります。（あるテーブルにデータを追加するには、参照先のデータが必要だが、参照先のデータを用意するには、こちらのデータも必要になる。）
つまり、テーブルの参照関係は閉路が存在しない有向非巡回グラフとなります。これはトポロジカルソートが可能で、テーブルの参照関係に対してトポロジカルソートを行うと、テーブルを依存の方向順に並び替えることができます。以下の図のような感じです。
こうして依存の方向順に並び替えることができたので、依存される方から依存する方へと、データを生成し、生成したデータを渡しながら続けていくと、外部キー制約を満たしつつサンプルデータを生成することが可能になります。
しかしこの方式にはこのままでは少し欠点があります。それは、生成したのテーブルのデータを、後続のテーブルのデータの生成ステップでも全てメモリ上に持ち続けているという点です。データ量が少ないうちであれば、これは特に問題になりませんが、大量のデータを生成するとなった際にはこれが原因でメモリが足りなくなるかもしれません。これを解決するには、閾値を超えた量のデータを生成する際に、生成したデータをバッファに溜めてディスク書き込みを行い、後続のデータ生成で必要になった際にはディスクから読み込みながら処理を行う方法も考えられます。が、今回は実装していません。
もう一種類の制約として、ユニークなカラム値の組み合わせを一意にするもの（ユニークキー、プライマリキー制約）が存在します。
ユニークキー、プライマリキー制約の解決
制約のない単なる値を生成する場合とは異なり、カラム値の組み合わせで一意にする必要がある場合には「各カラムでそのまま値を生成し、それらの値の組み合わせがユニークかチェックし、ユニークなものだけを残す」のような方法では効率が悪いと考えられます。そこで、カラム値の組み合わせを列挙することで、ユニークなカラム値の組み合わせのみを生成することにします。これは、各カラム値の組み合わせを全探索することで全列挙できます。が、生成したい行数分だけ列挙できれば十分であることに注意する必要があります。例えば、column1, column2, column3のカーディナリがそれぞれ10, 10, 10だったとします。すると、ユニークな組み合わせは10 x 10 x 10で 1000通り生成することができるのですが、実際に必要なのは10通りだとすれば、これは無駄に生成することになります。ですので、全探索を打ち切る必要が出てきます。木構造の全探索を実装しようとすると、生成するデータが非常に大きい場合にはスタックオーバーフローの恐れがあります。呼び出し状態を管理するスタックに出し入れすることで末尾再帰化しています。
といった感じで実装しています。では実際に使用例を軽く乗っけておきます。
使用例
- 呼び出し
import csdfs.mysql.MySQLCsdfs val csdfs = csdfs.mysql.MySQLCsdfs.instance csdfs.generateInsertStatements( Seq( """ |create table table1 ( | id int not null auto_increment, | column1 mediumint not null unique, | column2 enum('value1', 'value2') null, | column3 varchar(64) not null, | | foreign key (column3) | references table2 (column2) |) """.stripMargin, """ |create table table2 ( | id int not null auto_increment, | column1 char not null, | column2 varchar(64) not null, | | primary key (id, column1) |) """.stripMargin ), GenConf( Map((Table("table1"), GenConf.GenTableConf(Table("table1"), 10, Map((Column("column2"), GenConf.GenColumnConf(Column("column2"), cardinality = 2)))))) ) )
- 返却値
Right(List( """INSERT INTO table1 (id, column1, column2, column3) VALUES (628505154, 6367780, value1, 'twpLBNpv0TYukg4vYQHZ8GTCKrZxc37ZIQvOziPNIffaJ2g4SBI8FvjCg8OYNxTQ'), (1802938845, 16516874, value1, 'QeNg2mt0XiXUjK1US4sAaDvlO5J8ccQLe3MpqBfd19jIJRhmWCDXYVGxqJ0UJneH'), (122688015, 7907605, value1, 'KNwEHrgqKPg9wDkY6Ix7XAgO1WVEywI2BgyrU4Kj0uEkw3hnBm2iMqlDmWCWb1MV'), (738062263, 6200986, value1, 'tG1v1w5A03Pz4RKZUvg6lbrS29v9C66uCGqNk8iGl32YLjoRdkQwMdHPHvuDT58l'), (887723347, 5461001, value1, 'ZcrDCsMD8aNVQi2ma35cmwOuKcDbijxCENUtZlIQvT9qPheCbnCAxZ8uL58etFzn'), (76465175, 13984979, value1, 'VEtdhE8RlfxQ2F4tqv0oJ1eflJzfSV25hO6hU5hBjbcmOn3k6z8amS9M4IB376j6'), (217732770, 14715916, value1, '2GdCrDXINvBD7NWvKmeXFsPqAsmGg9hd21X2nB6KTDykaA0iyFZLvOUPerJS1Hww'), (227990809, 6879586, value1, 'hkEOdLYQ3ju0NH3lwMsYFVREtHuh84AZXzWdkOFNc3wjlTszuC19YtZ6v76APGEu'), (1740430165, 13493854, value2, '7nLgoLxQoqu6B8B77y0ZI57XgMbXBv0uQ5nfkF0CyYhQJInvJDby0ZLocFRjQIzE'), (296392555, 261530, value1, 'vqF3QStaVdS9xh9K7vQBgWkdLLrmvGCt1mMxqrmjrlUidm8FNY5riaLp8PskTO1e');""", """INSERT INTO table2 (column2, id, column1) VALUES ('VEtdhE8RlfxQ2F4tqv0oJ1eflJzfSV25hO6hU5hBjbcmOn3k6z8amS9M4IB376j6', 1257347204, '6'), ('2GdCrDXINvBD7NWvKmeXFsPqAsmGg9hd21X2nB6KTDykaA0iyFZLvOUPerJS1Hww', 1257347204, 'l'), ('KNwEHrgqKPg9wDkY6Ix7XAgO1WVEywI2BgyrU4Kj0uEkw3hnBm2iMqlDmWCWb1MV', 1257347204, 'F'), ('tG1v1w5A03Pz4RKZUvg6lbrS29v9C66uCGqNk8iGl32YLjoRdkQwMdHPHvuDT58l', 1257347204, 'm'), ('hkEOdLYQ3ju0NH3lwMsYFVREtHuh84AZXzWdkOFNc3wjlTszuC19YtZ6v76APGEu', 1257347204, 'b'), ('vqF3QStaVdS9xh9K7vQBgWkdLLrmvGCt1mMxqrmjrlUidm8FNY5riaLp8PskTO1e', 1257347204, 'x'), ('QeNg2mt0XiXUjK1US4sAaDvlO5J8ccQLe3MpqBfd19jIJRhmWCDXYVGxqJ0UJneH', 1257347204, 'A'), ('7nLgoLxQoqu6B8B77y0ZI57XgMbXBv0uQ5nfkF0CyYhQJInvJDby0ZLocFRjQIzE', 1257347204, 'v'), ('ZcrDCsMD8aNVQi2ma35cmwOuKcDbijxCENUtZlIQvT9qPheCbnCAxZ8uL58etFzn', 1257347204, 'd'), ('twpLBNpv0TYukg4vYQHZ8GTCKrZxc37ZIQvOziPNIffaJ2g4SBI8FvjCg8OYNxTQ', 1257347204, 'U');"""))
今回作成したもののリポジトリは以下になります。
