[SQL 튜닝 실습] SQL 튜닝 준비 5

DEPENDENT SUBQUERY

 

UNION 또는 UNION ALL 을 사용하는 서브쿼리가 메인테이블에 영향을 받는 경우로, 
UNION 으로 연결된 단위 쿼리들 중에서 처음으로 작성한 단위쿼리에 해당되는 경우이다.
UNION으로 연결되는 첫번째 단위쿼리가 독립적으로 수행하지 못하고 메인테이블로부터 값을 하나씩 공급받는 구조이므로
성능적으로 불리하여 SQL문이 튜닝대상이 된다.

explain				
select 관리자.부서번호,				
      (				
      select 사원1.이름				
        from 사원 as 사원1				
        where 성별 = 'F'				
        and 사원1.사원번호 = 관리자.사원번호				
        union all				
      select 사원2.이름				
        from 사원 as 사원2				
        where 성별 = 'M'				
        and 사원2.사원번호 = 관리자.사원번호				
       ) as 이름				
from 부서관리자 as 관리자;

UNCACHEABLE SUBQUERY

 

메모리에 상주해서 재활용되어야할 서브쿼리가 재사용되지 못할 때 출력되는 유형으로
1) 해당 서브쿼리 안에 사용자 정의함수나 사용자 변수가 포함
2) RAND(), UUID() 함수등을 사용해서 매번 조회 시마다 결과가 달라지는 경우에 해당된다.

자주 호출되는 SQL문이라면 메모리에 서브쿼리 결과가 상주할 수 있도록 변경하는 방향으로 SQL 튜닝 검토 필요

explain			
select *			
from 사원			
where 사원번호 = (			
   select round(rand() * 1000000)			
);

rand() 로 인해 메모리에 캐시 불가

MATERIALIZED

 

IN 절 구문에 연결된 서브쿼리가 임시테이블을 생성한 후, 
조인이나 가공작업 수행 시 출력되는 유형으로
IN 절의 서브쿼리를 임시테이블로 만들어서 이후에 조인작업을 수행하는 것

explain	
select *	
from 사원	
where 사원번호 in (	
   select 사원번호	
    from 급여	
    where 시작일자 > '2020-01-01'	
);

table

테이블명을 표시하는 항목으로
실행계획 정보에서 테이블명을 출력, 서브쿼리(임시테이블) 로 

별도 작업수행 시에는 subquery# 또는 derived# 출력

 

explain			
select 사원.사원번호, 급여.연봉			
from 사원,			
   (			
      select 사원번호,			
            max(연봉) as 연봉			
      from 급여			
        where 사원번호 between 10001 and 20000			
    ) as 급여			
where 사원.사원번호 = 급여.사원번호;

eq_ref

조인에서 처음 읽은 테이블의 컬럼 값을 그 다음 읽어야 할 테이블의 프라이머리 키나 유니크 키 컬럼의 검색 조건에 사용할 때

DERIVED

단위 select쿼리의 실행 결과를 메모리나 디스크에 임시 테이블을 생성하는 것을 의미, 서브 쿼리가 from절에 사용된 경우

 

 

partitions

실행계획의 부가정보로 데이터가 저장된 논리적인 영역을 표시하는 항목

사전에 정의한 전체 파티션 중 특정 파티션에 선택적으로 접근하는 것이 SQL 성능 측면에서 유리하다

 

type

테이블의 데이터를 어떻게 찾을지에 대한 정보를 제공하는 항목이다

테이블을 처음부터 끝까지 전부 확인할 지 아니면 인덱스를 통해 바로 데이터를 찾아갈 지 등을 해석할 수 있다.

 

system

테이블에 데이터가 없거나 한 개만 있는 경우로 성능상 최상의 type

 

create table myisam_테이블 (			
   col1 INT(11) NULL DEFAULT NULL			
)			
ENGINE=MYISAM;

INSERT INTO myisam_테이블 VALUES(1);

explain	
select *	
from myisam_테이블;

const

조회 데이터가 1건일 때 출력되는 유형으로 아주 유리한 방식

 

explain		
select *		
from 사원		
where 사원번호 = 10001;

eq_ref

조인이 수행될 때 드리븐 테이블의 데이터에 접근하여 고유 인덱스로 단 한건의 데이터를 조회하는 방식
드라이빙 테이블과의 조인 키가 드리븐 테이블에 유일하므로 조인이 수행될 때 성능상 가장 유리하다

 

explain				
select 매핑.사원번호, 부서.부서번호, 부서.부서명				
from 부서사원_매핑 as 매핑, 부서				
where 매핑.부서번호 = 부서.부서번호				
and 매핑.사원번호 between 100001 and 100010;

ref

eq_ref 유형과 유사한 방식으로 조인 수행 시 드리븐 테이블의 데이터 접근범위가 두개 이상일 경우
즉, 드라이빙 테이블과 드리븐 테이블이 조인을 수행하면 1:N 관계가 되므로 
드라이빙 테이블의 1개 값이 드리븐 테이블에서는 2개 이상의 데이터로 존재한다
드리븐 테이블 데이터량이 많지 않을 때는 괜찮지만, 

데이터량이 많아지면 접근해야할 데이터 범위가 넓어져서
성능 저하의 원인이 되는지 확인 필요하다