[DB] 대용량 페이징 조회 성능 개선 | 민민의 하드디스크 - 티스토리

 

 

매일 쌓이는 데이터를 조회하는 화면에서 수백만 건 이상 데이터 페이징 조회가 필요했다.
단순히 SELECT * (모든 로우 또는 많은 로우들)로 페이징을 처리하면 응답 시간이 급격히 느려졌고, DB 부하도 눈에 띄게 증가했다. (화면 조회조건으로 돌릴 시 대충 10 ~ 20초 이상 걸림), 조회조건에 PK를 사용한다기 보단 기간(날짜 from ~ to)과 공통코드들을 위주로 조회하기 때문에 인덱스가 안 걸려있음.

실무에서 효과가 있었던 방법은 CTE(또는 서브쿼리)로 PK만 먼저 조회한 뒤, 그 결과를 다시 조인해서 상세 컬럼을 조회하는 방식이다.
이 글에서는 왜 이 방식이 빠른지에 대한 원리와 실제 SQL 예시를 정리한다.

---

1. 기존 방식의 문제점 (모든 컬럼을 바로 조회)

SELECT  *
FROM    EMP_LOG
WHERE   USE_YN = 'Y'
ORDER BY REG_DT DESC
OFFSET  :offset ROWS FETCH NEXT :pageSize ROWS ONLY;

문제점

• 테이블 건수: 100만 건 이상
• 컬럼 수가 많고 (VARCHAR, CLOB 포함)
• 정렬 + 페이징 과정에서 불필요한 컬럼까지 모두 읽음

DB 입장에서는

1. 조건에 맞는 전체 ROW 탐색
2. 정렬 수행
3. OFFSET 이전 ROW도 모두 처리
4. 그 과정에서 모든 컬럼을 메모리/디스크에서 읽음

→ 페이징인데도 전체 조회에 가까운 비용 발생

---

2. 개선 방식 – CTE로 PK만 먼저 조회 (실무형 조건 기준)

조회 화면의 조건은 대부분

• PK 조건 ❌
• 기간 조건 (DT FROM ~ TO)
• 공통코드 조건 (COMM_CD)
• 상태값 (USE_YN, STATUS 등)

즉, PK로 바로 거르는 구조가 아니다.
그래서 더더욱 SELECT *(모든 컬럼 또는 불필요한 컬럼들) 페이징은 비효율적이다.

---

예시 상황

• 테이블: EMP_LOG
• 데이터 건수: 100만 건 이상
• 사용 화면: 전 직원 공통 조회 화면
• 조회 조건
  • 등록일자 기간
  • 업무구분 코드
  • 사용 여부

---

Step 1. 조건 + 정렬 기준으로 PK만 페이징

WITH PAGE_PK AS (
    SELECT  EMP_ID
    FROM    EMP_LOG
    WHERE   REG_DT BETWEEN :fromDt AND :toDt
    AND     COMM_CD = :commCd
    AND     USE_YN = 'Y'
    ORDER BY REG_DT DESC, EMP_ID
    OFFSET  :offset ROWS FETCH NEXT :pageSize ROWS ONLY
)
SELECT  E.EMP_ID,
        E.EMP_NM,
        E.DEPT_CD,
        E.COMM_CD,
        E.REG_DT,
        E.STATUS
FROM    EMP_LOG E
JOIN    PAGE_PK P
        ON E.EMP_ID = P.EMP_ID
ORDER BY E.REG_DT DESC;

---

3. 왜 이 방식이 더 효과적인가 (비 PK 조건 기준)

1) 비 PK 조건에서도 인덱스 효율 극대화

• REG_DT, COMM_CD, USE_YN 은 조회 화면에서 가장 흔한 조건
• 이 컬럼들 + PK로 복합 인덱스 구성 가능

CREATE INDEX IDX_EMP_LOG_01
ON EMP_LOG (REG_DT DESC, COMM_CD, USE_YN, EMP_ID);

→ CTE 단계에서 Index Range Scan + 정렬 최소화 가능

2) PK는 "식별용"으로만 사용

• PK는 조건용이 아니라 ROW 식별자 역할
• 상세 조회는 반드시 PK 조인으로 수행

→ 대용량 조건 필터링 + 소량 데이터 조회 분리

3) 실제 화면 페이징 흐름과 동일

1. 조건으로 대상 집합 결정
2. 최신순 / 기준순 정렬
3. 화면에 보여줄 PK n건 결정
4. 그 PK의 상세 정보만 조회

→ 사용자 화면 로직과 DB 처리 흐름이 일치

---

3. 왜 이 방식이 더 빠른가?

1) 불필요한 컬럼 I/O 제거

• CTE 단계에서는 PK 컬럼만 조회
• PK는 보통
  • 크기가 작고
  • 인덱스에 포함되어 있음

→ Index Scan만으로 페이징 처리 가능

2) 정렬 대상 데이터 최소화

• 정렬은 CTE 단계에서 PK + 정렬 컬럼만으로 수행
• 대용량 컬럼(CLOB, 긴 VARCHAR)은 정렬 대상에서 제외

→ Sort 비용 급감

3) 실제 데이터 조회는 "필요한 ROW만"

• 두 번째 SELECT에서는
  • 이미 PK가 n건으로 줄어든 상태
  • 조인 대상이 매우 작음 (ex. 20~50건)

→ Table Access 비용이 거의 무시 가능한 수준

4) 하루 종일 호출되는 화면에서 효과 극대화

• 전 직원 공통 사용 화면
• 짧은 조회 쿼리가 수천~수만 번 호출됨

→ 쿼리 1회당 50ms → 5ms만 줄어도 전체 DB 부하는 큰 차이

---

4. 실행 계획 관점에서의 차이

기존 방식

• TABLE FULL SCAN 또는 대량 TABLE ACCESS
• SORT 영역 사용량 큼
• BUFFER CACHE 점유 증가

개선 방식

• INDEX RANGE SCAN (PK 또는 정렬 인덱스)
• 소량 ROW 조인
• SORT 대상 최소화

---

5. 실무 적용 시 주의사항

1) 정렬 컬럼 + PK 인덱스 필수

-- 예시
CREATE INDEX IDX_EMP_LOG_01
ON EMP_LOG (REG_DT DESC, EMP_ID);

• 정렬 컬럼 + PK 조합 인덱스가 없으면 효과 반감

2) OFFSET이 너무 큰 경우

• OFFSET 100,000 이상이면 이 방식도 느려질 수 있음
• 가능하면
  • Keyset Pagination (마지막 PK 기준 조회) 고려

3) CTE 대신 Inline View도 동일한 원리

SELECT  E.*
FROM    EMP_LOG E
JOIN (
    SELECT EMP_ID
    FROM EMP_LOG
    WHERE USE_YN = 'Y'
    ORDER BY REG_DT DESC
    OFFSET :offset ROWS FETCH NEXT :pageSize ROWS ONLY
) P
ON E.EMP_ID = P.EMP_ID;

---

6. 정리

• 대용량 페이징 조회에서 병목은 "조회 건수"가 아니라 "읽는 컬럼의 양"
• PK만 먼저 페이징 → 상세 컬럼은 필요한 만큼만 조회
• 하루 종일 사용되는 공통 화면일수록 효과가 크다

페이징 = 화면 단위 조회이지, 컬럼까지 페이징할 필요는 없다.

실무 DB 튜닝에서 가장 체감이 컸던 패턴 중 하나였다.