【MySQL】INSERT IGNOREでAUTO_INCREMENTの値が飛ぶ問題。連番にする方法

テーブルのidはAUTO_INCREMENTにすることが多いと思う。AUTO_INCREMENTのカラムはレコードを挿入する度に文字通りインクリメントされるので、基本的には連番になる。

しかし、以下のような操作を行うと連番ではなく飛び飛びになってしまう。

SQL

これは、INSERTの成否に関わらずAUTO_INCREMENTのカラムの採番が行われているから(INSERTされない場合は採番した値は使わない)。

INSERT IGNOREを行うときに発生しやすいが、普通のINSERT文が重複により失敗したときにも発生する。

AUTO_INCREMENTカラムが連番でなくなると何が困るのか?

idの消費ペースが上がる

最も大きな問題はINSERT IGNOREを頻繁に実行するようなシステムだとidの消費がものすごく早くなってしまうこと。

場合にもよるが、符号なしINTだと約42億までしか採番できないので非常に枯渇しやすい。

符号なしBIGINTだと約1800京なので十分に感じる。しかし、年単位でサービスを運営する前提ならこれでも少し不安が残る。

MEMO

もちろんidの消費ペースによるので、INSERT IGNOREを使いつつもINTで十分なケースもあるはず。

連番でないと気持ち悪い

「連番でないと気持ち悪い」という考えもあるかもしれない。

とはいえ、レコード自体は独立しているものであり各レコードを一意に特定できさえすればidが連番になっていても飛んでいても影響がない。

要は気持ちの問題なので、こちらは無視して良いと思う。

AUTO_INCREMENTの値を連番にする方法

my.confの設定を変える

「INSERTの成否に関わらず採番する」設定は”innodb_autoinc_lock_mode”で行う。

デフォルトだと”innodb_autoinc_lock_mode = 1″だが、この値を0にするとINSERT IGNOREやINSERT失敗時には採番されなくなるのでAUTO_INCREMENTの連番が保証される。

\mysql\bin\my.conf

ただし、もともとパフォーマンスを上げるために”innodb_autoinc_lock_mode = 1″になっているので、この値を変えてしまうとDBのパフォーマンスが低下する可能性がある点には要注意。

14.6.5.2 構成可能な InnoDB の自動インクリメントロック

https://dev.mysql.com/doc/refman/5.6/ja/innodb-auto-increment-configurable.html

※日本語、少し古いので注意


15.6.1.6 AUTO_INCREMENT Handling in InnoDB

https://dev.mysql.com/doc/refman/8.0/en/innodb-auto-increment-handling.html

また、設定ファイルを弄る = プログラムのソースコードを見ただけでは設定が変わっていることに気が付かない(気が付けない)、という点にも注意。

INSERTの前に存在確認をする

INSERTを実行する前に、当該レコードが存在するかどうかを調べる方法もある。

レコードの存在確認

当該レコードがないとき(= COUNTの値が0のとき)だけINSERTすれば採番が行われないので無駄なidの消費を抑えることができる。

この方法だとプログラムのソースコード中に”SELECT COUNT(*) …”と記載されるので「存在確認をしている」というのが後から見て分かりやすい。

しかし頻繁に存在確認を実行するようだとそのせいでパフォーマンスが落ちる可能性は考えられるので注意しないといけない。

コメントを残す

メールアドレスが公開されることはありません。 * が付いている欄は必須項目です