数据插入失败引发的主键auto_increment问题

昨天在调试一个业务代码中，无意间发现了一个问题。数据入库后的主键不是连续自增的，主键键值没过几秒就从两千多直接跳到了五千上下。这是为什么？瞬间引起我的注意。

先简单说明下环境。Mysql版本：5.6.23。为了防止某些数据重复，数据库中对某些字段设置了唯一索引，即unique key。经确认此表也只有一个业务程序在操作。那么，问题就定位到主键的auto_increment属性上了。

问题重现

下面来还原问题，以便能准确查找出原因。
表结构如下：

CREATE TABLE `test_innodb` (
   `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '自增ID',
   `username` varchar(100) NOT NULL COMMENT '用户名',
   PRIMARY KEY (`id`),
   UNIQUE KEY `UNIQUE_USERNAME` (`username`)
 ) ENGINE=InnoDB DEFAULT CHARSET=utf8

username字段设置了唯一索引（unique key）。先插入一条数据：

INSERT INTO test_innodb (`username`) VALUES('admin');

执行成功，主键ID为“1”。再次执行此SQL，因username重复，数据入库失败，提示：

Duplicate entry 'admin' for key 'UNIQUE_USERNAME'

然后再成功插入一条username不重复的数据，可以看到主键ID为“3”，已经略过了“2”。问题重现。

问题原因

在mysql官网的文档中，对“AUTO_INCREMENT Handling in InnoDB”，有这样一段说明：
InnoDB uses the in-memory auto-increment counter as long as the server runs. When the server is stopped and restarted, InnoDB reinitializes the counter for each table for the first INSERT to the table, as described earlier.
A server restart also cancels the effect of the AUTO_INCREMENT = N table option in CREATE TABLE and ALTER TABLE statements, which you can use with InnoDB tables as of MySQL 5.0.3 to set the initial counter value or alter the current counter value.
You may see gaps in the sequence of values assigned to the AUTO_INCREMENT column if you roll back transactions that have generated numbers using the counter.

大概意思是，Innodb存储引擎的auto_increment计数器是随着mysql-server启动分配，并永久缓存在内存中。当插入数据失败或者回滚事务时，内存中的auto_increment计算器的值却不会回滚。

举一反三

Innodb存储引擎会引起此问题，那MyISAM存储引擎呢？经过测试之后，以MyISAM作为存储引擎的数据表，不会出现上述的问题。

参考资料：http://dev.mysql.com/doc/refman/5.0/en/innodb-auto-increment-handling.html