[MySQL] INSERT ...ON DUPLIATE UPDATE 시 auto_increment 가 불필요하게 증가하는 이슈.
[MySQL] INSERT IGNORE 시 auto_increment 가 불필요하게 증가하는 이슈.
출처
http://stackoverflow.com/questions/7087869/mysql-insert-on-duplicate-update-adds-one-to-the-autoincrement/7100901#7100901
상황
mysql 에서만 존재하는 INSERT IGNORE & INSERT … ON DUPLICATE UPDATE 문법을 사용하여 상품 데이터를 INSERT/UPDATE 하고 있는데 auto_increment로 지정된 PK값이 실제로 insert되는 rows에 비해 비정상적으로 증가하는 이슈가 있었다.
정확히는 이슈라기 보다, 좀 거슬리는 것에 가깝긴 했지만 어쨌든 해결하는 게 좋을 듯 했다.
원인 분석
INSERT … ON DUPLICATE KEY UPDATE 는 mixed-mode insert 라고 하여, 기본적으로 InnoDB auto_increment 은 증가할 수 있는 최대값 (다시 말해 last_id + 최대로 insert 될수 있는 값)으로 업데이트 된다고 한다.
MySQL에서는 아래와 같이, InnoDB에서는 실제로 insert 될 값보다 더 크게 auto_increment값을 잡아놓는다고 한다. 그리고 실제로 insert 된 값보다 커진 auto_increment 에 해당하는 숫자는 실질적으로 버려지는 것이다.
...for “mixed-mode inserts”... InnoDB will allocate more auto-increment values than the number of rows to be inserted. However, all values automatically assigned are consecutively generated (and thus higher than) the auto-increment value generated by the most recently executed previous statement. “Excess” numbers are lost.
이에 대한 해결책
INSERT … ON DUPLICATE KEY UPDATE 를 쓰지 않는다.
InnoDB 의 innodbautoinclock_mode 값을 0으로 변경한다.
Set the innodb_autoinc_lock_mode parameter to 0, for "traditional" autoincrement lock mode, which guarantees that all INSERT statements will assign consecutive values for AUTO_INCREMENT columns. However, this is accomplished by locking during the statement, so there's a performance loss associated with this setting.
AUTO_INCREMENT 컬럼에서의 차이 자체를 무시하면 된다. (증가한 값 그냥 무시하면 될지도…)
메뉴얼에는?
http://dev.mysql.com/doc/refman/5.1/en/innodb-auto-increment-handling.html
innodbautoinclock_mode = 0 (“traditional” lock mode) 으로 설정
lock mode 변경을 통해 미리 auto_increment 값을 계산하지 않고, 전부 수행될 때 까지 락을 잡고 있도록 한다. Performance loss가 있을 수 있다. auto_increment값에 대한 lock을 쿼리 수행 시간동안 잡고 있기 때문이다.
내 경우
batch job 이 시간당 수십 개씩 돌고 있기 때문에 무시할 수 있는 범위는 아니라서 InnoDB 설정을 변경하였다.
MySQL이 아닌 다른 DBMS로 갈아탈 때는 저 문법이 문제될 수가 있으므로 아예 사용하지 않고 NOT EXIST 나 다른 식으로 처리하는 게 훨씬 나을 수도 있다.