[MySQL] PK, 어떤 데이터 타입이 제일 적절할까?

선상원·2025년 2월 1일

DBA PK mysql

mysql

목록 보기

10/12

오늘의 주제는 “PK 성능 테스트” 입니다.

💡 들어가며

MySQL의 경우 Oracle / MSSQL과 달리 클러스터드 인덱스를 사용하면서 내부적으로 논리적인 키값을 사용하지 않고, 테이블의 Primary Key 값을 통해 구성되는 것이 특징입니다.

그렇기 때문에 MySQL을 사용할 경우 PK의 대상이 되는 데이터 타입이 중요합니다.

대부분의 엔지니어분들은 "INT" 혹은 "VARCHAR" 타입을 사용하여 키를 설정하는데요, 과거에는 CHAR / VARCHAR 둘 중 어떤 데이터 타입이 더 효율적인지가 화제였다고 합니다.
(물론 테스트 결과, 동일했다 라는 정보가 더 많았습니다.)

이처럼 해당 글에서는 어떤 데이터 타입으로 기본 키(Primary Key)를 설정하면 좋을지 테스트를 해보려고 합니다.

🔧 테스트 환경

1. 운영 환경

▶ Aurora MySQL 3.03 (MySQL 8.0.26)

2. 비교 대상

▶ bigint auto_increment
▶ varchar utf8mb4_unicode_ci
▶ varchar latin1_general_ci

3. 테이블 구조

▉ 원본 테이블 구조

▶ 약 1억 개의 데이터가 저장된 테이블
▶ PK: INT, AUTO_INCREMENT
▶ Secondary Index: 10개

▉ 테스트 테이블 구조

xxx_bigint
▶ PK: BIGINT, AUTO_INCREMENT
▶ Secondary Index: 10개

xxx_utf8mb4
▶ PK: VARCHAR utf8mb4_unicode_ci
▶ Secondary Index: 10개
▶ 기존 PK였던 인조키 제거

xxx_latin1
▶ PK: VARCHAR latin1_general_ci
▶ Secondary Index: 10개
▶ 기존 PK였던 인조키 제거

📋 테스트 시나리오

다음 4가지 스캔 방식을 기준으로 성능을 테스트합니다.

INDEX FULL SCAN - 첫 번째 Leaf 노드부터 마지막 Leaf 노드까지 전체 스캔 방식
TABLE FULL SCAN - 테이블의 첫 번째 레코드부터 마지막 레코드까지 전체 스캔 방식
INDEX RANGE SCAN - 필요한 범위만 탐색하는 인덱스 스캔 방식
PK RANDOM ACCESS - 물리적으로 떨어진 페이지에 랜덤 접근 방식

⚠️ 테스트 유의사항

테스트 수행 방법에 맞춰 테스트를 진행할 경우, 데이터가 캐시에 저장되어 테스트 목적과 다른 결과가 나올 수 있으므로 회차가 변경될 때마다 인스턴스 재부팅을 통해 캐시 초기화를 진행했습니다.

MySQL의 경우 Oracle / MSSQL과 달리 실행계획을 정확히 알 수 없다는 것이 특징입니다. 그렇기 때문에 정확한 측정이 불가능하여 프로파일을 통한 execute 시간만을 체크했습니다.

🎯 해당 결과값만으로는 부정확한 테스트가 될 확률이 높지만, 어느 정도 납득할 수 있는 결과가 나올 것으로 추정됩니다.

🧪 테스트 준비: 데이터 INSERT

-- TEST DATA INSERT (1개 테이블마다 인스턴스 재부팅)
INSERT INTO test_database.xxx_bigint (`a`, `b`, `c`, `d`, `e`, `f`, `g`, `h`, `i`, `j`, `k`, `l`, `m`, `n`, `o`, `p`, `q`, `r`, `s`, `t`, `u`, `v`, `w`, `x`, `y`, `z`)
SELECT `a`, `b`, `c`, `d`, `e`, `f`, `g`, `h`, `i`, `j`, `k`, `l`, `m`, `n`, `o`, `p`, `q`, `r`, `s`, `t`, `u`, `v`, `w`, `x`, `y`, `z`
FROM test_database.origin_table
ORDER BY 1 DESC
LIMIT 1000000;

