홈 파티셔닝
포스트
취소

파티셔닝

게시 2024/02/07
By Sangwon Lee
19 분읽는 시간

파티션(Partition)이란?

  • 크기가 큰 테이블을 여러 개로 분할하는 기능
  • 인덱스를 사용하더라도 테이블의 크기가 매우 크면 부하가 크기때문에 이를 줄이기 위해 사용하는 기능
  • 파티션을 사용하지 않는다면 인덱스의 크기도 매우 커지고 물리적인 메모리 공간도 많이 필요하다.

파티션의 특징

  • 파티션으로 나누어서 사용한다면 물리적 메모리를 효율적으로 사용할 수 있다.
  • 주로 데이터가 실시간으로 쌓이는 DB 환경에서 효율적이다.
  • 물리적으로 나누어지기 때문에 전체 데이터의 훼손 가능성이 적어진다.
  • Full Scan에서 데이터의 접근 범위를 줄여서 성능 향상이 발생할 수 있다.
  • 파티션으로 나누려고 하는 속성은 반드시 PK 안에 속해있어야 한다.

파티션의 종류

  • Range Partition
  • List Partition
  • Hash Partition
  • Key Partition
  • Composite Partition

Range Partition

  • 범위를 기반으로 파티션을 나누는 방식
  • 범위 기반으로 데이터를 여러 파티션에 균등하게 나눌 수 있는 경우에 사용한다.
  • Range Partition에서 null은 어떤 값보다 가장 작은 값으로 취급한다.
  • 구분값은 하나만 지정할 수 있다.

최초 파티션 설정

CREATE TABLE
  • 기본 형식
CREATE TABLE 테이블명 (
    ...
)
PARTITION BY RANGE (구분기준) (
    PARTITION 파티션명1 VALUES LESS THAN (구분값1),
    PARTITION 파티션명2 VALUES LESS THAN (구분값2),
    PARTITION 파티션명3 VALUES LESS THAN (구분값3),
    ...
);
  • 사용 예시
CREATE TABLE example (
	`idx` BIGINT NOT NULL AUTO_INCREMENT COMMENT '고유번호',
	`title` VARCHAR(200) NOT NULL COMMENT '제목',
	`content` TEXT NOT NULL COMMENT '내용',
	`reg_date` DATETIME NOT NULL COMMENT '작성일시',
	PRIMARY KEY (`idx`, `reg_date`)
)
PARTITION BY RANGE (YEAR(REG_DATE)) (
    PARTITION pRd2022 VALUES LESS THAN (2022),
    PARTITION pRd2023 VALUES LESS THAN (2023),
    PARTITION pRd2024 VALUES LESS THAN MAXVALUE
);
  • 테스트용 쿼리
INSERT INTO example (title, content, reg_date) VALUES ('제목 2020', '내용 2020', '2020-02-07 21:57:16');
INSERT INTO example (title, content, reg_date) VALUES ('제목 2021', '내용 2021', '2021-02-07 21:57:16');
INSERT INTO example (title, content, reg_date) VALUES ('제목 2022', '내용 2022', '2022-02-07 21:57:16');
INSERT INTO example (title, content, reg_date) VALUES ('제목 2023', '내용 2023', '2023-02-07 21:57:16');
INSERT INTO example (title, content, reg_date) VALUES ('제목 2024', '내용 2024', '2024-02-07 21:57:16');
ALTER TABLE
  • 기본 형식
ALTER TABLE 테이블명 
PARTITION BY RANGE (구분기준) (
    PARTITION 파티션명1 VALUES LESS THAN (구분값1),
    PARTITION 파티션명2 VALUES LESS THAN (구분값2),
    PARTITION 파티션명3 VALUES LESS THAN (구분값3),
    ...
);
  • 사용 예시
ALTER TABLE example 
PARTITION BY RANGE (YEAR(REG_DATE)) (
    PARTITION pRd2021 VALUES LESS THAN (2021),
    PARTITION pRd2022 VALUES LESS THAN (2022)
);

파티션 추가

  • 파티션 추가 시 지정되 있는 파티션 구분값 중에서 가장 큰 구분값보다 더 큰 값으로만 구분값을 지정할 수 있다.
  • 파티션 추가를 진행하는데 다음과 같은 오류가 발생한다면 파티션 분리를 진행해야 한다.
    • VALUES LESS THAN value must be strictly increasing for each partition
      • 파티션을 뒤에 새로 추가하는게 아닌 앞이나 중간에 추가하는 경우
    • MAXVALUE can only be used in last partition definition
      • MAXVALUE 파티션이 존재하여 새로운 파티션을 추가할 수 없는 경우
    • 이 오류는 새로 바로 위에서 설명한 경우에 해당하는데, 이런 경우에는 파티션 분리를 통해 파티션을 추가해야 한다.
  • 기본 형식
