データベース設計の落とし穴：AUTO_INCREMENT idの型選びで失敗しないコツ

1. 背景

多くのエンジニアは日々の業務で、テーブルの主キー（IDカラム）をINT型のAUTO_INCREMENTに設定しています。私もその一人でした。しかし、あるECプロジェクトで、購買履歴テーブルのレコード数がINT型の上限（2,147,483,647）に迫る状況に直面し、この慣習の再考を迫られました。
この上限に達すると、新規レコードの追加ができなくなり、サービス停止という重大な事態につながる可能性があります。苦心の末、カラムの型をINT型からBIGINT型に変更し、上限を引き上げる対応を行いました。この変更プロセスについては、別の記事でRDS Blue/Green Deploymentsを利用した実現方法を詳しく解説する予定です。

本記事では、テーブル設計時に考慮すべき重要な点として、自動増分IDにINT型とBIGINT型のどちらを選択すべきかについて詳しく解説します。この選択が将来のシステム拡張性とパフォーマンスに与える影響を理解することで、より堅牢なデータベース設計が可能になります。

2. INT型とBIGINT型の比較

2.1 保存可能なレコード数の上限

データ型	サイズ	符号付き範囲	符号なし範囲
INT	4バイト	-2,147,483,648 ～ 2,147,483,647	0 ～ 4,294,967,295
BIGINT	8バイト	-9,223,372,036,854,775,808 ～ 9,223,372,036,854,775,807	0 ～ 18,446,744,073,709,551,615

2.2 符号付きと符号なしの違い

MySQLでは、INT型のカラムはデフォルトで符号付きです。つまり、INT型のカラムを作成する際に明示的にUNSIGNEDを指定しない限り、理論上は負の値を格納することが可能です。
しかし、AUTO_INCREMENTを使用する場合、符号付きのINT型であっても、通常は負の値を生成しません。デフォルトでは1から開始するため、実質的に処理できるレコード数は2,147,483,647件に制限されます。
一方、明示的にUNSIGNED（符号なし）を指定すると、カラムは正の値のみを取るようになり、処理可能なレコード数の範囲が2倍になり、0から4,294,967,295までの値を扱うことができます。
この違いは、特に大規模なデータベースや長期運用を想定するシステムにおいて重要な考慮事項となります。

