on duplicate key update導緻主鍵不連續自增的問題

最近項目上需要實作這麼一個功能:統計每個人每個軟體的使用時長,用戶端發過來消息,如果該使用者該軟體已經存在增更新使用時間,如果沒有則新添加一條記錄,代碼如下:

<!-- 批量儲存軟體使用時長表 -->
    <update  id="saveApp"  parameterType="java.util.List">
  		<foreach collection="appList" item="item" index="index"  separator=";">
  			insert into app_table(userName,app,duration)
  			values(#{userName},#{item.app},#{item.duration})
  			on duplicate key update duration=duration+#{item.duration}
		</foreach>
  </update>
           

為了效率用到了on duplicate key update進行自動判斷是更新還是新增,一段時間後發現該表的主鍵id(已設定為連續自增),不是連續的自增,總是跳躍的增加,這樣就造成id自增過快,已經快超過最大值了,通過查找資料發現,on duplicate key update有一個特性就是,每次是更新的情況下id也是會自增加1的,比如說現在id最大值的5,然後進行了一次更新操作,再進行一次插入操作時,id的值就變成了7而不是6.

為了解決這個問題,有兩種方式,第一種是修改innodb_autoinc_lock_mode中的模式,第二種是将語句修拆分為更新和操作2個動作

第一種方式: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模式,隻能将資料庫代碼拆分成為更新和插入2個步驟,第一步先根據使用者名和軟體名更新使用時長,代碼如下:

<update id="updateApp" parameterType="App">
  	update app_table
  	set duration=duration+#{duration}
  	where userName=#{userName} and appName=#{appName}
  </update>
           

然後根據傳回值,如果傳回值大于0,說明更新成功不再需要插入資料,如果傳回值小于0則需要進行插入該條資料,代碼如下:

<insert id="saveApp" keyProperty = "id" useGeneratedKeys = "true"  parameterType="App">
  	insert into app_table(userName,appName,duration)
  	values(#{userName},#{appName},#{duration})
  </insert>
           

這樣解決效率上肯定為受到影響,不知道會不會丢資料,觀察一段時間再優化吧!