[SQL 튜닝] 처음부터 모든 데이터를 가져오는 나쁜 SQL 문

처음부터 모든 데이터를 가져오는 나쁜 SQL 문

현황 분석

| 튜닝 전 실행 계획 |

사원번호가 10,0001번부터 10,100번까지인 사원들의 평균연봉과 최고연봉, 최저연봉을 구하는 쿼리FROM 절의 급여 테이블에 사원별 평균/최고/최저 연봉을 조회하는 인라인 뷰가 있고, 사원 테이블의 사원번호 열과 내부 조인하여 최종 통계 결과 출력

EXPLAIN
SELECT 사원.사원번호,
	급여.평균연봉,
	급여.최고연봉,
	급여.최저연봉
		FROM 사원, 
			(SELECT 사원번호,
				ROUND(AVG(연봉),0) 평균연봉,
				ROUND(MAX(연봉),0) 최고연봉,
				ROUND(MIN(연봉),0) 최저연봉
				FROM 급여
				GROUP BY 사원번호
			) 급여
		WHERE 사원.사원번호 = 급여.사원번호
		 AND 사원.사원번호 BETWEEN 10001 AND 10100;

- SQL문 결과 : 총 100건의 결과 출력, 1.32 s 소요

- id 가 1인 두개 행 먼저 접근

- 중첩 루프 조인을 하는 두 개 테이블은 사원테이블과 <derived2> 테이블로, 먼저 출력된 사원 테이블이 드라이빙 테이블, 나중에 출력된 <derived2> 테이블이 드리븐 테이블

- <derived2> 테이블은 id가 2이고 select_type 이 DERIVE로 작성된 세 번째 행의 인라인 뷰를 가리킴

- FROM 절에서 급여 테이블로 수행한 그루핑 결과를 새로 생성한 임시 테이블의 메모리나 디스크에 올려놓음

- 이후 WHERE 절의 사원.사원번호 = 급여.사원번호 구문으로 데이터 추출하고 조인 수행

 

튜닝 수행

SELECT COUNT(1) FROM 사원; /* 300024 */
SELECT COUNT(1) FROM 사원 WHERE 사원번호 BETWEEN 10001 AND 10100; /* 100 */

- type 항목의 index 유형은 인덱스 풀 스캔을 수행하는 방식으로 FROM 절의 급여 테이블을 그루핑하면서 수행 

- 이 때 조건절이 없이 그루핑을 수행하므로 많은 데이터에 접근 함

- row 항목 : 2628895 수치는 급여 테이블의 전체 데이터 건수(2844047건)와 유사한 값으로 모든 데이터 접근

- 사원 테이블에서 BETWEEN 구문으로 데이터에 접근 : type 항목이 range 유형을 통해 범위 검색을 수행

- 사원 테이블의 전체 데이터는 약 30만 건, BETWEEN 구문으로 추출 데이터는 100건

 

튜닝 결과

| 튜닝 후 실행 계획 |

사원 테이블에서 WHERE 절의 BETWEEN 구문으로 100건의 데이터만 가져옴

전체 사원 데이터가 아닌 필요한 사원정보에만 접근한 뒤, 급여테이블에서 각 사원번호별 평균/최고/최저 연봉을 구함

SELECT 절에서 급여 테이블에 3번이나 접근하지만, WHERE 절에서 추출하려는 사원 테이블의 데이터가 사원 테이블의 전체 데이터 대비 극히 소량 (약 0.0003%)에 불과하므로, 인덱스를 활용해서 수행하는 3번의 스칼라 서브쿼리는 많은 리소스를 소모하지 않음

EXPLAIN
SELECT 사원.사원번호,
	(SELECT ROUND(AVG(연봉),0)
		FROM 급여 as 급여1
		WHERE 사원번호 = 사원.사원번호
	 ) AS 평균연봉,
	 (SELECT ROUND(MAX(연봉),0)
		FROM 급여 as 급여2
		WHERE 사원번호 = 사원.사원번호
	 ) AS 최고연봉,
	 (SELECT ROUND(MIN(연봉),0)
		FROM 급여 as 급여3
		WHERE 사원번호 = 사원.사원번호
	 ) AS 최저연봉
	FROM 사원
	WHERE 사원.사원번호 BETWEEN 10001 AND 10100;

 

- 1.32s → 2.2ms (시간 단축)

- id 가 1인 사원 테이블에 가장 먼저 접근, 다음으로 id 가 2, 3, 4 순으로 급여테이블(급여1, 급여2, 급여3)에 접근

- 이때 사원 테이블의 사원번호 조건을 SELECT 절의 3개 스칼라 서브쿼리에서 매번 받으므로 select_type 항목은DEPENDENT SUBQUERY 라고 출력

- 호출을 반복해 일으키므로 지나치게 자주 반복 호출될 경우 지양해야할 유형

- 100건의 데이터가 추출되는 사원 테이블 기준에서는 3개의 스칼라 서브쿼리를 갖는 급여 테이블에 100번만 접근하므로, 성능 측면에서는 비효율적인 부분은 거의 없음

 

* 참고

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