INSERT INTO test_database.xxx_utf8mb4 (`a`, `b`, `c`, `d`, `e`, `f`, `g`, `h`, `i`, `j`, `k`, `l`, `m`, `n`, `o`, `p`, `q`, `r`, `s`, `t`, `u`, `v`, `w`, `x`, `y`, `z`)
SELECT `a`, `b`, `c`, `d`, `e`, `f`, `g`, `h`, `i`, `j`, `k`, `l`, `m`, `n`, `o`, `p`, `q`, `r`, `s`, `t`, `u`, `v`, `w`, `x`, `y`, `z`
FROM test_database.origin_table
ORDER BY 1 DESC
LIMIT 1000000;

INSERT INTO test_database.xxx_latin1 (`a`, `b`, `c`, `d`, `e`, `f`, `g`, `h`, `i`, `j`, `k`, `l`, `m`, `n`, `o`, `p`, `q`, `r`, `s`, `t`, `u`, `v`, `w`, `x`, `y`, `z`)
SELECT `a`, `b`, `c`, `d`, `e`, `f`, `g`, `h`, `i`, `j`, `k`, `l`, `m`, `n`, `o`, `p`, `q`, `r`, `s`, `t`, `u`, `v`, `w`, `x`, `y`, `z`
FROM test_database.origin_table
ORDER BY 1 DESC
LIMIT 1000000;

✍ 결과

xxx_bigint
▶ 1회차: 3m 12.45s (executing: 192.37s)
▶ 2회차: 3m 9.91s (executing: 189.87s)
▶ 3회차: 2m 51.99s (executing: 171.94s)

xxx_utf8mb4
▶ 1회차: 3m 33.78s (executing: 213.66s)
▶ 2회차: 3m 29.52s (executing: 209.43s)
▶ 3회차: 3m 18s (executing: 198.22s)

xxx_latin1
▶ 1회차: 3m 24.36s (executing: 204.25s)
▶ 2회차: 3m 26.48s (executing: 206.37s)
▶ 3회차: 2m 58.86s (executing: 178.78s)

🧪 테스트 1: TABLE FULL SCAN

SELECT *
FROM test_database.xxx_bigint
WHERE group_code = 'g20240101'
  AND user_number LIKE '%010%'
LIMIT 10000;

SELECT *
FROM test_database.xxx_utf8mb4
WHERE group_code = 'g20240101'
  AND user_number LIKE '%010%'
LIMIT 10000;

SELECT *
FROM test_database.xxx_latin1
WHERE group_code = 'g20240101'
  AND user_number LIKE '%010%'
LIMIT 10000;

📈 실행계획

xxx_bigint (1,2,3회차 모두 동일한 결과)

id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	xxx_bigint	ref	user_id, user_id_2, user_id_3, user_id_4	user_id_4	88	const	67218	11.11	Using where

xxx_utf8mb4 (1,2,3회차 모두 동일한 결과)

id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	xxx_utf8mb4	ref	user_id, user_id_2, user_id_3, user_id_4	user_id_2	88	const	71398	11.11	Using where

xxx_latin1 (1,2,3회차 모두 동일한 결과)

id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	xxx_latin1	ref	user_id, user_id_2, user_id_3, user_id_4	user_id_2	88	const	71398	11.11	Using where

✍ 결과

1차 테스트
▶ xxx_bigint: 0.33s / 0.32s / 0.32s
▶ xxx_utf8mb4: 0.46s / 0.43s / 0.42s
▶ xxx_latin1: 0.40s / 0.37s / 0.37s

2차 테스트
▶ xxx_bigint: 0.32s / 0.32s / 0.31s
▶ xxx_utf8mb4: 0.45s / 0.41s / 0.44s
▶ xxx_latin1: 0.39s / 0.38s / 0.37s

3차 테스트
▶ xxx_bigint: 0.33s / 0.30s / 0.31s
▶ xxx_utf8mb4: 0.44s / 0.43s / 0.41s
▶ xxx_latin1: 0.36s / 0.37s / 0.37s

🧪 테스트 2: INDEX FULL SCAN

SELECT user_address, COUNT(*)
FROM test_database.xxx_bigint
GROUP BY user_address
ORDER BY COUNT(*) DESC
LIMIT 100;

