개요
2021.09.18 - [Partition] 개념 및 장단점에서 파티션의 개념과 특징, 파티셔닝의 종류와 분할 기준 등을 정리해보았다.
이번 글에서는 DB에서 파티션 테이블을 생성/수정/삭제하는 SQL 쿼리를 정리해둔다.
파티션 지원 여부 확인
일단 MySQL 서버에서 파티션을 사용할 수 있는지 확인해야 한다.
SHOW VARIABLES LIKE '%partition%';
have_partitioning 항목이 YES이므로 파티션을 지원한다는 것을 알 수 있다.
MySQL 5.6.29부터는 아래 명령으로 확인한다.
SHOW PLUGINS;
partition 항목이 ACTIVE이므로 파티션을 지원하는 버전임을 알 수 있다.
파티션 테이블 생성
일반 테이블 생성 후 파티셔닝
예시는 날짜를 기준으로 수평 파티셔닝 한 것이다. 파티션 기준이 되는 파티션 키는 Primary Key에 포함되어 있어야 한다.
CREATE TABLE partTest (
ID INT NOT NULL AUTO_INCREMENT,
regtime DATE NOT NULL DEFAULT '2020-01-01',
PRIMARY KEY(ID, regtime)
);
ALTER TABLE partTest PARTITION BY RANGE(TO_DAYS(regtime))(
PARTITION p20201230 VALUES LESS THAN (TO_DAYS('2020-12-31')),
PARTITION p20201231 VALUES LESS THAN (TO_DAYS('2021-01-01')),
PARTITION p20210101 VALUES LESS THAN (TO_DAYS('2021-01-02')),
PARTITION p20210102 VALUES LESS THAN (TO_DAYS('2021-01-03')),
PARTITION p20210103 VALUES LESS THAN (TO_DAYS('2021-01-04'))
);
파티션 테이블 생성
파티셔닝된 테이블을 생성한다.
CREATE TABLE partTest (
ID INT NOT NULL auto_increment,
regtime DATE NOT NULL DEFAULT '2020-01-01',
PRIMARY KEY(ID, regtime)
) PARTITION BY RANGE(TO_DAYS(regtime))(
PARTITION p20201230 VALUES LESS THAN (TO_DAYS('2020-12-31')),
PARTITION p20201231 VALUES LESS THAN (TO_DAYS('2021-01-01')),
PARTITION p20210101 VALUES LESS THAN (TO_DAYS('2021-01-02')),
PARTITION p20210102 VALUES LESS THAN (TO_DAYS('2021-01-03')),
PARTITION p20210103 VALUES LESS THAN (TO_DAYS('2021-01-04'))
);
파티션 테이블 확인
SELECT TABLE_SCHEMA, TABLE_NAME, PARTITION_NAME, PARTITION_ORDINAL_POSITION, TABLE_ROWS
FROM information_schema.PARTITIONS
WHERE TABLE_NAME = 'partTest';
테이블 내 파티션 추가
alter table … add partition 구문을 이용한다.
ALTER TABLE partTest ADD PARTITION(
PARTITION p20210107 VALUES LESS THAN (TO_DAYS('2021-01-08')),
PARTITION p20210108 VALUES LESS THAN (TO_DAYS('2021-01-09'))
);
테이블 내 파티션/데이터 삭제
alter table … drop partition 구문을 이용한다. drop 시 파티션 내의 테이터도 함께 삭제된다.
ALTER TABLE partTest DROP PARTITION p20210103;
파티션 해제
테이블의 파티션 설정을 삭제하는 것으로 테이블 내의 데이터는 유지된다.
ALTER TABLE partTest REMOVE PARTITIONING;