澳门1495

再次出口MySQL auto_increment空洞问题。MySQL auto_increment空洞问题。

九月 27th, 2018  |  综合体育

以档次遭到经常会生这种需要,用户通过第三正系登录时如无注册,则自动为用户注册,注册过之用户自行登录。有时候图方便可能就是直INSERT INTO user ON DUPLICAET KEY UPDATE...无异于句
SQL
解决了,功能都健康,问题就是只要用户表中发生auto_increment字段,则会招auto_increment字段产生空洞问题,一段时间后会发觉用户ID会经常出现不连续的事态,虽然mysql的自增ID可以死十分,一般系统是够用之,但是对强迫症患者是是心有余而力不足接受的。我测试的mysql版本也5.5.58,使用的凡Innodb引擎,隔离级别也Repeatable
Read。

MySQL中auto_increment字段估计大家都隔三差五下,特别是innodb引擎。我为时时用,只了解mysql可以包是字段在差不多进程操作时的原子性,具体原理不深了了,一浅心血来潮,遂去查了MySQL手册和相关资料,了解了个大致。本文仅探究mysql5.5被innodb引擎auto_increment的问题,因为myisam引擎不会见设有auto_increment空洞问题。

1 场景

当用户从第三正在登录时,假定用的凡手机号举行唯一标识,通常以我们协调的系统遭到会修一个用户表,如下:

 CREATE TABLE `user` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `mobile` varchar(11) DEFAULT NULL,
  `last_login_time` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `mobile` (`mobile`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

当用户从第三正登录时,我们校验通过后,会将手机号插入到user表里登记用户。如果用户都在,则更新最后登录时,为了便利,经常像下这么做,功能上看起是无错的,问题就是运行一段时间后会意识user表的id字段居然是未总是的,而且常常两独id之间空洞还生十分,比如达一个id是4,下一个变成了21。如下面例子中,再插入一久新记录时,id会变成3,也就是说id=2这个价值为浪费了。

mysql> INSERT INTO user(mobile, last_login_time) VALUES('15012345678',
 NOW()) ON DUPLICATE KEY UPDATE last_login_time = NOW();
Query OK, 1 row affected (0.00 sec)

mysql> show create table user;
+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                                                                                                                               |
+----------------------------------------------------------------------+
| user  | CREATE TABLE `user` (
......
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 |

mysql> INSERT INTO user(mobile, last_login_time) VALUES('15012345678', 
NOW()) ON DUPLICATE KEY UPDATE last_login_time = NOW();
Query OK, 2 rows affected (0.00 sec)

mysql> show create table user;
+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                                                                                                                               |
+-------+---------------------------------------------------------------------
| user  | CREATE TABLE `user` (
......
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8 |

1.传统auto_increment原理

传统的auto_increment实现机制:mysql
innodb引擎的表中的auto_increment字段是由此在内存中保障一个auto-increment计数器,来兑现该字段的赋值,注意起增字段必须是索引,而且是索引的第一列,不必然要是是主键。例如我本于自家的数据库test中创造一个表t,语句如下:

CREATE TABLE t (a bigint unsigned auto_increment primary key) ENGINE=InnoDB;

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

SELECT MAX(a) FROM t FOR UPDATE;

Innodb获取到手上表明中a字段的顶深价值并拿长1(默认是增多1,如果要调动为增其他数,可以设置auto_increment_increment这个布局的装)然后赋值给该列以及内存中该表对应之计数器。

而表t为空,则InnoDB用来安装的价值吗呢1.自这默认值夜可以经过
auto_increment_offset这个布局起来改。

auto-increment计数器初始化以后,如果插入数据没有指定auto_increment列的值,则Innodb直接长auto-increment计数器的价并拿加码后底值赋给新的排列。如果插入数据指定了auto_increment列的值且这个价超过该表当前计数器的值,则该表计数器的值会被设置为该值。

安插数据时假如指定auto_increment列的值为NULL或者0,则跟而从未点名这个列的价一样,mysql会自计数器中分红一个值为该列.而若指定auto_increment列的值吗负数或者超越该列所能够储存的极致要命勤值,则该行为在mysql中没有概念,可能会见冒出问题.根据自身的测试来拘禁,插入负值会时有发生警告,不过最终存储的多寡或者对的.如果是过了准上面定义的表t的bigint类型的卓绝深价值,同样会有警示,而且插入的数值是bigint类型所能积存的最好可怜价值18446744073709551615.

在传统的auto_increment设置中,每次访auto-increment计数器的下,
INNODB都见面添加一个名为AUTO-INC锁直到该语句结束(小心锁就享有到告知句结束,不是事情了).AUTO-INC锁是一个奇特之阐发级别的吊,用来提升包含auto_increment列的产出插入性能.因此,两个事情不可知而且获取与一个表明上面的AUTO-INC锁,如果持有AUTO-INC锁太长时也许会见影响到数据库性能(比如INSERT
INTO t1… SELECT … FROM t2当即好像讲话).

2 分析

在MySQL官方文档已经关系了这题材了实际上,当表t1遭遇列a已经生一个值也1底情状下,通常状态施行下就片漫长告句效果是同样的,但是注意了,如果表t1是InnoDB引擎而且出一致列为auto_increment的景况下,影响是匪平等的,会有眼前提到的auto_increment空洞问题。MyISAM引擎的表明不让之影响,不会见时有发生空洞问题。

INSERT INTO t1 (a,b,c) VALUES (1,2,3)
  ON DUPLICATE KEY UPDATE c=c+1;

UPDATE t1 SET c=c+1 WHERE a=1;

更为确切的说,产生空洞问题尚同innodb_autoinc_lock_mode本条MySQL配置相关。该配置于MySQL5.1引入,是为提升auto_increment字段的产出性能引入的,默认值为1。该值可以配备为0(traditional lock mode),1(consecutive lock mode),2(interleaved lock mode),除了0基本无发生空洞外,配置外价值都是可能有auto_increment空洞的,简单总结如下,更详实的可以参考
innodb-auto-increment-handling。

  • 1)如果事情回滚了,则无是0,1,2还见面造成业务中采取了的auto_increment的价值浪费。

  • 2)如果设置为0,是traditional lock mode,则任意插入语句都见面加
    AUTO-INC 锁,基本未见面生空洞,除了1被的rollback情况他。

  • 3)如果安为1或者2的时节,simple inserts谈(simple
    inserts指的凡那种会先行确定插入行数的口舌,比如INSERT/REPLACE INTO
    等插入单行或者多行的言语,语句中未包括嵌套子查询)不会见有空洞。但是对bulk inserts(bulk
    inserts指的凡预先无法确定插入行数的口舌,比如INSERT/REPLACE INTO …
    SELECT FROM…, LOAD DATA等)和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值,导致一些浪费。
    特别是装也2底时,在实施任意插入语句都无见面加 AUTO-INC
    锁,从而以讲话执行进程遭到还或发生空洞。

