Mysql id 自增空洞问题

场景

最近写了一个收集号码的逻辑，早上来 count 了一下 phone 表，发现已经收集到了 33w 条记录。

> select count(*) from phone

336019

但细心的我留意到似乎有 id 值很大的记录

> select min(id) from phone

1

> select max(id) from phone

1003498

咂摸着觉着不对味。

原因

查了查资料这还有个术语，叫 MySQL auto_increment 空洞问题，是因为我插入/更新表的事后偷懒使用了 upsert 函数搞出来，生成的 SQL 语句是

INSERT INTO xxtable ON DUPLICATE KEY UPDATE

而上面的语句是属于 mixed-mode inserts，分配时并不知道是插入还是更新，所以都统统让 id 自增加大。而且 innodb 的默认 innodb_autoinc_lock_mode 模式为 1，在 mixed-mode inserts 中的确会造成空洞。

inserts mode

插入类型有以下几种

simple inserts

simple inserts 指的是那种能够事先确定插入行数的语句，比如 INSERT/REPLACE INTO 等插入单行或者多行的语句，语句中不包括嵌套子查询。

此外，INSERT INTO ... ON DUPLICATE KEY UPDATE 也除外。

bulk inserts

bulk inserts 批量插入，事先无法确定插入行数的语句。

mixed-mode inserts

simple inserts 类型中有些行指定了 auto_increment 列的值，有些没有指定，比如：
INSERT INTO t1 (c1,c2) VALUES (1,'a'), (NULL,'b'), (5,'c'), (NULL,'d');

另一种情况 INSERT ... ON DUPLICATE KEY UPDATE 这种语句，可能导致分配的 auto_increment 值没有被使用。

innodb_autoinc_lock_mode

Mysql 5.1 后加了一个配置叫 innodb_autoinc_lock_mode

innodb_autoinc_lock_mode = 0（traditional lock mode）

传统的 auto_increment 机制，针对 auto_increment 列的插入操作都会加 AUTO-INC 锁，分配的值也是一个个分配，是连续的，正常情况下也不会有空洞（当然如果事务rollback了这个auto_increment值就会浪费掉，从而造成空洞）。

innodb_autoinc_lock_mode = 1（consecutive lock mode）, Innodb 默认

这种情况下，针对 bulk inserts 才会采用 AUTO-INC 锁这种方式，而针对 simple inserts，则采用了一种新的轻量级的互斥锁来分配 auto_increment 列的值。当然，如果其他事务已经持有了 AUTO-INC 锁，则 simple inserts 需要等待.

需要注意的是，在 innodb_autoinc_lock_mode=1 时，语句之间是可能出现 auto_increment 值的间隔的。比如 mixed-mode inserts 以及 bulk inserts 中都有可能导致一些分配的 auto_increment 值被浪费掉从而导致空洞。后面会有例子。

innodb_autoinc_lock_mode=2（interleaved lock mode）

这种模式下任何类型的 inserts 都不会采用 AUTO-INC 锁，性能最好，但是在同一条语句内部产生 auto_increment 值空洞。此外，这种模式对 statement-based replication 也不安全。