[MySQL] ORDER BY와 LIMIT 사용 시 성능 저하 원인: Reverse Scan과 인덱스 튜닝

by 2carrot

4 min read January 11, 2026

Categories

• development

Tags

• limit
• mysql
• order by
• reverse scan

26년 새해 각오로 블로그를 일기처럼 대부분이 아는 내용일지도 조금 더 자주 작성해 보자는 목표를 가지고 첫 게시물을 올려봅니다.

25년 연말을 맞이하여, 주요 과제는 모두 끝나고 백로그 위주 작업을 하던 중 운영팀에서 자주 사용하는 어드민 메뉴의 특정 필터 사용 시 결과가 나오지 않는 건을 개선하는 티켓을 받았습니다.

개인적으로 이런 티켓은 사용자가 느끼는 속도 지연을 수치화 할 수 있는지 파악 후

해당 수치를 줄일 수 있는 맞춤 작업을 하는게 맞다고 생각합니다.

혹자는 모든 조건을 사용해도 성능이 빨라야 되는거 아니야? 라고 생각할 수 있지만 개발자라면 100% 를 보장하는 코드는 없다라는 생각이 전제되어야 한다고 생각합니다.

사용자가 사용하지 않거나, 성능 이슈가 있다고 생각하지 않는 경우의 수를 포함한 성능 최적화라는건 불가능하지 않나 생각합니다.

그래서 아래 단계로 업무를 진행해보려고 했습니다.

1. 사용자 패턴 분석 및 성능 지연이 있는 케이스 확인
2. (DBA 요청 전) 개발자 선에서 해소 가능한지 확인
3. 1,2번을 종합해서 DBA 분에게 쿼리 검수 요청
4. DBA 분과 협업 하여 최종 적용 방향 결정 후 적용
5. 반영 결과 확인

1. 사용자 패턴 분석 및 성능 지연이 있는 케이스 확인

우선 성능이라는 부분은 주관적인 부분으로 사용자가 지연이라고 느끼는 속도가 재현이 가능한지? 모두가 공감 하는지? 가 중요하다고 생각합니다.

확인해 본 결과 동일 조건 사용 시 100% 확률로 60s 초과 타임아웃이 발생하였고, 이 부분에 모든 공감대가 이루어졌습니다.

우선 운영팀이 사용하는 사유는 결제 실패가 발생한 건을 확인하여, 재결제 처리를 위해 주기적으로 사용하는 필터 조건이었습니다.

사용 조건

• 조회 기간 : 시작 일시 ~ 종료 일시
• 결제 상태 : 결제 실패

두 화면 모두 위와 같은 사용 조건을 사용할 경우 성능이 느려져 결과 조회가 되지 않았기에 해당 조건일때 쿼리를 가지고 DBA 분께 요청 전에 현상 파악 및 개선점을 찾아봤습니다.

2. (DBA 요청 전) 개발자 선에서 해소 가능한지 확인

DBA 랑 협업을 해보신 분들은 아시겠지만, DBA 분들은 저희가 전달하는 쿼리 외에 비지니스 로직이나 왜 그런 쿼리를 쓰는지는 알지 못 합니다.

그저 주어진 쿼리 정보에서 성능이 느려지는 지점을 찾아주시고 개선을 위한 방법을 제공해 주시죠.

하지만, 단순히 개발자의 실수로 index 가 있음에도 타지 않는 경우를 확인 못 하고 DBA 분에게 요청을 하면 민망한 상황이 발생하겠죠?

참고 자료 : 인덱스를 타지 않는 쿼리 10가지 경우

그렇기에 간단한 실행 계획 확인 및 위와 같은 개발자 실수는 없는지 현재 사용자가 필요한 비즈니스 요건인지 정도는 확인하는게 좋다고 생각합니다.

요즘은 ai 를 통해 실행 계획을 전달하거나, 현재 쿼리나 테이블에 존재하는 index 정보를 주면 어느정도 튜닝 가이드를 제공해 줍니다.

그래서 확인한 결과는 아래와 같았습니다.

2-1. 성능 저하 포인트

성능 저하의 케이스는 명확했습니다.

결제 상태가 '실패' 일때만 발생을 하였고, 결제 성공으로 조회하거나, 결제 상태를 모두로 하면 발생하지 않았습니다.

-- 1번 화면
SELECT *
  FROM '테이블_C' c
 WHERE c.'생성일시' BETWEEN STR_TO_DATE('2020-12-15 14:30:00', '%Y-%m-%d %H:%i:%s') AND STR_TO_DATE('2021-12-22 14:30:59', '%Y-%m-%d %H:%i:%s')
   AND c.'상태' != '준비'
   AND c.'결제상태' IN ('실패') -- 이슈 조건
 ORDER BY c.id desc
 LIMIT 0, 50
;
create index IDX01
    on '테이블_C' ('생성일시', '상태');
create index IDX02
    on '테이블_C' ('생성일시', '상태', '결제상태');

이슈 쿼리를 간단히 표현했고, 당시 존재하는 관련 인덱스는 위와 같았습니다.

제가 AI 랑 대화한 결과는 해당 테이블의 ‘결제상태’의 데이터 분포가 아래와 같았기에 ‘실패’ 를 조건을 걸때만 발생한다 생각했습니다.

결제상태	건수
성공	20만
실패	30

3. 1,2번을 종합해서 DBA 분에게 쿼리 검수 요청