2.改进的auto_increment

鉴于传统auto_increment机制使加AUTO-INC这种特殊的表级锁,性能还是最差,于是在mysql5.1从头,新添了一个布置起innodb_autoinc_lock_mode来设定auto_increment方式.可以设置的值为0,1,2.其中0就是第一省吃讲述的风土人情auto_increment机制,而1以及2虽说是初长的模式,默认该值为1,可以中mysql配置文件中改该值.这里要来探视这片种植新的方的区别,在描述差别前待先明了几只插入类型:

  • 1)simple inserts

    simple
    inserts指的凡那种会优先确定插入行数的语句,比如INSERT/REPLACE INTO
    等插入单行或者多行的口舌,语句中不包括嵌套子查询。此外,INSERT INTO
    … ON DUPLICATE KEY UPDATE这看似语句也要是除。

  • 2)bulk inserts

    bulk inserts指的是先期无法确定插入行数的口舌,比如INSERT/REPLACE
    INTO … SELECT, LOAD DATA等。

  • 3)mixed-mode inserts

    借助于的是simple
    inserts类型受到微微行指定了auto_increment列的值多少没点名,比如:
    INSERT INTO t1 (c1,c2) VALUES (1,’a’), (NULL,’b’), (5,’c’),
    (NULL,’d’);
    另外一栽mixed-mode inserts是 INSERT … ON DUPLICATE KEY
    UPDATE这种话,可能导致分配的auto_increment值没有让使用。

