on duplicate key update導致主鍵不連續自增解決方案

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>

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