MySQL自增id超大题目查询

弁言

小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

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 AUTO_INCREMENT=5 DEFAULT CHARSET=utf8 COMMENT='测试replace into'; 

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

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

查了资料之后，小A得知，，原本，mysql主键自增有个参数innodb_autoinc_lock_mode，他有三种也许只0,1,2，mysql5.1之后插手的，默认值是1，之前的版本可以看做都是0。

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

select @@innodb_autoinc_lock_mode; 

（编辑：湖南网）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!