(MySQL) 파티션의 종류

RealMySQL 10장 파티션 정리 3

MySQL 에서 다음의 4가지 기본 파티셔닝 기법을 제공하고 있다. 해시 / 키 파티션에 대해서는 리니어 파티션 같은 추가적인 기법도 제공한다.

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

레인지 파티션

파티션 키의 연속된 범위로 파티션을 정의한 방법, 가장 일반적으로 사용되는 파티션 방법 중 하나. 다른 파티션 방법과는 달리 MAXVALUE 라는 키워드를 이용해 명시되지 않은 범위의 키 값이 담긴 레코드를 저장하는 파티션을 정의할 수 있다.

레인지 파티션의 용도

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

레인지 파티션 생성

create table partition_employees (
    id int not null ,
    first_name varchar(30),
    last_name varchar(30),
    hired date not null default '1970-01-01'
) ENGINE=INNODB
PARTITION BY RANGE (YEAR(hired)) (
    PARTITION p0 VALUES LESS THAN (1991) ENGINE = INNODB,
    PARTITION p1 VALUES LESS THAN (1996) ENGINE = INNODB,
    PARTITION p2 VALUES LESS THAN (2001) ENGINE = INNODB,
    PARTITION p3 VALUES LESS THAN MAXVALUE ENGINE = INNODB
);

• PARTITION BY RANGE 키워드로 레인지 파티션을 정의
• PARTITION BY RANGE 뒤에 컬럼 또는 내장 함수를 이용해 파티션 키를 명시
• VALUES LESS THAN 으로 명사된 값보다 작은 값만 파티션에 저장
• VALUES LESS THAN MAXVALUE 로 명시되지 않은 레코드를 저장할 파티션 지정 (선택사항이지만, 만약 지정하지 않으면 명시되지 않은 레코드를 저장할때 에러가 날 것이다)
• 테이블과 파티션은 같은 스토리지 엔진으로 정의, 명시하지 않으면 파티션 스토리지 엔진은 테이블의 스토리지 엔진이 자동으로 적용

partition_employees 테이블에 레코드가 insert 될 때는 입사일자의 년도에 따라 다른 파티션에 저장될 것이다.

레인지 파티션의 분리와 병합

단순 파티션 추가, 삭제

# 2011 이하인 레코드를 저장하기 위한 새로운 파티션 p4 를 추가하는 ALTER 명령
ALTER TABLE partition_employees ADD PARTITION (PARTITION p4 VALUES LESS THEN (2011));
# p0 파티션을 삭제하는 명령 레인지, 리스트 파티션 테이블의 특정 파티션 삭제 작업은 빠르게 실행된다.
ALTER TABLE partition_employees DROP PARTITION p0;

위의 예제 처럼 테이블에 MAXVALUE 파티션이 이미 정의돼 있을 때는 테이블에 새로운 파티션을 추가할 수 없다. 이때는 MAXVALUE 파티션을 분리하는 방법으로 새로운 파티션을 추가해야한다.

기존 파티션 분리와 병합

REORGANIZE PARTITION 명령을 사용한다.

# MAXVALUE 파티션 이었던 p3 을 두 개의 파티션 (p3, p4) 로 나누는 명령
ALTER TABLE partition_employees
 REORGANIZE PARTITION p3 INTO (
  PARTITION p3 VALUES LESS THAN (2012),
  PARTITION p4 VALUES LESS THAN MAXVALUE
);
# p2, p3 파티션을 p23 으로 병합하는 명령
ALTER TABLE partition_employees 
 REORGANIZE PARTITION p2, p3 INTO (
  PARTITION p23 VALUES LESS THAN (2012)
);

레인지 파티션 주의 사항

NULL 은 어떤 값보다 작은 값으로 간주된다. 따라서 partition_employees 파티션 테이블에 hired 컬럼이 NULL 인 레코드가 INSERT 되면 p0 파티션에 저장될 것이다.

날짜 컬럼에 대해 레인지 파티션을 적용할 경우 파티션 키로 다음 2개의 함수 중 하나를 사용하길 권장한다.

• YEAR(date_column)
• TO_DAYS(date_column)

위의 두 날짜 함수는 MySQL 서버 내부적으로 파티션 프루닝 처리가 최적화 되어 있어 성능상 문제가 발생되지 않는다.

리스트 파티션

리스트 파티션은 레인지 파티션과 많은 부분에서 흡사하게 동작하지만, 가장 큰 차이점은 레인지 파티션은 키 값의 연속된 값 범위로 파티션을 구성할 수 있지만 리스트 파티션은 파티션 키 값 하나하나를 리스트로 나열해야 한다는 점이다. 그리고 리스트 파티션은 MAXVALUE 파티션을 정의할 수 없다.

리스트 파티션의 용도

• 파티션 키 값이 코드 값이나 카테고리와 같이 고정적일 때
• 키 값이 연속되지 않고 정렬 순서와 관계없이 파티션을 해야 할 때
• 파티션 키 값을 기준으로 레코드의 건수가 균일하고, 검색 조건에 파티션 키가 자주 사용될 때

