[MYSQL] DB 파티셔닝

2020 - [KT] CPN

DB 성능을 위해 파티셔닝 작업.

 

대상 테이블

• API_LOG (api 로그 기록 테이블)
• CPN_DTL_USE_HST (쿠폰 사용이력 테이블)
• CPN (전체 쿠폰 저장 테이블)

결과

API_LOG는 RAGE로

CPN_DTL_USE_HST는 HASH로

CPN은 하지 않았다. 이유는 별 차이가 없어서

---

파티셔닝(Partitioning)이란?

계념

• TABLE을 '파티션(Partition)'이라는 작은 단위로 나누어 관리하는 기법이다.
• '파티션(Partition)'기법을 통하여 데이터베이스를 분산처리하고, 성능 저하를 방지한다.

논리적인 데이터(element)들을 다수의 entity로 쪼개는 행위를 뜻하는 일반적인 용어
대용량의 Table이나 index를 관리하기 쉬운 partition이라는 작은 단위로 문리적으로 분할하는 것을 의미한다.

목적

성능(Performance)

• 대용량 Data를 다루는 환경에서 효율적이다.
• Full Scan에서 데이터 Access의 범위를 줄여 성능을 향샹시킨다.
• 많은 insert실행이 있는 OLTP시스템에서 insert작업을 작은 단위인 partition으로 분산시켜 경합을 줄인다.

OLTP란? - OLTP(온라인 트랜잭션 처리)
네트웨크상의 여러 이용자가 실시간으로 DB의 데이터를 갱신하거나 조회하는 등의 작업을 처리하는 방식을 말한다.
OLTP vs OLAP

가용성(Availability)

• 물리적인 파티셔닝으로 인해 전체 데이터의 훼손 가능성을 줄인다.
• 각 partition영역 별로 독립적으로 관리(백업 및 복구)할 수 있다.
• table의 partition단위로 I/O를 분산하여 경합을 줄이기 때문에 update성능을 향상 시킨다.
• (주의! 파티션에서의 업데이트는 delete -> insert로 이루어진다.)

관리용이성 (Manageability)

• 대용량 table들을 제거하여 관리를 쉽게 해준다.

---

'파티션(Partiton)' 종류

• RANG
• LIST
• HASH
• COMPOSITE (주로사용안함. 설명없음.)

RANG 파티셔닝

• 주로 API 로그를 저장하는 테이블에 많이 사용된다.
• 특정 키(컬럼)의 값의 범위를 기준으로 파티셔닝 한다.
• 파티션 키로 최대 16개까지 지정할 수 있다.
• 보관 주기 정책에 따라 과거 데이터를 백업하고 삭제하는 등의 작업을 효율적으로 수행 할 수 있다.

ALTER TABLE `TABLE` ADD PARTITION BY RANGE(DATE) 
(
	PARTITION P01 VALUES LESS THAN ('2020-01-01')
    , PARTITION P02 VALUES LESS THAN ('2020-01-02')
    , PARTITION PM VALUES LESS THAN (MAXVALUE)
);

 

LIST 파티셔닝

• 사용자에 의해 미리 정해진 그룹핑 기준에 따라 데이터를 분할 저장하는 방식이다.
• 불연속 적인 값의 목록으로 결정된다.
• 파티션 키로 1개까지 지정가능하다. (단일 컬럼으로만 파티션 키를 지정해야 한다.)
• DEFAULT 파티션을 생성해 두어야 안전하다. (범위외의 값이 들어갈시 입력 에러가 발생하기 때문)
• 파티션 키값을 NULL값으로도 명시 할 수 있다.
• 파티션 키값의 대소문자를 구분한다.

ALTER TABLE `TABLE` ADD PARTITION BY LIST(AREA)
(
	PARTITION P01 VALUES('A'),
	, PARTITION P02 VALUES('B','C','D')
	, PARTITION P03 VALUES ('E','F')
	, PARTITION P04 VALUES (DEFAULT)
);

 

HASH 파티셔닝

• 파티션 키값에 해시함수를 적용하여 데이터를 분할하는 방식이다.
• 데이터의 물리적 위치를 알수 없다.
• 파티션의 개수를 반드시 명시해야한다.
• null값은 첫 번째 파티션에 위치한다.

ALTER TABLE `TABLE` ADD PARTITION BY HASH(`KEY`)
PARTITIONS 300;

---

파티션 SELECT문

SELECT TABLE_SCHEMA, TABLE_NAME, PARTITION_NAME, PARTITION_ORDINAL_POSITION, TABLE_ROWS
FROM INFORMATION_SCHEMA.PARTITIONS
WHERE TABLE_NAME =  'TABLE' AND TABLE_SCHEMA = 'PROD1';


EXPLAIN PARTITIONS
SELECT TABLE_SCHEMA, TABLE_NAME, PARTITION_NAME, PARTITION_ORDINAL_POSITION, TABLE_ROWS
FROM INFORMATION_SCHEMA.PARTITIONS
WHERE TABLE_NAME =  'TABLE' AND TABLE_SCHEMA = 'PROD1';

파티션 삭제

ALTER TABLE `TABLE` DROP PARTITION `P01`;

 

---

주의!!

• 파티셔닝하려는 테이블에는 CONSTRAINT이 없어야한다.
• 외래키 FULL TEXT INDEX를 사용하면 안된다.
• Table과 index를 같이 파티셔닝해야 한다.
• 파티션 단위로 인덱스를 변경 할 수 없다.
• MYSQL파티션은 최대 1024개 파티션을 가질수 있다.(서브 파티션 포함)
• 파티션 테이블 성능은 일반 테이블보다 떨어질 수 있다.
• 속도 및 성능향상을 위해 파티셔닝을 진행하기 전에 쿼리 튜닝을 먼저 진행하는 것이 효과적이다. (CPN테이블에서 약간 성능 향상을 못봤음. - CPN테이블은 쿼리튜닝만으로도 성능 향상되었음. 이후 파티셔닝 했을때는 별 차이가 없었다.)
• 파티션 테이블을 조건 검색시에는 파티션 키로 무조건 검색해야한다. 그렇지 않은경우에는 FULL SCAN으로 파티셔닝한 의미가 사라진다.
• 파티션 테이블의 ORDER기능은 전체 SORT가 아니다. 파티션 내부에서 각자 SORT이다.
• 파티션 테이블과 파티션의 인덱스 테이블은 전혀 다르다. 파티션된 인덱스는 파티션 테이블과 별개의 것으로, 인덱스를 별도로 파티션한 것이다.
• 파티션을 삭제하면 그안의 데이터도 지워진다.

 

---

https://gmlwjd9405.github.io/2018/09/24/db-partitioning.html

[DB] DB 파티셔닝(Partitioning)이란 - Heee's Development Blog

http://wiki.gurubee.net/pages/viewpage.action?pageId=26742648

1. 테이블 파티셔닝 - [종료]구루비 DB 스터디 - 개발자, DBA가 함께 만들어가는 구루비 지식창고!