[SQL] PK(기본키)는 어떤 값이 좋을까? (auto_increment, uuid)

데이터베이스에 테이블을 추가하는 작업을 해야한다고 가정해봅시다. 테이블의 설계에 따라 ID 값이 필요한 상황입니다.
이 때, ID값으로 어떤 값을 사용하는 것이 가장 이상적일까요? 간단히 필요한 개념을 먼저 살펴보고 알아보도록 하겠습니다.

SQL에서 ID 란?

ID는 Identifier, 식별자라는 의미로 SQL에서는 데이터베이스 내의 각 행(레코드)을 고유하게 식별하는 값을 의미합니다.

이 ID는 다음과 같은 특징을 가집니다.

• 고유 식별자: 각 테이블의 행(row)은 고유한 ID를 가진다.
• 관계 형성: 테이블에서 다른 테이블의 ID를 참조하여 데이터 간의 관계를 설정할 수 있다.
• 데이터 무결성: 고유한 ID를 통해 데이터의 중복을 막고, 각 데이터가 명확하게 식별될 수 있도록 한다.

각 행을 식별하는 어떠한 값도 ID가 될수는 있지만, 일반적으로는 1씩 증가하는 고유한 숫자를 자동으로 할당받는 자동 생성(AUTO_INCREMENT)방식과 전 세계적으로 고유한 값(UUID : Universally Unique Identifier)을 생성하여 ID값으로 사용하는 방식이 있습니다.

PK, Primary Key

PK는 PRIMARY KEY, 기본키라는 의미로 데이터베이스 테이블의 각 행(레코드)을 고유하게 식별하는 데 사용되는 컬럼을 의미합니다.

PK는 다음과 같은 특징을 가집니다.

• NOT NULL 이어야하며, 중복된 값을 가질 수 없다.
• 테이블당 하나만 정의할 수 있다.
• 참조 무결성: 다른 테이블의 외래키(Foreign Key)가 PK를 참조하여 테이블 간의 관계를 맺는 데 사용된다.
• 데이터 무결성: 각 레코드를 명확하게 구분하여 데이터의 정확성을 보장한다.
• 데이터 검색 효율 증대: PK는 인덱스로 사용되어 데이터를 빠르게 검색할 수 있게 돕는다.
  PK는 MySQL(InnoDB 엔진)기준으로 기본적으로 클러스터 인덱스로 사용된다.

인덱스란?

INDEX는 데이터베이스 테이블의 검색 속도를 높이기 위해 사용하는 자료구조입니다. 일반적으로 데이터베이스는 특정 데이터를 찾기 위해 모든 행을 전부 탐색하게되고 만약 테이블에 상당한 수의 데이터가 있다면 DB 성능이 좋지 못할 것입니다. 이때 사용하는 것이 인덱스입니다. 인덱스는 쉽게 말하면 빠르게 Read하고자하는 컬럼을 정렬해놓은 복사본입니다. 인덱스의 기본 원리는 자주 Read되는 컬럼의 데이터만 따로 복사해서 정렬해서 보관해두고, 조건에 따라 소거해가며 테이블을 통해 직접 읽는 것 보다 비용은 더 많이 들지만 원하는 레코드를 훨씬 빠르게 찾는 것입니다.

MySQL(InnoDB엔진)은 대부분의 인덱스가 B+tree에 정렬되어 저장됩니다. 이번에 알아볼 PK(Clustered Index)와 Secondary Index는 B+tree를 사용하고 있습니다.

B+tree

B+tree를 간단히 알아보겠습니다. B+tree는 트리의 리프 노드에만 데이터 포인터를 저장하며, 리프 노드는 레코드에 대한 순차적인 접근을 제공하기 위해 연결된 형태를 가집니다. 맨 밑에만 데이터를 보관하고 노드끼리 연결되어있어 이동이 쉬워 범위 검색에 장점이 있습니다.

• 리프 노드(가장 아래)에만 데이터 포인터가 있다.
• 모든 키는 리프 노드에 있어 데이터 검색이 더 빠르고 정확하다.
• 리프 노드끼리 연결되어있어 범위 검색이 빠르다.
• 보통 트리의 높이가 3~4 레벨로 유지되어 일반적인 데이터 탐색에 2~3번의 페이지 접근으로 원하는 값을 찾을 수 있다.

