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

인덱스를 하나만 사용하는 나쁜 SQL 문

튜닝 전 SQL 문

select *		
from 사원		
where 이름 = 'Matt'		
or 입사일자 = '1987-03-31'

튜닝 전 쿼리라고 해도 짧은 소요시간만을 기준으로 삼지않으며
더 나은 쿼리로 개선할 수 있다면 튜닝대상이 된다.

 

튜닝 전 실행계획

explain	
select *	
from 사원	
where 이름 = 'Matt'	
or 입사일자 = '1987-03-31'

사원 테이블은 테이블 풀 스캔(ALL) 으로 처리된다.

 

튜닝 수행

조건절에 해당하는 데이터 분포 확인

이름=Matt : 233 건

입사일자=1987-03-31 : 111건

 

전체 데이터가 약 30만 건에 달하는 사원 테이블의 데이터 건수와 비교했을 때
이름 컬럼과 입사일자 컬럼의 조건절에서 조회하는 
데이터 건수가 상대적으로 매우 적다는 사실을 유추할 수 있다.

 

233 건

select count(1) 
from 사원;

 

111 건

select count(1) 
from 사원
where 이름 = 'Matt';

 

30만건

select count(1) 
from 사원
where 입사일자 = '1987-03-31';

 

이처럼 소량의 데이터를 조회할 때는 보통 테이블 풀 스캔보다는 인덱스 스캔이 효율적이다

 

사원 테이블의 인덱스

show index
from 사원;

입사일자 컬럼은 확인이 되지만
이름 컬럼이 포함된 인덱스는 없다

따라서 이름 컬럼에 대한 인덱스를 생성해서
각 조건절이 각각의 인덱스를 사용해서
데이터에 접근할 수 있도록 튜닝을 진행한다

 

인덱스 생성 구문은 

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

 

튜닝 후 실행계획

explain		
select *		
from 사원		
where 이름 = 'Matt'		
or 입사일자 = '1987-03-31'

2개의 조건절이 각각 인덱스 스캔으로 수행되고
각 결과는 병합(index_merge) 된다
병합된 결과(Using union(I_이름, I_입사일자))는 최종 결과를 출력한다

 

인덱스 원상 삭제

alter table 사원
drop index I_이름;

---

큰 규모의 데이터 변경으로 인덱스에 영향을 주는 나쁜 SQL 문

튜닝 전 SQL 문

update 사원출입기록
set 출입문 = 'X'
where 출입문 = 'B';

 

튜닝 전 수행결과

DML문을 수행 시 커밋은 기본적으로 자동저장된다.
튜닝 전 SQL 문은 update문이므로 별도 설정을 하지않으면 바로 자동 저장된다.
반복되는 update문 수행 시 자동저장되는 커밋설정을, 
본인이 접속한 세션에 한해서만 자동 저장되지 않도록 변경한다.

 

select @@autocommit;

명령을 사용하면 autocommit 이라는 세션 기준의 시스템변수를 확인한다.

 

자동커밋은 1

수동커밋은 0

 

기본적으로 자동커밋이지만, 수동커밋으로 설정한다.

 

set autocommit=0;

원상복구

rollback;

 

튜닝 전 실행계획

explain	
update 사원출입기록	
set 출입문 = 'X'	
where 출입문 = 'B';

튜닝 수행

튜닝 전 SQL 문은 데이터를 수정하는 update 문이므로
인덱스와 무관한 듯 보일 수도 있다
하지만 update문은 수정할 데이터에 접근 후 set 절의 수정값으로 변경하므로
인덱스로 데이터에 접근한다는 측면에서 인덱스 존재 여부는 중요하다.

한편 조회한 데이터를 변경하는 범위에는 테이블 뿐만 아니라 인덱스도 포함되므로
인덱스가 많은 테이블의 데이터를 변경할 때는 성능적으로 불리하다

튜닝 전 update문에 포함된 테이블의 인덱스 목록 확인

 

show index
from 사원출입기록;

 

튜닝 후 SQL 문

update 사원출입기록
set 출입문 = 'X'
where 출입문 = 'B';

 

튜닝 후 수행결과

오토커밋 확인 후 값이 0이면 수동커밋 설정이므로 인덱스 삭제 후 동일한 SQL 문 수행 시 
수행시간이 대폭 감소한 것을 확인할 수 있다.

select @@autocommit;

 

롤백처리

rollback;

 

튜닝 후 실행 계획

explain	
update 사원출입기록	
set 출입문 = 'X'	
where 출입문 = 'B';

alter table 사원출입기록		
add index I_출입문(출입문);

수동커밋

set autocommit = 1;