카테고리 : MySQL/기술노트

좋은 Primary Key 설계: 단조 증가, 폭, 변경 불가성, 분산 쓰기

InnoDB Primary Key의 단조 증가성, 폭, 변경 불가성, 분산 ID 생성이 쓰기와 보조 인덱스에 미치는 영향을 설명한다.

저자: MySQL 기술 노트 작성: 2026.07.20 약 19분 11,186자
다운로드
#MySQL#InnoDB#인덱스#아키텍처#운영

Primary Key는 단순히 중복 row를 막는 제약 조건이 아니다. InnoDB에서는 Primary Key가 clustered index의 정렬 기준이며, 모든 보조 인덱스가 row를 다시 찾기 위한 locator로 Primary Key 값을 포함한다. 따라서 Primary Key의 삽입 순서, byte 폭, 변경 가능성은 한 인덱스에만 머물지 않고 테이블 전체의 page 배치, Buffer Pool 효율, redo 양, 보조 인덱스 크기와 복제 부하에 영향을 준다.

좋은 Primary Key는 업무적으로 유일하기만 한 key가 아니다. 저장 엔진 관점에서는 가능하면 좁고, 안정적으로 증가하며, 생성 후 바뀌지 않고, 필요한 쓰기 토폴로지에서 충돌 없이 만들 수 있는 key가 유리하다. 하지만 네 조건을 항상 동시에 만족시키기는 어렵다. 이 글은 InnoDB 내부 구조에서 각 조건이 왜 중요한지 설명하고, BIGINT AUTO_INCREMENT, 무작위 UUID, 시간 정렬형 128-bit ID, 분산 생성형 64-bit ID를 어떤 기준으로 선택해야 하는지 정리한다. 기본 대상은 MySQL 8.0 이상이며 SQL 예제는 MySQL 8.0 임시 환경에서 실행 검증한다.

1. Primary Key가 테이블 전체의 물리 설계가 되는 이유

InnoDB 테이블의 row는 Primary Key 순서로 clustered index leaf page에 저장된다. 보조 인덱스 leaf에는 보조 key와 함께 해당 row의 Primary Key가 들어간다. 보조 인덱스로 찾은 row의 나머지 column이 필요하면 InnoDB는 그 Primary Key로 clustered index를 다시 탐색한다.

flowchart LR
    I["INSERT: 새 Primary Key"] --> C["Clustered B+Tree<br/>PK 순서로 leaf 위치 결정"]
    C --> P{"대상 leaf에<br/>공간이 있는가?"}
    P -->|예| W["record 기록<br/>dirty page·redo 생성"]
    P -->|아니요| S["page split 또는 재배치<br/>추가 page 변경"]
    W --> X["각 Secondary B+Tree 갱신"]
    S --> X
    X --> L["secondary key + Primary Key 저장"]
    L --> R["더 넓은 PK일수록<br/>모든 secondary leaf가 커짐"]

이 구조에서 Primary Key 설계가 전파되는 경로는 다음과 같다.

  1. 삽입 위치: 새 Primary Key 값이 clustered B+Tree의 어느 leaf에 들어갈지 결정한다.
  2. Clustered record 폭: Primary Key 자체가 clustered record의 일부가 된다.
  3. Secondary record 폭: 동일한 Primary Key 값이 모든 secondary leaf에 row locator로 저장된다.
  4. Row 재탐색 비용: non-covering secondary access가 Primary Key를 사용해 clustered B+Tree를 다시 찾는다.
  5. 변경 비용: Primary Key 변경은 clustered 위치 변경과 secondary locator 갱신을 유발한다.

Primary Key가 없다고 이 문제가 사라지는 것도 아니다. 명시적 Primary Key가 없으면 InnoDB는 모든 column이 NOT NULL인 첫 번째 UNIQUE 인덱스를 clustered index로 선택할 수 있고, 그것도 없으면 내부 6-byte row ID를 생성한다. 내부 row ID는 application이 참조하거나 운영자가 안정적인 식별자로 사용할 수 없다. 명시적이고 의도적인 Primary Key를 두는 편이 schema와 실행 계획을 예측하기 쉽다.

2. 좋은 Primary Key를 평가하는 네 축

2.1 단조 증가성: 쓰기 locality를 만든다

새 key가 기존 최댓값보다 대체로 크면 insert는 clustered B+Tree의 오른쪽 끝 leaf 부근에 집중된다. BIGINT AUTO_INCREMENT가 대표적이다. 최근 leaf가 Buffer Pool에 남기 쉽고, tree 전역의 임의 page를 반복해서 불러오는 패턴보다 쓰기 locality가 좋다.

단조 증가는 “모든 insert가 항상 한 page에만 기록된다”는 뜻은 아니다. 현재 page가 차면 새 page가 생기고 오른쪽으로 진행한다. 또한 높은 동시 쓰기에서는 rightmost leaf와 관련 latch가 hot spot이 될 수 있다. AUTO_INCREMENT 값 할당에 관한 동시성 제어와 B+Tree leaf page 경쟁도 서로 다른 현상이다. 처리량 한계가 보이면 추측으로 UUID를 도입하기 전에 wait event, transaction concurrency, redo와 storage latency를 함께 측정해야 한다.

