TL;DR
- innodb_autoinc_lock_mode が0の場合、ステートメントが始まってから終わるまでまるまるAUTOINC_LOCKで保護する -> 歯抜けが起きない
- non 0 の場合、ステートメントが始まった時点でAUTOINC_LOCK ->
(2^0 + 2^1 + 2^2 + ..)個autoinc払い出し -> AUTOINC_LOCK解除 するので歯抜けが起きる
InnoDBのパラレルリードマスター @atsuizo さんがふと、
MySQLで、auto increment列があるテーブルに「insert into ... select 0 from 自分自身」を繰り返してレコード件数を倍々に増やしていくと、auto incrementの欠番が激しく発生するんだけど(5.6から8.0まで確認)、これパーティション切ってあると欠番発生しないんだな。わけわからん。— atsuizo (@atsuizo) 2019年1月27日
↓この現象が気になるっぽい
mysql57 2> DROP TABLE t1;
Query OK, 0 rows affected (0.01 sec)
mysql57 2> CREATE TABLE t1 (num int unsigned NOT NULL auto_increment PRIMARY KEY);
Query OK, 0 rows affected (0.01 sec)
mysql57 2> SELECT @@innodb_autoinc_lock_mode;
+----------------------------+
| @@innodb_autoinc_lock_mode |
+----------------------------+
| 1 |
+----------------------------+
1 row in set (0.01 sec)
mysql57 2> INSERT INTO t1 VALUES (0); -- 1が入るので次は2
Query OK, 1 row affected (0.01 sec)
mysql57 2> SHOW CREATE TABLE t1\G
*************************** 1. row ***************************
Table: t1
Create Table: CREATE TABLE `t1` (
`num` int(10) unsigned NOT NULL AUTO_INCREMENT,
PRIMARY KEY (`num`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4
1 row in set (0.00 sec)
mysql57 2> INSERT INTO t1 SELECT 0 FROM t1; -- 2が入るので次は3
Query OK, 1 row affected (0.00 sec)
Records: 1 Duplicates: 0 Warnings: 0
mysql57 2> SHOW CREATE TABLE t1\G
*************************** 1. row ***************************
Table: t1
Create Table: CREATE TABLE `t1` (
`num` int(10) unsigned NOT NULL AUTO_INCREMENT,
PRIMARY KEY (`num`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4
1 row in set (0.00 sec)
mysql57 2> INSERT INTO t1 SELECT 0 FROM t1; -- 3, 4が入るので次は5になってほしいんだけど
Query OK, 2 rows affected (0.00 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql57 2> SHOW CREATE TABLE t1\G -- 5を飛ばして6になっちゃう
*************************** 1. row ***************************
Table: t1
Create Table: CREATE TABLE `t1` (
`num` int(10) unsigned NOT NULL AUTO_INCREMENT,
PRIMARY KEY (`num`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8mb4
1 row in set (0.00 sec)
mysql57 2> INSERT INTO t1 SELECT 0 FROM t1; -- 6, 7, 8, 9が入るので次は10になってほしいんだけど
Query OK, 4 rows affected (0.00 sec)
Records: 4 Duplicates: 0 Warnings: 0
mysql57 2> SHOW CREATE TABLE t1\G -- 13まで飛んじゃう
*************************** 1. row ***************************
Table: t1
Create Table: CREATE TABLE `t1` (
`num` int(10) unsigned NOT NULL AUTO_INCREMENT,
PRIMARY KEY (`num`)
) ENGINE=InnoDB AUTO_INCREMENT=13 DEFAULT CHARSET=utf8mb4
1 row in set (0.00 sec)
mysql57 2> SELECT * FROM t1;
+-----+
| num |
+-----+
| 1 |
| 2 |
| 3 |
| 4 | -- 5がない
| 6 |
| 7 |
| 8 |
| 9 | -- 次は13から始まる
+-----+
8 rows in set (0.00 sec)
という訳で調べてみた。
というかここのコメントに答えがあった。
というかここのコメントに答えがあった。
(gdb) b ha_innobase::get_auto_increment
+b ha_innobase::get_auto_increment
Breakpoint 2 at 0xeff860: file /home/yoku0825/mysql-5.7.25/storage/innobase/handler/ha_innodb.cc, line 16700.
(gdb) c
+c
Continuing.
Breakpoint 2, ha_innobase::get_auto_increment (this=0x7fb570022740, offset=1, increment=1, nb_desired_values=1,
first_value=0x7fb59812e230, nb_reserved_values=0x7fb59812e240)
at /home/yoku0825/mysql-5.7.25/storage/innobase/handler/ha_innodb.cc:16700
16700 {
(gdb) c
+c
Continuing.
Breakpoint 2, ha_innobase::get_auto_increment (this=0x7fb570022740, offset=1, increment=1, nb_desired_values=2,
first_value=0x7fb59812e230, nb_reserved_values=0x7fb59812e240)
at /home/yoku0825/mysql-5.7.25/storage/innobase/handler/ha_innodb.cc:16700
16700 {
(gdb) c
+c
Continuing.
Breakpoint 2, ha_innobase::get_auto_increment (this=0x7fb570022740, offset=1, increment=1, nb_desired_values=4,
first_value=0x7fb59812e230, nb_reserved_values=0x7fb59812e240)
at /home/yoku0825/mysql-5.7.25/storage/innobase/handler/ha_innodb.cc:16700
16700 {
(gdb) c
+c
Continuing.
Breakpoint 2, ha_innobase::get_auto_increment (this=0x7fb570022740, offset=1, increment=1, nb_desired_values=8,
first_value=0x7fb59812e230, nb_reserved_values=0x7fb59812e240)
at /home/yoku0825/mysql-5.7.25/storage/innobase/handler/ha_innodb.cc:16700
16700 {
(gdb) c
+c
Continuing.
これがさっきの結果にもう一度
INSERT INTO t1 SELECT 0 FROM t1 で8行足した時のステップ実行。
最初のautoinc = 13から見て、
13 + (1) .. 1行 < 8行なのでもう一度
13 + (1 + 2) .. 3行 < 8行なので略
13 + (1 + 2 + 4) .. 7行 < 8行略
13 + (1 + 2 + 4 + 8) .. 15行 > 8行なので、ここまで払い出して終了
13 + (1) .. 1行 < 8行なのでもう一度
13 + (1 + 2) .. 3行 < 8行なので略
13 + (1 + 2 + 4) .. 7行 < 8行略
13 + (1 + 2 + 4 + 8) .. 15行 > 8行なので、ここまで払い出して終了
mysql57 3> SELECT * FROM t1;
+-----+
| num |
+-----+
| 1 |
| 2 |
| 3 |
| 4 |
| 6 |
| 7 |
| 8 |
| 9 |
| 13 |
| 14 |
| 15 |
| 16 |
| 17 |
| 18 |
| 19 |
| 20 |
+-----+
16 rows in set (0.00 sec)
mysql57 3> SHOW CREATE TABLE t1\G
*************************** 1. row ***************************
Table: t1
Create Table: CREATE TABLE `t1` (
`num` int(10) unsigned NOT NULL AUTO_INCREMENT,
PRIMARY KEY (`num`)
) ENGINE=InnoDB AUTO_INCREMENT=28 DEFAULT CHARSET=utf8mb4
1 row in set (0.01 sec)
という訳で、autoincを一気に払い出すのが
(2^0 + 2^1 + 2^2 + ..) だから、歯抜けになるようでしたん。
じゃあなんで
(2^0 + 2^1 + 2^2 + ..) で払い出すのかまではわからんですが。気になる人は、3に変えてみて性能検証とかしてみるといいんじゃないですかね…。。