SELECT user_address, COUNT(*)
FROM test_database.xxx_utf8mb4
GROUP BY user_address
ORDER BY COUNT(*) DESC
LIMIT 100;

SELECT user_address, COUNT(*)
FROM test_database.xxx_latin1
GROUP BY user_address
ORDER BY COUNT(*) DESC
LIMIT 100;

📈 실행계획

xxx_bigint (1,2,3회차 모두 동일한 결과)

id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	xxx_bigint	ALL	NULL	NULL	NULL	NULL	757645	100.00	Using temporary; Using filesort

xxx_utf8mb4 (1,2,3회차 모두 동일한 결과)

id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	xxx_utf8mb4	ALL	NULL	NULL	NULL	NULL	899563	100.00	Using temporary; Using filesort

xxx_latin1 (1,2,3회차 모두 동일한 결과)

id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	xxx_latin1	ALL	NULL	NULL	NULL	NULL	945490	100.00	Using temporary; Using filesort

✍ 결과

1차 테스트
▶ xxx_bigint: 2.25s / 2.22s / 2.20s
▶ xxx_utf8mb4: 2.24s / 2.24s / 2.24s
▶ xxx_latin1: 2.21s / 2.19s / 2.17s

2차 테스트
▶ xxx_bigint: 2.28s / 2.24s / 2.27s
▶ xxx_utf8mb4: 2.26s / 2.24s / 2.21s
▶ xxx_latin1: 2.26s / 2.22s / 2.23s

3차 테스트
▶ xxx_bigint: 2.30s / 2.27s / 2.28s
▶ xxx_utf8mb4: 2.33s / 2.33s / 2.29s
▶ xxx_latin1: 2.31s / 2.27s / 2.27s

🧪 테스트 3: INDEX RANGE SCAN

SELECT category, SUM(price), COUNT(*)
FROM test_database.xxx_bigint FORCE KEY(user_id)
WHERE group_code LIKE 'g202401%'
GROUP BY category;

SELECT category, SUM(price), COUNT(*)
FROM test_database.xxx_utf8mb4 FORCE KEY(user_id)
WHERE group_code LIKE '202401%'
GROUP BY category;

SELECT category, SUM(price), COUNT(*)
FROM test_database.xxx_latin1 FORCE KEY(user_id)
WHERE group_code LIKE '202401%'
GROUP BY category;

📈 실행계획

xxx_bigint (1,2,3회차 모두 동일한 결과)

id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	xxx_bigint	range	user_id	user_id	90	NULL	229760	100.00	Using index condition; Using temporary

xxx_utf8mb4 (1,2,3회차 모두 동일한 결과)

id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	xxx_utf8mb4	range	PRIMARY, user_id, user_id_2	PRIMARY	90	NULL	219272	100.00	Using where; Using temporary

xxx_latin1 (1,2,3회차 모두 동일한 결과)

id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	xxx_latin1	range	PRIMARY, user_id, user_id_2	PRIMARY	24	NULL	219272	100.00	Using where; Using temporary

✍ 결과

1차 테스트
▶ xxx_bigint: 0.48s / 0.45s / 0.45s
▶ xxx_utf8mb4: 0.24s / 0.22s / 0.22s
▶ xxx_latin1: 0.21s / 0.20s / 0.20s

2차 테스트
▶ xxx_bigint: 0.48s / 0.46s / 0.46s
▶ xxx_utf8mb4: 0.24s / 0.22s / 0.22s
▶ xxx_latin1: 0.22s / 0.20s / 0.20s

3차 테스트
▶ xxx_bigint: 0.48s / 0.46s / 0.45s
▶ xxx_utf8mb4: 0.23s / 0.22s / 0.22s
▶ xxx_latin1: 0.21s / 0.20s / 0.19s

🧪 테스트 4: PK RANDOM ACCESS

SELECT COUNT(*)
FROM (
    SELECT b.user_id, b.user_name, b.group_code
    FROM test_database.xxx_middle_bigint a
    JOIN test_database.xxx_bigint b ON a.middle_user_id = b.user_id
) a;

SELECT COUNT(*)
FROM (
    SELECT b.user_id, b.user_name, b.group_code
    FROM test_database.xxx_middle_utf8mb4 a
    JOIN test_database.xxx_utf8mb4 b ON a.middle_user_id = b.user_id
) a;

