Bulk Insert시 Auto Increment Table 이슈

 

MySQL에서 Bulk Insert(Load Data Infile, Insert into Select~ )을 사용해 대량으로 데이터를 삽입할 때, 종종 테이블의 AUTO_INCREMENT 값이 실제 삽입된 데이터보다 훨씬 커지는 현상이 발생합니다. 이는 의도된 동작이며, MySQL의 AUTO_INCREMENT 동작 방식과 관련이 있습니다.

 

문제 상황 예시

테이블에 auto_increment 컬럼이 존재 할때 Bulk Insert를 통해 데이터를 인서트할 경우 발생하게 된다.

 

원인: innodb_autoinc_lock_mode 설정

MySQL에서 AUTO_INCREMENT 값의 증가 방식은 innodb_autoinc_lock_mode 설정에 따라 다르게 동작합니다.

mode는 총 3개로 나뉘어져 있다.

0 (traditional) : 테이블 전체 잠금. 각 INSERT가 순차적으로 수행되어 ID가 정확히 맞지만 성능 저하

1 ( consecutive) : 기본값으로 INSERT INTO ... VALUES는 순차 삽입, LOAD DATA INFILE 등 대량 삽입은 인터리브처럼 동작 가능

2 (interleaved) : 병렬 실행 허용, 성능 좋음. 대신 AUTO_INCREMENT 값이 중간에 "튀는" 현상 발생 가능

 

MySQL 공식 문서 요약

When "bulk inserts" are executed, there may be gaps in the auto-increment values assigned by any given statement.

대량 삽입이 실행되면, 해당 문장에 의해 할당된 AUTO_INCREMENT 값에는 공백이 생길 수 있다.

 

 

이 현상이 발생하는 명령어들

다음과 같은 대량 삽입 계열 명령어는 모두 비슷한 AUTO_INCREMENT 튀는 현상을 유발할 수 있습니다.

• LOAD DATA INFILE
• INSERT INTO ... SELECT
• INSERT INTO ... VALUES (...), (...), (...) (멀티 로우 삽입)

 

 

나의 경우 아래 2가지를 통해서 인서트를 진행하였다. 

1. 서비스 중인 테이블이 아닌 경우 Bulk Insert 이후 Alter Table로 auto_increment 값 수정

2. 서비스 중이라면 Bulk Insert 사용하지 않고 Auto commit 비활성화, 단일 데이터 Insert, Commit 순으로 진행하였다.