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

실습관련 기본 세팅

워크벤치 메뉴 > Edit > Preferences

SQL Execution: Limit Rows 체크 해제 및 카운트 999999 로 설정

(*) 기본적으로 1000 로우값 출력이 기본으로 세팅되어있음

SQL 튜닝 준비하기

 

각 테이블별 인덱스 목록

 

급여 PK INDEX

부서 PK INDEX

부서관리자 PK INDEX

부서사원_매핑 PK INDEX

사원 PK INDEX INDEX

사원출입기록 PK INDEX INDEX INDEX

직급 PK

 

(*) 고유 인덱스 : UI_로 시작 (Unique Index)

(*) 비고유 인덱스 : I_로 시작 (Index)

 

인덱스 정보 확인

show index 

from 테이블명;

 

---

SQL문 단순수정으로 착한쿼리 만들기

기본 키를 변형하는 나쁜 SQL문 튜닝 방법

 

튜닝 전 SQL문

select *			
from 사원			
where substring(사원번호, 1, 4) = 1100			
and length(사원번호) = 5;

 

실행계획

explain			
select *			
from 사원			
where substring(사원번호, 1, 4) = 1100			
and length(사원번호) = 5;

튜닝 전 분석

show index 	
from 사원;

사원번호 컬럼이 PK 이므로 데이터에 빠르게 접근가능한데,
쿼리 조건을 보면 사원번호 컬럼을 가공하여 작성하고 있기때문에 기본키를 사용하지 않는다.
따라서 가공된 사원번호 PK를 사용할 수 있도록 조정한다.

 

튜닝 후 SQL 문

select *		
from 사원		
where 사원번호		
between 11000 and 11009;

 

---

사용하지 않는 함수를 포함하는 나쁜 SQL 문

 

튜닝 전 SQL 문

select ifnull(성별, 'no data') as 성별, 			
       count(1) 건수			
from 사원			
group by ifnull(성별, 'no data');

 

실행 계획

explain			
select ifnull(성별, 'no data') as 성별, 			
        count(1) 건수			
from 사원			
group by ifnull(성별, 'no data');

인덱스 풀 스캔방식으로 수행
임시 테이블 사용
파일정렬 사용

 

튜닝 전 분석

- 사원테이블 우선 분석

desc 사원;

파일정렬에 사용되는 성별 컬럼을 확인해보니
M, F 라는 두개의 데이터만 저장한다는 사실을 확인.
ifnull() 를 처리하기위해 내부적으로 별도 임시 테이블을 만들어서 null값 여부를 체크할 필요가 없다.
즉, 성별 열에는 null 값이 존재할 수 없기때문에 null체크 함수가 필요없다.

 

 

튜닝 후 SQL

select 성별,	
       count(1) 건수	
from 사원	
group by 성별;

 

튜닝 후 실행계획

explain	
select 성별,	
       count(1) 건수	
from 사원	
group by 성별;

extra 항목에서 인덱스만 사용함을 확인할 수 있다.