[트러블슈팅] 외래키 자동 인덱스와 성능 최적화 분석

문제 상황 발견

Spring Boot와 JPA를 활용한 대용량 데이터 처리 프로젝트에서 예상과 다른 성능 최적화 결과를 경험하게 되었습니다. 인덱스를 추가로 생성했음에도 불구하고 쿼리 성능 개선 효과가 미미했는데, 이에 대한 원인 분석과 해결 과정을 공유하고자 합니다.

---

초기 테스트 환경 및 한계점

대용량 데이터 생성

초기에는 다음과 같은 규모의 데이터를 생성하여 성능 테스트를 진행했습니다:

• Users: 1,000,000건
• Todos: 2,000,000건
• Managers: 2,400,000건
• Comments: 4,000,000건

 

초기 테스트의 한계

대용량 데이터에서 텍스트 검색 기반의 쿼리를 테스트했지만, 인덱스 적용 효과가 거의 나타나지 않았습니다. 이는 MySQL의 일반적인 B-Tree 인덱스로는 LIKE 검색이나 복잡한 텍스트 검색에 한계가 있기 때문입니다. 이러한 검색 최적화를 위해서는 Full-Text Index나 Elasticsearch와 같은 전문 검색 엔진이 필요하다는 결론에 도달했습니다.

---

개선된 테스트 설계

추가 데이터 생성

기존 대용량 데이터에 더해 최근 날짜 기준으로 다음 데이터를 추가 생성했습니다:

• Users: 50명 (최신)
• Todos: 20개 (최신)
• Managers: 각 Todo당 20명씩
• Comments: 각 Todo당 1,000개씩

이를 통해 JOIN이 많이 발생하는 상황에서의 인덱스 효과를 더 정확히 측정할 수 있었습니다.

 

핵심 발견사항

외래키 제약조건의 자동 인덱스 생성

JPA와 MySQL의 외래키 제약조건은 자동으로 인덱스를 생성하여 기본적인 조회 성능을 보장합니다:

테이블	자동 생성 인덱스	생성 이유	최적화되는 작업
users	PRIMARY KEY (id)	기본키	ID 기반 조회
todos	PRIMARY KEY (id)	기본키	ID 기반 조회
	INDEX (user_id)	외래키 제약조건	users와 JOIN
managers	PRIMARY KEY (id)	기본키	ID 기반 조회
	INDEX (user_id)	외래키 제약조건	users와 JOIN
	INDEX (todo_id)	외래키 제약조건	todos와 JOIN
	UNIQUE (todo_id, user_id)	중복 방지 제약조건	중복 체크
comments	PRIMARY KEY (id)	기본키	ID 기반 조회
	INDEX (user_id)	외래키 제약조건	users와 JOIN
	INDEX (todo_id)	외래키 제약조건	todos와 JOIN

 

성능 테스트 결과

테스트 대상 쿼리

복합 조건과 집계를 포함한 다음 쿼리로 성능을 측정했습니다:

 

쿼리 성능 비교 결과

최적화 단계	평균 실행 시간	개선율	주요 변경사항
인덱스 없음	428ms	기준	외래키 인덱스만 존재
created_at 인덱스 추가	436ms	-1.9%	정렬 조건 인덱스
weather 인덱스 추가	386ms	9.8%	WHERE 조건 인덱스
복합 인덱스 적용	387ms	9.6%	두 인덱스 동시 적용

 

분석 결과

1. 외래키 인덱스의 강력한 효과

기본적인 JOIN과 관계 기반 조회는 이미 외래키 인덱스로 충분히 최적화되어 있었습니다. 다음과 같은 작업들이 이미 최적화되어 있었습니다:

작업 유형	쿼리 예시	최적화 상태	성능
기본 JOIN	todos와 users 조인	최적화됨	빠름
외래키 검색	특정 사용자의 todos 조회	최적화됨	빠름
댓글 조회	특정 todo의 comments 조회	최적화됨	빠름
담당자 조회	특정 todo의 managers 조회	최적화됨	빠름
ID 기반 조회	기본키를 통한 단건 조회	최적화됨	매우 빠름

 

2. 제한적인 추가 인덱스 효과

단순한 컬럼별 인덱스 추가는 예상보다 효과가 제한적이었습니다. 이는 다음과 같은 이유들 때문입니다:

• 쿼리의 복잡성 (복합 JOIN + GROUP BY + 집계 함수)
• 단일 인덱스만으로는 전체 성능 개선에 한계
• OR 조건으로 인한 옵티마이저의 인덱스 활용 제약

 

3. OR 조건의 구조적 한계

WHERE weather = 'SUNNY' OR title = '배포_8_iuPtr' 같은 OR 조건은 MySQL 옵티마이저가 인덱스를 효율적으로 활용하기 어려운 구조입니다. 이런 경우 쿼리 구조 자체의 개선이 인덱스 추가보다 더 효과적일 수 있습니다.

---

실무적 교훈

성능 최적화 우선순위

우선순위	최적화 방법	효과	적용 시점
1순위	쿼리 구조 개선	높음	개발 초기
2순위	적절한 연관관계 설정	높음	설계 단계
3순위	캐싱 전략 도입	높음	성능 이슈 발생시
4순위	복합 인덱스 생성	중간	특정 쿼리 최적화
5순위	단일 인덱스 추가	낮음	세부 튜닝

 

실제 서비스에서의 접근법

1. 화면별 데이터 분리: 모든 정보를 한 번에 조회하지 않고, 필요한 정보만 단계적으로 로딩
2. 적절한 페이징: 대용량 데이터는 적절한 크기로 나누어 조회
3. 캐싱 활용: 변경 빈도가 낮은 집계 데이터는 캐시 활용
4. 비동기 처리: 즉시 필요하지 않은 데이터는 백그라운드에서 처리

---

결론

외래키 제약조건이 자동으로 생성하는 인덱스는 이미 상당한 수준의 성능 최적화를 제공합니다. 따라서 추가적인 인덱스 최적화보다는 쿼리 구조 개선, 적절한 데이터 분리, 캐싱 전략 등이 더 효과적인 성능 개선 방법일 수 있습니다.

 

특히 실제 서비스에서는 모든 데이터를 한 번에 조회하려 하지 않고, 사용자가 실제로 필요로 하는 정보를 단계적으로 제공하는 것이 더 나은 사용자 경험과 성능을 동시에 달성할 수 있는 방법입니다.

---

향후 계획

현재의 분석을 바탕으로 다음과 같은 추가 연구를 계획하고 있습니다:

1. 테이블 연관관계 제거: 외래키 제약조건을 제거하고 애플리케이션 레이어에서 의존성을 관리하는 방식의 성능 비교
2. CQRS 패턴 적용: 이벤트 기반 비동기 방식으로 명령과 조회를 분리한 아키텍처에서의 성능 분석
3. Elasticsearch 도입: 복잡한 검색 요구사항에 대한 전문 검색 엔진 활용 방안 연구

이러한 연구를 통해 대용량 데이터 처리와 성능 최적화에 대한 더 깊이 있는 인사이트를 얻고자 합니다.