[SQL 튜닝] 대량의 데이터를 가져와 조인하는 나쁜 SQL 문

대량의 데이터를 가져와 조인하는 나쁜 SQL 문

현황 분석

| 튜닝 전 실행 계획 |

부서관리자 테이블과 부서사원_매핑 테이블을 부서번호 열로 조인하고 중복을 제거한 부서번호를 출력하는 쿼리

즉, 부서의 관리자가 소속된 부서번호를 조회하면서 부서사원_매핑 테이블에도 있는 부서번호를 선택

 

EXPLAIN
SELECT DISTINCT 매핑.부서번호
	FROM 부서관리자 관리자, 부서사원_매핑 매핑
WHERE 관리자.부서번호 = 매핑.부서번호
ORDER BY 매핑.부서번호;

- SQL문 결과 : 총 9건의 결과 출력, 382ms 소요

- 부서번호 정보에서 중복이 제거되고 오름차순으로 정렬된 9건의 데이터 출력

- id가 1인 부서사원_매핑 테이블과 부서관리자 테이블의 2개 행 출력

- 드라이빙 테이블 : 부서사원_매핑 테이블, 드리븐 테이블 : 부서관리자 테이블, 중첩 루프 조인 수행

- 데이터에 먼저 접근하는 부서사원_매핑 테이블은 인덱스 풀 스캔(type 항목 : index) 방식으로 접근

- 부서사원_매핑 테이블의 데이터 접근을 구체화할 수 있는 조건문이 없으므로 I_부서번호 인덱스로 처음부터 끝까지 전체 인덱스를 스캔

- 부서관리자 테이블 : 관리자.부서번호 = 매핑.부서번호 조건절로 데이터에 접근, 중복 제거하는 DISTINCT 연산도 수행 

 

튜닝 수행

- SELECT 절을 보면 중복이 제거된 부서번호 열만 조회하려고 함

- 2개 테이블을 부서번호 열로 내부 조인 수행

- 조인을 수행하는 부서번호 열이 부서관리자 테이블과 부서사원_매핑 테이블에 모두 있으므로 SELECT 절을 매핑.부서번호 또는 관리자.부서번호로 작성해도 모두 동일한 결과를 출력

- 두 테이블 모두 데이터에 접근한 뒤 부서번호가 같은지 일일이 확인하는 과정이 필요한지 고민(둘 중 하나의 테이블은 단순히 부서번호가 존재하는지 여부만 판단해도 충분함)

- FROM 과 WHERE 절로 조인을 수행한 뒤 그 조인 결과에서 DISTINCT 작업 수행 → 수십만 개의 데이터를 조인하기 전에 미리 중복제거 할 수 있는 방법 고민

 

튜닝 결과

| 튜닝 후 실행 계획 |

FROM 절에서 부서사원_매핑 테이블의 데이터를 가져올 때 부서번호 데이터를 미리 중복 제거

부서관리자 테이블은 같은 부서번호 데이터가 있는지 여부만 판단

부서관리자 테이블의 데이터를 모두 확인하지 않고도 동일한 부서번호가 있다면 이후의 데이터에 더 접근하지 않는 EXISTS 연산자 활용 → 중복제거를 미리 수행하고 SELECT 절에서 활용하지 않는 부서관리자 데이터는 존재 여부만 판단하는 방식으로 SQL 튜닝

EXPLAIN
SELECT 매핑.부서번호
	FROM (SELECT DISTINCT 부서번호
			FROM 부서사원_매핑 매핑
			) 매핑
WHERE EXISTS (SELECT 1
				FROM 부서관리자 관리자
					WHERE 부서번호 = 매핑.부서번호)
ORDER BY 매핑.부서번호;

- 382ms → 0.5ms (시간 단축)

- id가 1인 부서관리자 테이블과 <derived2> 테이블은 조인 수행

- <derived2> 테이블은 id가 2인 행의 인라인 뷰로, FROM 절에 DISTINCT 작업까지 마친 매핑 테이블

- DISTINCT 작업을 수행하고자 I_부서번호 인덱스로 정렬한 뒤 중복을 제거(Extra 항목 : Using index for group-by)

- 드라이빙 테이블인 부서관리자 테이블은 전체 24개 데이터를 인덱스 풀 스캔으로 수행한 뒤에 드리븐 테이블인 중복 제거된 부서사원_매핑 테이블과 조인

- 부서관리자 테이블에 EXISTS 연산자로 비교할 부서번호가 있다면 이후로 동일한 부서번호 데이터는 확인하지 않고 건너뛰므로 Extra 항목에 LooseScan으로 표시

- rows 항목 : 부서사원_매핑 테이블에 접근하는 데이터 건수가 한자리수로 줄음

 

* 참고

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