- Published on
MySQL 8.0 세컨더리 인덱스 폭증 - 히든 PK·커버링으로 IOPS 절감
- Authors
- Name
- 스타차일드
- https://x.com/ETFBITX
운영에서 디스크 IOPS가 먼저 터지고 CPU는 놀고 있는 상황을 자주 봅니다. 특히 MySQL 8.0(InnoDB)에서는 세컨더리 인덱스(보조 인덱스)가 눈덩이처럼 커지면서 버퍼풀 적중률이 떨어지고, 랜덤 읽기 I/O가 늘어나는 패턴이 흔합니다. 문제는 단순히 인덱스를 “많이 만들어서”가 아니라, InnoDB 구조상 세컨더리 인덱스 엔트리 자체가 커질 수밖에 없는 조건이 있기 때문입니다.
이 글은 다음을 목표로 합니다.
- MySQL 8.0에서 세컨더리 인덱스가 커지는 구조적 이유(히든 PK 포함)
- 커버링 인덱스와 쿼리 패턴으로
table lookup(클러스터드 인덱스 재조회) 줄이기 - 불필요한 인덱스를 줄이고, 필요한 인덱스는 “가볍게” 만드는 체크리스트
- 실제로 IOPS를 줄이기 위한 측정과 검증 방법
(운영 장애 디버깅 관점은 Kubernetes CrashLoopBackOff 원인 7가지와 실전 디버깅 글의 접근 방식과 비슷하게, 증상 -> 가설 -> 측정 -> 변경 -> 검증 순서로 진행하는 게 효과적입니다. 여기서는 DB에 적용해보겠습니다.)
왜 MySQL(InnoDB) 세컨더리 인덱스가 폭증하나
InnoDB의 핵심: 데이터는 클러스터드 인덱스에 붙는다
InnoDB 테이블은 내부적으로 클러스터드 인덱스(대개 PK) B-Tree에 데이터 레코드가 저장됩니다. 즉, “테이블”은 곧 “클러스터드 인덱스”입니다.
세컨더리 인덱스는 별도의 B-Tree인데, 여기에는 다음이 저장됩니다.
- 세컨더리 인덱스 키 컬럼들
- 그리고 해당 레코드를 찾아가기 위한 포인터 역할의 PK 값
이 “PK 값이 같이 저장된다”는 점이 세컨더리 인덱스 크기를 결정적으로 키웁니다.
PK가 길면 세컨더리 인덱스가 같이 길어진다
예를 들어,
- PK가
BIGINT(8바이트)인 테이블 - PK가
CHAR(36)UUID(최대 36바이트, 문자셋/콜레이션에 따라 더 커질 수 있음)인 테이블
두 테이블이 동일한 세컨더리 인덱스(예: idx_created_at)를 갖고 있어도, 세컨더리 인덱스 엔트리당 “뒤에 붙는 PK” 길이 차이 때문에 인덱스 페이지 밀도가 크게 달라집니다.
결과적으로:
- 인덱스 페이지에 들어가는 엔트리 수 감소
- B-Tree 높이 증가 가능성
- 버퍼풀에 같은 엔트리를 캐시하기 위해 더 많은 페이지 필요
- 랜덤 I/O 증가(특히 워킹셋이 버퍼풀을 초과하면)
PK가 없으면? 히든 PK가 생긴다
MySQL 8.0에서 PK를 정의하지 않으면 InnoDB는 내부적으로 히든 클러스터드 키(일명 hidden PK) 를 만들어 클러스터링을 수행합니다.
중요한 포인트는 다음입니다.
- PK가 없다고 해서 “세컨더리 인덱스가 PK를 안 들고 있는” 구조가 되지 않습니다.
- InnoDB는 여전히 레코드 포인터가 필요하고, 그 역할을 히든 키가 합니다.
즉 “PK를 정의하지 않으면 인덱스가 가벼워질 것”이라는 기대는 대개 성립하지 않습니다. 오히려 PK 부재는 다음 문제를 같이 부릅니다.
- 애플리케이션에서 안정적인 정렬/페이징 키가 없어짐
- 복제/CDC/백필 등에서 예측 가능한 키 기반 작업이 어려워짐
- 결국 다른 유니크 키를 강제로 쓰거나, 쿼리가 비효율적으로 변함
정리하면, **세컨더리 인덱스 폭증의 1차 원인은 “보조 인덱스 엔트리에 PK가 포함되는 구조”**이고, PK가 길수록 폭증이 가속됩니다.
IOPS가 늘어나는 직접 원인: 세컨더리 인덱스 -> 테이블 재조회
세컨더리 인덱스를 타고 조건을 만족하는 레코드를 찾은 뒤, 실제 필요한 컬럼이 세컨더리 인덱스에 없으면 InnoDB는 PK를 이용해 클러스터드 인덱스를 다시 탐색합니다. 흔히 back to table, table lookup이라고 부르는 단계입니다.
이때 비용이 커지는 이유:
- 세컨더리 인덱스 탐색은 비교적 순차적/지역성이 있을 수 있으나
- PK로 클러스터드 인덱스를 찍는 순간 랜덤 읽기가 늘어남
- 결과 건수가 많을수록 랜덤 읽기가 선형적으로 증가
즉, 세컨더리 인덱스가 커졌다는 사실 자체도 문제지만, 더 큰 문제는 세컨더리 인덱스를 “잘 타는 것처럼 보이지만” 결국 랜덤 I/O를 유발하는 쿼리입니다.
커버링 인덱스로 IOPS 줄이기: 가장 즉효
커버링 인덱스란
쿼리가 필요한 컬럼을 전부 인덱스에서만 읽고 끝내는 방식입니다. 즉 table lookup을 없애거나 최소화합니다.
예시: 주문 목록 API
SELECT order_id, user_id, status, created_at
FROM orders
WHERE user_id = ?
AND created_at >= ?
ORDER BY created_at DESC
LIMIT 50;
이 쿼리에 인덱스가 idx_user_created(user_id, created_at)만 있으면, order_id, status를 가져오기 위해 테이블을 다시 읽을 수 있습니다.
커버링을 노리면 다음처럼 설계합니다.
CREATE INDEX idx_user_created_cover
ON orders (user_id, created_at, status, order_id);
포인트:
WHERE와ORDER BY에 필요한 컬럼을 앞쪽에 둠SELECT에 필요한 나머지 컬럼을 뒤에 붙여 인덱스만으로 결과를 만들게 함
이렇게 하면 디스크 IOPS는 대개 확 줄어듭니다(특히 LIMIT이 작고, 조회가 빈번한 API에서).
커버링 인덱스의 함정: 인덱스가 더 커진다
커버링 인덱스는 “테이블 재조회 IOPS를 줄이는 대신” 세컨더리 인덱스 자체 크기를 늘립니다.
그래서 다음 원칙이 중요합니다.
- 자주 호출되고,
LIMIT이 작고, 지연에 민감한 쿼리부터 적용 - 커버링에 넣는 컬럼은 최소화(특히
TEXT,BLOB, 긴VARCHAR지양) - 같은 목적의 인덱스가 중복되지 않게 정리
즉, 커버링은 만능이 아니라 “IOPS를 돈 주고 사는” 도구입니다. 다만 운영에서는 이 거래가 이득인 경우가 많습니다.
PK 설계로 세컨더리 인덱스 폭증을 늦추기
UUID를 PK로 쓸 때의 현실적인 선택지
UUID를 PK로 쓰면:
- 세컨더리 인덱스 엔트리마다 UUID가 붙음
- 페이지 밀도 감소로 인덱스 크기 증가
- 삽입 시 B-Tree 페이지 분할 가능성 증가(랜덤 UUID일수록)
대안은 보통 세 가지입니다.
- PK는
BIGINT(자동 증가)로 두고 UUID는 별도UNIQUE KEY로 둔다
CREATE TABLE users (
id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
uuid BINARY(16) NOT NULL,
email VARCHAR(255) NOT NULL,
PRIMARY KEY (id),
UNIQUE KEY uk_users_uuid (uuid)
) ENGINE=InnoDB;
- UUID를
BINARY(16)으로 저장해 폭을 줄인다
CHAR(36)대비 저장/비교 비용이 줄어듭니다.
- 순서성이 있는 UUID를 사용한다(예: UUIDv7 계열)
- 페이지 분할과 랜덤 I/O를 줄이는 데 유리합니다.
핵심은 “PK 폭”이 세컨더리 인덱스 전체에 전파된다는 점입니다.
PK를 아예 안 만들면 되나
앞서 말했듯 PK가 없으면 히든 키가 생깁니다. 그리고 운영 관점에서 PK 없는 테이블은 대개 관리 난이도가 올라가므로 추천하지 않습니다.
인덱스 다이어트: 폭증을 막는 가장 확실한 방법
1) 중복/유사 인덱스 제거
다음 두 인덱스는 많은 경우 중복입니다.
(user_id)(user_id, created_at)
두 번째가 있으면 첫 번째는 커버되는 경우가 많습니다(쿼리 패턴에 따라 예외는 존재). 인덱스 하나를 지우면:
- 저장공간 감소
- 쓰기 비용 감소(INSERT/UPDATE/DELETE 시 인덱스 유지 비용)
- 버퍼풀 효율 증가
중복 인덱스는 sys 스키마 뷰나 performance_schema를 활용해 후보를 뽑을 수 있습니다.
2) 안 쓰는 인덱스 제거(사용량 기반)
MySQL 8.0에서는 인덱스 사용 통계를 통해 “거의 안 쓰는 인덱스”를 찾을 수 있습니다.
SELECT
object_schema,
object_name,
index_name,
count_read,
count_write
FROM performance_schema.table_io_waits_summary_by_index_usage
WHERE object_schema = 'app'
AND index_name IS NOT NULL
ORDER BY count_read ASC;
주의:
- 통계는 서버 재시작이나 설정에 따라 리셋될 수 있습니다.
- 배치/월말 작업처럼 “특정 시점에만 필요한 인덱스”가 있을 수 있으니, 관측 기간을 충분히 두세요.
3) 긴 문자열을 인덱싱해야 한다면 prefix 인덱스 고려
예: 이메일을 전부 인덱싱하면 폭이 커집니다.
CREATE INDEX idx_email_prefix ON users (email(16));
단, prefix 인덱스는 선택도가 떨어질 수 있고, 유니크 제약을 완전히 대체하지 못하는 경우가 있으니(요구사항에 따라) 신중해야 합니다.
쿼리 패턴으로 IOPS 줄이기: 인덱스보다 먼저 바꿀 때도 있다
OFFSET 페이징은 I/O를 태운다
SELECT id, created_at
FROM orders
WHERE user_id = ?
ORDER BY created_at DESC
LIMIT 50 OFFSET 5000;
이 방식은 앞의 5000건을 읽고 버리는 비용이 들어서, 인덱스를 타도 I/O가 커집니다.
대신 seek pagination으로 바꿉니다.
SELECT id, created_at
FROM orders
WHERE user_id = ?
AND created_at < ?
ORDER BY created_at DESC
LIMIT 50;
이 패턴은 커버링 인덱스와 결합하면 효과가 큽니다.
필요한 컬럼만 SELECT 하기
SELECT *는 커버링을 불가능하게 만들고, 테이블 재조회를 유발합니다. API 응답에 필요한 컬럼만 고정해서 가져오면 인덱스 설계 여지가 커지고 IOPS가 줄어듭니다.
측정과 검증: “느낌”이 아니라 숫자로 줄이기
실행 계획에서 커버링 여부 확인
EXPLAIN ANALYZE
SELECT order_id, user_id, status, created_at
FROM orders
WHERE user_id = 10
ORDER BY created_at DESC
LIMIT 50;
확인 포인트:
Using index(커버링) 힌트가 보이는지rows examined가 불필요하게 크지 않은지- 실제 실행 시간에서
table lookup단계가 두드러지지 않는지
인덱스 크기 확인
SELECT
table_name,
index_name,
stat_value
FROM mysql.innodb_index_stats
WHERE database_name = 'app'
AND table_name = 'orders'
AND stat_name IN ('size', 'n_leaf_pages')
ORDER BY index_name;
인덱스가 폭증했는지, 커버링 인덱스 추가로 얼마나 증가했는지 수치로 확인합니다.
버퍼풀/IO 관측
SHOW ENGINE INNODB STATUS에서 버퍼풀 히트/읽기 패턴을 점검performance_schema에서 대기 이벤트(특히 파일 I/O) 비중을 확인- 스토리지 레벨에서는 프로비저닝 IOPS 대비 실제 사용률과 지연을 함께 봅니다
운영에서 이런 “측정 -> 변경 -> 재측정” 루프를 자동화해두면, 장애성 이슈뿐 아니라 비용 최적화에도 도움이 됩니다. 재시도/중복 호출 같은 애플리케이션 레벨 비용 최적화는 OpenAI 429와 Rate Limit 헤더로 재시도 설계 글처럼, 결국 관측 가능한 지표를 기준으로 설계해야 효과가 지속됩니다.
실전 체크리스트
세컨더리 인덱스 폭증을 의심할 때
- PK가
UUID또는 긴 문자열인가 - PK가 없어서 히든 키를 쓰는 테이블이 많은가
- 세컨더리 인덱스가 많고, 특히 복합 인덱스가 난립하는가
- 읽기 쿼리는 인덱스를 타는데도 디스크 읽기가 줄지 않는가(테이블 재조회 의심)
IOPS 절감 우선순위
- 상위 트래픽 쿼리에서
SELECT *제거, 필요한 컬럼만 - OFFSET 페이징
->seek pagination - 커버링 인덱스는 “핵심 API 쿼리”부터 최소 컬럼으로
- 중복/미사용 인덱스 제거
- PK 폭 줄이기(가능하면
BIGINT기반 surrogate key, UUID는BINARY(16)등)
마무리
MySQL 8.0에서 세컨더리 인덱스가 커지는 건 “운영 실수”라기보다 InnoDB의 자연스러운 구조적 결과인 경우가 많습니다. 핵심은 다음 한 문장으로 요약됩니다.
- 세컨더리 인덱스 엔트리에는 PK가 붙고, PK가 길수록 인덱스는 더 빨리 커진다.
그리고 IOPS를 줄이는 가장 실전적인 해법은 두 축입니다.
table lookup을 없애는 커버링 인덱스/쿼리 패턴- 인덱스 자체를 줄이고 가볍게 만드는 인덱스 다이어트/PK 설계
인덱스는 “빠르게 읽기 위한 구조”이지만, 동시에 “계속 유지해야 하는 데이터 구조”입니다. 읽기 성능만 보고 늘리면 쓰기 비용과 IOPS로 되돌아옵니다. 측정 가능한 지표를 기준으로, 필요한 곳에만 정확히 투자하는 방식으로 접근하면 MySQL 8.0에서도 충분히 안정적으로 I/O를 컨트롤할 수 있습니다.