[업무에 바로 쓰는 SQL 튜닝] 4. 악성 SQL 튜닝으로 초보자 탈출하기

양바른 님의 "Real MySQL" 책을 정리한 포스팅 입니다.

1. 준비

실무적인 튜닝 절차 이해하기

1. 현황 파악

• 결과 및 소요 시간 확인
  • 실행계획 분석 ("EXPLAIN ANALYZE")
  • 단계별 소요 시간 확인 ("SHOW PROFILE FOR QUERY")
  • 쿼리 비용 확인 ("SHOW STATUS LIKE 'Last_query_cost'")
• 조인/서브쿼리 구조 확인 ("EXPLAIN")
• 동등/범위 조건
  • 프라이머리 키나 유니크 키로 사용되는 지 확인
  • range로 처리되는지 확인
  • ICP를 활용할 수 있는지 확인

 

2. 튜닝 방향 판단 및 개선

 

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

기본키 변형

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

• 사원번호 컬럼이 PK로 걸려있음
• PK 컬럼을 가공하였으므로 인덱스를 타지 못해 풀 테이블 스캔을 하였음
• 가공하지 않고 조건식을 조정하여 같은 결과를 내는 조건식으로 새로 작성

 

중복 제거 

explain 
select distinct 사원.사원번호, 사원.이름, 사원.성, 부서관리자.부서번호 
from 사원 join 부서관리자 on (사원.사원번호 = 부서관리자.사원번호);

• Distinct 구문을 수행하고자 별도의 임시 테이블을 만듬 (Using Temporary)

 

explain 
select 사원.사원번호, 사원.이름, 사원.성, 부서관리자.부서번호 
from 사원 join 부서관리자 on (사원.사원번호 = 부서관리자.사원번호);

• 사원 테이블의 사원번호 열이 기본 키라서 중복된 데이터가 출력될 수 없음
• Distinct 구문을 사용할 필요 없음

 

UNION

explain
select 'M' as 성별, 사원번호 
from 사원 where 성별 = 'M' and 성 = 'Baba'
union
select 'F' as 성별, 사원번호 
from 사원 where 성별 = 'F' and 성 = 'Baba';

• 임시 테이블을 생성하여 각 결과의 UNION 작업 수행 (Using Temporary)

 

explain
select 'M' as 성별, 사원번호 
from 사원 where 성별 = 'M' and 성 = 'Baba'
union all
select 'F' as 성별, 사원번호 
from 사원 where 성별 = 'F' and 성 = 'Baba';

• 두 결과를 합칠 뿐, 중복을 제거하는 작업은 포함되지 않음
• 즉, 임시테이블 사용 필요 없음

 

date 타입 like 사용

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

• PRIMARY KEY(입사일자)로 인덱스 레인지 스캔하여 읽어옴
  • 범위 조건을 사용할 경우 채택됨
• Using WHERE을 사용하여 11.11 %의 필터링을 통해 결과 도출

 

explain
select 사원번호 from 사원 
where 입사일자 between '1989-01-01' and '1989-12-31' 
and 사원번호 > 100000;

• I_입사일자 로 인덱스 레인지 스캔하여 읽어옴
  • 범위 조건을 사용할 경우, 해당 인덱스로 사용될 가능성이 높음
• Using Index를 사용하여 커버링 인덱스로 데이터 가져옴
  • 테이블 접근 X

 

동등조건으로 인덱스 사용

explain
select * from 사원출입기록 where 출입문 = 'B';

• I_출입문 인덱스를 사용하여 ref 방식으로 조회함
  • 인덱스 스캔 방식으로, 랜덤 액세스로 데이터를 가져옴
• 출입문값이 'B'인 레코드가 반을 차지함
  • 인덱스로 가져오는 것이 적절한지 고민해보아야 함

 

explain
select * from 사원출입기록 IGNORE INDEX(I_출입문) where 출입문 = 'B';

• 인덱스를 사용하지 않고 ALL 방식으로 풀 테이블 스캔함
• 시퀀셜 액세스로 디스크 접근을 최소화할 수 있음

 

범위 조건으로 인덱스 사용

explain 
select 이름, 성 
from 사원 
where 입사일자 between str_to_date('1994-01-01', '%Y-%m-%d') 
                 and str_to_date('2000-12-31', '%Y-%m-%d');

• range
  • I_입사일자 인덱스를 사용하여 인덱스 레인지 스캔을 수행함

• Using index condition
  • ICP를 통해 스토리지 엔진에서 추가 필터링을 수행함
  • 인덱스에서 읽은 데이터를 필터링 하고 스토리지 엔진에 데이터 요청함
• Using MRR
  • MRR를 사용하여 디스크 랜덤 액세스를 줄이고, 순차적인 읽기를 수행하여 성능을 최적화함
  • 인덱스로 읽은 데이터를 버퍼에 저장하고 디스크에 시퀀셜 액세스하여 디스크 접근을 최소화

 

explain 
select 이름, 성 
from 사원 
where year(입사일자) between '1994' and '2000';

• 조건을 만족하는 데이터 비율이 높아 테이블 풀스캔으로 처리하는게 나을 수 있음
  • 인덱스를 사용하면 랜덤 I/O가 많아짐
  • 테이블 풀 스캔은 연속된 블록을 한번에 읽어 순차 I/O가 발생함
• 컬럼을 가공하여 인덱스를 사용하지 못하게 하기

 

3. 테이블 조인 설정 변경으로 착한 쿼리 만들기

작은 테이블이 먼저 조인에 참여

• 드라이빙 테이블에서 큰 크기의 드리븐 테이블에 대해 랜덤 액세스하면 비효율적
• 드리븐 테이블 크기만큼 디스크 접근을 하기 때문

 

메인 테이블에 계속 의존

• select_type 항목에 DEPENDENT 키워드가 있으면 외부 테이블에서 조건절을 받은 뒤 처리되어야 함
  • 메인 쿼리가 실행될 때마다 불필요하게 반복적으로 실행될 가능성 있음
• join + group by로 해결할 수 있음

 

불필요한 조인

• 드라이빙 테이블의 데이터가 드리븐 테이블과 매칭 여부만 알면 될 경우 EXISTS로 표현하기
  • 불필요한 데이터를 가져오지 않고 존재 여부만 알도록 할 수 있음
  • 임시테이블을 사용하여 중간 결과를 저장함으로써 재사용이 가능해짐