Auto_Increment 개념 및 버전에 따른 주의 사항

해당 페이지에서는 MySQL, MariaDB 에서 사용하는 Auto_Increment 개념 및 특정 버전을 사용 할 때 주의 해야 할 사항에 대해서 다룬다.

 

해당 내용은 아래의 공식 문서를 참조하여 기재한다.

 

https://dev.mysql.com/doc/refman/8.0/en/example-auto-increment.html

 

https://bugs.mysql.com/bug.php?id=199 

 

 

Auto_Increment 개념

 

MySQL 및 MariaDB 에서는 다른 DB engine 들과 다르게 시퀀스 개념이 존재하지 않는다.

그래서 이에 대한 대응책으로 두가지 방법을 사용 할 수 있다.

 

- Auto_Increment 기능 사용

 

- 시퀀스 같이 작동하는 프로시저를 생성 (구글링으로 검색시 많은 사례가 공유 되어 있음)

 

여기서 Auto_Increment 는 단어 뜻 그대로 자동으로 숫자를 메겨주는 기능이다.

그래서 자동으로 데이터에 숫자가 증가 되도록 메겨주는 시퀀스 대응으로 쓸 수 있는 것이다.

 

InnoDB Engine 내 Auto_Increment 사용 예시

 

아무래도 아래 예시와 같이 Auto_Increment 자체의 기능을 쓰는게 편하긴 하다.

컬럼에 이 속성만 지정하면 아래와 같이 자동으로 숫자를 기입 해 주기 때문이다.

 

[Document 내 Test 문]

CREATE TABLE animals ( id MEDIUMINT NOT NULL AUTO_INCREMENT, name CHAR(30) NOT NULL, PRIMARY KEY (id) ); 

INSERT INTO animals (name) VALUES ('dog'),('cat'),('penguin'), ('lax'),('whale'),('ostrich'); 

SELECT * FROM animals;

[결과]

+----+---------+
| id | name    |
+----+---------+
|  1 | dog     |
|  2 | cat     |
|  3 | penguin |
|  4 | lax     |
|  5 | whale   |
|  6 | ostrich |
+----+---------+

 

여기에 숫자를 명시해서 연속된 숫자를 건너 뛸 수도 있고, Not Null 컬럼속성에도 Null 을 사용하여 명시된 숫자의 다음숫자를 기입 할 수 있다. 

 

INSERT INTO animals (id,name) VALUES(100,'rabbit');
INSERT INTO animals (id,name) VALUES(NULL,'mouse');

SELECT * FROM animals;

+-----+-----------+
| id  | name      |
+-----+-----------+
|   1 | dog       |
|   2 | cat       |
|   3 | penguin   |
|   4 | lax       |
|   5 | whale     |
|   6 | ostrich   |
|   7 | groundhog |
|   8 | squirrel  |
| 100 | rabbit    |
| 101 | mouse     |
+-----+-----------+

 

당연히 0 부터 시작하는 데이터도 insert 할 수 있다.

 

INSERT INTO animals (id,name) VALUES(0,'groundhog');

 

또한,  시작하는 숫자도 바꿀 수 있다.

 

mysql> ALTER TABLE tbl AUTO_INCREMENT = 100;

 

 

MyISAM Engine 내 Auto_Increment 사용 예시

 

MyISAM Engine 에서는 아래와 같이 중간 컬럼에서 카테고리 별로 숫자를 매길 수도 있다.

 

[Document 내 Test 문]

CREATE TABLE animals (
    grp ENUM('fish','mammal','bird') NOT NULL,
    id MEDIUMINT NOT NULL AUTO_INCREMENT,
    name CHAR(30) NOT NULL,
    PRIMARY KEY (grp,id)
) ENGINE=MyISAM;

INSERT INTO animals (grp,name) VALUES
    ('mammal','dog'),('mammal','cat'),
    ('bird','penguin'),('fish','lax'),('mammal','whale'),
    ('bird','ostrich');

SELECT * FROM animals ORDER BY grp,id;

[결과]

+--------+----+---------+
| grp    | id | name    |
+--------+----+---------+
| fish   |  1 | lax     |
| mammal |  1 | dog     |
| mammal |  2 | cat     |
| mammal |  3 | whale   |
| bird   |  1 | penguin |
| bird   |  2 | ostrich |
+--------+----+---------+

 

 

