테이블 파티셔닝

만약 테이블에 데이터가 대량으로 계속 쌓일 때 해당 테이블에는 어떤 문제가 발생할 수 있을까?

그리고 어떤 것을 고려해서 어떻게 해결해야할까?

고려해볼 수 있는 부분은 인덱스, 정규화와 비정규화, 파티셔닝, 쿼리 최적화, 캐싱 등이 있을 것 같다.

이 해결책 중 하나로 대량의 데이터를 효율적으로 분할하고 저장하기 위해 사용하는 테이블 파티셔닝을 공부해보고자 한다.

 

테이블 파티셔닝의 핵심은 대량의 데이터를 물리적으로 분할하고 파티션 키로 원하는 데이터에 빠르게 액세스하여 관리를 용이하게 하고 성능을 최적화시키는 것이다.

하지만 그 안에 파티셔닝 전략과 테이블의 구조 변경, 키 전략, 데이터의 이전, 인덱스의 재생성으로 인한 효울적인 데이터 처리가 고려되어야 한다.

 

파티셔닝이란?

테이블 파티셔닝은 하나의 테이블을 여러 개의 작은 테이블(파티션)로 물리적으로 분할하여 저장하는 기법을 말한다.

논리적으로는 앱과 SQL 입장에서는 여전히 하나의 테이블처럼 보이지만, 실제로 DB 엔진 입장에서는 각 파티션이 독립적인 저장 공간을 가지고 쪼개어져 있다.  테이블 파티셔닝을 통해 쿼리 성능을 향상시키고, 데이터 관리 및 유지보수를 용이하게 할 수 있게된다.

(논리적으로는 하나의 테이블이기 때문에 조회 쿼리문을 따로 특별하게 작성할 필요가 없다. 당연히 기존 쿼리를 수정할 필요도 없다.)

 

파티셔닝의 종류

 

수평 파티셔닝(Horizontal Partitioning) : 행(row) 기준으로 테이블을 분할

범위 파티셔닝 (range paritioning)

각 파티션에 대해 설정한 파티션 키 값의 범위를 기반으로 데이터를 파티션에 매핑한다. 가장 일반적인 분할 유형이며 날짜와 함께 자주 사용된다.

CREATE TABLE sales_range 
(salesman_id  NUMBER(5), 
salesman_name VARCHAR2(30), 
sales_amount  NUMBER(10), 
sales_date    DATE)
PARTITION BY RANGE(sales_date) 
(
PARTITION sales_jan2000 VALUES LESS THAN(TO_DATE('02/01/2000','DD/MM/YYYY')),
PARTITION sales_feb2000 VALUES LESS THAN(TO_DATE('03/01/2000','DD/MM/YYYY')),
PARTITION sales_mar2000 VALUES LESS THAN(TO_DATE('04/01/2000','DD/MM/YYYY')),
PARTITION sales_apr2000 VALUES LESS THAN(TO_DATE('05/01/2000','DD/MM/YYYY'))
);

목록 파티셔닝 (list partitioning)

특정 컬럼의 값 목록에 따라 데이터를 나눈다. (지역별, 카테고리별 데이터 분할)

CREATE TABLE sales_list
(salesman_id  NUMBER(5), 
salesman_name VARCHAR2(30),
sales_state   VARCHAR2(20),
sales_amount  NUMBER(10), 
sales_date    DATE)
PARTITION BY LIST(sales_state)
(
PARTITION sales_west VALUES('California', 'Hawaii'),
PARTITION sales_east VALUES ('New York', 'Virginia', 'Florida'),
PARTITION sales_central VALUES('Texas', 'Illinois')
PARTITION sales_other VALUES(DEFAULT)
);

해시 파티셔닝 (hash partitioning)

해시 함수를 사용하여 데이터를 무작위로 분산 시킨다.

CREATE TABLE sales_hash
(salesman_id  NUMBER(5), 
salesman_name VARCHAR2(30), 
sales_amount  NUMBER(10), 
week_no       NUMBER(2)) 
PARTITION BY HASH(salesman_id) 
PARTITIONS 4 
STORE IN (data1, data2, data3, data4);

복합 파티셔닝(Composite Partitioning)

복합 파티셔닝은 범위 파티셔닝 방식을 사용하여 데이터를 분할하고, 각 파티션 내에서 해시 또는 리스트 파티셔닝 방식을 사용하여 데이터를 하위 분할 하는 방식이다. 범위-해시 파티셔닝은 범위 분할의 향상된 관리 용이성과 해시 파티셔닝의 데이터 배치 등의 이점을 제공한다.

범위-리스트 파티셔닝은 범위 파티셔닝의 관리 용이성과 하위 파티션에 대한 리스트 파티셔닝의 명시적 제어 기능을 제공한다.

