728x90
반응형
매일 쌓이는 데이터를 조회하는 화면에서 수백만 건 이상 데이터 페이징 조회가 필요했다.
단순히 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 입장에서는
- 조건에 맞는 전체 ROW 탐색
- 정렬 수행
- OFFSET 이전 ROW도 모두 처리
- 그 과정에서 모든 컬럼을 메모리/디스크에서 읽음
→ 페이징인데도 전체 조회에 가까운 비용 발생
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) 실제 화면 페이징 흐름과 동일
- 조건으로 대상 집합 결정
- 최신순 / 기준순 정렬
- 화면에 보여줄 PK n건 결정
- 그 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 튜닝에서 가장 체감이 컸던 패턴 중 하나였다.
728x90
반응형