[MySQL] Table Partitioning - MAXVALUE 파티션에 따른 테이블 재구성시 유의사항

파티션을 재구성할시 보통 REORGANIZE PARTITION을 자주 사용합니다.

이때 몇가지 유의해야 할 사항이 있습니다. 바로 MAXVALUE 파티션 유무에 따른 재구성입니다.

 

■ 예제 테이블

CREATE TABLE dbadm.part_table_test (
  `id` int(10) unsigned NOT NULL,
  `sec_id` int(10) unsigned NOT NULL,
  `target_date` datetime NOT NULL,
  PRIMARY KEY (id,target_date)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
/*!50500 PARTITION BY RANGE  COLUMNS(target_date)
(PARTITION part201912 VALUES LESS THAN ('2020-01-01') ENGINE = InnoDB,
 PARTITION part202001 VALUES LESS THAN ('2020-02-01') ENGINE = InnoDB,
 PARTITION part202002 VALUES LESS THAN ('2020-03-01') ENGINE = InnoDB,
 PARTITION part202003 VALUES LESS THAN ('2020-04-01') ENGINE = InnoDB,
 PARTITION part202004 VALUES LESS THAN ('2020-05-01') ENGINE = InnoDB,
 PARTITION part202005 VALUES LESS THAN ('2020-06-01') ENGINE = InnoDB,
 PARTITION part202006 VALUES LESS THAN ('2020-07-01') ENGINE = InnoDB,
 PARTITION part202007 VALUES LESS THAN ('2020-08-01') ENGINE = InnoDB,
 PARTITION part202008 VALUES LESS THAN ('2020-09-01') ENGINE = InnoDB,
 PARTITION part202009 VALUES LESS THAN ('2020-10-01') ENGINE = InnoDB) */
;

CREATE TABLE dbadm.part_table_test (
  `id` int(10) unsigned NOT NULL,
  `sec_id` int(10) unsigned NOT NULL,
  `target_date` datetime NOT NULL,
  PRIMARY KEY (id,target_date)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
/*!50500 PARTITION BY RANGE  COLUMNS(target_date)
(PARTITION part201912 VALUES LESS THAN ('2020-01-01') ENGINE = InnoDB,
 PARTITION part202001 VALUES LESS THAN ('2020-02-01') ENGINE = InnoDB,
 PARTITION part202002 VALUES LESS THAN ('2020-03-01') ENGINE = InnoDB,
 PARTITION part202003 VALUES LESS THAN ('2020-04-01') ENGINE = InnoDB,
 PARTITION part202004 VALUES LESS THAN ('2020-05-01') ENGINE = InnoDB,
 PARTITION part202005 VALUES LESS THAN ('2020-06-01') ENGINE = InnoDB,
 PARTITION part202006 VALUES LESS THAN ('2020-07-01') ENGINE = InnoDB,
 PARTITION part202007 VALUES LESS THAN ('2020-08-01') ENGINE = InnoDB,
 PARTITION part202008 VALUES LESS THAN ('2020-09-01') ENGINE = InnoDB,
 PARTITION part202009 VALUES LESS THAN ('2020-10-01') ENGINE = InnoDB,
 PARTITION partMAXVAL VALUES LESS THAN (MAXVALUE) ENGINE = InnoDB) */
;

맨 뒤에 MAXVALUE 파티션이 있고 없고의 차이입니다.

 

▶에러 사항 1 : 맨 앞에 파티션에 파티션 추가.

테이블 맨 앞에 즉 2020-01-01 이전보다 파티션을 만들시 ADD로는 안됩니다. REORGANIZE PARTITION을 해야 파티션 추가가 가능합니다.

ALTER TABLE dbadm.part_table_test 
REORGANIZE PARTITION  part201912 INTO ( 
PARTITION part201911 VALUES LESS THAN ('2019-11-01') ENGINE = InnoDB, 
PARTITION part201912 VALUES LESS THAN ('2019-12-01') ENGINE = InnoDB 
);
SQL Error [1520] [HY000]: Reorganize of range partitions cannot change total ranges except for last partition where it can extend the range

Reorganize를 해도 에러가 남. 전체 파티션을 Reorganize해야 가능. 아래에서 설명함.

 

ALTER TABLE dbadm.part_table_test 
ADD PARTITION 
(
PARTITION part201910 VALUES LESS THAN ('2019-11-01') ENGINE = InnoDB, 
PARTITION part201911 VALUES LESS THAN ('2019-12-01') ENGINE = InnoDB 
);
SQL Error [1493] [HY000]: VALUES LESS THAN value must be strictly increasing for each partition

파티션 추가도 에러가 남.

위 에러사항은 MAXVALUE가 있고 없고의 유무없이 공통적으로 나는 에러입니다.

 

 

 

▶에러사항 2 : 맨뒤에 파티션 추가

MAXVALUE 파티션이 없다면 기존 파티션 뒤에 파티션을 추가하는데 문제는 없습니다.

ALTER TABLE dbadm.part_table_test 
ADD PARTITION 
( 
PARTITION part202010 VALUES LESS THAN ('2020-11-01') ENGINE = InnoDB, 
PARTITION part202011 VALUES LESS THAN ('2020-12-01') ENGINE = InnoDB 
);

그러나 MAXVALUE 파티션이 있다면 파티션을 추가하는데 문제가 발생합니다.

SQL Error [1493] [HY000]: VALUES LESS THAN value must be strictly increasing for each partition

 

이유는 MAXVALUE 파티션에 어떠한 값이 들어가 있을지 누구도 장담할 수 없기 때문입니다. 그래서 MAXVALUE가 있는 파티션은 무조건 REORGANIZE PARTITION을 해야 파티션 분할이 가능합니다. MAXVALUE 파티션의 안의 레코드값을 재구성해서 값에 맞는 파티션에 입력하기 때문입니다.

 

■ 파티션 재구성 명령어.

맨 뒤의 MAXVALUE 파티션이 없다면 파티션 하나를 가지고도 재구성이 가능합니다.

ALTER TABLE dbadm.part_table_test 
REORGANIZE PARTITION  part202009 INTO ( 
 PARTITION part202009 VALUES LESS THAN ('2020-10-01') ENGINE = InnoDB, 
 PARTITION part202010 VALUES LESS THAN ('2020-11-01') ENGINE = InnoDB 
);

 

그러나 MAXVALUE가 있다면 파티션을 재구성해야 합니다.

MAXVALUE 파티션 존재에 따른 재구성은 파티션 전체를 들었다 놔야 합니다. 즉 모든 파티션에 재구성을 요구합니다.

ALTER TABLE dbadm.part_table_test 
REORGANIZE PARTITION  part201912, part202001,part202002, part202003, part202004, part202005, 
part202006, part202007, part202008, part202009, partMAXVAL INTO ( 
 PARTITION part202019 VALUES LESS THAN ('2020-10-01') ENGINE = InnoDB, 
 PARTITION part202010 VALUES LESS THAN ('2020-11-01') ENGINE = InnoDB, 
 PARTITION part202011 VALUES LESS THAN ('2020-12-01') ENGINE = InnoDB, 
 PARTITION part202012 VALUES LESS THAN ('2021-01-01') ENGINE = InnoDB, 
 PARTITION part202101 VALUES LESS THAN ('2021-02-01') ENGINE = InnoDB, 
 PARTITION part202102 VALUES LESS THAN ('2021-03-01') ENGINE = InnoDB, 
 PARTITION partMAXVAL VALUES LESS THAN (MAXVALUE) ENGINE = InnoDB 
);

보시는바와 같이 기존 테이블안에 있는 모든 파티션을 참여시켜 아예 파티션 자체를 재구성하는 방식입니다.

이 방식은 원하는 파티션을 제거할 수도 있으며 추가할 수도 있습니다.

 

▶ 파티션 재구성 예제

기존 파티션

CREATE TABLE `part_table_test` ( 
  `id` int(10) unsigned NOT NULL, 
  `sec_id` int(10) unsigned NOT NULL, 
  `target_date` datetime NOT NULL, 
  PRIMARY KEY (`id`,`target_date`) 
) ENGINE=InnoDB DEFAULT CHARSET=utf8 
/*!50500 PARTITION BY RANGE  COLUMNS(target_date) 
(PARTITION part201912 VALUES LESS THAN ('2020-01-01') ENGINE = InnoDB, 
 PARTITION part202001 VALUES LESS THAN ('2020-02-01') ENGINE = InnoDB, 
 PARTITION part202002 VALUES LESS THAN ('2020-03-01') ENGINE = InnoDB, 
 PARTITION part202003 VALUES LESS THAN ('2020-04-01') ENGINE = InnoDB, 
 PARTITION part202004 VALUES LESS THAN ('2020-05-01') ENGINE = InnoDB, 
 PARTITION part202005 VALUES LESS THAN ('2020-06-01') ENGINE = InnoDB, 
 PARTITION part202006 VALUES LESS THAN ('2020-07-01') ENGINE = InnoDB, 
 PARTITION part202007 VALUES LESS THAN ('2020-08-01') ENGINE = InnoDB, 
 PARTITION part202008 VALUES LESS THAN ('2020-09-01') ENGINE = InnoDB, 
 PARTITION part202009 VALUES LESS THAN ('2020-10-01') ENGINE = InnoDB, 
 PARTITION partMAXVAL VALUES LESS THAN (MAXVALUE) ENGINE = InnoDB) */

 

재구성 파티션

CREATE TABLE `part_table_test` ( 
  `id` int(10) unsigned NOT NULL, 
  `sec_id` int(10) unsigned NOT NULL, 
  `target_date` datetime NOT NULL, 
  PRIMARY KEY (`id`,`target_date`) 
) ENGINE=InnoDB DEFAULT CHARSET=utf8 
/*!50500 PARTITION BY RANGE  COLUMNS(target_date) 
(PARTITION part202009 VALUES LESS THAN ('2020-10-01') ENGINE = InnoDB, 
 PARTITION part202010 VALUES LESS THAN ('2020-11-01') ENGINE = InnoDB, 
 PARTITION part202011 VALUES LESS THAN ('2020-12-01') ENGINE = InnoDB, 
 PARTITION part202012 VALUES LESS THAN ('2021-01-01') ENGINE = InnoDB, 
 PARTITION part202101 VALUES LESS THAN ('2021-02-01') ENGINE = InnoDB, 
 PARTITION part202102 VALUES LESS THAN ('2021-03-01') ENGINE = InnoDB, 
 PARTITION partMAXVAL VALUES LESS THAN (MAXVALUE) ENGINE = InnoDB) */

완전히 파티션이 재구성되어 기존에 있던 파티션은 빠지고 새로운 파티션이 생성되었습니다. 이런식으로 파티션 자체를 완전히 재구성하는 것이라 테이블 크기가 클 경우 생각보다 오래 시간이 소요됩니다.

반드시 파티션을 재구성이 필요한 경우에만 사용하시길 권해 드립니다.