MySQL 파티션 테이블

MySQL 파티션 테이블에 대한 부분을 설명합니다. 기존 데이터가 있는 테이블에 Range 파티션을 만들어서 데이터를 분리하고, 신규 데이터에 대해 파티션이 적용되게 하며, 필요에 따라 파티션을 추가하거나, MaxValue로 구성된 파티션을 분리하거나 두 개 이상의 파티션을 병합하는 방법을 설명합니다.

 

테이블 복사하기

파티션을 운영에 적용하기 전 해당 테이블을 복사해서 테스트한 후 적용하는 것을 권장합니다.

테이블을 복사하는 방법으로 CTAS를 많이 이용하지만 제약조건이 복제되지 않아 해당 내용을 아래 설명합니다.

일반적인 CTAS를 이용해서 테이블을 복사하면 제약조건이 포함되지 않습니다.

create table b as select * from a;

 

별도로 PK, 인덱스와 같은 제약조건을 생성해 줘야 함

like 명령어로 제약조건을 포함해서 테이블 구조만 생성

create table b like a;

insert into b select * from a;

 

ALTER 파티션

HASH함수를 사용해서 8개의 파티션으로 나누기, 테이블 생성

CREATE TABLE t1 (
    id INT,
    year_col INT
);

 

파티션 나누기

ALTER TABLE t1
    PARTITION BY HASH(id)
    PARTITIONS 8;

 

테이블에서 보이는 것은 동일함

MySQL Partitioning

 

Table Inspector에서 Partitions를 보면 파티션정보가 보임

MySQL Partitioning

 

명령어로 조회

select * from information_schema.partitions where table_name = 't1';

MySQL Partitioning

 

파티션별 데이터 건수 확인

SELECT TABLE_NAME, TABLE_ROWS, PARTITION_NAME
FROM INFORMATION_SCHEMA.PARTITIONS
WHERE TABLE_SCHEMA = 'DB명'
  AND TABLE_NAME = 't1';

MySQL Partitioning

 

삭제 후 처음과 같이 t1테이블 생성, 이번에는 year_col에 파티션 생성

CREATE TABLE t1 (
    id INT,
    year_col INT
);

-- 연도별 파티션 생성
ALTER TABLE t1
    PARTITION BY RANGE(year_col)(
    PARTITION p0 VALUES LESS THAN (2022),             -- 2022보다 작은값
    PARTITION p1 VALUES LESS THAN (2023),             -- 2023보다 작은값
    PARTITION p2 VALUES LESS THAN (2024)              -- 2024보다 작은값
);

MySQL Partitioning

 

데이터 입력 후 확인

insert into t1 values (1, 2023);
insert into t1 values (2, 2023);
insert into t1 values (3, 2024);
-- 2024 데이터 입력시 오류가 발생됨

MySQL Partitioning

 

실제 운영 데이터 작업 예시, 시간 측정

3.6GB, 2,335,186건 테이블

create table b like a;
-- 바로 완료
insert into b select * from a;
-- affected Records: 2471657  Duplicates: 0  Warnings: 0	442.907 sec

 

바로 파티션 테이블 생성 시 아래와 같이 오류 발생됨

Error Code: 1503. A PRIMARY KEY must include all columns in the table’s partitioning function

-- PK에 컬럼을 추가하기 위해 PK를 삭제 후 재 생성
alter table b drop primary key, add primary key(id, reg_Dt);
-- 133.157 sec

 

파티션추가

ALTER TABLcE b
PARTITION BY RANGE(YEAR(reg_Dt)) (
    PARTITION p0 VALUES LESS THAN (2003),
    PARTITION p1 VALUES LESS THAN (2004),
    PARTITION p2 VALUES LESS THAN (2005),
    PARTITION p3 VALUES LESS THAN (2006),
    PARTITION p4 VALUES LESS THAN (2007),
    PARTITION p5 VALUES LESS THAN (2008),
    PARTITION p6 VALUES LESS THAN (2009),
    PARTITION p7 VALUES LESS THAN (2010),
    PARTITION p8 VALUES LESS THAN (2011),
    PARTITION p9 VALUES LESS THAN (2012),
    PARTITION p10 VALUES LESS THAN (2013),
    PARTITION p11 VALUES LESS THAN (2014),
    PARTITION p12 VALUES LESS THAN (2015),
    PARTITION p13 VALUES LESS THAN (2016),
    PARTITION p14 VALUES LESS THAN (2017),
    PARTITION p15 VALUES LESS THAN (2018),
    PARTITION p16 VALUES LESS THAN (2019),
    PARTITION p17 VALUES LESS THAN (2020),
    PARTITION p18 VALUES LESS THAN (2021),
    PARTITION p19 VALUES LESS THAN (2022),
    PARTITION p20 VALUES LESS THAN (2023),
    PARTITION p21 VALUES LESS THAN (2024),
    PARTITION p22 VALUES LESS THAN MAXVALUE
);
-- 408.765 sec

 

피티션별 데이터 건수

MySQL Partitioning

 

추가 테스트

-- 테이블을 만들고
CREATE TABLE t1 (
    id INT,
    year_col INT
);

-- 파티션을 2024보다 작은거까지 만들고
ALTER TABLE t1
    PARTITION BY RANGE(year_col)(
    PARTITION p0 VALUES LESS THAN (2022),
    PARTITION p1 VALUES LESS THAN (2023),
    PARTITION p2 VALUES LESS THAN (2024),
    PARTITION p3 VALUES LESS THAN maxvalue
);

-- 2024나 2025를 입력하면 해당 데이터는 p3에 입력됨을 확인
insert into t1 values (1, 2023);
insert into t1 values (2, 2024);
insert into t1 values (2, 2025);


SELECT TABLE_NAME, TABLE_ROWS, PARTITION_NAME
FROM INFORMATION_SCHEMA.PARTITIONS
WHERE TABLE_NAME = 't1';

MySQL Partitioning

 

P3에 있는 데이터 조회

select * from t1 partition(p3);

MySQL Partitioning

 

p3 파티션을 조정하기 위해 삭제하면

ALTER TABLE t1 DROP PARTITION p3;

MySQL Partitioning

 

파티션이 삭제되며 P2에도 데이터가 없는 것처럼 보임

파티션 추가

ALTER TABLE t1 DROP PARTITION p2;

ALTER TABLE t1 ADD PARTITION (PARTITION p2 VALUES LESS THAN (2024));

 

MAXVALUE can only be used in last partition definition. 오류 발생

-- 파티션 분리
ALTER TABLE t1 REORGANIZE PARTITION P3 INTO (
PARTITION p3 VALUES LESS THAN(2025),
PARTITION p4 VALUES LESS THAN MAXVALUE
);
-- 분리후 조회시 안보임
SELECT TABLE_NAME, TABLE_ROWS, PARTITION_NAME
FROM INFORMATION_SCHEMA.PARTITIONS
WHERE TABLE_NAME = 't1';

-- 아래와 같이 조회하면 보임
select * from t1 partition(p3);

-- 파티션 합치기
ALTER TABLE t1 REORGANIZE PARTITION P1, P2 INTO (
PARTITION p2 VALUES LESS THAN (2024)
);

MySQL Partitioning