완전히 무작위인 Primary Key는 insert 위치를 tree 전역에 분산한다. 표면적으로는 hot spot을 줄일 수 있지만 다음 비용을 만들 수 있다.

  • 여러 leaf page가 동시에 dirty page가 되어 working set이 넓어진다.
  • Buffer Pool에 없는 leaf를 불러오고 다른 page를 축출할 가능성이 커진다.
  • 중간 page의 여유 공간이 부족할 때 page split과 재배치가 발생한다.
  • 물리적으로 가까운 시간에 들어온 row가 key 순서로는 흩어져 range locality가 낮아진다.
  • key가 UUID 문자열처럼 넓다면 random insert 문제와 폭 문제가 동시에 발생한다.

따라서 “분산 쓰기에는 random key가 항상 좋다”는 결론은 성립하지 않는다. ID 생성 주체의 분산과 B+Tree 삽입 위치의 무작위화는 별도 설계 문제다.

2.2 폭: 한 테이블이 아니라 모든 인덱스에 곱해진다

Primary Key 폭은 clustered index와 모든 secondary index에 영향을 준다. 예를 들어 동일한 논리 ID를 다음과 같이 표현할 수 있다.

표현 대표적인 저장 폭 장점 주요 비용·주의점
BIGINT UNSIGNED 8 byte 좁고 비교가 단순하며 AUTO_INCREMENT와 결합하기 쉬움 외부 생성 시 node·시간·sequence bit 설계가 필요할 수 있음
BINARY(16) 16 byte 128-bit ID를 문자열보다 조밀하게 저장 사람이 직접 읽기 어렵고 byte 순서 규약이 필요함
CHAR(36) UUID 문자열 36개 문자와 문자 집합·collation 비용 로그와 도구에서 읽기 쉬움 넓은 index record, 문자열 비교, 모든 secondary index로 폭 전파
자연키 문자열 업무 값에 따라 가변 업무 의미가 직접 드러남 값이 길거나 바뀌기 쉽고 collation·정규화 규칙의 영향이 큼

표의 byte 수만으로 실제 index page 크기를 정확히 계산할 수는 없다. record header, nullable bitmap, variable-length metadata, page directory, page fill 상태와 collation이 함께 영향을 준다. 그러나 같은 cardinality와 workload라면 좁은 Primary Key가 한 page에 더 많은 entry를 담아 fan-out과 cache density에 유리하다는 방향은 변하지 않는다.

특히 secondary index가 많을수록 폭의 증폭 효과가 커진다. Primary Key가 8 byte에서 36-character 문자열로 바뀌면 차이는 clustered index 한 곳에만 생기지 않는다. (status, created_at), (customer_id, state), (tenant_id, event_time) 같은 모든 secondary leaf가 더 넓은 row locator를 보관한다.

2.3 변경 불가성: 정체성과 물리 위치를 안정시킨다

Primary Key는 생성 후 바뀌지 않는 값을 선택하는 것이 좋다. InnoDB에서 Primary Key 변경은 제자리에서 식별자 몇 byte만 덮어쓰는 가벼운 작업으로 생각해서는 안 된다. clustered index의 정렬 key가 달라지므로 기존 위치에서 record를 제거하고 새 key 위치에 넣는 것과 유사한 물리 작업이 필요하다. 보조 인덱스가 보관한 row locator도 새 Primary Key로 바뀌어야 한다.

외래 키가 ON UPDATE CASCADE라면 참조하는 child row까지 연쇄 갱신된다. cascade가 없으면 변경이 거부될 수 있다. 어느 경우든 “업무 코드가 바뀌었으니 Primary Key도 바꾼다”는 모델은 잠금 범위, redo, replication apply, 장애 복구와 application cache invalidation을 복잡하게 만든다.

변할 수 있는 이메일 주소, 전화번호, 조직 코드, 외부 파트너 코드 대신 내부 surrogate key를 Primary Key로 두고, 업무상 유일성은 별도의 UNIQUE 제약으로 표현하는 패턴이 일반적으로 안전하다. 자연키가 짧고 영구 불변이라는 강한 도메인 보장이 있다면 자연키도 가능하지만, “현재까지 바뀐 적이 없다”와 “계약상 절대 바뀌지 않는다”를 구분해야 한다.

2.4 분산 쓰기 적합성: 생성 위치와 저장 순서를 함께 본다

단일 MySQL writer에서는 BIGINT AUTO_INCREMENT가 단순하고 효율적인 기본값이다. 그러나 여러 독립 writer나 shard가 중앙 조율 없이 ID를 생성해야 한다면 하나의 local sequence만으로는 전역 충돌을 막을 수 없다.

