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

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

 

튜닝 전 SQL 문

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

튜닝 전 실행계획

id가 1인 행만 있으므로 먼저 출력된 사원테이블이 데이터에 접근하는 드라이빙 테이블이고
나중에 출력된 부서관리자 테이블이 드리븐 테이블임을 알 수 있다.
사원 테이블은 사원번호 > 300000 조건때문에 레인지스캔을 수행하며
기본키를 활용해서 데이터에 접근할 것임을 알 수 있다.
부서관리자 테이블은 사원.사원번호 = 관리자.사원번호 의 외부 조인에 따라 
기본키를 활용하여 중첩루프조인할 때마다
1건의 데이터(rows 항목 : 1)에 접근하는 걸 확인할 수 있다.

 

튜닝 후 SQL 문

최종적으로 필요한 사원번호의 건수를 구하는 과정에서 부서관리자 테이블은 필요없다.
외부 조인의 경우 드리븐 테이블인 부서관리자 테이블은 있어도 되고 없어도 되는 역할이기때문
부서관리자 테이블과 관련된 부분을 제거하고 
사원테이블의 데이터에만 접근하도록 SQL 문을 간소화한다

 

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

 

튜닝 후 실행계획

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

부서관리자 테이블의 연산작업이 제거되고
사원테이블에 대한 실행계획만 존재

 

---

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

튜닝 전 SQL 문

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

 

튜닝 전 실행계획

데이터에 먼저 접근하는 부서사원_매핑 테이블은 인덱스 풀스캔(index) 방식으로 데이터에 접근
여기서 인덱스 풀 스캔 방식은 부서사원_매핑 테이블의 데이터 접근을 구체화할 수 있는 조건문이 없으므로
I_부서번호 인덱스로 처음부터 끝까지 전체 인덱스를 스캔한다.

드리븐 테이블인 부서관리자 테이블은 관리자.부서번호 = 매핑.부서번호 조건절로 데이터에 접근한다.
이 때 중복을 제거하는 distinct 연산도 수행한다.

 

튜닝 후 SQL 문

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

 

from 절에서 부서사원_매핑 테이블의 데이터를 가져올 때 
부서번호 데이터를 미리 중복제거한다.
제거된 부서사원_매핑 테이블의 데이터에 대해 부서관리자 테이블은
같은 부서번호 데이터가 있는지 여부만 판단한다
굳이 부서관리자 테이블의 데이터를 모두 확인하지 않고도
동일한 부서번호가 있다면 이후의 데이터에는 더 접근하지 않는
EXISTS 연산자를 활용하는 것이다

 

튜닝 후 실행계획

튜닝 후 실행 계획을 살펴보면 id가 똑같이 1로 나타난 부서관리자 테이블과 
<derived2> 테이블이 조인수행함을 알 수 있다.
derived2 테이블은 id가 2인 행의 인라인 뷰로 
from 절에 distinct 작업까지 마친 매핑테이블이다.
distinct 작업을 수행하고자 I_부서번호 인덱스로 정렬한 후 
중복을 제거하겠다는 의미로 Using index fro group-by 가 표시된다

즉, 드라이빙 테이블인 부서관리자 테이블은 전체 24개 데이터를 인덱스 풀 스캔으로 수행한 후
드리븐 테이블인 중복제거된 부서사원_매핑 테이블과 조인한다.

 

---

인덱스없이 작은 규모의 데이터를 조회하는 나쁜 SQL 문

튜닝 전 SQL 문

select *	
from 사원	
where 이름 = 'Georgi'	
and 성 = 'Wielonsky'

튜닝 전 실행계획

explain	
select *	
from 사원	
where 이름 = 'Georgi'	
and 성 = 'Wielonsky'

사원 테이블을 테이블 풀 스캔(ALL)해서 데이터를 조회한다.

조건절로 필요한 데이터를 추출(Using where)하여 출력한다.

 

튜닝 수행

select count(distinct(이름)) 이름_개수,			
	count(distinct(성)) 성_개수,		
	count(1) 전체		
from 사원;

최종적으로 단 1건의 데이터를 갖고오고자 테이블을 처음부터 끝까지
스캔하는 방식은 비효율적일 수 있다.
조건절에 해당하는 열들이 자주 호출된다면, 인덱스로
빠른 데이터 접근을 유도하는 방식으로 튜닝을 하면된다.

이름 컬럼과 성 컬럼을 대상으로 인덱스를 생성하기 전에 먼저 더 다양한 값이 있는
컬럼이 뭔지 파악한다.

이름 컬럼의 데이터에는 1275개의 값이 있고
성 컬럼에는 1637개의 값이 있으므로
데이터 범위를 더 축소할 수 있는 성 컬럼을 선두 컬럼으로 삼아 
인덱스를 생성해본다.

 

alter table 사원			
add index I_사원_성_이름 (성, 이름);

I_사원_성_이름 인덱스 생성
값은 성 + 이름 순서

 

인덱스 생성 후 사원 테이블의 인덱스 목록 확인

show index
from 사원

튜닝 후 SQL 문

select *	
from 사원	
where 이름 = 'Georgi'	
and 성 = 'Wielonsky'

 

튜닝 후 실행계획

explain	
select *	
from 사원	
where 이름 = 'Georgi'	
and 성 = 'Wielonsky'

생성된 인덱스 데이터 원상 삭제

alter table 사원		
drop index I_사원_성_이름;