简介小A在balabala写代码，DBA小B突然发来消息，“看你的用户特有信息表T，里面的主键就是自增id，已经达到16亿了，有多久了，短时间内主键会越界，插入失败，balabala...”记不太清了，最多1k，算了接下来，真的是1100万。原来运维是通过auto_increment的值来看的，也就是说表里面有大量的delete和insert操作，但是我的case大部分是update的。怎么会这样？故障排除该表是一个正在使用的简单接口服务。大数据每天都会统计大量的信息，然后推送给小A，小A会把信息更新到数据库中。如果是新数据，则插入，旧数据会更新之前的数据，对外接口只需要查询即可。很快，小A查看了自己的代码。没有地方可以删除，也没有地方可以主动插入或更新id。怎么会这样呢？是因为小B吗？这也不太可能。DBA管理许多表。如果有问题，早暴露出来，但我不知道问题出在哪里。小A仔细观察了现有的1000万多条数据，以插入时间和id作为主要观察字段。很快，他发现了一个问题。每天最先插入的数据总是比前一天多了1000万以上，有时增加的多，有时增加的少，小A把矛头指向了DBA小B，再次向小B描述了问题。小B问小A，“你用REPLACEINTO...语句了吗”，怎么回事，原来REPLACEINTO...会对主键有影响。REPLACEINTO...对主键的影响假设有一张表t1：CREATETABLE`t1`(`id`int(10)unsignedNOTNULLAUTO_INCREMENTCOMMENT'ID,auto-increment',`uid`bigint(20)unsignedNOTNULLDEFAULT'0'COMMENT'用户uid',`name`varchar(20)NOTNULLDEFAULT''COMMENT'用户昵称',PRIMARYKEY(`id`),UNIQUEKEY`u_idx_uid`(`uid`))ENGINE=InnoDBDEFAULTCHARSET=utf8COMMENT='testreplaceinto';如果新建一个Table，执行下面语句，***数据记录如何？insertintot1values(NULL,100,"test1"),(NULL,101,"test2");replaceintot1values(NULL,100,"test3");本来，REPLACEINTO...如果每插入唯一索引对应的数据time如果已经存在，会删除原来的数据，然后重新插入新的数据，会导致id增加，但实际期望可能是更新那条数据。小A说：“我知道replace是这样的，所以没用”，但是我又检查了一遍，果然不是我的问题，我没有用REPLACEINTO...，小A仔细检查了一下细细的，还是没有发现问题，让小B查看binlog日志，看看有没有奇怪的地方，查看之后，还是没有发现问题，确实是跳转了，但是没有实质性的问题。下图中@1的值对应自增主键id。使用(@2,@3)作为唯一索引后，过了很久，小B给小A指了一个方向，小A开始怀疑他的插入更新语句INSERT...ONDUPLICATEKEYUPDATE...是的，查了半天，果然这里没有问题。INSERT...ONDUPLICATEKEYUPDATE...对主键的影响这条语句类似于REPLACEINTO...，但是不会改变记录的主键，还是上面的表t1，我们执行下面的语句，执行后的结果是什么？insertintot1values(NULL,100,"test4")onduplicatekeyupdatename=values(name);是的，和小A预料的一样，主键没有增加，name字段也更新成自己想要的，但是执行结果中有提示，引起了小A的注意没有错误；2行受影响，用了10.7ms明明更新了一条数据，为什么这里受影响的记录数是2？小A，看auto_incrementCREATETABLE`t1`(`id`int(10)unsignedNOTNULLAUTO_INCREMENTCOMMENT'ID,autoincrement',`uid`bigint(20)unsignedNOTNULLDEFAULT'0'COMMENT'useruid',`name在当前表`varchar(20)NOTNULLDEFAULT''COMMENT'用户昵称',PRIMARYKEY(`id`),UNIQUEKEY`u_idx_uid`(`uid`))ENGINE=InnoDBAUTO_INCREMENT=5DEFAULTCHARSET=utf8COMMENT='testreplaceinto';原来是5`，这里应该是4。也就是说，上面的语句，类似于REPLACEINTO...，会把自增ID加1，但是实际的记录并没有加。为什么是这样？小A查资料得知，原来mysqlAutoinc的主键有个参数innodb_autoinc_lock_mode，有0、1、2三种可能，是mysql5.1后加入的。默认值为1，之前的版本可视为0，可以通过下面的语句查看当前是哪种模式select@@innodb_autoinc_lock_mode;A使用的数据库的默认值也是1，在做简单的insert时（可以确定插入的行数），不解锁table，直接将auto_increment加1，这样也提高了性能。当inserted语句类似于insertintoselect...复杂的语句是事先不知道的时候，插入的行数是事先不知道的。这时候需要给表加锁（一个特殊的表锁，名字叫AUTO_INC），这样auto_increment是准确的，直到语句结束才释放锁。还有一种insert叫做Mixed-modeinserts，比如INSERTINTOt1(c1,c2)VALUES(1,'a'),(NULL,'b'),(5,'c'),(NULL,'d'),有的明确指定自增主键值，有的不指定，我们这里讨论的INSERT...ONDUPLICATEKEYUPDATE...也属于此类。这个时候会分析语句，然后尽可能的按赋值auto_incrementid可能有很多情况，这个怎么理解，我看下面这个例子：truncatetablet1;insertintot1values(NULL,100,"test1"),(NULL,101,"test2"),(NULL,102,"test2"),(NULL,103,"test2"),(NULL,104,"test2"),(NULL,105,"test2");--此时数据表中下一个自增id为7deletefromt1whereidin(2,3,4);--此时数据表中只剩下1,5,6,和自增id仍然是7insertintot1values(2,106,"test1"),(NULL,107,"test2"),(3,108,"test2");--这里的自增id是什么？上面的例子执行完之后，表的下一个自增id是10，你理解对了吗，因为最后执行的是一个Mixed-modeinserts语句，innoDB会解析语句，然后赋值三个id。一个id是10，但是分配的三个id并没有全部使用。在此感谢@alwayslate指出，如果你看了官方文档，误解了mode0，不管什么情况都会加表锁，语句执行时释放。如果真的添加一条记录，auto_increment加1。至于方式2，任何情况下都不加AUTO_INC锁，存在安全问题。当binlog格式设置为Statement方式时，从库同步时，执行结果可能与主库不一致，这是个大问题。因为可能有一个复杂的插入还在执行，又来了一个插入，恢复的时候一个一个执行，所以这种并发问题无法复现，记录ID可能不匹配。id跳转的问题分析到此为止。由于innodb_autoinc_lock_mode的值为1，INSERT...ONDUPLICATEKEYUPDATE...是一个简单的语句，可以提前计算出受影响的行数，所以不管更新与否，这里都会进行auto_increment加1（多行大于1）。如果把innodb_autoinc_lock_mode的值改成0，再执行一次INSERT...ONDUPLICATEKEYUPDATE...，会发现auto_increment并没有增加，因为这个mode直接加了AUTO_INC锁，语句执行的时候释放，并且发现没有增加行数，自增id不会增加。INSERT...ONDUPLICATEKEYUPDATE...受影响的行数是1为什么它返回2？为什么会这样？按理说受影响的行数是1。看看官方文档。使用ONDUPLICATEKEYUPDATE，如果该行作为新行插入，则每行的受影响行值为1，如果更新现有行，则为2，如果现有行设置为其当前值，则为0官方明确表示插入影响1行，更新影响2行，0表示存在且更新前后的值相同。难懂吗？事实上，你必须这样想。这是为了区分是插入还是更新。返回1表示插入成功，返回2表示更新成功。解决方案将innodb_autoinc_lock_mode设置为0肯定可以解决问题，但是这样的话，插入的并发可能会受到很大的影响，所以小A想着DBA也不会答应。经过考虑，目前准备了两种可能的解决方案：修改业务逻辑修改业务逻辑，拆解INSERT...ONDUPLICATEKEYUPDATE...语句，先查询，再更新，这样可以保证主键它不会不受控制地增长，但会增加复杂性。原来的一个请求可能变成两个。先看看有没有，再更新。删除表的自增主键，删除自增主键，使用唯一索引作为主键。基本上不需要做任何改动。只要确保当前的自增主键没有实际用途即可。在这种情况下，它可能会影响插入和删除。效率，但对于查询较多的情况，小A对比两者后更愿意选择后者。结论其实INSERT...ONDUPLICATEKEYUPDATE...受影响的行数是2，小A早就发现了，只是没保持好奇心，没当回事。很多新知识出来了，很好。看来小A还是需要保持对外界的好奇和敏感，这样才能进步。