분산 생성 전략은 대략 다음 범주로 나뉜다.

  • 범위 또는 offset 분할: writer별 ID 범위나 auto_increment_increment·auto_increment_offset을 나눈다. 고정된 소수 writer에는 단순하지만 topology 변경, 범위 고갈과 재할당을 운영해야 한다.
  • 중앙 ID 서비스: 전역 순서를 만들기 쉽지만 가용성, 지연 시간, batch allocation과 장애 시 중복 방지가 새로운 핵심 문제가 된다.
  • 시간+node+sequence의 64-bit ID: 8 byte 폭과 대체로 시간 순서를 함께 얻을 수 있다. clock rollback, node ID 중복, 같은 tick의 sequence 고갈을 명시적으로 처리해야 한다.
  • 시간 정렬형 128-bit ID: 여러 생성 주체에서 충돌 가능성을 낮추면서 key의 상위 부분에 시간 순서를 반영할 수 있다. 동일 시간 구간의 정렬, generator별 순서, clock skew와 binary encoding 규약을 검증해야 한다.
  • 무작위 128-bit ID: 중앙 조율 없이 만들기 쉽지만 clustered B+Tree의 random insert와 넓은 key 비용을 감수한다. 문자열보다 BINARY(16) 저장을 우선 검토한다.

분산 ID가 “전역적으로 엄격히 1씩 증가”할 필요가 있는지 먼저 질문해야 한다. 대부분의 업무에서는 유일성, 대략적인 시간 정렬, 생성 가능성이 필요할 뿐 gap 없는 연속 번호는 필요하지 않다. gap 없는 번호가 회계·법적 요구라면 Primary Key와 별도의 업무 번호로 분리하고, 그 번호의 직렬화와 감사 규칙을 별도로 설계하는 편이 낫다.

3. 후보별 선택 기준

후보 삽입 locality 폭 독립 분산 생성 운영 복잡도 적합한 상황
BIGINT AUTO_INCREMENT 높음 8 byte 낮음 낮음 단일 writer, 일반 OLTP의 기본 선택
writer별 범위를 쓴 BIGINT 대체로 높음 8 byte 중간 중간 writer 수와 범위를 통제할 수 있는 환경
시간+node+sequence BIGINT 대체로 높음 8 byte 높음 높음 다수 생성 주체, generator 운영 역량이 있는 환경
시간 정렬형 BINARY(16) 중간~높음 16 byte 높음 중간 128-bit namespace와 대략적 시간 순서가 필요한 환경
무작위 BINARY(16) 낮음 16 byte 높음 낮음~중간 생성 독립성이 locality보다 중요한 환경
UUID CHAR(36) 낮음 36문자 높음 낮음 사람이 읽는 표현이 index 효율보다 중요한 제한적 환경
변경 가능한 자연키 workload에 따라 다름 가변 도메인 의존 높음 영구 불변·짧음·유일성이 보장되지 않으면 피함

여기서 시간 정렬형 ID도 항상 순차 AUTO_INCREMENT와 같은 물리 특성을 보장하지는 않는다. timestamp 해상도 안에서 random suffix가 섞이거나 여러 node의 clock이 어긋나면 동일한 key range 안에서 삽입 순서가 뒤섞일 수 있다. 실제 generator가 내보내는 16-byte 순서와 MySQL이 비교하는 binary 순서가 일치하는지도 확인해야 한다.

MySQL의 UUID_TO_BIN(uuid, 1)에서 두 번째 인수는 특정 UUID time field의 byte 재배치를 위한 기능이다. 임의의 UUID 형식이나 application 고유 ID에 무조건 적용하는 “시간 정렬 스위치”가 아니다. 사용하는 UUID version과 byte layout을 정한 뒤 writer와 reader가 동일한 변환 규약을 사용해야 한다.

4. 실행 환경과 ID 표현 확인

먼저 서버 버전, InnoDB page size, AUTO_INCREMENT의 현재 분산 설정을 확인한다.

SELECT VERSION() AS mysql_version,
       @@innodb_page_size AS innodb_page_size_bytes,
       @@auto_increment_increment AS auto_increment_step,
       @@auto_increment_offset AS auto_increment_offset;

실행 결과(MySQL 8.0.x):

mysql> SELECT VERSION() AS mysql_version,
    ->        @@innodb_page_size AS innodb_page_size_bytes,
    ->        @@auto_increment_increment AS auto_increment_step,
    ->        @@auto_increment_offset AS auto_increment_offset;

+---------------+------------------------+---------------------+-----------------------+
| mysql_version | innodb_page_size_bytes | auto_increment_step | auto_increment_offset |
+---------------+------------------------+---------------------+-----------------------+
| 8.0.46        |                  16384 |                   1 |                     1 |
+---------------+------------------------+---------------------+-----------------------+
1 row in set (0.00 sec)

auto_increment_increment=1, auto_increment_offset=1은 일반적인 단일 sequence 설정이다. 이 값만 보고 실제 writer가 하나라고 단정할 수는 없으며, application의 ID 생성 방식과 replication topology도 함께 확인해야 한다.

5. 재현 예제: Primary Key 폭이 secondary index에 전파되는 구조

다음 예제는 동일한 논리 데이터를 BIGINT, UUID CHAR(36), BINARY(16) Primary Key로 저장한다. 세 테이블에는 동일한 (state, created_at) secondary index를 둔다. UUID 문자열 column은 값이 ASCII 문자로 제한된다는 의도를 드러내기 위해 CHARACTER SET ascii를 사용한다.

DROP TABLE IF EXISTS pk_bigint_demo;
DROP TABLE IF EXISTS pk_uuid_char_demo;
DROP TABLE IF EXISTS pk_uuid_bin_demo;

