[MySQL] 파티션

Database/mysql

[MySQL] 파티션

hyun_ji 2022. 1. 13. 17:20

SMALL

데이터가 많아지면 mysql에 부하가 걸리므로 파티션을 나눠줘야 한다.

MySQL에서는 4가지 파티션 종류를 지원한다.

- Range 파티션
- List 파티션
- Hash 파티션
- Key 파티션

Range 파티션

파티션 키의 연속된 범위로 파티션을 정의 하는 방식 이며 가장 일반적으로 사용 되는 파티션 종류 입니다.

MAXVALUE 파티션을 이용해 정의 되지 않은 값의 범위의 키 값을 저장을 하게 됩니다.
Range 파티션에서 NULL은 어떤 값보다 작은 값으로 간주 됩니다.

날짜 컬럼의 값으로 파티션 할 경우 다음의 예시 를 주의 해야 합니다.

- UNIX_TIMESTAMP() 이용한 변환 식을 파티션 키로 사용
- 날짜를 문자열로 포맷팅 한 형태로 사용(예 : '2020-10-10')
- YEAR() 나 TO_DAYS() 함수 이외에 다른 함수가 사용된 파티션

위와 같은 표현식으로 파티션된 테이블에서는 파티션 프루닝이 동작하지 않을수 있습니다.

그래서 날짜 컬럼에서 Range 파티션을 사용 할 경우 파티션 키로는 YEAR() 나 TO_DAYS() 를 사용하는 것이 좋습니다.

- 날짜를 기반으로 데이터가 누적되고 년도나 월 또는 이라 단위로 적재하고 삭제 할 경우
- 범위 기반으로 데이터를 여러 파티션에 나누고자 할때
- 파티션 키 위주로 검색이 자주 일어날때

사용하면 효과를 볼 수 있습니다.

Range 파티션 테이블 생성

CREATE TABLE `tb_range_table`
(id int not null,
name varchar(10),
dept varchar(10),
hiredate date not null default '2010-01-01'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
Partition by range(YEAR(hiredate)) (
partition p0 VALUES LESS THAN(2011) ENGINE=InnoDB,
partition p1 VALUES LESS THAN(2012) ENGINE=InnoDB,
partition p2 VALUES LESS THAN(2013) ENGINE=InnoDB,
partition p3 vVALUES LESS THAN(2014) ENGINE=InnoDB,
partition p999 VALUES LESS THAN MAXVALUE ENGINE=InnoDB
);

VALUES LESS THAN 으로 명시되 값도 작은 값만 해당 파티션에 저장되도록 설정 되며 LESS THAN 절에 명시된 값은 그 파티셔에 포함되지 않습니다.

VALUES LESS THAN MAXVALUE 로 명시되지 않은 레코드를 저장할 파티션을 지정하게 됩니다.
각 파티션별 ENGINE을 별도로 지정하지 않으면 테이블의 스토리지 엔진으로 생성되게 됩니다.

일반테이블에서 Range 파티션 추가

CREATE TABLE `tb_range_table`
(id int not null,
name varchar(10),
dept varchar(10),
hiredate date not null default '2010-01-01'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

ALTER TABLE `tb_range_table` PARTITION BY RANGE(YEAR(hiredate)) (
partition p0 VALUES LESS THAN(2011) ENGINE=InnoDB,
partition p1 VALUES LESS THAN(2012) ENGINE=InnoDB,
partition p2 VALUES LESS THAN(2013) ENGINE=InnoDB,
partition p3 VALUES LESS THAN(2014) ENGINE=InnoDB,
partition p999 VALUES LESS THAN MAXVALUE ENGINE=InnoDB
);

Range 파티션 변경

추가
ALTER TABLE `tb_range_table` ADD PARTITION
(PARTITION p4 VALUES LESS THAN(2015) ENGINE=InnoDB);

2015년 파티션을 추가 하기 위해서 위와 같은 명령어를 통해 파티션 추가를 하면 됩니다.
다만 MAXVALUE 파티션이 있다면 새로운 파티션을 추가 할 수 없습니다.
Error Code: 1481. MAXVALUE can only be used in last partition definition

이럴때는 파티션 분리를 해야 합니다.

기존 파티션 분리
하나의 파티션을 두개 이상의 파티션으로 분리하고자 할 때는 REORGANIZE PARTITION 명령어를 사용 합니다.

ALTER TABLE `tb_range_table` REORGANIZE PARTITION P999 INTO (
PARTITION P4 VALUES LESS THAN (2015),
PARTITION P999 VALUES LESS THAN MAXVALUE
);

기존 파티션 병합
여러개의 파티션을 하나의 파티션으로 병합하는 작업도 REORGANIZE PARTTION 을 사용 합니다.
ALTER TABLE `tb_range_table` REORGANIZE PARTITION P3,P4 INTO (
PARTITION P3 VALUES LESS THAN (2015)
);

삭제
ALTER TABLE `tb_range_table` DROP PARTITION p3;

파티션 정보 조회
SELECT TABLE_SCHEMA, TABLE_NAME,
PARTITION_NAME, PARTITION_ORDINAL_POSITION,
TABLE_ROWS
FROM INFORMATION_SCHEMA.PARTITIONS
WHERE TABLE_NAME = 'tb_range_table';

LIST