파티션

☁️ 파티션

파티션 기능은 논리적인 하나의 테이블을 물리적으로 여러 개의 테이블로 분리해서 관리하는 기능이다. 주로 대용량의 테이블을 물리적으로 여러 개의 소규모 테이블로 분산하는 목적으로 사용한다. 또한 파티션을 분리하면 필요한 파티션에만 접근할 수 있다는 장점을 취할 수 있다.

대용량 테이블을 분산하여 저장한다면 조회 쿼리의 성능이 좋아지게 된다. 하지만 잘못된 쿼리를 사용하는 경우 오히려 쿼리의 성능이 나빠질 수 있다. 파티션 사용 시 유의해야 할 사항을 알아보자.

 

파티션을 사용하는 이유

테이블의 데이터가 많다고 해서 무조건 파티션을 적용하는 것은 옳지 않다. 테이블의 크기가 커져 인덱스가 메모리보다 커지는 경우 혹은 데이터 특성상 주기적인 삭제작업이 필요한 경우 등에 파티션을 사용하면 좋다.

 

인덱스는 레코드 조회 성능을 높이는 데 큰 도움을 준다. 하지만 인덱스의 크기가 메모리의 물리적인 크기보다 커진다면 인덱스는 메모리에서 캐싱되지 못한다. 즉, 인덱스를 통해 조회할 때마다 디스크 I/O가 발생하여 성능이 크게 떨어지는 결과를 초래한다.

이런 상황에서 파티션을 사용하면 데이터와 인덱스를 잘게 분리해서 인덱스, 데이터 조각을 메모리에 저장할 수 있는 크기로 만든다. 이제 메모리에서 데이터와 인덱스를 저장하고 사용할 수 있게 되어 성능의 문제를 해결할 수 있다.

테이블의 데이터가 10GB이고 인덱스가 3GB라고 가정해보자. 하지만 대부분의 테이블은 13GB 전체를 항상 사용하는 것이 아니라 그중에서 일정 부분만 활발하게 사용한다. 즉, 게시물이 100만 건이 저장된 테이블이라고 하더라도 그중에서 최신 20~30%의 게시물만 활발하게 조회될 것이다. 대부분의 테이블 데이터가 이런 형태로 사용된다고 볼 수 있는데, 활발하게 사용되는 데이터를 워킹 셋(Working Set)이라고 표현한다. 테이블의 데이터를 활발하게 사용되는 워킹 셋과 그렇지 않은 부분으로 나눠서 파티션할 수 있다면 상당히 효과적으로 성능을 개선할 수 있을 것이다.

<Real MySQL 8.0 2> (백은빈.이성욱 지음) 중에서

 

데이터 파일이나 인덱스 파일이 크다면 백업이나 관리 작업 또한 어려워진다. 이러한 문제는 파티션을 사용하여 데이터 파일의 크기를 조절하여 해결할 수 있다.

 

비슷한 문제로 로그 데이터는 단기간에 대량으로 누적되고 일정 기간이 지나면 쓸모없어지는 특징이 있다. 보통 로그 데이터는 일정 시간이 지나면 백업하고 삭제하는 것이 일반적이다. 대용량 데이터를 가지는 테이블에서 데이터를 백업하거나 삭제하는 작업을 상당히 고부하 작업에 속한다. 로그 테이블을 년도 별로 파티셔닝하면 간단하고 빠르게 백업과 삭제를 해결할 수 있다.

 

파티션 테이블의 검색

파티션 테이블을 조회할 때 성능에 크게 영향을 미치는 두 가지 조건이 있다.

• WHERE 절의 조건으로 검색해야 할 파티션을 선택할 수 있는가
• WHERE 절의 조건이 인덱스 레인지 스캔을 사용할 수 있는가

 

이 두 가지 조건을 조합하여 4가지의 상황을 추측할 수 있다.

1. 파티션 선택 O + 인덱스 레인지 스캔 O = 효율적인 탐색 가능
2. 파티션 선택 X + 인덱스 레인지 스캔 O = 효울적인 탐색 가능 (모든 파티션 탐색)
3. 파티션 선택 O + 인덱스 레인지 스캔 X = 파티션 풀 스캔
4. 파티션 선택 X + 인덱스 레인지 스캔 X = 모든 파티션 풀 스캔

