MariaDB DBMS/MariaDB 튜닝 2

[SQL 튜닝 실습] SQL 튜닝 중급 1

MINOKUMA 2021. 8. 9. 22:08
반응형

SQL 문 재작성으로 착한쿼리 만들기

 

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

 

튜닝 전 SQL 문

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

 

튜닝 전 실행계획

튜닝 후 SQL 문

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 급여3				
        where 사원번호 = 사원.사원번호				
       ) as 최저연봉				
	from 사원			
    where 사원.사원번호 between 10001 and 10100;

사원 테이블에서 where 절의 between 구문으로 100건의 데이터만 가져온다.
전체 사원 데이터가 아닌 필요한 사원정보에만 접근한 후 
급여 테이블에서 각 사원번호별 평균연봉, 최고연봉, 최저연봉을 구한다.

튜닝 전 SQL 문에 비해 SELECT 절에서 급여 테이블에 3번이나 접근하므로
비효율적인 방식이 아닌지 의문이 들 수 있지만, WHERE 절에서 추출하려는
사원 테이블의 데이터가 사원테이블의 전체 데이터 대비
극히 소량에 불과하므로 인덱스를 활용해서 수행하는 3번의 스칼라 서브쿼리는
많은 리소스를 소모하지 않는다.

 

튜닝 후 실행계획

제일 먼저 접근하는 테이블은 

id가 1인 사원 테이블이고
그 다음이 2인 급여1 테이블
그 다음이 3인 급여2 테이블
그 다음이 4인 급여3 테이블
순으로 접근한다

이때 사원테이블의 사원번호 조건을 SELECT 절의 3개의 스칼라 서브쿼리에서 매번 받으므로 
select_type 항목은 DEPENDENT SUBQUERY 라고 출력된다.
이것은 호출을 반복해서 일으키므로 지나치게 자주 반복호출될 경우에는 지양해야할 유형이다
하지만 100건의 데이터가 추출되는 사원 테이블 기준에서는 

3개의 스칼라 서브쿼리를 갖는 급여테이블에 100번만 접근하므로
성능 측면에서 비효율적인 부분은 거의 없습니다.

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

 

튜닝 전 SQL 문 

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

 

튜닝 전 실행계획

드라이빙 테이블인 사원테이블과 드리븐 테이블인 급여테이블을 조인한다.
사원테이블과 급여테이블은 각각 기본키로 데이터에 접근한다.
이 때 드라이빙 테이블인 사원테이블은 그루핑과 정렬연산을 위해 임시테이블(Using temporary) 
을 생성한 후 

정렬 작업(Using filesort)을 수행함을 알 수 있다.

 

튜닝 후 SQL 문 

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 절로 
제약설정을 하여 사원테이블과 조인할 수 있는 데이터 건수를 줄일 수 있다.

 

튜닝 후 실행계획

derived2 테이블과 사원 테이블 대상으로 중첩 루프조인을 수행한다.
드라이빙 테이블인 derived2 테이블은 id가 2에 해당되는 급여테이블이며
드리븐 테이블은 사원 테이블이다.

derived2 테이블은 where 절의 사원번호 between 10001 and 50000 조건절에 따라 
레인지 스캔을 수행하는 인라인 뷰로
데이터를 임시 테이블에 상주시켜 정렬(Using filesort) 작업을 수행하게 된다.

인라인 뷰인 급여 테이블 기준으로 사원테이블에 반복해 접근하고 
where 절의 사원.사원번호 = 급여.사원번호 조건절로 조인을 수행한다
드라이빙 테이블은 테이블 풀 스캔(ALL) 한다.
드리븐 테이블은 기본 키를 활용해서 데이터를 추출 (eq_ref)하며
중첩 루프조인에 따라 기본 키를 매번 가져오므로
rows 항목에는 1개 데이터에만 접근한 것으로 출력된다.

 

 

 

 

 

반응형