ALTER TABLE 테이블명 ADD PARTITION(PARTITION 파티션명 VALUES LESS THAN (구분값));
  • 사용 예시
ALTER TABLE example ADD PARTITION(PARTITION pRd2021 VALUES LESS THAN (2021));

파티션 삭제

  • ※ 주의점 : 파티션은 기존에 물리적으로 저장된 데이터를 구분값에 따라 나눠서 분리하는 것이기 때문에
    파티션 제거 시 단순하게 파티션 구조만 바뀌는게 아니라 실제 데이터도 함께 사라진다.

  • 기본 형식

ALTER TABLE 테이블명 DROP PARTITION 파티션명;
  • 사용 예시
ALTER TABLE example DROP PARTITION pRd2021;

파티션 병합

  • 파티션 병합을 위한 조건은 매우 까다롭다.
  • 파티션 기준값이 이웃한 파티션끼리만 병합할 수 있다.
  • REORGANIZE 진행 시 INTO 안에는 무조건 순서대로 작성해야 한다.
    • 병합하여 사라질 파티션을 먼저 작성하고 병합하고 남는 파티션을 작성해야 한다.
    • 그렇지 않으면 라는 오류가 발생한다.
  • 기본 형식
ALTER TABLE example 
REORGANIZE PARTITION 병합될_파티션명, 기존_파티션명 INTO (
    PARTITION 기존_파티션명 VALUES LESS THAN (기존_구분값)
);
  • 사용 예시
ALTER TABLE example 
REORGANIZE PARTITION pRd2021, pRd2022 INTO (
    PARTITION pRd2022 VALUES LESS THAN (2021)
);

파티션 분리

  • 파티션 분리를 위한 조건은 매우 까다롭다.
  • 기존 파티션 구분값 목록에 있는 값의 범위 내에 있거나 그보다 작은 값을 구분값으로 사용해야 할 때 사용한다.
  • 추가하고 싶은 파티션이 있다면 그에 해당하는 구분값의 바로 다음 단계에 있는 파티션을 분리해야 한다.
    • 예시
      • 전제
        • 파티션 p2021, p2023, p2024가 존재하고 각 파티션에 해당하는 구분값 2021, 2023, 2024가 존재
      • 파티션 추가 방법
        • p2020을 추가하고 싶은 경우
          • p2021을 p2020과 p2021로 분리
        • p2022을 추가하고 싶은 경우
          • p2023을 p2022과 p2023로 분리
        • p2025를 추가하고 싶은 경우
          • 그냥 추가하면 된다.
  • REORGANIZE 진행 시 INTO 안에는 무조건 순서대로 작성해야 한다.
    • 신규 파티션을 먼저 작성하고 기존 파티션을 작성해야 한다.
    • 그렇지 않으면 라는 오류가 발생한다.
  • 기본 형식
ALTER TABLE 테이블명
REORGANIZE PARTITION
기존_파티션명 INTO (
    PARTITION 분리할_파티션명 VALUES LESS THAN (분리할_구분값),
    PARTITION 기존_파티션명 VALUES LESS THAN (기존_구분값));
  • 사용 예시
ALTER TABLE example
REORGANIZE PARTITION
pRd2023 INTO (
    PARTITION pRd2022 VALUES LESS THAN (2022),
    PARTITION pRd2023 VALUES LESS THAN (2023)
);

파티션에 있는 데이터 조회

  • 기본 형식
SELECT
    *
FROM
    테이블명
PARTITION
    (파티션_목록);
  • 사용 예시
SELECT
    *
FROM
    example
PARTITION
    (pRd2020, pRd2021);

파티션마다의 데이터 수 확인하기

  • 기본 형식
SELECT
    TABLE_SCHEMA
    ,TABLE_NAME
    ,PARTITION_NAME
    ,PARTITION_ORDINAL_POSITION
    ,TABLE_ROWS
FROM
    INFORMATION_SCHEMA.PARTITIONS
WHERE
    TABLE_NAME =  '테이블명';
  • 사용 예시
SELECT
    TABLE_SCHEMA
    ,TABLE_NAME
    ,PARTITION_NAME
    ,PARTITION_ORDINAL_POSITION
    ,TABLE_ROWS
FROM
    INFORMATION_SCHEMA.PARTITIONS
WHERE
    TABLE_NAME =  'example';

