26.6 Restrictions and Limitations on Partitioning

26.6 Restrictions and Limitations on Partitioning

26.6.1 Partitioning Keys, Primary Keys, and Unique Keys 26.6.2 Partitioning Limitations Relating to Storage Engines 26.6.3 Partitioning Limitations Relating to Functions

이 절에서는 현재 MySQL 파티셔닝 지원에 대한 제약 사항과 제한 사항을 다룹니다.

금지된 construct.

파티셔닝 표현식에서는 다음 construct를 사용할 수 없습니다:

• 저장 프로시저, 저장 함수, 로더블 함수 또는 플러그인
• 선언된 변수 또는 사용자 변수

파티셔닝 표현식에서 허용되는 SQL 함수 목록은
Section 26.6.3, “Partitioning Limitations Relating to Functions”을 참조하십시오.

산술 및 논리 연산자.

산술 연산자 +, -, *의 사용은 파티셔닝 표현식에서 허용됩니다. 그러나 결과는 정수 값이거나 NULL이어야 합니다 ([LINEAR] KEY 파티셔닝의 경우는 이 장의 다른 곳에서 설명한 예외입니다. 자세한 내용은 Section 26.2, “Partitioning Types”를 참조하십시오).

DIV 연산자도 지원되지만 / 연산자는 허용되지 않습니다.

비트 연산자 |, &, ^, <<, >>, ~는 파티셔닝 표현식에서 허용되지 않습니다.

Server SQL mode.

사용자 정의 파티셔닝을 사용하는 테이블은 생성 시점에 적용되던 SQL 모드를 보존하지 않습니다. 이 매뉴얼의 다른 곳에서 설명한 것처럼( Section 7.1.11, “Server SQL Modes” 참조), 많은 MySQL 함수와 연산자의 결과는 서버 SQL 모드에 따라 달라질 수 있습니다.

따라서, 파티션된 테이블을 생성한 이후에 SQL 모드를 변경하면 이러한 테이블의 동작이 크게 달라질 수 있으며, 손상이나 데이터 손실로 쉽게 이어질 수 있습니다. 이러한 이유로, 파티션된 테이블을 생성한 후에는 서버 SQL 모드를 절대 변경하지 말 것을 강력히 권장합니다.

서버 SQL 모드 변경으로 인해 파티션된 테이블을 사용할 수 없게 되는 예로, 다음과 같은 CREATE TABLE 문을 고려해 보겠습니다. 이 문은 NO_UNSIGNED_SUBTRACTION 모드가 적용 중일 때만 성공적으로 실행될 수 있습니다:

1mysql> SELECT @@sql_mode;
2+------------+
3| @@sql_mode |
4+------------+
5|            |
6+------------+
71 row in set (0.00 sec)
8
9mysql> CREATE TABLE tu (c1 BIGINT UNSIGNED)
10    ->   PARTITION BY RANGE(c1 - 10) (
11    ->     PARTITION p0 VALUES LESS THAN (-5),
12    ->     PARTITION p1 VALUES LESS THAN (0),
13    ->     PARTITION p2 VALUES LESS THAN (5),
14    ->     PARTITION p3 VALUES LESS THAN (10),
15    ->     PARTITION p4 VALUES LESS THAN (MAXVALUE)
16    -> );
17ERROR 1563 (HY000): Partition constant is out of partition function domain
18
19mysql> SET sql_mode='NO_UNSIGNED_SUBTRACTION';
20Query OK, 0 rows affected (0.00 sec)
21
22mysql> SELECT @@sql_mode;
23+-------------------------+
24| @@sql_mode              |
25+-------------------------+
26| NO_UNSIGNED_SUBTRACTION |
27+-------------------------+
281 row in set (0.00 sec)
29
30mysql> CREATE TABLE tu (c1 BIGINT UNSIGNED)
31    ->   PARTITION BY RANGE(c1 - 10) (
32    ->     PARTITION p0 VALUES LESS THAN (-5),
33    ->     PARTITION p1 VALUES LESS THAN (0),
34    ->     PARTITION p2 VALUES LESS THAN (5),
35    ->     PARTITION p3 VALUES LESS THAN (10),
36    ->     PARTITION p4 VALUES LESS THAN (MAXVALUE)
37    -> );
38Query OK, 0 rows affected (0.05 sec)

