[업무에 바로 쓰는 SQL 튜닝] 4.2.3 형변환으로 인덱스를 활용하지 못하는 나쁜 SQL 문

목차

❌ 문제의 SQL 문
😎 실행 계획 살펴보기
⭕ 형변환이 발생하지 않도록 SQL을 수정해 보자
🚀 정리

 

❌ 문제의 SQL 문

요구사항: 급여 테이블에서 현재 유효한 급여 정보만 조회하는 쿼리를 작성해 주세요.

사용여부 열의 값이 1인 데이터가 유효한 급여 정보입니다.

SELECT COUNT(*)
    FROM 급여
    WHERE 사용여부 = 1;

 

출력:

1건이 출력되었고 데이터는 총 42,842건이며 sql 소요시간은 약 0.48초로 나타났다.

 

😎 실행 계획 살펴보기

key항목이 I_사용여부로 출력되고, type항목이 index이므로 인덱스 풀 스캔 방식으로 테이블의 데이터를 찾고 있다. 그리고 filtered항목이 10이므로 MySQL엔진으로 가져온 데이터 중 10%를 추출해서 최종 데이터를 출력했다는 것을 알 수 있다.

 

여기서는 어떤 문제가 있을까? 우선 급여 테이블의 인덱스 현황을 알아볼 필요가 있다.

SHOW INDEX FROM 급여;

사용여부 열이 인덱스로 구성되어 있지만, 인덱스를 효율적으로 활용하지 못하고 인덱스 풀 스캔으로 수행되고 있는 상황이다.

 

이어서 테이블의 구조를 확인해 보자.

DESC 급여;

사용여부 열은 문자형인 char(1) 데이터 유형이다. 그런데 문제의 SQL문에서는 WHERE 사원번호 = 1과 같이 숫자 유형으로 써서 데이터에 접근했었다. 그럼 DMBS 내부적으로 묵시적 형변환이 발생했을 것이다. 그 결과 I_사용여부 인덱스를 제대로 활용하지 못하고 엔덱스 풀 스캔을 한 것이다!

 

그럼, 형변환이 발생하지 않도록 SQL을 조정하면 해결할 수 있다.

 

⭕ 형변환이 발생하지 않도록 SQL을 수정해 보자

SELECT COUNT(*)
    FROM 급여
    WHERE 사용여부 = '1';

sql 소요시간이 기존 약 0.48초에서 약 0.01초로 개선됨을 확인할 수 있다.

 

실행계획까지 확인해 보자.

key 항목에서 I_사용여부 인덱스를 사용한다는 것을 알 수 있고, type 항목이 ref이므로 인덱스를 통해 특정 조건에 맞는 데이터를 효율적으로 가져온다. 또한, filtered 값이 100이므로 가져온 데이터가 모두 사용되는 것을 알 수 있다.

 

🚀 정리

1. 데이터 유형에 맞게 열을 사용하자!

그래야 내부적인 형변환이 발생되지 않는다. 형변환이 일어나면 인덱스를 제대로 활용하지 못하는 경우가 있음에 주의하자.

 

2. 실행 계획, 테이블의 인덱스 현황 및 테이블 구조를 함께 확인하면서 개선 가능한 포인트가 있는지 확인하는 습관을 들이자!

[실행 계획]

filtered :
예를 들어 10이라는 의미는 스토리지 엔진에서 MySQL엔진으로 가져온 데이터 중 10%를 추출해서 최종 테이터를 출력하는 것

type: 테이블의 데이터를 어떻게 찾을지
  - index : 인덱스 블록을 처음부터 끝까지 훑는 방식으로 인덱스 풀 스캔을 의미함
     → 그다지 효율적이지 않음
  - ref : 인덱스를 이용해 특정 조건(주로 =)에 맞는 데이터를 찾는 방식으로, 조인할 때 주로 사용된다 → 원하는 데이터를 빠르게 찾을 수 있어 효율적

 

[유용한 SQL문]

SHOW INDEX FROM [테이블명];
테이블의 인덱스 현황 확인

DESC [테이블명]; 
테이블의 구조 확인

 

 

 

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