下看看设置innodb_autoinc_lock_mode为不同值时的景况:

  • innodb_autoinc_lock_mode=0(traditional lock mode)

    传统的auto_increment机制,详见1.这种模式下有对auto_increment列的插入操作都见面加AUTO-INC锁,分配的值也是一个个分红,是接连的,正常情形下啊无见面产生空洞(当然要事务rollback了是auto_increment值就会浪费掉,从而导致空洞)。

  • innodb_autoinc_lock_mode=1(consecutive lock mode)

    这种情景下,针对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也未安全。

3 一种错误示范

这就是说以减小第一节吃的auto_increment空洞问题,一种植办法就是是INSERT前先判断下用户是否有,不存才实施插入语句,否则用户每次登录都见面招致auto_increment值被浪费。方案如下:

with transaction:
    user = SELECT * FROM user WHERE mobile = '15012345678' FOR UPDATE;
    if not user:
       INSERT INTO user(mobile, last_login_time) VALUES('15012345678', NOW()) 
    UPDATE user SET last_login_time = NOW();

本条代码乍看是不曾问题了,mobile是unique
key,这样的FOR UPDATE如木有问题,这是一个排除异锁,一个session对当下漫漫记下加了清除异锁,其他session不能够针对就长长的记下加锁和改(不可知
LOCK IN SHARE MODE 以及 UPDATE
等,要留意下SELECT FOR UPDATE特当业务中或autocommit关闭的情下才见面加锁)。但是,这仅当记录是的状况下才是针对性记录加X锁,没有Gap锁。而而这记录不存,则针对第一个未满足条件的记录加Gap锁,保证没有满足条件的笔录插入。

如果mobile=15012345678这条记下不存,并发的多单session都得以进入SELECT ... FOR UPDATE,因为都是加的Gap锁(X
locks gap before
rec),Gap锁之间是相当的。此时,其中任意一个session再实践
INSERT INTO user(mobile, last_login_time) VALUES('15012345678', NOW())语句会因为加insert intention lock(注:插入意向锁是一致种植特殊之Gap锁,不是MySQL的表级意向锁IS,IX等)超时而执行破产。其实这之Gap锁不就是沿住了
15012345678 这条记下,如果表中生其它的记录,会以可能插入 15012345678
的间距都锁住,MySQL加锁详细分析可以展现参考资料5。

3.可能产生空洞原因总结

通过地方的文档分析,下面总结下对auto_increment字段的各种类型的inserts语句可能出现空洞问题之由来:

  • simple inserts

    针对innodb_autoinc_lock_mode=0,1,2,只有在一个有auto_increment列操作的工作出现回滚时,分配的auto_increment的值会丢弃不再用,从而造成空洞。

  • bulk inserts(这里就不考虑工作回滚的事态了,事务回滚是会见招致空洞的)

    innodb_autoinc_lock_mode=0,由于直接会持有AUTO-INC锁直到谈结束,生成的值都是接二连三的,不见面时有发生空洞。
    innodb_autoinc_lock_mode=1,这时候一长告句内无见面生出空洞,但是说话之间或者会见起空洞。后面会有例子说明。
    innodb_autoinc_lock_mode=2,如果产生起的insert操作,那么同样条告句内还或发生空洞。

  • mixed-mode inserts

    这种模式下本着innodb_autoinc_lock_mode的价值配置不同,结果吗会见不同,当然innodb_autoinc_lock_mode=0时时不见面有空洞的,而innodb_autoinc_lock_mode=1以及innodb_autoinc_lock_mode=2是碰头出空洞的。后面例子说明。

