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

Transcript

1. MySQL初心者が311個のカラムに Not NULL制 約を追加していって ALTER TABLEについて学 んだ話 Roppongi.rb #30 @hatsu_38 2025/05/08
2. 自己紹介 • Ruby歴 5年 = エンジニア歴 • SHE Inc. • React.js / TypeScript / GitHub Actions • Rubyが一番好き
3. 技術スタック • Backend: Ruby, Ruby on Rails, GraphQL, Sidekiq • Frontend: React.js, TypeScript, Next.js • Database: MySQL 8.0 • CI: GitHub Actions • Infrastructure: Kubernetes
4. Validationのpresence: ture しかないnullableが多くあった 💭
5. Validationだけではnullを防げない
6. Validationだけではnullを防げない
7. validates :xxx, presence: trueだけあるカラムを探 す • active_record_doctor gemを使った • database_consistency gemもあるが、今回の例では active_record_doctorが使いやすかった • presence: trueがあるけどnot null制約がないとか • 外部キー制約が存在しないとかを検知してくれる • presence: trueだがnot null制約のないカラムが117テーブル / 311カラ ム発見された
8. Not NULL制約を追加していく 🚀
9. MySQL 8.0.32 - REPEATABLE-READ
10. オンライン DDLがあるとはいえ、 本番運用中のテーブルに Not NULL制約追加しても影響がでないか？ 🤔
11. MySQL 8.0 生成されたカラム操作のオンライン DDL サポート https://dev.mysql.com/doc/refman/8.0/ja/innodb-online-ddl-operations.html#online-ddl-column-operations
12. メタデータとは • テーブルの構造や定義に関する情報 のこと • カラム名やデータ型、制約(Not NULL, Primary Keyなど) • 実際のレコード(データ)とは別物 • スキーマ変更時には、このメタデータにロックがかかることが多い • SELECT, INSERTなどのDMLでも共有メタデータロック がかかる • ALTER TABLE実行時の多くの場合、排他メタデータロック がかかる
13. 共有MDLと排他MDL 排他MDL中は、他のMDLは動かせない 共有MDL 排他MDL 共有MDL ♻競合しない 💥競合する 排他MDL 💥競合する 💥競合する
14. MySQL 8.0 生成されたカラム操作のオンライン DDL サポート https://dev.mysql.com/doc/refman/8.0/ja/innodb-online-ddl-operations.html#online-ddl-column-operations
15. ALGORITHMの違い 特徴 INSTANT INPLACE COPY 主な用途 カラム追加（末尾）、デフォルト値変更（SET DEFAULT）など、メタデータのみの変更で済む操作 多くの一般的なALTER TABLE操作 (カラムの追加・ 削除、インデックスの追加など) テーブル構造を大きく変更する場合 (データ型変更、文字セット変更 など) テーブル再構築 不要 不要だったり必要だったりする 必要 処理速度 非常に速い 中程度 遅い 排他的メタデータロック (非常に短時間取得する ) 準備および実行中に取得されない 操作の準備・実行フェーズで短時間取得される場合 がある 操作期間中、テーブル全体に排他 MDLを取得する
16. ALGORITHM=INPLACEの実行時の 3ステップ 1. 初期化: ALGORITHMの決定(共有メタデータロックを取 得する) 2. 実行: テーブルのコピーなどの実行(実行直後は排他メ タデータロックを取得。解放後共有メタデータロックに なる) 3. コミット: DDLコミット(実行直後は排他メタデータロック を取得し、コミットする)
17. ロック時間の注意ポイント 🔐
18. 注意1 t1テーブルへの ALTER TABLE実行前に 別のセッションで t1テーブルへの DML が走って共有メタデータロックが取得されている場合
19. 注意2: t1テーブルへの ALTER TABLE実行中(DDLコミット前 )に 別のセッションで t1で DMLが走って共有メタデータロックが取得されている場合
20. 注意3 セッション Aでトランザクションが走るが t2テーブルの DMLしかない→セッション 2 でt1テーブルの DDL走る→セッション Aのトランザクションで t2テーブルの DMLが 実行された場合
21. ALTER TABLE実行時間⏰
23. おわりに 1. 次は外部キー制約入ってない箇所をやっていきたい 2. でも外部キー制約の追加は ALGORITHM=COPYになりそうで大変な気が... 3. ActiveRecordDoctor gemだと偽陽性の検知が多かったので直すPRを出してい ます 4. ALTER TABLE実行時の多くの場合、排他メタデータロックかかる