슬로우 쿼리를 EXPLAIN으로 잡은 과정 — 단일 인덱스에서 복합 인덱스로 (3s → 1.4s)

상황

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

콜센터와 학습 센터 담당자들이 학습 현황을 조회하려고 버튼을 눌렀을 때 화면이 멈추는 것처럼 느껴지는 상황이었다. 상담 중에 화면이 안 뜨면 그냥 고객이 기다리게 된다.

처음엔 "서버가 바빠서 그런 거 아닐까"라고 생각하기 쉬운 상황이었다. 실제로 원인을 파악하기 전까지는 그랬다.

슬로우 쿼리 로그부터 시작

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

-- 슬로우 쿼리 로그 활성화 (실행 시간 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로 정렬해서 볼 수 있다.

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

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

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: 84320 — student_id로 찾았는데도 8만 건이 넘는 행을 검사하고 있다
2. Using filesort — ORDER BY created_at을 인덱스 없이 메모리에서 정렬하고 있다

왜 단일 인덱스가 부족했나

student_id 컬럼에는 이미 인덱스가 있었다. 그런데도 느렸던 이유는 인덱스가 너무 넓게 걸려 있었기 때문이다.

idx_student_id로 찾으면 특정 학생의 모든 학습 이력이 나온다. 수년치 데이터가 쌓인 학생이라면 수만 건이 될 수 있다. 거기서 다시 subject_code = 'MATH'를 where 조건으로 걸러내고, created_at 기준으로 정렬한다.

즉, 인덱스가 데이터를 많이 걸렀지만 충분히 좁히지는 못했다.

단일 인덱스: 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가 사라진다.

EXPLAIN 결과 비교

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

변경 전

변경 후

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

N+1도 같이 잡았다

인덱스 작업을 하면서 쿼리 로그를 분석하다 보니 N+1 패턴도 발견됐다.

학습 이력 목록을 가져온 뒤, 각 이력마다 학생 정보를 별도로 조회하는 코드가 있었다.

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

이걸 JPQL의 JOIN FETCH로 변경해서 한 번의 쿼리로 해결했다.

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

N+1은 데이터가 적을 때는 티가 안 나다가, 행이 늘어나면 선형으로 쿼리 수가 증가한다. 이력 100건이면 쿼리 101번, 1000건이면 1001번이다.

파티셔닝은 왜 넣었나

인덱스와 N+1 개선만으로도 응답 시간이 크게 줄었다. 파티셔닝은 추가로 도입한 방어책이었다.

학습 이력 테이블은 시간이 지날수록 계속 쌓이는 구조다. 인덱스가 아무리 효율적이어도 테이블 자체가 수천만 건이 되면 스캔 비용이 다시 올라올 수밖에 없다.

created_at 기준으로 월별 파티셔닝을 적용했다. 최근 3개월 이력 조회가 대부분이었기 때문에, 파티션 프루닝으로 대부분의 쿼리가 전체 테이블이 아닌 최근 파티션만 보게 됐다.

ALTER TABLE learning_history
PARTITION BY RANGE (YEAR(created_at) * 100 + MONTH(created_at)) (
    PARTITION p202301 VALUES LESS THAN (202302),
    PARTITION p202302 VALUES LESS THAN (202303),
    -- ...
    PARTITION p_future VALUES LESS THAN MAXVALUE
);

파티셔닝은 만능이 아니다. 파티션 키가 쿼리 조건에 없으면 오히려 전체 파티션을 스캔해서 더 느려질 수도 있다. EXPLAIN PARTITIONS로 어느 파티션이 선택됐는지 반드시 확인해야 한다.

결과

p95 기준으로 응답 지연이 약 50% 개선됐다. 피크 시간대 상담 화면에서 로딩 지연 관련 이슈가 눈에 띄게 줄었다.

마무리: EXPLAIN은 습관이다

이 경험에서 가장 중요하게 느낀 건 EXPLAIN을 먼저 보는 습관이었다.

"느리다"는 말을 들었을 때 코드를 먼저 열어보는 게 자연스러운 반응이지만, DB 성능 문제는 코드보다 실행 계획에 답이 있는 경우가 많다. 슬로우 쿼리 로그 → EXPLAIN → 인덱스 설계 순서로 접근하면 원인을 훨씬 빠르게 찾을 수 있다.

복합 인덱스 컬럼 순서를 잘못 잡으면 인덱스가 있어도 효과가 없다. 그리고 인덱스가 많을수록 쓰기 성능이 떨어진다. 어떤 쿼리를 가장 자주 쓰는지를 기준으로 인덱스를 설계해야 한다.

쿼리 하나를 제대로 분석하면 수만 건의 불필요한 스캔이 사라진다. 그 시간이 쌓여서 사용자 경험이 달라진다.