CREATE TABLE pk_bigint_demo (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    state CHAR(1) NOT NULL,
    created_at DATETIME NOT NULL,
    payload VARCHAR(80) NOT NULL,
    PRIMARY KEY (id),
    KEY ix_state_created (state, created_at)
) ENGINE=InnoDB;

CREATE TABLE pk_uuid_char_demo (
    id CHAR(36) CHARACTER SET ascii COLLATE ascii_bin NOT NULL,
    state CHAR(1) NOT NULL,
    created_at DATETIME NOT NULL,
    payload VARCHAR(80) NOT NULL,
    PRIMARY KEY (id),
    KEY ix_state_created (state, created_at)
) ENGINE=InnoDB;

CREATE TABLE pk_uuid_bin_demo (
    id BINARY(16) NOT NULL,
    state CHAR(1) NOT NULL,
    created_at DATETIME NOT NULL,
    payload VARCHAR(80) NOT NULL,
    PRIMARY KEY (id),
    KEY ix_state_created (state, created_at)
) ENGINE=InnoDB;

실행 결과(MySQL 8.0.x):

mysql> DROP TABLE IF EXISTS pk_bigint_demo;

Query OK, 0 rows affected (0.00 sec)

mysql> DROP TABLE IF EXISTS pk_uuid_char_demo;

Query OK, 0 rows affected (0.00 sec)

mysql> DROP TABLE IF EXISTS pk_uuid_bin_demo;

Query OK, 0 rows affected (0.00 sec)

mysql> CREATE TABLE pk_bigint_demo (
    ->     id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    ->     state CHAR(1) NOT NULL,
    ->     created_at DATETIME NOT NULL,
    ->     payload VARCHAR(80) NOT NULL,
    ->     PRIMARY KEY (id),
    ->     KEY ix_state_created (state, created_at)
    -> ) ENGINE=InnoDB;

Query OK, 0 rows affected (0.00 sec)

mysql> CREATE TABLE pk_uuid_char_demo (
    ->     id CHAR(36) CHARACTER SET ascii COLLATE ascii_bin NOT NULL,
    ->     state CHAR(1) NOT NULL,
    ->     created_at DATETIME NOT NULL,
    ->     payload VARCHAR(80) NOT NULL,
    ->     PRIMARY KEY (id),
    ->     KEY ix_state_created (state, created_at)
    -> ) ENGINE=InnoDB;

Query OK, 0 rows affected (0.00 sec)

mysql> CREATE TABLE pk_uuid_bin_demo (
    ->     id BINARY(16) NOT NULL,
    ->     state CHAR(1) NOT NULL,
    ->     created_at DATETIME NOT NULL,
    ->     payload VARCHAR(80) NOT NULL,
    ->     PRIMARY KEY (id),
    ->     KEY ix_state_created (state, created_at)
    -> ) ENGINE=InnoDB;

Query OK, 0 rows affected (0.01 sec)

재현 데이터는 세 테이블에 각각 6,000건을 넣는다. 문자열·binary ID는 MD5()를 이용한 결정적 테스트 값일 뿐, 운영 ID 생성법을 권장하는 예제가 아니다. 암호학적 식별자나 보안 token 생성에 MD5()를 사용해서는 안 된다.

SET SESSION cte_max_recursion_depth = 7000;

INSERT INTO pk_bigint_demo (state, created_at, payload)
WITH RECURSIVE seq AS (
    SELECT 1 AS n
    UNION ALL
    SELECT n + 1 FROM seq WHERE n < 6000
)
SELECT CHAR(65 + MOD(n, 4)),
       TIMESTAMP('2026-01-01 00:00:00') + INTERVAL n SECOND,
       RPAD(CONCAT('row-', n), 60, 'x')
FROM seq;

INSERT INTO pk_uuid_char_demo (id, state, created_at, payload)
WITH RECURSIVE seq AS (
    SELECT 1 AS n
    UNION ALL
    SELECT n + 1 FROM seq WHERE n < 6000
), digests AS (
    SELECT n, MD5(CONCAT('pk-', n)) AS h FROM seq
)
SELECT LOWER(CONCAT(SUBSTR(h, 1, 8), '-', SUBSTR(h, 9, 4), '-',
                    SUBSTR(h, 13, 4), '-', SUBSTR(h, 17, 4), '-', SUBSTR(h, 21, 12))),
       CHAR(65 + MOD(n, 4)),
       TIMESTAMP('2026-01-01 00:00:00') + INTERVAL n SECOND,
       RPAD(CONCAT('row-', n), 60, 'x')
FROM digests;

INSERT INTO pk_uuid_bin_demo (id, state, created_at, payload)
WITH RECURSIVE seq AS (
    SELECT 1 AS n
    UNION ALL
    SELECT n + 1 FROM seq WHERE n < 6000
)
SELECT UNHEX(MD5(CONCAT('pk-', n))),
       CHAR(65 + MOD(n, 4)),
       TIMESTAMP('2026-01-01 00:00:00') + INTERVAL n SECOND,
       RPAD(CONCAT('row-', n), 60, 'x')
FROM seq;

ANALYZE TABLE pk_bigint_demo, pk_uuid_char_demo, pk_uuid_bin_demo;

