【MySQL】InnoDBのテーブルでAUTO_INCREMENTの連番が飛ぶ

問題

InnoDBエンジンのあるテーブルに、insert into T select * from XXX 系のSQLを流すと、AUTO_INCREMENTの連番が余分に消費されて、次に挿入するレコードにはかなり飛んだ番号が付きました。

1,2,3,4,5,6,7,8,9,10 の10行が入った後で、次の行を入れると連番が16になっているなど。

なぜでしょう。

答え

より単純な再現手順。

insertは2行のはずだが、連番が3進む。1つ余分に進む。

/* AUTO_INCREMENTのカラムを持つテーブルを作成 */
create table T (
    id int primary key auto_increment,
    code varchar(10) not null
) engine=InnoDB;

/* insert select をする. code 'a' と 'b' の行を追加する. */
insert into T (code)
select 'a'
union
select 'b';

/* AUTO_INCREMENTの値を確認する. 次は4. つまり1,2,3は消費された. */
show table status like 'T';
+------+--------+---------+------------+--------+ ～ 略 ～ +----------------+ ～ 略
| Name | Engine | Version | Row_format | Rows   | ～ 略 ～ | Auto_increment | ～ 略
+------+--------+---------+------------+--------+ ～ 略 ～ +----------------+ ～ 略
| T    | InnoDB |      10 | Compact    |      2 | ～ 略 ～ |              4 | ～ 略
+------+--------+---------+------------+--------+ ～ 略 ～ +----------------+ ～ 略

InnoDBの性能優先の設定だとこうなるようだ。

innodb_autoinc_lock_modeという設定項目があり、innodb_autoinc_lock_mode=1 がデフォルト。

innodb_autoinc_lock_mode=0 の設定にすると、飛ばなくなった。

特に理由がなければデフォルトそのままにしておいた方がよいでしょう。

参考

http://dev.mysql.com/doc/refman/5.1-olh/ja/innodb-auto-increment-configurable.html

http://dev.mysql.com/doc/refman/5.1/en/innodb-parameters.html#sysvar_innodb_autoinc_lock_mode