인덱스가 있는데도 쿼리가 느릴 때

교육 플랫폼 차세대 LMS를 개발할 때였다. 유아·초등·중학 서비스를 단일 애플리케이션으로 운영하던 구조였는데, 학습 이력과 리포트 조회 화면에서 응답이 3초를 넘기는 경우가 생겼다. 단순히 피크타임이니 응답이 느리겠지 하고 넘겼었다.

EXPLAIN이 던진 두 가지 힌트

그런데 시간대와 상관없이 계속 반복되길래, 문제의 조회 쿼리를 EXPLAIN으로 떠봤다. 처음엔 결과가 낯설지만 핵심 컬럼 몇 개만 이해하면 된다.

EXPLAIN SELECT * FROM learning_history
WHERE student_id = 12345
  AND subject_code = 'MATH'
ORDER BY created_at DESC;

봐야 할 것들은 이렇다.

• type: 접근 방식. ALL이면 풀 스캔, ref나 range면 인덱스 사용 중
• key: 실제로 사용된 인덱스. NULL이면 인덱스를 못 찾은 것
• rows: 쿼리 실행을 위해 검사한 예상 행 수. 클수록 느리다
• Extra: Using filesort는 정렬을 인덱스 없이 별도로 처리한다는 뜻

위 결과에서 두 가지가 눈에 띄었다.

1. rows가 84,320이다. student_id로 찾았는데도 8만 건이 넘는 행을 검사하고 있다는 뜻이다.
2. Using filesort가 떴다. ORDER BY created_at을 인덱스 없이 메모리에서 정렬하고 있다는 뜻이다.

인덱스가 있는데 왜 8만 건을 훑을까

범인은 이미 잡혀 있던 student_id 단일 인덱스였다. 인덱스를 타고 있으니 겉보기엔 멀쩡한데, 실은 이 인덱스가 너무 넓게 걸려 있었다.

idx_student_id로 들어가면 그 학생의 학습 이력이 통째로 나온다. 수년치 쌓인 학생이면 수만 건이다. DB는 일단 그 수만 건을 다 끌어올린 다음에야 subject_code = 'MATH'를 걸러내고, 그러고도 정렬할 인덱스가 없어 created_at 기준으로 filesort까지 돌린다. 인덱스가 데이터를 줄여주긴 했지만, 정작 결정적으로 좁혀주지는 못한 거다.

단일 인덱스: student_id
→ 해당 학생의 모든 이력 조회 (N건)
→ N건 중 subject_code 필터링
→ 남은 결과를 created_at으로 정렬 (filesort)

복합 인덱스 설계: 컬럼 순서가 중요하다

해결책은 student_id, subject_code, created_at을 묶은 복합 인덱스였다. 그런데 순서가 중요하다.

MySQL 복합 인덱스는 왼쪽에서 오른쪽 순서로 활용된다. 첫 번째 컬럼 없이 두 번째 컬럼만으로는 인덱스를 탈 수 없다.

컬럼 순서는 이렇게 잡았다.

1. 등치 조건(=)을 먼저 둔다. WHERE student_id = ?처럼 값이 하나로 고정되는 조건이다.
2. 범위 조건(>, <, BETWEEN)은 뒤에 둔다. 범위 조건 이후의 컬럼은 인덱스 정렬 이점을 못 챙기기 때문이다.
3. ORDER BY 컬럼을 범위 조건 뒤에 붙이면 filesort 제거 가능

이 쿼리에서 조건을 분류하면 student_id = ?와 subject_code = ?는 등치, ORDER BY created_at DESC는 정렬이다.

-- 최종 복합 인덱스
CREATE INDEX idx_student_subject_created
ON learning_history (student_id, subject_code, created_at DESC);

이렇게 만들면 인덱스가 student_id + subject_code로 데이터를 좁힌 뒤 created_at 순서로 이미 정렬된 상태로 데이터를 반환한다. filesort가 사라진다.

변경 전

변경 후

rows가 84,320에서 312로 줄었고 Using filesort도 사라졌다.

결과

3초가 걸리던 조회가 평균 1.4초로 떨어졌다. p95로 보면 5초가 넘던 게 2.5초 안쪽으로, 체감상 절반 가까이 줄었다. EXPLAIN이 8만 건을 훑던 게 312건이 되었다.

이 일 이후로 "느리다"는 말을 들으면 코드보다 먼저 최종 끝단인 DB의 쿼리부터 확인하려고 EXPLAIN을 먼저 띄우는 습관이 생겼다.