tu를 생성한 이후에 NO_UNSIGNED_SUBTRACTION 서버 SQL 모드를 제거하면, 이 테이블에 더 이상 접근할 수 없게 될 수 있습니다:

1mysql> SET sql_mode='';
2Query OK, 0 rows affected (0.00 sec)
3
4mysql> SELECT * FROM tu;
5ERROR 1563 (HY000): Partition constant is out of partition function domain
6mysql> INSERT INTO tu VALUES (20);
7ERROR 1563 (HY000): Partition constant is out of partition function domain

Section 7.1.11, “Server SQL Modes”도 참조하십시오.

서버 SQL 모드는 파티션된 테이블의 복제에도 영향을 줍니다. 소스와 레플리카의 SQL 모드가 서로 다르면, 파티셔닝 표현식의 평가가 달라질 수 있습니다. 이로 인해, 특정 테이블에 대해 소스와 레플리카의 파티션 간 데이터 분포가 서로 다를 수 있으며, 심지어 소스에서는 성공한 파티션된 테이블에 대한 insert가 레플리카에서는 실패할 수도 있습니다. 최상의 결과를 얻기 위해서는 소스와 레플리카에서 항상 동일한 서버 SQL 모드를 사용해야 합니다.

성능 관련 고려사항.

파티셔닝 작업이 성능에 미치는 영향 중 일부는 다음과 같습니다:

• File system 작업.

  파티셔닝 및 리파티셔닝 작업(예: PARTITION BY ..., REORGANIZE PARTITION, REMOVE PARTITIONING을 사용하는 ALTER TABLE)은 구현을 위해 파일 시스템 작업에 의존합니다. 즉, 이러한 작업의 속도는 파일 시스템의 종류와 특성, 디스크 속도, 스왑 공간, 운영 체제의 파일 처리 효율성, 그리고 파일 처리와 관련된 MySQL 서버 옵션 및 변수와 같은 요인의 영향을 받습니다.

  특히 large_files_support가 활성화되어 있고 open_files_limit이 적절히 설정되어 있는지 확인해야 합니다. InnoDB 테이블이 포함된 파티셔닝 및 리파티셔닝 작업은 innodb_file_per_table을 활성화하면 더 효율적으로 수행될 수 있습니다.

  또한
  Maximum number of partitions도 참조하십시오.

• Table lock.

  일반적으로, 테이블에 대해 파티셔닝 작업을 수행하는 프로세스는 해당 테이블에 쓰기 잠금을 겁니다. 이러한 테이블에 대한 읽기는 상대적으로 거의 영향을 받지 않습니다. 대기 중인 INSERT 및 UPDATE 작업은 파티셔닝 작업이 완료되는 즉시 수행됩니다. 이 제한에 대한 InnoDB 전용 예외는 Partitioning Operations를 참조하십시오.

• Index; partition pruning.

  비파티션 테이블과 마찬가지로, 인덱스를 적절히 사용하면 파티션된 테이블에 대한 쿼리 속도를 크게 높일 수 있습니다. 추가로, 파티션 프루닝을 활용할 수 있도록 파티션된 테이블과 해당 테이블에 대한 쿼리를 설계하면 성능을 대폭 향상시킬 수 있습니다. 자세한 내용은 Section 26.4, “Partition Pruning”을 참조하십시오.

  Index condition pushdown은 파티션된 테이블에서도 지원됩니다. Section 10.2.1.6, “Index Condition Pushdown Optimization”을 참조하십시오.

• LOAD DATA 사용 시 성능.

  MySQL 9.5에서 LOAD DATA는 성능 향상을 위해 버퍼링을 사용합니다. 이 버퍼는 이를 위해 파티션당 130 KB의 메모리를 사용한다는 점을 알아 두어야 합니다.

Partition 수의 최대값.

NDB 스토리지 엔진을 사용하지 않는 특정 테이블에 대해 가능한 파티션의 최대 개수는 8192입니다. 이 수에는 서브파티션도 포함됩니다.

NDB 스토리지 엔진을 사용하는 테이블에 대해 사용자 정의 파티션의 가능한 최대 개수는 사용 중인 NDB Cluster 소프트웨어 버전, 데이터 노드의 수 및 기타 요인에 따라 결정됩니다. 자세한 내용은 NDB and user-defined partitioning을 참조하십시오.

(최대값보다 작기는 하지만) 많은 수의 파티션을 가진 테이블을 생성할 때,
Got error ... from storage engine: Out of resources when opening file 같은 에러 메시지가 발생한다면, open_files_limit 시스템 변수의 값을 증가시켜 문제를 해결할 수 있습니다. 그러나 이는 운영 체제에 따라 달라지며, 모든 플랫폼에서 가능하거나 바람직한 것은 아닙니다. 자세한 내용은 Section B.3.2.16, “File Not Found and Similar Errors”을 참조하십시오.

일부 경우에는 수백 개와 같은 큰 수의 파티션을 사용하는 것이 다른 이유로 바람직하지 않을 수도 있으므로, 파티션의 수를 늘린다고 해서 자동으로 더 좋은 결과가 보장되는 것은 아닙니다.

또한
File system operations도 참조하십시오.

Partitioned InnoDB 테이블에 대해 foreign key가 지원되지 않음.

InnoDB 스토리지 엔진을 사용하는 파티션된 테이블은 foreign key를 지원하지 않습니다. 보다 구체적으로, 다음 두 가지 문장은 참입니다:

1. 사용자 정의 파티셔닝을 사용하는 InnoDB 테이블 정의에는 foreign key 참조가 포함될 수 없으며, 정의에 foreign key 참조가 포함된 InnoDB 테이블은 파티션될 수 없습니다.

2. InnoDB 테이블 정의에는 사용자 파티션 테이블에 대한 foreign key 참조가 포함될 수 없으며, 사용자 정의 파티셔닝을 사용하는 InnoDB 테이블은 foreign key가 참조하는 컬럼을 포함할 수 없습니다.

위에서 나열한 제한 사항의 적용 범위는 InnoDB 스토리지 엔진을 사용하는 모든 테이블에 해당합니다. 이러한 제한을 위반하는 테이블을 생성하게 되는 CREATE TABLE 및 ALTER TABLE 문은 허용되지 않습니다.

ALTER TABLE ... ORDER BY.

파티션된 테이블에 대해 ALTER TABLE ... ORDER BY column 문을 실행하면, 각 파티션 내에서만 로우가 정렬됩니다.

ADD COLUMN ... ALGORITHM=INSTANT.

파티션된 테이블에 대해 ALTER TABLE ... ADD COLUMN ... ALGORITHM=INSTANT를 수행하면, 이후 이 테이블과 파티션 간의 exchange를 할 수 없게 됩니다.

Primary key 변경이 REPLACE 문에 미치는 영향.

일부 경우( Section 26.6.1, “Partitioning Keys, Primary Keys, and Unique Keys” 참조)에는 테이블의 기본 키를 변경하는 것이 바람직할 수 있습니다. 이때, 애플리케이션에서 REPLACE 문을 사용하고 있다면, 이러한 문의 결과가 크게 달라질 수 있다는 점을 유의하십시오. 자세한 내용과 예시는 Section 15.2.12, “REPLACE Statement”를 참조하십시오.

FULLTEXT index.

파티션된 테이블은 FULLTEXT 인덱스나 검색을 지원하지 않습니다.

Spatial column.

POINT 또는 GEOMETRY와 같은 공간 데이터 타입을 가진 컬럼은 파티션된 테이블에서 사용할 수 없습니다.

Temporary table.

Temporary 테이블은 파티션할 수 없습니다.

Log table.

Log 테이블은 파티션할 수 없습니다. 이러한 테이블에 대해 ALTER TABLE ... PARTITION BY ... 문을 실행하면 에러가 발생합니다.

Partitioning key의 data type.

파티셔닝 키는 정수 컬럼이거나 정수로 해석되는 표현식이어야 합니다. ENUM 컬럼을 사용하는 표현식은 사용할 수 없습니다. 컬럼 또는 표현식 값은 NULL일 수도 있습니다. Section 26.2.7, “How MySQL Partitioning Handles NULL”을 참조하십시오.

이 제한에는 두 가지 예외가 있습니다:

1. [LINEAR] KEY로 파티셔닝할 때는, 내부 키 해시 함수가 이러한 타입으로부터 올바른 데이터 타입을 생성하기 때문에 TEXT나 BLOB을 제외한 모든 유효한 MySQL 데이터 타입의 컬럼을 파티셔닝 키로 사용할 수 있습니다. 예를 들어, 다음 두 개의 CREATE TABLE 문은 유효합니다:

1CREATE TABLE tkc (c1 CHAR)
2PARTITION BY KEY(c1)
3PARTITIONS 4;
4
5CREATE TABLE tke
6       ( c1 ENUM('red', 'orange', 'yellow', 'green', 'blue', 'indigo', 'violet') )
7PARTITION BY LINEAR KEY(c1)
8PARTITIONS 6;

2. RANGE COLUMNS 또는 LIST COLUMNS로 파티셔닝할 때는 문자열, DATE, DATETIME 컬럼을 사용할 수 있습니다. 예를 들어, 다음의 각 CREATE TABLE 문은 유효합니다:

1CREATE TABLE rc (c1 INT, c2 DATE)
2PARTITION BY RANGE COLUMNS(c2) (
3       PARTITION p0 VALUES LESS THAN('1990-01-01'),
4       PARTITION p1 VALUES LESS THAN('1995-01-01'),
5       PARTITION p2 VALUES LESS THAN('2000-01-01'),
6       PARTITION p3 VALUES LESS THAN('2005-01-01'),
7       PARTITION p4 VALUES LESS THAN(MAXVALUE)
8);
9
10CREATE TABLE lc (c1 INT, c2 CHAR(1))
11PARTITION BY LIST COLUMNS(c2) (
12       PARTITION p0 VALUES IN('a', 'd', 'g', 'j', 'm', 'p', 's', 'v', 'y'),
13       PARTITION p1 VALUES IN('b', 'e', 'h', 'k', 'n', 'q', 't', 'w', 'z'),
14       PARTITION p2 VALUES IN('c', 'f', 'i', 'l', 'o', 'r', 'u', 'x', NULL)
15);

위 두 예외는 모두 BLOB 또는 TEXT 컬럼 타입에는 적용되지 않습니다.

Subquery.

파티셔닝 키는, 해당 서브쿼리가 정수 값 또는 NULL로 해석되더라도, 서브쿼리가 될 수 없습니다.

Key partitioning에서 column index prefix 미지원.

키로 파티셔닝되는 테이블을 생성할 때, 파티셔닝 키에 포함된 컬럼이 컬럼 prefix를 사용하는 경우, 이 컬럼은 테이블의 파티셔닝 함수에서 사용할 수 없습니다. 세 개의 VARCHAR 컬럼을 가지고, 기본 키가 세 컬럼 전부를 사용하면서 그 중 하나에 대해 prefix를 지정하는 다음 CREATE TABLE 문을 고려해 보십시오. 이 문은 다음과 같이 에러와 함께 거부됩니다:

1mysql> USE d;
2Database changed
3mysql> CREATE TABLE t1 (
4    ->     a VARCHAR(10000),
5    ->     b VARCHAR(25),
6    ->     c VARCHAR(10),
7    ->     PRIMARY KEY (a(10), b, c)
8    -> ) PARTITION BY KEY() PARTITIONS 2;
9ERROR 6123 (HY000): Column 'd.t1.a' having prefix key part 'a(10)' in the
10PARTITION BY KEY() clause is not supported.

키로 테이블을 파티셔닝하는 것에 대한 일반 정보는
Section 26.2.5, “KEY Partitioning”을 참조하십시오.

Subpartition 관련 이슈.

서브파티션은 HASH 또는 KEY 파티셔닝을 사용해야 합니다. RANGE와 LIST 파티션만 서브파티션될 수 있으며, HASH와 KEY 파티션은 서브파티션될 수 없습니다.

