id가 1, 2, 3으로 가다가 갑자기 7로 건너뛰는 걸 보고 "데이터가 사라졌나" 하고 놀라는 경우가 많습니다. 결론부터 말하면 auto_increment 값이 비는 것(gap)은 정상 동작이며, ID는 순번이 아니라 고유 식별자일 뿐입니다. 다만 왜 비는지를 알아야 불필요하게 갭을 메우려는 위험한 시도를 막을 수 있습니다.
갭이 생기는 주요 원인
| 원인 | 설명 |
|---|---|
| 트랜잭션 롤백 | ID는 미리 할당되고, 롤백돼도 그 번호는 반환되지 않음 |
INSERT 실패 | 유니크 충돌 등으로 실패해도 카운터는 이미 증가 |
| 행 삭제 | 삭제해도 카운터는 되돌아가지 않음 |
| 대량 삽입 모드 | INSERT ... SELECT 등에서 번호를 블록 단위로 미리 확보 |
innodb_autoinc_lock_mode | 값이 2(interleaved)면 동시 삽입 시 번호가 섞이며 비는 폭이 커짐 |
핵심은 카운터가 단조 증가만 하고 절대 뒤로 가지 않는다는 것입니다. 한 번 발급된 번호는 쓰이지 않아도 회수되지 않습니다.
직접 확인해 보기
롤백이 갭을 만드는 걸 재현해 봅니다.
SQL
CREATE TABLE t (id INT PRIMARY KEY AUTO_INCREMENT, v INT);
INSERT INTO t (v) VALUES (10); -- id = 1
START TRANSACTION;
INSERT INTO t (v) VALUES (20); -- id = 2 발급됨
ROLLBACK; -- 행은 사라지지만 2는 소비됨
INSERT INTO t (v) VALUES (30); -- id = 3, 2는 영영 빈다
현재 카운터 값은 다음으로 확인합니다.
SQL
SHOW TABLE STATUS LIKE 't'; -- Auto_increment 컬럼이 다음 발급값
MySQL 8.0부터는 이 카운터가 redo 로그에 기록돼, 서버 재시작 후에도 값이 유지됩니다. 8.0 이전에는 재시작 시 MAX(id)+1로 다시 계산돼, 최근 삭제된 꼬리 번호가 재사용되는 차이가 있었습니다.
갭을 메우려 하지 말 것
- 번호를 다시 매기지 마세요.
UPDATE로 ID를 당기면 이 ID를 참조하는 외래 키·캐시·외부 시스템이 모두 깨집니다. ALTER TABLE ... AUTO_INCREMENT = N은 낮추지 마세요. 카운터를 기존 최대값 아래로 내리면 다음 삽입에서 중복 충돌이 납니다.- 순번이 필요하면 조회 시 만드세요. 화면용 연번은
ROW_NUMBER() OVER (ORDER BY id)로 조회 시점에 부여하면 됩니다. lock_mode를 바꾸려면 복제·성능을 함께 보세요. 갭을 줄이려0/1로 바꾸면 동시 삽입 처리량이 떨어질 수 있습니다.
요점 정리
auto_increment갭은 롤백·실패·삭제·동시성 때문에 생기는 정상 동작입니다.- 카운터는 단조 증가하며 발급된 번호는 회수되지 않습니다.
- ID는 식별자일 뿐, 연속성·개수의 근거로 쓰면 안 됩니다.
- 갭을 메우려는 재번호·카운터 강제 조정은 무결성을 깨는 위험한 행위입니다.
트랜잭션과 InnoDB 동작을 직접 재현하며 이해하는 실습은 데이터베이스 트랙에서 회원가입 없이 무료로 할 수 있습니다.