아는척 하기 시리즈 - InnoDB에서 Primary Key 설계 전략

InnoDB의 클러스터링 인덱스 구조에서 Primary Key 선택은 전체 테이블 성능을 좌우한다. 이 글에서는 업무 도메인 키(자연키)와 AUTO_INCREMENT(인조키) 중 어떤 것을 선택해야 하는지, 실무 관점에서 분석한다.

InnoDB 클러스터링 인덱스의 특성

InnoDB는 Primary Key를 기준으로 데이터를 물리적으로 정렬하여 저장한다. 이는 다음과 같은 특징을 갖는다:

1. PK 검색 성능 최적화: 인덱스 검색과 데이터 접근이 한 번에 이루어진다
2. 범위 검색 효율성: PK 기준 정렬로 인해 순차 I/O가 가능하다
3. 세컨더리 인덱스 오버헤드: 모든 세컨더리 인덱스가 PK 값을 포함한다

자연키가 유리한 경우

1. 조회 패턴이 명확한 경우

-- 주문 테이블에서 주문번호로 자주 조회하는 경우
SELECT * FROM orders WHERE order_no = 'ORD-2024-001234';

-- PK가 order_no라면 가장 빠른 조회 가능

주문번호, 거래번호 등 업무상 유일하고 자주 조회되는 키는 PK로 적합하다.

2. 시계열 데이터의 범위 조회

-- 로그 테이블에서 시간 범위 조회가 빈번한 경우
SELECT * FROM access_logs 
WHERE log_timestamp BETWEEN '2024-01-01' AND '2024-01-31';

타임스탬프를 포함한 복합 PK는 시간 범위 조회에 최적화된다.

3. 복합키가 자연스러운 경우

-- 다대다 관계 테이블
CREATE TABLE user_roles (
    user_id INT,
    role_id INT,
    PRIMARY KEY (user_id, role_id)
);

관계 테이블에서는 복합 자연키가 더 효율적이다.

인조키(AUTO_INCREMENT)가 유리한 경우

1. 자연키 변경 가능성

주민등록번호를 PK로 사용했던 시스템들이 개인정보보호법 시행 후 겪은 문제가 대표적이다. 법적, 정책적 변경에 취약한 자연키는 피해야 한다.

2. 긴 자연키

-- 비효율적: 긴 문자열 PK
CREATE TABLE products (
    product_code VARCHAR(100) PRIMARY KEY,  -- 'ELEC-TV-SAMSUNG-2024-Q4-65INCH-QLED'
    ...
);

-- 효율적: 인조키 사용
CREATE TABLE products (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    product_code VARCHAR(100) UNIQUE,
    ...
);

긴 자연키는 모든 세컨더리 인덱스 크기를 증가시켜 메모리 효율성을 떨어뜨린다.

3. 복제 및 샤딩 환경

-- 샤딩 환경에서 AUTO_INCREMENT 문제 해결
CREATE TABLE orders (
    id BIGINT PRIMARY KEY,
    -- id = (shard_id << 48) | (timestamp << 16) | sequence
    ...
);

분산 환경에서는 UUID나 Snowflake ID 같은 전역 유일 식별자가 필요하다.

실무 적용 가이드라인

자연키 선택 체크리스트

1. 불변성: 키 값이 절대 변경되지 않는가?
2. 짧은 길이: 대부분 4-8바이트 이내인가?
3. 조회 빈도: 해당 키로 조회가 가장 빈번한가?
4. 순차성: 삽입 시 페이지 분할을 최소화할 수 있는가?

인조키 선택 시 고려사항

1. BIGINT 사용: INT는 약 21억까지만 가능하므로 BIGINT 권장
2. 시작값 설정: 특정 범위 예약이 필요한 경우 시작값 조정
3. 캐시 설정: innodb_autoinc_lock_mode=2로 성능 최적화

하이브리드 접근법

CREATE TABLE users (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    email VARCHAR(255) NOT NULL,
    UNIQUE KEY uk_email (email)
) ENGINE=InnoDB;

인조키를 PK로 사용하되, 자연키에 UNIQUE 인덱스를 추가하는 방식이다. 이는 다음 장점을 제공한다:

1. PK 변경 위험 제거
2. 자연키 조회 성능 보장
3. 세컨더리 인덱스 크기 최적화

성능 측정 예시

-- 1000만 건 테이블에서 측정
-- 자연키(VARCHAR(50)) vs 인조키(BIGINT)

-- 인덱스 크기 비교
-- 자연키: 세컨더리 인덱스 1개당 약 500MB
-- 인조키: 세컨더리 인덱스 1개당 약 200MB

-- 조회 성능 (평균)
-- 자연키 PK 조회: 0.001초
-- 인조키 PK 조회: 0.001초
-- 자연키 세컨더리 인덱스 조회: 0.003초

결론

Primary Key 선택은 단순한 기술적 결정이 아니라 비즈니스 요구사항, 데이터 특성, 시스템 확장성을 종합적으로 고려해야 하는 설계 결정이다.

권장 사항:

1. 불변하고 짧은 자연키가 있다면 적극 활용
2. 불확실하다면 BIGINT AUTO_INCREMENT 사용
3. 필요시 하이브리드 접근법 고려
4. 항상 실제 워크로드로 성능 검증

데이터베이스 설계는 미래의 변경 비용을 최소화하는 방향으로 결정해야 한다. 대부분의 경우, 안정적인 인조키가 더 나은 선택이 될 것이다.