마지막 특징에서 볼 수 있듯 단일 탐색 기준으로 많은 데이터가 있는 테이블에서 탐색 성능의 차이가 상당함을 알 수 있습니다.

 

MySQL에서는 옵티마이저가 카디널리티 기반으로 비용을 계산하여 인덱스를 사용하는 것과 풀스캔을 하는 것의 비용을 계산해 더 저렴한 방향으로 선택하여 데이터를 탐색하는데 조건을 만족하는 row 비율이 전체 테이블의 30% 이상이되면 풀스캔 선택 가능성이 높아집니다.

데이터 탐색을 위해서는 리프 노드에서 해당하는 데이터의 PK를 찾아서 원래 테이블(클러스터 인덱스)페이지로 다시 가야하기 때문에 레코드가 많아지면 페이지 점프(page fetch) 횟수가 증가하여 비용이 풀스캔보다 높아질 수 있습니다.

그리고 그 외에 인덱스가 사용되지 않는 경우 등의 케이스도 있지만 해당 글에서는 너무 깊게 설명하진 않고 글 하단에 따로 정리한 글을 남겨놓겠습니다.

Page와 Page Split

데이터베이스 인덱스에서 페이지(Page)와 페이지 분할(Page Split)을 이해하는 것은 데이터베이스 성능 최적화와 효율적인 운영을 위해 중요하다고 할 수 있습니다. 아래에서 설명할 클러스터 인덱스와 이 글의 주제인 ID로 숫자 AUTO_INCREMENT와 UUID의 비교를 더 잘 이해할 수 있게 간단히 알고 넘어가겠습니다.

// InnoDB엔진 기준으로 한 페이지의 크기는 16KB이다.
SHOW VARIABLES LIKE 'innodb_page_size';

//  Variable_name       Value
// innodb_page_size	16384

Page

• 물리적 저장 단위: 데이터베이스에서 데이터를 디스크에 저장하고 읽어오는 최소 물리적 단위이다.
  MySQL(InnoDB)에서 일반적으로 16KB 크기를 가진다.
  디스크에서 레코드(row)를 한 줄씩 읽는 것이 아니라, 이 16KB 단위로 읽고 쓰는 것이다.
• 데이터 저장소: 하나의 페이지는 여러 행(row)의 데이터를 담을 수 있다.
  16KB 크기의 상자에 PK 순서대로 레코드들이 꽉 채워 들어가 있다는 이미지이다.
• 인덱스 구조: 인덱스 트리의 각 노드 하나가 바로 하나의 페이지에 해당한다. 인덱스 페이지에는 키 값과 해당 데이터의 물리적 위치(주소)가 저장되어 있다.

Page Split

• 공간 부족 시 발생: 인덱스 페이지에 새로운 데이터를 삽입하려고 할 때, 해당 페이지의 저장 공간이 부족하면 발생한다.
• 데이터 재분배: 데이터베이스 관리 시스템(DBMS)은 기존 페이지의 데이터를 절반 정도 나누어 새로운 페이지로 분산 시킨다. 이 과정에서 상위 노드(부모 페이지)에 새로운 키와 주소를 추가하여 인덱스 구조의 균형을 유지하려한다.
• 예시: 만약 키 10, 20, 30, 40이 있는 페이지에 25를 넣어야하는 상황인데 페이지에 여유 공간이 없다면, InnoDB가 새로운 페이지를 하나 더 만들고 기존 페이지에 있던 레코드 중 절반 정도를 새 페이지로 옮겨버린다. 그리고 위의 부모 페이지(브랜치 노드)에 페이지의 경계를 나타내는 키를 추가(수정)한다.
• 성능 저하 요인: 페이지 분할은 새로운 페이지 할당, 기존 데이터 이동, 상위 노드 업데이트 등 추가적인 디스크 I/O 작업과 CPU 연산을 수반하므로, 빈번하게 발생하면 데이터 삽입과 수정 성능이 저하될 수 있다.