3, 4번 방식은 가능하다면 피하는 것이 좋다. 2번 방식도 파티션 개수가 많은 경우 서버에 부하를 줄 수 있으니 주의하는 것이 좋다.

 

파티션의 인덱스

파티션 테이블의 인덱스는 각 파티션마다 생성된다. 전체 테이블에 해당하는 인덱스는 생성되지 않는다. 따라서 각 파티션의 인덱스는 전체적으로 정렬되어 있지 않고 독자적으로 정렬되어 있다.

만약 두개 이상의 파티션에서 읽은 데이터를 인덱스 컬럼순으로 정렬하고자 한다면, 추가적인 정렬 작업이 이루어진다고 생각할 수 있으나 내부적으로 우선순위 큐를 사용하여 머지&소트 작업을 거쳐 정렬된다.

 

파티션 프루닝

총 3개의 파티션이 존재하는데 옵티마이저에 의해 2개의 파티션만 읽으면 된다고 판단되었다면, 1개의 파티션에는 접근하지 않는다. 이렇게 불필요한 파티션에 대해 접근하지 않는 방식을 파티션 프루닝 이라고 한다.

 

주의사항

MySQL에서 파티션 기능은 많은 제약조건을 가진다.

• 파티션 구분의 기준이 되는 파티션 키 컬럼은 모든 유니크 인덱스에 포함되어야 한다.(프라이머리 키 포함)
• 파이션된 테이블은 로컬 인덱스이며, 모든 파티션은 동일한 구조의 인덱스만 가질 수 있다.
• 한 테이블의 모든 파티션은 동일한 스토리지 엔진만 가질 수 있다.
• 최대 8192개의 파티션을 가질 수 있다.
• 파티션 테이블은 외래키를 사용할 수 없다.
• 파티션 테이블은 전문 검색 인덱스 생성이나 전문 검색 쿼리를 사용할 수 없다.

 

파티션 구분의 기준이 되는 파티션 키 컬럼은 테이블의 모든 유니크 인덱스에 포함되어 있어야 한다. 파티션 키 컬럼은 어느 파티션을 검색할지 범위를 좁히는 데 사용되기 때문이다. 만약 파티션 키 컬럼이 유니크 인덱스에 포함되어 있지 않다면 해당 인덱스만으로는 검색해야 할 파티션을 선택할 수 없기 때문이다.

 

이부분은 왜 제약조건인지 이해가 잘 안되는 부분이라 추후 보완하도록 하겠다. 아마 유니크 인덱스를 걸었음에도 불구하고 인덱스를 제대로 활용할 수 없기 때문이 아닌가 싶다.

 

 

☁️ 파티션의 종류

레인지 파티션

파티션의 구분이 파티션 키 컬럼의 범위로 결정되는 방법이다. 아래의 테이블을 보면 hire 컬럼을 파티션 키 컬럼으로 설정하고 ~1991, 1991 ~ 1996, 1996 ~ 2001, 2001 ~ 라는 hire 범위로 파티션을 나눈다.

보통 로그 테이블에 레인지 파티션을 적용하면 파티션의 두 장점인 테이블의 분리와 필요한 파티션에만 접근 모두를 취할 수 있다.

CREATE TABLE employees (          
    id INT NOT NULL,         
    first_name VARCHAR(30),         
    last_name VARCHAR(30),         
    hired DATE NOT NULL DEFAULT '1970-01-01',
    ...       
) PARTITION BY RANGE( YEAR(hired) ) (         
    PARTITION p0 VALUES LESS THAN (1991),  -- hire < 1991
    PARTITION p1 VALUES LESS THAN (1996),  -- 1991 <= hire < 1996
    PARTITION p2 VALUES LESS THAN (2001),  -- 1996 <= hire < 2001
    PARTITION p3 VALUES LESS THAN MAXVALUE -- 2001 <= hire
  -- 보통 MAXVALUE 파티션은 생성하지 않고 미래를 위한 파티션을 추가로 생성해두는 방식과
  -- 배치 프로그램을 통해 주기적으로 파티션을 추가하는 방법을 사용한다.
  -- MAXVALUE 파티션을 생성한 경우 REORGANIZE PARTITION을 사용하여 파티션을 추가한다.
);

 

