[MySQL + MariaDB] 날짜를 기준으로 파티셔닝

개요

분 단위로 쌓이는 통계 정보를 저장하는 테이블을 생성했고 데이터가 엄청 많이 쌓일 것이기 때문에 월별로 테이블 파티셔닝을 진행했습니다.

기존에 작성한 PostgreSQL 테이블 파티셔닝과 살짝 다른 점이 있어 별도로 정리해봤습니다.

https://jaimemin.tistory.com/1913

[PostgreSQL] 월별로 DB 파티셔닝 예제

 

1. 파티셔닝 지원 확인

우선, 현재 MySQL 버전이 파티셔닝을 지원하는지 확인을 해야 합니다.

제가 알기로는 MySQL 5.7 버전부터 파티셔닝을 지원하며 확인 방법은 아래의 쿼리를 수행해보면 됩니다.

SHOW VARIABLES LIKE '%parition%';

 

만약 MySQL 버전이 5.7 이상이고 위 쿼리를 수행했을 때 아무 변수도 나타나지 않는다면 아래 쿼리를 수행해보시면 확인하실 수 있습니다.

SHOW PLUGINS;

 

2. 테이블 파티셔닝 진행 방법

 

2.1 새로운 테이블을 생성함과 동시에 파티셔닝 진행

 

CREATE TABLE SAMPLE_PARTITIONING_TABLE
(
	...,
        DATE_TIME         DATETIME DEFAULT CURRENT_TIMESTAMP,
	PRIMARY KEY (..., DATE_TIME)
) PARTITION BY RANGE COLUMNS(DATE_TIME)
(
	PARTITION P202207 VALUES LESS THAN ('2022-08-01 00:00:00.000'),
	PARTITION P202208 VALUES LESS THAN ('2022-09-01 00:00:00.000'),
	PARTITION P202209 VALUES LESS THAN ('2022-10-01 00:00:00.000'),
        PARTITION P202210 VALUES LESS THAN ('2022-11-01 00:00:00.000'),
        ...,
	PARTITION PMAXVALUES VALUES LESS THAN MAXVALUE
);

 

* 주의:  파티셔닝 기준이 되는 칼럼은 반드시 Primary Key에 포함되어야 합니다.

 

2.2 기존에 생성된 테이블에 대해 새로 파티셔닝 진행

 

ALTER TABLE SAMPLE_PARTITIONING_TABLE PARTITION BY RANGE(DATE_TIME)
(
	PARTITION P202207 VALUES LESS THAN ('2022-08-01 00:00:00.000'),
	PARTITION P202208 VALUES LESS THAN ('2022-09-01 00:00:00.000'),
	PARTITION P202209 VALUES LESS THAN ('2022-10-01 00:00:00.000'),
        PARTITION P202210 VALUES LESS THAN ('2022-11-01 00:00:00.000'),
        ...,
	PARTITION PMAXVALUES VALUES LESS THAN MAXVALUE
);

 

DDL 쿼리는 위와 같이 작성하시면 되고 한 가지 주의할 점은 테이블을 파티셔닝 할 경우 Primary Key를 제외한 Unique Key는 존재할 수 없습니다.

Unique Key를 부여할 경우 결국 해당 Key가 기존 테이블에 존재하는지 판단하기 위해 Table Full Scan을 진행하게 되고 이는 파티셔닝을 하는 의미를 무색하게 만듭니다.

따라서, Unique Key가 필요한 경우 Primary Key를 Compound Key 즉, 기본 키를 복합 키로 생성하셔야 합니다.

또한, PostgreSQL과 달리 MySQL은 각 파티셔닝 테이블에 대해 인덱스를 부여하는 방법이 없는 것으로 보입니다.

따라서, 인덱싱이 필요한 칼럼 또한 기본 키에 추가해주는 것을 추천드립니다. (이 부분은 정확하지 않을 수 있으므로 오류가 있을 경우 언제든지 코멘트 남겨주시면 감사하겠습니다!)

 

3. 파티션 테이블 제거하는 쿼리

ALTER TABLE SAMPLE_PARTITIONING_TABLE DROP PARTITION P202207;

* 위 쿼리를 수행할 경우 해당 파티션에 존재하는 데이터 삭제됨

 

4. 테이블 파티셔닝 해제하는 쿼리

ALTER TABLE SAMPLE_PARTITIONING_TABLE REMOVE PARTITIONING;

 

5. 파티션 테이블 정보 확인하는 쿼리

SELECT TABLE_SCHEMA, TABLE_NAME, PARTITION_NAME, PARTITION_ORDINAL_POSITION, TABLE_ROWS
FROM information_schema.PARTITIONS
WHERE TABLE_NAME = 'SAMPLE_PARTITIONING_TABLE';

 

6. 복합 파티셔닝

galgulee님 블로그 참고하여 작성하는 내용입니다.

월별 파티셔닝을 진행하고 싶은데 테이블에 연도와 월을 각기 다른 칼럼에 저장하고 있을 경우 복합 파티셔닝 혹은 서브 파티셔닝을 진행해야 합니다.

해당 쿼리는 아래와 같습니다.

ALTER TABLE SAMPLE_PARTITIONING_TABLE PARTITION BY RANGE (YEAR(`year`))
SUBPARTITION BY HASH (MONTH(`month`))
SUBPARTITIONS 12 (
    PARTITION p2013 VALUES LESS THAN (2013) ENGINE = InnoDB,
    PARTITION p2014 VALUES LESS THAN (2014) ENGINE = InnoDB,
    PARTITION p2015 VALUES LESS THAN (2015) ENGINE = InnoDB,
    PARTITION p2016 VALUES LESS THAN (2016) ENGINE = InnoDB,
    PARTITION p2017 VALUES LESS THAN (2017) ENGINE = InnoDB,
    PARTITION p2018 VALUES LESS THAN (2018) ENGINE = InnoDB,
    PARTITION p2019 VALUES LESS THAN (2019) ENGINE = InnoDB,
    PARTITION p2020 VALUES LESS THAN (2020) ENGINE = InnoDB,
    PARTITION pmax VALUES LESS THAN MAXVALUE
);

 

출처

https://www.galgulee.com/mysql-mariadb-%ED%8C%8C%ED%8B%B0%EC%85%94%EB%8B%9D%EC%9D%98-%EC%84%A4%EC%A0%95%EC%B6%94%EA%B0%80%EC%82%AD%EC%A0%9C%EC%9E%AC%EA%B5%AC%EC%84%B1/

MySQL & MariaDB 파티셔닝의 설정,추가,삭제,재구성 - Galgulee - 갈구리