MySQL 自增 ID 超大问题查询

谁借莪１个温暖的怀抱￠ 2021-09-08 12:54 453阅读 0赞

### **引言**    ###

小A正在balabala写代码呢，DBA小B突然发来了一条消息，“快看看你的用户特定信息表T，里面的主键，也就是自增id，都到16亿了，这才多久，在这样下去过不了多久主键就要超出范围了，插入就会失败，balabala......”

我记得没有这么多，最多1k多万，count了下，果然是1100万。原来运维是通过 auto\_increment那个值看的，就是说，表中有大量的删除插入操作，但是我大部分情况都是更新的，怎么会这样？

###  ###

### **问题排查** ###

这张表是一个简单的接口服务在使用，每天大数据会统计一大批信息，然后推送给小A，小A将信息更新到数据库中，如果是新数据就插入，旧数据就更新之前的数据，对外接口就只有查询了。

很快，小A就排查了一遍自己的代码，没有删除的地方，也没有主动插入、更新id的地方，怎么会这样呢？难道是小B的原因，也不太可能，DBA那边儿管理很多表，有问题的话早爆出来了，但问题在我这里哪里也没头绪。

小A又仔细观察了这1000多万已有的数据，将插入时间、id作为主要观察字段，很快，发现了个问题，每天第一条插入的数据总是比前一天多1000多万，有时候递增的多，有时候递增的少，小A又将矛头指向了DBA小B，将问题又给小B描述了一遍。

小B问了小A，“你是是不是用了 REPLACE INTO...语句”，这是怎么回事呢，原来 REPLACE INTO...会对主键有影响。

##### **“REPLACE INTO ...”对主键的影响** #####

假设有一张表 t1：

![abeafdb4a96d72b0e6377c09a30d01f6.png][]

如果新建这张表，执行下面的语句，最后的数据记录如何呢？

![20ddc27771e23aa4ad4fb484f18593a1.png][]

![da03eddcd55cbf743f6653b759e5196e.png][]

原来， REPLACE INTO...每次插入的时候如果唯一索引对应的数据已经存在，会删除原数据，然后重新插入新的数据，这也就导致id会增大，但实际预期可能是更新那条数据。

小A说：“我知道replace是这样，所有既没有用它”，但还是又排查了一遍，确实不是自己的问题，没有使用 REPLACE INTO...。

小A又双叒叕仔细的排查了一遍，还是没发现问题，就让小B查下binlog日志，看看是不是有什么奇怪的地方，查了之后还是没发现问题，确实存在跳跃的情况，但并没有实质性的问题。

下图中 @1的值对应的是自增主键 id，用 (@2,@3)作为唯一索引：

![61a00fcf4a7207e86d9c2cc6d390d1eb.png][]

后来过了很久，小B给小A指了个方向，小A开始怀疑自己的插入更新语句

INSERT...ON DUPLICATE KEY UPDATE...了，查了许久，果然是这里除了问题。

##### **“INSERT ... ON DUPLICATE KEY UPDATE ...”对主键的影响** #####

这个语句跟 REPLACE INTO...类似，不过他并不会变更该条记录的主键，还是上面 t1这张表，我们执行下面的语句，执行完结果是什么呢？

![196f749d69fd91677d12bb2b3965bc86.png][]

![cc360e69c82517f35b4eb105f547372a.png][]

没错，跟小A预想的一样，主键并没有增加，而且 name字段已经更新为想要的了，但是执行结果有条提示，引起了小A的注意：

> No errors; 2 rows affected, taking 10.7ms

明明更新了一条数据，为什么这里的影响记录条数是2呢？小A，又看了下目前表中的 auto\_increment：

![148dc3b28bfbd97d231d587e8d4b86f9.png][]

