13.3 MySQL 파티션의 종류

기본 4가지 파티션

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

해시와 키 파티션에 대해서는 리니어(Linear) 파티션과 같은 추가적인 기법도 제공한다

13.3.1 레인지 파티션

파티션 키의 연속된 범위로 파티션을 정의하는 방법

가장 일반적으로 사용되는 파티션 방법 중 하나

다른 파티션과 달리 MAXVALUE라는 키워드를 이용해 명시되지 않은 범위의 키 값이 담긴 레코드를 저장하는 파티션을 정의할 수 있다

13.3.1.1 레인지 파티션의 용도

다음과 같은 성격을 지닌 테이블에서 레인지 파티션을 사용하는 것이 좋음

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

마지막 항목은 모든 파티션에 일반적으로 적용되는 내용이지만 레인지나 리스트 파티션에서 더 필요한 요건

DB에서 파티션 장점

큰 테이블을 작은 크기의 파티션으로 분리
필요한 파티션만 접근(쓰기와 읽기 모두)

첫 번째 보다 두 번째 장점의 효과가 매우 큰편

하지만 파티션을 적용하면서 두 번째 장점보다는 첫 번째 장점에만 집중하다보니 소탐대실되는 경우가 많다

파티션 때문에 오히려 MySQL 서버의 성능을 더 떨어트리게 되는 경우도 생김

저자 경험상 이력을 저장하는 테이블에서 레인지 파티션은 두 가지 장점을 모두 어렵지 않게 취할 수 있어서 최소환 레인지 파티션에 대해서는 정확한 사용 방법을 익혀두길 추천

13.3.1.2 레인지 파티션 테이블 생성

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) ENGINE=INNODB,
    PARTITION p1 VALUES LESS THAN (1996) ENGINE=INNODB,
    PARTITION p2 VALUES LESS THAN (2001) ENGINE=INNODB,
    PARTITION p3 VALUES LESS THAN MAXVALUE
);

PARTITION BY RANGE

해당 키워드로 레인지 파티션 정의
해당 키워드 뒤에 칼럼 또는 내장 함수를 이용해 파티션 키 명시 (여기서는 사원의 입사 일자에서 연도만을 파티션 키로 사용함)

VALUES LESS THAN

괄호안에 명시된 값보다 작은 값만 해당 파티션에 저장하게 설정
LESS THAN 절에 명시된 값은 그 파티션에 포함되지 않음

VALUES LESS THAN MAXVALUE

해당 명령어로 명시되지 않은 레코드를 저장할 파티션을 지정
예제에서는 2001년부터 9999년 사이에 입사한 사원 정보는 p3 파티션에 저장됨
선택사항이므로 지정하지 않아도됨
예제 기준으로 해당 명령어가 정의되지 않으면 hired='2011-02-03' 인 레코드가 INSERT될 때 에러가 발생하면서 Table has no partition for value 2011 이라는 메시지가 표시됨

테이블과 각 파티션은 같은 스토리지 엔진으로 정의될 수 있다

MySQL 8.0 에서는 InnoDB 스토리지 엔진이 기본 스토리지 엔진이므로 별도로 명시하지 않아도 InnoDB 테이블로 생성된다

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

13.3.1.3.1 단순 파티션 추가

employess 테이블에 입사 일자가 2001년부터 2010년 이하인 레코드를 저장하기 위한 새로운 파티션 p4를 추가하는 ALTER TABLE 명령

ALTER TABLE employees
ADD PARTITION (PARTITION p4 VALUES LESS THAN (2011));

그런데 이미 해당 테이블은 LESS THAN MAXVALUE 파티션을 가지고 있어서 ALTER TABLE ... ADD PARTITION 명령으로 새로운 파티션을 추가하려고하면 에러가 발생한다

2011년 파티션이 2개의 파티션에 나뉘어 저장되는 결과가 만들어지는데 하나의 레코드는 반드시 하나의 파티션에만 저장돼야 한다는 기본 조건을 벗어났다\

다음과 같이 해야함

ALTER TABLE employees ALGORITHM=INPLACE, LOCK=SHARED,
REORGANIZE PARTITION p3 INFO (
    PARTITION p3 VALUES LESS THAN (2011),
    PARTITION p4 VALUES LESS THAN MAXVALUE
);

ALTER TABLE ... REORGANIZE PARTITION 명령은 p3 파티션의 레코드를 모두 새로운 두 개의 파티션으로 복사하는 작업을 필요로 한다

p3 파티션의 레코드가 매우 많다면 이 작업은 매우 오랜 시간이 걸림

그래서 레인지 파티션에서는 일반적으로 LESS THAN MAXVALUE 절을 사용하는 파티션은 추가하지 않고, 미래에 사용될 파티션을 2~3 개 정도 더 만들어 놓는 방식으로 테이블을 생성한다고 함

그리고 배치 스크립트를 이용해 주기적으로 파티션 테이블의 여유 기간을 판단해서 파티션을 자동으로 추가하는 방법을 사용한다

이렇게 필요한 파티션을 배치 스크립트에 의존하는 경우 배치 스크립트의 오류로 파티션이 자동으로 추가되지 못할 수도 있다
그러면 응용 프로그램에서는 INSERT를 실행하지 못하게 되는 치명적인 문제가 발생할 수도 있다

LESS THAN MAXVALUES 파티션이 존재한다고 하더라도 이 파티션이 데이터를 가지고 있지 않다면 ALTER TABLE ... REORGANIZE PARTITION 명령은 매우 빨리 완료될 것이므로 성능 걱정을 안해도 된다

MAXVALUE 파티션을 추가할지 말지는 INSERT되는 데이터의 특성이나 배치 스크립트의 안정성에 따라 적절히 판단하면 된다

13.3.1.3.2 파티션 삭제

