[SQL 튜닝] 기본 키를 변형하는 나쁜 SQL 문

실무적인 SQL 튜닝 절차



기본 키를 변형하는 나쁜 SQL 문

현황 분석

| 튜닝 전 실행 계획 |

EXPLAIN
SELECT * FROM 사원
WHERE SUBSTRING(사원번호,1,4) =1100
AND LENGTH(사원번호)=5;

- 사원 테이블 컬럼 수 : 300024 (약 30만건)

- SQL문 결과 : 총 10건의 결과 출력, 100ms 소요

- type 항목 : ALL → 테이블 풀 스캔 방식

- 인덱스를 사용하지 않고 테이블에 바로 접근함

- 필요한 범위에만 접근하는 대신 처음부터 끝까지 스캔하기 때문에 비효율적임

 

현황 분석

기본 키와 인덱스 현황 확인

기본 키(PRIMARY) : 사원번호

인덱스 : I_입사일자, I_성별_성(성별, 성) (3개)

SHOW index FROM 사원;

튜닝 전 SQL문에서는 사원번호 열을 조건문(WHERE 절)으로 작성 → 기본 키를 통해 빠르게 접근 가능

SUBSTRING(사원번호,1,4), LENGTH(사원번호) 와 같이 가공하여 작성해서 테이블 풀 스캔으로 수행 됨

가공된 사원번호 열을 변경하여 기본 키를 사용할 수 있도록 조정 (튜닝)

 

튜닝 결과

기본 키를 별다른 변형 없이 작성, BETWEEN 또는 비교 연산자(>=, <=, >, <) 사용

EXPLAIN
SELECT * FROM 사원
WHERE 사원번호 BETWEEN 11000 AND 11009;

EXPLAIN
SELECT * FROM 사원
WHERE 사원번호 >= 11000 AND 사원번호 <= 11009;

100ms → 0.9ms (속도 개선)

type : range

Key : PRIMARY

 

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

현황 분석

| 튜닝 전 실행 계획 |

EXPLAIN
SELECT * FROM 사원
WHERE SUBSTRING(사원번호,1,4) =1100
AND LENGTH(사원번호)=5;

- 사원 테이블 컬럼 수 : 300024 (약 30만건)

- SQL문 결과 : 총 10건의 결과 출력, 100ms 소요

- type 항목 : ALL → 테이블 풀 스캔 방식

- 인덱스를 사용하지 않고 테이블에 바로 접근함

- 필요한 범위에만 접근하는 대신 처음부터 끝까지 스캔하기 때문에 비효율적임

 

튜닝 수행

기본 키와 인덱스 현황 확인

기본 키(PRIMARY) : 사원번호

인덱스 : I_입사일자, I_성별_성(성별, 성) (3개)

SHOW index FROM 사원;

튜닝 전 SQL문에서는 사원번호 열을 조건문(WHERE 절)으로 작성 → 기본 키를 통해 빠르게 접근 가능

SUBSTRING(사원번호,1,4), LENGTH(사원번호) 와 같이 가공하여 작성해서 테이블 풀 스캔으로 수행 됨

가공된 사원번호 열을 변경하여 기본 키를 사용할 수 있도록 조정 (튜닝)

 

튜닝 결과

| 튜닝 후 실행 계획 |

기본 키를 별다른 변형 없이 작성, BETWEEN 또는 비교 연산자(>=, <=, >, <) 사용

EXPLAIN
SELECT * FROM 사원
WHERE 사원번호 BETWEEN 11000 AND 11009;

EXPLAIN
SELECT * FROM 사원
WHERE 사원번호 >= 11000 AND 사원번호 <= 11009;

100ms → 0.9ms (속도 개선)

type : range

Key : PRIMARY

 

* 참고

- 업무에 바로쓰는 SQL 튜닝(도서) - 4장 악성 SQL 튜닝으로 초보자 탈출하기