[MySQL] 파티셔닝(Partitioning) 적용 (1) 기존의 테이블을 변경하기 - 연도,월,일별 파티셔닝

안녕하세요 안젤라입니다😇

오늘은 MySQL 파티셔닝(Partitioning)에 대해서 포스팅을 준비했습니다.

파티셔닝의 니즈

우선 저의 경우, 파티셔닝에 대한 니즈는 다음과 같았습니다.

DB에 로그성 데이터가 쌓이는데 다른 DB로 교체할 수는 없고 file로 떨구고 싶지도 않다.
하지만 이를 주기적으로 삭제하는 시나리오로 설계를 해보자
feat. 삭제할 때 overhead가 없었음 좋겠다

라는 니즈였습니다.

이런경우 참고해주시면 좋은 포스팅입니다

파티셔닝의 정의 

파티셔닝의 정의는 다음과 같습니다.

MySQL 파티셔닝은 대용량 데이터를 처리할 때 성능을 향상시키기 위해 데이터베이스 테이블을 분할하는 기술입니다. 파티셔닝을 사용하면 테이블을 논리적으로 여러 개의 작은 테이블로 분할할 수 있으며, 이를 통해 쿼리 처리 시간을 줄이고 성능을 개선할 수 있습니다.

파티셔닝은 일반적으로 테이블의 행(row) 수에 따라 수평적으로 분할합니다. 이를 통해 하나의 테이블에서 데이터를 검색하거나 조작할 때 파티션 단위로 처리를 분산시킬 수 있으며, 이를 통해 데이터베이스의 부하를 줄이고 더 빠른 처리가 가능합니다.

파티셔닝 방법

파티셔닝을 하는 방법은 3가지가 있는데 이 중 우리는 날짜 순으로 정렬하는 Range 파티셔닝을 진행해 볼거예요.
기존의 테이블에 created_date를 key로 추가해주시고 진행해주셔야 합니다. 왜냐면 우리는 날짜를 Range의 기준으로 삼을거니깐요.

연도별 Range 파티셔닝

ALTER TABLE tb_log
    PARTITION BY RANGE (YEAR(created_date))
        SUBPARTITION BY HASH (MONTH(created_date))
        SUBPARTITIONS 12 (
        PARTITION p2021 VALUES LESS THAN (2022) ENGINE = InnoDB,
        PARTITION p2022 VALUES LESS THAN (2023) ENGINE = InnoDB,
        PARTITION p2023 VALUES LESS THAN (2024) ENGINE = InnoDB,
        PARTITION pmax VALUES LESS THAN MAXVALUE
    );

월별 Range 파티셔닝

ALTER TABLE tb_log
    PARTITION BY RANGE (TO_DAYS(created_date))(
        PARTITION p202204 VALUES LESS THAN (TO_DAYS('2022-05-01')),
        PARTITION p202205 VALUES LESS THAN (TO_DAYS('2022-06-01')),
        PARTITION p202206 VALUES LESS THAN (TO_DAYS('2022-07-01')),
        PARTITION p202207 VALUES LESS THAN (TO_DAYS('2022-08-01')),
        PARTITION p202208 VALUES LESS THAN (TO_DAYS('2022-09-01')),
        PARTITION p202209 VALUES LESS THAN (TO_DAYS('2022-10-01')),
        PARTITION p202210 VALUES LESS THAN (TO_DAYS('2022-11-01')),
        PARTITION p202211 VALUES LESS THAN (TO_DAYS('2022-12-01')),
        PARTITION p202212 VALUES LESS THAN (TO_DAYS('2023-01-01')),
        PARTITION p202301 VALUES LESS THAN (TO_DAYS('2023-02-01')),
        PARTITION p202302 VALUES LESS THAN (TO_DAYS('2023-03-01')),
        PARTITION pmax VALUES LESS THAN (MAXVALUE)
    );

일별 Range 파티셔닝

ALTER TABLE tb_log
	PARTITION BY RANGE (TO_DAYS(created_date))(
        PARTITION p20221202 VALUES LESS THAN (TO_DAYS('2022-12-03')),
        PARTITION p20221203 VALUES LESS THAN (TO_DAYS('2022-12-04')),
        PARTITION p20221204 VALUES LESS THAN (TO_DAYS('2022-12-05')),
        PARTITION p20221205 VALUES LESS THAN (TO_DAYS('2022-12-06')),
        PARTITION p20221206 VALUES LESS THAN (TO_DAYS('2022-12-07')),
        PARTITION p20221207 VALUES LESS THAN (TO_DAYS('2022-12-08')),
        PARTITION p20221208 VALUES LESS THAN (TO_DAYS('2022-12-09')),
        PARTITION p20221209 VALUES LESS THAN (TO_DAYS('2022-12-10')),
        PARTITION p20221210 VALUES LESS THAN (TO_DAYS('2022-12-11')),
        PARTITION p20221211 VALUES LESS THAN (TO_DAYS('2022-12-12')),
        PARTITION p20221212 VALUES LESS THAN (TO_DAYS('2022-12-13')),
        PARTITION p20221213 VALUES LESS THAN (TO_DAYS('2022-12-14')),
        PARTITION p20221214 VALUES LESS THAN (TO_DAYS('2022-12-15')),
        PARTITION p20221215 VALUES LESS THAN (TO_DAYS('2022-12-16')),
        PARTITION p20221216 VALUES LESS THAN (TO_DAYS('2022-12-17')),
        PARTITION p20221217 VALUES LESS THAN (TO_DAYS('2022-12-18')),
        PARTITION p20221218 VALUES LESS THAN (TO_DAYS('2022-12-19')),
        PARTITION p20221219 VALUES LESS THAN (TO_DAYS('2022-12-20')),
        PARTITION p20221220 VALUES LESS THAN (TO_DAYS('2022-12-21')),
        PARTITION pmax VALUES LESS THAN (MAXVALUE)
    );

이렇게 파티션을 적용해놓고 실제 파티셔닝된 데이터를 보기 위해서는 Range로 잡아둔 컬럼에 데이터가 있어야 합니다.
다음의 명령어를 통해 확인할 수 있습니다.

SELECT TABLE_NAME,
       PARTITION_NAME,
       PARTITION_ORDINAL_POSITION POSITION,
       PARTITION_DESCRIPTION      PART_DESC,
       TABLE_ROWS,
       AVG_ROW_LENGTH
FROM INFORMATION_SCHEMA.PARTITIONS
WHERE 1 = 1
  AND TABLE_NAME = 'tb_log';

만약 기존의 테이블을 alter 하는게 아니고 새롭게 테이블을 생성할 때는 키값이나 좀 더 심화적으로 알아봐야 할 것들이 많습니다.
그리고 이렇게 설정은 되었는데 제대로 검색할때 동작하는게 맞나? 라는 의문이 들 수 있습니다.

그 두가지는 다음 포스트에서 진행해 볼게요.