竟然是5\`，这里本应该是4的。

也就是说，上面的语句，会跟 REPLACE INTO...类似的会将自增ID加1，但实际记录没有加，这是为什么呢?

查了资料之后，小A得知，原来，mysql主键自增有个参数 innodb\_autoinc\_lock\_mode，他有三种可能只 0, 1, 2，mysql5.1之后加入的，默认值是 1，之前的版本可以看做都是 0。

可以使用下面的语句看当前是哪种模式：

1.  `select @@innodb_autoinc_lock_mode;`

小A使用的数据库默认值也是1，当做简单插入（可以确定插入行数）的时候，直接将auto\_increment加1，而不会去锁表，这也就提高了性能。当插入的语句类似insert into select ...这种复杂语句的时候，提前不知道插入的行数，这个时候就要要锁表（一个名为AUTO\_INC的特殊表锁）了，这样auto\_increment才是准确的，等待语句结束的时候才释放锁。

还有一种称为Mixed-mode inserts的插入，比如INSERT INTO t1 (c1,c2) VALUES (1,'a'), (NULL,'b'), (5,'c'), (NULL,'d')，其中一部分明确指定了自增主键值，一部分未指定，还有我们这里讨论的INSERT ... ON DUPLICATE KEY UPDATE ...也属于这种，这个时候会分析语句，然后按尽可能多的情况去分配auto\_incrementid，这个要怎么理解呢，我看下面这个例子：

![aea03a85de5436c91524b563fd4dc0b4.png][]

此时数据表下一个自增id是7：

deletefrom t1 where id in (2,3,4);

此时数据表只剩1，5，6了，自增id还是7：

![d731361a89bb0d3ec463a033ebb93492.png][]

这里的自增id是多少呢？

上面的例子执行完之后表的下一个自增id是10，你理解对了吗，因为最后一条执行的是一个 Mixed-mode inserts语句，innoDB会分析语句，然后分配三个id，此时下一个id就是10了，但分配的三个id并不一定都使用。此处\* @总是迟到\[zongshichidao\] \* 多谢指出，看官方文档理解错了。

模式 0的话就是不管什么情况都是加上表锁，等语句执行完成的时候在释放，如果真的添加了记录，将 auto\_increment加1。

至于模式 2，什么情况都不加 AUTO\_INC锁，存在安全问题，当 binlog格式设置为 Statement模式的时候，从库同步的时候，执行结果可能跟主库不一致，问题很大。因为可能有一个复杂插入，还在执行呢，另外一个插入就来了，恢复的时候是一条条来执行的，就不能重现这种并发问题，导致记录id可能对不上。

至此，id跳跃的问题算是分析完了，由于 innodb\_autoinc\_lock\_mode值是1， INSERT...ON DUPLICATE KEY UPDATE...是简单的语句，预先就可以计算出影响的行数，所以不管是否更新，这里都将 auto\_increment加1（多行的话大于1）。

如果将 innodb\_autoinc\_lock\_mode值改为 0，再次执行 INSERT...ON DUPLICATE KEY UPDATE...的话，你会发现 auto\_increment并没有增加，因为这种模式直接加了 AUTO\_INC锁，执行完语句的时候释放，发现没有增加行数的话，不会增加自增id的。

##### **“INSERT ... ON DUPLICATE KEY UPDATE ...”影响的行数是1为什么返回2？** #####

为什么会这样呢，按理说影响行数就是1啊，看看官方文档的说明：

> With ON DUPLICATE KEY UPDATE, the affected-rows value per row is 1 if the row is inserted as a new row, 2 if an existing row is updated, and 0 if an existing row is set to its current values

官方明确说明了，插入影响1行，更新影响2行，0的话就是存在且更新前后值一样。是不是很不好理解？

其实，你要这样想就好了，这是为了区分到底是插入了还是更新了，返回1表示插入成功，2表示更新成功。

###     ###

**解决方案**

将 innodb\_autoinc\_lock\_mode设置为0肯定可以解决问题，但这样的话，插入的并发性可能会受很大影响，因此小A自己想着DBA也不会同意。经过考虑，目前准备了两种较为可能的解决方案：

##### **修改业务逻辑** #####

修改业务逻辑，将 INSERT...ON DUPLICATE KEY UPDATE...语句拆开，先去查询，然后去更新，这样就可以保证主键不会不受控制的增大，但增加了复杂性，原来的一次请求可能变为两次，先查询有没有，然后去更新。

##### **删除表的自增主键** #####

删除自增主键，让唯一索引来做主键，这样子基本不用做什么变动，只要确定目前的自增主键没有实际的用处即可，这样的话，插入删除的时候可能会影响效率，但对于查询多的情况来说，小A比较两种之后更愿意选择后者。

###     ###

**结语**

其实 INSERT...ON DUPLICATE KEY UPDATE...这个影响行数是2的，小A很早就发现了，只是没有保持好奇心，不以为然罢了，没有深究其中的问题，这深究就起来会带出来一大串新知识，挺好，看来小A还是要对外界保持好奇心，保持敏感，这样才会有进步。