데이터베이스에서 디스크 I/O는 몇 바이트인가보다 몇 번 읽느냐가 더 중요합니다. 한 번 읽을 때 16KB를 가져오면 주변 레코드들도 같이 캐시에 올라오게되어 재사용성이 좋아지게됩니다. B+tree 구조에서 한 노드(페이지)에 여러 레코드를 넣을 수 있게되어 트리 높이가 감소하게되고, 트리 높이가 낮을수록 탐색에 필요한 I/O 횟수가 감소하게 됩니다.

 

페이지는 데이터의 물리적 저장 단위이고, 이 페이지의 공간이 부족하여 페이지 분할이 자주 일어날수록 데이터 베이스의 성능에 영향을 줄 수 있습니다.

클러스터 인덱스와 정렬 방식

Clustered Index (Primary Index)는 테이블 자체가 인덱스 트리로 저장되는 구조를 말하며 MySQL(InnoDB)에서 테이블은 곧 PK 기반 B+tree 이자 클러스터 인덱스라고 볼 수 있습니다. 테이블 자체가 일정 기준(PK) 순서대로 정렬되어진 B+tree 형태인 것 입니다. 데이터 자체가 이미 정렬된 B+tree 라서 PK를 사용해 범위 조회(BETWEEN), 정렬(ORDER BY) 등의 작업이 매우 빠릅니다.

위에서 인덱스 설명을 빠르게 Read하고자하는 컬럼을 정렬해놓은 복사본라고 했지만, 클러스터 인덱스의 경우 따로 저장하는 정보 공간을 두지 않고 테이블 자체를 활용합니다.
MySQL에서는 PK 설정이 되어있다면 해당 키를 클러스터 인덱스로, 만약 없다면 유니크하면서 Not Null인 컬럼을, 그것도 없으면 내부적으로 보이지 않는 컬럼을 두고 클러스터 인덱스로 사용합니다.

정렬되어진 형태라 조회는 상당히 빠르지만 데이터를 삽입하는 경우 페이지 분할이나 추가적인 정렬이 필요해 성능이 나빠지게 됩니다.

 

이렇게 기본적인 개념을 간단히 알아보았습니다. 

우리가 테이블을 생성해서 ID에 PK를 설정하면, PK는 클러스터 인덱스가 되어 그 기준으로 테이블에 데이터가 정렬되어 저장되게 됩니다.

이제 이 ID에 AUTO_INCREMENT(INT)와 UUID를 사용했을 때의 차이를 비교해보겠습니다.

AUTO_INCREMENT(INT)

auto_increment는 데이터베이스에서 각 행이 삽입될 때마다 자동으로 순차적인 정수 값을 생성하는 기능입니다.

주로 PK에 사용되어 각 행을 고유하게 식별하는 ID로 사용되며, 1부터 시작하여 값이 자동으로 1씩 증가합니다. 이 기능을 사용하면 ID와 같은 고유 번호를 직접 입력하지 않아도 되서 편리함에 장점이 있습니다.

• 자동 번호 생성: 새로운 행이 추가될 때마다 이전 값에 1을 더한 값이 자동으로 할당된다.
• 정수 자료형: auto_increment는 정수(int) 자료형 컬럼에만 적용할 수 있다.
• PK: 고유성을 보장해야 하는 기본 키(PK) 컬럼에 주로 사용된다.
• 삭제된 값 재사용 안 함: 중간에 행을 삭제하더라도, 삭제된 번호는 재사용되지 않고 계속해서 새로운 번호로 생성된다.
• 직접 값 입력 비권장: auto_increment로 설정된 컬럼에는 직접 값을 넣지 않고 자동 생성된 값을 사용하는 것이 권장된다. 일관성이 깨지는 것을 막기 위함이다.

장점

