ON DUPLICATE KEY UPDATE를 쓴 뒤 AUTO_INCREMENT에 구멍이 계속 생긴 이유

문제 발생

콘텐츠를 slug 기준으로 업서트하는 import 스크립트를 매일 돌렸는데, 새로 들어온 행이 없는데도 id가 수백씩 건너뛰었습니다.

INSERT INTO notes (slug, title) VALUES ('a', 'A')
ON DUPLICATE KEY UPDATE title = VALUES(title);

그리고 "몇 건이 바뀌었나"를 affected rows로 세고 있었는데 숫자가 실제 변경 건수와 맞지 않았습니다.

원인 분석

AUTO_INCREMENT 소비. INSERT ... ON DUPLICATE KEY UPDATE는 충돌해서 UPDATE로 처리되더라도 AUTO_INCREMENT 값을 이미 소비합니다. 일반 UPDATE는 소비하지 않습니다. 그래서 업서트를 반복하면 실제 행 수와 무관하게 id가 계속 앞으로 나갑니다. 기능상 문제는 아니지만 "id가 곧 건수"라고 가정한 코드나 대시보드는 틀리게 됩니다.

affected rows의 세 가지 값. 이 값은 "변경된 행 수"가 아닙니다.

UPDATE가 2로 세어지므로 그냥 합산하면 실제보다 부풀려집니다.

유니크 인덱스가 둘 이상일 때. 공식 문서가 명시적으로 경고하는 부분입니다. 두 개 이상의 유니크 인덱스가 있는 테이블에서 여러 행이 동시에 충돌하면 그중 한 행만 갱신되고, 어느 행인지는 보장되지 않습니다. 결과가 예측 불가능해지므로 이 조합 자체를 피해야 합니다.

해결 방안

1. VALUES() 대신 행 별칭을 씁니다. MySQL에서 VALUES() 함수는 deprecated이고 향후 제거될 수 있습니다.

INSERT INTO notes (slug, title) VALUES ('a', 'A') AS new
ON DUPLICATE KEY UPDATE title = new.title;

2. 변경 건수는 별도로 셉니다. affected rows로 세지 말고, 무엇이 바뀌었는지 알아야 한다면 RETURNING(MariaDB)이나 갱신 전 조회로 판단합니다.
3. 유니크 인덱스는 하나만 두고 업서트합니다. 이 저장소의 노트 import가 slug 하나만 유니크로 두는 이유입니다. 충돌 기준이 하나면 위의 불확실성이 생기지 않습니다.
4. id 구멍이 문제가 되는 설계를 피합니다. AUTO_INCREMENT는 유일성을 보장할 뿐 연속성을 보장하지 않습니다. 이 저장소가 공개 URL에 id 대신 slug를 쓰는 이유도 같은 맥락입니다 — id를 노출하면 그 값에 의미가 있는 것처럼 읽히게 됩니다.
5. INSERT IGNORE와 섞지 않습니다. UPDATE 절이 만든 중복 키 오류까지 경고로 삼켜서, 실패를 성공처럼 보이게 만듭니다.
6. INSERT ... SELECT ... ON DUPLICATE KEY UPDATE는 문 기반 복제에서 안전하지 않습니다. SELECT의 행 순서에 결과가 의존하기 때문입니다. 복제를 쓴다면 행 기반 포맷인지 확인합니다.

공식 문서

• MySQL: INSERT ... ON DUPLICATE KEY UPDATE
• MariaDB: INSERT ON DUPLICATE KEY UPDATE
• MySQL: AUTO_INCREMENT 사용