Posted inMySQL

2億レコードのテーブルに0.1秒でカラムを追加：MySQL 8.0におけるALGORITHM=INSTANTの威力

MySQL tutorial - IT technology blog
MySQL tutorial - IT technology blog

ALTER TABLEという悪夢と100GBのデータテーブル

約2億件のレコードを保持するordersテーブルに対してALTER TABLEコマンドを実行し、冷や汗をかいた経験がある方なら、その恐怖がわかるはずです。単純なカラム追加一つでシステムが「フリーズ」してしまうことがあります。データベースが詰まり、アプリケーションはタイムアウトエラーを返し、ユーザーからのプレッシャーが運用チームに重くのしかかります。

以前、MySQL 5.7を使用していた際、80GBのテーブルにstatusカラムを1つ追加するだけで2時間以上待たされたことがありました。その間、テーブルは完全にロック（table lock）され、注文関連の全機能が停止状態に。しかし、MySQL 8.0からはALGORITHM=INSTANTの登場により、この悪夢は一瞬で終わる軽い操作へと変わりました。

テーブル構造変更の3世代：COPY、INPLACE、そしてINSTANT

この進化を理解するために、InnoDBが各時代でどのようにデータを処理してきたかを見てみましょう：

  • ALGORITHM=COPY: MySQLが新しいテーブルを作成し、データを1行ずつコピーした後に古いテーブルを削除します。1億行規模のテーブルでは、I/O負荷が極めて高く、書き込みロック（Write Lock）がかかります。
  • ALGORITHM=INPLACE: ファイル上のデータを直接修正しますが、依然として多くのシナリオでテーブルの再構築（rebuild）が必要です。ロック時間は短縮されますが、サーバーリソースを大幅に消費します。
  • ALGORITHM=INSTANT: MySQL 8.0.12から導入された革命的な手法です。ディスク上の実際のデータには触れず、データ辞書（Data Dictionary）内のメタデータのみを更新します。テーブルのサイズが1GBであろうと1TBであろうと、操作は数ミリ秒で完了します。

なぜINSTANTは驚くほど速いのか？

その秘密は「遅延更新（lazy update）」メカニズムにあります。数億件の既存行に即座にデフォルト値を書き込む代わりに、MySQLは賢いトリックを使います。メタデータに「レコード番号N以降、このテーブルにはデフォルト値Yを持つカラムXが追加された」とメモするだけなのです。

ディスク上の古いデータはそのままの状態を維持します。MySQLが実際に新しいカラムのデータを書き込むのは、その行に対してUPDATEを実行したときか、新しいレコードを追加したときだけです。テーブル全体をスキャンするステップをスキップするため、ALTER操作はほぼ瞬時に完了します。

INSTANTの威力を発揮するための条件

非常に強力な機能ですが、MySQLが自動的に古いアルゴリズムにフォールバックしないよう、ルールを把握しておく必要があります：

  1. バージョン：最低でも8.0.12以上が必要です。特に、DROP COLUMNやテーブル内の任意の位置へのカラム追加をサポートしている8.0.29へのアップグレードを推奨します。
  2. 行フォーマット：テーブルがDYNAMIC（MySQL 8.0のデフォルト）、REDUNDANT、またはCOMPACT形式である必要があります。
  3. 操作内容：（デフォルト値を持つ）カラムの追加、カラム名の変更、またはメタデータの修正などがINSTANTの最適な候補です。

実践：本番環境での安全な操作

単にALTERを実行して祈るのではなく、アルゴリズムを明示的に指定しましょう。条件を満たさない場合、MySQLは勝手にCOPYを使用してシステムをハングさせる代わりに、即座にエラーを返してくれます：

-- バージョンのクイック確認
SELECT VERSION();

-- 巨大なusersテーブルにloyalty_pointsカラムを追加
ALTER TABLE users 
ADD COLUMN loyalty_points INT DEFAULT 0, 
ALGORITHM=INSTANT;

通常、結果は Query OK, 0 rows affected (0.01 sec) と表示されます。数億レコードのテーブルでの「0.01秒」という数字こそが、INSTANTがもたらす価値です。

テーブルがINSTANTを使用したか確認する方法

データベースの「内部構造」を覗くことで、テーブルにいくつメタデータのみで作成されたカラムがあるかを確認できます：

SELECT NAME, INSTANT_COLS 
FROM information_schema.INNODB_TABLES 
WHERE NAME LIKE '%users%';

INSTANT_COLS が0より大きい場合、そのテーブルはメタデータによって最適化されていることを意味します。

大規模データテーブルにおける実戦経験

テラバイト級の本番DBを運用してきた経験から、4つの重要な注意点を挙げます：

  • 列の途中への追加を控える： 8.0.29以降でサポートされていますが、末尾への追加が依然として全バージョンにおいて最も安全で安定した方法です。
  • クエリの遅延： INSTANT使用後、MySQLがオンザフライでデフォルト値を計算する必要があるため、SELECT * クエリが数ミリ秒遅くなる可能性があります。しかし、その差は非常に小さいため心配はいりません。
  • メタデータの整理： 数十回の ALTER INSTANT を繰り返すと、メタデータが肥大化します。アクセスの少ない時間帯（例：深夜2時など）に OPTIMIZE TABLE をスケジュールし、テーブルをクリーンな状態に戻しましょう。
  • バックアップは生命線： どんなに技術が進化しても、バックアップに勝るものはありません。大規模なテーブル構造を変更する前には、必ずデータのスナップショットを取得してください。

まとめ

ALGORITHM=INSTANT は、まだMySQL 5.7を使っている場合に8.0へのアップグレードを検討すべき最大の理由の一つです。これにより、スキーマ変更のたびにダウンタイムを心配することなく、開発チームはより柔軟に機能開発を進めることができます。

この記事が、巨大なデータテーブルを扱う際の自信に繋がれば幸いです。MySQLの最適化について質問があれば、ぜひ下のコメント欄で教えてください！

Share:
Tags: