MySQLauto_increment间隙问题_MySQL

MySQL中auto_increment字段估计大家都经常用到，特别是innodb引擎。我也经常用，只知道mysql可以保证这个字段在多进程操作时的原子性，具体原理不甚了了，一次心血来潮，遂去查阅了MySQL手册以及相关资料，了解了个大概。本文只探究了mysql5.5中innodb引擎auto_increment的问题，myisam引擎未测试，后续如果有时间我会补上。

1.传统auto_increment原理

传统的auto_increment实现机制：mysql innodb引擎的表中的auto_increment字段是通过在内存中维护一个auto-increment计数器，来实现该字段的赋值，注意自增字段必须是索引,而且是索引的第一列,不一定要是主键。例如我现在在我的数据库test中创建一个表t，语句如下:

<code class="hljs sql">CREATE TABLE t (a bigint unsigned auto_increment primary key) ENGINE=InnoDB;</code>

则字段a为auto_increment类型，在mysql服务器启动后，第一次插入数据到表t时，InnoDB引擎会执行等价于下面的语句:

<code class="hljs sql"><code class="hljs sql">SELECT MAX(a) FROM t FOR UPDATE;</code></code>

<code class="hljs sql">Innodb获取到当前表中a字段的最大值并将增加1(默认是增加1，如果要调整为增加其他数目，可以设置auto_increment_increment这个配置的设置)然后赋值给该列以及内存中该表对应的计数器。

<code class="hljs sql">如果表t为空，则InnoDB用来设置的值为为1.当然这个默认值夜可以通过 auto_increment_offset这个配置项来修改。

<code class="hljs sql">auto-increment计数器初始化以后，如果插入数据没有指定auto_increment列的值，则Innodb直接增加auto-increment计数器的值并将增加后的值赋给新的列。如果插入数据指定了auto_increment列的值且这个值大于该表当前计数器的值，则该表计数器的值会被设置为该值。

<code class="hljs sql">插入数据时如果指定auto_increment列的值为NULL或者0,则和你没有指定这个列的值一样,mysql会从计数器中分配一个值给该列.而如果指定auto_increment列的值为负数或者超过该列所能存储的最大数值,则该行为在mysql中没有定义,可能会出现问题.根据我的测试来看,插入负值会有警告,不过最终存储的数据还是正确的.如果是超过了比如上面定义的表t的bigint类型的最大值,同样会有警告,而且插入的数值是bigint类型所能存储的最大值18446744073709551615.

<code class="hljs sql">在传统的auto_increment设置中,每次访问auto-increment计数器的时候, INNODB都会加上一个名为AUTO-INC锁直到该语句结束(<strong>注意锁只持有到语句结束,不是事务结束</strong>).AUTO-INC锁是一个特殊的表级别的锁,用来提升包含auto_increment列的并发插入性能.因此,两个事务不能同时获取同一个表上面的AUTO-INC锁,如果持有AUTO-INC锁太长时间可能会影响到数据库性能(比如INSERT INTO t1&hellip; SELECT &hellip; FROM t2这类语句).

<code class="hljs sql">2.改进的auto_increment

<code class="hljs sql">鉴于传统auto_increment机制要加AUTO-INC这种特殊的表级锁,性能还是太差,于是在mysql5.1开始,新增加了一个配置项innodb_autoinc_lock_mode来设定auto_increment方式.可以设置的值为0,1,2.其中0就是第一节中描述的传统auto_increment机制,而1和2则是新增加的模式,默认该值为1,可以中mysql配置文件中修改该值.这里主要来看看这两种新的方式的差别，在描述差别前需要先明确几个插入类型：

<code class="hljs sql">1）simple inserts

<code class="hljs sql">simple inserts指的是那种能够事先确定插入行数的语句，比如INSERT/REPLACE INTO 等插入单行或者多行的语句，语句中不包括嵌套子查询。此外，INSERT INTO &hellip; ON DUPLICATE KEY UPDATE这类语句也要除外。

<code class="hljs sql">2）bulk inserts

<code class="hljs sql">bulk inserts指的是事先无法确定插入行数的语句，比如INSERT/REPLACE INTO &hellip; SELECT, LOAD DATA等。

<code class="hljs sql">3）mixed-mode inserts

<code class="hljs sql">指的是simple inserts类型中有些行指定了auto_increment列的值有些没有指定，比如：<br /> INSERT INTO t1 (c1,c2) VALUES (1,&rsquo;a&rsquo;), (NULL,&rsquo;b&rsquo;), (5,&rsquo;c&rsquo;), (NULL,&rsquo;d&rsquo;);<br /> 另外一种mixed-mode inserts是 INSERT &hellip; ON DUPLICATE KEY UPDATE这种语句，可能导致分配的auto_increment值没有被使用。

<code class="hljs sql">下面看看设置innodb_autoinc_lock_mode为不同值时的情况：

<code class="hljs sql">innodb_autoinc_lock_mode=0（traditional lock mode）

<code class="hljs sql">传统的auto_increment机制，详见1.这种模式下所有针对auto_increment列的插入操作都会加AUTO-INC锁，分配的值也是一个个分配，是连续的，正常情况下也不会有间隙（当然如果事务rollback了这个auto_increment值就会浪费掉，从而造成间隙）。

<code class="hljs sql">innodb_autoinc_lock_mode=1（consecutive lock mode）

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

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

<code class="hljs sql">innodb_autoinc_lock_mode=2（interleaved lock mode）

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