create table partition_product (
    id int not null,
    name varchar(30) not null ,
    category_id int not null
) partition by list (category_id) (
    partition p_appliance values in (3),
    partition p_computer values in (1, 9),
    partition p_sport values in (2, 6, 7),
    partition p_etc values in (4, 5, 8, null)
);

• partition by list 키워드로 리스트 파티션을 생성한다.
• partition by list 키워드 뒤에 파티션 키를 정의한다.
• values in (…) 을 사용해 각 파티션별로 저장할 파티션 키 값의 목록을 나열한다.

해시 파티션

• 해시 파티션은 MySQL 에서 정의한 해시 함수에 의해 레코드가 저장될 파티션을 결정하는 방법이다.
• 해시 파티션의 파티션 키는 항상 정수 타입의 컬럼이거나 정수를 반환하는 표현식만 사용될 수 있다.
• MySQL 에서 정의한 해시 함수는 파티션 표현식의 결과 값을 파티션 개수로 나눈 나머지로 저장될 파티션을 결정하는 방식이다.
• 해시 파티션에서 파티션의 개수는 레코드를 각 파티션에 할당하는 알고리즘과 연관되기 때문에 파티션을 병합하거나 분할하는 작업에는 테이블 전체적으로 레코드를 재분배하는 작업이 따른다.

해시 파티션의 용도

• 레인지 파티션이나 리스트 파티션으로 데이터를 균등하게 나누는 것이 어려울때
• 테이블의 모든 레코드가 비슷한 사용 빈도를 보이지만 테이블이 너무 커서 파티션을 적용해야 할 때

대표적인 용도로는 회원 테이블을 들 수 있다. 가입 일자가 오래 되었다고 해서 사용 되지 않는 것도 아니고, 최신 이어서 더 빈번하게 사용 되거나 하지 않기 때문이다. 이처럼 테이블의 데이터가 특정 칼럼의 값에 영향을 받지 않고, 전체적으로 비슷한 사용 빈도를 보일 때 적합한 파티션 방법이다.

create table partition_employees (
    id int not null ,
    first_name varchar(30),
    last_name varchar(30),
    hired date not null default '1970-01-01'
) ENGINE=INNODB
PARTITION BY HASH (id)
partitions 4 (
    PARTITION p0,
    PARTITION p1,
    PARTITION p2,
    PARTITION p3
);

• PARTITION BY HASH 키워드로 파티션 종류를 해시 파티션으로 지정한다.
• PARTITION BY HASH 키워드 뒤에 파티션 키를 명시 한다.
• PARTITIONS n 으로 몇 개의 파티션을 생성할 것인지 명시한다. 1024 보다 작은 값이면 된다.

분리와 병합

해시 파티션의 분리와 병합은 대상 테이블의 모든 파티션에 저장된 레코드를 재분배하는 작업이 필요하다. 파티션의 분리나 병합으로 인해 파티션 개수가 변경된다는 것을 해시 함수 알고리즘을 변경하는 것이기 때문이다. 해시 파티션을 추가하거나 생성하는 작업은 많은 부하를 발생시킨다.

분리

# 파티션1개만 추가하면서 파티션 이름을 부여
ALTER TABLE partition_employees ADD PARTITION(PARTITION p5);
# 동시에 6개의 파티션을 별도의 이름 없이 추가하는 경우
ALTER TABLE partition_employees ADD PARTITION PARTITIONS 6;

병합

정확히는 2개 이상의 파티션을 하나로 병합하는 것이 아닌 단지 파티션의 개수를 줄이는 것이다.

# 1 개의 파티션을 줄이기
ALTER TABLE partition_employees COALESCE PARTITION 1;

키 파티션

• 키 파티션은 해시 파티션과 사용법과 특성이 거의 같다.
• 키 파티션에서는 해시 파티션과 다르게 거의 대부분의 데이터 타입에 대해 파티션 키를 적용할 수 있다.
• MySQL 서버는 선정된 파티션 키의 값을 MD5() 함수를 이용해 해시 값을 계산하고, 그 값을 MOD 연산해서 각 파티션에 분배한다.

create table k1 (
    id int not null ,
    name varchar(30),
    primary key (id)
)
PARTITION BY key ()
partitions 2;

• PARTITION BY KEY 키워드로 키 파티션을 정의한다.
• PARTITION BY KEY 키워드 뒤에 파티션 키 컬럼을 명시한다. 아무것도 명시하지 않으면 MySQL 서버가 자동으로 프라이머리 키나 유니크 키의 모든 컬럼을 파티션 키로 선택한다.
• 유니크 키를 파티션 키로 사용할 때는 유니크 키는 반드시 NOT NULL 이어야 한다.
• 해시 파티션에 비해 파티션 간의 레코드를 더 균등하게 분할할 수 있기 때문에 키 파티션이 더 자주 사용된다.