MySQL 테이블 partition 설정 하기

1. 지원 확인

mysql 에 로그인하여 아래 명령으로 지원 여부를 확인 합니다.

mysql> show variables like '%partition%;    // have_partitioning 항목 확인

MySQL 5.6.29 버전부터는 아래 명령으로 확인이 가능 합니다.

mysql> show plugins;  // partition 항목 확인

2. 사용 예제

파티셔닝은 테이블의 데이터가 많거나 용량 사이즈가 커서 데이터 입출력이 느린 경우 사용합니다.

여기에서는 처리를 빠르게 하기 위해 데이터를 연도를 기준으로 나누는 예제를 준비 하였습니다.

1) 기존 일반 테이블을 파티셔닝 테이블로 변경

(일반 테이블 생성)

mysql> create table test (id int(10) AUTO_INCREMENT PRIMARY KEY, Name varchar(20), Insert_time DATETIME);

(파티셔닝 적용)

mysql> alter table test PARTITION BY RANGE (TO_DAYS(Insert_time))

(

PARTITION p2017 VALUES LESS THAN (TO_DAYS('2018-01-01')),

PARTITION p2018 VALUES LESS THAN (TO_DAYS('2019-01-01')),

PARTITION p2019 VALUES LESS THAN (TO_DAYS('2020-01-01')),

PARTITION p2020 VALUES LESS THAN (TO_DAYS('2021-01-01')),

partition eternal values less than MAXVALUE

);

* 위 네 개의 조건에 해당되지 않는 데이터는 (2021년 이후 데이터) eternal 이라는 파티션에 저장 하도록 했습니다.

   (생략 가능)

* 파티션 이름은 반드시 영문으로 시작해야 합니다.

* LESS 는 미만을 뜻합니다.

* 추후에 2021년도 파티션 생성을 원할 경우 MAXVALUE 삭제 후 2021 년, MAXVALUE 를 차례로 다시 생성하는 방법으로 추가가 가능합니다.

mysql> alter table test drop partition eternal;

mysql> alter table test add partition (PARTITION p2021 VALUES LESS THAN (TO_DAYS('2022-01-01'))); 

mysql> alter table test add partition (PARTITION eternal VALUES LESS THAN MAXVALUE);

* 파티션 설정 쿼리 실행시 PRIMARY KEY 관련 에러가 출력 될 수 있습니다.

ERROR 1503 (HY000) at line 25: A PRIMARY KEY must include all columns in the table's partitioning function

이경우 show create table {테이블 이름} 을 실행하여 출력 내용을 참고 합니다.

PRIMARY KEY (`id`) 이렇게 있으면 안되고

KEY `id` (`id`) 이렇게 되어 있어야 됩니다.

2) 처음부터 파티셔닝 테이블 생성

mysql> CREATE TABLE test (

  id int(10) AUTO_INCREMENT,

  Name varchar(20),

  Insert_time DATETIME,

  KEY id (id)

) ENGINE=InnoDB CHARSET=utf8

PARTITION BY RANGE(TO_DAYS(Insert_time)) (

  PARTITION p2017 VALUES LESS THAN (TO_DAYS('2018-01-01')),

  PARTITION p2018 VALUES LESS THAN (TO_DAYS('2019-01-01')),

  PARTITION p2019 VALUES LESS THAN (TO_DAYS('2020-01-01')),

  PARTITION p2020 VALUES LESS THAN (TO_DAYS('2021-01-01')),

  partition eternal values less than MAXVALUE

);

3) 파티션 추가 & 삭제

위에서 파티션 추가 예시처럼 사용하시면 됩니다.

mysql> alter table test add partition (PARTITION p2021 VALUES LESS THAN (TO_DAYS('2022-01-01'))); 

mysql> alter table test drop partition p2021;

4) 파티션 없는 일반 테이블로 변경 (데이터 합치기)

mysql> alter table test remove partitioning;