SELECT 'pk_bigint_demo' AS table_name, COUNT(*) AS row_count FROM pk_bigint_demo
UNION ALL
SELECT 'pk_uuid_char_demo', COUNT(*) FROM pk_uuid_char_demo
UNION ALL
SELECT 'pk_uuid_bin_demo', COUNT(*) FROM pk_uuid_bin_demo;

다음 실행 결과는 세 INSERT의 반복 CTE 부분을 줄이고, 적재 건수와 ANALYZE TABLE 성공 여부를 중심으로 발췌한 것이다.

실행 결과(MySQL 8.0.x):

mysql> SET SESSION cte_max_recursion_depth = 7000;
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO pk_bigint_demo ...;
Query OK, 6000 rows affected (0.03 sec)
Records: 6000  Duplicates: 0  Warnings: 0

mysql> INSERT INTO pk_uuid_char_demo ...;
Query OK, 6000 rows affected (0.05 sec)
Records: 6000  Duplicates: 0  Warnings: 0

mysql> INSERT INTO pk_uuid_bin_demo ...;
Query OK, 6000 rows affected (0.03 sec)
Records: 6000  Duplicates: 0  Warnings: 0

mysql> ANALYZE TABLE pk_bigint_demo, pk_uuid_char_demo, pk_uuid_bin_demo;
+-----------------------------------+---------+----------+----------+
| Table                             | Op      | Msg_type | Msg_text |
+-----------------------------------+---------+----------+----------+
| mysql_tech_note.pk_bigint_demo    | analyze | status   | OK       |
| mysql_tech_note.pk_uuid_char_demo | analyze | status   | OK       |
| mysql_tech_note.pk_uuid_bin_demo  | analyze | status   | OK       |
+-----------------------------------+---------+----------+----------+
3 rows in set (0.01 sec)

mysql> SELECT 'pk_bigint_demo' AS table_name, COUNT(*) AS row_count FROM pk_bigint_demo
    -> UNION ALL
    -> SELECT 'pk_uuid_char_demo', COUNT(*) FROM pk_uuid_char_demo
    -> UNION ALL
    -> SELECT 'pk_uuid_bin_demo', COUNT(*) FROM pk_uuid_bin_demo;
+-------------------+-----------+
| table_name        | row_count |
+-------------------+-----------+
| pk_bigint_demo    |      6000 |
| pk_uuid_char_demo |      6000 |
| pk_uuid_bin_demo  |      6000 |
+-------------------+-----------+
3 rows in set (0.00 sec)

mysql.innodb_index_stats의 size는 persistent statistics가 추정한 page 수이며 실시간 정밀 측정값이 아니다. 다음 query는 세 표현의 방향성 비교와 추세 관찰에 사용할 수 있다. 작은 데이터에서는 extent 할당과 sampling 때문에 값이 같거나 실행마다 달라질 수 있으므로, 특정 page 수를 일반 법칙으로 고정해서는 안 된다.

SELECT table_name,
       index_name,
       stat_value AS estimated_pages
FROM mysql.innodb_index_stats
WHERE database_name = 'mysql_tech_note'
  AND table_name IN ('pk_bigint_demo', 'pk_uuid_char_demo', 'pk_uuid_bin_demo')
  AND index_name IN ('PRIMARY', 'ix_state_created')
  AND stat_name = 'size'
ORDER BY index_name, table_name;

실행 결과(MySQL 8.0.x):

mysql> SELECT table_name,
    ->        index_name,
    ->        stat_value AS estimated_pages
    -> FROM mysql.innodb_index_stats
    -> WHERE database_name = 'mysql_tech_note'
    ->   AND table_name IN ('pk_bigint_demo', 'pk_uuid_char_demo', 'pk_uuid_bin_demo')
    ->   AND index_name IN ('PRIMARY', 'ix_state_created')
    ->   AND stat_name = 'size'
    -> ORDER BY index_name, table_name;

+-------------------+------------------+-----------------+
| table_name        | index_name       | estimated_pages |
+-------------------+------------------+-----------------+
| pk_bigint_demo    | PRIMARY          |              97 |
| pk_uuid_bin_demo  | PRIMARY          |              97 |
| pk_uuid_char_demo | PRIMARY          |              97 |
| pk_bigint_demo    | ix_state_created |              12 |
| pk_uuid_bin_demo  | ix_state_created |              15 |
| pk_uuid_char_demo | ix_state_created |              23 |
+-------------------+------------------+-----------------+
6 rows in set (0.00 sec)

이 비교에서 주목할 점은 ix_state_created DDL에 id를 쓰지 않았는데도 Primary Key 표현에 따라 secondary record 폭이 달라질 수 있다는 사실이다. InnoDB가 row locator로 Primary Key를 내부 포함하기 때문이다. 운영에서는 한 번의 page 수보다 데이터 증가에 따른 index size, Buffer Pool 점유, write latency와 page split 관련 지표의 추세를 비교하는 편이 안전하다.

6. 재현 예제: Primary Key 변경의 연쇄 효과

다음 예제는 parent Primary Key를 변경할 때 ON UPDATE CASCADE가 child row까지 갱신하는 동작을 보여준다. 작은 예제의 성공은 운영에서 변경이 저렴하다는 뜻이 아니다. 실제로는 parent의 clustered record, parent secondary locator, child 외래 키 인덱스와 관련 row에 변경이 전파된다.

