【MySQL】ENUMのALTERについて

mysql

MySQLでALTER文を発行してテーブル定義を変えると、相当な時間がかかったりしますが、ENUMに追加するだけなら一瞬で終わります。

時間がかかる理由

そもそも、ALTERが時間がかかるのは、古いテーブルから新しいテーブルにデータを移行するからです。
ALTER文の対象になるテーブルのデータ量が多ければ多いほど実行時間が長くなります。

通常は、

新しい定義のテンポラリテーブルを作る。

古いテーブルから新しいテーブルにデータをINSERTする。

全部INSERTできてから、古いテーブルを削除。

テンポラリテーブルを生成なテーブルにする。

という手順を踏みます。

しかし、そのALTER文を発行しても、既存のデータに影響がないことが確定している場合は、テーブルの作り直しとデータ移行を行わないため、あっという間にALTER文が終わります。

例えば、列につけるコメントを変更しても、既存のデータには何の影響もありませんので、そのテーブルにどんなに大量のデータが蓄積されていたとしても、一瞬で完了します。

ENUMの場合

ENUMについて

ENUMは文字列定数の集合体で列挙型といいます。
列に追加するデータを限定するために使います。

今、肉屋さんの商品を管理するproductテーブルがあったとします。

要素
id int
category enum beef, pork
name char

categoryにenumを使っていて、要素は’beef’と’pork’です。

最後に追加

牛肉と豚肉しか扱ってなかったけど、鶏肉も扱うことにしたので、categoryにchickenを追加することにします。

ALTER TABLE product MODIFY category ENUM (‘beef’, ‘pork’, ‘chicken’);

これだと、すぐに終わります。

途中に追加

もし、アルファベット順にしたいとかで、下記のようにしたとします。

ALTER TABLE product modify category ENUM (‘beef’, ‘chicken’, ‘pork’);

これは、データのコピーが発生するので、データ量によっては、時間がかかります。

なぜでしょうか?

ENUMは内部的には、整数値で管理されています。

最初の段階では、
‘beef’ => 1
‘pork’ => 2
です。

以下のようなSQLを発行しとします。

INSERT INTO product VALUES (1, ‘pork’, ‘sirloin’);

そうすると、productテーブルには「’pork’」ではなく「2」として記録されます。

上記のALTER文を実行すると、
‘beef’ => 1
‘chiken’ => 2
‘pork’ => 3
に変わります。

‘pork’の定義は「3」になったのですが、テーブルに入っているデータは「2」なので’chiken’です。
‘pork’で入れたはずなのに’chiken’になっています。
データを「3」に書き換えないといけません。
新規にテーブルを作り古いテーブルからデータを取り出しつつ修正していくので遅くなります。

変更/削除はNG

‘chicken’にするつもりが間違って’chickin’にしたとします。

ALTER TABLE product MODIFY category ENUM (‘beef’, ‘pork’, ‘chickin’);

慌てて、もう一回下記を実行して直したとします。

ALTER TABLE product MODIFY category ENUM (‘beef’, ‘pork’, ‘chicken’);

1回目の’chickin’はすぐに終わります。

2回目の’chicken’の方は、データのコピーがおきるので時間がかかります。

まだ’chickin’のデータ入れていないから、関係ないからすぐに終わるはず思うのは人間側の勝手な解釈です。
MySQLはそんなこと知りません。’chickin’は候補からなくなったので、入っていたら消さないといけませんので遅くなります。

他の制約に注意

罠っぽいやつを一点。

NULLを許可したくないから、ついでにNOT NULL制約をつけようとしたとします。

ALTER TABLE product MODIFY category ENUM (‘beef’, ‘pork’, ‘chickin’) NOT NULL;

これは、当然遅いパターンです。

注意したいのは、この後さらに、’horsemeat’(馬肉!!)を追加しようとして、

ALTER TABLE product MODIFY category ENUM (‘beef’, ‘pork’, ‘chickin’, ‘horsemeat’);

ってやったときに、追加しただけのつもりなのに、意図せず「NOT NULL」から「DEFAULT NULL」に変わって、すぐ終わるはずが、場合によってはいつまでたっても終わらないという悲劇になります。

1バイトの壁

もう一点落とし穴を。

MySQLはENUMを数値で管理していると書きましたが、上記の例だと1byteもあれば十分ですので、MySQLはcategoryのために1byteの領域を確保して管理します。
1byteで表現できるのは256なので、256(種類の文字列の羅列)を超える場合は、2byte必要になります。そうすると、データ領域を2byteに拡張しないとデータが格納できなくなるので、テーブルの作り直しが発生します。

255番目の要素を追加するALTERは高速だけど、256番目を追加するALTERは遅くなります。

勘のいい方は「あれ、ちょっと間違っていない?」と思われたでしょうか?

ENUMは暗黙のうちに空文字を0に割り当てます。
(要素以外のを入れると0で格納されます)
ですので、257番目ではなく256番目になります。

先端技術・イノベーション領域に強いエンジニア転職支援サービス【Kaguya】無料会員登録

おまけ

ALTERがいつ終わるかは以下のコマンドで予想できます。

SHOW GLOBAL STATUS LIKE ‘Handler_write’;
[ALTER完了後の値] – [ALTER実行前の値] = [対象テーブルのデータ件数] + 1 になります。

実行前に一度確認してから都度実行することで進捗がわかります。