[SQL 튜닝 실습] SQL 튜닝 기초 4

인덱스 고려없이 열을 사용하는 나쁜 SQL 문

튜닝 전 SQL 문

select 성, 성별, count(1) as 카운트
from 사원
group by 성, 성별

 

튜닝 전 실행 계획

explain		
select 성, 성별, count(1) as 카운트		
from 사원		
group by 성, 성별

I_성별_성 인덱스키를 가지고 인덱스 풀스캔(Using index)으로 접근한다.
성과 성별 컬럼 순으로 재정렬하기위해 Using temporary 를 사용한다.

 

사원 테이블의 인덱스

show index	
from 사원

튜닝 전 SQL 문에서 사원테이블의 I_성별_성 인덱스를 활용하는데도 메모리나 디스크에 임시 테이블을 꼭 생성해야할지 고민해야한다.
I_성별_성 인덱스는 성별과 성 컬럼순으로 생성된 오브젝트로 해당 인덱스는 성별 열 컬럼기준으로 정렬된 후 성 컬럼 기준으로 정렬되었다는 말이다.
그루핑을 성 + 성별 순으로 할때와 성별 + 성 순으로 수행할 때의 결과는 동일하다.
이미 존재하는 I_성별_성 인덱스를 최대한 활용하려면 인덱스 순서대로 그루핑하면 된다. 
그러면 별도 임시테이블을 생성하지않고도 그루핑과 카운트 연산을 수행할 수 있다.

 

튜닝 후 SQL 문

select 성, 성별, count(1) as 카운트			
from 사원			
group by 성별, 성

 

튜닝 후 실행계획

explain			
select 성, 성별, count(1) as 카운트			
from 사원			
group by 성별, 성

별도로 임시 테이블을 생성하지않고 I_성별_성 인덱스만으로 그루핑 이후의 정렬 작업까지 수행된다.
Extra 컬럼에서 Using temporary 가 사라졌다는 것을 확인할 수 있다.

 

---

엉뚱한 인덱스를 사용하는 나쁜 SQL 문

튜닝 전 SQL 문

select 사원번호
from 사원
where 입사일자 like '1989%'
and 사원번호 > 100000

 

튜닝 전 실행계획

explain		
select 사원번호		
from 사원		
where 입사일자 like '1989%'		
and 사원번호 > 100000

레인지 스캔으로 사원번호 컬럼으로 구성된 PK 라는 것을 알 수 있다.

 

 

튜닝 수행

 

전체 데이터 대비 약 10%를 차지하므로 입사일자 컬럼을 데이터 액세스 조건으로 활용 검토

전체 데이터 건수 대비 약 70%나 차지하는 만큼
스토리지 엔진에서 데이터에 접근 시 사원번호 컬럼으로 구성된 PK로 액세스하는게 
과연 효율적일지 고민해야한다.

 

이후 I_입사일자 인덱스를 강제로 타도록 USE INDEX 힌트를 설정 후 실행계획을 출력
(*) 힌트를 추가하지않을 경우 기본키를 사용하는 인덱스 스캔으로 수행

 

 

explain		
select 사원번호		
from 사원 use index(I_입사일자)		
where 입사일자 like '1989%'		
and 사원번호 > 100000

 

I_입사일자 인덱스를 강제로 타도록 USE INDEX 힌트를 설정한 후 실행 계획을 출력
(*) 힌트를 추가하지 않으면 기본 키를 사용하는 인덱스 스캔으로 수행

단, 인덱스 루스 스캔 방식에 의해 인덱스를 스킵하는 오버헤드가 발생할 수 있다.

 

LIKE 절 보다 부등호 조건절이 우선하여 인덱스를 사용하므로 데이터 접근 범위를 줄일 수 있다.

 

튜닝 후 SQL 문

 

기존

입사일자 like '1989%'

수정

입사일자 >= '1989-01-01' and 입사일자 < '1990-01-01'

select 사원번호					
from 사원					
where 입사일자 >= '1989-01-01' and 입사일자 < '1990-01-01'					
and 사원번호 > 100000;

 

튜닝 후 실행계획

explain					
select 사원번호					
from 사원					
where 입사일자 >= '1989-01-01' and 입사일자 < '1990-01-01'					
and 사원번호 > 100000;

 

I_입사일자 인덱스를 활용해서 레인지 스캔을 수행한다.

테이블에 접근하지않고 I_입사일자 인덱스만 사용해서 최종결과를 출력하는데 이를 커버링 인덱스 스캔(Extra 항목 : Using index)이라고 한다.