MySQLのInnoDBでは最適化でオートインクリメントが戻るのでしょうか？

Question

表題の件で、InnoDBでは、再起動でオートインクリメントが戻る(カウンターがリセットされる)ことは学んでいたのですが、
いくつかブログを読んでいると、最適化でも再起動と同様な挙動をする、とありました。
ただ、私の手元で確認した限りは、戻っておりませんでした。(5.5と5.7での確認になります)

こちら、最適化でもそのようなことが起こるのでしょうか？
もしくは、あるバージョンから改善されたのでしょうか？

申し訳ありませんが、宜しくお願いします。

Answer 1

実際のところデータ削除がされなければauto_incrementの数値がずれることはあまりありません。
削除系の処理を削除フラグカラムなどで管理すればさほど気にすることではないような気がします。

また実際に削除されような仕組みで運用しても、auto_incrementの基本は主キーとして
レコードをconstに特定するものでしかなく、順序を担保してソートするための仕組みではないため
原則穴埋めもないので、抜け番の可能性があることだけ念頭においておけばよいでしょう

Comments

[naoki855]

ご回答いただき、ありがとうございます！
私も周囲の方から、そもそも物理削除する運用なの？と指摘を受けております。。。
今後、運用面からもテーブル構造を考えられるようになろうと思います。

[maisumakun]

たとえ削除をしなくても、タイミングよくINSERTが来てロールバックが挟まったりすると、AUTO_INCREMENTが進んだまま戻らない、という状態には容易になってしまいます。

[naoki855]

> maisumakun さん
ありがとうございます！
抜け番ができてしまうことはある程度留意する必要があるということですね。

Answer 2

MySQL のマニュアルからすると、

• InnoDB で最適化（OPTIMIZE TABLE）を行うと、別テーブルにデータをコピーして作り直す
• InnoDB の auto_increment のカラムは、カウンタをメモリにのみ持ち、ストレージ上には持たない
• カウンタの値は、最初に INSERT をするときに自動的に MAX() を取得する形で更新される

ということになるので、OPTIMIZE TABLE 直後で INSERT する前だとカウンタがリセットされているのではないでしょうか。

Comments

[tacsheaven]

リリースノートの 5.5.9 で、
InnoDB: The OPTIMIZE TABLE statement reset the auto-increment counter for an InnoDB table. Now the auto-increment value is preserved across this operation. (Bug #18274)
とあるので、これ以後は修正されているのかと思われます。

[naoki855]

ご回答いただき、ありがとうございます！
私もドキュメントの方を確認しました。
確認不足で申し訳ありません。