레인지 파티션은 아래와 같은 성격을 가진 테이블에 사용하면 효율적이다.

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

레인지 파티션에서 파티션을 추가하거나 삭제할 때는 가장 마지막에 범위의 파티션만 추가할 수 있고, 가장 오래된 파티션 순서로만 삭제할 수 있다. 중간에 있는 파티션은 삭제할 수 없다. 기존의 파티션을 분리하거나 병합할 수는 있는데, 이때 REORGANIZE 명령어를 사용한다.

 

리스트 파티션

파티션 키 값이 코드 혹은 카테고리의 특성을 띄어 고정적일 때, 키 값이 연속적이지 않고 정렬 순서와 관련 없이 파티셔닝 해야할 때, 키 값을 기준으로 레코드 건수가 균일할 때 리스트 파티션을 사용하면 효율적이다.

CREATE TABLE product(          
    id INT NOT NULL,         
    name VARCHAR(30),          
    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_sports VALUES IN (2,6,7),          
    PARTITION p_etc VALUES IN (4,5,8,NULL)       
);

 

해시 / 키 파티션

해시 파티션은 파티션 키를 해시 함수로 해싱하여 반환된 값을 통해 파티셔닝하는 방식이다. 레인지 파티션이나 리스트 파티션 같이 데이터를 균등하게 나누기 어려운 상황, 테이블이 커서 파티셔닝하고 싶은 상황에 사용한다.

키 파티션은 해시 값 계산 시 MD5() 함수를 사용한다는 점을 제외하면 해시 파티션과 모두 동일하다.

CREATE TABLE employees (         
    id INT NOT NULL,         
    first_name VARCHAR(30),         
    last_name VARCHAR(30),         
    hired DATE NOT NULL DEFAULT '1970-01-01',          
    ...       
) PARTITION BY HASH(id) PARTITIONS 4; -- id 해시 값을 기준으로 4개의 파티션을 생성
    -- 아래는 각 파티션에 이름을 붙이고 싶은 경우 명시하면 된다.
    --(           
    -- PARTITION p0 ENGINE=INNODB,            
    -- PARTITION p1 ENGINE=INNODB,            
    -- PARTITION p2 ENGINE=INNODB,            
    --PARTITION p3 ENGINE=INNODB        
--);

해시 파티션에서 새로운 파티션이 추가된다면 기존의 각 파티션에 저장된 모든 레코드가 재배치 되어야 한다. 해시 파티션은 (키 컬럼) MOD (파티션 개수)를 수행하여 레코드를 어떤 파티션에 배치할지 결정하기 때문이다.

해시 파티션은 삭제, 분할, 병합 작업이 불가능하다. 파티션 수를 줄이는 작업을 수행할 수 있다.

 

리니어 해시 / 키 파티션

해시 / 키 파티션은 새로운 파티션을 추가하거나 파티션의 수를 줄일 때 모든 레코드를 대상으로 재배치 작업을 수행해야 한다. 대상 레코드가 많은 경우 엄청난 성능 저하를 발생시킬 것이다. 리니어 해시 / 키 파티션은 이러한 단점을 최소화 하여 보완한 파티션 방식이다.

 

파티션 키 컬럼을 통해 해시 값을 구한 뒤 MOD 연산이 아닌 Power-of-two 알고리즘을 사용하여 레코드를 분류하는 방식을 사용한다. 이러한 방식을 통해 파티션의 추가, 축소 작업 시 대상 파티션에서만 레코드 분류 작업을 수행하면 된다. 일반 해시 / 키 파티션 방식보다 각 파티션의 레코드 수가 균등하지 않을 수 있다.

 

파티션을 조정할 필요가 거의 없다면 일반 해시 / 키 파티션을 사용하자.