MySQLテーブルロッキング：カラムやインデックスを追加するとどうなるか

MySQLテーブルの構造変更は、テーブルレベルのロックの問題を伴うことがあります。

これは特にデータ量の多いテーブルでは重要であり、ビジネスシステムのパフォーマンスに大きな影響を与える可能性があります。

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

テーブルレベルロックの概要

テーブルレベルロックとは、データの一貫性を確保するために、特定の操作中にテーブル全体をロックすることを指します。

具体的には：

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

このテーブルロックの動作は、小規模なテーブルや同時実行性の低いシステムにはほとんど影響を与えません。

ただし、大規模なデータセットや同時実行性の高いビジネスシステムを扱う場合、テーブルロックは深刻なパフォーマンスボトルネックを引き起こし、システムクラッシュにつながる可能性さえあります。

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

MySQLがカラムの追加などの操作を実行する際、テーブルロックは以下の問題を引き起こす可能性があります。

クエリの遅延: そのテーブルに対するすべてのSQLクエリは、テーブルロックが解除されるまでブロックされます。
書き込み操作のブロック: テーブルに対するすべての書き込み操作（INSERT、UPDATE、DELETEなど）は、ロックが解除されるまで保留されます。
システムのスローダウン: テーブルロック操作の完了に時間がかかりすぎると、ビジネスシステム全体のパフォーマンスが大幅に低下し、システム停止を引き起こす可能性があります。

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

InnoDBストレージエンジンには、テーブルロックを軽減し、同時実行性能を向上させるためのいくつかのメカニズムが用意されています。

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

MySQL 5.6より前

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

つまり、操作中、テーブルはロックされ、操作が完了するまで他の読み書き操作は実行できなくなります。

この全テーブルロックの動作は、大規模なテーブルでALTER TABLE操作を実行する際に、長い待ち時間とアプリケーションの中断を引き起こす可能性があります。

つまり：MySQL 5.6より前は、テーブル構造を直接変更するとテーブルがロックされていました。

具体的な手順は以下のとおりです。

まず、ALTER TABLEコマンドを使用して、変更された構造を持つ新しい一時テーブルを作成します。
次に、元のテーブルから一時テーブルにデータをインポートします。
元のテーブルを削除します。
最後に、一時テーブルの名前を元のテーブルの名前に変更します。

MySQLバージョン5.6および8.0では、テーブルロックの問題に対処するための最適化が導入されました。

MySQL 5.6

MySQL 5.6以降、InnoDBはオンラインDDL操作を導入し、特定のテーブルの変更をテーブルをロックせずに実行できるようになりました。

カラムの追加はオンライン操作であり、ALGORITHM=INPLACEを使用してテーブル全体のロックを回避できます。

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

ALGORITHM=INPLACEは、オンラインDDLメカニズムの一部である、その場でのアルゴリズムを修正に使用する必要があることを示します。
- MySQLに、テーブル構造をその場で変更するように明示的に指示します。
- MySQLは、可能な限りテーブル全体を再作成せずに変更を適用しようとします。
LOCK=NONEは、テーブルをロックしないようにし、同時クエリへの影響を最小限に抑える必要があることを意味します。
- 他のセッションは、テーブルの読み取りと書き込みを許可されています。
- ただし、データの一貫性のリスクが残る可能性があります。

これにより、実行効率が大幅に向上し、テーブルロックが回避されます。

ただし、これは2つのケースに分かれます。

NULLを許容しないカラムの追加：

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

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

テーブル全体をロックせずに、高速なメタデータのみの操作として実行されます。
他のセッションは読み書き操作を続行できますが、変更中に短い行レベルロックが発生する場合があります。

注意：

InnoDBストレージエンジンはロックを軽減しますが、ALTER TABLEを実行すると、依然としてパフォーマンスに影響を与える可能性があります。

これらは、内部メタデータ操作、データ再編成、またはログ書き込みによるものです。

したがって、大規模なテーブルの構造を変更する場合は、アプリケーションへの影響を最小限に抑えるために、ピーク時以外の時間帯に操作を実行することをお勧めします。

MySQL 8.0

MySQL 8.0では、ほとんどのALTER TABLE操作をテーブルをロックせずに実行できる新機能が導入されました。

簡単に言うと：オンラインDDL操作の機能を強化します。

