MySQL 파티션

파티션

테이블 혹은 인덱스를 여러 부분으로 쪼개서 저장하는 방식을 파티션이라고 한다.
파티션을 활용하면 특정 파티션에만 접근하여 검색을 하거나 조작을 할 수 있기 때문에 성능이 향상된다.

사용자는 쿼리를 할 때 파티션과 관계 없이 기존과 똑같이 쿼리를 하면 되고, 내부에서는 미리 설정한 파티션 대로 나눠진다.

RANGE 파티션

테이블을 생성할 때 파티션 키를 함께 지정한다.

CREATE TABLE partTbl(
    userId CHAR(8) NOT NULL,
    name VARCHAR(10) NOT NULL,
    birthYear INT NOT NULL,
    addr CHAR(2) NOT NULL)
PARTITION BY RANGE(birthYear) (
    PARTITION part1 VALUES LESS THAN (1999), 
    PARTITION part2 VALUES LESS THAN (2000), 
    PARTITION part3 VALUES LESS THAN MAXVALUE
);

PARTITION BY을 사용해 파티션을 나눈다.
파티셔닝 방식에는 RANGE, LIST, HASH, KEY 등이 존재한다.

괄호에 들어가는 컬럼명 혹은 컬럼명들은 파티션을 나눌 때 기준이 되는 파티션 키이다. 파티션 키의 값 범위에 따라서 데이터를 분할할 수 있도록 설정한다.

위 코드에서는 출생연도를 파티션 키로 지정해주었고 RANGE 방식으로 파티
셔닝을 했다. LESS THAN 구문과 함께 사용하여 파티션들을 나누었다.

INSERT INTO partTbl
    SELECT userId, name, birthYear, addr FROM sqldb.userTbl;
-- 테이블 데이터 삽입

SELECT TABLE_SCHEMA, TABLE_NAME, PARTITION_NAME, PARTITION_ORDINAL_POSITION, TABLE_ROWS
    FROM INFORMATION_SCHEMA.PARTITIONS
    WHERE TABLE_NAME = 'partTbl';
-- 파티션 정보 확인

INFORMATION_SCHEMA.PARTITIONS로부터 테이블 이름을 지정하여 해당 테이블의 파티션 정보를 확인할 수 있다.

EXPLAIN SELECT * FROM partTbl WHERE birthYear <= 1999;

EXPLAIN을 사용하여 쿼리의 정보들과 함께 쿼리에 사용된 파티션 정보를 확인할 수 있다.

파티션 수정

파티션은 한 번 만들어지면 수정할 수 없는 것이 아니라 추가 분할 및 병합이 가능하다.

파티션 추가 분할

ALTER TABLE partTbl
    REORGANIZE PARTITION part3 INTO (
        PARTITION part3 VALUES LESS THAN (2001),
        PARTITION part4 VALUES LESS THAN MAXVALUE
    );

기존 파티션에서는 part3에 2000년 이후 출생자들의 모든 데이터가 저장되도록 설정되어 있었다.
REORGANIZE PARTITION을 사용해 part3를 두 개의 파티션으로 재분할하였다.

OPTIMIZE TABLE partTbl;

그리고 분할을 적용하기 위해 위 쿼리를 실행한다.

EXPLAIN INSERT INTO partTbl VALUES('KMJ','김민정',2001, '양산');

테이블에 출생연도가 2001년인 새로운 데이터를 넣게 된다면, 해당 쿼리는 part4 파티션을 사용하게 되는 것을 확인할 수 있다.

파티션 병합

ALTER TABLE partTbl
    REORGANIZE PARTITION part1, part2 INTO (
        PARTITION part12 VALUES LESS THAN (2000)    
    );

OPTIMIZE TABLE partTbl; -- 적용

위 쿼리를 통해 part1, part2 파티션들을 part12라는 하나의 파티션으로 병합하였다.

파티션 삭제

ALTER TABLE partTbl DROP PARTITION part12;
OPTIMIZE TABLE partTbl; -- 적용

위 쿼리로 파티션을 삭제할 수도 있다. 그러나 파티션을 삭제하면 해당 파티션 내부의 모든 데이터도 테이블에서 사라지기 때문에 주의해야 한다.

그렇기 때문에 데이터를 삭제하고 싶지 않다면 삭제하려는 파티션 내부의 데이터들을 다른 파티션으로 옮길 수 있도록 파티션 범위를 수정하고 삭제해야 한다.

LIST COLUMNS 파티션

LIST 내부의 값들로 파티셔닝이 가능하다.
LIST 파티셔닝은 단일 컬럼에 대해서 정수형 데이터 타입으로만 설정이 가능하지만, LIST COLUMNS 파티셔닝은 다중 컬럼에 대해서 다양한 데이터 타입 설정이 가능하여 조금 더 유연하다.

PARTITION BY LIST (addr) (
    PARTITION part1 VALUES IN ('서울', '경기'),
    PARTITION part2 VALUES IN ('충북', '충남'),
    PARTITION part3 VALUES IN ('경북', '경남'),
    PARTITION part4 VALUES IN ('전북', '전남'),
    PARTITION part5 VALUES IN ('강원', '제주'),
);

테이블 생성 시 위와 같이 파티셔닝을 하게 되는 경우, 파티션 키로 지정된 주소를 사용해 파티션을 하게 된다. IN 구문의 리스트에 포함된 값들 중 같은 값이 있는 경우 해당 파티션에 종속된다.

정리

• 파티션 테이블에는 외래키를 설정할 수 없다. 단독으로 사용되는 테이블에만 파티션을 설정할 수 있다.
• 스토어드 프로시저, 스토어드 함수, 사용자 변수 등을 파티션 함수나 식에 사용할 수 없다.
• 임시 테이블은 파티셔닝이 불가능하다.
• 파티션 키에는 일부 함수만 사용할 수 있다.
• 파티션 개수는 최대 8192개까지 지원된다.
• RANGE 파티션은 숫자형 연속된 범위를 사용하고, LIST 파티션은 숫자형의 연속되지 않은 하나하나씩 파티션 키 값을 지정한다.
• LIST 파티션에는 MAXVALUE를 사용할 수 없다. 즉, 모든 경우의 파티션 키 값을 지정해야 한다.