Zenn
🔒

MySQLのスキーマ変更：テーブルはロックされるのか？

に公開
Web
Database
プログラミング
backend
tech

表紙

MySQL テーブルの構造変更は、しばしばテーブルレベルのロックの問題を伴います。

特にデータ量の多いテーブルにおいては、これが業務システムのパフォーマンスに大きな影響を与えることがあります。

テーブル構造の変更操作を最適化することで、開発者はロック時間を回避または最小限に抑え、システムの正常な運用を確保できます。

テーブルレベルロックの紹介

テーブルレベルロックとは、ある操作を実行する際にデータの一貫性を確保するため、テーブル全体にロックをかけることを指します。

具体的には：

テーブルに対して ALTER TABLE 操作を実行すると、MySQL はデフォルトでテーブルをロックし、その操作が完了するまで他のトランザクションによる読み書きをブロックします。

このようなテーブルロックの挙動は、小規模なテーブルや高い並列性を必要としない状況では大きな問題になりません。

しかし、大量のデータを持つテーブルや高並列アクセスのある業務システムでは、テーブルロックが深刻なパフォーマンスボトルネックとなり、場合によってはサービスダウンを引き起こすこともあります。

テーブルロックの具体的影響

MySQL がカラム追加操作を行う際、テーブルロックによって以下のような問題が発生します：

  • クエリの待機：そのテーブルを参照する全ての SQL クエリがロック解除までブロックされます。
  • 書き込み操作の停止：INSERT、UPDATE、DELETE などの書き込み操作もロック解除まで待機状態になります。
  • システム応答の遅延：ロック状態が長引くと、業務システム全体のパフォーマンスが著しく低下し、最悪の場合システムダウンを引き起こします。

ただし、新しいバージョンの MySQL では、InnoDB ストレージエンジンを使用することで、テーブルにカラムを追加する際に必ずしもテーブルをロックするとは限りません。

InnoDB ストレージエンジンは、並列処理性能を向上させるため、テーブルロックを軽減するいくつかの仕組みを提供しています。

MySQL においてテーブルにカラムを追加する際にテーブルがロックされるかどうかは、使用しているストレージエンジンと MySQL のバージョンによって異なります。

MySQL5.6 以前

MySQL の古いバージョンでは、ALTER TABLE コマンドでカラムを追加する場合、InnoDB ストレージエンジンを使用していても、デフォルトではテーブルがロックされます。

つまり、操作の実行中はテーブル全体がロックされ、その他の読み書き操作はすべてブロックされ、操作が完了するまで待機する必要があります。

このような全体ロックの挙動により、大規模テーブルに対して ALTER TABLE 操作を行うと、長時間のロック待ちやアプリケーションの停止を引き起こす可能性があります。

したがって：MySQL5.6 より前のバージョンでは、テーブル構造の直接変更はテーブルロックを引き起こします。

具体的な操作手順は以下の通りです：

  1. 最初に新しい一時テーブルを作成し、ALTER TABLE コマンドで新しい構造を定義します。
  2. 次に、元のテーブルからデータを一時テーブルにインポートします。
  3. 元のテーブルを削除します。
  4. 最後に一時テーブルの名前を元のテーブル名に変更します。

MySQL5.6 および MySQL8.0 では、このテーブルロック問題に対する最適化が行われました。

MySQL5.6

MySQL5.6 以降、InnoDB はオンライン DDL（Online DDL）操作を導入し、いくつかのテーブル変更操作をテーブルをロックせずに実行できるようになりました。

カラムを追加する操作はオンライン操作と見なされ、ALGORITHM=INPLACE を使用することでテーブルの全体ロックを回避できます。

ALTER TABLE your_table ADD COLUMN new_column INT ALGORITHM=INPLACE, LOCK=NONE;

ALGORITHM=INPLACE は、インプレース（その場）アルゴリズムを使用して変更を加えることを示しており、オンライン DDL 操作の一部です。

  • MySQL に対し、可能な限り元のテーブルを再作成せずに構造変更を試みるよう明示します。

LOCK=NONE は、可能な限りテーブルをロックせず、並列クエリへの影響を最小限に抑える設定です。

  • 他のセッションがテーブルに対して読み書き操作を続けられます。
  • ただし、一部のケースではデータの不整合が発生する可能性もあります。

このように実行することで、パフォーマンスが大幅に向上し、テーブルロックも回避されます。

ただし、次の 2 つの状況に分けられます：

NOT NULL カラムを追加する場合：

  • 高速なメタデータ操作が実行され、テーブル全体のロックは発生しません。
  • 変更中でも他のセッションは引き続きテーブルデータを読み書きできます。

NULL 許容カラムを追加する場合：

  • 高速なメタデータ操作が実行され、テーブル全体のロックは発生しません。
  • 他のセッションは読み書き可能ですが、変更中に一時的な行ロックが発生する可能性があります。

注意点：

InnoDB ストレージエンジンはロックを最小限に抑えるとはいえ、ALTER TABLE 実行時には内部的なメタデータ操作やデータの再編成、ログの書き込みなどにより、一定のパフォーマンスへの影響があります。

したがって、大規模テーブルに対して構造変更を行う際は、アプリケーションへの影響を最小限に抑えるため、負荷の少ない時間帯に実行することが推奨されます。

MySQL8.0

