[SQL 튜닝] 엉뚱한 인덱스를 사용하는 나쁜 SQL 문

엉뚱한 인덱스를 사용하는 나쁜 SQL 문

현황 분석

| 튜닝 전 실행 계획 |

사원 테이블에서 입사일자 열의 값이 '1989'로 시작하면서 사원번호가 100000를 초과하는 데이터를 조회하는 쿼리

EXPLAIN
SELECT 사원번호
	FROM 사원
WHERE 입사일자 LIKE '1989%' AND 사원번호 > 100000;

- 사원 테이블 : 300024 (약 30만)

- SQL문 결과 : 총  20001건의 결과 출력, 10.4ms 소요

- key 항목 : PRIMARY → 범위 스캔(type 항목 : range) 수행- 스토리지 엔진으로부터 기본 키를 구성하는 사원번호를 조건으로 데이터를 가져온 뒤, MYSQL 엔진에서 남은 필터 조건(입사일자 LIKE '1989%) 으로 추출하여 filtered 항목에 11.11% 라는 예측 값 출력

 

튜닝 수행

인덱스 현황, WHERE 절의 조건문 열 파악

type 항목 : 범위 스캔(ragne scan) → 사원번호 열로 구성된 기본 키 (key : PRIMARY)

SHOW INDEX FROM 사원;

SELECT COUNT(1) FROM 사원; /* 300024 */
SELECT COUNT(1) FROM 사원 WHERE 입사일자 LIKE '1989%'; /* 28394 */
SELECT COUNT(1) FROM 사원 WHERE 사원번호 > 100000; /* 210024 */

- 입사일자 1989년도 데이터 건수 : 28,394건 (10%)

- 사원번호 > 100000 데이터 건수 : 210,024건 (70%)

- 사원번호가 100,000번을 초과하는 데이터가 전체 데이터 건수 대비 약 70% 나 차지 → 스토리지 엔진에서 데이터에 접근할 때 사원번호 열로 구성된 기본 키 (Key : PRIMARY)로 액세스 하는게 효율적인지 고민

- 입사일자가 1989년인 사원 수 데이터는 전체 데이터 대비 약 10% 차지 → 입사일자 열을 데이터 액세스 조건으로 활용 검토

- I_입사일자 인덱스를 강제로 타도록 USE INDEX 힌트 설정(힌트를 추가하지 않으면 기본 키를 사용하는 인덱스 스캔으로 수행)

- I_입사일자 인덱스로 스캔하지만, 인덱스 루스 스캔(Extra 항목: Using index for skip scan) 방식에 의해 인덱스를 스킵(skip) 하는 오버헤드가 발생할 수 있음

EXPLAIN
SELECT 사원번호
	FROM 사원 USE INDEX(I_입사일자)
WHERE 입사일자 LIKE '1989%' AND 사원번호 > 100000;

입사일자 열 데이터 유형 : date 타입

조건문에서 입사일자 열에 대해 부분검색이 목적인 LIKE 절을 사용하는게 맞는지 확인

연속된 일자의 데이터만 필요한 만큼 다른 방법 고민

DESC 사원;

 

튜닝 결과

| 튜닝 후 실행 계획 |

입사일자 LIKE '1989%'  → 입사일자 >= '1989-01-01' AND 입사일자 < '1990-01-01' 로 변경

EXPLAIN
SELECT 사원번호
	FROM 사원
WHERE 입사일자 >= '1989-01-01' AND 입사일자 < '1990-01-01';

LIKE 절보다 부등호(<,>,<=,>=) 조건절이 우선하여 인덱스를 사용하므로 데이터 접근 범위를 줄일 수 있음

10.4ms → 1.8ms (속도 개선)

사원 테이블에서 I_입사일자 인덱스를 활용하여 범위 스캔(type 항목: range) 수행

테이블에 접근하지 않고 I_입사일자 인덱스만 사용하여 최종 결과 출력 → 커버링 인덱스 스캔(Extra 항목: Using index)

스토리지 엔진으로부터 I_입사일자 인덱스에 있는 데이터를 가져온 뒤 MYSQL 엔진에서 사원번호에 대한 필터 조건으로 데이터를 추출

 

* 참고

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