레인지, 리스트 파티션 테이블에서 특정 파티션을 삭제하는 작업은 아주 빠름

날짜 단위로 파티션된 데이터 테이블에서 오래된 데이터를 삭제하는 용도로 자주 사용

ALTER TABLE employees DROP PARTITION p0;

레인지 파티션을 사용하는 테이블에서는 항상 가장 오래된 파티션 순서로만 삭제할 수 있다

추가는 가장 마지막 파티션만 추가할 수 있는 것과 비슷

13.3.1.3.3 기존 파티션의 분리

ALTER TABLE employees ALGORITHM=INPLACE, LOCK=SHARED,
REORGANIZE PARTITION p3 INFO (
    PARTITION p3 VALUES LESS THAN (2011),
    PARTITION p4 VALUES LESS THAN MAXVALUE
);

MAXVALUE 말고도 다른 파티션도 분리할 수 있다

기존 파티션의 레코드를 새로운 파티션으로 복사해야 하기 때문에 기존 파티션의 레코드 수가 많다면 온라인 DDL로 실행할 수 있게 ALGORITHM과 LOCK절을 사용하자

파티션 재구성 명령은 INPLACE 알고리즘은 사용할 수 있지만 최소한 읽기 잠금이 필요

파티션이 재구성되는 동안 테이블의 쓰기가 불가능해짐
파티션 재구성 작업은 서비스 점검 시간대나 쿼리 처리가 많지 않은 시간대에 진행하는 것이 좋다

13.3.1.3.4 기존 파티션의 병합

병합도 REORGANIZE PARTITION 명령으로 처리할 수 있다

ALTER TABLE employees ALGORITHM=INPLACE, LOCK=SHARED,
REORGANIZE PARTITION p2, p3 INFO (
    PARTITION p23 VALUES LESS THAN (2011),
);

p2 파티션과 p3 파티션을 p23 파티션으로 병합

13.2 주의사항

대부분 파티션의 태생적인 한계이기 때문에 서버가 업그레이드 되어도 여전히 가지는 제약사항일 수 있음
13.2.1 파티션의 제약 사항
MySQL 서버의 파티션이 가지는 제약 사항
- 스토어드 루틴이나 UDF, 사용자 변수 등을 파티션 표현식에 사용할 수 없다.
- 파티션 표현식은 일반적으로 칼럼 그 자체 또는 MySQL 내장 함수를 사용할 수 있는데, 여기서 일부 함수들은 파티션 생성은 가능하지만 파티션 프루닝을 지원하지 않을 수도 있다.
- 기본적인 산술 연산자 +, -, * 사용 가능
- MySQL 8.0.21 버전 기준 사용가능한 내장함수
  - ABS, CEILING, EXTRACT, FLOOR, MOD, DATEDIFF, DAY, DAYOFMONTH, DAYOFWEEK, DAYOFYEAR, HOUR, MICROSECOND, MINUTE, MONTH, QUATHER, SECOND, TIME_TO_SEC, TO_DAYS, TO_SECONDS, UNIX_TIMESTAMP, WEEKDAY, YEAR, YEARWEEK
- 파티션 테이블을 처음 설계할 때 파티션 프루닝 기능이 정상적으로 동작하는지 확인한 뒤 응용 프로그램에 적용하는 것을 권장
- 프라이머리 키를 포함해서 테이블의 모든 유니크 인덱스는 파티션 키 칼럼을 포함해야 한다. : 가장 크게 영향을 미치는 사항
- 파티션된 테이블의 인덱스는 모두 로컬 인덱스이며, 동일 테이블에 소속된 모든 파티션은 같은 구조의 인덱스만 가질 수 있다. 또한 파티션 개별로 인덱스를 변경하거나 추가할 수 없다.
- 동일 테이블에 속한 모든 파티션은 동일 스토리지 엔진만 가질 수 있다.
- 최대(서브 파티션을 포함해서) 8192개의 파티션을 가질 수 있다.
- 파티션 생성 이후 MySQL 서버의 sql_mode 시스템 변수 변경은 데이터 파티션의 일관성을 깨뜨릴 수 있다.
- 파티션 테이블에서는 외래키를 사용할 수 없다.
- 파티션 테이블은 전문 검색 인덱스 생성이나 전문 검색 쿼리를 사용할 수 없다.
- 공간 데이터를 저장하는 컬럼 타입(POINT, GEOMERTY,,,)은 파티션 테이블에서 사용할 수 없다.
- 임시 테이블(Temporary table)은 파티션 기능을 사용할 수 없다.

13.2.2 파티션 사용 시 주의사항

13.2.2.1 파티션과 유니크 키(프라이머리 키 포함)

유니크 키에 대해 파티션 키가 제대로 설정되었는지 간단히 체크하기 위해서는 각 유니크 키 값이 주어졌을 때 어느 파티션에 저장될 지 계산할 수 있는지 확인해보면 됨
- 잘못된 파티션 테이블 생성 방법: p.273 ~ p.274 참고
- 파티션 테이블 생성 방법: p.275
- UNIQUE KEY 에 파티션 키 컬럼이 모두 포함되어야 함

13.2.2.2 파티션과 open_files_limit 시스템 변수 설정

open_files_limit: 동시에 오픈할 수 있는 파일 개수 설정
일반적으로 테이블을 파일 단위로 관리하므로 파티션 개수에 따라 파티션 테이블의 오픈된 파일 갯수는 파티션 개수 * 2~3개가 됨
파티션을 많이 사용하는 경우 해당 변수를 적절히 높은 값으로 다시 설정하기를 권장

mojh7 / real-mysql-study

2023/05/17 ~ 2023/05/23 #53

다음 스터디

정리 범위

정리 범위 무작위 선택 봇 🔎