追加・削除の多いテーブルにおけるMySQLの自動採番について

Question

SQLiteからMySQLに差し替える作業を行っており、主キー(Integer)自動採番の動作の違いで困っています。
以下の検討はしましたが、他に良いアイデアは無いでしょうか？

テーブルの特性は以下のとおりです。

• Webアプリのユーザーが日々データ(行)を追加する
• 古くなると自動で削除する
• したがって、総行数はそれほど多くはならないが、追加、削除の頻度は高い

SQLiteの自動採番

行を削除するなどして欠番になっているIDは、Insert時に再利用されます。
したがって、行数さえIntegerの値域に収まっていれば問題ありません。

On an INSERT, if the ROWID or INTEGER PRIMARY KEY column is not explicitly given a value, then it will be filled automatically with an unused integer, usually one more than the largest ROWID currently in use. This is true regardless of whether or not the AUTOINCREMENT keyword is used.

https://www.sqlite.org/autoinc.html

MySQLの自動採番(AUTO_INCREMENT)

行を削除するなどして欠番になっているIDは、再利用されず欠番となります。
追加・削除の多いテーブルでは使い切ってしまう恐れがあります。

検討した対策

• AUTO_INCREMENTの値をリセットする → テーブルを一度空にする以外に方法が見つかりませんでした。
• 主キーをBIGINTにすることで、上限に達するまでの期間を延ばす

環境

• MySQL 8.0.19

Answer 1

使い切ってしまう恐れ

理論上unsignedなbigintは1844京個のデータを保持できるので
1秒間に1000回ずつインクリメントしても5億年くらい持ちそうな気がします

Comments

[hkcomori]

おっしゃるとおり、BIGINTなら実質問題ないと思います。
最初からBIGINTにしておけばよかったのですが、SQLiteと同様に使いまわしされるものと思い込んでINTにしてしまったため、質問するに至りました。
こういう場合には、型を変えるのがベストな選択なのでしょうか？

Answer 2

MySQL固有のAUTO_INCREMENTを標準SQLに変更すれば、リセットやリサイクルが可能です。
MySQL 固有の SERIAL データ型を標準SQL で書き換える（内部シーケンス生成子:IDENTITY）

ただ、

古くなると自動で削除する

という事であれば、他のトランザクションとの整合性を担保しなくて良い、ログ的なものでしょうから、AUTO_INCREMENTによるキーは設定せず、timestampなどの時系列を判断できる項目を用いて削除すれば良いだけではないでしょうか。

尚、AUTO_INCREMENTにはバグがあります。
MySQL 8.0でInnoDBのAUTO_INCREMENT問題解消を確認してみる

Comments

[hkcomori]

> MySQL固有のAUTO_INCREMENTを標準SQLに変更すれば、リセットやリサイクルが可能です。
ありがとうございます。機能としては存在するのですね。
ただ、試してみたところ、Error 1064が出てうまくいきませんでした。
以下のエラーと同じようですが、この例ではAUTO_INCREMENTにして解決しているので、参考になりませんでした。
https://stackoverflow.com/questions/43965114/sql-syntax-error-generated-always-as-identity

[hkcomori]

AUTO_INCREMENTのバグのご連絡、ありがとうございます。
後ほど確認はして見ますが、MySQL8.0なのでおそらく問題ないと思います。
(環境書き忘れていたので追記しました)

[sazi]

AUTO_INCREMENTの設定は以下の様にすれば行えるとは思います。
ALTER TABLE xxxx AUTO_INCREMENT=100;
※但しprimarykeyである必要がありますが。

ただ、integerのままだと直ぐに被りそうだし、削除する位ならAUTO_INCREMENTの項目に固執しなくて良いのではないですか。

[hkcomori]

そうですね、主キーをタイムスタンプ等にするか、BIGINTにするか、どちらかで対応しようと思います。
> ALTER TABLE xxxx AUTO_INCREMENT=100;
これも主キーの最大値+1以上しか設定できないので、継続的に増え続けるテーブルではAUTO_INCREMENTを下げる効果はあまり期待できなさそうです。
いろいろな方法をご提示いただき、ありがとうございました。