DB & AWS Knowledge
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
'MySQL > 기타 지식' 카테고리의 다른 글
Explain for connection 기능과 제한 사항 (0) | 2023.07.06 |
---|---|
MySQL및 MariaDB 에서의 max_open_files 및 open_files_limit 의 의의와 AWS RDS, Aurora Cluster 의 제한 사항 (0) | 2023.03.15 |
MySQL / MariaDB Replication -Slave 서버 데이터 기입 (0) | 2021.07.09 |
MySQL Community / MySQL 상용 Edition 차이 (0) | 2021.05.27 |
테이블 rename 시 동일 인덱스 적용 가능 유무 (0) | 2021.03.03 |