Partition 이란?

개요

테이블 설계시  쿼리 성능 향상을 위한 방법 중 한가지인 파티션에 대해 공부한 바를 서술함.

환경

DB Tool : DBeaver 

RDBMS : mySQL

목적

파티션에 대한 기초 설명

 

목차

1. 파티션이란?

2. 파티션 사용 이유

3. 파티션 컬럼 선정 기준

4. 파티션 형식 종류

 

---

1. 파티션이란?

 

크기가 큰 테이블을 컬럼을 기준으로 여러개로 분할하는 기능입니다.

논리적으로는 하나의 테이블이지만, 실제 사용시에는 (MySQL 서버 입장애서) 컬럼 기준으로 나누어진 풀 에서 기능을 수행하게 됩니다.

---

2. 파티션 사용 이유

 

하나의 큰 테이블에서 select 하는 것 보다 나누어진 테이블에서 찾는게 더 빠르기 때문에 사용합니다.

따라서 규모가 작은 테이블에서 파티션 적용은 불필요 합니다.

 

인덱스의 크기가 메모리 크기보다 훨씬 큰 경우 파티션 테이블을 이용하여 분할해야 쿼리의 성능이 보장됩니다.

 

SELECT 성능 향상

파티션 이용시 각각의 파티션에 인덱스가 생성되기 때문에 작은 index 탐색 범위로 쿼리속도가 향상됩니다.

 

INSERT 성능 향상

인덱스를 사용하는 테이블에서 파티션으로 리프 노드를 분리하게 되면 자연스럽게 인덱스 크기가 줄어들게 되고, 

큰 인덱스보다는 작은 인덱스가 insert 성능에 도움을 줍니다.

 

정기적 삭제

일정 기간 이후에 삭제해야 하는 데이터의 경우 단순히 파티션을 삭제하는 방식으로 해결 가능합니다.

 

물리적 저장소 분리

 

가 되겠습니다.

---

3. 파티션 컬럼 선정 기준

 

대부분 인덱스 키를 파티션 기준으로 설정합니다.

※무조건 인덱스가 파티션 키의 기준은 아닙니다. ( 만들수는 있지만 성능이 떨어짐 안하니만 못하게 되는 경우 다수)

 

1. WHERE 절의 조건에 파티션의 기준이 되는 컬럼이 속해지는가?

2. 해당 WHERE 절이 INDEX 를 타는 쿼리인가?

 

1번과 2번이 지켜지는 WHERE 절 사용시 최고의 성능을 낼 수 있습니다.

 

---

4.파티션 형식 종류

 

4.1. Range

 

일반적으로 많이 사용하는 방식입니다.

범위 기반 파티션 방식이며, 파티션이 지정이 안된 값들은

MAXVALUE 를 이용해서 미정의된 범위의 값을 저장하게 됩니다.

 

여기서의 null 은 0보다 작은 수로 저장되며 null 진입시 최초의 파티션에 저장되게 됩니다.

따라서 null 여부를 잘 확인해야겠습니다.

 

사용에 적합한 부분

• 날짜를 기반으로 데이터 적재 혹은 삭제가 빈번할 경우
• 파티션 키 위주로 검색이 자주 될 때
• 대량 데이터 삭제

 

또한 날자파티션의 경우 YEAR() 혹은 TO_DAYS() 함수만 사용을 권장하고 있습니다.

( ※내부적 파티션 프루닝 처리 여부)

 

예제

