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

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

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

SQL


# valueカラムはUNIQUE
$ MariaDB [test]> SHOW COLUMNS FROM auto_increment_test;
+-------+------------------+------+-----+---------+----------------+
| Field | Type             | Null | Key | Default | Extra          |
+-------+------------------+------+-----+---------+----------------+
| id    | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| value | int(10) unsigned | NO   | UNI | NULL    |                |
+-------+------------------+------+-----+---------+----------------+
2 rows in set (0.015 sec)


# 3行挿入
$ MariaDB [test]> INSERT INTO auto_increment_test(value) VALUES (100), (200), (300);
Query OK, 3 rows affected (0.003 sec)
Records: 3  Duplicates: 0  Warnings: 0


# 挿入した結果
$ MariaDB [test]> SELECT * FROM auto_increment_test;
+----+-------+
| id | value |
+----+-------+
|  1 |   100 |
|  2 |   200 |
|  3 |   300 |
+----+-------+
3 rows in set (0.000 sec)


# INSERT IGNORE(重複するレコードがある場合は挿入しない)
#     valueカラムが100・200・300のレコードは存在するのでINSERTされない
$ MariaDB [test]> INSERT IGNORE INTO auto_increment_test(value) VALUES (100), (200), (300);
Query OK, 0 rows affected, 3 warnings (0.004 sec)
Records: 3  Duplicates: 3  Warnings: 3


# valueカラムが重複しないように3行挿入
$ MariaDB [test]> INSERT INTO auto_increment_test(value) VALUES (400), (500), (600);
Query OK, 3 rows affected (0.003 sec)
Records: 3  Duplicates: 0  Warnings: 0


# id(AUTO_INCREMENT)が飛んで連番ではなくなっている
$ MariaDB [test]> SELECT * FROM auto_increment_test;
+----+-------+
| id | value |
+----+-------+
|  1 |   100 |
|  2 |   200 |
|  3 |   300 |
|  7 |   400 |
|  8 |   500 |
|  9 |   600 |
+----+-------+
6 rows in set (0.000 sec)

これは、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


[mysqld]
innodb_autoinc_lock_mode=0


# ログイン
$ mysql -u test -p
Enter password:

# innodb_autoinc_lock_modeの値を確認する
$ SELECT @@innodb_autoinc_lock_mode;
+----------------------------+
| @@innodb_autoinc_lock_mode |
+----------------------------+
|                          0 |
+----------------------------+

ただし、もともとパフォーマンスを上げるために”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を実行する前に、当該レコードが存在するかどうかを調べる方法もある。

レコードの存在確認


# 当該レコードが存在する場合
$ MariaDB [test]> SELECT COUNT(*) FROM auto_increment_test WHERE value = 100;
+----------+
| COUNT(*) |
+----------+
|        1 |
+----------+
1 row in set (0.001 sec)

# 当該レコードが存在しない場合
$ MariaDB [test]> SELECT COUNT(*) FROM auto_increment_test WHERE value = 900;
+----------+
| COUNT(*) |
+----------+
|        0 |
+----------+
1 row in set (0.001 sec)

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

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

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