DROP TABLE IF EXISTS pk_child_demo;
DROP TABLE IF EXISTS pk_parent_demo;

CREATE TABLE pk_parent_demo (
    account_id BIGINT NOT NULL,
    account_code VARCHAR(20) NOT NULL,
    PRIMARY KEY (account_id),
    UNIQUE KEY ux_account_code (account_code)
) ENGINE=InnoDB;

CREATE TABLE pk_child_demo (
    event_id BIGINT NOT NULL,
    account_id BIGINT NOT NULL,
    event_name VARCHAR(40) NOT NULL,
    PRIMARY KEY (event_id),
    KEY ix_account_id (account_id),
    CONSTRAINT fk_child_account
        FOREIGN KEY (account_id) REFERENCES pk_parent_demo (account_id)
        ON UPDATE CASCADE
) ENGINE=InnoDB;

INSERT INTO pk_parent_demo (account_id, account_code)
VALUES (100, 'ACCOUNT-A');

INSERT INTO pk_child_demo (event_id, account_id, event_name)
VALUES (1, 100, 'created'),
       (2, 100, 'verified');

UPDATE pk_parent_demo
SET account_id = 900
WHERE account_id = 100;

SELECT p.account_id,
       p.account_code,
       c.event_id,
       c.event_name
FROM pk_parent_demo AS p
JOIN pk_child_demo AS c
  ON c.account_id = p.account_id
ORDER BY c.event_id;

다음은 준비 DDL의 세부 column 정의를 줄이고, Primary Key 변경과 cascade 결과를 중심으로 발췌한 실행 결과다.

실행 결과(MySQL 8.0.x):

mysql> CREATE TABLE pk_parent_demo (...);
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE TABLE pk_child_demo (... ON UPDATE CASCADE);
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO pk_parent_demo (account_id, account_code)
    -> VALUES (100, 'ACCOUNT-A');
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO pk_child_demo (event_id, account_id, event_name)
    -> VALUES (1, 100, 'created'), (2, 100, 'verified');
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> UPDATE pk_parent_demo
    -> SET account_id = 900
    -> WHERE account_id = 100;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> SELECT p.account_id, p.account_code, c.event_id, c.event_name
    -> FROM pk_parent_demo AS p
    -> JOIN pk_child_demo AS c ON c.account_id = p.account_id
    -> ORDER BY c.event_id;
+------------+--------------+----------+------------+
| account_id | account_code | event_id | event_name |
+------------+--------------+----------+------------+
|        900 | ACCOUNT-A    |        1 | created    |
|        900 | ACCOUNT-A    |        2 | verified   |
+------------+--------------+----------+------------+
2 rows in set (0.00 sec)

Cascade 대상 row가 많으면 transaction이 길어지고 더 많은 record lock, undo와 redo, replication apply 작업을 만들 수 있다. Primary Key 변경을 정기 업무 절차로 허용하기보다 변하지 않는 surrogate key와 변경 가능한 업무 key를 분리하는 것이 좋다.

예제 객체는 다음과 같이 정리한다.

DROP TABLE pk_child_demo;
DROP TABLE pk_parent_demo;
DROP TABLE pk_bigint_demo;
DROP TABLE pk_uuid_char_demo;
DROP TABLE pk_uuid_bin_demo;

실행 결과(MySQL 8.0.x):

mysql> DROP TABLE pk_child_demo;

Query OK, 0 rows affected (0.00 sec)

mysql> DROP TABLE pk_parent_demo;

Query OK, 0 rows affected (0.00 sec)

mysql> DROP TABLE pk_bigint_demo;

Query OK, 0 rows affected (0.00 sec)

mysql> DROP TABLE pk_uuid_char_demo;

Query OK, 0 rows affected (0.01 sec)

mysql> DROP TABLE pk_uuid_bin_demo;

Query OK, 0 rows affected (0.00 sec)

7. 운영 schema에서 Primary Key를 점검하는 방법

운영 점검은 “Primary Key가 있는가”에서 끝나지 않는다. 다음 항목을 schema inventory와 workload 정보에 연결해야 한다.

7.1 Key column과 자료형

information_schema.STATISTICS에서 INDEX_NAME='PRIMARY'인 column 순서를 확인하고, information_schema.COLUMNS에서 type, character set, collation을 확인한다. Composite Primary Key라면 전체 column 폭이 모든 secondary index에 전파된다. 문자열이면 실제 값 길이뿐 아니라 character set과 collation 비교 semantics도 검토한다.

7.2 Secondary index 개수와 크기

넓은 Primary Key의 비용은 secondary index 개수에 비례해 커질 수 있다. 사용되지 않는 인덱스가 있는지 Performance Schema의 table I/O 통계를 참고하되, 관찰 기간에 실행되지 않았다는 이유만으로 즉시 삭제하지 않는다. 월말·분기말 query, 장애 대응 query, foreign key 지원 인덱스가 있을 수 있다.

7.3 삽입 순서와 page split

ID 표본을 시간 순서로 수집하여 새 key가 대체로 증가하는지, 특정 시간 구간에서 역전되는지 확인한다. InnoDB metrics와 storage write latency를 함께 관찰한다. Global counter는 다른 table의 활동도 합산할 수 있으므로 단일 table의 원인으로 바로 귀속하지 않는다. 대표 부하를 재현한 별도 환경에서 ordered ID와 random ID를 같은 row 폭·같은 index 구성으로 비교하는 것이 가장 명확하다.

