auto_increment란?

데이터가 삽입 될 때마다 1씩 증가해주는 역할을 한다.

mysql> create table animals (
    -> id mediumint not null auto_increment,
    -> name char(30) not null,
    -> primary key (id));
Query OK, 0 rows affected (0.00 sec)

mysql> insert into animals (name) values
    ->  ('dog'),('cat'),('penguin'),('lax'),('whale'),('ostrich');
Query OK, 6 rows affected (0.00 sec)
Records: 6  Duplicates: 0  Warnings: 0

테이블 구조 생성시 칼럼에 보면 A_I로 된 열이 있는데 이 열이 바로 auto_increment이다.

체크 해주면 이 열이 A_I로 되어 데이터 삽입시에 하나씩 증가하게 된다.

위 테이블에서는 기본키로 쓰였다.

값을 초기화를 할 경우

ALTER TABLE 'table' AUTO_INCREMENT = (변경하고자 하는 인덱스);

기존에 있는 열을 재정렬 하는 경우

ALTER TABLE 'table' AUTO_INCREMENT = 1; 

SET @count = 0;

UPDATE 'table' SET 'table'.'column' = @count:=@count+1;

A_I의 문제점 고려 사항입니다.

Auto_Increment In InnoDB

Auto_Increment는 스토리지 엔진 별로 다르게 동작합니다. 파일 기반의 스토리지 엔진인 MyISAM 경우에는 현재 Auto_Increment값이 파일에 일일이 기록되는 방식으로 관리됩니다. 그러나 메모리 기반의 스토리지 엔진인 InnoDB에서는 조금 다른 방식으로 관리됩니다.

InnoDB에서는 MyISAM과는 다르게 Auto_Increment 값이 변경될 때마다 기록하지 않습니다. “메모리 상에서 Auto_Increment 값을 관리”하는 것이죠. DB가 처움 구동되면 다음과 같이 Auto_Increment 속성이 있는 테이블은 모두 초기화됩니다.

1	SELECT MAX(ai_col) FROM t for UPDATE

만약 결과 값이 NULL이면 Auto_Increment_Offset으로 대체되거나, 1로 초기화됩니다. 그리고 Auto_Increment_Increment만큼 증가되어 Auto_Increment 가 관리되는 것이죠. 이런 상황에서 어떤 문제가 발생할 수 있을까요?

Problem Case

인지하고 있어야 하는 부분은 바로 위에서 Auto_Increment값이 초기화되는 부분입니다. 각 테이블의 Auto_Increment값을 최대값을 기준으로 초기화하기 때문에, 서버 재시작 시 올바른 Auto_Increment 값이 설정되지 않을 가능성이 있는 것입니다.

그렇다면 테스트를 해볼까요? 다음과 같이 테이블을 생성합니다.

1 2 3 4 5

CREATE TABLE `test` (   `i` int(11) NOT NULL AUTO_INCREMENT,   `j` char(1) DEFAULT NULL,   PRIMARY KEY (`i`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8

그리고 10 건의 데이터를 넣고, 현재 Auto_Increment 값을 확인해봅니다.

1 2 3 4 5 6 7 8 9 10

## 10건 데이터 Insert mysql> insert into test (j) values ('1');   ## 테이블 스키마 조회 mysql> show create table test\G CREATE TABLE `test` (   `i` int(11) NOT NULL AUTO_INCREMENT,   `j` char(1) DEFAULT NULL,   PRIMARY KEY (`i`) ) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8

이 상황에서 모든 데이터를 지우고 다시 한번 Auto_Increment값을 확인해봅니다.

1 2 3 4 5 6 7 8 9 10

mysql> delete from test; Query OK, 10 rows affected (0.00 sec)   ## 테이블 스키마 mysql> show create table test\G CREATE TABLE `test` (   `i` int(11) NOT NULL AUTO_INCREMENT,   `j` char(1) DEFAULT NULL,   PRIMARY KEY (`i`) ) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8

여전히 Auto_Increment 값은 11로 변동이 없습니다.

그렇다면 여기서 DB를 재시작 후 확인해보면 어떨까요? DB를 재시작 후 다시 한번 스키마를 확인해 봅니다.

1 2 3 4 5

CREATE TABLE `test` (   `i` int(11) NOT NULL AUTO_INCREMENT,   `j` char(1) DEFAULT NULL,   PRIMARY KEY (`i`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8

분명 11로 설정되어 있어야할 값이 마치 테이블이 처음 생성된 것처럼 조회가 됩니다. 이 상태에서 한 건의 데이터를 넣고 다시 한번 테이블 스키마를 확인해 봅니다.

1 2 3 4 5 6 7 8

mysql> insert into test (j) values ('1');   mysql> show create table test\G CREATE TABLE `test` (   `i` int(11) NOT NULL AUTO_INCREMENT,   `j` char(1) DEFAULT NULL,   PRIMARY KEY (`i`) ) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8

Auto_Increment 값이 11에서 2로 변경되는 어이없는 현상이 발생했습니다. 이 같은 현상은 파일 기반 스토리지 엔진인 MyISAM에서는 발생하지 않습니다. 비록 Delete가 된다고 하더라도 그 값은 디스크에 기록을 하기 때문이죠.

Conclusion

MyISAM테이블을 성능 및 안정성 이슈로 InnoDB로 전환 후 서버 재시작 시 매번 Primary Key 중복 오류가 발생한 사례가 있습니다. 결과적으로 Delete 스케줄링이 문제가 되었고, 관련 로직을 제거함으로써 해결하게 되었죠. Auto_Increment의 가장 최근 데이터를 삭제 처리하는 로직만 없다면 아~무런 문제가 없습니다.

InnoDB에서 Auto_Increment를 사용하고 있다면 이와 같은 특성을 반드시 이해하고 예기치 않는 장애 사항을 사전에 예방하시기 바랍니다. ^^

- 출처 : http://gywn.net/2013/02/mysql-innodb-auto-increment/