1. 시작하며: 완벽하다고 믿었던 내 복합 인덱스의 배신
가상 자산 거래소 프로젝트를 진행하며 가장 데이터가 많이 누적될 테이블은 단연 orders (주문 내역) 테이블이었습니다. 유저가 자신의 과거 거래 리스트를 조회할 때, 시스템은 특정 유저의 ID를 조건으로 검색하고, 주문 일시를 기준으로 최신순 정렬 및 페이징 처리를 수행해야 합니다.
이에 대비하여 저는 데이터베이스 설계 단계에서부터 user_id와 order_date를 묶은 복합 인덱스(Multi-Column Index)인 idx_user_date를 생성해 두었습니다. 동등 조건으로 검색 범위를 좁히고, B+Tree 인덱스 구조를 통해 정렬 부하를 완벽히 제거하겠다는 정석적인 전략이었습니다.
// 가상 자산 주문 내역 조회를 위한 JPA Repository Repository 코드
public interface OrderRepository extends JpaRepository<Order, Long> {
@Query(value = "select o from Order o join fetch o.stock where o.user.id = :userId order by o.orderDate desc",
countQuery = "select count(o) from Order o where o.user.id = :userId")
Page<Order> findAllByUserIdWithStock(@Param("userId") Long userId, Pageable pageable);
}연관된 stock(가상자산 종목) 데이터의 N+1 문제를 방지하기 위해 join fetch까지 깔끔하게 적용했고, Order 입장에서 Stock은 @ManyToOne 관계이므로 DB 레벨의 페이징 쿼리(LIMIT)도 안전하게 작동하는 무결점의 코드였습니다. 이제 내 인덱스가 의도대로 잘 작동하는지 EXPLAIN(실행 계획)을 통해 검증할 차례였습니다. 하지만, 모니터러링 화면에는 충격적인 성적표가 찍혔습니다.
2. 충격적인 실행 계획: 왜 ALL(풀 스캔)과 filesort가 뜰까?
인덱스 현황을 조회했을 때 기본키(PRIMARY)와 외래키 인덱스 외에 우리가 설계한 idx_user_date가 선행 컬럼 user_id(Seq 1), 후행 컬럼 order_date(Seq 2)로 정교하게 물리 결합해 있는 것을 확인했습니다. 그러나 실제 조회 쿼리 앞에 EXPLAIN을 붙여 실행한 결과는 끔찍했습니다.
🚨 최초의 EXPLAIN 검증 결과
- type : ALL (Table Full Scan)
- key : NULL (인덱스를 전혀 사용하지 않음)
- rows : 43
- Extra : Using where; Using filesort
명시적으로 복합 인덱스를 지정해 주었음에도 옵티마이저는 이를 철저히 무시한 채 테이블 전체를 뒤지는 ALL 스캔을 선택했고, 인덱스의 정렬 구조를 쓰지 못해 메모리 버퍼에서 소팅 연산을 무겁게 수행하는 Using filesort 경고까지 내뿜고 있었습니다. 기껏 설계한 인덱스 아키텍처가 완전히 무력화된 순간이었습니다.
3. 범인은 43건: 똑똑한 옵티마이저의 영리한 배신
코드를 몇 번이나 뒤지며 자책하던 중, rows: 43이라는 통계 수치에 시선이 멈췄습니다. 당시 개발 및 테스트 초기 단계였던 제 데이터베이스 테이블에는 고작 43건의 주문 데이터만 저장되어 있었습니다.
여기서 MySQL의 핵심 두뇌인 옵티마이저(Optimizer)의 반전 트리거를 깨달았습니다. 데이터베이스 엔진은 비용 기반 최적화(CBO) 모델을 따릅니다. 무조건 인덱스가 있다고 해서 타는 것이 아니라, '인덱스를 타는 비용'과 '풀 스캔을 때리는 비용'을 저울질하여 더 저렴한 경로를 선택합니다.
💡 인덱스 스캔의 숨겨진 비용, Data Lookup
인덱스를 사용한다는 것은 B+Tree 인덱스 노드를 타고 내려가 원하는 값을 찾은 뒤, 그곳에 저장된 PK 값을 들고 다시 실제 데이터 페이지(디스크 블록)로 점프하는 랜덤 I/O(Data Lookup) 과정을 동반합니다. 데이터 모수가 수십 건 정도로 극도로 적을 때는, 인덱스 나무를 타는 수고와 디스크 점프를 반복하느니, 차라리 메모리에 한 번에 테이블 전체를 풀 스캔(ALL)으로 긁어와 직접 정렬(filesort)해 버리는 것이 컴퓨터 공학적으로 훨씬 빠릅니다.
즉, 제가 인덱스를 잘못 설계한 것이 아니었습니다. 데이터가 너무 적어서 옵티마이저가 의도적으로 인덱스를 '패스'했던 것입니다. 면접 단골 질문인 "인덱스를 걸었는데 왜 실행 계획에 ALL이 뜰까요?"에 대한 완벽한 실무적 정답을 제 눈으로 직접 목격한 셈이었습니다.
4. 디테일 타임: 내 테이블에 자동으로 생성된 인덱스들의 정체
원인을 파악한 김에, SHOW INDEX FROM orders; 결과 구조에 나타난 인덱스들의 정체와 데이터베이스 물리 계층의 연관 지식을 확실하게 정리하고 넘어가고자 합니다.
| Key_name | Column_name | 인덱스 유형 및 물리 구조의 비밀 |
|---|---|---|
| PRIMARY | id | 클러스터드 인덱스 (Clustered Index) 저는 이 구조를 명시적으로 선언한 적이 없습니다. 하지만 MySQL InnoDB 엔진은 테이블에 PK가 선언되는 순간, 해당 PK를 기준으로 데이터 행 전체를 물리적으로 정렬하여 B+Tree 사전 구조로 자동 빌드합니다. 최상위 성능을 보장하는 단 하나의 인덱스입니다. |
| idx_user_date | user_id (Seq 1) | 넌클러스터드 복합 인덱스 (Composite Index) 성능 최적화를 위해 컬럼 2개를 하나로 묶어 생성한 인덱스입니다. InnoDB 스토리지 엔진 특성상, 이 복합 인덱스의 리프 노드(Leaf Node)에는 user_id와 order_date뿐만 아니라 논리적 주소 역할을 수행할 실제 PK(id) 값도 물리적으로 자동 포함됩니다. |
| order_date (Seq 2) | ||
| FK9kccy... | stock_code | 외래키 자동 인덱스 JPA로 @ManyToOne 관계를 맺으면 Hibernate가 DDL을 밀어 넣을 때 외래키 제약조건을 형성합니다. 이때 MySQL 엔진은 외래키를 활용한 조인 연산의 효율성을 보장하기 위해 물리적인 외래키용 인덱스를 내부적으로 자동 생성해 줍니다. (단, user_id는 이미 복합 인덱스의 선행 컬럼으로 포함되었기에 중복 생성이 생략되었습니다.) |
5. 상편 마무리에 기하며: 진짜 무대를 위한 빌드업
결국 인덱스의 진정한 성능과 실효성을 검증하기 위해서는, 옵티마이저가 무시할 수 없을 정도의 대용량 데이터 환경(Production 레벨)을 강제로 구축해야 한다는 결론에 도달했습니다.
저는 곧바로 부하 테스트 툴인 JMeter를 장전했습니다. 동시 접속 스레드 100개가 찰나의 순간인 1초 만에 10,000건의 주문 요청을 서버에 폭격하듯 때려 박는 '스파이크 부하 테스트'를 기획한 것입니다. 과연 제 Spring Boot 서버의 비동기 아키텍처는 이 대량의 압박 속에서 무사히 데이터를 적재할 수 있었을까요? 그리고 데이터가 10,000건 이상 쌓인 뒤 데이터베이스의 실행 계획은 어떻게 뒤바뀌었을까요?
대용량 트래픽 폭격의 생생한 결과와, 마침내 베일을 벗은 복합 인덱스의 진짜 위력(하편)에서 이어가도록 하겠습니다!