LESS THAN 이해하기

  • 파티션 생성 시 파티션의 구분 기준이 되는 구분값과 동일한 결과가 생성되는 데이터들이
    해당 파티션들에 소속될 것 같지만 놀랍게도 아니다.
  • 기본적으로 LESS THAN이라는 것은 한국어로 번역하면 미만이라는 뜻으로
    이에 따라서 구분 기준 적용 시 해당 구분값보다 작은 데이터들이 해당 파티션에 소속된다.
  • 예시
    • 전제
      • 년도에 따라 생성되는 파티션 존재
      • 예시를 위해서 2018, 2019, 2020, 2022, 2023, MAXVALUE를 기준값으로 삼는다.
      • 테스트를 위해서 각 기준 값을 만족할 수 있는 데이터를 저장한다.
        • 2015 1건, 2020 1건, 2021 3건, 2022 3건, 2023 3건, 2024 3건
      • 예시를 위한 속성명은 작성일로 정의한다.
    • 결과
      • 2018 파티션
        • 해당 건 수 : 1건
        • 이유 : 작성일의 연도가 2018년보다 1건만 존재
      • 2019 파티션
        • 해당 건 수 : 0건
        • 이유 : 작성일의 연도가 2019년보다 작은 데이터가 없음
      • 2020 파티션
        • 해당 건 수 : 0건
        • 이유 : 작성일의 연도가 2020년보다 작은 데이터가 없음
      • 2022 파티션
        • 해당 건 수 : 4건 (2020년, 2021년)
        • 이유 : 작성일의 연도가 2022년도보다 작으면서 다른 파티션에 소속되있지 않음
      • 2023 파티션
        • 해당 건 수 : 3건 (2022년)
        • 이유 : 작성일의 연도가 2023년도보다 작으면서 다른 파티션에 소속되있지 않음
      • MAXVALUE 파티션
        • 해당 건 수 : 6건 (2023년, 2024년)
        • 이유
          • 이유 1 : 2023 파티션은 기준 조건의 결과가 2023보다 적어야 소속할 수 있음
          • 이유 2 : MAXVALUE를 제외하고는 2023이 가장 큰 기준 값이라서 작성일의 연도가 2024인 데이터는 MAXVALUE 파티션에 소속된다.

List Partition

  • 코드나 카테고리 등 특정 값을 기반으로 파티션을 나누는 방식
  • 파티션 키 값을 기준으로 레코드 건수가 균일하고 검색 조건에 파티션 키가 자주 사용되는 경우에 사용한다.
  • MAXVALUE는 지정할 수 없다.
  • 구분값을 하나 이상 지정할 수 있다.
  • 기존에는 정수형 값만 사용할 수 있었지만, MySQL V5.5 부터는 파티션 키 값에 정수형 값 이외에 문자열 타입도 사용할 수 있다.
  • 자료형에 따라 사용하는 키워드가 다르다.
    • 정수형
      • PARTITION BY LIST
    • 문자열
      • PARTITION BY LIST COLUMNS

최초 파티션 설정

CREATE TABLE
  • 기본 형식
CREATE TABLE 테이블명 (
    ...
)
PARTITION BY LIST [COLUMNS] (구분기준) (
    PARTITION 파티션명1 VALUES IN (구분값_목록_1),
    PARTITION 파티션명2 VALUES IN (구분값_목록_2),
    PARTITION 파티션명3 VALUES IN (구분값_목록_3),
    ...
);
  • 사용 예시
CREATE TABLE blog_post (
	`idx` BIGINT NOT NULL AUTO_INCREMENT COMMENT '고유번호',
	`title` VARCHAR(200) NOT NULL COMMENT '제목',
	`content` TEXT NOT NULL COMMENT '내용',
	`reg_date` DATETIME NOT NULL COMMENT '작성일시',
	`type` VARCHAR(20) NOT NULL COMMENT '게시글 유형',
	PRIMARY KEY (`idx`, `type`)
)
PARTITION BY LIST COLUMNS(type) (
    PARTITION pTypePost VALUES IN ('post'),
    PARTITION pTypeVote VALUES IN ('vote'),
    PARTITION pTypeNull VALUES IN (NULL)
);
ALTER TABLE
  • 기본 형식
ALTER TABLE 테이블명
PARTITION BY LIST COLUMNS(구분기준) (
    PARTITION 파티션명1 VALUES IN (구분값_목록_1),
    PARTITION 파티션명2 VALUES IN (구분값_목록_2),
    PARTITION 파티션명3 VALUES IN (구분값_목록_3),
    ...
);
  • 사용 예시
ALTER TABLE blog_post
PARTITION BY LIST COLUMNS(type) (
    PARTITION pTypePost VALUES IN ('post'),
    PARTITION pTypeVote VALUES IN ('vote'),
    PARTITION pTypeNull VALUES IN (NULL)
);

파티션 추가

  • 기본 형식
