[MySQL] 월별 Partition Table 생성, 수정, 삭제

MySQL에서 지원되는 Partition은 Range, List, Hash, Key의 4가지인데, 여기선 Range를 사용하겠습니다.

​

통계 데이터나 로그 데이터처럼 데이터량이 많을 경우, 파티션 키 위주로 조회를 자주 하거나 대량의 과거 데이터를 삭제 시 사용합니다.

​

​

로그 테이블을 하나 생성하겠습니다.

reg_ymdt 컬럼을 사용할 예정인데, Partition RANGE로 사용하려면 해당 테이블에 PK가 지정되어있지 않거나

PK가 지정되어있다면 reg_ymdt 컬럼이 PK로 등록되어야 합니다.

그리고 파티션 range key로 사용할 컬럼은 int 값 이어야 하는데, 날짜로 분리하기 위해 timestamp를 사용하겠습니다.

CREATE TABLE audit_log (
  id BIGINT(20) NOT NULL AUTO_INCREMENT COMMENT '일련번호',
  reg_ymdt TIMESTAMP NOT NULL COMMENT '등록 년월일시간',
  ...
  PRIMARY KEY (id, reg_ymdt),
  KEY IDX_regYmdt (reg_ymdt)
)

 

 

파티션 등록

reg_ymdt로 월별 partition을 등록합니다.

ALTER TABLE audit_log
PARTITION BY RANGE (UNIX_TIMESTAMP(reg_ymdt)) (
	PARTITION p_202010 VALUES LESS THAN (UNIX_TIMESTAMP('2020-11-01 00:00:00')),
	PARTITION p_202011 VALUES LESS THAN (UNIX_TIMESTAMP('2020-12-01 00:00:00')),
	PARTITION p_202012 VALUES LESS THAN (UNIX_TIMESTAMP('2021-01-01 00:00:00')),
	PARTITION p_max VALUES LESS THAN (MAXVALUE)
);

 

 

생성된 파티션 조회

audit_log에 등록된 partition을 조회합니다.

SELECT TABLE_NAME, PARTITION_NAME, PARTITION_METHOD, PARTITION_EXPRESSION, PARTITION_DESCRIPTION
FROM information_schema.PARTITIONS
WHERE TABLE_NAME = 'audit_log';

 

 

파티션에 포함된 데이터 조회

audit_log의 파티션 p_202012에 속한 데이터를 조회합니다.

SELECT *
FROM audit_log PARTITION (p_202012);

 

 

파티션 수정(추가)

만약 p_max 없이 p_202101 파티션을 추가한다고 할 때,

파티션을 추가하기 전 audit_log 테이블에 '202101' 데이터가 들어가 있다면 아래 쿼리는 오류가 날 것입니다.

ALTER TABLE audit_log ADD PARTITION(PARTITION p_202101 VALUES LESS THAN (UNIX_TIMESTAMP('2021-02-01 00:00:00')));

 

혹시 모를 사고를 방지하여 p_max로 파티션을 분리하는 것을 추천합니다.

ALTER TABLE audit_log REORGANIZE PARTITION p_max INTO (
	PARTITION p_202101 VALUES LESS THAN (UNIX_TIMESTAMP('2021-02-01 00:00:00')),
	PARTITION p_max VALUES LESS THAN (MAXVALUE)
);

 

 

파티션 삭제

ALTER TABLE audit_log DROP PARTITION p_202010;

 

 

파티션을 타고 있는지 조회

reg_ymdt 컬럼으로 조회를 했을 경우, 어떤 파티션을 사용했는지 등의 정보를 조회합니다.

EXPLAIN PARTITIONS
SELECT * FROM audit_log
WHERE reg_ymdt BETWEEN '2020-12-11' AND '2020-12-19';