[MySQL] Partition

개념

파티션이란?

• 테이블을 더 작은 단위로 나누어 보다 효율적으로 데이터를 관리하고 쿼리 성능을 향상시킬 수 있는 방법을 말한다.

파티션의 종류

• RANGE 파티셔닝 : 연속적인 값 범위에 따라 데이터를 분리. ex) 날짜 범위별
• LIST 파티셔닝 : 특정 값 목록에 따라 데이터를 분리. ex) 지역별
• HASH 파티셔닝 : 해시 함수를 사용하여 데이터를 고르게 분산시켜 분리. 균등한 데이터 분포를 보장함

기본 예제

파티션 테이블 생성

CREATE TABLE Message
(
    id        BIGINT UNSIGNED AUTO_INCREMENT,
    title     VARCHAR(100),
    contents  VARCHAR(500),
    createdAt DATETIME(3) NOT NULL,
    PRIMARY KEY (id, createdAt) -- 주의: 파티션 키는 기본키에 포함되어야함
)
PARTITION BY RANGE (TO_DAYS(createdAt)) (
    PARTITION p_202408 VALUES LESS THAN (TO_DAYS('2024-09-01')),
    PARTITION p_202409 VALUES LESS THAN (TO_DAYS('2024-10-01'))
);

테이블 파티션 조회

SELECT * FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_NAME = 'Message';

파티션 추가

ALTER TABLE Message ADD PARTITION (PARTITION p_202410 VALUES LESS THAN (TO_DAYS('2024-11-01')));

파티션 삭제

ALTER TABLE Message DROP PARTITION p_202408;

어떤 파티션을 조회했는지 확인하기

-- 아래 쿼리 수행시 partitions 컬럼에 p_202408,p_202409로 확인
EXPLAIN SELECT * FROM Message WHERE createdAt BETWEEN '2024-08-01' and '2024-09-25';

snowflake을 활용한 파티션 예제

설명

• 위 기본예제에서 날짜기준으로 RANGE 파티셔닝을 하기위해 id 뿐만아니라 createdAt도 기본키에 포함되어야했다.
• id만 활용하여 날짜기준 파티셔닝을 수행하고싶다면 id에 날짜 정보가 포함되어있으면 된다.
• snowflake는 id를 정수로 사용하여 성능에 이점이 있고, 날짜 정보가 포함되어있어 id만 활용하여 날짜기준 RANGE 파티셔닝이 가능하다.

파티셔닝을 위한 snowflake 범위값 확인

-- 2024-01-01 00:00:00는 snowflake의 기준 timestamp
SELECT (UNIX_TIMESTAMP('2024-09-01 00:00:00') - UNIX_TIMESTAMP('2024-01-01 00:00:00')) * 1000 << 22; -- 88422639206400000
SELECT (UNIX_TIMESTAMP('2024-10-01 00:00:00') - UNIX_TIMESTAMP('2024-01-01 00:00:00')) * 1000 << 22; -- 99294275174400000

테이블 생성

• PARTITION 지정시 shift 연산을 사용할 수 없어서 미리 범위값 확인 후 하드코딩으로 지정해야함

CREATE TABLE Message
(
    id        BIGINT UNSIGNED,
    title     VARCHAR(100),
    contents  VARCHAR(500),
    createdAt DATETIME(3) NOT NULL,
    PRIMARY KEY (id)
)
PARTITION BY RANGE (id) (
    PARTITION p_202408 VALUES LESS THAN (88422639206400000),
    PARTITION p_202409 VALUES LESS THAN (99294275174400000)
);

데이터 추가

INSERT INTO Message (id, title, contents, createdAt) VALUES ((UNIX_TIMESTAMP('2024-08-01 23:59:00') - UNIX_TIMESTAMP('2024-01-01 00:00:00')) * 1000 << 22, 'TEST-1', 'TEST', STR_TO_DATE('2024-08-01 23:59', '%Y-%m-%d %H:%i'));
INSERT INTO Message (id, title, contents, createdAt) VALUES ((UNIX_TIMESTAMP('2024-09-01 23:59:00') - UNIX_TIMESTAMP('2024-01-01 00:00:00')) * 1000 << 22, 'TEST-2', 'TEST', STR_TO_DATE('2024-09-01 23:59', '%Y-%m-%d %H:%i'));

데이터 조회

SELECT * FROM Message WHERE id < (UNIX_TIMESTAMP('2024-08-05 23:59:00') - UNIX_TIMESTAMP('2024-01-01 00:00:00')) * 1000 << 22