2021. 8. 9. 14:09ㆍ카테고리 없음
테이블 조인 설정 변경으로 착한 쿼리 만들기
작은 테이블이 먼저 조인에 참여하는 나쁜 SQL 문
튜닝 전 SQL 문
select 매핑.사원번호, 부서.부서번호
from 부서사원_매핑 매핑, 부서
where 매핑.부서번호 = 부서.부서번호
and 매핑.시작일자 >= '2002-03-01';튜닝 전 실행계획
explain
select 매핑.사원번호, 부서.부서번호
from 부서사원_매핑 매핑, 부서
where 매핑.부서번호 = 부서.부서번호
and 매핑.시작일자 >= '2002-03-01';
드라이빙 테이블인 부서 테이블과 드리븐 테이블인 부서사원_매핑 테이블은 중첩 루프 조인을 수행
UI_부서명 인덱스를 활용해 인덱스 풀 스캔을 한다.
드리븐 테이블에서 대량 데이터에 대해 랜덤 액세스를 하면 비효율적이다
튜닝 후 SQL 문
select straight_join
매핑.사원번호,
부서.부서번호
from 부서사원_매핑 매핑, 부서
where 매핑.부서번호 = 부서.부서번호
and 매핑.시작일자 >= '2002-03-01'부서사원_매핑 테이블에 필요한 데이터 건수를 0.4%로 줄일 수 있는 조건절이 이미 있으므로,
해당 조건절을 적절히 활용해서 드라이빙 테이블에서의 조인비교건수를 줄이도록 SQL 튜닝 수행
STRAIGHT_JOIN 힌트를 사용해서 FROM 절에 작성된 테이블 순서대로 조인에 참여할 수 있도록 고정한다.
즉, 부서사원_매핑 테이블에 먼저 접근 후
부서 테이블에 반복하여 접근하면서 최종결과를 추출한다.
튜닝 후 실행계획
explain
select straight_join
매핑.사원번호,
부서.부서번호
from 부서사원_매핑 매핑, 부서
where 매핑.부서번호 = 부서.부서번호
and 매핑.시작일자 >= '2002-03-01'
id가 1인 각 테이블은 중첩 루프조인으로 처리된다
우선 드라이빙 테이블인 부서사원_매핑 테이블로 테이블의 랜덤액세스없이
테이블 풀 스캔(ALL 타입)으로 한 번에 다수 페이지에 접근
그리고나서 드라이빙 테이블에서 추출된 데이터만큼 반복해서 접근하게 되는 드리븐 테이블은 부서 테이블이 된다.
상대적으로 대용량인 부서사원_매핑 테이블을 테이블 풀 스캔으로 처리하고,
부서 테이블에는 기본키로 반복 접근하여 1개의 데이터에만 접근하는 식으로 수행된다.
메인 테이블에 계속 의존하는 나쁜 SQL 문
튜닝 전 SQL 문
select 사원.사원번호, 사원.이름, 사원.성
from 사원
where 사원번호 > 450000
and (
select max(연봉)
from 급여
where 사원번호 = 사원.사원번호
) > 100000;
튜닝 전 실행계획
explain
select 사원.사원번호, 사원.이름, 사원.성
from 사원
where 사원번호 > 450000
and (
select max(연봉)
from 급여
where 사원번호 = 사원.사원번호
) > 100000;
FROM 절의 메인테이블인 사원테이블에 접근한다. 즉. Id가 1인 사원 테이블이 기본키를 활용해서
레인지 스캔을 수행한다.
그 다음에 id가 2인 급여 테이블에 접근한다.
해당쿼리는 외부 사원테이블로부터 조건절을 전달받아 수행해야하는 의존성을 가진 서브쿼리(DEPENDENT_SUBQUERY) 이다
급여 테이블은 기본키를 활용함을 알 수 있다.
사원 테이블의 인덱스
급여 테이블의 인덱스
보통 DEPENDENT SUBQUERY 이면 외부 테이블에서 조건절을 받은 후 처리되어야하기때문에
튜닝대상으로 고려되어야 한다.
id가 2인 행의 급여 테이블이 튜닝대상이다
(*) 서브쿼리보다는 조인으로 수행하는 편이 성능 측면에서 유리할 가능성이 높다.
튜닝 후 SQL 문
select 사원.사원번호,
사원.이름,
사원.성
from 사원, 급여
where 사원.사원번호 > 450000
and 사원.사원번호 = 급여.사원번호
group by 사원.사원번호
having max(급여.연봉) > 100000;where절의 서브쿼리를 조인으로 변경하면서 group by 절과 having 절을 이용해서
튜닝 전의 그룹별 최댓값을 계산하도록 개선한다
즉, group by 절에서는 사원번호별 그루핑을 수행
having 절에서는 연봉의 최댓값으로 조건을 설정하여 원하는 데이터 조회
튜닝 후 실행계획
explain
select 사원.사원번호,
사원.이름,
사원.성
from 사원, 급여
where 사원.사원번호 > 450000
and 사원.사원번호 = 급여.사원번호
group by 사원.사원번호
having max(급여.연봉) > 100000;
id가 1인 두개 행에서 먼저 접근하는 드라이빙 테이블은 급여 테이블이고
그 다음 접근하는 드리븐 테이블은 사원 테이블이다.
튜닝 전 실행계획에서 수행된 급여 테이블의 DEPENDENT SUBQUERY 방식은 제거되고
사원 테이블과 급여 테이블의 단순 조인방식으로 변경되어 수행효율이 향상됨을 확인
불필요한 조인을 수행하는 나쁜 SQL 문
튜닝 전 SQL 문
select count(distinct 사원.사원번호) as 데이터건수
from 사원,
(
select 사원번호
from 사원출입기록 기록
where 출입문 = 'A'
) 기록
where 사원.사원번호 = 기록.사원번호튜닝 전 실행계획
explain
select count(distinct 사원.사원번호) as 데이터건수
from 사원,
(
select 사원번호
from 사원출입기록 기록
where 출입문 = 'A'
) 기록
where 사원.사원번호 = 기록.사원번호
id가 둘 다 1이므로 조인수행됨을 알 수 있다.
WHERE 절에서 값이 'A' 인 상수와 직접 비교하므로
ref 항목이 const로 출력
인덱스를 사용한 동등(=) 비교를 수행하므로 ref 로 표시
튜닝수행
select count(distinct 기록.사원번호) as 데이터건수
from 사원, 사원출입기록 기록
where 사원.사원번호 = 기록.사원번호
and 출입문 = 'A';FROM 절의 인라인 뷰는 사실상 옵티마이저에 의해 조인방식이 뷰 병합으로
최적화되어 수행
튜닝 후 SQL 문
select count(1) as 데이터건수
from 사원
where exists (
select 1
from 사원출입기록 기록
where 출입문 = 'A'
and 기록.사원번호 = 사원.사원번호
);
사원 테이블의 구조
사원출입기록 테이블의 데이터는 최종결과에 사용하지않고
단지 존재여부만 파악하면 되므로 EXISTS 구문으로 변경한다.
출입문 A에 대한 기록이 있는 사원번호에 대해 조인을 수행한 후
해당하는 데이터만 집계하는 방식으로 튜닝한다
튜닝 후 실행계획
explain
select count(1) as 데이터건수
from 사원
where exists (
select 1
from 사원출입기록 기록
where 출입문 = 'A'
and 기록.사원번호 = 사원.사원번호
);
사원 테이블은 드라이빙 테이블이고
subquery2 는 드리븐 테이블이다
subquery2 는 id가 2인 사원출력기록 테이블로서
사원출력기록 테이블은 EXISTS 연산자로
데이터 존재여부를 파악하기위해 임시테이블을 생성하는 MATERIALIZED로 표시된다.