(데이터베이스) 파티셔닝

*파티셔닝의 제약사항

-파티셔닝 된 테이블은 스토리지 엔진이 같아야 한다.

-외부키 제약은 사용 할 수 없다

-FULLTEXT인덱스를 사용할 수없다.

- GIS칼럼을 사용할 수 없다.

*장/단점

1)장점

-대량의 데이터 저장

-테이블을 분할 할 수 있기 때문에 많은 양의 데이터를 저장 할 수 있다.

-부하 경감

-해당 디스크에 남아있는 분할데이터를 받을 확률이 높기 때문에 캐시히트율도 높아진다.

- 집계함수(SUM/COUNT)가 병렬처리가 가능해 속도향상을 가져온다.

2)단점

 분할 방법의 정의, 관리, 그것을 취급하는 애플리케이션 측의 구현, 조사 비용 등이 증가한다.

7 | 파티션 테이블

테이블을 생성할 때 파티션으로 테이블을 만들 수 있다. 파티션이라 함은 테이블에 있는 특정 컬럼 값을 기준으로 데이터를 분할해 저장해 놓는 것이다. 이때 논리적인 테이블은 1개지만, 물리적으로는 분할한 만큼 파티션이 만들어져 입력되는 컬럼 값에 따라 분할된 파티션별로 데이터가 저장된다. 파티션 테이블을 만드는 목적은 대용량 테이블의 경우 데이터 조회 시 효율성과 성능을 높이기 위한 것이다.

예를 들어, 샘플 스키마에 매출정보가 있는 sales 테이블은 총 91만여 건의 데이터가 담겨 있다. 이 테이블에서 특정 데이터를 찾으려면 91만 건을 모두 뒤져봐야 한다. 물론 인덱스를 사용하면 좀더 낫겠지만 그렇더라도 91만여 건을 읽는다는 것은 변함없다. 이때 테이블에 있는 판매일자(sales_date)와 판매월(sales_month) 컬럼을 이용해 조회할 때의 성능을 높여 보자. 판매월별로 데이터를 분할해 놓고 데이터 조회 시 특정 월을 조건으로 걸면 전체 데이터를 뒤져보지 않아도 된다.

만약 2000년 11월에 판매된 특정 상품 판매데이터를 검색한다고 하면,

❶ 파티션이 없는 경우: 91만여 건을 모두 뒤져 조건에 맞는 데이터를 걸러낸다.

❷ 파티션이 있는 경우: 2000년 11월 데이터(19,000여건)만 뒤져 조건에 맞는 데이터를 걸러낸다.

이처럼 대용량 테이블은 파티션을 만들어 놓는 것이 훨씬 유리하다. 파티션 테이블 생성도 CRATE TABLE문을 사용한다. sales 테이블 생성 구문을 살펴 보자.

    CREATE TABLE SALES(
      PROD_ID Number(6,0) NOT NULL,
      CUST_ID Number(6,0) NOT NULL,
      CHANNEL_ID Number(6,0) NOT NULL,
      EMPLOYEE_ID Number(6,0) NOT NULL,
      SALES_DATE Date DEFAULT SYSDATE NOT NULL,
      SALES_MONTH Varchar2(6 ),
      QUANTITY_SOLD Number(10,2),
      AMOUNT_SOLD Number(10,2),
      CREATE_DATE Date DEFAULT SYSDATE,
      UPDATE_DATE Date DEFAULT SYSDATE
    )
    PARTITION BY RANGE(SALES_MONTH)
    (
    ...
      PARTITION SALES_Q1_1998 VALUES LESS THAN ('199804') TABLESPACE MYTS,
      PARTITION SALES_Q2_1998 VALUES LESS THAN ('199807') TABLESPACE MYTS,
      PARTITION SALES_Q3_1998 VALUES LESS THAN ('199810') TABLESPACE MYTS,
      PARTITION SALES_Q4_1998 VALUES LESS THAN ('199901') TABLESPACE MYTS,
    ...
      PARTITION SALES_Q4_2003 VALUES LESS THAN ('200401') TABLESPACE MYTS
    );

판매월을 기준으로 RANGE 파티션을 사용했는데, 1998년 1분기는 SALES_Q1_1998, 2분기는 SALES_Q2_1998, 3분기는 SALES_Q3_1998, 4분기는 SALES_Q1_1998란 이름의 파티션을 만들었다. 이렇게 테이블을 생성한 뒤 INSERT를 하면 들어오는 데이터 값에 따라 자동으로 파티션별로 데이터가 적재된다. 이때는 한 파티션에 세 달치 데이터가 들어가게 될 것이다. 파티션 종류에는 RANGE 파티션 외에도, LIST 파티션, 해시 파티션, 그리고 여러 파티션을 조합한 복합 파티션이 있다.

파티션 테이블이든 일반 테이블이든 개발자 입장에서 DML문을 작성할 때 차이점은 전혀 없으나, 대용량 테이블은 파티션으로 분할해 놓으면 성능 향상에 큰 도움이 된다.

지금까지 오라클에서 제공하는 주요 데이터베이스 객체에 대해 알아 보았다. 여기서 소개한 내용 이외에도 데이터베이스 링크, 클러스터, 트리거, IOT, M-View, 함수, 프로시저, 패키지 등이 있다. 함수, 프로시저, 패키지에 대해서는 PL/SQL 학습 시 자세히 설명할 것이며, 나머지 객체들은 사용빈도가 그리 높지 않으므로 필요할 때만 간략히 다루겠다.

