MySQLのCharsetをutf8mb3からutf8mb4に移行した話

2023年12月27日

松浦広樹

URLをコピーしました！

こんにちは。開発本部の23卒の松浦です。今回は、WebClippingで使用しているテーブルの一部のカラムのCharsetをutf8mb3からutf8mb4に移行した話について書こうと思います。

何が問題か

前提として移行前の多くのテーブル構成は下記の通りでした。

Charset: utf8mb3
Collation: utf8mb3_general_ci

上記の構成では絵文字（🍣、😭、🍺など）や、特殊文字（𠀋、𠮷など）のUTF-8で1文字4バイトになる文字を保存することができません。上記の設定で保存できるのは1文字3バイト以下の文字のみです。

WebClippingでは、クローラーでサイトから記事を取得するといったサービスの特性上、記事内にこのような特殊文字が使用されているとエラーとなり、テーブルにデータを挿入することができません。近年、絵文字がよく使用される場面が増えていることや、旧字体などの特殊文字が保存できないと記事の正確性の面において懸念があったため、早急な対応が必要でした。

対応といっても、utf8mb3をutf8mb4にするALTER TABLEを全てのテーブルに実行するだけでは？と、思うかもしれませんが、実際には様々な課題がありました。

移行に時間がかかる

記事を取り込むといった性質上、テーブルによってはサイズが20GBを超えるものもありました。全てのテーブルをutf8mb4にするのが理想ですが、いざ実行するとなると膨大な時間がかかってしまうことがわかりました。

長時間データベースに大きな負荷をかけ続けるのは何が起こるかわからないので、優先度の高い2つのタスクをまず進めることにしました。

クローラーが使用するカラムのみutf8mb4に移行する。
キーワードなど、ユーザーの入力値を保存するカラムのみutf8mb4に移行する。

テーブルロックの恐れ

下記にutf8mb3からutf8mb4に移行するシンプルなSQLがあります。

ALTER TABLE tbl_name MODIFY col_name VARCHAR(255) NOT NULL, CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;

これを見て、テーブル情報を変更するとテーブルロックされるのではないか？と、思われるかもしれません。MySQL 5.6以降から用意されているOnline DDLという機能により、一部の設定はテーブルロックせず変更することができるようになりました。また、MySQL8以降では今回のケースであるutf8mb3からutf8mb4に移行する際でもOnline DDLが利用できるようになりました。

https://dev.mysql.com/doc/refman/8.0/ja/innodb-online-ddl.html

しかし、Online DDLに対応しているものであっても、条件によっては使用できないパターンもあります！

今回あったパターンでは、変更するカラムにインデックスがあった場合、テーブルロックされてしまうことがローカル環境で検証した際にわかりました。下記ドキュメント内に書かれていますが、インデックスがある場合はテーブルロックされてしまうようでした。Web Clippingでは使用していないインデックスが多数あり、今回は使用していないインデックスだったためスキーマ改善の一環として最初にインデックスを削除してからutf8mb4に移行するALTER TABLEを流すように対応しました。

https://dev.mysql.com/doc/refman/8.0/ja/alter-table.html#alter-table-performance

今回はローカル環境でしたが仮に本番環境だった場合、思わぬ障害につながっていた可能性がありました。前述のSQLのままではテーブルロックされてしまう可能性がありますが、下記SQLのようにオプションを指定すると仮にテーブルロックが必要だった場合、SQLを実行せずエラーとして出力してくれます。

ALTER TABLE tbl_name MODIFY col_name VARCHAR(255) NOT NULL, CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci, ALGORITHM = INPLACE, LOCK = NONE;

このように、条件によってはできないものも存在するので実際にやる場合は、Online DDLとして実行できるかどうか一度ドキュメントを確認することをお勧めします。また、誤ってテーブルロックされないように、LOCKなどのオプションをつけることを推奨します。

https://dev.mysql.com/doc/refman/8.0/ja/innodb-online-ddl-operations.html

結果

Staging環境や本番のレプリカなどで何度かテストを重ねた結果、無事utf8mb4に移行することに成功しました！　特に大きな問題が発生することはなく、2時間程度で完了しました。

反省点

しかし無事移行は成功したものの、いくつか考慮すべきだった点がありました。

Free Storage Space

今回はテーブルサイズが20GBほどのテーブルも移行しました。移行するにあたってMySQL内部でコピーが作られるため一時的に今あるテーブルサイズの2倍必要になります。また最終的に、utf8mb4に移行するためにテーブル自体のサイズがそれぞれ4/3倍になります。しかしそこまで考慮できていませんでした。これによって、ストレージ容量不足になってしまう可能性がありました。