이제 개발자로 파악할 부분은 파악했고, DBA 분께 내용을 전달하여, 정확한 원인 및 개선 방향을 들어야할 단계입니다.

DBA 분의 답변은 해당 데이터 분포 상황에 ORDER BY DESC + LIMIT 를 함께 사용했기 때문에

이미 존재하는 아래 인덱스를 타지 않고, 리버스 스캔 발생으로 PK 인덱스 사용 되는 것이 정확한 원인이였습니다.

create index IDX02
    on '테이블_C' ('생성일시', '상태', '결제상태');

우선 DBA 분이 내용을 전달한 방식에 감탄을 했으며, 답변을 그대로 옮겨서 공유드리면 아래와 같습니다.

LIMIT + ORDER BY ASC

해당 조건 레코드 건 수는 30건밖에 없는데, 쿼리 LIMIT은 50건이기 때문에 MySQL 서버는 50개 레코드를 채우기 위해 모든 레코드를 검사합니다.

-- 0.04초 소요
SELECT c.id
  FROM '테이블_C' c
 WHERE c.'생성일시' BETWEEN STR_TO_DATE('2020-12-15 14:30:00', '%Y-%m-%d %H:%i:%s') AND STR_TO_DATE('2021-12-22 14:30:59', '%Y-%m-%d %H:%i:%s')
   AND c.'상태' != '준비'
   AND c.'결제상태' IN ('실패')
 ORDER BY id 
 LIMIT 30;

-- 11.51초 소요
SELECT c.id
  FROM '테이블_C' c
 WHERE c.'생성일시' BETWEEN STR_TO_DATE('2020-12-15 14:30:00', '%Y-%m-%d %H:%i:%s') AND STR_TO_DATE('2021-12-22 14:30:59', '%Y-%m-%d %H:%i:%s')
   AND c.'상태' != '준비'
   AND c.'결제상태' IN ('실패')
 ORDER BY id 
 LIMIT 31;

LIMIT + ORDER BY DESC

내림차순 정렬은 LIMIT 조건을 만족하는 레코드를 찾아도 종료되지 않고 다음 레코드를 계속해서 검색합니다. (아래 쿼리 결과 소요 시간 확인)
반면에 위와 같은 오름차순 정렬은 LIMIT 조건을 만족하는 레코드를 찾으면 검색 범위를 벗어나지 않고 최적화를 통해 쿼리를 즉시 종료합니다.

-- 13.16초 소요
SELECT c.id
  FROM '테이블_C' c
 WHERE c.'생성일시' BETWEEN STR_TO_DATE('2020-12-15 14:30:00', '%Y-%m-%d %H:%i:%s') AND STR_TO_DATE('2021-12-22 14:30:59', '%Y-%m-%d %H:%i:%s')
   AND c.'상태' != '준비'
   AND c.'결제상태' IN ('실패')
 ORDER BY id DESC 
 LIMIT 30;

-- 13.06초 소요
SELECT c.id
  FROM '테이블_C' c
 WHERE c.'생성일시' BETWEEN STR_TO_DATE('2020-12-15 14:30:00', '%Y-%m-%d %H:%i:%s') AND STR_TO_DATE('2021-12-22 14:30:59', '%Y-%m-%d %H:%i:%s')
   AND c.'상태' != '준비'
   AND c.'결제상태' IN ('실패')
 ORDER BY id DESC 
 LIMIT 31;

4. DBA 분과 협업 하여 최종 적용 방향 결정 후 적용

위와 같이 현재 데이터 분포를 봤을때 LIMIT(페이징)과 ORDER BY DESC(내림차순 정렬) 을 제거할 수는 없기에 2가지 중 첫번째 의견을 적용하기로 결정하였습니다.

1,2안 중 1안 선택 사유

‘테이블_C’ 테이블에 (‘결제상태’, ‘생성일시’, ‘상태’)구성의 인덱스를 생성하면 좋겠지만, MySQL 서버에서 인덱스 컨디션 푸시다운(ICP, Index Condition Pushdown)이라는 최적화가 적용되기 때문에 현재는 굳이 추가할 필요가 없을 것 같습니다.

-- 1안. 기존 인덱스 힌트 추가
...
 FROM '테이블_C' c use index(IDX02)
...
-- 2안. 신규 인덱스 추가
create index IDX03
    on '테이블_C' ('결제상태', '생성일시', '상태');

5. 반영 결과 확인

반영 후 아래와 같은 개선 효과를 공유하며 행복한 연말을 맞이하였습니다.

개선 효과

as-is : 60s 이상 조회가 불가하여 error 발생
to-be : 전체 기간 + 결제실패 조회해도 200ms 미만으로 응답

마무리

다소 긴 내용이였지만 제가 하고 싶은 얘기는 두가지 입니다.

1. 쿼리 관련 성능 개선 업무를 대하는 개발자의 자세
2. 오류에 대한 정확한 원인 이해가 중요함

만약 지금 같이 일하는 유능한 DBA 가 없는 환경이였다면,

데이터 분포가 원인이라고 단정 짓고, 이후 비슷한 상황에서 오름차순 정렬로 해결 가능함에도 무조건 인덱스 추가만 진행했을 것을 생각하니 끔찍합니다.

최대한 빠른 시일내에 새롭고 캐주얼한 주제로 돌아오겠습니다.

그럼 이만. 🥕👋🏼🖐🏼

Share Tweet LinkedIn Reddit