CREATE TABLE `t_testTable` (
  `ID` varchar(13) NOT NULL,
  `YYYYMM` char(6) NOT NULL,
  `DAY` char(2) NOT NULL,
  `HOUR` char(2) NOT NULL,
  `MINUTE` char(2) NOT NULL,
  `VALUE1` decimal(10,2) DEFAULT NULL,
  `VALUE2` decimal(10,2) DEFAULT NULL,
  `VALUE3` decimal(10,2) DEFAULT NULL,
  `VALUE4` char(12) DEFAULT NULL,
  PRIMARY KEY (`ID`,`YYYYMM`,`DAY`,`HOUR`,`MINUTE`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
 PARTITION BY RANGE COLUMNS(`YYYYMM`)
(
 PARTITION `PT_202401` VALUES LESS THAN('202402') ENGINE = InnoDB,
 PARTITION `PT_202402` VALUES LESS THAN('202403') ENGINE = InnoDB,
 PARTITION `PT_202403` VALUES LESS THAN('202404') ENGINE = InnoDB,
 PARTITION `PT_202404` VALUES LESS THAN('202405') ENGINE = InnoDB,
 PARTITION `PT_202405` VALUES LESS THAN('202406') ENGINE = InnoDB,
 PARTITION `PT_202406` VALUES LESS THAN('202407') ENGINE = InnoDB,
 PARTITION `PT_202407` VALUES LESS THAN('202408') ENGINE = InnoDB,
 PARTITION `PT_202408` VALUES LESS THAN('202409') ENGINE = InnoDB,
 PARTITION `PT_202409` VALUES LESS THAN('202410') ENGINE = InnoDB,
 PARTITION `PT_202410` VALUES LESS THAN('202411') ENGINE = InnoDB,
 PARTITION `PT_202411` VALUES LESS THAN('202412') ENGINE = InnoDB,
 PARTITION `PT_202412` VALUES LESS THAN('202501') ENGINE = InnoDB,
 PARTITION `PT_999` VALUES LESS THAN MAXVALUE ENGINE = InnoDB
);

 

4.2. List

 

Range 방식과 비슷하긴 하나,

파티션 키 하나하나 리스트로 생성해야 하며,

 

Range 에서 나머지를 전부 저장하는 MAXVALUE 파티션이 없습니다.

또한 null 값을 구분합니다.

 

사용에 적합한 부분

• 키값을 기준으로 값이 균등하게 적재
• 키값을 기준으로 where 조건에 자주 등장
• null 값 구분시
• 유지보수 면에서, 파티션 미 생성시 에러 발생을 유도할 경우

예제

CREATE TABLE `t_testTable` (
  `ID` varchar(13) NOT NULL,
  `YYYYMM` char(6) NOT NULL,
  `DAY` char(2) NOT NULL,
  `HOUR` char(2) NOT NULL,
  `MINUTE` char(2) NOT NULL,
  `VALUE1` decimal(10,2) DEFAULT NULL,
  `VALUE2` decimal(10,2) DEFAULT NULL,
  `VALUE3` decimal(10,2) DEFAULT NULL,
  `VALUE4` char(12) DEFAULT NULL,
  PRIMARY KEY (`ID`,`YYYYMM`,`DAY`,`HOUR`,`MINUTE`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
 PARTITION BY RANGE COLUMNS(`YYYYMM`)
(
 PARTITION `PT_202401` VALUES IN('202401') ENGINE = InnoDB,
 PARTITION `PT_202402` VALUES IN('202402') ENGINE = InnoDB,
 PARTITION `PT_202403` VALUES IN('202403') ENGINE = InnoDB,
 PARTITION `PT_202404` VALUES IN('202404') ENGINE = InnoDB,
 PARTITION `PT_202405` VALUES IN('202405') ENGINE = InnoDB,
 PARTITION `PT_202406` VALUES IN('202406') ENGINE = InnoDB,
 PARTITION `PT_202407` VALUES IN('202407') ENGINE = InnoDB,
 PARTITION `PT_202408` VALUES IN('202408') ENGINE = InnoDB,
 PARTITION `PT_202409` VALUES IN('202409') ENGINE = InnoDB,
 PARTITION `PT_202410` VALUES IN('202410') ENGINE = InnoDB,
 PARTITION `PT_202411` VALUES IN('202411') ENGINE = InnoDB,
 PARTITION `PT_202412` VALUES IN('202412') ENGINE = InnoDB
);

 

4.3. Hash

 

해시 함수에 의해 레코드가 저장될 파티션이 결정되는 방식

 

사용에 적합한 부분

• 모든 레코드가 비슷한 사용 빈도를 가지고 있는 경우
• 테이블이 너무 커서 파티셔닝을 해야하지만 list,range 중 선택이 모호한 경우

※특이사항

 

파티션 키는 항상 정수형 타입이거나 정수를 반환하는 표현식만 사용할 수 있습니다.

 

Hash 파티션의 경우 처음 생성시의 갯수로 MOD 연산에 따라 저장할 파티션이 결정되는데,

이미 생성된 파티션에 새로운 파티션을 추가할 경우 재배치가 일어나 부하가 많이 발생한다.

 

예제

CREATE TABLE `t_testTable` (
  `ID` int NOT NULL,
  `YYYYMM` char(6) NOT NULL,
  `DAY` char(2) NOT NULL,
  `HOUR` char(2) NOT NULL,
  `MINUTE` char(2) NOT NULL,
  `VALUE1` decimal(10,2) DEFAULT NULL,
  `VALUE2` decimal(10,2) DEFAULT NULL,
  `VALUE3` decimal(10,2) DEFAULT NULL,
  `VALUE4` char(12) DEFAULT NULL,
  PRIMARY KEY (`ID`,`YYYYMM`,`DAY`,`HOUR`,`MINUTE`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
 PARTITION BY HASH (`ID`)
PARTITION 12;

 

위의 list 와 range 예시 처럼 파티션 이름을 명시한 부분이 없기 때문에 삭제가 불가능하다.

파티션 분할도 불가하고 추가만 가능하며, 병합기능도 제공하지 않는다.

 

4.4.key

hash 와 사용법과 특성이 비슷하지만,

MD5()  함수를 이용해 해시 값을 계산하고 그걸 다시 MOD 연산하여 이를 기준으로 데이터를 분배한다.

Hash 파티션에 비해 좀더 균등하게 분배가 가능합니다.

 

※특이사항

hash 와 별개로 정수타입 혹은 이를 반환하는 표현식 외의 데이터 타입에 대해서 파티션키로 사용 가능합니다.

 

예시

CREATE TABLE `t_testTable` (
  `ID` varchar(13) NOT NULL,
  `YYYYMM` char(6) NOT NULL,
  `DAY` char(2) NOT NULL,
  `HOUR` char(2) NOT NULL,
  `MINUTE` char(2) NOT NULL,
  `VALUE1` decimal(10,2) DEFAULT NULL,
  `VALUE2` decimal(10,2) DEFAULT NULL,
  `VALUE3` decimal(10,2) DEFAULT NULL,
  `VALUE4` char(12) DEFAULT NULL,
  PRIMARY KEY (`ID`,`YYYYMM`,`DAY`,`HOUR`,`MINUTE`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
 PARTITION BY KEY (`ID`)
PARTITION 12;

or

CREATE TABLE `t_testTable` (
  `ID` varchar(13) NOT NULL,
  `YYYYMM` char(6) NOT NULL,
  `DAY` char(2) NOT NULL,
  `HOUR` char(2) NOT NULL,
  `MINUTE` char(2) NOT NULL,
  `VALUE1` decimal(10,2) DEFAULT NULL,
  `VALUE2` decimal(10,2) DEFAULT NULL,
  `VALUE3` decimal(10,2) DEFAULT NULL,
  `VALUE4` char(12) DEFAULT NULL,
  PRIMARY KEY (`ID`,`YYYYMM`,`DAY`,`HOUR`,`MINUTE`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
 PARTITION BY KEY ()
PARTITION 12;

 

이렇게 파티션에대해 간단히 알아보았습니다.

 

list 와 range 를 주로 사용하고

key와 hash 는 자주 사용하는 편은 아닌거 같습니다..

DBA 가 다량의 데이터가 적재되는 테이블 설계를 partition을 전재로 설계하나 봅니다..

 

감사합니다.