innodb_autoinc_lock_mode <> 0 でINSERT .. SELECT .. をかけた時にauto_incrementなカラムが歯抜けになる理由を調べてみる

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日

MySQL 8.0.14で全件 COUNT(*)でも加速しない！？- 「innodb_parallel_read_threads」検証その３ - なからなLife

↓この現象が気になるっぽい

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)

という訳で調べてみた。
というかここのコメントに答えがあった。

https://github.com/mysql/mysql-server/blob/mysql-5.7.25/sql/handler.cc#L3548-L3559

(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行なので、ここまで払い出して終了

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に変えてみて性能検証とかしてみるといいんじゃないですかね…。。

​