ALTER TABLE 테이블명 ADD PARTITION(PARTITION 파티션명 VALUES IN (구분값_목록_));
  • 사용 예시
ALTER TABLE blog_post ADD PARTITION(PARTITION pTypeGame VALUES IN ('game'));

파티션 삭제

  • 기본 형식
ALTER TABLE 테이블명 DROP PARTITION 파티션명;
  • 사용 예시
ALTER TABLE blog_post DROP PARTITION pTypeGame;

파티션 병합

  • 기본 형식
ALTER TABLE 테이블명 
REORGANIZE PARTITION 기존_파티션명1, 기존_파티션명2 INTO (
    PARTITION 신규_파티션명 VALUES IN (기준값_목록)
);
  • 사용 예시
ALTER TABLE blog_post
REORGANIZE PARTITION pTypeGame, pTypeGame2 INTO (
    PARTITION pTypeGame VALUES IN ('game', 'game2')
);

파티션 분리

  • 기본 형식
ALTER TABLE 테이블명
REORGANIZE PARTITION pTypeGame INTO (
    PARTITION 신규_파티션명1 VALUES IN (기준값_목록_1),
    PARTITION 신규_파티션명2 VALUES IN (기준값_목록_2)
);
  • 사용 예시
ALTER TABLE blog_post
REORGANIZE PARTITION pTypeGame INTO (
    PARTITION pTypeGame VALUES IN ('game'),
    PARTITION pTypeGame2 VALUES IN ('game2')
);

Hash Partition

  • Hash 함수에 의해 레코드가 저장될 파티션을 결정하는 방식
  • 테이블의 모든 레코드가 비슷한 사용빈도를 보이지만 너무 커서 파티션이 필요한 경우 사용된다.

최초 파티션 설정

  • 최초 할당 시 지정한 숫자만큼의 파티션이 자동으로 생성된다.
  • 파티션명은 p0, p1, p2…처럼 p 뒤에 0부터 시작하는 정수값이 붙어서 자동으로 생성된다.
CREATE TABLE
  • 기본 형식
CREATE TABLE 테이블명 (
    ...
)
PARTITION BY HASH (구분기준)
PARTITIONS 파티션_개수;
  • 사용 예시
CREATE TABLE blog_reply (
	`idx` BIGINT NOT NULL AUTO_INCREMENT COMMENT '고유번호',
	`content` TEXT NOT NULL COMMENT '내용',
	`reg_date` DATETIME NOT NULL COMMENT '작성일시',
	PRIMARY KEY (`idx`, `reg_date`)
)
PARTITION BY HASH (idx)
PARTITIONS 4;
ALTER TABLE
  • 기본 형식
ALTER TABLE 테이블명
PARTITION BY HASH (구분기준)
PARTITIONS 파티션_개수;
  • 사용 예시
ALTER TABLE blog_reply
PARTITION BY HASH (idx)
PARTITIONS 4;

파티션 추가

  • 파티션의 갯수로 MOD 연산한 결과에 따라 각 레코드를 저장할 파티션을 결정하므로,
    새로이 파티션이 추가될 경우 파티션에 저장된 모든 레코드는 재배치 되어야 하므로 많은 부하가 발생한다.

  • 기본 형식

ALTER TABLE 테이블명 ADD PARTITION(PARTITION 파티션명 ENGINE = INNODB);
  • 사용 예시
ALTER TABLE blog_reply ADD PARTITION(PARTITION p5 ENGINE = INNODB);

파티션 삭제

  • 파티션 키 값을 이용하여 데이터를 각 파티션으로 분산한 것이므로,
    각 파티션에 저장된 레코드의 부류를 사용자가 예측할 수 없기에
    해시나 키를 이용한 파티션에서는 파티션 단위의 삭제는 불가능하다.

파티션 병합

  • Hash Partition에서는 파티션 병합 기능을 제공하지 않는다.

파티션 분리

  • Hash Partition에서는 파티션 분리 기능을 제공하지 않는다.

Key Partition

  • MD5() 함수를 이용한 Hash 값을 기반으로 파티션을 나누고 Hash Partition과 거의 동일하다.
  • 선정된 파티션 키값에 대하여 내부적으로 MD5()를 이용하여 해시값을 계산하고,
    그 값에 MOD를 적용하여 저장할 파티션을 결정한다.
  • Hash Partition의 PARTITION BY HASH를 PARTITION BY KEY로 바꿔주기만 하면 된다.

Composite Partition

  • 여러 파티션 기법을 결합하여 사용하는 방식
  • 합성 분할이라고도 부른다.
SQL
이 기사는 저작권자의 CC BY 4.0 라이센스를 따릅니다.
최근 업데이트
바로가기