AUTO_INCREMENTのIDカラムがオーバーフローしたらどうなるの？実例から学ぶDB設計の注意点

AUTO_INCREMENTのIDカラムがオーバーフローしたらどうなるの？   〜実例から学ぶ DB設計の注意点〜   NE株式会社  やまもとひろや  PHPerKaigi2025 登壇資料 

自己紹介  • やまもとひろや  • NE株式会社開発部マネージャー  • X: @HiroyaYamamoto1  •
Qiita: @yamamoto_hiroya  • Zenn: yamamoto_hiroya  • 今回NE株式会社は4名登壇！  • 新卒採用もやってるので是非！  ◦ https://ne-inc.jp/recruit  ◦ 「NE株採用」で検索！ 

突然ですが   2147483647  これ何の数字か分かりますか？  

そうですね！   INT型の最大値ですね！   (2^31-1) 

4294967295  は分かりますか？  

そうですね！   符号なし(UNSIGNED)  INT型の最大値ですね！  

今回は実際に   UNSIGNED INTのIDカラムが  上限を超えた   弊社事例を紹介します！  

目次  • INT型（UNSIGNED INT）とは  • 実際に起きた弊社事例  • 対応方法と学び  • まとめ 

• 今回はMySQLのINT型に絞ってお話しします。  参照: https://dev.mysql.com/doc/refman/8.0/en/integer-types.html  INT型（UNSIGNED INT）とは 

INT型（UNSIGNED INT）とは  普通のINT  (符号あり)  UNSIGNED INT  (符号なし)  • 数値を入れるためのデータ型です。  •
赤い部分がそれぞれの範囲です。  • 通常のINTがマイナス値入るの忘れがち。  • UNSIGNEDはマイナス値入らない=符号なしとも呼ぶ  • 自分用: INTは約21億、符号なしは約43億  0  -2147483647  4294967295  2147483647 

実際に弊社で起きた事例   調査担当  私  以下エラーが出ているんですがどういう状況か分かります？  Duplicate entry '4294967295' for
key 'sample_table.PRIMARY' 

ふむふむ  どうやらsample_tableのPRIMARYの  カラム(ID)が4294967295を書き込もうとしてエラーになっているみたいだね。  Duplicateなので既にそのID値は存在しているみたい。  実際に弊社で起きた事例   調査担当  私 

実際に弊社で起きた事例   調査担当  私  じゃあIDが4294967295のレコードが  悪さをしてそうだから  調査してレコード削除等をすれば  いいですかね？ 

ん〜というか  4294967295はUNSIGNED INTの最大値だから  これもしかしてレコード上限  来ちゃってるかもね  実際に弊社で起きた事例   調査担当  私 

実際に弊社で起きた事例   調査担当  私  え？  UNSIGNED INTがオーバーフローすると Duplicateエラーになるんですか？ 

実際に弊社で起きた事例   • ある日突然以下のエラーが発生しました。  • Duplicate entry '4294967295' for key
'sample_table.PRIMARY'  • データの書き込みが全て止まった。 

再現テスト   • 状況をローカル環境にて再現しました。  • テーブル定義は以下のものを使います。  • データは以下のようにします。 

• この状態で新規レコードを追加しようとするとDuplicateエラーが再現しました。  再現テスト  

• 以下のような挙動をします。  • ID値が4294967295になった。  • 次の処理でAUTO_INCREMENTのためIDを+1しようとしたが最大値のため4294967295のままになった。  • IDのカラムはPRIMARYのため重複できない。  •
そのため4294967295を再びINSERTしようとしてDuplicateエラーとなった。  • 補足: 現在のauto_increment値の  確認方法  • SHOW TABLE STATUS LIKE   'sample_table';  解説 

調査担当  私  レコードの削除をしてauto_incrementの値をリセットする？  どう対処したか   約43億のレコード削除はだいぶ時間がかかるので今回は難しそう。 

調査担当  私  INT型を変えて桁数増やす？  どう対処したか   ALTERにだいぶ時間がかかるので今回は難しそう。 

調査担当  私  今のsample_tableをsample_table_oldとし、新しくsample_tableを作成する？  どう対処したか   RENAMEも新しいsample_table作成もサクッとできる。作ったらすぐそのテーブルにレコードが書き込まれ始める。  よし、これで行こう！ 

解決！ 

補足  • ちなみに今回の例はサービス開始し約5年で発生しました。  ◦ 1日235万レコードペース  • そもそもそんな大量のデータをデータベースに持ちたいか？という観点から注意が必要です。  • どうしても持つ場合はBIGINTで持つと2^63乗まで持てます(約900京) 
◦ 例えばこれを10年で使い切るには1日250兆のレコードが入り続ける必要があり、ほぼ使い切らないくらい大きい上限値だと思ってもらって良いと思います。   ◦ もしBIGINTを使い切ったよっていうご経験がある方がいたら是非教えてください。  

学び  • AUTO_INCREMENTで  • PRIMARYの  • INT型の  • ID値が  •
上限に行くと  • Duplicateエラーとなる。 

まとめ  • 安易なID値設計は辞めましょう。  ◦ デフォルトのINT型AUTO_INCREMENT, PRIMARYで本当に大丈夫か？  • そもそもそのIDカラムは必要か？  ◦ サロゲートキーか？ナチュラルキーか？
  • 全て考えてIDカラムを持つとして、ものすごいレコードが入る場合は BIGINTにしましょう。  • そのテーブルは  • 「どういう使われ方をするのか」  • 「どういうデータが入るのか」  • 「どれくらいの頻度で入るのか」  • これらを考えて適切なテーブル設計をしていきましょう！ 

AUTO_INCREMENTのIDカラムがオーバーフローしたらどうなるの？実例から学ぶDB設計...

AUTO_INCREMENTのIDカラムがオーバーフローしたらどうなるの？実例から学ぶDB設計の注意点

yamamoto-hiroya

More Decks by yamamoto-hiroya

Other Decks in Technology

Featured

Transcript