■ 파티셔닝에 대한 제한 사항
이 글에서는 MySQL 파티셔닝 지원에 대한 현재 제한 사항에 대해 설명합니다.
▶︎ 금지된 문법.
파티셔닝 표현식에서는 다음 구성이 허용되지 않습니다.
저장 프로 시저, 저장 함수, UDF 또는 플러그인
선언된 변수 또는 사용자 변수
▶︎ 산술 및 논리 연산자.
산술 연산자 +,-및 *는 파티셔닝 표현식에서 사용할 수 있습니다. 그러나 결과는 정수값 또는 NULL이어야합니다([LINEAR] KEY 파티셔닝의 경우 제외).
DIV 연산자도 지원되며/연산자는 허용되지 않습니다.
비트 연산자 |, &, ^, <<, >> 및 ~는 파티셔닝 표현식에서 허용되지 않습니다.
▶︎ HANDLER 문법.
이전에는 HANDLER문이 파티션된 테이블에서 지원되지 않았습니다. 이 제한은 MySQL 5.7.1부터 제거되었습니다.
▶︎ 서버 SQL 모드.
사용자 정의 파티셔닝을 사용하는 테이블은 작성된 시점에 유효한 SQL 모드를 유지하지 않습니다. 즉, 작성된 시점의 SQL모드를 계속 가지고 가지 않습니다. 많은 MySQL 함수 및 연산자의 결과는 현재 서버 SQL 모드에 따라 변경 될 수 있습니다. 따라서 파티션된 테이블을 작성한 후 언제든지 SQL 모드를 변경하면 해당 테이블의 동작이 크게 변경되어 데이터가 손상되거나 손실될 수 있습니다. 이러한 이유로, 파티션된 테이블을 작성한 후에는 서버 SQL 모드를 변경하지 않는 것이 좋습니다.
▷ 예제.
다음 예는 서버 SQL 모드의 변경으로 인해 파티션된 테이블의 동작 변경 사항을 보여줍니다.
1. 오류 처리.
다음과 같이 파티션 표현식이 DIV 0 열 또는 MOD 0 열과 같은 파티션 된 테이블을 작성한다고 가정합니다.
mysql> CREATE TABLE tn (c1 INT)
-> PARTITION BY LIST(1 DIV c1) (
-> PARTITION p0 VALUES IN (NULL),
-> PARTITION p1 VALUES IN (1)
-> );
Query OK, 0 rows affected (0.05 sec)
MySQL의 기본 동작은 오류없이 0으로 나눈 결과에 대해 NULL을 반환하는 것입니다.
mysql> SELECT @@sql_mode;
+------------+
| @@sql_mode |
+------------+
| |
+------------+
1 row in set (0.00 sec)
-- NULL 즉 아무것도 표현되지 않습니다.
mysql> INSERT INTO tn VALUES (NULL), (0), (1);
Query OK, 3 rows affected (0.00 sec)
Records: 3 Duplicates: 0 Warnings: 0
그러나 0으로 나누기를 오류로 처리하고 엄격한 오류 처리를 시행하도록 서버 SQL 모드를 변경하면 다음과 같이 동일한 INSERT 문이 실패합니다.
mysql> SET sql_mode='STRICT_ALL_TABLES,ERROR_FOR_DIVISION_BY_ZERO';
Query OK, 0 rows affected (0.00 sec)
mysql> INSERT INTO tn VALUES (NULL), (0), (1);
ERROR 1365 (22012): Division by 0
2. 테이블 접근성. 때때로 서버 SQL 모드를 변경하면 파티션된 테이블을 사용할 수 없게 될 수 있습니다. NO_UNSIGNED_SUBTRACTION 모드가 유효한 경우에만 다음 CREATE TABLE 문을 실행할 수 있습니다.
mysql> SELECT @@sql_mode;
+------------+
| @@sql_mode |
+------------+
| |
+------------+
1 row in set (0.00 sec)
mysql> CREATE TABLE tu (c1 BIGINT UNSIGNED)
-> PARTITION BY RANGE(c1 - 10) (
-> PARTITION p0 VALUES LESS THAN (-5),
-> PARTITION p1 VALUES LESS THAN (0),
-> PARTITION p2 VALUES LESS THAN (5),
-> PARTITION p3 VALUES LESS THAN (10),
-> PARTITION p4 VALUES LESS THAN (MAXVALUE)
-> );
ERROR 1563 (HY000): Partition constant is out of partition function domain
mysql> SET sql_mode='NO_UNSIGNED_SUBTRACTION';
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT @@sql_mode;
+-------------------------+
| @@sql_mode |
+-------------------------+
| NO_UNSIGNED_SUBTRACTION |
+-------------------------+
1 row in set (0.00 sec)
mysql> CREATE TABLE tu (c1 BIGINT UNSIGNED)
-> PARTITION BY RANGE(c1 - 10) (
-> PARTITION p0 VALUES LESS THAN (-5),
-> PARTITION p1 VALUES LESS THAN (0),
-> PARTITION p2 VALUES LESS THAN (5),
-> PARTITION p3 VALUES LESS THAN (10),
-> PARTITION p4 VALUES LESS THAN (MAXVALUE)
-> );
Query OK, 0 rows affected (0.05 sec)
tu를 작성한 후 NO_UNSIGNED_SUBTRACTION 서버 SQL 모드를 제거하면 더 이상 이 테이블에 액세스하지 못할 수 있습니다.
mysql> SET sql_mode='';
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT * FROM tu;
ERROR 1563 (HY000): Partition constant is out of partition function domain
mysql> INSERT INTO tu VALUES (20);
ERROR 1563 (HY000): Partition constant is out of partition function domain
서버 SQL 모드는 파티션된 테이블의 복제에도 영향을줍니다. 마스터 및 슬레이브의 개별 SQL 모드는 파티셔닝 표현식이 다르게 평가 될 수 있습니다. 이로 인해 주어진 테이블의 마스터 및 슬레이브 사본에서 파티션 간 데이터 분배가 달라지고 마스터에서 성공한 파티션된 테이블에 대한 삽입이 슬레이브에서 실패 할 수도 있습니다. 최상의 결과를 얻으려면 항상 마스터와 슬레이브에서 동일한 서버 SQL 모드를 사용해야합니다.
▶︎ 성능 고려 사항.
파티션 작업이 성능에 미치는 영향은 다음 목록에 있습니다.
+ 파일 시스템 작업. 파티셔닝 및 파티션 분할 조작(예 : PARTITION BY ..., ALTER TABLEZE PARTITION 또는 REMOVE PARTITIONING을 갖는 ALTER TABLE)은 구현을 위한 파일 시스템 조작에 따라 다릅니다. 이는 이러한 작업의 속도가 파일 시스템 유형 및 특성, 디스크 속도, 스왑 공간, 운영 체제의 파일 처리 효율성 및 파일 처리와 관련된 MySQL 서버 옵션 및 변수와 같은 요소의 영향을 받음을 의미합니다. 특히 large_files_support가 사용 가능하고 open_files_limit가 올바르게 설정되어 있는지 확인해야 합니다. MyISAM 스토리지 엔진을 사용하는 파티션된 테이블의 경우 myisam_max_sort_file_size를 늘리면 성능이 향상될 수 있습니다. innodb_file_per_table을 활성화하면 InnoDB 테이블과 관련된 파티션 및 파티션 작업을보다 효율적으로 수행 할 수 있습니다.
+ MyISAM 및 파티션 파일 디스크립터 사용법. 파티션 된 MyISAM 테이블의 경우 MySQL은 열려있는 각 테이블에 대해 각 파티션마다 2개의 파일 디스크립터를 사용합니다. 이는 특히 ALTER TABLE 작업을 수행 할 때 후자의 테이블이 분할되지 않는다는 점을 제외하고는 동일한 테이블에서보다 분할된 MyISAM 테이블에서 작업을 수행하기 위해 더 많은 파일 디스크립터가 필요하다는 것을 의미합니다.
이 SQL 문으로 작성된 테이블과 같이 100 개의 파티션이 있는 MyISAM 테이블 t가 있습니다.
CREATE TABLE t (c1 VARCHAR(50))
PARTITION BY KEY (c1) PARTITIONS 100
ENGINE=MYISAM;
노트
간결하게 하기 위해 이 예제에 표시된 테이블에 KEY파티셔닝을 사용하지만 여기에 설명된 파일 디스크립터 사용법은 사용된 파티셔닝 유형에 관계없이 모든 파티션된 MyISAM 테이블에 적용됩니다. InnoDB와 같은 다른 스토리지 엔진을 사용하는 파티션된 테이블은 이 문제의 영향을 받지 않습니다.
이제 여기에 표시된 명령문을 사용하여 파티션을 101개로 분할하기를 원한다고 가정합니다.
이 ALTER TABLE 문을 처리하기 위해 MySQL은 402개의 파일 설명자를 사용합니다. 즉, 100 개의 원래 파티션 각각에 대해 2개, 101개의 새로운 파티션 각각에 대해 2개를 사용합니다. 이는 테이블 데이터 재구성 중에 모든 파티션(이전 파티션 및 새 파티션)을 동시에 열어야하기 때문입니다. 그러한 조작을 수행할 것으로 예상되는 경우, open_files_limit 시스템 변수가 수용하기에 너무 낮게 설정되지 않았는지 확인하는 것이 좋습니다.
+ 테이블 잠금.
일반적으로 테이블에서 파티셔닝 조작을 실행하는 프로세스는 테이블에서 쓰기 잠금을 사용합니다. 이러한 테이블의 읽기는 상대적으로 영향을받지 않습니다. 보류중인 INSERT 및 UPDATE 작업은 분할 작업이 완료되는 즉시 수행됩니다. 이 제한에 대한 InnoDB 관련 예외는 분할 작업을 참조하십시오.
+ 스토리지 엔진.
파티션 작업, 쿼리 및 업데이트 작업은 일반적으로 InnoDB 또는 NDB 테이블보다 MyISAM 테이블에서 더 빠릅니다.
+ 색인; 파티션 정리.
파티션되지 않은 테이블과 마찬가지로 인덱스를 올바르게 사용하면 파티션된 테이블에 대한 쿼리 속도가 크게 향상될 수 있습니다. 또한 파티션 정리를 활용하기 위해 이러한 테이블에서 파티션된 테이블 및 쿼리를 설계하면 성능이 크게 향상 될 수 있습니다.
이전에는 파티션된 테이블에 대해 인덱스 조건 푸시 다운이 지원되지 않았습니다.
+ LOAD DATA의 성능.
MySQL 5.7에서 LOAD DATA는 버퍼링을 사용하여 성능을 향상시킵니다. 버퍼는 이를 달성하기 위해 파티션 당 130KB 메모리를 사용합니다.
▶︎ 최대 파티션 수.
주어진 테이블에 대해 가능한 최대 파티션 수는 8192입니다. 이 수에는 하위 파티션이 포함됩니다.
많은 수의 파티션을 가지고 있는 (최대값보다는 적은) 테이블을 생성 할 때 다음과 같은 오류를 만날 수 있습니다.(Got error ... from storage engine: Out of resources when opening file, you may be able to address the issue by increasing the value of the open_files_limit system variable. : 스토리지 엔진에서 오류가 발생했습니다... : 파일을 열 때 리소스가 모두 사용되었습니다. open_files_limit 시스템 변수의 값을 증가시켜 문제를 해결할 수 있습니다.) 그러나 이것은 운영 체제에 따라 다르며 모든 플랫폼에서 가능하거나 권장되는 것은 아닙니다. 경우에 따라 다른 상황으로 인해 더 많은(수백개의) 파티션을 사용하더라도 좋은 결과를 얻지 못할 수 있습니다.
▶︎ 쿼리 캐시가 지원되지 않습니다.
쿼리 캐시는 파티션된 테이블에 대해 지원되지 않으며 파티션된 테이블과 관련된 쿼리에 대해 자동으로 비활성화됩니다. 이러한 쿼리에는 쿼리 캐시를 사용할 수 없습니다.
▶︎ 파티션당 키 캐시.
MySQL 5.7에서는 CACHE INDEX 및 LOAD INDEX INTO CACHE문을 사용하여 파티션된 MyISAM 테이블에 키 캐시가 지원됩니다. 키 캐시는 하나, 여러 또는 모든 파티션에 대해 정의 될 수 있으며, 하나, 여러 또는 모든 파티션에 대한 인덱스는 키 캐시에 사전로드 될 수 있습니다.
▶︎ 파티션 된 InnoDB 테이블에는 외래키가 지원되지 않음.
InnoDB 스토리지 엔진을 사용하는 파티션 된 테이블은 외래키를 지원하지 않습니다. 보다 구체적으로 이것은 다음 내용에서 사실임을 의미합니다.
1. 사용자 정의 파티셔닝을 사용하는 InnoDB테이블의 정의에는 외래키 참조가 포함될 수 없습니다. 정의에 외래키 참조가 포함된 InnoDB 테이블은 분할될 수 없습니다.
2. InnoDB 테이블 정의에는 사용자 분할 테이블에 대한 외래키 참조가 포함될 수 없습니다. 사용자 정의 파티션이있는 InnoDB 테이블에는 외래키가 참조하는 열이 포함될 수 없습니다.
방금 나열된 제한 범위에는 InnoDB 스토리지 엔진을 사용하는 모든 테이블이 포함됩니다. 이러한 제한을 위반하는 테이블을 생성하는 CREATE TABLE 및 ALTER TABLE 문은 허용되지 않습니다.
▶︎ ALTER TABLE ... ORDER BY.
파티션된 테이블에 대해 ALTER TABLE ... ORDER BY 컬럼 명령문을 실행하면 각 파티션 내에서만 행 순서가 조정됩니다.
▶︎ 기본키를 수정하여 REPLACE 문에 미치는 영향.
경우에 따라 테이블의 기본키를 수정하는 것이 좋습니다. 응용 프로그램이 REPLACE문을 사용하고 이를 수행하면 이러한 명령문의 결과가 크게 변경될 수 있습니다.
▶︎ FULLTEXT 인덱스.
파티션된 테이블은 InnoDB 또는 MyISAM스토리지 엔진을 사용하는 파티션된 테이블의 경우에도 FULLTEXT 인덱스 또는 검색을 지원하지 않습니다.
▶︎ 공간 열(Spatial columns).
POINT또는 GEOMETRY와 같은 공간 데이터 유형이 있는 컬럼은 파티션된 테이블에서 사용할 수 없습니다.
▶︎ 임시 테이블.
임시 테이블을 분할할 수 없습니다.
▶︎ 로그 테이블.
로그 테이블을 분할할 수 없습니다. 이러한 테이블의 ALTER TABLE ... PARTITION BY ... 문은 오류와 함께 실패합니다.
▶︎ 파티션 키의 데이터 유형.
분할키는 정수 열이거나 정수로 해석되는 식이어야 합니다. ENUM컬럼을 사용하는 표현식은 사용할 수 없습니다. 열 또는 표현식 값은 NULL일 수도 있습니다.
이 제한에는 두 가지 예외가 있습니다.
1.[LINEAR] KEY로 파티셔닝할 때 MySQL의 내부키 해싱 함수가 이러한 유형에서 올바른 데이터 형식을 생성하므로 TEXT 또는 BLOB 이외의 유효한 MySQL 데이터 형식의 열을 파티셔닝 키로 사용할 수 있습니다. 예를 들어, 다음 두 개의 CREATE TABLE 문이 유효합니다.
CREATE TABLE tkc (c1 CHAR)
PARTITION BY KEY(c1)
PARTITIONS 4;
CREATE TABLE tke
( c1 ENUM('red', 'orange', 'yellow', 'green', 'blue', 'indigo', 'violet') )
PARTITION BY LINEAR KEY(c1)
PARTITIONS 6;
2. RANGE COLUMNS 또는 LIST COLUMNS로 파티셔닝할 때 문자열, DATE 및 DATETIME 컬럼을 사용할 수 있습니다. 예를 들어, 다음 CREATE TABLE 문은 각각 유효합니다.
CREATE TABLE rc (c1 INT, c2 DATE)
PARTITION BY RANGE COLUMNS(c2) (
PARTITION p0 VALUES LESS THAN('1990-01-01'),
PARTITION p1 VALUES LESS THAN('1995-01-01'),
PARTITION p2 VALUES LESS THAN('2000-01-01'),
PARTITION p3 VALUES LESS THAN('2005-01-01'),
PARTITION p4 VALUES LESS THAN(MAXVALUE)
);
CREATE TABLE lc (c1 INT, c2 CHAR(1))
PARTITION BY LIST COLUMNS(c2) (
PARTITION p0 VALUES IN('a', 'd', 'g', 'j', 'm', 'p', 's', 'v', 'y'),
PARTITION p1 VALUES IN('b', 'e', 'h', 'k', 'n', 'q', 't', 'w', 'z'),
PARTITION p2 VALUES IN('c', 'f', 'i', 'l', 'o', 'r', 'u', 'x', NULL)
);앞의 예외중 어느 것도 BLOB또는 TEXT열 유형에는 적용되지 않습니다.
▶︎ 하위 쿼리.
하위 쿼리가 정수 값 또는 NULL로 해석 되더라도 분할 키는 하위 쿼리가 아닐 수 있습니다.
▶︎ 키 분할에는 컬럼 인덱스 접두사가 지원되지 않습니다.
키로 파티션 된 테이블을 작성할 때, 컬럼 접두부(인덱스 크기지정)를 사용하는 파티션키의 컬럼은 테이블의 파티션 기능에서 사용되지 않습니다. 세개의 VARCHAR컬럼이 있고 기본키가 세개의 컬럼을 모두 사용하고 그 중 두 개의 접두부(인덱스 크기지정)를 지정하는 다음
CREATE TABLE문이 있습니다.
CREATE TABLE t1 (
a VARCHAR(10000),
b VARCHAR(25),
c VARCHAR(10),
PRIMARY KEY (a(10), b, c(2))
) PARTITION BY KEY() PARTITIONS 2;이 명령문은 파티션키가 지정되지 않아도 허용되지만 내부적으로 테이블은 실제로 파티션키의 접두부(컬럼 b)를 포함하지 않는 기본키 컬럼만 사용하여 다음 명령문을 발행한 것처럼 작성됩니다.
CREATE TABLE t1 (
a VARCHAR(10000),
b VARCHAR(25),
c VARCHAR(10),
PRIMARY KEY (a(10), b, c(2))
) PARTITION BY KEY(b) PARTITIONS 2;
파티셔닝 키에 지정된 모든 컬럼이 접두부(Prefix)를 사용하는 경우를 제외하고는 경고가 표시되지 않거나 다른 표시가 표시되지 않습니다. 이 경우 명령문이 다음과 같은 오류메시지와 함께 실패합니다.
mysql> CREATE TABLE t2 (
-> a VARCHAR(10000),
-> b VARCHAR(25),
-> c VARCHAR(10),
-> PRIMARY KEY (a(10), b(5) c(2))
-> ) PARTITION BY KEY() PARTITIONS 2;ERROR 1503 (HY000): A PRIMARY KEY must include all columns in the table's partitioning function
이는 이러한 테이블을 변경하거나 업그레이드할 때도 발생하며, 파티셔닝 함수에 사용된 컬럼이 빈 PARTITION BY KEY()절을 사용하여 테이블 기본키의 컬럼으로 내재적으로 정의된 경우를 포함합니다.
이것은 알려진 문제이며 MySQL 8.0에서 허용되는 동작을 사용하지 않음으로써 해결됩니다. MYSQL 8.0에서 접두사를 사용하는 컬럼이 테이블의 파티셔닝 기능에 포함된 경우 서버는 각 해당 컬럼에 대해 적절한 경고를 기록하거나 필요한 경우 설명 오류를 발생시킵니다. (파티셔닝 키에서 접두사(prefix)가 붙은 컬럼을 사용하는 것은 향후 MySQL 버전에서 완전히 제거될 것입니다.)
▶︎ 하위 파티션 관련 문제
서브 파티션은 HASH 또는 KEY파티셔닝을 사용해야합니다. RANGE 및 LIST 파티션만 서브 파티션될 수 있습니다. HASH 및 KEY파티션은 하위 파티션으로 나눌 수 없습니다.
SUBPARTITION BY KEY를 사용하면 PARTITION BY KEY의 경우와 달리 하위 파티션열 또는 열을 명시 적으로 지정해야 하는데, 이 경우 생략 할 수 있습니다.(이와 같은 경우 기본적으로 테이블의 기본키 열이 사용됩니다). 이 명령문으로 작성된 테이블이 있습니다.
CREATE TABLE ts (
id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(30)
);
다음과 같은 명령문을 사용하여 KEY로 파티션된 동일한 열을 갖는 테이블을 작성할 수 있습니다.
CREATE TABLE ts (
id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(30)
)
PARTITION BY KEY()
PARTITIONS 4;
이전 명령문은 다음과 같이 작성된 것처럼 처리되며 테이블의 기본키 컬럼은 파티션 컬럼으로 사용됩니다.
CREATE TABLE ts (
id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(30)
)
PARTITION BY KEY(id)
PARTITIONS 4;
그러나 기본 파티션을 서브 파티션 열로 사용하여 서브 파티션된 테이블을 작성하려는 다음 명령문은 실패하며 명령문이 성공하려면 여기에 표시된대로 컬럼을 지정해야합니다.
mysql> CREATE TABLE ts (
-> id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
-> name VARCHAR(30)
-> )
-> PARTITION BY RANGE(id)
-> SUBPARTITION BY KEY()
-> SUBPARTITIONS 4
-> (
-> PARTITION p0 VALUES LESS THAN (100),
-> PARTITION p1 VALUES LESS THAN (MAXVALUE)
-> );
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ')
mysql> CREATE TABLE ts (
-> id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
-> name VARCHAR(30)
-> )
-> PARTITION BY RANGE(id)
-> SUBPARTITION BY KEY(id)
-> SUBPARTITIONS 4
-> (
-> PARTITION p0 VALUES LESS THAN (100),
-> PARTITION p1 VALUES LESS THAN (MAXVALUE)
-> );
Query OK, 0 rows affected (0.07 sec)
▶︎ DATA DIRECTORY 및 INDEX DIRECTORY 옵션.
DATA DIRECTORY 및 INDEX DIRECTORY에는 파티션된 테이블과 함께 사용하는 경우 다음 제한 사항이 적용됩니다.
+ 테이블 수준 DATA DIRECTORY 및 INDEX DIRECTORY 옵션은 무시됩니다.
+ Windows에서 DATA DIRECTORY 및 INDEX DIRECTORY 옵션은 MyISAM 테이블의 개별 파티션 또는 하위 파티션에 대해 지원되지 않습니다. 그러나 InnoDB 테이블의 개별 파티션 또는 하위 파티션에 DATA DIRECTORY를 사용할 수 있습니다.
▶︎ 파티션된 테이블 복구 및 재 구축.
CHECK TABLE, OPTIMIZE TABLE, ANALYZE TABLE 및 REPAIR TABLE 문은 파티션된 테이블에 지원됩니다.
또한 ALTER TABLE ... REBUILD PARTITION을 사용하여 파티션된 테이블의 하나 이상의 파티션을 재 빌드할 수 있습니다. ALTER TABLE ... REORGANIZE PARTITION을 사용하면 파티션이 다시 작성됩니다.
MySQL 5.7.2부터 ANALYZE, CHECK, OPTIMIZE, REPAIR 및 TRUNCATE 작업이 하위 파티션에서 지원됩니다. REBUILD는 MySQL 5.7.5 이전의 구문에도 적용되었지만 효과는 없었습니다.
mysqlcheck, myisamchk 및 myisampack은 파티션된 테이블에서 지원되지 않습니다.
▶︎ 내보내기 옵션 (FLUSH TABLES).
FLUSH TABLES 문의 FOR EXPORT 옵션은 MySQL 5.7.4 및 이전 버전의 파티션 된 InnoDB 테이블에 지원되지 않습니다.
▶︎ 파티션 및 하위 파티션의 파일 이름 구분 기호
테이블 파티션 및 하위 파티션 파일 이름에는 #P# 및 #SP#와 같은 생성된 구분자가 포함됩니다. 이러한 분리 문자의 소문자는 다양할 수 있으며 의존해서는 안됩니다.
■ 파티션 키, 프리머리키 및 유니크키
이 섹션에서는 파티션키와 프리머리키 및 유니크키의 관계에 대해 설명합니다. 이 관계를 관리하는 규칙은 다음과 같이 표현될 수 있습니다. 파티션된 테이블의 파티션 표현식에 사용된 모든 열은 테이블이 가질 수있는 모든 고유키의 일부여야 합니다.
다시 말해, 테이블의 모든 유니크키는 테이블의 파티션식에서 모든 컬럼을 사용해야합니다. (이것은 유니크키이므로 테이블의 프리머리키도 포함합니다.이 특정 사례는 이 글의 뒷부분에서 설명합니다.) 예를 들어, 다음 테이블 작성 명령문 각각이 유효하지 않습니다.
CREATE TABLE t1 (
col1 INT NOT NULL,
col2 DATE NOT NULL,
col3 INT NOT NULL,
col4 INT NOT NULL,
UNIQUE KEY (col1, col2)
)
PARTITION BY HASH(col3)
PARTITIONS 4;
CREATE TABLE t2 (
col1 INT NOT NULL,
col2 DATE NOT NULL,
col3 INT NOT NULL,
col4 INT NOT NULL,
UNIQUE KEY (col1),
UNIQUE KEY (col3)
)
PARTITION BY HASH(col1 + col3)
PARTITIONS 4;각각의 경우 제안된 테이블에는 분할식에 사용된 모든 열이 포함되지 않은 프리머리키가 하나 이상 있습니다.
다음 각 명령문은 유효하며 만약 유효하지 않은 테이블 작성 명령문이 있다면 작동 할 수 있는 한 가지 방법을 나타냅니다.
CREATE TABLE t1 (
col1 INT NOT NULL,
col2 DATE NOT NULL,
col3 INT NOT NULL,
col4 INT NOT NULL,
UNIQUE KEY (col1, col2, col3)
)
PARTITION BY HASH(col3)
PARTITIONS 4;
CREATE TABLE t2 (
col1 INT NOT NULL,
col2 DATE NOT NULL,
col3 INT NOT NULL,
col4 INT NOT NULL,
UNIQUE KEY (col1, col3)
)
PARTITION BY HASH(col1 + col3)
PARTITIONS 4;
이 예는 이러한 경우(유효하지 않은 테이블 작성 문법)에 발생하는 오류를 보여줍니다.
mysql> CREATE TABLE t3 (
-> col1 INT NOT NULL,
-> col2 DATE NOT NULL,
-> col3 INT NOT NULL,
-> col4 INT NOT NULL,
-> UNIQUE KEY (col1, col2),
-> UNIQUE KEY (col3)
-> )
-> PARTITION BY HASH(col1 + col3)
-> PARTITIONS 4;
ERROR 1491 (HY000): A PRIMARY KEY must include all columns in the table's partitioning function
col1과 col3이 모두 작성된 파티션키에 포함되어 있기 때문에 CREATE TABLE 문은 실패하지만 이 컬럼 중 어느 것도 테이블에서 프리머리키의 일부가 아닙니다. 이것은 유효하지 않은 테이블 정의에 대한 가능한 수정 사항을 보여줍니다.
mysql> CREATE TABLE t3 (
-> col1 INT NOT NULL,
-> col2 DATE NOT NULL,
-> col3 INT NOT NULL,
-> col4 INT NOT NULL,
-> UNIQUE KEY (col1, col2, col3),
-> UNIQUE KEY (col3)
-> )
-> PARTITION BY HASH(col3)
-> PARTITIONS 4;
Query OK, 0 rows affected (0.05 sec)이 경우 작성된 파티셔닝키 col3은 두 고유 키의 일부이며 테이블 작성 명령문이 성공합니다.
다음표는 프리머리키 둘 다에 속하는 컬럼을 파티션키에 포함시킬 방법이 없기 때문에 파티션될 수 없습니다.
CREATE TABLE t4 (
col1 INT NOT NULL,
col2 INT NOT NULL,
col3 INT NOT NULL,
col4 INT NOT NULL,
UNIQUE KEY (col1, col3),
UNIQUE KEY (col2, col4)
);
모든 프리머리키는 기본적으로 유니크키 이므로 이 제한에는 테이블의 프리머리키(있는 경우)도 포함됩니다. 예를 들어, 다음 두 명령문은 유효하지 않습니다.
CREATE TABLE t5 (
col1 INT NOT NULL,
col2 DATE NOT NULL,
col3 INT NOT NULL,
col4 INT NOT NULL,
PRIMARY KEY(col1, col2)
)
PARTITION BY HASH(col3)
PARTITIONS 4;
CREATE TABLE t6 (
col1 INT NOT NULL,
col2 DATE NOT NULL,
col3 INT NOT NULL,
col4 INT NOT NULL,
PRIMARY KEY(col1, col3),
UNIQUE KEY(col2)
)
PARTITION BY HASH( YEAR(col2) )
PARTITIONS 4;두 경우 모두 프리머리키에는 파티셔닝 표현식에서 참조되는 모든 컬럼이 포함되지 않습니다. 그러나 다음 두 생성문은 모두 유효합니다.
CREATE TABLE t7 (
col1 INT NOT NULL,
col2 DATE NOT NULL,
col3 INT NOT NULL,
col4 INT NOT NULL,
PRIMARY KEY(col1, col2)
)
PARTITION BY HASH(col1 + YEAR(col2))
PARTITIONS 4;
CREATE TABLE t8 (
col1 INT NOT NULL,
col2 DATE NOT NULL,
col3 INT NOT NULL,
col4 INT NOT NULL,
PRIMARY KEY(col1, col2, col4),
UNIQUE KEY(col2, col1)
)
PARTITION BY HASH(col1 + YEAR(col2))
PARTITIONS 4;테이블에 유니크키가 없는 경우 (프리머리키가 없는 경우 포함)이 제한 사항이 적용되지 않으며 컬럼 유형이 파티션유형과 호환되는 한 파티션 표현식에서 컬럼을 사용할 수 있습니다.
같은 이유로 키에 테이블의 파티셔닝 표현식에 사용된 모든 컬럼이 포함되어 있지 않으면 나중에 파티션 된 테이블에 프리머리키를 추가할 수 없습니다. 다음과 같이 생성된 파티션된 테이블을 있습니다.
mysql> CREATE TABLE t_no_pk (c1 INT, c2 INT)
-> PARTITION BY RANGE(c1) (
-> PARTITION p0 VALUES LESS THAN (10),
-> PARTITION p1 VALUES LESS THAN (20),
-> PARTITION p2 VALUES LESS THAN (30),
-> PARTITION p3 VALUES LESS THAN (40)
-> );
Query OK, 0 rows affected (0.12 sec)
다음 ALTER TABLE 문 중 하나를 사용하여 t_no_pk에 프리머리키를 추가할 수 있습니다.
# 가능한 PK
mysql> ALTER TABLE t_no_pk ADD PRIMARY KEY(c1);
Query OK, 0 rows affected (0.13 sec)
Records: 0 Duplicates: 0 Warnings: 0
# 이 PK를 삭제
mysql> ALTER TABLE t_no_pk DROP PRIMARY KEY;
Query OK, 0 rows affected (0.10 sec)
Records: 0 Duplicates: 0 Warnings: 0
# 다른 가능한 PK를 사용합니다
mysql> ALTER TABLE t_no_pk ADD PRIMARY KEY(c1, c2);
Query OK, 0 rows affected (0.12 sec)
Records: 0 Duplicates: 0 Warnings: 0
# 이 PK를 삭제
mysql> ALTER TABLE t_no_pk DROP PRIMARY KEY;
Query OK, 0 rows affected (0.09 sec)
Records: 0 Duplicates: 0 Warnings: 0그러나 c1은 파티션키의 일부이지만 제공된 기본키의 일부가 아니기 때문에 다음 명령문은 실패합니다.
# 오류 1503과 함께 실패
mysql> ALTER TABLE t_no_pk ADD PRIMARY KEY(c2);
ERROR 1503 (HY000): A PRIMARY KEY must include all columns in the table's partitioning functiont_no_pk의 파티셔닝 표현식에는 c1만 있으므로 c2에만 유니크키를 추가하려는 시도는 실패합니다. 그러나 c1과 c2를 모두 사용하는 유니크키를 추가할 수 있습니다.
이 규칙은 ALTER TABLE ... PARTITION BY를 사용하여 분할하려는 기존의 파티션되지 않은 테이블에도 적용됩니다. 다음과 같이 생성된 테이블 np_pk가 있습니다.
mysql> CREATE TABLE np_pk (
-> id INT NOT NULL AUTO_INCREMENT,
-> name VARCHAR(50),
-> added DATE,
-> PRIMARY KEY (id)
-> );
Query OK, 0 rows affected (0.08 sec)
추가된 컬럼이 테이블에서 유니크키의 일부가 아니기 때문에 다음 ALTER TABLE 문이 오류와 함께 실패합니다.
mysql> ALTER TABLE np_pk
-> PARTITION BY HASH( TO_DAYS(added) )
-> PARTITIONS 4;
ERROR 1503 (HY000): A PRIMARY KEY must include all columns in the table's partitioning function
그러나 파티션 컬럼에 id컬럼을 사용하는 이 명령문은 다음과 같이 유효합니다.
mysql> ALTER TABLE np_pk
-> PARTITION BY HASH(id)
-> PARTITIONS 4;
Query OK, 0 rows affected (0.11 sec)
Records: 0 Duplicates: 0 Warnings: 0np_pk의 경우 파티션식의 일부로 사용할수 있는 유일한 컬럼은 id입니다. 파티셔닝 표현식에서 다른 열을 사용하여 이 테이블을 분할하려면 먼저 원하는 컬럼을 프리머리키에 추가하거나 프리머리키를 모두 삭제하여 테이블을 수정해야합니다.
■ 스토리지 엔진과 관련된 파티션 제한
사용자 정의 테이블 파티셔닝이 있는 스토리지 엔진의 사용에는 다음과 같은 제한이 적용됩니다.
▶︎ MERGE storage engine.
사용자 정의 파티셔닝과 MERGE 스토리지 엔진은 호환되지 않습니다. MERGE 저장 엔진을 사용하는 테이블은 분할할 수 없습니다. 분할된 테이블은 병합할 수 없습니다.
▶︎ FEDERATED storage engine.
FEDERATED 테이블의 파티셔닝은 지원되지 않으며, 파티션 FEDERATED 테이블을 만들 수 없다.
▶︎ CSV 스토리지 엔진.
CSV 스토리지 엔진을 사용하는 파티션 테이블은 지원되지 않습니다. 분할된 CSV 테이블을 만들 수 없습니다.
▶︎ InnoDB 스토리지 엔진.
InnoDB 외래키와 MySQL 파티셔닝은 호환되지 않습니다. 파티션된 InnoDB 테이블은 외래키 참조를 가질 수 없으며 외래키가 참조하는 열을 가질 수도 없습니다. 외래키가 있거나 참조하는 InnoDB 테이블은 분할 할 수 없습니다.
InnoDB는 서브 파티션에 다중 디스크 사용을 지원하지 않습니다. (이는 현재 MyISAM에서만 지원됩니다.)
또한, ALTER TABLE... Optimize PARTITION은 InnoDB 스토리지 엔진을 사용하는 분할 테이블에서 올바르게 작동하지 않습니다. 이러한 테이블에 대해서는 ALTER TABLE ... REBUILD PARTITION 및 ALTER TABLE ... ANALYZE PARTITION을 사용합니다.
▶︎ 사용자 정의 파티셔닝 및 NDB 스토리지 엔진 (NDB 클러스터).
키를 사용한 분할(LINEAR 키 포함)은 NDB 스토리지 엔진에 지원되는 유일한 유형의 파티션입니다. 정상적인 상황에서는 NDB 클러스터에서 [LINEAR]키 이외의 파티션 유형을 사용하여 NDB 클러스터 테이블을 생성 할 수 없으며, 이를 시도하면 오류가 발생합니다.
예외(제작 용 아님) : NDB 클러스터 SQL 노드에서 새 시스템 변수를 ON으로 설정하여 이 제한을 무시할 수 있습니다. 이 작업을 선택하면 [LINEAR]KEY 이외의 파티셔닝 유형을 사용하는 테이블은 프로덕션에서 지원되지 않습니다. 이 경우 KEY 또는 LINEAR KEY 이외의 파티션 유형을 가진 테이블을 작성하고 사용할 수 있지만, 전적으로 사용자의 책임입니다.
NDB 테이블에 정의할 수 있는 최대 파티션 수는 클러스터의 데이터 노드 및 노드 그룹 수, 사용중인 NDB 클러스터 소프트웨어 버전 및 기타 요소에 따라 다릅니다. 자세한 정보는 NDB 및 사용자 정의 파티션을 참조하세요.
MySQL NDB Cluster 7.5.2부터 NDB 테이블의 파티션당 저장할 수 있는 최대 고정 크기 데이터 양은 128TB입니다. 이전에는 16GB였습니다.
사용자 파티션 된 NDB 테이블이 다음 두 요구 사항 중 하나 또는 둘 다를 충족시키지 못하게하는 CREATE TABLE 및 ALTER TABLE 문은 허용되지 않으며 오류와 함께 실패합니다.
1. 테이블에는 명시적 프리머리키가 있어야합니다.
2. 테이블의 파티셔닝 표현식에 나열된 모든 컬럼은 프리머리키의 일부여야 합니다.
▶︎ 예외.
빈 파티션 목록을 사용하여 (즉, PARTITION BY KEY() 또는 PARTITION BY LINEAR KEY()를 사용하여) 사용자 파티션된 NDB 테이블을 작성하는 경우 명시적 프리머리키가 필요하지 않습니다.
▶︎ 파티션된 테이블 업그레이드.
업그레이드를 수행할 때 KEY로 파티션되고 NDB 이외의 스토리지 엔진을 사용하는 테이블을 덤프하고 다시 로드해야합니다.
▶︎ 모든 파티션에 동일한 스토리지 엔진.
파티션된 테이블의 모든 파티션은 동일한 스토리지 엔진을 사용해야 하며 테이블에서 전체적으로 사용되는 동일한 스토리지 엔진이어야 합니다. 또한 테이블 레벨에서 엔진을 지정하지 않으면 파티션된 테이블을 작성하거나 변경할때 다음 중 하나를 수행해야합니다.
1. 파티션 또는 하위 파티션에 엔진을 지정하면 안됩니다.
2. 모든 파티션 또는 하위 파티션에 대한 엔진을 지정합니다.
■ 기능 관련 파티션 제한
이 글에서는 파티셔닝 표현식에 사용되는 함수와 관련된 MySQL 파티셔닝의 제한 사항에 대해 설명합니다.
다음 목록에 표시된 MySQL 함수만 파티셔닝 표현식에 허용됩니다.
+ ABS()
+ CEILING() (see CEILING() and FLOOR())
+ DATEDIFF()
+ DAY()
+ DAYOFMONTH()
+ DAYOFWEEK()
+ DAYOFYEAR()
+ EXTRACT() (see EXTRACT() function with WEEK specifier)
+ FLOOR() (see CEILING() and FLOOR())
+ HOUR()
+ MICROSECOND()
+ MINUTE()
+ MOD()
+ MONTH()
+ QUARTER()
+ SECOND()
+ TIME_TO_SEC()
+ TO_DAYS()
+ TO_SECONDS()
+ UNIX_TIMESTAMP() (with TIMESTAMP columns)
+ WEEKDAY()
+ YEAR()
+ YEARWEEK()
MySQL 5.7에서는 TO_DAYS(), TO_SECONDS(), YEAR() 및 UNIX_TIMESTAMP() 함수에 대해 파티션 제거가 지원됩니다.
▶︎ CEILING() 및 FLOOR().
이러한 각 함수는 INT유형 또는 DECIMAL과 같은 정확한 숫자 유형의 인수가 전달되는 경우에만 정수를 리턴합니다. 예를 들어 다음 CREATE TABLE 문은 여기에 표시된대로 오류와 함께 실패합니다.
mysql> CREATE TABLE t (c FLOAT) PARTITION BY LIST( FLOOR(c) )(
-> PARTITION p0 VALUES IN (1,3,5),
-> PARTITION p1 VALUES IN (2,4,6)
-> );
ERROR 1490 (HY000): The PARTITION function returns the wrong type
▶︎ WEEK지정자를 사용하는 EXTRACT() 함수.
EXTRACT(WEEK FROM col)로 사용될 때 EXTRACT() 함수에 의해 리턴되는 값은 default_week_format 시스템 변수의 값에 따라 다릅니다. 이러한 이유로 EXTRACT()는 단위를 WEEK로 지정할때 파티셔닝 함수로 허용되지 않습니다.
■ 파티셔닝과 잠금(Locking)
DML또는 DDL문을 실행할때 실제로 테이블 레벨 잠금을 실행하는 MyISAM과 같은 스토리지 엔진의 경우, 파티션된 테이블에 영향을주는 이전 버전의 MySQL (5.6.5 이하)의 명령문은 전체적으로 테이블에 잠금을 부과합니다. 즉, 명령문이 완료될 때까지 모든 파티션이 잠겼습니다. MySQL 5.7에서 파티션 잠금 프루닝은 많은 경우에 불필요한 잠금을 제거하며 파티션된 MyISAM테이블을 읽거나 업데이트하는 대부분의 명령문은 영향을받는 파티션만 잠그게합니다. 예를 들어, 파티션된 MyISAM테이블의 SELECT는 SELECT문의 WHERE 조건을 만족시키는 행을 실제로 포함하는 파티션만 잠급니다.
InnoDB와 같은 스토리지 엔진을 사용하여 파티션 레벨 테이블에 영향을 미치는 명령문의 경우, 행 레벨 잠금을 사용하고 파티션 프루닝전에 잠금을 실제로 수행하거나 수행해야하는 것은 문제가되지 않습니다.
다음 몇 문단에서는 테이블 레벨 잠금을 사용하는 스토리지 엔진을 사용하는 테이블에서 다양한 MySQL 명령문에 대한 파티션 잠금 프루닝의 영향에 대해 설명합니다.
▶︎ DML 문장에 미치는 영향
SELECT문 (유니온 또는 조인문)은 실제로 읽어야하는 파티션만 잠급니다. 이것은 SELECT ... PARTITION에도 적용됩니다.
UPDATE는 파티션 컬럼이 업데이트되지 않은 테이블에 대해서만 잠금을 제거합니다.
REPLACE 및 INSERT는 행을 삽입하거나 교체할 파티션만 잠급니다. 그러나 파티션 컬럼에 대해 AUTO_INCREMENT값이 생성되면 모든 파티션이 잠깁니다.
INSERT ... ON DUPLICATE KEY UPDATE 파티션 열이 업데이트되지 않는 한 UPDATE가 제거됩니다.
INSERT ... SELECT는 대상 테이블의 모든 파티션이 잠겨 있어도 소스 테이블에서 읽어야하는 파티션만 잠급니다.
파티션된 테이블에서 LOAD DATA 문에 의해 수행된 잠금은 제거 할 수 없습니다.
파티션된 테이블의 파티셔닝 컬럼을 사용하는 BEFORE INSERT 또는 BEFORE UPDATE 트리거가 존재하면 트리거가 값을 변경할 수 있으므로 이 테이블을 업데이트하는 INSERT 및 UPDATE 문에 대한 잠금을 제거할 수 없습니다. 컬럼은 BEFORE INSERT 트리거가 행을 삽입하기 전에 행의 파티션 컬럼을 변경하여 행을 다른 파티션으로 강제 실행하기 때문에 INSERT 또는 REPLACE로 설정된 잠금을 제거 할 수 없음을 의미합니다. 파티션 컬럼에서 BEFORE UPDATE 트리거는 UPDATE 또는 INSERT ... ON DUPLICATE KEY UPDATE에 의해 수행된 잠금을 제거 할 수 없음을 의미합니다.
▶︎ 영향을받는 DDL 문
CREATE VIEW는 잠금을 유발하지 않습니다.
ALTER TABLE ... EXCHANGE PARTITION 잠금을 제거합니다. 교환된 테이블과 교환된 파티션만 잠깁니다.
ALTER TABLE ... TRUNCATE PARTITION은 잠금을 제거합니다. 삭제될 파티션만 잠깁니다.
또한 ALTER TABLE 문은 테이블 레벨에서 메타 데이터 잠금을 사용합니다.
▶︎ 다른 명령문
LOCK TABLES는 파티션 잠금을 제거 할 수 없습니다.
CALL stored_procedure (expr)는 잠금 제거를 지원하지만 expr평가는 지원하지 않습니다.
DO 및 SET 문은 파티션잠금 제거를 지원하지 않습니다.
□ 테이블 파티셔닝 링크
[MySQL] Table Partitioning - 소개
https://myinfrabox.tistory.com/93
[MySQL] Table Partitioning - 소개
■ 파티셔닝 소개 비 네이티브 파티셔닝이 있는 테이블을 사용하면 ER_WARN_DEPRECATED_SYNTAX경고가 발생합니다. MySQL 5.7.17에서 5.7.20까지, 서버는 시작시 비 기본 파티션을 사용하는 테이블을 식별하��
myinfrabox.tistory.com
[MySQL] Table Partitioning-파티셔닝 타입
https://myinfrabox.tistory.com/94
[MySQL] Table Partitioning-파티셔닝 타입
■ 파티셔닝 타입 이 섹션에서는 MySQL 5.7에서 사용할 수있는 파티셔닝 유형에 대해 설명합니다. 여기에 나열된 유형이 포함됩니다. + RANGE 파티셔닝. 이 유형의 파티셔닝은 주어진 범위 내에 속하
myinfrabox.tistory.com
[MySQL] Table Partitioning - 파티셔닝 관리
https://myinfrabox.tistory.com/98
[MySQL] Table Partitioning - 파티셔닝 관리
■ 파티션 관리 MySQL 5.7은 분할된 테이블을 수정하는 여러 가지 방법을 제공합니다. 기존 파티션을 추가, 삭제, 재정의, 병합 또는 분할할 수 있습니다. 이러한 모든 조치는 ALTER TABLE 문에 대한 파
myinfrabox.tistory.com
[MySQL] Table Partitioning - 파티션 프루닝(파티션 정리)
https://myinfrabox.tistory.com/100
[MySQL] Table Partitioning - 파티션 프루닝(파티션 정리)
이 섹션에서는 파티션 정리(Partition 라고하는 최적화에 대해 설명합니다. 파티션 정리의 기본 개념은 비교적 간단하며“일치하는 값이 없는 파티션은 스캔하지 않습니다”라고 설명 할 수 있습�
myinfrabox.tistory.com
[MySQL] Table Partitioning-파티셔닝 선택
https://myinfrabox.tistory.com/102
[MySQL] Table Partitioning-파티셔닝 선택
■ 파티션 선택 MySQL 5.7은 명령문을 실행할때 주어진 WHERE 조건과 일치하는 행을 검사해야하는 파티션 및 하위 파티션의 명시적 선택을 지원합니다. 파티션 선택은 특정 파티션 만 일치하는지 검
myinfrabox.tistory.com
'Databases > MySQL' 카테고리의 다른 글
| [MySQL] Table Partitioning - MAXVALUE 파티션에 따른 테이블 재구성시 유의사항 (0) | 2020.09.07 |
|---|---|
| [MySQL] START TRANSACTION, COMMIT, 그리고 ROLLBACK문 (0) | 2020.09.04 |
| [MySQL] Table Partitioning-파티셔닝 선택 (0) | 2020.09.02 |
| [MySQL] Table Partitioning - 파티션 프루닝(파티션 정리) (0) | 2020.08.30 |
| [MySQL] Table Partitioning - 파티셔닝 관리 (0) | 2020.08.22 |