DATA 전문가로 가는 길

[Admin] MySQL/MariaDB AUTO_INCREMENT 본문

Data Architecture/Admin

[Admin] MySQL/MariaDB AUTO_INCREMENT

EstenPark 2020. 7. 6. 13:16

1. Auto increment 란?

테이블 컬럼에 auto_increment를 설정하게 되면, 데이터가 삽입 되는 순간 자동으로 증가 됩니다. 데이터 타입은 INT, BIGINT를 주로 많이 사용 합니다. InnoDB 엔진의 경우 Primary Key 생성과 동시에 인덱스 사이즈에 영향을 주기 때문에 테이블 설계시 많이 권고 하는 방법 입니다. MySQL 5.7 이전 버전에서는 MyISAM 엔진의 경우 auto_increment 값이 파일에 저장 해서 관리 되는 반면, InnoDB 인젠의 경우 메모리 기반으로 관리 됩니다. MySQL 8.0 에서는 InnoDB 엔진의 테이블 정보 저장 공간에 auto_increment 카운터 정보를 보관 합니다.

 

주로 많이 활용되는 방법은 PK + auto_increment를 결합해서 사용 합니다.(아래 샘플 예제)

 

CREATE TABLE `EST_INFO` (
  `USER_NO` int(11) NOT NULL AUTO_INCREMENT,
  `USER_ID` varchar(50) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
  `USER_NM` varchar(50) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
  PRIMARY KEY (`USER_NO`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
;

 

2. Persistent Auto Increment Value

 

auto_increment의 초기화 방식은 MySQL 8.0에서 변경 되었습니다. 자동 증가 값이 변경될 때마다 redo log에 매번 기록하고, 각 체크포인트를 관리하는 스토리지 엔진의 시스템 테이블에 해당 내용을 저장하게 되었습니다. 그동안 문제가 되었던 InnoDB 엔진의 auto_increment 삭제 후 재시작 할 경우에 문제가 된 부분도 해결 되었습니다. Data Dictionary 시스템 테이블에 저장된 가장 큰 자동 증가값을 사용해 메모리의 auto_increment 카운터를 초기화 합니다.

 

MySQL 5.7 및 이전 버전에서 롤백 직후 서버를 다시 시작하면 롤백 된 트랜잭션에 할당된 자동 증가 값을 재사용 하지만, MySQL 8.0에서는 현재 최대 자동 증가 값이 유지되므로 이전에 할당된 값을 재활용 할 수 없게 됩니다.

 

2.1. Auto Increment  카운트 점검(MySQL 8.0.17)

EST_INFO 테이블을 생성하고 AUTO_INCREMENT를 1로 설정 합니다.

mysql> CREATE TABLE `EST_INFO` (
  `USER_NO` int(11) NOT NULL AUTO_INCREMENT,
  `USER_ID` varchar(50) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
  `USER_NM` varchar(50) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
  PRIMARY KEY (`USER_NO`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
;

EST_INFO 테이블에 3 row를 생성 후 auto_increment 증가된 상황을 확인 합니다.

mysql> INSERT INTO EST_INFO(USER_ID, USER_NM) values('user01', 'estenpark01');
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO EST_INFO(USER_ID, USER_NM) values('user02', 'estenpark02');
Query OK, 1 row affected (0.01 sec)

mysql> INSERT INTO EST_INFO(USER_ID, USER_NM) values('user03', 'estenpark03');
Query OK, 1 row affected (0.01 sec)

mysql> SELECT  *  FROM EST_INFO;
+---------+---------+-------------+
| USER_NO | USER_ID | USER_NM     |
+---------+---------+-------------+
|       1 | user01  | estenpark01 |   >> auto_increment 1 증가
|       2 | user02  | estenpark02 |   >> auto_increment 2 증가
|       3 | user03  | estenpark03 |   >> auto_increment 3 증가
+---------+---------+-------------+

 테스트를 위해서 EST_INFO 테이블에 모든 데이터를 삭제 하고, DDL 결과를 확인 합니다.

mysql> DELETE FROM EST_INFO;
Query OK, 3 rows affected (0.01 sec)

mysql> SELECT  *  FROM EST_INFO;
Empty set (0.00 sec)

mysql> show create table EST_INFO;
+----------+-------------------------------------------------------------------------------+
| Table    | Create Table                                                                  |
+----------+-------------------------------------------------------------------------------+
| EST_INFO | CREATE TABLE `EST_INFO` (
  `USER_NO` int(11) NOT NULL AUTO_INCREMENT,
  `USER_ID` varchar(50) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
  `USER_NM` varchar(50) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
  PRIMARY KEY (`USER_NO`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |
+----------+-------------------------------------------------------------------------------+
1 row in set (0.00 sec)

MySQL 서비스를 재 시작 합니다.

root@localhost /]# systemctl stop mysqld;
[root@localhost /]# systemctl start mysqld;

EST_INFO 테이블에 2 row를 생성 하게 되면, 이전 버전과 다르게 4, 5로 증가된 것을 확인 할 수 있습니다.

mysql> INSERT INTO EST_INFO(USER_ID, USER_NM) values('user04', 'estenpark04');
Query OK, 1 row affected (0.02 sec)

mysql> INSERT INTO EST_INFO(USER_ID, USER_NM) values('user05', 'estenpark05');
Query OK, 1 row affected (0.00 sec)

mysql> SELECT  *  FROM EST_INFO;
+---------+---------+-------------+
| USER_NO | USER_ID | USER_NM     |
+---------+---------+-------------+
|       4 | user04  | estenpark04 |   >> auto_increment 4 증가(초기화 되지 않고 증가 됨)
|       5 | user05  | estenpark05 |   >> auto_increment 5 증가(초기화 되지 않고 증가 됨)
+---------+---------+-------------+

 

EST_INFO 테이블을 TRUNCATE(DDL 문법) 수행할 경우에는 초기화 된 결과가 나오게 됩니다..

mysql> TRUNCATE TABLE EST_INFO;
Query OK, 0 rows affected (0.04 sec)

mysql> INSERT INTO EST_INFO(USER_ID, USER_NM) values('user06', 'estenpark06');
Query OK, 1 row affected (0.00 sec)

mysql> select * from EST_INFO;
+---------+---------+-------------+
| USER_NO | USER_ID | USER_NM     |
+---------+---------+-------------+
|       1 | user06  | estenpark06 |   >> auto_increment 1 증가(초기화)
+---------+---------+-------------+

 

3. Auto Increment 결론

 

MySQL 5.7, MariaDB 10.1.12 버전은 데이터를 삭제하고, DB를 재시작 할 경우 초기화 된 정보가 나오게 되며, MySQL 8.0, MariaDB 10.3.8 버전은 데이터를 삭제하고, DB를 재 시작 한다고 해도 초기화 되지 않고 계속 증가하게 됩니다. 이러한 버전에 특성을 이해 하면 장애를 최소화 할 수 있습니다.

 

4. 참고

 

 

저작자표시 비영리 변경금지

'Data Architecture > Admin' 카테고리의 다른 글

[Admin] MySQL/MariaDB 대용량 데이터 마이그레이션 성능 비교(CTAS, mysqldump, INTO OUTFILE~LOAD DATA LOCAL INFILE)  (0) 2020.07.09
[Admin] MySQL/MariaDB Primary Key vs Unique Key  (0) 2020.07.02
[Admin] MySQL/MariaDB Clustered vs Non-Clustered Index  (0) 2020.07.01
[Admin] ORA-30556 함수 기반 인덱스, 컬럼(타입) 사이즈 변경 에러  (0) 2019.09.20
[Admin] Oralce 파라미터 설정 자동 가이드(audit_trail, processes, archive log directory, _diag_daemon, memory_target)  (1) 2019.08.16
'Data Architecture/Admin' Related Articles more
Comments