PK 선언 타입에 관한 기준 - Ordered Insert vs Random Insert

[1] InnoDB 기본키와 B-Tree

(1) InnoDB 기본 키의 중요성

InnoDB 기본 키는 인덱스 정렬 저장 엔진(Index-Organized Storage Engine) 이다. 이는 기본 키를 B-Tree 를 사용해 데이터를 저장하며, 모든 InnoDB 테이블에 기본 키가 필수이다. 보조 인덱스 또한 B-Tree 로 구성되면, 여기서 저장된 값은 기본 키를 통해 데이터에 접근하게 된다.

 

(2) B-Tree

B-Tree 는 디스크와 같은 블록 디바이스에서 효율적으로 동작하도록 설계된 데이터 구조이다. 디스크에서 단일 바이트를 임의의 위치에서 읽는 데 소요하는 시간은 큰 블록을 읽는 데 걸리는 시간과 크게 다르지 않는 장점이 있다. 루트 노드로 부터 시작해 검색 키를 비교해 중간 노드, 데이터에 존재하는 리프 노드와 같은 구조로 되어 있기 때문이다. InnoDB 는 16KB 단위로 데이터를 관리하며, 이는 읽기 및 쓰기 성능을 최적화한다.

ref : https://utsman.github.io/mysql/2017/07/26/mysql-index/

 

 

[2] Ordered Insert vs Random Insert

(1) 정렬된 삽입(Ordered Insert)

기본 키가 자동 증가하는 정수(AUTO_INCREMENT) 일 경우, 데이터가 정렬된 순서로 삽입된다.

• 이는 InnoDB 가 페이지를 최대한 채운 상태(약15KB) 로 유지하며, 페이지 분할을 최소화한다. 
• 이러한 방식은 쓰기 작업 작업(insert)이 많고, 읽기 작업(read)이 상대적으로 적은 워크로드에서 적합하다.

 

(2) 랜덤 삽입(Random Insert)

기본 키가 임의 값인 경우, 데이터가 랜덤하게 삽입되며, 랜덤 삽입은 읽기 작업이 많은 워크로드에서 적합할 수 있다.

• 페이지가 자주 분할되고 평균 채우기 비율이 약 65-75% 로 감소해 데이터 크기가 증가한다.
• 삽입 작업에서 추가 오버헤드 발생한다.

 

(3) 워크로드 유형에 따른 기본 키 선택

1. 쓰기 중심 워크로드(Insert-Heavy Workload)

• 특징 : 다량의 데이터를 실시간으로 기록해야 하며, 쓰기 지연이 허용되지 않음
• 권장 사항 : 자동 증가 정수(AUTO_INCREMENT) 를 사용해 정렬된 삽입을 유도

2. 일기 중심 워크로드(Read-Heavy Workload)

• 특징 : 다순의 읽기 작업이 대규모 테이블에서 발생하며, Disk I/O 가 많음
• 문제 : 쿼리당 여러 페이지를 읽어야 하며, 효율성이 낮음.
• 해결책 : 기본 키(PK) 를 워크로드에 맞게 조정해 데이터가 한 페이지에 모이도록 설계해야 한다.

 

[3] PK 별 데이터 삽입 벤치마킹

PERCONA 에서 제공한 벤치마킹 결과를 확인해보면 데이터 삽입에 의한 데이터 크기와 소요 시간이 UUIDv4 를 사용할 때 비효율적임을 확인할 수 있다. PK 를 관리하고자 하는 경우, 순서 정렬을 보장하는 UUID 또는 AUTO_INCREMENT 사용을 검토해보면 좋을 것 같다.

ref : https://www.percona.com/blog/store-uuid-optimized-way/

 

 

[4] PK 를 어떻게 선언할 것인가??

다양한 기준이 있겠지만 해당 내용을 리뷰하면서 아래와 같은 기준을 세웠다.

(1) UUID ver7 를 PK 로 선언하는 방법

1. UUID ver7 은 time stamp 를 기반한 정렬을 제공하는 UUID 이다.
2. 이는 정렬된 데이터 정렬을 보장하며, 추가시의 데이터 사이즈와 삽입 속도에 이점이 있다.

(2) PK는 AUTO_INCREMENT + UUID ver7 을 별도의 UK 로 선언하는 방법

1. 유저에 관련된 식별자는 보안적인 입장에서 식별자 노출을 권장하지 않는다.
2. 이를 방지하기 위해 대체키를 UUID ver7 을 통해 노출시키고, 실제 PK 는 auto_increment 사용을 검토해보자.
3. auto_increment 사용하는 이유는 PK의 데이터 사이즈 관점에서의 선택이다. 다른 UUID ver7 을 선언하고 대체키를 사용해도 검토해볼 사항일 것 같다.

 

JPA 기반으로 uuid ver7 을 사용하는 방법은 아래 글을 참고해보자.

• [dev-cooper] JPA에 UUID version7 적용하기

 

Reference

• [PERCONA] Tuning InnoDB Primary Keys : https://www.percona.com/blog/tuning-innodb-primary-keys/
• [PERCONA] Storing UUID Values in MySQL : https://www.percona.com/blog/store-uuid-optimized-way/