CREATE TABLE sales_composite 
(salesman_id  NUMBER(5), 
 salesman_name VARCHAR2(30), 
 sales_amount  NUMBER(10), 
 sales_date    DATE)
PARTITION BY RANGE(sales_date) 
SUBPARTITION BY HASH(salesman_id)
SUBPARTITION TEMPLATE(
SUBPARTITION sp1 TABLESPACE data1,
SUBPARTITION sp2 TABLESPACE data2,
SUBPARTITION sp3 TABLESPACE data3,
SUBPARTITION sp4 TABLESPACE data4)
(PARTITION sales_jan2000 VALUES LESS THAN(TO_DATE('02/01/2000','DD/MM/YYYY'))
 PARTITION sales_feb2000 VALUES LESS THAN(TO_DATE('03/01/2000','DD/MM/YYYY'))
 PARTITION sales_mar2000 VALUES LESS THAN(TO_DATE('04/01/2000','DD/MM/YYYY'))
 PARTITION sales_apr2000 VALUES LESS THAN(TO_DATE('05/01/2000','DD/MM/YYYY'))
 PARTITION sales_may2000 VALUES LESS THAN(TO_DATE('06/01/2000','DD/MM/YYYY')));

파티셔닝의 장점

작업 시간 단축

파티셔닝을 사용하면 데이터 로드, 인덱스 생성 및 재구축, 백업과 복구와 같은 데이터 관리 작업을 전체 테이블이 아닌 파티션 수준에서 수행할 수 있다.

성능 향상

쿼리 시 파티셔닝 된 특정 파티션만 검색하는 파티션 프루닝(Pruning) 기법을 통해 검색 속도를 높일 수 있다.

유지 보수와 관리

파티셔닝을 사용하면 유지 관리 작업의 예정된 가동 중지로 인한 영향을 크게 줄일 수 있다.

파티션 유지 관리 작업을 위한 파티션 독립성을 통해 동일한 테이블 또는 인덱스의 여러 파티션에 대해 동시 유지 관리 작업을 수행할 수 있다.  또한, 로그처럼 오래되거나 주기적으로 삭제해야 하는 데이터를 특정 파티션으로 분리하여 해당 파티션만 효율적으로 관리할 수 있다.

가용성 증대 

여러 파티션으로 분산되어 있어 하나의 파티션에 문제가 발생해도 전체 테이블에 미치는 영향을 최소화할 수 있다.

편리성

애플리케이션을 수정하지 않고도 파티셔닝을 구현할 수 있다. 예를 들어 SELECT 해당 테이블에 액세스하는 명령문이나 DML 문을 수정하지 않고도 파티셔닝되지 않은 테이블을 파티셔닝된 테이블로 변환할 수 있다. 파티셔닝을 활용하기 위해 애플리케이션 코드를 다시 작성할 필요가 없다.

파티셔닝의 단점

복잡성 증가

파티셔닝을 잘못 설계하면 데이터베이스 관리가 복잡해질 수 있고 파티션 키를 잘못 지정하면 오히려 성능이 저하될 수 있다.

테이블 간 조인 비용 증가

여러 테이블을 조인할 때, 데이터가 여러 파티션에 분산되어 있어 조인 비용이 증가할 수 있다.

테이블과 인덱스 분리 불가

테이블과 인덱스를 별도로 파티셔닝할 수 없다. 테이블을 파티셔닝하면 인덱스도 함께 파티셔닝해야 한다.

일부 DB 엔진 및 기능 제한

모든 MySQL 엔진이 파티셔닝을 지원하는 것은 아니며, 외래 키 제약 조건 등 일부 기능이 파티셔닝된 테이블에서 제한될 수 있다.

경합 심화

결합 파티션과 같이 파티션 개수가 늘어나면 인덱스 경합이 심화될 수 있다.

 

주의점

1. 파티션 테이블에는 외래 키 설정이 불가능하므로 단독으로 사용하는 테이블에만 파티션을 설정할 수 있다.
2. 파티셔닝을 고려하는 기존 테이블의 크기가 충분히 커야 의미가 있다. (오라클 문서에서는 2GB라고 언급되어 있다.)
3. 파티션 수가 너무 많으면 오히려 성능에 악영향을 줄 수 있다. MySQL에서는 파티션 수가 테이블 크기 / 파티션별 평균 데이터 크기 와 같은 연산을 통해 적절하게 설정되도록 관리가 필요하다.
4. 파티셔닝 기준은 업무 성격에 맞게 결정하면 된다.
5. 데이터를 이전하고 인덱스를 구조에 맞게 재생성하고 쿼리 튜닝을 거쳐서 테스트 단계에서 정상 동작하는지 판단해야한다.