• 단순성과 관리 용이성: 데이터베이스가 자동으로 고유한 값을 생성해주기 때문에, 개발자가 직접 키 생성 로직 관리를 하지 않아도 되어 간단하다.
• 성능 효율: 데이터가 순차적으로 정렬된 상태(B-tree 인덱스 구조)로 삽입되므로 디스크 I/O가 효율적이며, 새로운 레코드 삽입 시 물리적인 재정렬이 발생하지 않는다.
• 저장 공간 효율: UUID와 같은 다른 키 생성 방식보다 차지하는 공간이 적어, 외래 키로 사용될 때 조인 성능 향상에 기여할 수 있다.
• 가독성과 사용 편의성: 숫자형태로 단순하여 직관적이다.
• 비즈니스 로직과의 분리: 키 자체가 비즈니스적인 의미를 갖지 않으므로, 비즈니스 로직 변경에 영향을 받지 않는다.

단점

• 분산 시스템에서의 확장성 문제: 여러 데이터베이스 서버 간에 데이터를 분산하거나 병합할 때, 각 서버에서 독립적으로 생성된 동일한 ID가 충돌할 가능성이 있다.
• ID 예측 가능성과 보안 취약: ID가 단순히 순차적으로 증가하므로, 악의적인 사용자가 ID범위를 추측, 스캔하여 다른 데이터(사용자 수, 총 재고 등)를 추측하거나 접근할 수 있는 보안 위험이 존재한다.
• 데이터베이스 의존성: 새 레코드가 삽입될 때 데이터베이스와 통신해야 다음 ID를 알 수 있으므로, 애플리케이션 코드 내에서 엔티티 생성 시 불편함이 있을 수 있다.

UUID

UUID(Universally Unique Identifier)는 범용 고유 식별자라는 의미로 컴퓨터 시스템에서 중복되지 않는 고유한 값을 생성하기 위한 표준 규약입니다. 128-bit의 숫자이며 매우 큰 난수를 사용해 로컬에서 고유하게 생성됩니다. 

123e4567-e89b-12d3-a456-426614174000

예시와 같은 형태를 가진 총 32개의 16진수(0부터 9까지의 숫자, A부터 F까지의 문자 총 16가지 기호를 사용하여 수를 표기하는 방식) 숫자가 4개의 하이픈으로 구분되어져 있는 구조입니다.

여러 시스템에서 동시에 ID를 생성해도 충돌 가능성이 극히 낮아 분산 환경에서 데이터를 고유하게 관리할 수 있게 해줍니다.

장점

• 분산 환경: 여러 서버나 서비스에서 데이터를 생성할 때 충돌 없이 고유한 ID를 보장하므로, 데이터 병합 및 관리가 용이하다.
• 고유성 보장: 전세계적으로 사실상 고유한 ID를 생성하므로, 데이터의 중복을 방지하고 신뢰성을 높일 수 있다.
• 보안: 숫자형 ID에 비해 예측이 어렵고 유추하기 어렵기 때문에, ID를 이용한 공격을 방지하는 데 도움이 될 수 있다.

단점

• 성능 저하: 무작위로 생성되는 UUID (버젼 4)는 데이터가 물리적으로 여러 페이지에 분산되어 저장되게 만든다.
• 빈번한 페이지 분할: 새로운 데이터가 삽입될 때마다 인덱스 페이지의 여유 공간이 부족해 페이지가 분할되는 횟수가 잦아져, 삽입 및 갱신 성능이 저하될 수 있다.
• 높은 I/O 비용: 데이터가 분산되어 저장되므로, 특정 데이터를 읽기 위해 더 많은 디스크 I/O가 발생하여 읽기 성능이 저하될 수 있다.
• 캐시 효율성 감소: 자주 접근하는 데이터가 메모리가 아닌 디스크에 흩어져 있어 히트율이 낮아질 수 있다.
• 저장 공간 비효율성: 일반적인 정수형 ID보다 UUID는 더 많은 저장 공간을 차지한다.

인덱스 관점에서의 auto_increment(int)와 UUID

클러스터 인덱스는 B+tree 구조이고 PK 값 기준으로 정렬된다고 앞서 설명했습니다.

이번에는 PK로 숫자 형태의 자동증가를 썼을때와 UUID를 사용했을 때를 비교해보겠습니다.

AUTO_INCREMENT

auto_increment를 쓰면 숫자가 +1씩 증가하는 형태로 자동으로 생성됩니다.