7.4 변경 시도와 cascade 범위

Audit log, binary log 기반 CDC, application code에서 Primary Key를 UPDATE하는 경로를 찾는다. 외래 키의 ON UPDATE action과 child cardinality를 확인한다. 대량 key 교체가 필요하다면 한 번의 거대한 transaction보다 새 column·mapping·점진 migration을 포함한 schema 변경 계획으로 다룬다.

8. 실패 모드와 흔한 오해

“UUID는 충돌하지 않으므로 가장 좋은 Primary Key다”

유일성은 한 평가 축일 뿐이다. UUID version, generator 품질, binary layout, 문자열 저장 여부, 삽입 locality와 secondary index 증폭을 함께 봐야 한다. 128-bit ID가 필요하다면 CHAR(36)보다 BINARY(16)을 우선 검토하되, 운영 도구와 application 변환 규약도 준비한다.

“AUTO_INCREMENT는 gap이 생기면 고장이다”

Transaction rollback, failed insert, allocation 방식 때문에 sequence에는 gap이 생길 수 있다. Primary Key의 목적은 row 식별이지 gap 없는 업무 번호 보장이 아니다. 연속 번호가 필요하면 별도의 규칙과 직렬화 비용을 명시한다.

“단조 증가 key는 page split이 전혀 없다”

오른쪽 끝 page도 가득 차면 새 page가 필요하다. 장점은 split이 사라진다는 데 있지 않고, 삽입 위치와 working set이 예측 가능한 좁은 범위에 머무는 데 있다. 높은 동시성에서는 right edge 경쟁도 측정해야 한다.

“무작위 key는 쓰기를 분산하므로 항상 확장성이 좋다”

한 MySQL instance 안에서 random key는 writer를 여러 storage node로 자동 분산하지 않는다. 오히려 clustered tree 전역의 page를 dirty하게 만들 수 있다. 분산 ID 생성과 database write scaling을 혼동하지 않는다.

“자연키에 UNIQUE가 있으니 반드시 Primary Key로 써야 한다”

업무상 유일성은 UNIQUE 제약으로도 표현할 수 있다. 자연키가 길거나 바뀔 수 있으면 좁고 불변인 surrogate Primary Key와 분리하는 편이 물리 설계와 업무 규칙을 각각 안정적으로 관리하기 쉽다.

“Primary Key를 UPDATE해도 한 column만 바뀐다”

Clustered 정렬 위치와 모든 secondary row locator가 영향을 받는다. 외래 키 cascade가 있으면 child row까지 변경된다. DDL과 작은 테스트에서 성공하는 것과 대규모 운영 변경의 비용은 별개다.

9. Aurora MySQL에서의 해석

Aurora MySQL에서도 InnoDB 호환 clustered·secondary index의 논리 구조와 Primary Key 폭의 영향은 중요하다. 분산 storage 계층을 사용한다고 해서 넓은 random Primary Key의 B+Tree 유지 비용이나 secondary index 증폭이 사라지지 않는다.

운영에서는 다음 차이를 함께 고려한다.

  • Aurora cluster의 일반적인 write 경로는 writer instance를 통과한다. 여러 reader가 있다고 해서 AUTO_INCREMENT 생성과 table write가 자동으로 multi-writer가 되는 것은 아니다.
  • writer failover 후에도 ID 생성 규칙이 충돌하지 않아야 한다. application-side generator는 node ID, clock rollback과 재시작 시 sequence 복구를 포함해 검증한다.
  • writer와 reader의 cache 상태가 다를 수 있으므로 random key로 넓어진 working set의 영향은 instance별로 관찰한다.
  • 복제·storage 계층의 구현이 일반 MySQL과 다르더라도 secondary index 유지, redo 성격의 변경 작업, lock과 transaction 비용을 무시할 수 없다.
  • Aurora Global Database 같은 다중 리전 구성에서는 “ID를 어디서 생성하는가”와 “어느 리전이 write authority를 갖는가”를 분리한다. 전역 유일 ID가 multi-writer consistency를 대신하지 않는다.
  • Performance Insights 또는 Database Insights, CloudWatch의 write latency·commit latency·Buffer Cache 관련 지표를 statement digest와 함께 비교한다. ID 변경 전후의 처리량만 보고 원인을 단정하지 않는다.

Aurora로 이전한다는 이유만으로 기존 BIGINT를 UUID 문자열로 바꾸는 것은 합리적 기본값이 아니다. 실제 write topology와 ID 생성 요구가 달라지는지부터 확인해야 한다.

10. 설계 의사결정 절차

