unit test の動作速度を速めるためにMySQLからH2 databaseに移行しようとした話

次世代システム研究室の Y.I です。H2 Database（以下 H2）について、MySQL 8向けに動作する unit test コードの速度アップを目的として、unit test用DBを H2 へ変更しようとして得た知見をまとめます。 最終的に変更を断念したのですが、その内容も含めて知見として残しておければと思います。

一般的な違い

H2 DatabaseとMySQL 8は、異なる用途と環境に適した特徴を持っています。

• H2は主に開発やテスト環境向け、MySQLは本番環境や大規模アプリケーション向け
• H2は組み込みおよびインメモリデータベース、MySQLはトラディショナルなリレーショナルデータベース
• H2は軽量で高速、MySQLは大量のデータ処理と高い並行処理をサポート
• H2はインメモリ操作に特化したSQL機能を提供し、軽量な処理に重点を置いている
• H2はモードにより各種DBのSQLにほぼ対応できる（MODE=MySQL/MariaDB/PostgreSQL/Oracle/MSSQLServer/DB2など）

該当バージョン

検証した各DBバージョンはこちらになります。

• H2 2.2.224
• MySQL 8.0.28

SQLサポートの違い

MySQLからH2へ移行する際に修正が必要だったSQLです。

set コマンドで使えるものが異なる

MySQLで使える SET コマンドがなかったり異なるコマンドになっている

=>対策： 該当のコマンドを H2公式(https://h2database.com/html/commands.html)から探す。コマンドがなければ対策なし。

- 文字設定
(MySQL)  SET CHARACTER_SET_CLIENT
(H2) なし

- 外部キー制約 ON/OFF
(MySQL)  SET FOREIGN_KEY_CHECKS
(H2) SET REFERENTIAL_INTEGRITY　（※ただし試した環境では機能しなかった）

- カレントスキーマ変更
(MySQL)  use {DB}
(H2) SET SCHEMA {SCHEMA_NAME}
など

mysql変数は使えない

MySQLで変数に値を登録しておき後で元に戻すなど利用できるがH2ではエラーになる

=>対策： なし

(MySQL)  SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT ;
(H2) なし

INDEX名はスキーマ内でユニークにする必要がある

MySQLはテーブル単位でINDEX名がユニークならば動作するが、H2はschema単位でユニークにする必要がある

=>対策： スキーマ内でユニークになるようにINDEX名を定義する

(MySQL) TABLE A ... `KEY `idx_01` (`column1`);  TABLE B ... `KEY `idx_01` (`column1`);　　テーブルが異なるので重複OK
(H2)  TABLE A ... `KEY `idx_01` (`column1`);  TABLE B ... `KEY `idx_01` (`column1`);　　　スキーマ内で重複NG

set型が利用できない

MySQLにある set型が h2には存在せず利用できない。ただしENUM型は利用できる。

=>対策： ENUM型を利用する（要件が許せば）

(H2) 
CREATE TABLE ....
`column1` SET('PC', 'SP') CHARACTER -- SET utf8mb4 NOT NULL,
Unknown data type: "SET"; SQL statement:

FULLTEXT KEY は使えない

MySQLで使える全文検索キー FULL TEXT KEYはH2では利用できずエラーとなる。

=>対策： なし

(H2) 
CREATE TABLE .... FULLTEXT KEY `ft_title_description` (`title`,`description`)
org.h2.jdbc.JdbcSQLNonTransientException: 不明なデータ型: "KEY"
Unknown data type: "KEY"; SQL statement:

DEFAULT NULL がエラーとなる

MySQLで設定できる　DEFAULT NULL が以下のカラム定義でエラーとなる。

=>対策： なし（要件が許せばDEFAULT NULLを除外）

CREATE TABLE  ...
     `column1` varchar(2500) COLLATE utf8mb4_bin DEFAULT NULL

enum カラムに DEFAULT 設定があるとエラー

MySQLで利用できるenum型にDEFAULT設定があるとH2ではエラーとなる。

=>対策： DEFAULT箇所を除外（要件が許せば）

(H2) 
CREATE TABLE ....
        `column1` enum('DAILY', 'FULL') COLLATE utf8mb4_bin NOT NULL DEFAULT 'DAILY' 

カラムにCOLLATEとDEFAULT定義あるとエラー

MySQLではテーブルおよびカラムにCOLLATEを設定できるが、H2ではエラーになる。

=>対策： カラムのCOLLATE設定をやめてテーブル単位で設定する

(H2) 
CREATE TABLE ....
    `column1` varchar(255) COLLATE utf8mb4_bin NOT NULL DEFAULT '',

enum型カラムに charset utf8mb4 がエラーとなる

=>対策： テーブル定義にcharsetを定義してカラム定義のcharsetを削除する

(H2) 
CREATE TABLE ....
    `column1` enum('aa', 'bb') charset utf8mb4 NOT NULL 

decimal型カラムに unsigned 指定があるとエラー

H2でdecimal型は使えるが unsigned 指定に対応していない。（検証したバージョンにて）

=>対策： なし。（要件が許せば） unsigned 指定をやめる

(H2) 
CREATE TABLE ....
    `column1` decimal(5, 4) unsigned NOT NULL DEFAULT 0 

まとめ

H2にMySQLモードで動作する設定がありますが、細かいところなど多数の違いがあることがわかりました。標準SQLに沿ったSQLだったり、シンプルなSQLならば問題なくH2で動作しますが、MySQL拡張設定などを利用する場合、（要件が許せば）TABLE定義を変更するなどしない限りH2で代替とすることは難しいかもしれません。 ですが、unit testを目的とするならば、 DEFAULT設定やSET型を一時的に変更してテストすることは可能です。筆者が進めていた対策は、unit testのinit処理でCREATE TABLEする際に、CREATE TABLE文のH2でエラーとなる箇所を置換してから実行することで対応していました。

・置換コマンド（Mac）

sed -i '' '/--/d' schema-.sql;
sed -i '' '/^SET /d' schema-.sql;
sed -i '' 's/SET(/enum(/' schema-.sql;
sed -i '' 's/set(/enum(/' schema-.sql;
sed -i '' '/FULLTEXT KEY/d' schema-.sql;
sed -i '' '/CONSTRAINT/d' schema-.sql;
sed -i '' '/KEY `/d' schema-.sql;
sed -i '' 's/DEFAULT NULL//' schema-.sql;
sed -i '' "s/DEFAULT 'DAILY'//" schema-.sql
sed -i '' "s/DEFAULT 'NON'//" schema-.sql
sed -i '' 's/COLLATE utf8mb4_bin//' schema-.sql;
sed -i '' 's/charset utf8mb4//' schema-.sql;

# 末尾カンマ削除
sed -e ':a' -e 'N' -e '$!ba' -e 's/\n/ /g' schema-.sql > schema--h2.sql;
sed -i '' 's/, ) ENGINE/ ) ENGINE/g' schema--h2.sql;
sed -i '' 's/,  ) ENGINE/ ) ENGINE/g' schema--h2.sql;
sed -i '' 's/, )ENGINE/ ) ENGINE/g' schema--h2.sql;
sed -i '' 's/;/;\n\n/g'  schema--h2.sql;

最後に

グループ研究開発本部では、グループ全体のインテグレーションを支援してくれるアーキテクトを募集しています。アプリケーション開発の方、次世代システム研究室にご興味を持って頂ける方がいらっしゃいましたら、ぜひ募集職種一覧からご応募をお願いします。