MySQL初心者が311個のカラムにNot NULL制約を追加していってALTER TABLEについて学んだ話

MySQL初心者が311個のカラムに Not NULL制約を追加していって ALTER TABLEについて学んだ話 Roppongi.rb #30 @hatsu_38
2025/05/08

自己紹介 • Ruby歴 5年 = エンジニア歴 • SHE Inc. •
React.js / TypeScript / GitHub Actions • Rubyが一番好き

技術スタック • Backend: Ruby, Ruby on Rails, GraphQL, Sidekiq •
Frontend: React.js, TypeScript, Next.js • Database: MySQL 8.0 • CI: GitHub Actions • Infrastructure: Kubernetes

Validationのpresence: ture しかないnullableが多くあった 💭

Validationだけではnullを防げない

validates :xxx, presence: trueだけあるカラムを探す • active_record_doctor gemを使った • database_consistency
gemもあるが、今回の例では active_record_doctorが使いやすかった • presence: trueがあるけどnot null制約がないとか • 外部キー制約が存在しないとかを検知してくれる • presence: trueだがnot null制約のないカラムが117テーブル / 311カラム発見された

Not NULL制約を追加していく 🚀

MySQL 8.0.32 - REPEATABLE-READ

オンライン DDLがあるとはいえ、本番運用中のテーブルに Not NULL制約追加しても影響がでないか？ 🤔

MySQL 8.0 生成されたカラム操作のオンライン DDL サポート https://dev.mysql.com/doc/refman/8.0/ja/innodb-online-ddl-operations.html#online-ddl-column-operations

メタデータとは • テーブルの構造や定義に関する情報のこと • カラム名やデータ型、制約(Not NULL, Primary Keyなど) •
実際のレコード(データ)とは別物 • スキーマ変更時には、このメタデータにロックがかかることが多い • SELECT, INSERTなどのDMLでも共有メタデータロックがかかる • ALTER TABLE実行時の多くの場合、排他メタデータロックがかかる

共有MDLと排他MDL 排他MDL中は、他のMDLは動かせない共有MDL 排他MDL 共有MDL ♻競合しない 💥競合する排他MDL 💥競合する 💥競合する

MySQL 8.0 生成されたカラム操作のオンライン DDL サポート https://dev.mysql.com/doc/refman/8.0/ja/innodb-online-ddl-operations.html#online-ddl-column-operations

ALGORITHMの違い特徴 INSTANT INPLACE COPY 主な用途カラム追加（末尾）、デフォルト値変更（SET DEFAULT）など、メタデータのみの変更で済む操作多くの一般的なALTER TABLE操作
(カラムの追加・削除、インデックスの追加など) テーブル構造を大きく変更する場合 (データ型変更、文字セット変更など) テーブル再構築不要不要だったり必要だったりする必要処理速度非常に速い中程度遅い排他的メタデータロック (非常に短時間取得する ) 準備および実行中に取得されない操作の準備・実行フェーズで短時間取得される場合がある操作期間中、テーブル全体に排他 MDLを取得する

ALGORITHM=INPLACEの実行時の 3ステップ 1. 初期化: ALGORITHMの決定(共有メタデータロックを取得する) 2. 実行: テーブルのコピーなどの実行(実行直後は排他メタデータロックを取得。解放後共有メタデータロックに
なる) 3. コミット: DDLコミット(実行直後は排他メタデータロックを取得し、コミットする)

ロック時間の注意ポイント 🔐

注意1 t1テーブルへの ALTER TABLE実行前に別のセッションで t1テーブルへの DML が走って共有メタデータロックが取得されている場合

注意2: t1テーブルへの ALTER TABLE実行中(DDLコミット前 )に別のセッションで t1で DMLが走って共有メタデータロックが取得されている場合

注意3 セッション Aでトランザクションが走るが t2テーブルの DMLしかない→セッション 2 でt1テーブルの DDL走る→セッション Aのトランザクションで t2テーブルの
DMLが実行された場合

ALTER TABLE実行時間⏰

おわりに 1. 次は外部キー制約入ってない箇所をやっていきたい 2. でも外部キー制約の追加は ALGORITHM=COPYになりそうで大変な気が... 3. ActiveRecordDoctor gemだと偽陽性の検知が多かったので直すPRを出しています
4. ALTER TABLE実行時の多くの場合、排他メタデータロックかかる

MySQL初心者が311個のカラムにNot NULL制約を追加していってALTER TABLE...

MySQL初心者が311個のカラムにNot NULL制約を追加していってALTER TABLEについて学んだ話

hatsu

More Decks by hatsu

Other Decks in Programming

Featured

Transcript