工作中负责开发的一个准实时的异步写入系统,需要每天将用户的听歌记录写入 数据库 中,

写入量比较大,每天一个表的写入量大概有5000万次左右,有update,也有insert.

数据库 用的是percona的 MySQL ,上线后一直运行挺好,基本上都是实时的,但是突然有一天发现一个统计用户听歌次数的表数据不更新了,

update和insert 都不起作用了,非常的诡异,后来运维show create table的时候

发现表的数据引擎是这样的

ENGINE=InnoDB AUTO_INCREMENT=2147483647 DEFAULT CHARSET=utf8 ROW_FORMAT=COMPACT 

auto_increament 已经达到极限了.怎么会这样呢?

设计表结构时,为了innodb的插入性能,表中使用了一个id自增主键,步长是2,表本身也有一个uniq的索引:key1

当前表中的数据大概有5000w条,相邻的2条的自增主键间隔并不是2,而是有很多的空洞,一直到目前AUTO_INCREMENT值已经达到极限,因此出现了这个情况.

赶紧和开发人员查了一下代码中的sql语句,发现所有的insert和update都使用了这条语句

insert into table1 (key1,key2,key3) values (x,x,x) on duplicated key update key2 = key2+1 ,key3=key3+1;

但是为什么这条语句会使数据之间产生这么大的空洞呢?

手动试验了一下,

比如说当前表中有一个key1=1 的记录,运行这条语句

insert into table1 (key1,key2,key3) values (1,1,1) on duplicate key update key2 = key2+1 ,key3=key3+1

的时候,每次都是update,运行10次后,在运行一条

insert into table1 (key1,key2,key3) values (2,1,1) on duplicate key update key2 = key2+1 ,key3=key3+1

假设当前表中没有key1=2的数据,上面这条语句就会插入一条,这时候会发现插入的数据自增id并不是比key1大2,

而是我之前的每次update都把AUTO_INCREMENT增大了,所以造成了这种数据空洞以及只有5000w条记录的时候

AUTO_INCREMENT值已经到极限了.

后来翻查了一下 mysql 的手册,发现有这么一个配置

innodb_autoinc_lock_mode

这个有3中模式,0,1和2,mysql5的默认配置是1,

0是每次分配自增id的时候都会锁表.

1只有在bulk insert的时候才会锁表,简单insert的时候只会使用一个light-weight mutex,比0的并发性能高

2.没有仔细看,好像是很多的不保证...不太安全.

数据库默认是1的情况下,就会发生上面的那种现象,每次使用insert into .. on duplicate key update 的时候都会把简单自增id增加,不管是发生了insert还是update

而改为0后,就解决了这个问题,只有实际的发生insert的时候才增加,但是每次都会锁表,并发性不太好.

目前线上先是简单的把这个配置改成了0,观察了一下,高峰期的时候更新数据还是很实时的,没什么太大问题.

后序可能还要持续观察性能问题~~

以上所述就是小编给大家介绍的《mysql ON DUPLICATE KEY UPDATE 引起自增ID变化的解决办法》，希望对大家有所帮助，如果大家有任何疑问请给我留言，小编会及时回复大家的。在此也非常感谢大家对 码农网 的支持！

