[SQL 튜닝] 작은 테이블이 먼저 조인에 참여하는 나쁜 SQL 문

작은 테이블이 먼저 조인에 참여하는 나쁜 SQL 문

현황 분석

| 튜닝 전 실행 계획 |

부서사원_매핑 테이블과 부서 테이블을 조인하여 부서 시작일자가 '2002-03-01' 이후인 사원의 데이터 조회하는 쿼리

EXPLAIN
SELECT 매핑.사원번호,
	부서.부서번호
FROM 부서사원_매핑 매핑,
	부서
WHERE 매핑.부서번호 = 부서.부서번호
	AND 매핑.시작일자 >= '2002-03-01';

- SQL문 결과 : 총 1,341건의 결과 출력, 327 ms 소요

- 드라이빙 테이블인 부서 테이블과 드리븐 테이블인 부서사원_매핑 테이블은 중첩 루프 조인을 수행

- 작은 크기의 부서 테이블에서 부서.부서번호 열만 SELECT 절과 WHERE 절에 필요 → UI_부서명 인덱스 활용해 인덱스 풀 스캔

- 부서번호 열로 구성된 기본 키(Key 항목 : PRIMARY)를 사용하도록 USE INDEX(PRIMARY) 힌트 추가하더라도 개선 안됨

- 상대적으로 큰 크기의 부서사원_매핑 테이블은 I_부서번호 인덱스로 인덱스 스캔을 수행

- rows 항목 : 41392 SQL문을 수행하고자 조사한 행의 예측 건수, 인덱스 스캔을 하고 랜덤 액세스로 테이블에 접근

- 드리븐 테이블에서 대량의 데이터에 대해 랜덤 액세스하면 비효율적임

- 부서사원_매핑 테이블에는 30만 건 이상의 데이터가 있으나, MYSQL 엔진으로 가져온 모든 데이터에 대해 WHERE 절의 필터 조건 (매핑.시작일자 >= '2002-03-01')을 수행

 

튜닝 수행

SELECT COUNT(1) FROM 부서사원_매핑; /* 331603 */
SELECT COUNT(1) FROM 부서; /* 9 */
SELECT COUNT(1) FROM 부서사원_매핑 WHERE 시작일자 >= '2002-03-01'; /* 1341 */

- 드라이빙 테이블인 부서 테이블 : 9건

- 드리븐 테이블인 부서사원_매핑 테이블 : 약 33만 건

- SQL문에 작성된 조건절(매핑.시작일자 >= '2002-03-01')로 추출한 부서사원_매핑 테이블의 데이터 건수 : 1,341건(0.4%)

- 상대적으로 규모가 큰 부서사원_매핑 테이블의 매핑.시작일자 >= '2002-03-01'  조건절을 먼저 적용 → 조인할 떄 비교대상이 줄어듬

- 부서사원_매핑 테이블에 대해 시작일자 열이 범위 조건으로 작성되는지, 그 범위에 해당하는 데이터가 5% 이하(혹은 10% 또는 1% 등 명확한 기준은 없음)에 불과한 소량의 데이터를 조회하는지 함께 분석

- 부서사원_매핑 테이블에 시작일자 열 기준으로 인덱스를 생성 → 인덱스 스캔을 통해 더 효율적으로 데이터 조회 가능

 

튜닝 결과

| 튜닝 후 실행 계획 |

부서사원_매핑 테이블에 필요한 데이터 건수를 0.4%로 줄일 수 있는 조건절이 이미 있으므로, 해당 조건절을 적절히 활용하여 드라이빙 테이블에서의 조인 비교 건수를 줄이도록 SQL 튜닝 수행

STRAIGHT_JOIN 힌트 사용 → FROM 절에 작성된 테이블 순서대로 조인에 참여할 수 있도록 고정

즉, 부서사원_매핑 테이블에 먼저 접근, 이후 부서 테이블에 반복하여 접근하면서 최종 결과 추출

EXPLAIN
SELECT STRAIGHT_JOIN
	매핑.사원번호,
	부서.부서번호
FROM 부서사원_매핑 매핑, 부서
WHERE 매핑.부서번호 = 부서.부서번호
AND 매핑.시작일자 >= '2002-03-01';

- 327 ms → 63.8 ms (시간 개선)

- id 가 1인 부서사원_매핑 테이블과 부서 테이블은 각각 중첩 루프 조인으로 처리

- 먼저 접근하는 드라이빙 테이블은 부서사원_매핑 테이블로, 테이블의 랜덤 액세스 없이 테이블 풀 스캔(type 항목 : ALL)으로 한번에 다수의 페이지(데이터가 저장되는 최소 단위)에 접근

- 드라이빙 테이블에서 추출된 데이터만큼 반복하여 접근하게 되는 드리븐 테이블 : 부서 테이블

- 상대적으로 대용량인 부서사원_매핑 테이블을 테이블 풀 스캔으로 처리, 부서 테이블에는 기본 키 (Key 항목 : PRIAMRY)로 반복 접근하여 1개의 데이터에만 접근하는 식으로 수행

 

* 참고

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