<code class="hljs sql">3.可能产生间隙原因总结

<code class="hljs sql">经过上面的文档分析，下面总结下针对auto_increment字段的各种类型的inserts语句可能出现间隙问题的原因：

<code class="hljs sql">simple inserts

<code class="hljs sql">针对innodb_autoinc_lock_mode=0,1,2，只有在一个有auto_increment列操作的事务出现回滚时，分配的auto_increment的值会丢弃不再使用，从而造成间隙。

<code class="hljs sql">bulk inserts（这里就不考虑事务回滚的情况了，事务回滚是会造成间隙的）

<code class="hljs sql">innodb_autoinc_lock_mode=0,由于一直会持有AUTO-INC锁直到语句结束，生成的值都是连续的，不会产生间隙。<br /> innodb_autoinc_lock_mode=1，这时候一条语句内不会产生间隙，但是语句之间可能会产生间隙。后面会有例子说明。<br /> innodb_autoinc_lock_mode=2，如果有并发的insert操作，那么同一条语句内都可能产生间隙。

<code class="hljs sql">mixed-mode inserts

<code class="hljs sql">这种模式下针对innodb_autoinc_lock_mode的值配置不同，结果也会不同，当然innodb_autoinc_lock_mode=0时时不会产生间隙的，而innodb_autoinc_lock_mode=1以及innodb_autoinc_lock_mode=2是会产生间隙的。后面例子说明。

<code class="hljs sql"><strong>另外注意的一点是，在master-slave这种架构中，复制如果采用statement-based replication这种方式，则innodb_autoinc_lock_mode=0或1才是安全的。而如果是采用row-based replication或者mixed-based replication，则innodb_autoinc_lock_mode=0,1,2都是安全的。</strong>

<code class="hljs sql">4.实例

<code class="hljs sql">测试的两个表分别为t和t1，定义分别如下:

<code class="hljs sql"><code class="hljs sql"><code class="hljs sql">CREATE TABLE `t` (
  `a` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`a`)
) ENGINE=InnoDB;


CREATE TABLE `t1` (
  `c1` int(11) NOT NULL AUTO_INCREMENT,
  `c2` varchar(10) DEFAULT NULL,
  PRIMARY KEY (`c1`)
) ENGINE=InnoDB</code></code></code>

<code class="hljs sql"><code class="hljs sql">首先在表t插入1-10000000共1千万条数据，为了后面测试方便。开启session1，执行下面语句：

<code class="hljs sql"><code class="hljs sql"><code class="hljs sql"><code class="hljs sql">insert into t1(c2) select * from t;</code></code></code></code>

<code class="hljs sql"><code class="hljs sql"><code class="hljs sql">然后开启session2，在t1中插入数据：

<code class="hljs sql"><code class="hljs sql"><code class="hljs sql"><code class="hljs sql"><code class="hljs sql">insert into t1(c2) values(400);</code></code></code></code></code>

<code class="hljs sql"><code class="hljs sql"><code class="hljs sql"><code class="hljs sql">针对innodb_autoinc_lock_mode不同的情况，新插入的数据的c1的值也不同。

<code class="hljs sql"><code class="hljs sql"><code class="hljs sql"><code class="hljs sql">innodb_autoinc_lock_mode=0时，因为session1的语句都是加AUTO-INC锁，因此，session1先开始的话，c1列的值都是1-10000000连续的值，由于在传统机制下，auto_increment值都是一个个分配，因此session2插入的数据c1的值则是10000001。最终看到的就是有两条这样的数据(400,400),(10000001,400)。

<code class="hljs sql"><code class="hljs sql"><code class="hljs sql"><code class="hljs sql">innodb_autoinc_lock_mode=1时，同样session1也会加AUTO-INC锁，但是由于该模式下会预先分配auto_increment的值，所以可以看到在session2中插入的数据的c1值不会是10000001，但是不会是1-10000000这其中的数字，因为session1有加AUTO-INC锁。最终的数据会是这样两条:（400，400）, （10026856，400）。

<code class="hljs sql"><code class="hljs sql"><code class="hljs sql"><code class="hljs sql">innodb_autoinc_lock_mode=2时，session1不会加AUTO-INC锁，因此虽然session2是后执行，但是并不影响auto_increment值分配，最终的值跟我们执行session2的时间有关，最终的值可能是这样的：(400,400)，（1235603，400）这样的，会占用1-10000000之间的值。

<code class="hljs sql"><code class="hljs sql"><code class="hljs sql"><code class="hljs sql">5.另外几点

<code class="hljs sql"><code class="hljs sql"><code class="hljs sql"><code class="hljs sql">1）关于innodb_autoinc_lock_mode=1时，auto_increment预先分配策略可以参照参考资料2，假定表t中已经初始有一条记录1，然后在表t中我们用`insert into t select NULL from t执行四次，可以看到表t中最终的记录会是1，2，3，4，6，7，8，9，13，14，15，16，17，18，19，20，其中5，10，11，12都浪费掉了。参考资料1后面部分也有讲到预分配问题。

<code class="hljs sql"><code class="hljs sql"><code class="hljs sql"><code class="hljs sql">2）INSERT INTO t1…SELECT … FROM t这类语句会对表t1加record lock，如果隔离级别是read committed，或者设置了innodb_locks_unsafe_for_binlog且隔离级别不是serialize，则不会对t加锁，否则对t加shared next-key lock。