[MySQL] MySQL 5 vs 8 ( Auto_increment )

MySQL은 5.7과 8.0 간에 바뀐 기능들이 많이 있다 그중에 Auto_Increment도 포함되어있다.

 

 

■ MySQL 5 Auto_Increment 동작 방식

- InnoDB Table에 Auto_Increment 속성을 추가하게 되면, 해당 Count 값들은 메모리 기반으로 관리되게 된다. 

- 따라서 DB가 재기동되면 Insert 구문을 실행하는 시점에 InnoDB는 해당 Table의 Auto_increment Column의 MAX 값을 조회하게 되고 해당 값에 +1을 하여 Insert를 하게 된다.

- 때문에 Auto_increment의 값이 유니크하게 증가되지 않고 삭제 후 DB 재기 동시 삭제된 값이 재사용되는 문제가 발생한다.

 

 

■ MySQL 8 Auto_Increment 동작 방식

- MySQL 8.0 에서는 Auto_increment 값이 변경될 때마다 redo log에 매번 기록하고, 각 체크포인트를 관리하는 스토리지 엔진의 시스템 테이블에 해당 내용을 저장하게 되었습니다.

- 따라서 정상적인 종료 후 서버를 다시 시작하면 Data Dictionary 시스템 테이블에 저장된 가장 큰 자동 증가 값을 사용해 메모리의 Auto_Increment 카운터를 초기화된다. 된다.

 

 

■ 8.0 Auto_increment의 문제점

- 1,2 Insert 하고 Start Transaction후 3 Insert 이후 commit 하기 전에 DB 비정상 종료 후 다시 insert 하면 1,2,4 이렇게 들어가게 된다.

- MySQL 8.0에서는 Data Dictionary의 시스템 테이블에 저장된(MySQL 서버 리스타트 후에도 유지되는) 가장 큰 자동 증가 값을 사용해 메모리의 Auto_Increment 카운터를 초기화하므로 롤백된 id = 3 이후 값인 4가 할당되었습니다.

 

 

■ 테스트 ( 5.7 )

- Insert / Delete한후 DB가 재기동하게 되면 메모리에서 기존 Auto_increment의 Count값이 지워져 다시 id=1로 시작되는 것을 확인할 수 있다.

mysql> create table lee3(id int not null auto_increment primary key);
Query OK, 0 rows affected (0.01 sec)

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

mysql> delete from lee3;
Query OK, 6 rows affected (0.00 sec)

####Restart DB####

mysql> insert into lee3 values();
Query OK, 1 row affected (0.01 sec)

mysql> select * from lee3;
+----+
| id |
+----+
|  1 |
+----+
1 row in set (0.00 sec)

 

■ 테스트 ( 8.0 )

- Insert / Delete한후 DB가 재기동하였지만 메모리에서 지워졌지만  Data Dictionary 시스템 테이블에 저장된 가장 큰 자동 증가 값을 사용하여 정상적으로 5가 들어간 것을 볼 수 있다.

mysql> create table lee3(a int auto_increment primary key);
Query OK, 0 rows affected (0.01 sec)

mysql> insert into lee3 values();
Query OK, 1 row affected (0.01 sec)

mysql> insert into lee3 values();
Query OK, 1 row affected (0.01 sec)

mysql> insert into lee3 values();
Query OK, 1 row affected (0.01 sec)

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

mysql> select * from lee3;
+---+
| a |
+---+
| 1 |
| 2 |
| 3 |
| 4 |
+---+

mysql> delete from lee3;
Query OK, 4 rows affected (0.01 sec)

####Restart DB#####

mysql> insert into lee.lee3 values();
Query OK, 1 row affected (0.00 sec)

mysql> select * from lee.lee3;
+---+
| a |
+---+
| 5 |
+---+

 

■ 테스트 ( 8.0 ) 문제점

- 중간에 Start Transaction후 Insert / transaction을 끝내지 않고DB 재기동을 하였다. 

- Table을 조회해보면 Insert 했던 8이 보이지 않고 다음 Auto_increment을 수는 9가 되었다. 

mysql> select * from lee3;
+---+
| a |
+---+
| 6 |
| 7 |
+---+

mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)

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

mysql> select * from lee3;
+---+
| a |
+---+
| 6 |
| 7 |
| 8 |
+---+

###DB RESTART###

mysql> select * from lee3;
+---+
| a |
+---+
| 6 |
| 7 |
+---+
2 rows in set (0.00 sec)


mysql> show create table lee3;
+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                                     |
+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| lee3  | CREATE TABLE `lee3` (
  `a` int NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`a`)
) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |
+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)


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


mysql> select * from lee3;
+---+
| a |
+---+
| 6 |
| 7 |
| 9 |
+---+
3 rows in set (0.00 sec)

--> 8이 빠져있다.

 

 

■ 참고

https://medium.com/naver-cloud-platform/%EC%9D%B4%EB%A0%87%EA%B2%8C-%EC%82%AC%EC%9A%A9%ED%95%98%EC%84%B8%EC%9A%94-mysql-8-0-%EA%B0%9C%EB%B0%9C%EC%9E%90%EB%A5%BC-%EC%9C%84%ED%95%9C-%EC%8B%A0%EA%B7%9C-%EA%B8%B0%EB%8A%A5-%EC%82%B4%ED%8E%B4%EB%B3%B4%EA%B8%B0-1-innodb-d638a3e4fde9

[이렇게 사용하세요!] MySQL 8.0, 개발자를 위한 신규 기능 살펴보기! #1 InnoDB