除此以外注意的少数凡是,在master-slave这种架构中,复制如果用statement-based
replication这种艺术,则innodb_autoinc_lock_mode=0或1才是安全之。而只要是以row-based
replication或者mixed-based
replication,则innodb_autoinc_lock_mode=0,1,2且是安全之。

4 解决方案

啊者,如果只要优化auto_increment的荒废问题,又如果避上亦然省提到的死锁问题,还是略微事情要召开的。可行的几种方式如下:

  • 还是就算是干脆一点,在查询用户是否留存时时直用GET_LOCK(mobile),通过字符串锁而非是FOR UPDATE来避免上一致节省提到的问题。
  • 或者就算是先不加FOR UPDATE查询同一次等用户表,如果用户不存在,然后重新INSERT IGNORE INTO user ...。多一如既往坏查询,后面的逻辑不转换。
  • 当然,percona的马上篇稿子avoiding-auto-increment-holes-on-innodb-with-insert-ignore还来个很tricky的点子来避免auto_increment的泛问题,有趣味的得参见。

MySQL Innodb如果出现了有些加锁问题,可以透过下就几乎单指令来扶持分析。

show engine innodb status;
select * from information_schema.innodb_locks;
select * from information_schema.innodb_lock_waits;
select * from information_schema.innodb_trx;

4.实例

测试的少数个说明分别吗t和t1,定义分别如下:

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

第一在表t插入1-10000000一并1千万条数,为了后面测试好。开启session1,执行下告诉句:

insert into t1(c2) select * from t;

然后被session2,在t1中插数据:

insert into t1(c2) values(400);

针对innodb_autoinc_lock_mode不同的气象,新栽的多寡的c1的价值吗不同。

innodb_autoinc_lock_mode=0时,因为session1的说话都是加AUTO-INC锁,因此,session1先开吧,c1排列的价值都是1-10000000总是的值,由于当风俗体制下,auto_increment值都是一个个分配,因此session2插入的数额c1的值则是10000001。最终看的哪怕是产生零星漫长这样的数量(400,400),(10000001,400)。

innodb_autoinc_lock_mode=1常常,同样session1也会加AUTO-INC锁,但是由于该模式下会预先分配auto_increment的价,所以可以看出于session2遭插的数目的c1价未会见是10000001,但是未见面是1-10000000即间的数字,因为session1有加AUTO-INC锁。最终之数据会是这般少长:(400,400),
(10026856,400)。

innodb_autoinc_lock_mode=2时,session1不会见加AUTO-INC锁,因此虽然session2是继执行,但是并无影响auto_increment值分配,最终之价值与咱们实施session2的年华关于,最终之值可能是这样的:(400,400),(1235603,400)这样的,会占1-10000000里边的价值。

5 参考资料

  • https://dev.mysql.com/doc/refman/5.5/en/insert-on-duplicate.html
  • https://dev.mysql.com/doc/refman/5.5/en/innodb-auto-increment-handling.html
  • https://dev.mysql.com/doc/refman/5.5/en/innodb-locking.html
  • https://www.percona.com/blog/2011/11/29/avoiding-auto-increment-holes-on-innodb-with-insert-ignore/
  • http://hedengcheng.com/?p=844

5.另外几接触

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后头有也发出道到预分配问题。

2)INSERT INTO t1…SELECT … FROM t这类语词会针对表t1加record
lock,如果断级别是read
committed,或者安装了innodb_locks_unsafe_for_binlog且隔离级别不是serialize,则无见面针对t加锁,否则对t加shared
next-key lock。

6.参考资料

  • innodb-auto-increment-handling
  • auto_increment
    gaps
  • innodb-locks-set

相关文章

标签:, ,

Your Comments

近期评论

    功能


    网站地图xml地图