[SQL 튜닝] 메인 테이블에 계속 의존하는 나쁜 SQL 문

메인 테이블에 계속 의존하는 나쁜 SQL 문

현황 분석

| 튜닝 전 실행 계획 |

사원번호가 450,000보다 크고, 최대 연봉이 100,000보다 큰 데이터를 찾아 출력하는 쿼리

사원번호가 450,000번을 초과하면서 그동안 받은 연봉 중 한 번이라도 100,000를 초과한 적이 있는 사원 정보 출력

EXPLAIN
SELECT 사원.사원번호, 사원.이름, 사원.성
	FROM 사원
WHERE 사원번호 > 450000
	AND (SELECT MAX(연봉) 
		FROM 급여 
		WHERE 사원번호 = 사원.사원번호) > 100000;

- SQL문 결과 : 총 3,155건의 결과 출력, 193 ms 소요

- 먼저 FROM 절의 메인 테이블인 사원 테이블에 접근

- id가 1인 사원 테이블이 기본 키 (key: PRIMARY)를 활용해서 범위 스캔(type 항목: range)을 수행

- id가 2인 급여테이블에 접근

- 외부의 사원 테이블로 부터 조건절을 전달받아 수행해야하는 의존성 서브쿼리(select_type 항목 : DEPENDENT SUBQUERY)

- 급여 테이블 키 (key : PRMARY 활용)

 

튜닝 수행

SELECT COUNT(1) FROM 사원; /* 300024 */
SELECT COUNT(1) FROM 급여; /* 2844047 */ 
SELECT COUNT(1) FROM 사원 WHERE 사원번호 > 450000; /* 49999 */

- 사원 테이블 : 약 30만 건 → 사원번호 450000 초과 데이터 49,999 건 (약 15% 수준)

- 급여 테이블 : 약 284만 건

SHOW INDEX FROM 사원;

SHOW INDEX FROM 급여;

- 튜닝 대상 SQL 문에서 활용하는 인덱스는 모두 기본 키(PRIMARY)

- 사원 테이블 : 사원번호 열로 구성된 기본키 사용

- 급여 테이블 : 사원번호와 시작일자로 구성된 기본키를 사용

- 보통 select_type 항목에 DEPENDENT 키워드 있으면 튜닝 대상으로 고려할 수 있음

- WHERE 절의 서브쿼리에서 외부 테이블인 사원 테이블의 사원정보를 조건절 (WHERE 사원번호 = 사원.사원번호)로 받아야할지 고민

 

TIP 서브쿼리 VS 조인
서브쿼리보다는 조인으로 수행하는 편이 성능 측면에서 유리할 가능성이 높음

 

튜닝 결과

| 튜닝 후 실행 계획 |

WHERE 절의 서브쿼리를 조인으로 변경하면서 GROUP BY 절과 HAVING 절을 이용하여 튜닝 전의 그룹별 최댓값을 계산하도록 개선

GROUP BY 절에서는 사원번호별 그루핑을 수행, HAVING 절에서는 연봉의 최댓값으로 조건을 설정하여 데이터 조회

/* 조인 방법1 */
EXPLAIN
SELECT 사원.사원번호, 사원.이름, 사원.성
	FROM 사원, 급여
WHERE 사원.사원번호 > 450000
	AND 사원.사원번호 = 급여.사원번호
GROUP BY 사원.사원번호
HAVING MAX(급여.연봉) > 100000;

/* 조인 방법2 */
EXPLAIN
SELECT 사원.사원번호, 사원.이름, 사원.성
	FROM 사원
	JOIN 급여 ON 사원.사원번호 = 급여.사원번호
	WHERE 사원.사원번호 > 450000
	GROUP BY 사원.사원번호
HAVING MAX(급여.연봉) > 100000;

- 193 ms → 175ms

- id = 1인 두 개 행에서 먼저 접근하는 드라이빙 테이블은 급여테이블이고, 드리븐 테이블은 사원 테이블

- 급여 테이블에 먼저 접근하기 위한 범위 축소 조건 : WHERE 사원.사원번호 > 450000 절을 통한 급여.사원번호 > 450000 조건절로 변형되어 적용 → 옵티마이저에 의해 SQL 문이 재작성된 부분으로 수행

- 튜닝 전 급여 테이블의 DEPENDENT SUBQUERY 방식은 제거

- 사원 테이블과 급여 테이블이 단순히 조인하는 방식으로 변경되어 수행 효율이 향상

 

* 참고

- 업무에 바로쓰는 SQL 튜닝(도서) - 4장 악성 SQL 튜닝으로 초보자 탈출하기