MySQL 자동 증가 ID의 조각화란?
데이터베이스 테이블의 레코드에 고유한 ID를 부여할 때, 번호를 다시 매기는 과정에서 여러 가지 문제가 발생할 수 있습니다. 특히 MySQL에서 자동 증가(AUTO_INCREMENT) ID는 특정 조건에 따라 값이 재사용되거나 중간에 빈 공간이 생기는데, 이를 ID 조각화(fragmentation)라고 합니다.
AUTO_INCREMENT ID가 재사용될 수 있는 상황
MySQL 5.6 버전의 InnoDB 스토리지 엔진에서는 INSERT 문에 ID 열을 직접 포함하고 원하는 특정 값을 지정하면 auto_increment ID를 재사용할 수 있습니다. 다음과 같은 상황에서 ID 재사용과 조각화 문제가 발생할 수 있습니다.
- 가장 큰 번호를 가진 ID를 삭제하는 경우
- MySQL 서버를 시작하거나 중지하는 경우
- 새로운 레코드를 삽입하는 경우
예제: auto_increment 변수를 활용한 ID 자동 증가
먼저 auto_increment 속성을 적용한 테이블을 생성합니다.
mysql> create table UniqueAutoId -> ( -> id int auto_increment, -> Unique(id) -> ); Query OK, 0 rows affected (0.45 sec)
생성된 테이블에 레코드를 차례대로 삽입합니다.
mysql> insert into UniqueAutoId values(); Query OK, 1 row affected (0.13 sec) mysql> insert into UniqueAutoId values(); Query OK, 1 row affected (0.16 sec) mysql> insert into UniqueAutoId values(); Query OK, 1 row affected (0.07 sec) mysql> insert into UniqueAutoId values(); Query OK, 1 row affected (0.10 sec) mysql> insert into UniqueAutoId values(); Query OK, 1 row affected (0.10 sec)
전체 레코드를 조회하면 ID가 1부터 5까지 순차적으로 자동 증가한 것을 확인할 수 있습니다.
mysql> select *from UniqueAutoId;
실행 결과는 다음과 같습니다.
+----+ | id | +----+ | 1 | | 2 | | 3 | | 4 | | 5 | +----+ 5 rows in set (0.00 sec)
레코드 삭제 후 ID 조각화 확인하기
DELETE 문을 사용하면 레코드를 삭제할 수 있습니다. 여기서는 id=5인 레코드를 삭제해 보겠습니다.
mysql> DELETE from UniqueAutoId where id=5; Query OK, 1 row affected (0.14 sec)
레코드를 다시 조회합니다.
mysql> select *from UniqueAutoId;
실행 결과는 다음과 같습니다.
+----+ | id | +----+ | 1 | | 2 | | 3 | | 4 | +----+ 4 rows in set (0.00 sec)
이번에는 테이블에서 id=2인 레코드를 한 번 더 삭제합니다.
mysql> delete from UniqueAutoId where id=2; Query OK, 1 row affected (0.15 sec)
다시 레코드를 조회해 보겠습니다.
mysql> select *from UniqueAutoId;
실행 결과는 다음과 같습니다.
+----+ | id | +----+ | 1 | | 3 | | 4 | +----+ 3 rows in set (0.00 sec)
결론: 조각화가 의미하는 것
위 실행 결과에서 볼 수 있듯이, 중간에 위치한 ID(2번)를 삭제하면 ID 시퀀스에 빈 공간이 생기는데, 이것이 바로 조각화(fragmentation)입니다. 이러한 조각화는 대용량 테이블에서 인덱스 성능 저하나 저장 공간 낭비로 이어질 수 있습니다. 따라서 주기적인 OPTIMIZE TABLE 실행이나 체계적인 ID 관리 전략을 통해 테이블 상태를 점검하고 관리하는 것이 좋습니다.