MySQL 월별 파티션

DATE, DATETIME, TIMESTAMP 데이터타입의 컬럼으로 파티션을 생성, 추가, 삭제 하는 예제입니다.

 

 

파티션테이블 생성

1) DATE, DATETIME 컬럼으로 파티션

아래는 월별 파티션 예제입니다.

1-1) TO_DAYS 함수 사용

CREATE TABLE sales (
    sale_id INT NOT NULL,
    sale_date DATE NOT NULL,
    customer_id INT NOT NULL,
    amount DECIMAL(10,2),
    PRIMARY KEY (sale_id, sale_date)
)
PARTITION BY RANGE (TO_DAYS(sale_date)) (
    PARTITION p_2024_01 VALUES LESS THAN (TO_DAYS('2024-02-01')),
    PARTITION p_2024_02 VALUES LESS THAN (TO_DAYS('2024-03-01')),
    PARTITION p_2024_03 VALUES LESS THAN (TO_DAYS('2024-04-01')),
    PARTITION p_2024_04 VALUES LESS THAN (TO_DAYS('2024-05-01')),
    PARTITION p_maxvalue VALUES LESS THAN MAXVALUE
);

Range 파티션은 값으로 int 형 데이터가 와야 하기 때문에 TO_DAYS 함수를 사용.

 

설명:

• TO_DAYS(sale_date) 함수는 분할을 위해 날짜를 숫자 값으로 변환
• 각 파티션은 특정 월(예: 2024년 1월, 2024년 2월 등)을 기준으로 정의
• p_maxvalue 파티션은 2024년 4월보다 큰 모든 데이터를 적재

 

✔ TO_DAYS 함수를 사용하는 이유 : Range 파티션은 값으로 int 형 데이터가 와야 하기 때문

 

1-2) 날짜형식의 String으로 사용

DATE 또는 DATETIME 열을 분할 열로 사용하여 RANGE COLUMNS로 테이블을 분할합니다. 

CREATE TABLE sales_1 (
    sale_id INT NOT NULL,
    sale_date DATE NOT NULL,
    customer_id INT NOT NULL,
    amount DECIMAL(10,2),
    PRIMARY KEY (sale_id, sale_date)
)
PARTITION BY RANGE COLUMNS(sale_date) (
    PARTITION p_2024_01 VALUES LESS THAN ('2024-02-01'),
    PARTITION p_2024_02 VALUES LESS THAN ('2024-03-01'),
    PARTITION p_2024_03 VALUES LESS THAN ('2024-04-01'),
    PARTITION p_2024_04 VALUES LESS THAN ('2024-05-01'),
    PARTITION p_maxvalue VALUES LESS THAN MAXVALUE
);

 

2) TIMESTAMP 컬럼으로 파티션

UNIX_TIMESTAMP() 함수를 사용해서 파티셔닝 한다.

MySQL 8.4에서는 UNIX_TIMESTAMP() 함수를 사용하여 TIMESTAMP 열의 값을 기준으로 RANGE로 테이블을 분할할 수 있다.

CREATE TABLE quarterly_report_status (
    report_id INT NOT NULL,
    report_status VARCHAR(20) NOT NULL,
    report_updated TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
)
PARTITION BY RANGE ( UNIX_TIMESTAMP(report_updated) ) (
    PARTITION p5 VALUES LESS THAN ( UNIX_TIMESTAMP('2009-04-01 00:00:00') ),
    PARTITION p6 VALUES LESS THAN ( UNIX_TIMESTAMP('2009-07-01 00:00:00') ),
    PARTITION p7 VALUES LESS THAN ( UNIX_TIMESTAMP('2009-10-01 00:00:00') ),
    PARTITION p8 VALUES LESS THAN ( UNIX_TIMESTAMP('2010-01-01 00:00:00') ),
    PARTITION p9 VALUES LESS THAN (MAXVALUE)
);

 

 

파티션 추가

1) 파티션 테이블이 MAXVALUE로 구성되어 있는 경우에는 REORGANIXE PRTITION 명령어로 기존 MAXVALUE 파티션을 나눈다.

ALTER TABLE sales REORGANIZE PARTITION p_maxvalue INTO(
    PARTITION p_2024_05 VALUES LESS THAN (TO_DAYS('2024-06-01')),
    PARTITION p_maxvalue VALUES LESS THAN MAXVALUE);

 

2) 파티션 테이블이 MAXVALUE로 구성되어 있지 않은 경우에는 ADD PRTITION 명령어로 파티션을 추가한다.

ALTER TABLE sales ADD PARTITION (
    PARTITION p_2024_05 VALUES LESS THAN (TO_DAYS('2024-06-01'))
);

 

파티션 삭제

삭제하고자 하는 파티션을 DROP PARTITION 명령어로 삭제한다. 아래는 p_2024_01 파티션을 삭제하는 예제이다.

ALTER TABLE sales DROP PARTITION p_2024_01;