새로운 레코드가 삽입될 때도 항상 가장 오른쪽 끝 리프 페이지에만 레코드가 들어가게 됩니다.

사실상 중간 페이지에서 페이지 분할이 일어날 일이 거의 없으니 오른쪽 끝에서 가끔 새 페이지 분할이 발생하여 구조적으로 안정적입니다.

장점

• 삽입 성능: 순차적인 값 증가형태로 새로운 데이터 행이 항상 인덱스의 맨 끝에 추가된다. 이로 인해 페이지 분할이 잘 발생하지 않는다.
• 효율적인 저장 공간 및 캐싱: 데이터가 물리적으로 오름차순 순서대로 저장되어, 범위 검색이 매우 빠르고 관련 데이터가 동일한 페이지에 모여있어 데이터베이스 캐시(buffer pool)효율이 높다.
• 작은 인덱스 크기: 일반적으로 8바이트 정수형(BIGINT)을 사용하여 16바이트 이상의 UUID보다 인덱스 크기가 작다. 
  보조 인덱스에도 PK값이 포함되기 때문에 전체적인 디스크 사용량과 메모리 오버헤드를 줄여준다.

단점

• 분산 시스템에서 사용 어려움, 예측 가능한 ID

UUID

UUID (v4)와 같은 랜덤한 형태는 새로 들어오는 키도 정렬 순서상 어디로 들어갈지가 완전히 랜덤입니다.

삽입마다 B+tree 중간 어딘가 리프 페이지를 찾아가서 끼워 넣어야한다는 뜻이고, 중간에 레코드를 끼워 넣어야 하니 해당 페이지의 남은 여유 공간을 소모하고, 페이지가 꽉 차 있다면 페이지 분할이 발생하게 됩니다.

모든 리프 페이지가 고르게 잘 사용되다가 결국 꽉 차는 시점에 여기저기서 페이지 분할이 발생할 수 있고, 이로 인해 페이지 분할이 여러 레벨에 걸쳐 일어날 수 있게됩니다. 이는 곧 트리의 재배치, 부모 페이지 수정, 상위 페이지 분할까지 영향을 끼쳐 결과적으로 페이지 분할 횟수 증가, 트리 높이 증가, 페이지가 디스크 곳곳에 단편화되어 저장되고, 이는 곧 캐시 효율 감소와 전체 인덱스가 무거워지는 결과를 낳습니다.

장점

• 전역적 고유성으로 보안에 장점, 로컬 생성 가능

단점

• 심각한 성능 저하 (삽입 시): 무작위로 생성되는 값이라 새로운 데이터가 인덱스 트리의 임의의 위치에 삽입된다. 이로 인해 빈번한 페이지 분할 및 데이터 재정렬이 발생하여 디스크 I/O가 증가하고 삽입 성능이 크게 저하된다.
• 디스크 단편화: 데이터가 물리적으로 흩어져 저장되므로 디스크 단편화가 발생하기 쉽고 이는 읽기 성능 저하로 이어진다.
• 큰 저장공간: UUID는 정수형보다 훨씬 큰 공간을 차지하므로, 인덱스 크기가 커지고 쿼리 성능에 영향을 미친다.

auto_increment(int)의 경우 id자체가 +1씩 증가하므로 들어오는 시간 순서대로 정렬됨을 직관적으로 알 수 있지만, UUID(v4)의 경우 테이블 자체(클러스터 인덱스)는 UUID 값의 오름차순으로 정렬되어있지만 auto_increment와 달리 시간 순서와는 무관합니다.

결론

삽입이나 조회의 성능이 중요하고 분산 시스템이 아니라면 auto_increment를 PK로 사용하는 것이 권장된다고 합니다. 하지만 분산 환경 등에서 UUID의 고유성이 반드시 필요하다면 auto_increment를 PK로 사용하고, UUID는 별도의 컬럼에 저장하며 고유(unique)인덱스를 설정하거나, UUID v7과 같이 시간 정보를 포함하여 어느 정도 순차성을 가지는 UUID 버전을 고려해볼 수 있을 것 같습니다.

