[업무에 바로 쓰는 SQL 튜닝] 4.2.1 기본키를 변형하는 나쁜 SQL문

4장은 문제가 있는 SQL문과 개선된 SQL문을 소개해주고 있다. 팀원과 한 파트씩 나눠서 발표 스터디를 진행하기로 했고, 오늘은 그 첫 번째 사례이다.

 

나는 도커 컨테이너 하나를 생성하고 안에 실습할 데이터베이스를 미리 넣어두었다. 그랬더니 컨테이너에 접속만 하면 쉽게 실습할 수 있어서 아주 편리했다!

 

 

다음은 목차이다. 앞으로도 목차는 비슷하게 흘러갈 예정이다.

❌ 문제의 SQL 문
😎 실행 계획 살펴보기
⭕ 사원번호(기본키)가 인덱스를 타도록 수정해 보자
🚀 정리

 

❌ 문제의 SQL 문

[주의] MySQL에서 문자열의 인덱스는 1부터 시작한다.

 

요구사항 :

사원번호가 1100으로 시작하면서 사원번호가 5자리인 사원의 정보를 모두 출력해 주세요.

SELECT *
    FROM 사원
    WHERE SUBSTRING(사원번호, 1, 4) = 1100 // 첫번째 자리부터 4번째 자리까지
        AND LENGTH(사원번호) = 5

 

출력 : 사원 테이블

총 10건의 결과가 출력되고 실습 환경에서 0.12초 소요되었다.

쿼리의 문제점은 없을까?, 더 개선할 포인트가 있지 않을까? 실행 계획을 확인해 보자!

 

😎 실행 계획 살펴보기

Type 항목이 ALL로 나타나므로 테이블 풀 스캔 방식인 것을 알 수 있다.

인덱스를 사용하지 않고 바로 테이블에 접근하여 필요한 범위가 아닌 처음부터 끝까지 스캔하고 있어 비효율적인 쿼리라 추측할 수 있다.

 

그럼 사원 테이블의 기본키와 인덱스를 파악해서 세부적인 원인을 알아보도록 하자.

Key_name 열 정보에서 PRIMARY, I_입사일자, I_성별_성이라는 3개 인덱스가 있다.

  - 기본키는 사원번호

  - I_입사일자 인덱스는 1개의 입사일자로 구성

  - I_성별_성 인덱스는 성별과 성 순서대로 구성

 

그럼 튜닝해야 할 SQL 문은 무엇일까?

여기서 의문이 하나 들어야 한다. ‘문제의 SQL’에서 사원번호 열을 조건문으로 작성했기 때문에 기본키를 통해 특정 범위를 스캔했어야 했는데, 실제로는 왜 풀 테이블 스캔이 발생한 것일까?

 

그 이유는 ‘사원번호’ 칼럼이 기본키이나 사원번호르 그대로 쓰는 대신 SUBSTRING(사원번호, 1, 4)와 LENGTH(사원번호)와 같이 가공하여 작성했으므로, 기본 키를 사용하지 않게되어 테이블 풀 스캔을 수행했기 때문이다.

 

그럼, 다시 사원번호가 인덱스를 타도록 수정하면 되겠다!

 

⭕ 사원번호(기본키)가 인덱스를 타도록 수정해 보자.

// 첫 번째 방법
SELECT *
    FROM 사원
    WHERE 사원번호 BETWEEN 11000 AND 11009

// 두 번째 방법
SELECT *
    FROM 사원
    WHERE 사원번호 >= 11000 AND 사원번호 <= 11009

BETWEEN 구문으로 범위 검색을 수행하거나 비교연산자를 활용해 사원번호가 변형되지 않도록 하였다.

 

해당 쿼리로는 %의 개선이 있었는지 확인해 보자.

총 10건의 결과가 출력되고 0.1232초 → 0.0012초로 성능이 약 98.99% 개선되었다.

 

그럼 이번에도 실행계획을 보면서 인덱스를 잘 활용했는지 살펴보자.

기본 키(key항목 : PRIMARY)의 특정 범위만 스캔(type항목: range)한다는 걸 알 수 있다.

 

🚀 정리

1. 기본키를 변형하면, 인덱스가 사용되지 않고 풀 테이블 스캔이 발생할 수 있다.

2. 실행계획 확인하는 습관을 가지자!

  - Type 항목이 ALL이라면, 풀 스캔 방식이므로 개선 포인트가 있는지 의심

  - 기본키, 인덱스 확인해서 활용이 되고 있는지 확인 

  - 쿼리를 튜닝했다면 실행 계획을 확인해 어느 부분에서 개선이 되었는지 확인

 

 

출처 : [도서] 업무에 바로 쓰는 SQL 튜닝