Home > Database > Mysql Tutorial > MySQLauto_increment间隙问题_MySQL

MySQLauto_increment间隙问题_MySQL

WBOY
Release: 2016-06-01 13:01:30
Original
1166 people have browsed it

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>
Copy after login

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

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

<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>
Copy after login

<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>
Copy after login

<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>
Copy after login

<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。

Related labels:
source:php.cn
Statement of this Website
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn
Popular Tutorials
More>
Latest Downloads
More>
Web Effects
Website Source Code
Website Materials
Front End Template