[MYSQL 쿼리튜닝] 파티셔닝

MYSQL 쿼리튜닝

 

파티셔닝은 굉장히 많이 쓰는거 같습니다

저는 히스토리를 조회하거나, 통계값을 뽑아낼때 자주 사용하곤 하는데요

 

이름은 거창하지만 별 건 없습니다

 

바로 예제 한번 봐봅시다.

 

0. 테이블 및 인덱스 정보

급여 테이블
사원번호	연봉	시작일자	종료일자	사용여부
int (PK)	int	date	date	char

 

PK	사원번호
I_사용여부	사용여부

 

 

1. 문제의 예제 쿼리와 실행계획과 전략짜보기

Select count(1)
  From tuning.급여
 Where 시작일자 Between str_to_date('2001-01-01', '%Y-%m-%d') And str_to_date('2001-12-31', '%Y-%m-%d');

-- 실행결과
-- count(1)
   247652

 

2001년도의 급여테이블의 건수를 조회해봤는데요. 24만건 정도고 실행에 0.9초 정도 소요됩니다.

 

explain
Select count(1)
  From tuning.급여
 Where 시작일자 Between str_to_date('2001-01-01', '%Y-%m-%d') And str_to_date('2001-12-31', '%Y-%m-%d');

2001년 급여 실행계획

위는 실행계획을 살펴 본 겁니다

사용여부로 인덱스로 커버링 인덱스를 수행하는 것을 볼 수 있습니다.

 

 

자! 예제는 급여조회 관련 입니다

다들 관심 많은 부분 아닙니까 😄 -> 연 단위로 조회를 많이 한다고 가정해봅시다

 

우선 년도 별로 데이터가 얼마나 분포 되어있는지 살펴봅시다

Select year(시작일자),
       count(1)
  From tuning.급여
 Group By year(시작일자)
 Order By year(시작일자);

-- 실행결과
--  year(시작일자)	count(1)
    '1985' 		 18293
    '1986' 		 37957
    '1987' 		 57440
    '1988' 		 76896
    '1989' 		 95957
    '1990' 		 114561
    '1991' 		 132624
    '1992' 		 151072
    '1993' 		 168139
    '1994' 		 185159
    '1995' 		 201637
    '1996' 		 218309
    '1997' 		 233190
    '1998' 		 247489
    '1999' 		 260957
    '2000' 		 255785
    '2001' 		 247652
    '2002' 		 140930

 

살펴보니 85년부터 02년까지 고루 퍼져있습니다

-> 연 단위로 조회를 많이 한다고 가정했으니, 2001년 데이터를 조회하는데 모든 연도에 접근할 필요없겠죠

-> 파티셔닝을 활용 해보자!

 

2. 파티셔닝 잡기

ALTER TABLE tuning.급여
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)
);

2001년 급여 실행계획

 

partitions에 p01,p02으로 조회하는 것을 볼 수 있다.

파티셔닝 잘됐죠~ 응 잡았죠

실제 쿼리를 실행해보면 0.9초 걸리던 것이

0.071초가 걸리게 되었다.

 

4. 결론

파티셔닝은 위의 예제처럼 대량 조회시에 년도 별로 쪼갠다거나,카테고리별로 쪼개어서 조회효율을 늘려주는 것이다.

 

 

참고자료

업무에 바로쓰는 SQL튜닝

현업