테이블의 작성

주의할 점으로써, 파티셔닝의 키로 하고싶은 칼럼을, Primary Key에 포함시킬 필요가 있다.

그러므로, Auto Increment의 컬럼이 있는 테이블이면 힘들다.구성을 다시하는것이 좋을지도..

create_table

CREATE TABLE `list_rtx` (
 `member_id` varchar(40) CHARACTER SET sjis COLLATE sjis_bin NOT NULL,
 `platform` varchar(10) NOT NULL,
 `year` smallint(5) unsigned NOT NULL,
 `month` tinyint(2) unsigned NOT NULL,
 `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
 PRIMARY KEY (`member_id`,`year`,`month`,`platform`)
) ENGINE=InnoDB DEFAULT CHARSET=utf-8

파티션의 작성

1년째의 파티션을 나누고자 한다.

년을 기준으로 범위를 정해서, 나눈다.

그러므로, 이번은 RANGE를 사용한다.

partitioning

ALTER TABLE `list_rtx` PARTITION BY RANGE (YEAR(`year`)) (
PARTITION p2013 VALUES LESS THAN (2013) ENGINE = InnoDB,
PARTITION p2014 VALUES LESS THAN (2014) ENGINE = InnoDB,
PARTITION p2015 VALUES LESS THAN (2015) ENGINE = InnoDB,
PARTITION p2016 VALUES LESS THAN (2016) ENGINE = InnoDB,
PARTITION p2017 VALUES LESS THAN (2017) ENGINE = InnoDB,
PARTITION p2018 VALUES LESS THAN (2018) ENGINE = InnoDB,
PARTITION p2019 VALUES LESS THAN (2019) ENGINE = InnoDB,
PARTITION p2020 VALUES LESS THAN (2020) ENGINE = InnoDB,
PARTITION pmax VALUES LESS THAN MAXVALUE
);

파티션을 나중에 조작하는것은, 서비스가 가동하고있는 경우는 유지보수를 하지않으면 안된다.

그러므로, 할수있는한 처음부터 Usecase를 구체적으로 상정해서, 상정보다 조금더 많은 파티션을 작성한다.

파티션의 확인

confirm_partition

SELECT TABLE_SCHEMA, TABLE_NAME, PARTITION_NAME, PARTITION_ORDINAL_POSITION, TABLE_ROWS
FROM INFORMATION_SCHEMA.PARTITIONS
WHERE TABLE_NAME =  'list_rtx';

어느 파티션이 사용되고있는지는 이하. EXPLAIN PARTITION을  상기에 추가

confirm_used_partition

EXPLAIN PARTITIONS
SELECT TABLE_SCHEMA, TABLE_NAME, PARTITION_NAME, PARTITION_ORDINAL_POSITION, TABLE_ROWS
FROM INFORMATION_SCHEMA.PARTITIONS
WHERE TABLE_NAME =  'list_rtx';

파티션의 삭제

이 파티션내의 데이터도 지워진다／(^o^)＼

delete_partition

ALTER TABLE list_rtx DROP PARTITION p2015;

 파티션의 추가,재구성

파티션의 추가는 기본적으로 지금 있는 파티션의 뒤에밖에 생기지않는다.

그러므로, maxvalue를 사용해서 나눈경우, 파티션을 추가할수 없게된다.

그렇기 때문에, 추가가아닌, 파티션전체를 재구성시킨다.

단, 데이터는 지워지지않는다.

예: 2013년보다 이전의 데이터를 보존하는 것이되었기 대문에, 파티션을 추가하고 싶다.

reorganize_partition

ALTER TABLE mau_list_rtx REORGANIZE PARTITION p2013 INTO (
    PARTITION p2010 VALUES LESS THAN (2010),
    PARTITION p2011 VALUES LESS THAN (2011),
    PARTITION p2012 VALUES LESS THAN (2012),
    PARTITION p2013 VALUES LESS THAN (2013)
);

복합파티셔닝

예를 들면 [매월]로 파티션을 나누고 싶어, 테이블에는 [년]과 [월]이 다른 컬럼에 있는 경우.

서브 파티션(복함파티셔닝)을 사용한다.

sub_partitioning

ALTER TABLE `list_rtx`
PARTITION BY RANGE (YEAR(`year`))
SUBPARTITION BY HASH (MONTH(`month`))
SUBPARTITIONS 12 (
    PARTITION p2013 VALUES LESS THAN (2013) ENGINE = InnoDB,
    PARTITION p2014 VALUES LESS THAN (2014) ENGINE = InnoDB,
    PARTITION p2015 VALUES LESS THAN (2015) ENGINE = InnoDB,
    PARTITION p2016 VALUES LESS THAN (2016) ENGINE = InnoDB,
    PARTITION p2017 VALUES LESS THAN (2017) ENGINE = InnoDB,
    PARTITION p2018 VALUES LESS THAN (2018) ENGINE = InnoDB,
    PARTITION p2019 VALUES LESS THAN (2019) ENGINE = InnoDB,
    PARTITION p2020 VALUES LESS THAN (2020) ENGINE = InnoDB,
    PARTITION pmax VALUES LESS THAN MAXVALUE
);