例如，如果列a被定义为unique，并且值为1，则下列语句有同样的效果，也就是说一旦出入的记录中存在a=1的情况，直接更新c = c + 1，而不执行c = 3的操作。 代码如下: insert into table(a, b, c) values (1, 2, 3) on dupli cat e key update c = c + 1;1 update table set c = c + 1 where a = 1; 另外值得一提的是，这个语句知识 mysql 中，而标准sql语句中是没有的。 INSERT INTO .. ON DUPLI CAT E KEY 更新多行记录 如果在 INSERT 语句末 今天查看数据库时，发现 id 设置了auto_increment，但是，数据库中 id 是不连续。正好趁着这个机会总结一下 mysql 常用的插入语句（ insert into、 insert ignore into、 insert into … on dupli cat e key update ) mysql 版本：5.7python版本：3.7 问题 描述： mysql 表A中数据有几十条，设置了一个 自增 1 的 id 作为主键，在程序运行一段时间后发现表中最后一条数据的 id 已经是几百万了表中所有数据如下：可以看到最大的一条 id 已经是四百多万了，并且 id 是不连续的操作表的sql语句为：sql = " INSERT INTO table A(xx, xx, xx, xx) VALUES('{xx}', ... CREATE TABLE `test_ dupli cat e_ key ` ( ` id ` int(11) NOT NULL AUTO_INCREMENT COMMENT '主键 id ', `name` varchar(255) DEFAULT NULL, `age` int(11) , PRIMARY KEY (` id `), UNIQUE KEY `name_ id x` (`name`) ) ENGINE=InnoDB; select * from test_ dupli cat e_ key 有业务需求是 如果存在就更新，如果不存在就新增 之前用的 django orm中自带的 update _or_create 发现操作1900条数据的时候将近两分钟，太耗时了，然后翻了翻 mysql 的书籍，发现了ON DUPLI CAT E KEY UPDATE 方法 1、该语句是基于唯一索引或主键使用，需要建立unique index 索引 2、更新操作必须是在已有的数据基础上才会被执行，如果要更新的字段是主键或者唯一索引，不能和表中已有的数据重复，否则插入更新都失败。 3、不管是更新还是增加等操作，都不 最近项目上需要实现这么一个功能:统计每个人每个软件的使用时长,客户端发过来消息,如果该用户该软件已经存在增更新使用时间,如果没有则新添加一条记录,代码如下: insert into app_table(userName,app,duration)values(#{userName},#{item.app},#{item.duration})on dupli cat e key update durati... 使用唯一索引，不存在则插入，存在则更新 INSERT INTO tb_addrbook(num,name,mobile) VALUE('1001','小李','13112345678') ON DUPLI CAT E KEY UPDATE name= '小李',mobile='13112345678' 但是，大家可能会发现，这个表如果是有设置 自增 ID 的话，这个 自增 ID 并不会按正常的记录增加而加1增长，而是会跳跃增长，增长跨度和SQL的执行次数成正比。当然， 自增 ID 在许多业务中只是作为一个记录唯一性标识而已，跳 基本用法：on d update key update 语句基本功能是：当表中没有原来记录时，就插入，有的话就更新。 1，on dupli cat e key update 语句根据主键 id 或唯一键来判断当前插入是否已存在。2，记录已存在时，只会更新on dupli cat e key update 之后指定的字段。3，如果同时传递了主键和唯一键，以主键为判断存在依据，唯一键字段内容可以被修改。4，特殊用法：参考第五、第六条。 一，构造测试数据 注意里面的唯一键 drop table if exist MySQL INSERT … ON DUPLI CAT E KEY UPDATE …对 自增 主键的影响 生产告警，数据库 自增 主键(int无符号)增长很快，溢出程序中Integer类型的最大值。 org.springframework.dao.DataIntegrityViolationException: Error attempting to get column 'match_ id ' from result set. Cause: com. mysql .jdbc.exceptions.jdbc4.MyS 原标题：《 MySQL 自增 ID 》告诉你不为人知的“秘密”......作者：Sunshine Koo1.概述“ MySQL 数据库是最常使用的数据库之一，我们经常需要用到它的 自增 ID 来标识记录。在 MySQL 中，可通过数据列的auto_increment属性来自动生成。也可以在建表时可用“auto_increment=n”选项来指定一个 自增 的初始值。可用“alter table table_name aut... 在执行Replace后，系统返回了所影响的行数，如果返回1，说明在表中并没有重复的记录，如果返回2，说明有一条重复记录，系统自动先调用了Delete删除这条记录，然后再记录用 Insert 来插入这条记录。使用Replace插入一条记录时，如果不重复，Replace就和 Insert 的功能一样，如果有重复记录，Replace就使用新记录的值来替换原来的记录值。场景大概是这样的，业务方的需求是查询一条语句在不在，如果在就给出一个 update 语句，更新这条记录，如果不在，就给出一个 insert 语句，插入这条记录。