引言
小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:
CREATE TABLE `t1` ( `id`int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT 'ID,自增', `uid` bigint(20) unsigned NOT NULL DEFAULT '0' COMMENT '用户uid', `name` varchar(20) NOT NULL DEFAULT '' COMMENT '用户昵称', PRIMARY KEY (`id`), UNIQUE KEY `u_idx_uid` (`uid`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='测试replace into' ;
如果新建这张表,执行下面的语句,最后的数据记录如何呢?
insert into t1 values(NULL, 100, "test1"), (NULL, 101, "test2"); replace into t1 values(NULL, 100, "test3");
原来, REPLACE INTO...每次插入的时候如果唯一索引对应的数据已经存在,会删除原数据,然后重新插入新的数据,这也就导致id会增大,但实际预期可能是更新那条数据。
小A说:“我知道replace是这样,所有既没有用它”,但还是又排查了一遍,确实不是自己的问题,没有使用 REPLACE INTO...。
小A又双叒叕仔细的排查了一遍,还是没发现问题,就让小B查下binlog日志,看看是不是有什么奇怪的地方,查了之后还是没发现问题,确实存在跳跃的情况,但并没有实质性的问题。
下图中 @1的值对应的是自增主键 id,用 (@2,@3)作为唯一索引:
后来过了很久,小B给小A指了个方向,小A开始怀疑自己的插入更新语句 INSERT...ON DUPLICATE KEY UPDATE...了,查了许久,果然是这里除了问题。
“INSERT ... ON DUPLICATE KEY UPDATE ...”对主键的影响
这个语句跟 REPLACE INTO...类似,不过他并不会变更该条记录的主键,还是上面 t1这张表,我们执行下面的语句,执行完结果是什么呢?
insert into t1 values(NULL, 100, "test4") on duplicate key update name = values(name);
没错,跟小A预想的一样,主键并没有增加,而且 name字段已经更新为想要的了,但是执行结果有条提示,引起了小A的注意:
No errors; 2 rows affected, taking 10.7ms
明明更新了一条数据,为什么这里的影响记录条数是2呢?小A,又看了下目前表中的 auto_increment: