13.2 파티션 - 주의사항

파티션은 5.1 버전부터 도입되어 8.0 버전까지 많은 발전이 있었지만, 아직도 많은 제약을 지니고있다.

대부분 파티션의 태생적인 한계이기 때문에 MySQL 서버가 아무리 업그레이드 된다고 하더라도 여전히 가질 제약사항일 수도 있다.

파티션의 제약 사항

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
CREATE TABLE tb_article (
    article_id INT NOT NULL AUTO_INCREMENT,
    reg_date DATETIME NOT NULL,
    ...
    PRIMARY KEY(article_id, reg_date)
) PARTITION BY RANGE ( YEAR(reg_Date) ) (
    PRATITION p2009 VALUES LESS THAN (2010),
    PRATITION p2010 VALUES LESS THAN (2011),
    PRATITION p2011 VALUES LESS THAN (2012),
    PRATITION p9999 VALUES LESS THAN MAXVALUE
);

PARTITION BY RANGE 절은 이 테이블이 레인지 파티션을 사용한다는 것을 의미한다.

스토어드 루틴이나 UDF, 사용자 변수 등을 파티션 표현식에 사용할 수 없다.
파티션 표현식은 일반적으로 컬럼 그 자체 또는 MySQL 내장 함수를 사용할 수 있는데, 일부 함수들은 파티션 생성은 가능하지만 프루닝을 지원하지 않을 수 있다.
프라이머리 키를 포함해서 테이블의 모든 유니크 인덱스는 파티션 키 컬럼을 포함해야 한다.
파티션 테이블의 인덱스는 모두 로컬 인덱스이며, 동일 테이블에 소속된 모든 파티션은 같은 구조의 인덱스만 가질 수 있다.
파티션 개별로 인덱스를 변경하거나 추가할 수 없다.
동일 테이블에 속한 모든 파티션은 동일 스토리지 엔진만 가질 수 있다.
서브 파티션까지 포함하여 8192개의 파티션을 가질 수 있다.
파티션 생성 이후 MySQL sql_mode 시스템 변수 변경은 데이터 파티션의 일관성을 깨뜨릴 수 있다.
파티션 테이블에서는 외래키를 사용할 수 없다.
파티션 테이블은 전문 검색 인덱스 생성이나 전문 검색 쿼리를 사용할 수 없다.
공간 데이터를 저장하는 컬럼 타입은 파티션 테이블에서 사용할 수 없다.
임시 테이블은 파티션 기능을 사용할 수 없다.

일반적으로 파티션 테이블을 생성할 때 가장 크게 영향을 미치는 제약 사항은 모드 유니크 인덱스에 파티션 키 컬럼이 포함돼야 한다는 것이다.

파티션 표현식에는 기본적인 산술 연산자를 사용할 수 있고, 추가로 MySQL 내장 함수를 사용할 수 있다.

내장 함수들을 파티션 표현시게 사용할 수 있다고 해서 모두 파티션 프루닝을 지원하는 것은 아니므로 메뉴얼을 참조하자.

파티션 사용 시 주의사항

파티션 테이블의 경우 프라이머리 키를 포함한 유니크 키에 대해서는 상당히 머리 아픈 제약사항이 있다.

파티션의 목적은 작업 범위를 좁히는 것인데, 유니크 인덱스는 중복 레코드에 대한 체크 작업 때문에 범위가 좁혀지지 않는다는 점이다.

MySQL 파티션은 일반 테이블과 같이 별도의 파일로 관리되는데, 이와 관련하여 서버가 조작할 수 있는 파일의 개수와 연관된 제약도 있다.

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

종류와 관계없이 테이블에 유니크 인덱스가 있으면 파티션 키는 모든 유니크 인덱스의 일부 또는 모든 컬럼을 포함해야 한다.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
CREATE TABLE tb_parition (
  fd1 INT NOT NULL,
  fd2 INT NOT NULL,
  fd3 INT NOT NULL,
  UNIQUE KEY (fb1, fb2)
) PARTITION BY HASH (fd3)
  PARTITIONS 4
;

CREATE TABLE tb_parition (
  fd1 INT NOT NULL,
  fd2 INT NOT NULL,
  fd3 INT NOT NULL,
  UNIQUE KEY (fb1),
  UNIQUE KEY (fb2)
) PARTITION BY HASH (fd1 + fd2)
  PARTITIONS 4
;

CREATE TABLE tb_parition (
  fd1 INT NOT NULL,
  fd2 INT NOT NULL,
  fd3 INT NOT NULL,
  PRIMARY KEY (fb1),
  UNIQUE KEY (fb2, fb3)
) PARTITION BY HASH (fd1 + fd2)
  PARTITIONS 4
;

위 예시는 모두 잘못된 테이블 파티션을 생성하는 방법이다.

유니크 키에 대해 파티션 키가 제대로 설정됐는지 확인하려면 각 유니크 키에 대해 값이 주어졌을 때 해당 레코드가 어느 파티션에 저장돼 있는지 계산할 수 있어야 한다는 점을 기억하면 된다.

첫 번째 쿼리는 유니크 키와 파티션 키가 전혀 연관이 없기 때문에 불가능
두 번째 쿼리는 첫 번째 유니크 키 컬럼인 fd1만으로 파티션 결정이 되지 않는다(fd2 컬럼도 같이 있어야 파티션 위치를 판단할 수 있다.)
세 번째 쿼리 또한 fd1 값 만으로 파티션 판단이 되지 않으며, 유니크 키인 fd2, fd3로도 파티션 위치를 결정할 수 없다.

아래는 옳은 예시이다.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
CREATE TABLE tb_parition (
  fd1 INT NOT NULL,
  fd2 INT NOT NULL,
  fd3 INT NOT NULL,
  UNIQUE KEY (fb1, fb2, fd3)
) PARTITION BY HASH (fd1)
  PARTITIONS 4
;

CREATE TABLE tb_parition (
  fd1 INT NOT NULL,
  fd2 INT NOT NULL,
  fd3 INT NOT NULL,
  UNIQUE KEY (fb1, fd2)
) PARTITION BY HASH (fd1 + fd2)
  PARTITIONS 4
;

CREATE TABLE tb_parition (
  fd1 INT NOT NULL,
  fd2 INT NOT NULL,
  fd3 INT NOT NULL,
  PRIMARY KEY (fb1, fd2, fd3),
  UNIQUE KEY (fb3)
) PARTITION BY HASH (fd3)
  PARTITIONS 4
;

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

MySQL에서는 일반적으로 테이을 파일 단위로 관리하기 때문에 MySQL 서버에서 동시에 오픈된 파일의 개수가 상당히 많아질 수 있다.

이를 제한하기 위해 시스템 변수에 동시에 오픈할 수 있는 적절한 파일의 개수를 설정하는데, 파티션 되지 않은 일반 테이블은 테이블 1개당 오픈된 파일의 개수가 2~3개 수준이지만 파티션 테이블에서는 파티션의 개수 * 2~3 개가 된다.

따라서 파티션을 많이 사용하는 경우 시스템 젼수를 절절히 높은 값으로 다시 설정할 필요가 있다.