파티셔닝 전략에 대한 선정 기준

 

추후에 MySQL에서 파티셔닝을 사용하게 될때 참고하면 좋을 듯하여, 작년에 면접준비를 위해 노션에 정리했던 내용 일부 가져왔습니다. 

---

 

📌 레인지 칼럼 파티셔닝 전략을 사용한 이유

 

큰 틀로 보면 파티션의 종류로 총 4가지가 있지만, 이력 데이터를 범위 기반으로 분리하기 위해 레인지 파티션을 사용했습니다.

• 레인지 파티션
• 리스트 파티션
• 해시 파티션
• 키 파티션

특히, 이체 내역은 기간 범위에 따라 조회하는 경우가 많기 때문에 파티션 프루닝 을 할 수 있도록 날짜와 시간 에 맞게 논리적으로 테이블을 분할할 수 있도록 설정했습니다.

 

 

그리고 레인지 파티션 중에서도 Range Column Partioning을 사용한 이유는 요구사항에 따라 연도와 월별 기준으로 데이터를 추출해야 하기 때문입니다.

• Range 의 경우 파티션 키로 정수형 타입만 가능하고, Range Column의 경우 정수형뿐만 아니라 문자열, 날짜 타입도 가능합니다.
  • 저는 생성시간의 데이터 타입을 DATETIME으로 설정했기 때문에, Range로 하면 생성시간을 key로 사용할 수 없었습니다.
  • TimeStamp로 했을 경우 unix_timestamp()함수를 활용하여 정수형으로 파티션 키를 설정할 수 있지만,
  • DATETIME은 이러한 함수를 지원하지 않습니다.
• 물론 YEAR()나 Month()함수 사용해서 더하기 연산으로 파티션 표현식을 만들 수도 있지만, 연산작업에서 발생하는 오버헤드와 가독성 측면에서 Range Column이 더 낫다고 판단했습니다.

 

📌 파티션 프루닝 이란?

파티션 프루닝은 여러 파티션 중에서 필요한 파티션만 읽어 최적화를 이루는 기법입니다. 예를 들어, 3개의 파티션 테이블 중 2개만 읽으면 되는 경우, 불필요한 파티션에는 접근하지 않아서 쿼리 성능을 향상시킬 수 있습니다.

어떤 파티션 테이블에 접근했는지는 실행 계획의 partitions 칼럼을 통해 확인할 수 있습니다.

 

📌 파티션 키로 선정할 칼럼의 기준 + 성능?

• Where 절의 조건으로 검색해야 할 파티션을 선택할 수 있는가?
• where절의 조건이 인덱스를 효율적으로 사용할 수 있는가(인덱스 레인지 스캔)

📌 파티션의 장단점

장점

• 쿼리 실행 시 필요한 파티션만 조회(쓰기, 읽기 모두)하여 불필요한 데이터 접근을 최소화할 수 있고,
• 각 파티션을 읽어오는 과정을 병렬로 처리할 수 있어서 대규모 데이터베이스에서 처리속도가 올라갑니다
• 오래된 데이터를 아카이빙하거나 삭제할때, 가장 오래된 파티션 순서로 삭제가 가능하기 때문에 관리가 용이합니다
• 파티션 단위로 분리되어 있어서, 불필요한 데이터를 백업하거나 삭제할때 시간과 자원을 절약할 수 있습니다.

단점

• 파티셔닝 키가 적절하지 않으면 데이터가 특정 파티션에 몰릴 수 있어 성능 저하가 발생할 수 있습니다.

📌 파티션 종류로 레인지, 리스트, 해시, 키가 있다고 하셨는데 적용 기준?

레인지

• 날짜 기반으로 데이터가 누적되고, 연도나 월 또는 일 단위로 분석하고 삭제할때
• 범위 기반으로 데이터를 여러 파티션에 균등하게 나눌 수 있을때
• 파티션 키 위주로 검색이 자주 실행될때

예시: 연도별로 트랜잭션 데이터를 파티션

CREATE TABLE transactions (
    id INT,
    transaction_date DATE,
    amount DECIMAL(10, 2),
    PRIMARY KEY (id, transaction_date)
)
PARTITION BY RANGE (YEAR(transaction_date)) (
    PARTITION p2022 VALUES LESS THAN (2023),
    PARTITION p2023 VALUES LESS THAN (2024),
    PARTITION p2024 VALUES LESS THAN (2025)
);

리스트

예시: 국가별로 사용자 데이터를 파티션

CREATE TABLE users (
    id INT,
    name VARCHAR(50),
    country VARCHAR(50),
    PRIMARY KEY (id, country)
)
PARTITION BY LIST (country) (
    PARTITION usa VALUES IN ('USA'),
    PARTITION canada VALUES IN ('Canada'),
    PARTITION uk VALUES IN ('UK')
);

해시

예시: 고객 ID를 해시하여 고객 데이터를 파티션

CREATE TABLE customers (
    id INT,
    name VARCHAR(50),
    email VARCHAR(100),
    PRIMARY KEY (id)
)
PARTITION BY HASH(id) PARTITIONS 4;

키

예시: 복합 키를 사용하여 주문 데이터를 파티션

CREATE TABLE orders (
    order_id INT,
    customer_id INT,
    order_date DATE,
    PRIMARY KEY (order_id, customer_id)
)
PARTITION BY KEY() PARTITIONS 4;

URL 저장 시 도메인 기준으로 파티션 (List Partitioning)

리스트 파티션은 도메인 기준으로 명확하게 데이터를 분리할 수 있어 도메인별로 URL 데이터를 효과적으로 관리할 수 있습니다

 

예시: 도메인별로 URL 데이터를 파티션

CREATE TABLE urls (
    id INT,
    url VARCHAR(255),
    domain VARCHAR(100),
    PRIMARY KEY (id, domain)
)
PARTITION BY LIST (domain) (
    PARTITION google VALUES IN ('google.com', 'google.co.uk', 'google.co.in'),
    PARTITION yahoo VALUES IN ('yahoo.com', 'yahoo.co.jp', 'yahoo.co.in'),
    PARTITION bing VALUES IN ('bing.com', 'bing.co.uk', 'bing.co.in')
);