MySQL使用on duplicate key update引起主键不连续自增

亦凉 2023-10-18 09:38 72阅读 0赞

项目中需要记录用户对资源的访问次数，实时写入数据库中。性能测试时数据量比较大，每次有10W次的访问次数，有update，也有insert。

为了效率用到了on duplicate key update进行自动判断是更新还是新增（MySQL判断记录是否存在的依据是主键或者唯一索引，insert在主键或者唯一索引已经存在的情况下会插入失败，而InsertOrUpdate在主键或者唯一索引已经存在的情况下就变成了根据主键或唯一索引update的操作）。一段时间后发现该表的主键id(已设置为连续自增)，不是连续的自增,总是跳跃的增加，这样就造成id自增过快，已经快超过最大值了，通过查找资料发现on duplicate key update有一个特性就是每次是更新的情况下id也是会自增加1的，比如说现在id最大值的5，然后进行了一次更新操作再进行一次插入操作时，id的值就变成了7而不是6。

<insert id="insertorUpdateCount" parameterType="map">
    	 	insert into call_count (chapterId, contentId, platform, count, updateTime)
    		values(#{chapterid,jdbcType=VARCHAR}, #{contentid,jdbcType=VARCHAR}, #{platform,jdbcType=VARCHAR}, 
    		     #{count,jdbcType=INTEGER}, #{updatetime,jdbcType=TIMESTAMP}) 
    		
    		ON DUPLICATE KEY UPDATE 
    		chapterId=VALUES(chapterId),contentId=VALUES(contentId),platform=VALUES(platform),count=count+1
    	 </insert>

解决这个问题，有两种方式：

第一种：拆分成两个动作，先更新，更新无效再插入（使用）  
1、根据唯一索引来更新表

<update id="updateCount" parameterType="map">
    		update call_count
    		   set  count=count+1
    		where chapterId = #{chapterid,jdbcType=VARCHAR} 
    		and contentId = #{contentid,jdbcType=VARCHAR} 
    		and platform = #{platform,jdbcType=VARCHAR}    
    	</update>

2、根据上一步的返回值，如果返回值大于0，说明更新成功不再需要插入数据，如果返回值不大于0则需要进行插入该条数据

<insert id="insertCount" parameterType="map">
    	    <selectKey keyProperty="id" order="AFTER" resultType="java.lang.Integer">
          		SELECT LAST_INSERT_ID()
        	</selectKey>
    		insert into call_count (chapterId, contentId, platform, count, updateTime)
    		values(#{chapterid,jdbcType=VARCHAR}, #{contentid,jdbcType=VARCHAR}, #{platform,jdbcType=VARCHAR}, 
    		     #{count,jdbcType=INTEGER}, #{updatetime,jdbcType=TIMESTAMP}) 
    	</insert>

第二种：修改innodb\_autoinc\_lock\_mode参数（未使用）  
  innodb\_autoinc\_lock\_mode中有3种模式,0,1,2，数据库默认是1的情况下，就会发生上面的那种现象,每次使用insert into … on duplicate key update 的时候都会把简单自增id增加,不管是发生了insert还是update  
innodb\_autoinc\_lock\_mode参数详解  
tradition(innodb\_autoinc\_lock\_mode=0) 模式：  
1、它提供了一个向后兼容的能力  
2、在这一模式下，所有的insert语句(“insert like”) 都要在语句开始的时候得到一个表级的auto\_inc锁，在语句结束的时候才释放这把锁，注意呀，这里说的是语句级而不是事务级的，一个事务可能包涵有一个或多个语句。  
3、它能保证值分配的可预见性，与连续性，可重复性，这个也就保证了insert语句在复制到slave的时候还能生成和master那边一样的值(它保证了基于语句复制的安全)。  
4、由于在这种模式下auto\_inc锁一直要保持到语句的结束，所以这个就**影响到了并发的插入**。

consecutive(innodb\_autoinc\_lock\_mode=1) 模式：  
1、这一模式下去simple insert 做了优化，由于simple insert一次性插入值的个数可以立马得到确定，所以mysql可以一次生成几个连续的值，用于这个insert语句；总的来说这个对复制也是安全的(它保证了基于语句复制的安全)  
2、**这一模式也是mysql的默认模式**，这个模式的好处是auto\_inc锁不要一直保持到语句的结束，只要语句得到了相应的值后就可以提前释放锁

interleaved(innodb\_autoinc\_lock\_mode=2) 模式：  
1、由于这个模式下已经没有了auto\_inc锁，所以这个模式下的性能是最好的；但是它也有一个问题，就是对于同一个语句来说它所得到的auto\_incremant值可能不是连续的。

由于用户访问量较大，0模式虽然只有实际的发生insert的时候才增加，但是每次都会在语句执行期间锁表，并发性不太好，所以最终选择使用第一种解决方案，把更新插入语句分开写。

推荐一篇mysql语句分析很到位的文章：[避坑必看：很详尽的MyBatis返回自增主键实验（包括插入或更新SQL语句insert on duplicate key update的自增主键返回情况）][MyBatis_SQL_insert on duplicate key update]

[MyBatis_SQL_insert on duplicate key update]: https://blog.csdn.net/qq_27680317/article/details/81118070