[SQL 튜닝] 비효율적인 페이징을 수행하는 나쁜 SQL 문

비효율적인 페이징을 수행하는 나쁜 SQL 문

현황 분석

| 튜닝 전 실행 계획 |

사원 테이블과 급여 테이블의 내부 조인을 수행하는 쿼리

사원번호가 10,0001 ~ 50,000번에 사이에 해당하는 데이터들을 사원번호 기준으로 그루핑한 뒤 연봉 합계 기준으로 내림차순 정렬 (150번째 데이터부터 10건의 데이터만 제한)

EXPLAIN
SELECT 사원.사원번호, 사원.이름, 사원.성, 사원.입사일자
	FROM 사원, 급여
	WHERE 사원.사원번호 = 급여.사원번호
	AND 사원.사원번호 BETWEEN 10001 AND 500000
	GROUP BY 사원.사원번호
	ORDER BY SUM(급여.연봉) DESC
LIMIT 150,10;

- SQL문 결과 : 총 10건의 결과 출력, 1.59s 소요

- 드라이빙 테이블 - 사원 테이블, 드리븐 테이블 - 급여 테이블

- 사원 테이블과 급여테이블은 각각 기본키 (key 항목 : PRIMARY)로 데이터 접근

- 사원 테이블은 그루핑과 정렬 연산을 위해 임시테이블(Extra 항목 : Using temporary)을 생성한 뒤 정렬 작업(Extra 항목 : Using filesort)을 수행

 

튜닝 수행

- LIMIT 연산으로 10건의 데이터를 가져오기 위해 수십만 건의 데이터 대상으로 조인을 수행한 뒤 그루핑과 정렬 작업 수행

- 전체 데이터를 가져온 뒤 마지막으로 10건의 데이터만 조회하는 게 효율적인 방식인지 고민

 

튜닝 결과

| 튜닝 후 실행 계획 |

급여 테이블에서 GROUP BY 절을 통한 그루핑과 ORDER BY 절을 통한 정렬 작업을 FROM 절의 인라인 뷰로 작성

인라인 뷰에 필요한 데이터 건수만큼 LIMIT 절로 제약을 설정하여 사원 테이블과 조인할 수 있는 데이터 건수를 줄일 수 있음

EXPLAIN
SELECT 사원.사원번호, 사원.이름, 사원.성, 사원.입사일자
	FROM (SELECT 사원번호
		FROM 급여
		WHERE 사원번호 BETWEEN 10001 AND 50000
		GROUP BY 사원번호
		ORDER BY SUM(급여.연봉) DESC
		LIMIT 150,10) 급여, 사원
WHERE 사원.사원번호 = 급여.사원번호;

- 1.59s → 108ms (시간 단축)

- id = 1 인 <derived2> 테이블과 사원 테이블 대상으로 중첩 루프 조인 수행

- 드라이빙 테이블인 <derived2> 테이블은 id가 2에 해당되는 급여 테이블이며, 드리븐 테이블은 사원 테이블

- <derived2> 테이블은 WHERE 절의 사원번호 BETWEEN 10001 AND 50000 조건절에 따라 범위 스캔(type 항목 : range)을 수행하는 인라인 뷰로, 스토리지 엔진에서 가져온 데이터를 임시 테이블(extra 항목: Using temporary)에 상주시켜 정렬(extra 항목: Using filesort) 작업 수행

- 인라인 뷰인 급여 테이블 기준으로 사원 테이블에 반복해 접근하고 WHERE 절의 사원.사원번호 = 급여.사원번호 조건절로 조인 수행

- 드라이빙 테이블은 테이블 풀스캔(type 항목 : ALL), 드리븐 테이블은 기본 키를 활용하여 데이터를 추출(type 항목 : eq_ref), 중첩 루프 조인에 따라 기본 키를 매번 가져오므로 rows 항목에는 1개 데이터에만 접근하는 것으로 출력

 

* 참고

- 업무에 바로쓰는 SQL 튜닝(도서) - 5장 악성 SQL 튜닝으로 전문가 되기