デフォルトでは、MySQL 8.0では、単純なALTER TABLE操作（カラムの追加など）は通常、テーブルをロックしません。

特定のALTER TABLE操作でテーブルがロックされるかどうかを確認するには、実行する前にEXPLAINステートメントを使用できます。

EXPLAIN ALTER TABLE your_table ADD COLUMN new_column INT;

このコマンドは、テーブルがロックされるかどうかなど、実行計画情報を表示します。

MySQL 8.0の具体的な最適化を次に示します。

アトミックDDL

MySQL 8.0では、アトミックDDL操作が導入されました。つまり、ALTER TABLEステートメントの実行は、以前よりもブロッキングが少なくなります。
カラムを追加する場合、アトミックDDLメカニズムはテーブルロック時間を短縮し、他のセッションがデータの読み取りと書き込みを続行できるようにします。

インスタントメタデータアップデート

MySQL 8.0では、カラムが追加されると、操作全体の完了を待たずに、テーブルのメタデータがすぐに更新されます。
これにより、ALTER TABLE操作の完了が高速化され、ロック時間が短縮されます。

InnoDBエンジン最適化

MySQL 8.0のInnoDBエンジンは、大規模なデータテーブルの構造変更に対して最適化されています。
たとえば、NULLを許容しないカラムを追加する場合、InnoDBはテーブルデータをすべてコピーする必要がなくなりました。
代わりに、軽量な操作を使用して新しいフィールドを追加し、ロック時間とリソースの使用量を削減します。

増分メタデータアップデート

MySQL 8.0では、増分メタデータアップデートが導入されました。これは、テーブル全体ではなく、ALTER TABLE操作中に影響を受けるメタデータのみを更新する必要があることを意味します。
これにより、ロック時間とオーバーヘッドが削減されます。

オンラインDDL

オンラインDDLとは、データベースの実行中にデータ定義言語（DDL）操作を実行することを指します。

たとえば、データベースの長期的なロックや利用不能を引き起こすことなく、テーブル構造、インデックスなどを作成、変更、または削除します。

従来のDDL操作では、通常、影響を受けるテーブルに対する排他的ロックが必要です。

これにより、他のセッションがテーブルに対する読み取りまたは書き込み操作を実行できなくなり、通常のデータベースの使用に影響を与える可能性があります。

現在、3つの主要なアルゴリズムがサポートされています。

COPY: MySQL 5.6より前のバージョンで使用される、非オンラインアルゴリズムです。
INPLACE: MySQL 5.6で導入されました。
INSTANT: MySQL 8.0.12で導入されました（Tencent DBAチームによって提供されました）。

基本原則

DDLの実行中、使用するアルゴリズムに関係なく、通常は3つのフェーズを経ます。

準備フェーズ
実行フェーズ（DDL）
コミットフェーズ

違いは、アルゴリズムに応じてこれらの各フェーズで適用される最適化にあります。

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

まとめ

MySQL 5.6以降では、カラムの追加、テーブル構造の変更、またはインデックスの追加は、通常、テーブル全体をロックする必要はありません。

ただし、場合によっては、MySQLがテーブル全体をロックする必要がある場合があります。

また、大量のデータを扱う場合、パフォーマンスの問題が発生する可能性があります。

したがって、実際の操作では、テーブル内のデータの量と最終的なサイズ（特にインデックスデータ）に注意してください。

同時に、MySQLのバージョンが比較的古い場合、または特定の理由によりオンラインDDL操作がサポートされていない場合は、ビジネスシステムへの影響を最小限に抑えるために、ピーク時以外の時間帯にALTER TABLE操作を実行してください。

Leapcellは、バックエンドプロジェクトをホストするための最適な選択肢です。

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

多言語サポート

Node.js、Python、Go、または Rust で開発します。

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

使用量に対してのみ支払います — リクエストも料金もかかりません。

比類のないコスト効率

アイドル料金なしの従量課金制。
例：$25 で 694 万リクエストを平均 60 ミリ秒の応答時間でサポートします。

合理化された開発者エクスペリエンス

簡単なセットアップのための直感的な UI。
完全に自動化された CI/CD パイプラインと GitOps 統合。
実用的な洞察のためのリアルタイムのメトリクスとロギング。

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

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

詳細については、ドキュメントをご覧ください。

X でフォローしてください: @LeapcellHQ