SELECT COUNT(*)
FROM (
    SELECT b.user_id, b.user_name, b.group_code
    FROM test_database.xxx_middle_latin1 a
    JOIN test_database.xxx_latin1 b ON a.middle_user_id = b.user_id
) a;

📈 실행계획

xxx_bigint (회차별 차이 발생)
1회차:

id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	a	index	middle_user_id	middle_user_id	90	NULL	94932	100.00	Using index
1	SIMPLE	b	eq_ref	user_id	user_id	90	test_database.a.middle_user_id	1	100.00	Using index

2회차:

id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	a	index	middle_user_id	middle_user_id	90	NULL	101574	100.00	Using index
1	SIMPLE	b	eq_ref	user_id	user_id	90	test_database.a.middle_user_id	1	100.00	Using index

3회차:

id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	a	index	middle_user_id	middle_user_id	90	NULL	93636	100.00	Using index
1	SIMPLE	b	eq_ref	user_id	user_id	90	test_database.a.middle_user_id	1	100.00	Using index

xxx_utf8mb4 (회차별 차이 발생)
1회차:

id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	a	index	middle_user_id	middle_user_id	90	NULL	101007	100.00	Using index
1	SIMPLE	b	eq_ref	user_id	user_id	90	test_database.a.middle_user_id	1	100.00	Using index

2회차:

id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	a	index	middle_user_id	middle_user_id	90	NULL	94932	100.00	Using index
1	SIMPLE	b	eq_ref	user_id	user_id	90	test_database.a.middle_user_id	1	100.00	Using index

3회차:

id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	a	index	middle_user_id	middle_user_id	90	NULL	98091	100.00	Using index
1	SIMPLE	b	eq_ref	user_id	user_id	90	test_database.a.middle_user_id	1	100.00	Using index

xxx_latin1 (회차별 차이 발생)
1회차:

id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	a	index	middle_user_id	middle_user_id	90	NULL	99711	100.00	Using index
1	SIMPLE	b	eq_ref	user_id	user_id	90	test_database.a.middle_user_id	1	100.00	Using index

2회차:

id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	a	index	middle_user_id	middle_user_id	90	NULL	99387	100.00	Using index
1	SIMPLE	b	eq_ref	user_id	user_id	90	test_database.a.middle_user_id	1	100.00	Using index

3회차:

id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	a	index	middle_user_id	middle_user_id	90	NULL	94041	100.00	Using index
1	SIMPLE	b	eq_ref	user_id	user_id	90	test_database.a.middle_user_id	1	100.00	Using index

✍ 결과

1차 테스트
▶ xxx_bigint: 0.51s / 0.49s / 0.49s
▶ xxx_utf8mb4: 0.58s / 0.56s / 0.55s
▶ xxx_latin1: 0.35s / 0.32s / 0.32s

2차 테스트
▶ xxx_bigint: 0.51s / 0.51s / 0.50s
▶ xxx_utf8mb4: 0.58s / 0.54s / 0.55s
▶ xxx_latin1: 0.33s / 0.32s / 0.32s

3차 테스트
▶ xxx_bigint: 0.52s / 0.50s / 0.50s
▶ xxx_utf8mb4: 0.58s / 0.54s / 0.56s
▶ xxx_latin1: 0.35s / 0.31s / 0.32s

📊 최종 결과 정리

스캔 방식별 최적 데이터 타입

SCAN 방식	최적 테이블
INDEX FULL SCAN	xxx_bigint
TABLE FULL SCAN	xxx_latin1
INDEX RANGE SCAN	xxx_latin1
PK RANDOM ACCESS	xxx_latin1

💡 결론

테스트 결과, varchar latin1_general_ci 방식이 INDEX FULL SCAN 방식을 제외하고 제일 우수한 성능을 보였습니다.

해당 테스트 결과를 토대로 MySQL에서 Primary Key를 설정한다면,
특히 스캔 방식이 다양한 환경에서는 varchar latin1_general_ci 설정이 도움이 될 수 있을 것 같습니다.

📝 마무리

