[SQL 튜닝] 분산 없이 큰 규모의 데이터를 사용하는 나쁜 SQL 문

분산 없이 큰 규모의 데이터를 사용하는 나쁜 SQL 문

현황 분석

| 튜닝 전 실행 계획 |

급여 테이블에서 시작일자가 2000년 1월 1일부터 2000년 12월 31일에 해당하는 데이터를 모두 집계하는 쿼리

즉, 2000년도의 급여 데이터 건수를 조회

EXPLAIN
SELECT COUNT(1)
	FROM 급여
	WHERE 시작일자 BETWEEN STR_TO_DATE('2000-01-01', '%Y-%m-%d')
	AND STR_TO_DATE('2000-12-31', '%Y-%m-%d');

- SQL문 결과 : 총 1건의 결과(255,785) 출력, 338ms 소요

- 급여 테이블의 I_사용여부 인덱스(key 항목 : I_사용여부)를 활용해서 커버링 인덱스로 수행

- 테이블 접근 없이 인덱스만으로 원하는 데이터를 조회

 

튜닝 수행

2000년도의 데이터는 255,785건으로, 전체 급여 데이터의 약 9% 수준(255,785/2,844,047*100)임을 산출

 

/* 2844047 */
SELECT COUNT(1)
	FROM 급여;

- 다른 연도의 데이터 건수 분포 확인 : 1986년부터 2002년까지 고루 퍼져 있음을 확인

- 이때 주로 특정 월 또는 연 대상으로 서비스가 호출되므로 모든 연도의 데이터에 접근할 필요 없음

SELECT YEAR(시작일자), COUNT(1)
	FROM 급여
GROUP BY YEAR(시작일자);

- 하나의 테이블로 구성된 급여 테이블을 시작일자라는 열로 논리적으로 분할하는 파티셔닝을 할 수 있음

- 1985년 12월 31일보다 작은 시작일자 데이터는 p85라는 파티션에 적재되고, 1986년 12월 31일보다 작은 시작일자 데이터는 p86 파티션에 적재되는 방식

- 시작일자가 범위 기준으로 호출되므로 범위 방식 파티션으로 설정

ALTER TABLE 급여
partition by range COLUMNS (시작일자)
(
	partition p85 values less than ('1985-12-31'),
	partition p86 values less than ('1986-12-31'),
	partition p87 values less than ('1987-12-31'),
	partition p88 values less than ('1988-12-31'),
	partition p89 values less than ('1989-12-31'),
	partition p90 values less than ('1990-12-31'),
	partition p91 values less than ('1991-12-31'),
	partition p92 values less than ('1992-12-31'),
	partition p93 values less than ('1993-12-31'),
	partition p94 values less than ('1994-12-31'),
	partition p95 values less than ('1995-12-31'),
	partition p96 values less than ('1996-12-31'),
	partition p97 values less than ('1997-12-31'),
	partition p98 values less than ('1998-12-31'),
	partition p99 values less than ('1999-12-31'),
	partition p00 values less than ('2000-12-31'),
	partition p01 values less than ('2001-12-31'),
	partition p02 values less than ('2002-12-31'),
	partition p03 values less than (MAXVALUE)
);

 

튜닝 결과

| 튜닝 후 실행 계획 |

SQL 문 자체는 변경 사항 없음

EXPLAIN
SELECT COUNT(1)
	FROM 급여
	WHERE 시작일자 BETWEEN STR_TO_DATE('2000-01-01', '%Y-%m-%d')
	AND STR_TO_DATE('2000-12-31', '%Y-%m-%d');

- SQL문 결과 : 338ms → 87.7ms (시간 단축)

- 범위 파티션을 설정하면 시작일자 데이터가 2000년도인 파티션에만 접근하여 SQL 문 효율을 올릴 수 있음

- 2000년도 데이터만 있는 p00 파티션에 접근한 뒤, 2000-12-31 시작일자의 다음 데이터도 2000년인지 확인하는 작업이 수행되므로 2001년 데이터까지 접근

- key항목(partitions) : p00,p01 

 

* 참고

- 업무에 바로쓰는 SQL 튜닝(도서) - 5장 악성 SQL 튜닝으로 전문가 되기