MySQL8.0 は、新しい機能をいくつか導入しており、ほとんどの ALTER TABLE 操作をテーブルをロックすることなく完了できるようになりました。

簡単に言えば：オンライン DDL 操作の機能が強化されました。

MySQL8.0 では、デフォルトで簡単な ALTER TABLE 操作（例えばカラムの追加など）は、通常テーブルをロックしません。

特定の ALTER TABLE 操作がロックを伴うかどうかを確認するには、操作を実行する前に EXPLAIN ステートメントを使って確認できます：

EXPLAIN ALTER TABLE your_table ADD COLUMN new_column INT;

このコマンドは操作の実行計画情報を表示し、テーブルロックの有無なども含まれます。

以下は MySQL8.0 におけるいくつかの具体的な最適化ポイントです：

原子 DDL（Atomic DDL）

  • MySQL8.0 は 原子 DDL（Atomic DDL） 操作を導入しました。これにより、ALTER TABLE ステートメントの実行中に発生するブロッキングが大幅に減少します。
  • カラム追加のケースでは、Atomic DDL によりテーブルのロック時間が短縮され、他のセッションが引き続きデータの読み書きを行うことが可能になります。

メタデータの即時更新

  • MySQL8.0 では、カラムを追加する際、操作の完了を待つことなく、即時にテーブルのメタデータが更新されます。
  • これにより、ALTER TABLE 操作がより迅速に完了し、ロック時間が短縮されます。

InnoDB エンジンの最適化

  • MySQL8.0 における InnoDB ストレージエンジンは、大規模データテーブルの構造変更に対していくつかの最適化を施しています。
  • 例えば、非 NULL フィールドの追加 において、InnoDB はもはやテーブル全体のデータをコピーする必要がありません。
  • その代わり、より軽量な操作によって新しいカラムが追加されるため、ロック時間とリソース消費が削減されます。

増分メタデータ更新（Incremental Metadata Update）

  • MySQL8.0 では、増分メタデータ更新 が導入され、ALTER TABLE 操作中に影響を受けたメタデータのみを更新します。
  • これにより、操作のオーバーヘッドとロック時間が削減されます。

オンライン DDL（Online DDL）

オンライン DDL（Online DDL） とは、データベースが稼働中の状態で DDL（データ定義言語）操作、すなわちテーブル構造やインデックスの作成・変更・削除などを、長時間のロックやサービス停止を引き起こさずに実行する手法を指します。

従来の DDL 操作では、対象のテーブルに対して排他ロックをかける必要がありました。

そのため、他のセッションがそのテーブルに対して読み書き操作を行うことができず、データベースの利用に支障をきたす可能性がありました。

現在、MySQL でサポートされている代表的な DDL アルゴリズムは以下の 3 種類です：

  • COPY：MySQL5.6 以前の非オンライン操作で使用。テーブルのコピーを伴う従来型の操作。
  • INPLACE：MySQL5.6 で登場。テーブルをコピーせず、元の場所で構造を変更する方式。
  • INSTANT：MySQL8.0.12 で登場（Tencent の DBA チームが貢献）。ほぼ即時にカラムを追加でき、最も効率的。

基本原理

いずれの DDL アルゴリズムも、以下の 3 つのステージを経て実行されます：

  1. 準備ステージ
  2. DDL 実行ステージ
  3. コミットステージ

違いは、それぞれのステージでどのような最適化が施されているかです。

詳細な実装については、以下の公式ドキュメントを参照してください：

まとめ

MySQL5.6 以降、単にカラムを 1 つ追加するような操作では、テーブル構造の変更やインデックスの追加において、通常テーブル全体をロックすることはなくなりました。

ただし、一部の状況では依然としてテーブルの全体ロックが必要になることがあります。

また、データ量が非常に多い場合には、パフォーマンスの問題が発生する可能性があります。

したがって、実際の運用では以下の点に注意が必要です：

  • テーブルのデータ量の多寡
  • 最終的なデータサイズ（インデックスも含めて）

加えて、もし使用している MySQL のバージョンが古い、あるいは特別な理由でオンライン DDL 操作が利用できない場合には：

  • 業務への影響を最小限に抑えるため、ピーク時を避けて ALTER TABLE 操作を実行する必要があります。

私たちはLeapcell、バックエンド・プロジェクトのホスティングの最適解です。

Leapcell

Leapcellは、Webホスティング、非同期タスク、Redis向けの次世代サーバーレスプラットフォームです：

複数言語サポート

  • Node.js、Python、Go、Rustで開発できます。

無制限のプロジェクトデプロイ

  • 使用量に応じて料金を支払い、リクエストがなければ料金は発生しません。

比類のないコスト効率

  • 使用量に応じた支払い、アイドル時間は課金されません。
  • 例: $25で6.94Mリクエスト、平均応答時間60ms。

洗練された開発者体験

  • 直感的なUIで簡単に設定できます。
  • 完全自動化されたCI/CDパイプラインとGitOps統合。
  • 実行可能なインサイトのためのリアルタイムのメトリクスとログ。

簡単なスケーラビリティと高パフォーマンス

  • 高い同時実行性を容易に処理するためのオートスケーリング。
  • ゼロ運用オーバーヘッド — 構築に集中できます。

ドキュメントで詳細を確認！

Try Leapcell

Xでフォローする：@LeapcellHQ

ブログでこの記事を読む

Discussion