많은 기술 블로그를 보면 "정수형이 좋다" 또는 "비즈니스에 따라서 다르게 설계해야 한다" 라는 내용이 가장 많았으나 실제 스트레스 테스트를 통한 지표는 많지 않아 의문이 많았습니다.
이번 테스트를 통해서 스캔 방식에 따른 성능 차이가 발생한다는 것을 알 수 있었으며 이를 통해 추후 비즈니스 설계 과정에서 해당 내용을 참고하면 좋을 것 같습니다.

💡 핵심 포인트

▶ MySQL은 클러스터드 인덱스를 PK 기반으로 구성 - PK 데이터 타입 선택이 중요
▶ 스캔 방식에 따라 성능 차이 발생 - TABLE FULL, INDEX FULL, INDEX RANGE, PK RANDOM ACCESS
▶ latin1_general_ci가 대부분의 스캔 방식에서 우수 - 특히 RANGE SCAN과 RANDOM ACCESS
▶ 비즈니스 요구사항과 함께 고려 - 무조건적인 적용보다는 상황에 맞는 선택 필요

선상원

쉼 없는 고민과 학습을 통해 가장 효율적인 데이터베이스 관리 방안을 찾고자 노력하는 DBA 입니다.

이전 포스트

[MySQL] SQL문 최적화

다음 포스트

[MySQL] PK, 어떤 데이터 타입이 제일 적절할까?

mysql

오늘의 주제는 “PK 성능 테스트” 입니다.

💡 들어가며

🔧 테스트 환경

1. 운영 환경

2. 비교 대상

3. 테이블 구조

▉ 원본 테이블 구조

▉ 테스트 테이블 구조

📋 테스트 시나리오

⚠️ 테스트 유의사항

🎯 해당 결과값만으로는 부정확한 테스트가 될 확률이 높지만, 어느 정도 납득할 수 있는 결과가 나올 것으로 추정됩니다.

🧪 테스트 준비: 데이터 INSERT

✍ 결과

🧪 테스트 1: TABLE FULL SCAN

📈 실행계획

xxx_bigint (1,2,3회차 모두 동일한 결과)

xxx_utf8mb4 (1,2,3회차 모두 동일한 결과)

xxx_latin1 (1,2,3회차 모두 동일한 결과)

✍ 결과

🧪 테스트 2: INDEX FULL SCAN

📈 실행계획

✍ 결과

🧪 테스트 3: INDEX RANGE SCAN

📈 실행계획

✍ 결과

🧪 테스트 4: PK RANDOM ACCESS

📈 실행계획

✍ 결과

📊 최종 결과 정리

스캔 방식별 최적 데이터 타입

💡 결론

📝 마무리

💡 핵심 포인트

[MySQL] SQL문 최적화

[MySQL] 아직도 IN 연산자만 고집하는 엔지니어를 위한 EXISTS 파헤치기

0개의 댓글

[MySQL] PK, 어떤 데이터 타입이 제일 적절할까?

mysql

오늘의 주제는 “PK 성능 테스트” 입니다.

💡 들어가며

🔧 테스트 환경

1. 운영 환경

2. 비교 대상

3. 테이블 구조

▉ 원본 테이블 구조

▉ 테스트 테이블 구조

📋 테스트 시나리오

⚠️ 테스트 유의사항

🎯 해당 결과값만으로는 부정확한 테스트가 될 확률이 높지만, 어느 정도 납득할 수 있는 결과가 나올 것으로 추정됩니다.

🧪 테스트 준비: 데이터 INSERT

✍ 결과

🧪 테스트 1: TABLE FULL SCAN

📈 실행계획

xxx_bigint (1,2,3회차 모두 동일한 결과)

xxx_utf8mb4 (1,2,3회차 모두 동일한 결과)

xxx_latin1 (1,2,3회차 모두 동일한 결과)

✍ 결과

🧪 테스트 2: INDEX FULL SCAN

📈 실행계획

✍ 결과

🧪 테스트 3: INDEX RANGE SCAN

📈 실행계획

✍ 결과

🧪 테스트 4: PK RANDOM ACCESS

📈 실행계획

✍ 결과

📊 최종 결과 정리

스캔 방식별 최적 데이터 타입

💡 결론

📝 마무리

💡 핵심 포인트

[MySQL] SQL문 최적화

[MySQL] 아직도 IN 연산자만 고집하는 엔지니어를 위한 EXISTS 파헤치기

0개의 댓글

[MySQL] SQL문 최적화