4. MySQL SQL 튜닝 3

1) 처음부터 모든 데이터를 가져오는 나쁜 SQL 문

(1) 현황분석

explain select 사원.사원번호,
	급여.평균연봉,
    급여.최고연봉,
    급여.최저연봉
    from 사원,
    (select 사원번호,
		round(avg(연봉), 0) 평균연봉,
        round(max(연봉), 0) 최고연봉,
        round(min(연봉), 0) 최저연봉
        from 급여
        group by 사원번호
        ) 급여
	where 사원.사원번호 = 급여.사원번호
    and 사원.사원번호 between 10001 and 10100;

• id가 1인 두개 행에 먼저 접근, 사원테이블이 드라이빙 테이블 derived2 테이블이 드리븐 테이블
• derived2테이블은 id가 2이고 select_type 항목이 DERIVED로 작성된 세 변째 행의 인라인 뷰를 가리킨다.
• from 절에서 급여 테이블로 수행한 그루핑 결과를 새로 생성한 임시 테이블의 메모리나 디스크에 올려 놓는다.

(2) 문제점

• from 절의 급여 테이블을 그루핑하면서 조건절 없이 지나치게 많은 데이터에 접근
• 사원 테이블 bwtween 구문으로 데이터 접근하는데, 테이블의 총 30만건 중 조건은 10건 차지

(3) 튜닝 수행

explain select 사원.사원번호,
	    (select round(avg(연봉), 0)
	    from 급여 as 급여1
	    where 사원번호 = 사원.사원번호
	    ) as 평균연봉,
       	(select round(max(연봉), 0)
	    from 급여 as 급여2
	    where 사원번호 = 사원.사원번호
	    ) as 최고연봉,
       	(select round(min(연봉), 0)
	    from 급여 as 급여2
	    where 사원번호 = 사원.사원번호
	    ) as 최저연봉
    from 사원
    where 사원.사원번호 between 10001 and 10100;

• 사원 테이블에서 beteen 구문으로 100건의 데이터 가져온 후, 급여 테이블에서 각 사원번호 별 연봉 구함
• 가장 먼저 id가 1인 사원 테이블 접근, 급여3 / 급여2 / 급여 1 순으로 접근
• DEPENDENT SUBQUERY는 호출을 반복해 일이키므로 지양해야 하지만, 100건의 데이터가 추출되는 사원 테이블 기준에서는 100번만 접근하므로 성능 측면에서 비효율적인 부분은 거의 없다.

2) 비효율적인 페이징을 수행하는 나쁜 SQL 문

(1) 현황분석

explain select 사원.사원번호, 사원.이름, 사원.성, 사원.입사일자
    from 사원
	inner join 급여 on 사원.사원번호 = 급여.사원번호
	where 사원.사원번호 between 10001 and 50000
    group by 사원.사원번호
    order by sum(급여.연봉) desc
    limit 150, 10;

• 드라이빙 테이블인 사원 테이블과 드리븐 테이블인 급여 테이블을 조인
• 사원 테이블은 그루핑과 정렬 연산을 위해 임시 테이블 Using temporary을 생성한 뒤 정렬 작업 Using filesort 수행

(2) 문제점

• LIMIT 연산으로 10건의 데이터를 가져오기 위해 수십만 건의 데이터 대상으로 조인 수행 뒤 그루핑과 정렬 작업 수행

(3) 튜닝 수행

explain select 사원.사원번호, 사원.이름, 사원.성, 사원.입사일자
    from (select 사원번호
            from 급여
            where 사원번호 between 10001 and 50000
            group by 사원번호
            order by sum(급여.연봉) desc
            limit 150, 10) 급여,
            사원
    where 사원.사원번호 = 급여.사원번호;

• 급여 테이블에서 group by와 order by 작업을 from 절의 인라인 뷰로 작성, 인라인 뷰에 필요한 데이터 건수만큼 LIMIT 절로 제약 설정하여 사원 테이블과 조인할 수 있는 데이터 건수 줄임
• id가 1인 derived2 테이블은 id 2인 급여 테이블이고, 드리븐 테이블은 사원 테이블 이다.
• 인라인 뷰인 급여 테이블 기준으로 사원 테이블에 반복 접근하여 조인 수행

3) 필요 이상으로 많은 정보를 가져오는 나쁜 SQL 문

(1) 현황분석

explain select count(사원번호) as 카운트
	from(
		select 사원.사원번호, 관리자.부서번호
        from (select *
                from 사원
                where 사원번호 > 300000
            ) 사원
	    left join 부서관리자 관리자
	        on 사원.사원번호 = 관리자.사원번호
    ) 서브쿼리;

• 부서 관리자 테이블은 외부 조인에 따라 PK를 활용하여 중첩 루프 조인 시 1건의 데이터 (rows 항목: 1)에 접근

(2) 문제점

• 사원 테이블의 사원번호를 집계 하는 과정에서 부서관리자 테이블과 외부 조인이 필요한지 고민해봐야 함

(3) 튜닝 수행

explain select count(사원번호) as 카운트
    from 사원
    where 성별 = 'M'
    and 사원번호 > 300000;

• 불필요한 외부 조건인 부서관리자 사원번호 제거

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

(1) 현황분석

explain select distinct 매핑.부서번호
    from 부서관리자 관리자,
         부서사원_매핑 매핑
    where 관리자.부서번호 = 매핑.부서번호
    order by 매핑.부서번호;

• 드라이빙 테이블은 부서사원_매핑 테이블로 인덱스 풀 스캔, 부서관리자 테이블은 드라이븐 테이블

(2) 문제점

• SELECT 절에 매핑.부서번호를 관리자.부서번호로 작성해도 동일한 겨로가 출력
  -> 부서번호가 같은지 확인하는 과정에 대한 고민 필요

• from절과 where 절로 조인 수행 뒤 그 조인 결과에 대해 DISTINCT 작업을 수행하는데 조인 전 미리 중복 제거 고려

(3) 튜닝 수행

explain select 매핑.부서번호
    from ( select distinct 부서번호
            from 부서사원_매핑 매핑
        ) 매핑
    where exists(select 1
                from 부서관리자 관리자
                where 부서번호 = 매핑.부서번호)
    order by 매핑.부서번호;

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

• 부서관리자 테이블의 데이터를 모두 확인하지 않고 동일한 부서번호가 있다면 이후의 데이터에는 더 접근하지 않는 EXISTS 연산자 활용

• id 2 행은 인라인 뷰로 Using index for group-by : DISTINCT 작업을 수행하고자 I_부서번호 인덱스로 정렬한 뒤에 중복을 제거

• id 1 행은 전체 24개의 행을 인덱스 풀 스캔 후 중복 제거된 부서사원_매핑 테이블과 조인, 이때 부서관리자 테이블에 exists 연산자로 비교할 부서번호가 있다면 이후로 동일한 부서번호 데이터는 확인하지 않고 건너뛰므로 Extra 항목에 LooseScan으로 표시

참고: 업무에 바로쓰는 SQL 튜닝 / 양바른 저