UUIDv7은 시간 기반 정렬로 대부분 증가하는 형태이며, InnoDB가 순차 삽입에 가까운 패턴을 가지게되어 페이지 분할도 일반적인 UUID에 비하여 많이 줄어들고 성능도 올라가서 AWS에서도 UUIDv7을 공식적으로 추천한다고 합니다.

• 단일 DB, 성능 최우선, ID 외부 노출 위험도 낮음 -> BIGINT AUTO_INCREMENT 고려
• 분산 시스템, 마이크로서비스, ID 충돌 방지 중요 -> BIGINT AUTO_INCREMENT 와 UUID(v7)컬럼과 유니크 인덱스 고려
• 직접 UUID도 쓰고 성능도 신경 씀 -> PK: BINARY(16) UUIDv7

---

UUID(v4)와 UUID(v7)이 페이지에 어떻게 끼워넣어지는지 비교

InnoDB 엔진의 클러스터 인덱스 입장에서 PK는 INT이든 BIGINT이든 BINARY(16)이든 CHAR(36)이든 결국 저장된 바이트들의 순서로 비교를 하게됩니다.

auto_increment 일때는 1(00 00 00 01), 2(00 00 00 02), 3(00 00 00 03)과 같이 내부에서 4바이트 정수로 바뀌어 사실상 1 < 2 <3 < 4 와 다를게 없고, 이런 구조 덕에 B+tree 리프 노드에 차곡차곡 오른쪽으로 붙게 됩니다.

UUID의 경우 4개의 UUID(v4)를 예시로 보겠습니다.

1. a3f1c210-1234-4b89-9abc-001122334455

2. 1f9ab3e0-5678-4cde-8f01-abcdef000111

3. 9c01ff20-9aaa-4eee-bbbb-112233445566

4. 3a77b920-0000-4ddd-cccc-778899aabbcc

1, 2, 3, 4는 실제 삽입 순서입니다. 하지만 클러스터 인덱스(CHAR(36))기준으로 정렬 순서는 문자열 사전순으로 맨 앞 글자부터 비교하게 됩니다. 16진수의 순서대로 맨 앞 글자부터 비교해서 정렬하게되면 정렬 순서는 2, 4, 3, 1 이 되고 이 순서(2, 4, 3, 1)대로 실제 B+tree 리프에 저장되게 됩니다. 이렇게 입력 시간과 정렬 위치가 뒤섞이게 된 것입니다.

이 때 만약 새로운 값인 523e4567-e89b-12d3-a456-426614174000가 들어왔다고 가정해보겠습니다. 그럼 루트 페이지를 로두하여 키를 비교하며 아래로 내려가서 어느 자식 노드로 가야할지 찾게되고 리프 페이지에 도착하면 해당 페이지 안의 키들과 다시 위의 비교 작업을 거쳐 끼워 넣을 위치를 판단하고 이때 페이지에 여유 공간이 없다면 페이지 분할이 발생하게 됩니다.

 

UUID(v7)의 경우에는 01GGGGGG-1234-7xxx-xxxx-xxxxxxxxxxxx와 같은 형태로 앞의 01GGGGGG 부분이 시간과 관련된 시간 필드입니다.

1.  018f1f0a-aaaa-7bcd-....

2. 018f1f0b-bbbb-7bcd-....

3. 018f1f0c-cccc-7bcd-....

위와 같은 순서대로 삽입되었다면 바이트 비교를해도 동일하게 1, 2, 3 순서를 유지하게 되어 삽입 순서와 정렬 순서, 시간순서가 맞게 정렬될 수 있는 것입니다. 동일한 밀리초에 여러 UUIDv7이 생성되면 하위 랜덤비트에 따라 순서가 완벽히 시간 정렬이 아닐수는 있지만 큰 범위에서 보면 삽입 시간과 정렬 위치가 거의 같은 방향으로 증가한다는 점에서 UUIDv4와 큰 차이가 있습니다.

 

---

인덱스

https://sihanni.tistory.com/198

[SQL] 3. 인덱스(Index)란?

 

UUID

https://docs.tosspayments.com/resources/glossary/uuid

UUID(Universally Unique Identifier) | 토스페이먼츠 개발자센터