새 테이블의 Primary Key는 다음 순서로 결정하면 불필요한 복잡도를 줄일 수 있다.

  1. 쓰기 authority를 정의한다. 한 writer가 생성하는가, 여러 application node가 독립 생성하는가, 여러 shard에서 전역 유일해야 하는가?
  2. 정렬 요구를 정의한다. 엄격한 전역 순서가 필요한가, 대략적인 시간 순서면 되는가, 순서가 전혀 필요 없는가?
  3. 가장 좁은 표현을 선택한다. 64-bit 공간으로 충분하면 BIGINT를 우선 검토하고, 128-bit가 필요하면 문자열보다 BINARY(16)을 검토한다.
  4. 불변성을 확인한다. 사람·조직·외부 시스템이 바꿀 수 있는 업무 값을 식별자에서 분리한다.
  5. 실제 byte ordering을 검증한다. 시간 정렬형 ID는 application의 encoding 결과를 byte 단위로 정렬해 MySQL 비교 순서와 일치하는지 확인한다.
  6. 보조 인덱스 증폭을 계산한다. secondary index 개수, 예상 row 수, key 폭과 Buffer Pool 용량을 함께 추정한다.
  7. 대표 부하로 비교한다. 같은 row·index 구성에서 ordered와 random insert의 latency, throughput, page split 관련 지표, index size를 비교한다.
  8. 장애 시나리오를 시험한다. generator 재시작, clock rollback, node ID 중복, writer failover, network partition에서 중복이 생기지 않는지 검증한다.

11. 설계·운영 체크리스트

Schema 설계

  • 업무상 변경 가능한 값은 별도 UNIQUE
  • UUID 계열을 문자열 대신 BINARY(16)

쓰기 경로

  • AUTO_INCREMENT

변경·참조 무결성

  • Primary Key를 UPDATE
  • 외래 키의 ON UPDATE

운영 관측

12. 맺음말

InnoDB에서 좋은 Primary Key는 논리적 유일성과 물리적 효율을 함께 만족해야 한다. 단조 증가 key는 쓰기 locality를 높이고, 좁은 key는 clustered index와 모든 secondary index의 cache density를 개선한다. 변경 불가능한 key는 clustered record 재배치와 참조 cascade를 피하게 하며, 분산 생성 전략은 충돌 방지뿐 아니라 실제 byte 정렬과 장애 복구까지 포함해야 한다.

대부분의 단일-writer OLTP에서는 좁고 불변인 BIGINT AUTO_INCREMENT가 여전히 강한 기본값이다. 분산 생성이 필요할 때도 곧바로 UUID 문자열을 선택하기보다 64-bit 분산 ID와 시간 정렬형 BINARY(16)을 포함한 후보를 같은 schema·같은 workload로 검증해야 한다. 다음 단계에서는 composite index의 column 순서와 selectivity를 살펴보면, Primary Key를 row locator로 포함하는 secondary B+Tree가 실제 query access path와 어떻게 결합되는지 더 구체적으로 이해할 수 있다.

관련 문서 태그 · 카테고리 · 링크 관계 기반 추천
B+Tree 인덱스 구조: root, branch, leaf page와 탐색 비용

InnoDB B+Tree의 root·branch·leaf page 구조와 탐색 비용, 클러스터형·보조 인덱스의 차이를 운영 관점에서 설명한다.

같은 태그 · 같은 카테고리 · 최근 문서 · 키워드 유사 Invisible index 활용: 인덱스 제거 전 안전한 영향도 검증

MySQL Invisible index로 인덱스 제거의 실행 계획과 운영 영향을 사전에 검증하고 안전하게 복구하는 절차를 정리한다.

같은 태그 · 같은 카테고리 · 최근 문서 · 키워드 유사 Change Buffer의 역할: secondary index 변경 지연과 운영상 장단점

InnoDB Change Buffer가 secondary index 변경을 지연 병합하는 방식과 성능 이점, 장애 복구 비용, 운영상 주의점을 정리한다.

같은 태그 · 같은 카테고리 · 인기 문서 · 키워드 유사 Buffer Pool warmup: dump/load와 재시작 직후 성능 안정화 전략

MySQL InnoDB Buffer Pool warmup의 dump/load 동작과 재시작 직후 성능 저하를 줄이는 운영 전략을 정리한다.

같은 태그 · 같은 카테고리 · 인기 문서 · 키워드 유사 Buffer Pool dirty page와 flush 정책: checkpoint와 write pressure

InnoDB Buffer Pool의 dirty page, checkpoint, flush 정책, write pressure를 운영 관점에서 정리한다.

같은 태그 · 같은 카테고리 · 인기 문서 · 키워드 유사 InnoDB data dictionary와 metadata lock의 기본 개념

MySQL 8.0 InnoDB data dictionary와 metadata lock이 DDL, DML, 장애 대응, 온라인 변경 작업에 미치는 영향을 운영 관점에서 정리한다.

같은 태그 · 같은 카테고리 · 인기 문서 · 키워드 유사 InnoDB hidden columns: DB_ROW_ID, DB_TRX_ID, DB_ROLL_PTR의 의미

InnoDB 레코드에 포함되는 hidden column이 clustered index, MVCC, undo log, Primary Key 설계에 어떤 의미를 갖는지 운영 관점에서 정리한다.

같은 태그 · 같은 카테고리 · 인기 문서 · 키워드 유사 MySQL 트랜잭션 처리 개요: autocommit, 명시적 트랜잭션, implicit commit

MySQL에서 autocommit, 명시적 트랜잭션, implicit commit이 실행 경로와 운영 안정성에 미치는 영향을 정리한다.

같은 태그 · 같은 카테고리 · 인기 문서 · 키워드 유사