데이터베이스 테이블 파티셔닝 개념과 사용 예제

테이블 파티셔닝은 대규모 데이터베이스 관리에서 중요한 기술이다. 데이터를 관리하기 용이하게 하고, 성능을 향상시키기 위해 테이블을 논리적으로 분리하는 방법이다.

테이블 파티셔닝에 대해 알아보자

 

 

1. 테이블 파티션 선정

테이블 파티션을 선정하는 과정에서는 어떤 기준으로 테이블을 분할할지 결정한다. 이는 테이블에 저장된 데이터의 특성과 애플리케이션이 데이터에 접근하는 방식을 고려하여 결정해야 하는데 일반적인 파티션 기준은 다음과 같다.

• 범위 파티셔닝(Range Partitioning): 특정 범위의 값을 기준으로 파티션을 나누는 방식이다. 예를 들어, 날짜 범위나 숫자 범위가 이에 해당한다.
• 리스트 파티셔닝(List Partitioning): 명시된 값의 리스트를 기준으로 파티션을 나누는 방식이다. 예를 들어, 지역 코드나 상품 카테고리 등이 될 수 있다.
• 해시 파티셔닝(Hash Partitioning): 해시 함수를 사용하여 파티션 키의 값을 해시하여 파티션을 나누는 방법이다. 이 방법은 데이터를 균등하게 분산시키고 싶을 때 사용한다. 
• 조합 파티셔닝(Composition Partitioning): 두 가지 이상의 파티셔닝 방법을 결합하여 테이블을 분할하는 방식이다. 주로 범위 파티셔닝과 리스트 파티셔닝을 결합하는 방식을 많이 사용한다.

범위와 리스트 파티셔닝은 Select 성능 최적화에 초첨에 맞춘 반면 해시 파티셔닝은 데이터 Insert 성능 최적화에 유리하다.

 

파티션 프루닝

DBMS는 쿼리를 실행할 때 파티션 프루닝이라는 최적화 기법을 사용하여 필요하지 않은 파티션에는 접근하지 않는다. 

쿼리에 사용된 조건과 파티션 키 값의 범위를 비교하여 해당 조건에 부합하지 않는 파티션은 조회 과정에서 제외된다.

 

예를 들어, 날짜 범위를 기준으로 파티셔닝된 테이블에 대해 특정 날짜 범위의 데이터만 요청하는 쿼리가 있다면, 해당 범위 외의 데이터가 저장된 파티션은 검색에서 제외된다.

 

성능 차이의 이유

파티셔닝된 테이블의 경우, DBMS가 불필요한 데이터를 빠르게 제외시킬 수 있어서 데이터 접근이 더 효율적이다.

반면, 파티셔닝되지 않은 테이블에서는 해당 조건과 일치하는 데이터를 찾기 위해 테이블의 모든 행을 검사해야 하므로, 데이터 검색에 더 많은 시간과 자원이 소모된다.

 

 

2. 파티션 옵션

파티션을 생성할 때 고려해야 할 옵션들이 있다. 데이터베이스 관리 시스템(DBMS)에 따라 다를 수 있지만, 일반적인 옵션은 다음과 같다.

• 파티션 키 선택: 어떤 열(또는 열의 조합)을 파티셔닝의 기준으로 사용할지 결정한다. (파티션 키가 인덱스에 포함되지 않으면, 데이터베이스가 모든 파티션을 스캔하므로 비효율적)
• 파티션의 수와 범위: 파티션을 몇 개로 나눌지와 각 파티션의 범위 또는 조건을 설정한다.
• 파티션의 저장 위치: 특정 파티션을 특정 디스크 또는 디렉토리에 저장할지 결정할 수 있다. 이는 I/O 성능 최적화에 도움을 줄 수 있다.

3. 파티셔닝 인덱스

파티셔닝된 테이블에 대한 인덱스 생성은 데이터의 검색 속도를 개선하기 위해 중요하다. 파티셔닝된 테이블에 인덱스를 적용할 때는 주로 로컬 인덱스, 글로벌 인덱스를 고려할 수 있다.

 

로컬 인덱스 (Local Indexes)

• 특징: 각 파티션에 대해 독립적으로 생성되는 인덱스. 로컬 인덱스는 특정 파티션 내의 데이터만을 참조하며, 각 파티션의 인덱스는 서로 분리되어 있다.
• 장점
  1. 파티션 관리가 용이하다. 예를 들어, 파티션을 삭제하거나 교체할 때 해당 파티션의 인덱스만 처리하면 되므로 관리가 간단하다.
  2. 파티션 단위의 데이터 조작 작업(예: 파티션 추가, 삭제)이 인덱스에 미치는 영향이 적어, 데이터베이스 유지 관리가 용이하다.