SUBPARTITION BY KEY는 PARTITION BY KEY의 경우와 달리 서브파티셔닝 컬럼을 명시적으로 지정해야 합니다. (PARTITION BY KEY에서는 이를 생략할 수 있으며, 생략 시 기본적으로 테이블의 기본 키 컬럼이 사용됩니다.) 다음 문으로 생성된 테이블을 고려해 보십시오:

1CREATE TABLE ts (
2    id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
3    name VARCHAR(30)
4);

다음과 같은 문을 사용하여 동일한 컬럼을 가지면서 KEY로 파티셔닝된 테이블을 생성할 수 있습니다:

1CREATE TABLE ts (
2    id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
3    name VARCHAR(30)
4)
5PARTITION BY KEY()
6PARTITIONS 4;

이전 문은, 테이블의 기본 키 컬럼이 파티셔닝 컬럼으로 사용되는 다음과 같이 작성된 것과 동일하게 처리됩니다:

1CREATE TABLE ts (
2    id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
3    name VARCHAR(30)
4)
5PARTITION BY KEY(id)
6PARTITIONS 4;

그러나, 기본 컬럼을 서브파티셔닝 컬럼으로 사용하여 서브파티션된 테이블을 생성하려는 다음 문은 실패하며, 아래와 같이 컬럼을 명시해야만 문이 성공합니다:

1mysql> CREATE TABLE ts (
2    ->     id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
3    ->     name VARCHAR(30)
4    -> )
5    -> PARTITION BY RANGE(id)
6    -> SUBPARTITION BY KEY()
7    -> SUBPARTITIONS 4
8    -> (
9    ->     PARTITION p0 VALUES LESS THAN (100),
10    ->     PARTITION p1 VALUES LESS THAN (MAXVALUE)
11    -> );
12ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that
13corresponds to your MySQL server version for the right syntax to use near ')
14
15mysql> CREATE TABLE ts (
16    ->     id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
17    ->     name VARCHAR(30)
18    -> )
19    -> PARTITION BY RANGE(id)
20    -> SUBPARTITION BY KEY(id)
21    -> SUBPARTITIONS 4
22    -> (
23    ->     PARTITION p0 VALUES LESS THAN (100),
24    ->     PARTITION p1 VALUES LESS THAN (MAXVALUE)
25    -> );
26Query OK, 0 rows affected (0.07 sec)

이는 알려진 이슈입니다(Bug #51470 참조).

DATA DIRECTORY 및 INDEX DIRECTORY option.

테이블 수준의 DATA DIRECTORY 및 INDEX DIRECTORY 옵션은 무시됩니다(Bug #32091 참조). 이러한 옵션은 InnoDB 테이블의 개별 파티션 또는 서브파티션에 대해 사용할 수 있습니다. DATA DIRECTORY 절에 지정된 디렉터리는 InnoDB가 인식하는 디렉터리여야 합니다. 자세한 내용은 Using the DATA DIRECTORY Clause를 참조하십시오.

Partitioned table 복구 및 재구축.

CHECK TABLE, OPTIMIZE TABLE, ANALYZE TABLE, REPAIR TABLE 문은 파티션된 테이블에 대해 지원됩니다.

또한, ALTER TABLE ... REBUILD PARTITION을 사용하여 파티션된 테이블의 하나 이상의 파티션을 재구축할 수 있습니다. ALTER TABLE ... REORGANIZE PARTITION 역시 파티션을 재구축하게 됩니다. 이 두 문에 대한 자세한 내용은 Section 15.1.11, “ALTER TABLE Statement”를 참조하십시오.

ANALYZE, CHECK, OPTIMIZE, REPAIR, TRUNCATE 작업은 서브파티션에서도 지원됩니다. Section 15.1.11.1, “ALTER TABLE Partition Operations”을 참조하십시오.

Partition 및 subpartition의 file name delimiter.

테이블 파티션 및 서브파티션 파일 이름에는 #P#, #SP#와 같은 자동 생성 delimiter가 포함됩니다. 이러한 delimiter의 대소문자는 달라질 수 있으며, 이에 의존해서는 안 됩니다.