작은 테이블이 먼저 조인에 참여하는 나쁜 SQL문
현황 분석
튜닝 전 SQL문
SELECT 매핑.사원번호,
부서.부서번호
FROM 부서사원_매핑 매핑,
부서
WHERE 매핑.부서번호 = 부서.부서번호
AND 매핑.시작일자 >= '2002-03-01'
부서사원_매핑 테이블, 부서 테이블을 조인하여 부서 시작일자 ‘2002-03-01’이후인 사원의 데이터를 조회하는 쿼리이다.
튜닝 전 수행 결과
튜닝 전 실행 계획
- 드라이빙 테이블인 부서와 드리븐 테이블인 부서사원_매핑은 중첩 루프 조인을 수행한다.
- 작은 크기의 부서 테이블에서
부서.부서번호열만SELECT,WHERE절에 필요하므로,UI_부서명인덱스를 활용해 인덱스 풀 스캔을 한다. - 상대적으로 큰 크기의 부서사원매핑 테이블은 `I부서번호
인덱스로 인덱스 스캔을 수행한다. 이때rows` 항목의 41392라는 수치는 SQL 수행을 위해 조사한 행의 예측 건수로, 인덱스 스캔 후 랜덤 액세스로 테이블에 접근하게 된다. 드리븐 테이블에서 대량의 데이터에 랜덤 액세스를 하면 비효율적이다. - 또한 부서사원_매핑 테이블에 30만 건 이상의 데이터가 있으나, MySQL 엔진으로 가져온 모든 데이터에 대해
WHERE절의 필터 조건(매핑.시작일자 ≥ ‘2002-03-01')을 수행한다.
튜닝 수행
튜닝 전 SQL문에 포함된 테이블의 데이터 건수부터 확인해보자.
드리븐 테이블인 부서사원_매핑에는 약 33만 건의 데이터가 있는데 반해, 조건절로 추출한 결과 데이터는 약 1,341건으로 전체 데이터 건수 대비 0.4% 수준에 불과하다.
상대적으로 규모가 큰 부서사원_매핑 테이블의 매핑.시작일자='2002-03-01' 조건절을 먼저 적용할 수 있다면 조인할 때 비교 대상이 줄어들 것이다.
더불어 부서사원매핑 테이블에 대해 시작일자 열이 범위 조건으로 작성되는지, 그 범위에 해당하는 데이터가 5% 이하(명확한 기준은 없음)에 불과한 소량 데이터를 조회하는지를 함께 분석해봐야 한다. 그런 경우 부서사원매핑 테이블에 시작일자 열 기준으로 인덱스를 생성한다면 인덱스 스캔을 통해 더 효율적으로 데이터를 분석할 수 있을 것이다.
튜닝 결과
튜닝 후 SQL문
SELECT STRAIGHT_JOIN
매핑.사원번호,
부서.부서번호
FROM 부서사원_매핑 매핑,
부서
WHERE 매핑.부서번호 = 부서.부서번호
AND 매핑.시작일자 >= '2002-03-01'조건절을 적절히 활용하여 드라이빙 테이블에서의 조인 비교 건수를 줄이도록 SQL 튜닝을 수행한다. 그러려면 STRAIGHT_JOIN 힌트를 사용하여 FROM 절에 작성된 데이터 순서대로 조인에 참여할 수 있도록 고정해야 한다.
튜닝 후 수행 결과
0.08초로 소요시간이 개선되었다.
튜닝 후 실행 계획
먼저 접근하는 드라이빙 테이블 부서사원_매핑에서 랜덤 액세스 없이 테이블 풀 스캔(type : ALL )로 한 번에 다수의 페이지에 접근한다. 그리고 드라이빙 테이블(부서)에서 추출된 데이터만큼 반복 접근하게 된다. 즉, 상대적으로 대용량인 부서사원_매핑을 테이블 풀 스캔으로 처리하고, 부서 테이블은 PK로 반복 접근하여 1개의 데이터에만 접근하는 식으로 수행된다.
메인 테이블에 계속 의존하는 나쁜 SQL문
현황 분석
튜닝 전 SQL문
SELECT 사원.사원번호, 사원.이름, 사원.성
FROM 사원
WHERE 사원번호 > 450000
AND (SELECT MAX(연봉)
FROM 급여
WHERE 사원번호 = 사원.사원번호
) > 100000위 쿼리는 사원번호가 450,000번을 초과하면서 받은 연봉이 한 번이라도 100,000달러를 초과한 적이 있는 사원의 정보를 출력한다. WHERE 절에서 추출 대상을 정하고, 중첩 서브쿼리와 급여 테이블에서 메인 테이블의 사원번호를 매번 받아와 해당 사원의 최대 연봉 데이터를 확인한다.
튜닝 전 수행 결과
튜닝 전 실행 계획
- 먼저, 메인 테이블인 사원에 PK를 활용해 범위 스캔을 수행한다.
- 그 다음 급여 테이블에
사원.사원번호를 가지고 PK를 활용해 접근한다.
튜닝 수행
테이블들의 데이터 현황부터 확인해보면, 사원 테이블에는 약 30만 건의 데이터가 급여 테이블에는 약 284만 건의 데이터가 있다. 이때 사원번호가 45,000를 초과하는 데이터는 49,999건으로 전체의 15% 수준이다.
튜닝 대상 SQL문에서 활용하는 인덱스는 모두 PK이다. 보통 실행 계획의 select_type 항목에 DEPENDENT 키워드가 있으면, 외부 테이블에서 조건절을 받은 후 처리되야 하므로 튜닝 대상으로 고려된다.
따라서 WHERE 절의 서브쿼리에서 외부 테이블 칼럼인 사원.사원번호 를 조건절로 받아야 할지 고민해봐야 한다. 서브쿼리 대신 조인으로 대체하는 것이 성능 측면에서 더 유리할 확률이 높다.
튜닝 결과
튜닝 후 SQL문
SELECT 사원.사원번호,
사원.이름,
사원.성
FROM 사원,
급여
WHERE 사원.사원번호 > 450000
AND 사원.사원번호 = 급여.사원번호
GROUP BY 사원.사원번호
HAVING MAX(급여.연봉) > 100000서브쿼리에서 조인으로 변경하며 GROUP BY , HAVING 을 이용하여 튜닝 전의 그룹별 최댓값을 계산하도록 개선한다.
튜닝 후 수행 결과
0.51초로 수행 시간이 개선되었다.
튜닝 후 실행 계획
- 드라이빙 테이블은 급여 테이블이고, 드리븐 테이블은 사원 테이블이다.
- 급여 테이블을 접근하기 위한 축소 조건은
WHERE 급여.사원번호 > 450000조건절로 변형되어 적용된다. (옵티마이저에 의한 SQL 재작성) - 튜닝 전 실행계획에서
DEPENDENT SUBQUERY방식은 제거되고, 단순 조인으로 변경되어 수행 효율이 향상됨을 알 수 있다.
불필요한 조인을 수행하는 나쁜 SQL문
현황 분석
튜닝 전 SQL문
SELECT COUNT(DISTINCT 사원.사원번호) AS 데이터건수
FROM 사원,
(SELECT 사원번호
FROM 사원출입기록 기록
WHERE 출입문 = 'A'
) 기록
WHERE 사원.사원번호 = 기록.사원번호A 출입문으로 출입한 사원이 총 몇 명인지 구하는 쿼리이다.
튜닝 전 수행 결과
튜닝 전 실행 계획
- 두 테이블의
id가 모두 1이므로 조인이 수행된다. - 드라이빙 테이블(사원출입기록)은
I_출입문인덱스를 활용하여 A 출입문에 관한 기록이 있는 사원번호를 구한다. WHERE절에서는 값이A인 상수와 직접 비교하므로ref항목이const로 출력되고, 인덱스를 사용한 동등 비교를 수행하므로type이ref로 표시된다.
튜닝 수행
FROM 절의 인라인 뷰는 사실상 옵티마이저에 의해 조인 방식이 뷰 병합으로 최적화되어 다음 SQL처럼 수행된다.
SELECT COUNT(DISTINCT 기록.사원번호) AS 데이터건수
FROM 사원,
사원출입기록 기록
WHERE 사원.사원번호 = 기록.사원번호
AND 출입문 = 'A'사원출입기록 테이블의 사원번호는 사원 테이블과 조인을 수행하는 과정 중에 값의 존재 여부만 알면 된다. 66만여 건에 달하는 사원출입기록 테이블의 데이터 결과가 최종 결과에 어떻게 활용되는지 확인해봐야 한다.
튜닝 결과
튜닝 후 SQL문
SELECT COUNT(1) AS 데이터건수
FROM 사원
WHERE EXISTS (SELECT 1
FROM 사원출입기록 기록
WHERE 출입문 = 'A'
AND 기록.사원번호 = 사원.사원번호)단지 존재 여부만 파악하면 되므로 EXISTS 구문으로 변경한다. 출입문 A 에 관한 기록이 있는 사원번호에 대해 조인을 수행한 뒤, 해당 데이터만 집계하는 방식으로 튜닝한다.
튜닝 후 수행 결과
0.83으로 수행시간이 개선되었다.
튜닝 후 실행 계획
- 사원은 드라이빙 테이블이고,
<subquery2>는 드리븐 테이블이다. <subquery2>는 id가 2인 사원출력기록 테이블로서, 해당 테이블은EXISTS연산자로 데이터 존재 여부를 파악하기 위해 임시 테이블을 생성하는MATERIALIZED로 표기된다.
