[SQL 튜닝] 형변환으로 인덱스를 활용하지 못하는 나쁜 SQL 문

형변환으로 인덱스를 활용하지 못하는 나쁜 SQL 문

현황 분석

| 튜닝 전 실행 계획 |

급여 테이블에서 현재 유효한 급여 정보만 조회하고자 사용여부 열의 값이 1인 데이터를 출력하는 쿼리

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

- 급여 테이블 컬럼 수 : 2844047 (약 284만 건)

- SQL문 결과 : 총 1건의 결과 출력, 373ms 소요

   COUNT(1) = 42842

- key 항목 : I_사용여부 인덱스 사용

- type 항목 : index → 인덱스 풀스캔 방식으로 수행

- filtered 항목 : 10 → MYSQL 엔진으로 가져온 데이터 중 10% 추출해서 최종 데이터 출력

 즉, rows 항목에 표시된 2,838,438건의 데이터를 스토리지 엔진에서 MYSQL 엔진으로 가져온 뒤 그중 약 10%에 해당하는 28만 건의 데이터가 최종 출력 예측

 

튜닝 수행

SELECT 사용여부, COUNT(1) 
	FROM 급여
GROUP BY 사용여부;

- 총 약 284만 건에 가까운 데이터가 있고, 사용여부 열에는 0,1 데이터가 저장되어 있음

- 사용여부 열의 값이 1인 데이터 건수는 전체 데이터 건수 대비 10% 이하

 

show index from 급여;

- Key_name : PRIMARY와 I_사용여부

- 기본 키(PRIMARY) : 사원번호, 시작일자 순으로 구성

- I_사용여부 인덱스는 사용여부 열로 구성

- 튜닝 대상인 SQL문에서 사용여부 열이 인덱스로 구성, WHERE 절의 조건문으로 작성되었음에도 인덱스 풀 스캔으로 수행되므로 테이블 구조 확인 필요

 

desc 급여;

- 사용여부 열 : 문자형 char(1) 데이터 유형으로 구성

- 튜닝 전 SQL 문에서 WHERE 사원번호 = 1과 같이 숫자 유형으로 데이터에 접근 → 문시적 형변환 발생

- I_사용여부 인덱스를 제대로 활용하지 못하고 전체 데이터를 스캔, 형변환이 발생하지 않도록 SQL문 조정

 

튜닝 결과

| 튜닝 후 실행 계획 |

사용여부 열의 값이 문자 '1'인 경우만 조회하는 쿼리

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

373ms → 7.5ms (속도 개선)

Key : I_사용여부 → 사용여부 = '1' 조건절이 스토리지 엔진에서 전달되어 필요한 데이터만 가져옴

Extra : Using Index (임시 테이블 없이 인덱스만 사용하여 데이터 추출)

사용여부 열을 문자 유형인 CHAR(1)에서 숫자 유형인 INT 변경할 때도 튜닝 대상 SQL문을 수정하지 않고 I_사용여부 인덱스를 사용할 수 있음, 그러나 테이블의 DDL문을 수행해야 하는 부담과 DDL 수행 시의 데이터 잠김(data lock) 현상으로 동시성 저하 문제가 발생할 수 있음

 

TIP 데이터 유형의 중요성
데이터 유형에 맞게 열을 활용해야 내부적인 형변환이 발생되지 않음
형변환의 영향으로 의도한 인덱스를 제대로 사용하지 못하는 경우가 있으니 주의

 

* 참고

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