CREATE TABLE purchase_history (
    purchase_id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT UNSIGNED NOT NULL,
    product_id INT UNSIGNED NOT NULL,
    quantity INT UNSIGNED NOT NULL,
    purchase_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

• purchase_id:
  • INT型で、AUTO_INCREMENTとPRIMARY KEYが設定されています。
  • デフォルトで符号付きなので、範囲は -2,147,483,648 から 2,147,483,647 までです。
  • しかし、AUTO_INCREMENTのカラムであり、1からスタートするため、結果として2,147,483,647件レコードまでしか処理できません。
• user_id,product_id
  • 明確的にINT UNSIGNEDに指定したため、0から4,294,967,295までの正の整数値を扱えます。

2.3 ストレージ容量への影響

BIGINT型を使用すると、ストレージ容量が増加します。20億件のデータを例に取ると：

• INT型：約80 GB　(20億 * 4バイト)
• BIGINT型：約160 GB (20億 * 8バイト)

差分は80GBとなります。この容量の差が大きいか小さいかは個人や状況によって判断が分かれるところですが、多くのエンジニアはストレージ容量を意識して、とりあえずINT型を選択し、さらにデフォルトの符号付きINTを使用することがおおいのではないでしょうか。
しかし、履歴管理テーブルの場合、データは継続的に増加し続けます。INT型の上限（約21億件）に近い規模になると、急遽カラムの型をINT型からBIGINT型に変更せざるを得なくなります。
この変更は一見、単純なデータ型の変更に思えるかもしれません。しかし、実際にはこの作業は非常に複雑で困難を伴います。

3. なぜINTからBIGINTへの変更は大変なのか？

INTからBIGINTへの変更は、大規模なデータ移行が必要となり、長時間のサービスのダウンタイムが発生する可能性があるからです。

3.1 DDLのALGORITHMについて

MySQLのDDL(Data Definition Language)は、データベースの構造を定義・変更するためのSQLコマンドです。主なDDLコマンドには以下のようなものがあります:

1. CREATE - テーブル、インデックス、ビューなどのオブジェクトを作成
2. ALTER - 既存のオブジェクトの構造を変更
3. DROP - オブジェクトを削除
4. TRUNCATE - テーブルのデータを全て削除

DDLのALGORITHMは、ALTER TABLE操作を実行する際にMySQLが使用するアルゴリズムを指定するオプションです。主なアルゴリズムには以下があります:

1. COPY - テーブルの完全なコピーを作成し、変更を適用します。最も互換性が高いですが、時間がかかります。
2. INPLACE - テーブルをコピーせず、データや構造を直接変更。COPYより高速ですが、一部の操作中にテーブルがロックされる可能性があります。
3. INSTANT - MySQL 8.0.12以降で導入された最も高速なアルゴリズムです。メタデータの更新のみを行い、テーブルデータの変更を必要としません。ただし、使用できる操作が限定的です

例えば、ALTERコマンドでALGORITHMを指定する場合:

ALTER TABLE table_name ADD COLUMN column_name INT, ALGORITHM=INSTANT;

MySQLは可能な限り効率的なアルゴリズムを自動選択します。ALGORITHMを指定しない場合、MySQLはINSTANT → INPLACE → COPY の順で試行します。
これらのアルゴリズムを適切に選択することで、DDL操作のパフォーマンスと可用性を最適化できます。

実際の運用では、ALTER TABLE文を実行する前に、ALGORITHM=INSTANTオプションを指定してテストし、オンラインDDLが可能かどうかを確認することをお勧めします。サポートされていない場合は、エラーメッセージで適切なアルゴリズムが提案されます。

参考記事：

ALGORITHMオプションは主に既存のテーブル構造を変更する操作（特にALTER TABLE）で使用されます。他のDDL操作では、通常、テーブル構造の複雑な変更を行わないため、ALGORITHMオプションは必要ありません。
CREATE INDEX文では、ALGORITHMオプションを使用できる場合があります。これは実質的にALTER TABLE ADD INDEXと同等の操作だからです。

アルゴリズム	速度	変更対象	利用version	用途	特徴
INSTANT	最速	メタデータのみ	8.0.12以降	-カラムの追加 -default値の変更 -ENUMやSETへの値追加	-ほぼ瞬時に完了 -メタデータのみ変更(データは変更しない) -完全にロックフリー -ダウンタイムなし
INPLACE	中速	直接変更	5.6以降	-インデックスの追加/削除 -カラム名の変更 -外部キーの追加/削除 -カラムの順序変更 -テーブル名の変更 -カラムのデータ型変更（一部）	-データをコピーしない -既存のデータや構造を直接変更 -一時的なテーブルロックが必要※ -読み取りと書き込みは通常可能※
COPY	最遅	新テーブル作成	5.5以前から	-カラムの型変更（大部分） -カラムの削除 -主キーの変更 -テーブルの文字セット変更 -パーティショニングの変更 -テーブルの圧縮変更	-新しいテーブルを作成してデータをコピー -処理中はテーブルがロックされ、読み取り/書き込みができない -大規模なテーブルで時間がかかる -リソースを多く消費

INPLACEアルゴリズムについて

1. 一時的なテーブルロック:
   • 通常、INPLACEアルゴリズムでは長時間のテーブルロックは必要ありません
   • ただし、操作の開始時と終了時に短時間の排他的メタデータロックが必要
   • このロックは通常ごく短時間（数ミリ秒程度）で済みます。
2. 読み取り操作:
   • INPLACEアルゴリズムを使用中も、ほとんどの場合読み取り操作は可能です。つまりSELECTクエリは通常ブロックされません。
3. 書き込み操作:
   • 多くの場合、INPLACEアルゴリズムを使用中も書き込み操作（INSERT、UPDATE、DELETE）が可能
   • ただし、操作の種類によっては一時的に書き込みがブロックされる場合があります。
     重要な点:
     LOCK=NONEオプションを指定すると、可能な限りロックを避けようとします。
     操作の種類によっては、完全な並行性を保証できない場合もあります。
     大規模なテーブルや高負荷な環境では、短時間のロックでも影響が出る可能性があります。
     結論として、INPLACEアルゴリズムは通常、読み取りと書き込みの両方を許可しながら操作を行いますが、完全にロックフリーではありません。短時間のメタデータロックは必要で、操作の種類によっては一時的な書き込みの制限が発生する可能性があります。

3.2 オンラインDDLとオフラインDDLの根本的な違い

1. オンラインDDL:
   • サービスを停止せずにスキーマ変更が可能だからオンラインDDLと呼ばれている
   • テーブルの変更中もデータベースの読み書きが可能
   • 通常、テーブルのロックが不要または最小限
   • INSTANTとINPLACE実行できるDDLはオンラインDDLに分類
     • INSTANTP：テーブルのロックはまったくなし
     • INPLACE：最小限に抑える(サービスのダウンタイムを最小限に抑えられる)
2. オフラインDDL
   • 変更操作中はサービスが影響を受けて、一定時間サービス停止になるからオフラインDDLと呼ばれる
   • DDL操作中はテーブルへの読み書きがブロックされ、読み取り/書き込みができない
   • COPYアルゴリズムを利用するDDLオフラインDDLに分類

3.3 MySQLのバージョンとの関係

• MySQL 5.5以前:

  • オンラインDDLの概念がなく、ほとんどのDDL操作がオフライン

• MySQL 5.6:

  • オンラインDDLが導入された
  • 一部のDDL操作がオンラインで実行可能になった

• MySQL 5.7:

  • オンラインDDLの機能が拡張され、より多くの操作がサポートされた

• MySQL 8.0:

  • さらにオンラインDDLの機能が強化された
  • 「インスタントDDL」が導入され、一部の操作がさらに高速に実行可能になった

• MySQL 8.0以降:

  • ALGORITHM=INSTANT, ALGORITHM=INPLACE, ALGORITHM=COPY などのオプションを使用して、DDL操作の実行方法をより細かく制御できるようになった

重要な点として、MySQLのバージョンが新しくなるにつれて、より多くのDDL操作がオンラインで実行可能になり、パフォーマンスと並行性が向上しています。ただし、すべてのDDL操作がオンラインで実行できるわけではなく、操作の種類や対象のテーブル構造によっては、依然としてオフラインDDLが必要な場合があります。

したがって、DDL操作を実行する際は、使用しているMySQLのバージョンと、実行しようとしている具体的な操作の種類を考慮し、適切なアプローチを選択することが重要です。

3.4 カラムの型はINTからBIGINTへの変更がなぜこんなに遅いのか？なぜ変更中は読み書きは一切できないのか？

カラムの型をINTからBIGINTに変更する操作は、通常COPYアルゴリズムを必要とします。これは、オフラインDDLに該当します。この操作は時間がかかり、実行中はテーブルへの読み書きが一切できないため、長時間のサービス停止を引き起こす可能性があります。
オンラインDDLでサポートされていない主な理由は、INTが4バイト、BIGINTが8バイトであるため、各行のデータ構造を変更する必要があるからです。これは単なるメタデータの変更ではなく、実際のデータページの再編成が必要になります。

3.4.1 データページ

1. データページの概念
   InnoDBストレージエンジンでは、データはページと呼ばれる固定サイズの単位で管理されています。
   • 1ページのデフォルトサイズは16KB
   • ページはテーブルデータ、インデックス、システム情報などを格納
   • ページはディスク上で連続して配置され、効率的なI/O操作を可能にする
2. ページの構造
   一般的なデータページの構造は以下のようになっています：

   +------------------+
   | ページヘッダ     |
   +------------------+
   | レコード1        |
   | レコード2        |
   | ...              |
   | レコードN        |
   +------------------+
   | 空き領域         |
   +------------------+
   | ページフッタ     |
   +------------------+
   

3.4.2 ページの再編成プロセス

1. 新しいページの作成:
   • bigint型に対応した新しいページ構造を作成
2. データの移行:
   • 古いページから新しいページにデータをコピー
   • この際、各レコードの4Bのint型フィールドを8Bのbigint型に変換
3. ページ分割:
   • 1ページに収まらないデータは新しいページに分割される
4. インデックスの再構築:
   • カラムのサイズが変わるため、そのカラムに関連するすべてのインデックスも再構築が必要になります

変更前（int型）:

+------------------+
| ページヘッダ     |
+------------------+
| レコード1 (4B)  |
| レコード2 (4B)  |
| レコード3 (4B)  |
| ...            |
| レコード100 (4B)|
+------------------+
| 空き領域        |
+------------------+
| ページフッタ     |
+------------------+

変更後（bigint型）:

+------------------+  +------------------+
| ページヘッダ     |   | ページヘッダ     |
+------------------+  +------------------+
| レコード1 (8B)   |  | レコード51 (8B)  |
| レコード2 (8B)   |  | レコード52 (8B)  |
| ...             |  | ...            |
| レコード50 (8B)  |  | レコード100 (8B) |
+------------------+  +------------------+
| 空き領域         |   | 空き領域         |
+------------------+  +------------------+
| ページフッタ      |   | ページフッタ     |
+------------------+  +------------------+

図中の「レコード1 (4B)」などは、簡略化のための表現です。実際には、レコードのサイズはフィールドの組み合わせによって決まります。
InnoDBは、小さな整数値（例：1や100）に対して、必ずしも4バイト全てを使用するわけではありません。
可能な場合、InnoDBは圧縮技術を使用して、小さな値をより少ないバイト数で格納します。

3.4.3 再編成の影響

• テーブルロック発生: 変更中もデータの一貫性を保証する必要があり、テーブルへの読み書きができない
• 時間がかかる：大規模なテーブルの場合、データのコピーやインデックスの再作成は非常に時間がかかる
• I/O負荷の増加: 大量のデータ読み書きが発生することとともに、大量のトランザクションログが生成されるため、リソースが大量に消費される
• ディスク使用量の増加: bigint型はint型の2倍のスペースを使用

MySQLのバージョンによってオンラインDDLの対応状況が異なる。
MySQL 8.0以降では、INDEXがない場合、INTからBIGINTへの変更をオンラインDDL（INPLACEアルゴリズム）で実行できるようになりました。

具体的には以下のような条件が満たされれば、オンラインDDLが可能です

1. MySQL 8.0以降のバージョンを使用している
2. 変更対象のカラムにインデックスが設定されていない
3. 変更対象のカラムが外部キーの一部ではない
4. テーブルにトリガーが設定されていない

この場合、以下のようなALTER TABLE文を使用できます。

ALTER TABLE your_table 
MODIFY COLUMN your_column BIGINT, 
ALGORITHM=INPLACE, LOCK=NONE;

MySQLのバージョンアップによってこのようなオンラインDDLの改善により、以前はCOPYアルゴリズムが必要だった操作の一部が、INPLACEアルゴリズムで実行できるようになりました。これにより、大規模なテーブルでもダウンタイムを最小限に抑えてデータ型の変更が可能になりました。

ただし、インデックスや外部キーが設定されている場合は、依然としてCOPYアルゴリズムが必要になる可能性が高いです。そのような場合は、一時的にインデックスや外部キーを削除してから変更を行い、その後再作成するなどの方法を検討する必要があります。

参照記事：

4. 結論

• AUTO_INCREMENTのカラムは明示的にUNSIGNEDに指定しましょう。
  • MySQLのAUTO_INCREMENTはデフォルトで符号付きであるため、テーブル作成時に明示的にUNSIGNEDを指定することを推奨します。
• データ量が少なく、将来的にも大幅な増加が見込まれないテーブルの主キーの場合は、INT型で十分です。
• 履歴管理テーブルなど、長期的な成長が予想されるテーブルでは、初期設計の段階でBIGINT型の使用を検討すべきです。これにより、将来的な問題を回避し、システムの長期的な安定性と拡張性を確保することができます。

ストレージ容量の考慮：
20億件のデータでも、INT型とBIGINT型の差は約80GBです。クラウドデータベースを使用している場合、この差は月々のコストにわずかな影響しか与えないでしょう。したがって、ストレージコストよりもシステムの安定性と将来の拡張性を優先して判断すべきです。