DB 버전에 따른 Auto_Increment 사용시 주의 사항.

 

 

이렇게 단순하게 쓰면 Auto_Increment 를 사용하면 별문제가 되지 않으나 스토리지 엔진 및 DB 버전에 따라 주의사항이 있다.

 

테이블에서 무조건 insert 만 하는게 아니라 delete 등으로 기록되던 숫자가 지워 질 때 고려 해야 될 상황이 생기는데

auto_increment 가 사용 될 시, delete 가 되면 일반적으로 그 다음숫자가 기입되게 된다. 

 

[Bug Report 내 MySQL 5.7 Test]

mysql> create table a(id int unsigned not null primary key auto_increment) type=innodb;
Query OK, 0 rows affected (4.81 sec)

mysql> insert into a values(NULL);
Query OK, 1 row affected (0.00 sec)

mysql> insert into a values(NULL);
Query OK, 1 row affected (0.00 sec)

mysql> insert into a values(NULL);
Query OK, 1 row affected (0.00 sec)

mysql> select * from a;
+----+
| id |
+----+
|  1 |
|  2 |
|  3 |
+----+
3 rows in set (0.24 sec)

mysql> delete from a where id=3;
Query OK, 1 row affected (0.58 sec)

mysql> insert into a values(NULL);
Query OK, 1 row affected (0.00 sec)

mysql> select * from a;
+----+
| id |
+----+
|  1 |
|  2 |
|  4 |
+----+
3 rows in set (0.00 sec)

 

그러나 MySQL 5.7 버전의 InnoDB 스토리지 엔진을 기본으로 사용하면 아래 예시와 같이 서버

(혹은 클라우드 시스템에서 인스턴스) 가 재기동 되면 지워진 데이터의 숫자를 다시 사용하게 된다.

(MyISAM 에서는 이와 달리 예상대로 똑같이 다음 숫자가 기입된다.)

 

[위의 예시에서 계속 됨]

mysql> delete from a where id=4;
Query OK, 1 row affected (0.00 sec)

<이 단계에서 재기동 수행>

mysql> select * from a;
ERROR 2006: MySQL server has gone away
No connection. Trying to reconnect...
Connection id:    1
Current database: test

+----+
| id |
+----+
|  1 |
|  2 |
+----+
2 rows in set (1.12 sec)

mysql> insert into a values(NULL);
Query OK, 1 row affected (0.00 sec)

mysql> select * from a;
+----+
| id |
+----+
|  1 |
|  2 |
|  3 |
+----+
3 rows in set (0.00 sec)

 

그래서 이로 인하여 트랜잭션이 운영되다가 3부터 시작하는 현상으로 인하여 그 이후에 들어오는 데이터들이 사용자측에서 관리 목적등으로 따로 만든 시퀀스와 같이 엮이게 된다면 duplicate 되는 현상이 발생 할 수 있다. 

 

이는 MySQL 5.7 의 InnoDB 에서 마지막으로 사용된 값이 스토리지가 아닌 메모리에 저장되는 logic 으로 인하여 발생하는 버그다. 메모리 영역에선 재기동되면 보관했던 데이터들이 휘발되고, 이 때, DB engine 은 묵시적으로 값을 보관한 테이블에

 

InnoDB implicitly reset the value of auto_increment using internal query 'SELECT MAX(ai_col) FROM table_name FOR UPDATE;' 

 

를 수행하게 된다. 즉, 최신값이 3이라는 내용이 삭제되었으니 결국 위의 테이블에서 최신 max 값은 3으로 시작하게 만드는 것이다. 이 이슈는 InnoDB 엔진을 기본사상으로 사용하는 AWS RDS, Aurora Cluster 에서 발생할 가능성이 높다. 

 

 

이를 해결하는 방법은 아래와 같다.

 

- 이를 개선하여 메모리가 아닌 스토리지 내 redo log 로 최신값을 보관하는 logic 을 사용하는 MySQL 8.0 을 사용

 

- 재기동이 되었다면 아래의 쿼리를 수행

 

SET @CNT = 0; UPDATE (table) SET (table).(column) = @CNT:=@CNT+1;

 

- Auto_Increment 리셋

 

ALTER TABLE ... AUTO_INCREMENT = N