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

상황

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

콜센터와 학습 센터 담당자들이 학습 현황을 조회하려고 버튼을 눌렀을 때 화면이 멈춘 것처럼 느껴졌다. 상담 중에 화면이 안 뜨면 그대로 고객을 기다리게 만든다.

이런 상황은 "서버가 바빠서 그런 거 아닐까"라고 넘기기 쉽다. 원인을 파악하기 전까지는 나도 그랬다.

슬로우 쿼리 로그부터

막연하게 코드를 뒤지는 건 비효율적이다. 먼저 MySQL 슬로우 쿼리 로그를 켰다.

sql

-- 슬로우 쿼리 로그 활성화 (실행 시간 1초 이상인 쿼리 수집)
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 1;
SET GLOBAL slow_query_log_file = '/var/log/mysql/slow.log';

수집한 로그는 mysqldumpslow로 정렬해서 본다.

bash

# 실행 시간 기준 상위 10개
mysqldumpslow -s t -t 10 /var/log/mysql/slow.log

로그를 보니 학습 이력 조회 쿼리가 반복적으로 잡혔다. 쿼리 자체는 단순해 보였는데 실행 시간이 들쭉날쭉했다. 데이터가 많은 학생의 이력을 조회할수록 느려지는 패턴이었다.

EXPLAIN이 던진 두 가지 힌트

쿼리를 찾았으면 EXPLAIN으로 실행 계획을 본다. 처음엔 결과가 낯설지만 핵심 컬럼 몇 개만 이해하면 된다.

sql

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

id	select_type	table	type	key	rows	Extra
1	SIMPLE	learning_history	ref	idx_student_id	84320	Using where; Using filesort

봐야 할 것들은 이렇다.

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

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

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

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

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

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

text

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

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

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

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

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

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

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

sql

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

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

변경 전

type	key	rows	Extra
ref	idx_student_id	84320	Using where; Using filesort

변경 후

type	key	rows	Extra
ref	idx_student_subject_created	312	Using index condition

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

덤으로 걸린 N+1

인덱스 잡겠다고 쿼리 로그를 들여다보다 엉뚱한 게 하나 더 걸렸다. 학습 이력 목록을 가져온 뒤 각 이력마다 학생 정보를 또 조회하는, 전형적인 N+1이었다. 원래 목표는 아니었지만 이왕 눈에 띈 거 같이 잡았다.

java

// N+1 발생 패턴
List<LearningHistory> histories = historyRepository.findByStudentId(studentId);
for (LearningHistory h : histories) {
    Student student = studentRepository.findById(h.getStudentId()); // N번 추가 쿼리
    // ...
}

JPQL의 JOIN FETCH로 바꿔 한 번의 쿼리로 해결했다.

java

// 개선 후
@Query("SELECT h FROM LearningHistory h JOIN FETCH h.student WHERE h.studentId = :studentId")
List<LearningHistory> findWithStudentByStudentId(@Param("studentId") Long studentId);

각각은 빠른 쿼리여도 이력 수만큼 반복되면 그만큼 왕복 비용이 쌓인다. 평소엔 안 보이다가 데이터가 늘면 슬그머니 커지는, 인덱스 문제와는 또 다른 결의 비용이다.

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

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

상황

슬로우 쿼리 로그부터

EXPLAIN이 던진 두 가지 힌트

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

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

덤으로 걸린 N+1

인덱스로 안 되는 문제는 따로 있었다

결과

마무리