こんにちは。
プロダクト開発部の伊藤です。
2025年2月からジンジャーにジョインしました。
入社早々のテックブログの執筆担当依頼でしたが、色々な企業さまのテックブログをちょくちょく拝見していて、実は少しやってみたいと思っていました。
過去に、テーブル設計をおこなう中で「文字列型のカラムに特定の文字列だけを格納させる制約をつけたい」と考えたことがあります。
「アプリケーション側でバリデーションすべきか？」など少し悩んでいたのですが、「Enum型でカラムを作成してしまえばいいのでは？」と思いつきました。
テーブル側で特定の文字列以外は弾く設定にしてしまえば、手間が省けます。
ただ、その「特定の文字列」が複数パターンあって、さらに今後パターンが増えたり減ったりするとなると、「運用が面倒なのでは...？レコードが大量に存在してしまっている場合は大丈夫なのか...？」などの懸念があり、その際は結局導入に至りませんでした。
上記の経験から、今回は「MySQLのEnum型をalter tableするとき、どのようなときに遅くなるのか」について検証してみようと思います。
そもそもEnumとは？
"ENUM は、テーブル作成時にカラム仕様に明示的に列挙された、許可されている値のリストから選択された値を持つ文字列オブジェクトです。"とのこと。
CREATE TABLE shirts (
name VARCHAR(40),
size ENUM('x-small', 'small', 'medium', 'large', 'x-large')
);
https://dev.mysql.com/doc/refman/8.0/ja/enum.html （MySQL 8.0 リファレンスマニュアル 11.3.5 ENUM 型より抜粋）
上記のような形でテーブルを作成した場合、shirtsテーブルのsizeカラムは、ENUMとして定義した「'x-small', 'small', 'medium', 'large', 'x-large'」の文字列しか許容しません。
「'xx-small'」や「'xx-large'」といった未定義の値をsizeカラムに格納できないということになります。
Enum型を持つテーブルに対してALTER TABLEしていく
テスト用に、先述したクエリでEnum型を持つテーブルを作成してみます。（一応プライマリキーとしてidも追加）
CREATE TABLE shirts (
id int(11) AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(40),
size ENUM('x-small', 'small', 'medium', 'large', 'x-large', 'xx-large')
);
20万件ほどレコードを追加しました。
- 実行後のshirtsテーブル
各レコードの値はランダムに挿入されています。
この状態でEnumの再定義を実行してみます。
では、このテーブルに対してALTER TABLEしていってみましょう。
定義済みのEnumに新たな値を定義
定義済みのEnumに新たな値、'xx-small'を追加して再定義してみます。
ALTER TABLE shirts
MODIFY COLUMN size ENUM('x-small', 'small', 'medium', 'large', 'x-large', 'xx-large', 'xx-small');
- 実行結果
かなり高速で終わりました。
定義済みのEnumの順番を変えて再定義
「'x-small', 'small', 'medium', 'large', 'x-large', 'xx-large', 'xx-small'」として定義したEnumを、「'small', 'medium', 'large', 'x-large', 'xx-large', 'xx-small', 'x-small'」の順番に変更して再定義してみます。
ALTER TABLE shirts
MODIFY COLUMN size ENUM('small', 'medium', 'large', 'x-large', 'xx-large', 'xx-small', 'x-small');
- 実行結果
先ほどよりかなり実行時間が長くなりました。
定義済みのEnumから特定の値を除いて再定義
「'small', 'medium', 'large', 'x-large', 'xx-large', 'xx-small', 'x-small'」として定義されたEnumから'small'を削除し、再定義します。
ALTER TABLE shirts
MODIFY COLUMN size ENUM('medium', 'large', 'x-large', 'xx-large', 'xx-small', 'x-small');
- 実行結果
こちらもかなり実行時間が長くなります。
'medium','large'も削除して実行してみます。
ALTER TABLE shirts
MODIFY COLUMN size ENUM('x-large', 'xx-large', 'xx-small', 'x-small');
- 実行結果
先ほどよりは短いですが、追加定義時に比べるとかなり長い実行時間です。
- 実行後のshirtsテーブル
'small','medium','large'の値がレコードからも削除され、空値になっているようです。
上記の空欄になったカラムの値がどのような扱いになっているのかを検証してみます。
空の文字列で絞り込んでみます。
SELECT * FROM shirts WHERE size = "";
- 実行結果
どうやら、空文字列と見なされているようです。
検証結果
定義済みのEnumに新たな値を追加して定義する場合は、短い実行時間で完了しました。
定義済みのEnumから特定の値を削除する場合、該当の値を持つレコードのカラムの値の削除もおこなっているようなので、多少時間がかかるようです。
定義済みのEnumの順番を変えて再定義する場合も、定義済みのEnumにインデックスを振り直して再定義し（https://dev.mysql.com/doc/refman/8.0/ja/enum.html （MySQL 8.0 リファレンスマニュアル 11.3.5 ENUM 型より）、既存のレコードのEnum値へ再度割り振る必要があるため、こちらも同様多少時間がかかるようでした。
（ちなみに、EnumのインデックスでもWHERE句で絞り込めるようで、前述の空欄になったカラムのインデックスは0とも見なされており、SELECT * FROM shirts WHERE size = 0;を実行してもsizeカラムが空値のレコードが取得されます。）
一度定義したEnumに新たな値追加以外の変更をおこなう場合は、そのテーブルの全レコードを確認する必要があるので、実行時間が長くなるのではと思われます。
今回はダミーデータでしたが、実務のデータとなるともっと複雑で大量のレコードのパターンもあるかと思うので、その場合は当然さらに実行時間が長くなるでしょう。
Enum型について
「mysql enum」で検索すると、「mysql enum アンチパターン」といった候補が出てきます。
こちらの記事にもあるように、Enumの値に変更があった際に変更コストが高いというデメリットがあるようです。
やはり長期的にプロダクトを運用・変更していくことを考えると、Enum型のカラムの採用は慎重に考えるべきかと思います。
冒頭にも書いた、諸々の懸念によってEnum型のカラムの導入をしなかった判断は正しかったのかもしれません。
また、Enum型としてカラムを作成するのでなく、外部キーとして参照させるという解決策もあるとのこと。
最後に
拙い記事だったかもしれませんが、どこかで誰かの役に立てれば幸いです。
まだ入社したばかりですが、とてもポジティブかつチャレンジングな環境なので、これからジンジャーをどんどん良いプロダクトにするためにレベルアップし、日本一の開発組織を目指していきたいと思います。
興味がある方はぜひ一緒に日本一を目指しましょう。
Discussion