• 적용 사례: 파티션 별로 자주 접근하는 경우, 특히 파티션 키를 기준으로 자주 쿼리하는 상황에서 유리하다.

글로벌 인덱스 (Global Indexes)

• 특징: 테이블 전체를 대상으로 생성되는 인덱스로, 모든 파티션에 걸쳐 있는 데이터를 참조한다. 글로벌 인덱스는 파티셔닝된 구조를 무시하고 전체 데이터에 대한 하나의 인덱스를 유지한다.
• 장점:
  1. 파티션 키가 아닌 다른 열을 기준으로 자주 쿼리를 실행할 때 유용하다. 이는 파티션 구조와 무관하게 전체 데이터셋에 대해 빠른 검색을 가능하게 한다.
  2. 다양한 쿼리 유형에 대해 유연하게 대응할 수 있다.
• 적용 사례: 전체 데이터셋에 대해 자주 접근하고 쿼리할 때 효과적이다.

---

Range, List, Hash 파티셔닝 사용 예제

-- Range 파티셔닝(날짜) 테이블 생성
CREATE TABLE sales (
    id NUMBER PRIMARY KEY,
    sale_date DATE NOT NULL,
    amount NUMBER
)
PARTITION BY RANGE (sale_date) (
    PARTITION sales_q1 VALUES LESS THAN (TO_DATE('2023-04-01', 'YYYY-MM-DD')),
    PARTITION sales_q2 VALUES LESS THAN (TO_DATE('2023-07-01', 'YYYY-MM-DD')),
    PARTITION sales_q3 VALUES LESS THAN (TO_DATE('2023-10-01', 'YYYY-MM-DD')),
    PARTITION sales_q4 VALUES LESS THAN (TO_DATE('2024-01-01', 'YYYY-MM-DD'))
);

-- List 파티셔닝 테이블 생성
CREATE TABLE customer (
    id NUMBER PRIMARY KEY,
    name VARCHAR2(100),
    region VARCHAR2(50) NOT NULL
)
PARTITION BY LIST (region) (
    PARTITION east VALUES ('East'),
    PARTITION west VALUES ('West'),
    PARTITION north VALUES ('North'),
    PARTITION south VALUES ('South')
);

-- Hash 파티셔닝 테이블 생성
CREATE TABLE orders (
    id NUMBER PRIMARY KEY,
    customer_id NUMBER NOT NULL,
    order_date DATE NOT NULL
)
PARTITION BY HASH (customer_id) (
    PARTITIONS 4
);

---

Composition 파티셔닝 사용 예제

-- Range-List 파티셔닝 테이블 생성
CREATE TABLE orders (
    order_id NUMBER PRIMARY KEY,
    order_date DATE NOT NULL,
    region VARCHAR2(50) NOT NULL,
    amount NUMBER
)
PARTITION BY RANGE (order_date)
SUBPARTITION BY LIST (region) (
    PARTITION p2023_q1 VALUES LESS THAN (TO_DATE('2023-04-01', 'YYYY-MM-DD')) (
        SUBPARTITION east VALUES ('East'),
        SUBPARTITION west VALUES ('West'),
        SUBPARTITION north VALUES ('North'),
        SUBPARTITION south VALUES ('South')
    ),
    PARTITION p2023_q2 VALUES LESS THAN (TO_DATE('2023-07-01', 'YYYY-MM-DD')) (
        SUBPARTITION east VALUES ('East'),
        SUBPARTITION west VALUES ('West'),
        SUBPARTITION north VALUES ('North'),
        SUBPARTITION south VALUES ('South')
    ),
    PARTITION p2023_q3 VALUES LESS THAN (TO_DATE('2023-10-01', 'YYYY-MM-DD')) (
        SUBPARTITION east VALUES ('East'),
        SUBPARTITION west VALUES ('West'),
        SUBPARTITION north VALUES ('North'),
        SUBPARTITION south VALUES ('South')
    ),
    PARTITION p2023_q4 VALUES LESS THAN (TO_DATE('2024-01-01', 'YYYY-MM-DD')) (
        SUBPARTITION east VALUES ('East'),
        SUBPARTITION west VALUES ('West'),
        SUBPARTITION north VALUES ('North'),
        SUBPARTITION south VALUES ('South')
    )
);

 

위 조합 파티셔닝 예시를 자세히 설명하자면

• PARTITION BY RANGE (order_date)는 order_date 컬럼을 기준으로 데이터를 분할한다.
• SUBPARTITION BY LIST (region)는 각 범위 파티션 내에서 region 컬럼을 기준으로 데이터를 다시 분할한다.
• 각 범위 파티션(p2023_q1, p2023_q2, p2023_q3, p2023_q4)은 분기별로 데이터가 분할되고, 각 분기 내에서 region 값에 따라 데이터를 다시 분할한다.