MySQL 5.6 한글메뉴얼

19.2.3.1 RANGE COLUMNS 파티셔닝

RANGE COLUMNS 파티셔닝은 RANGE 파티셔닝과 비슷하지만 여러 컬럼 값을 기준으로 범위를 사용하여 파티션을 정의 할 수 있습니다. 또한 정수 이외의 형태의 컬럼을 사용하여 범위를 정의 할 수 있습니다.

RANGE COLUMNS 파티셔닝은 다음의 점에서 RANGE 파티셔닝과 크게 다릅니다.

RANGE COLUMNS 는 식을 허용하지 않습니다 (컬럼 이름 만).
RANGE COLUMNS 는 하나 이상의 컬럼의 목록을 받아들입니다.

RANGE COLUMNS 파티션은 스칼라 값의 비교가 아니라 튜플 (컬럼 값 목록)의 비교에 근거합니다. RANGE COLUMNS 파티션에서 행의 배치도 튜플의 비교에 근거합니다. 이 내용은이 섹션 뒷부분합니다.
RANGE COLUMNS 파티셔닝 컬럼은 정수 컬럼에 제한되지 않습니다. 문자열, DATE 및 DATETIME 컬럼도 파티셔닝 컬럼으로 사용할 수 있습니다. (자세한 내용은 섹션 19.2.3 "COLUMNS 파티셔닝" 를 참조하십시오).

RANGE COLUMNS 로 파티션 된 테이블을 작성하기위한 기본 구문을 보여줍니다.

CREATE TABLE table_name
PARTITIONED BY RANGE COLUMNS(column_list) (
    PARTITION partition_name VALUES LESS THAN (value_list)[,
    PARTITION partition_name VALUES LESS THAN (value_list)][,
    ...]
) 

column_list:
    column_name[, column_name][, ...]

value_list:
    value[, value][, ...]

참고

분할 된 테이블을 만들 때 사용할 수있는 CREATE TABLE 옵션을 모두 보여주고있는 것은 아닙니다. 자세한 내용은 섹션 13.1.17 "CREATE TABLE 구문" 을 참조하십시오.

위의 구문에서 column_list 는 하나 이상의 컬럼 목록 (파티셔닝 컬럼리스트로 불리기도합니다) value_list 값 목록 (즉, 파티션 정의 값 목록입니다)입니다. value_list 각 파티션 정의에 지정해야 각 value_list 에는 column_list 의 컬럼과 같은 수의 값이 필요합니다. 일반적으로 COLUMNS 절에 N 개의 컬럼을 사용하는 경우 각 VALUES LESS THAN 절에 N 개의 값의 목록을 지정해야합니다.

파티셔닝 컬럼 목록 및 각 파티션을 정의하는 값 목록의 요소는 같은 순서로 지정해야합니다. 또한 값 목록의 각 요소는 열 목록에서 해당 요소와 같은 데이터 형식이어야합니다. 그러나 파티셔닝 컬럼 목록 및 값 목록에서 컬럼 이름의 순서는 CREATE TABLE 문 주요부의 테이블 컬럼 정의의 순서와 동일 할 필요는 없습니다. RANGE 로 파티션 된 테이블과 마찬가지로 MAXVALUE 를 사용하여 지정된 컬럼에 삽입되는 정당한 값보다 확실히 큰 값을 나타낼 수 있습니다. 이러한 점을 설명하는 데 도움이 CREATE TABLE 문 예를 보여줍니다.

mysql> CREATE TABLE rcx (
    ->     a INT,
    ->     b INT,
    ->     c CHAR(3),
    ->     d INT
    -> )
    -> PARTITION BY RANGE COLUMNS(a,d,c) (
    ->     PARTITION p0 VALUES LESS THAN (5,10,'ggg'),
    ->     PARTITION p1 VALUES LESS THAN (10,20,'mmmm'),
    ->     PARTITION p2 VALUES LESS THAN (15,30,'sss'),
    ->     PARTITION p3 VALUES LESS THAN (MAXVALUE,MAXVALUE,MAXVALUE)
    -> );
Query OK, 0 rows affected (0.15 sec)

테이블 rcx 는 컬럼 a , b , c 및 d 가 포함되어 있습니다. COLUMNS 절에 지정된 파티셔닝 컬럼리스트에는 이러한 컬럼 중 3 개가 a , d 및 c 의 순서로 사용되고 있습니다. 파티션을 정의하는 데 사용되는 각 값 목록에는 동일한 순서로 3 개의 값이 포함됩니다. 즉, 각 값 목록 튜플 형식은 컬럼 a , d 및 c 가 (이 순서로) 사용하는 데이터 형에 해당하는 ( INT , INT , CHAR(3) )입니다.

파티션에 행이 어떻게 배치 될지은 삽입 된 행의 튜플 ( COLUMNS 절의 컬럼 목록에 일치)와 VALUES LESS THAN 절에 사용되는 튜플 (테이블의 파티션을 정의)를 비교 에 의해 결정됩니다. 스칼라 값이 아니라 튜플 (즉, 값 목록 또는 세트)을 비교하기 위해 RANGE COLUMNS 파티션에서 사용되는 VALUES LESS THAN 의 의미는 단순한 RANGE 파티션의 경우와는 약간 다릅니다. RANGE 파티셔닝은 VALUES LESS THAN 의 한계와 동일한 식 값을 생성하는 행은 해당 파티션에 배치되지 않습니다. 그러나 RANGE COLUMNS 파티셔닝을 사용하면 파티셔닝 컬럼리스트의 첫 번째 요소가 VALUES LESS THAN 값 목록의 첫 번째 요소와 값이 동일한 행이 해당 파티션에 배치 될 수 있습니다.

다음 문에 의해 생성되는 파티션 된 RANGE 테이블을 검토합니다.

CREATE TABLE r1 (
    a INT,
    b INT
)
PARTITION BY RANGE (a)  (
    PARTITION p0 VALUES LESS THAN (5),
    PARTITION p1 VALUES LESS THAN (MAXVALUE)
);

각 행의 a 컬럼 값이 5 인 3 개의 행이 테이블에 삽입하는 경우, 각 행의 a 컬럼 값이 5 이상이기 때문에 3 행이 모든 파티션 p1 에 저장됩니다. 이것은 INFORMATION_SCHEMA.PARTITIONS 테이블에 대해 적절한 쿼리를 실행하여 확인할 수 있습니다.

mysql> INSERT INTO r1 VALUES (5,10), (5,11), (5,12);
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> SELECT PARTITION_NAME,TABLE_ROWS
    ->     FROM INFORMATION_SCHEMA.PARTITIONS
    ->     WHERE TABLE_NAME = 'r1';
+----------------+------------+
| PARTITION_NAME | TABLE_ROWS |
+----------------+------------+
| p0             |          0 |
| p1             |          3 |
+----------------+------------+
2 rows in set (0.00 sec)

여기에서 다음과 같이 생성 된 컬럼 a 와 b 가 모두 COLUMNS 절에서 참조되는 RANGE COLUMNS 파티셔닝을 사용하는 동일한 테이블 rc1 을 검토합니다.

CREATE TABLE rc1 (
    a INT, 
    b INT
) 
PARTITION BY RANGE COLUMNS(a, b) (
    PARTITION p0 VALUES LESS THAN (5, 12),
    PARTITION p3 VALUES LESS THAN (MAXVALUE, MAXVALUE)
);

r1 에 삽입 한 것과 똑같은 줄을 rc1 에 삽입하면 행의 배분은 상당히 다릅니다.

mysql> INSERT INTO rc1 VALUES (5,10), (5,11), (5,12);
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> SELECT PARTITION_NAME,TABLE_ROWS
    ->     FROM INFORMATION_SCHEMA.PARTITIONS
    ->     WHERE TABLE_NAME = 'rc1';
+--------------+----------------+------------+
| TABLE_SCHEMA | PARTITION_NAME | TABLE_ROWS |
+--------------+----------------+------------+
| p            | p0             |          2 |
| p            | p1             |          1 |
+--------------+----------------+------------+
2 rows in set (0.00 sec)

이것은 스칼라 값이 아닌 행을 비교하고 있기 때문입니다. 삽입 된 행 값과 테이블 rc1 파티션 p0 을 정의하는 데 사용 된 VALUES THAN LESS THAN 절 행 제한 값과를 다음과 같이 비교할 수 있습니다.

mysql> SELECT (5,10) < (5,12), (5,11) < (5,12), (5,12) < (5,12);
+-----------------+-----------------+-----------------+
| (5,10) < (5,12) | (5,11) < (5,12) | (5,12) < (5,12) |
+-----------------+-----------------+-----------------+
|               1 |               1 |               0 |
+-----------------+-----------------+-----------------+
1 row in set (0.00 sec)

2 개의 튜플 (5,10) 및 (5,11) 는 (5,12) 보다 작다고 평가되기 때문에 파티션 p0 에 저장되어 있습니다. 5는 5 이상 12 12 이상이기 때문에 (5,12) 는 (5,12) 이상으로 간주 파티션 p1 에 저장되어 있습니다.

이전 예제의 SELECT 문은 다음과 같이 명시적인 행 생성자를 사용하여 설명 할 수있었습니다.

SELECT ROW(5,10) < ROW(5,12), ROW(5,11) < ROW(5,12), ROW(5,12) < ROW(5,12);

MySQL에서 행 생성자를 사용하는 방법에 대한 자세한 내용은 섹션 13.2.10.5 "행 서브 쿼리" 를 참조하십시오.

단일 파티션 컬럼만을 사용하여 RANGE COLUMNS 로 파티션 된 테이블의 경우 파티션에 행의 저장은 RANGE 에 의해 분할 된 동일한 테이블의 경우와 동일합니다. 다음의 CREATE TABLE 문은 하나의 파티셔닝 컬럼을 사용하여 RANGE COLUMNS 로 파티션 된 테이블이 작성됩니다.

CREATE TABLE rx (
    a INT,
    b INT
)
PARTITION BY RANGE COLUMNS (a)  (
    PARTITION p0 VALUES LESS THAN (5),
    PARTITION p1 VALUES LESS THAN (MAXVALUE)
);

이 테이블에 행 (5,10) ( (5,11) 및 (5,12) 를 삽입하는 경우, 그 배치는 이전에 작성하여 인구가 테이블 r 의 경우와 동일합니다.

mysql> INSERT INTO rx VALUES (5,10), (5,11), (5,12);
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> SELECT PARTITION_NAME,TABLE_ROWS
    ->     FROM INFORMATION_SCHEMA.PARTITIONS
    ->     WHERE TABLE_NAME = 'rx';
+--------------+----------------+------------+
| TABLE_SCHEMA | PARTITION_NAME | TABLE_ROWS |
+--------------+----------------+------------+
| p            | p0             |          0 |
| p            | p1             |          3 |
+--------------+----------------+------------+
2 rows in set (0.00 sec)

하나 이상의 컬럼 제한이 연속 파티션 정의에서 반복되는 RANGE COLUMNS 로 파티션 된 테이블을 만들 수 있습니다. 이렇게 파티션을 정의하는 데 사용되는 컬럼 값의 튜플이 엄격하게 점차 증가해야합니다. 예를 들어, 다음 각 CREATE TABLE 문은 유효합니다.

CREATE TABLE rc2 (
    a INT,
    b INT
)
PARTITION BY RANGE COLUMNS(a,b) (
    PARTITION p0 VALUES LESS THAN (0,10),
    PARTITION p1 VALUES LESS THAN (10,20),
    PARTITION p2 VALUES LESS THAN (10,30),
    PARTITION p3 VALUES LESS THAN (MAXVALUE,MAXVALUE)
 );
 
CREATE TABLE rc3 (
    a INT,
    b INT
)
PARTITION BY RANGE COLUMNS(a,b) (
    PARTITION p0 VALUES LESS THAN (0,10),
    PARTITION p1 VALUES LESS THAN (10,20),
    PARTITION p2 VALUES LESS THAN (10,30),
    PARTITION p3 VALUES LESS THAN (10,35),
    PARTITION p4 VALUES LESS THAN (20,40),
    PARTITION p5 VALUES LESS THAN (MAXVALUE,MAXVALUE)
 );

다음 명령문은 성공하지만 열 b 제한이 파티션 p0 25 및 파티션 p1 20 컬럼 c 제한이 파티션 p1 100 및 파티션 p2 에서 50이기 때문에 언뜻보기에는 그렇지 않은 보일지도 모릅니다.

CREATE TABLE rc4 (
    a INT,
    b INT,
    c INT
)
PARTITION BY RANGE COLUMNS(a,b,c) (
    PARTITION p0 VALUES LESS THAN (0,25,50),
    PARTITION p1 VALUES LESS THAN (10,20,100),
    PARTITION p2 VALUES LESS THAN (10,30,50)
    PARTITION p3 VALUES LESS THAN (MAXVALUE,MAXVALUE,MAXVALUE) 
 );

RANGE COLUMNS 로 파티션 테이블을 설계 할 때 다음과 같이 mysql 클라이언트를 사용하여 원하는 튜플을 비교함으로써 언제든지 연속 파티션 정의를 테스트 할 수 있습니다.

mysql> SELECT (0,25,50) < (10,20,100), (10,20,100) < (10,30,50);
+-------------------------+--------------------------+
| (0,25,50) < (10,20,100) | (10,20,100) < (10,30,50) |
+-------------------------+--------------------------+
|                       1 |                        1 |
+-------------------------+--------------------------+
1 row in set (0.00 sec)

CREATE TABLE 문에 포함 된 파티션 정의가 엄격하게 점차 증가하지 않는 순서 인 경우 다음의 예와 같이 오류로 실패합니다.

mysql> CREATE TABLE rcf (
    ->     a INT,
    ->     b INT,
    ->     c INT
    -> )
    -> PARTITION BY RANGE COLUMNS(a,b,c) (
    ->     PARTITION p0 VALUES LESS THAN (0,25,50),
    ->     PARTITION p1 VALUES LESS THAN (20,20,100),
    ->     PARTITION p2 VALUES LESS THAN (10,30,50),
    ->     PARTITION p3 VALUES LESS THAN (MAXVALUE,MAXVALUE,MAXVALUE)
    ->  );
ERROR 1493 (HY000): VALUES LESS THAN value must be strictly increasing for each partition

그런 오류가 발생하면 그 컬럼리스트의 "작음"비교를 작성하여 잘못된 파티션 정의를 추정 할 수 있습니다. 이 경우 문제는 파티션 p2 의 정의에 있습니다. 다음과 같이 그것을 정의하는 데 사용되는 튜플이 파티션 p3 를 정의하는 데 사용되는 튜플 이상이기 때문입니다.

mysql> SELECT (0,25,50) < (20,20,100), (20,20,100) < (10,30,50);
+-------------------------+--------------------------+
| (0,25,50) < (20,20,100) | (20,20,100) < (10,30,50) |
+-------------------------+--------------------------+
|                       1 |                        0 |
+-------------------------+--------------------------+
1 row in set (0.00 sec)

RANGE COLUMNS 를 사용하면 여러 VALUES LESS THAN 절에 같은 컬럼에 MAXVALUE 를 지정 할 수 있습니다. 다만, 그 이외의 경우는 연속 파티션 정의에서 개별 컬럼의 한계는 점차 증가해야하며, MAXVALUE 를 모든 컬럼 값의 상한으로 사용하는 파티션은 하나만 정의해야하며 이 파티션 정의는 PARTITION ... VALUES LESS THAN 절 목록의 마지막에 지정해야합니다. 또한 여러 파티션 정의의 첫 번째 컬럼의 한계로 MAXVALUE 를 사용할 수 없습니다.

전술 한 바와 같이, RANGE COLUMNS 파티셔닝은 정수가 아닌 컬럼을 파티셔닝 컬럼으로 사용할 수 있습니다 (이 완전한 목록은 섹션 19.2.3 "COLUMNS 파티셔닝" 를 참조하십시오) . 다음 문을 사용하여 생성 된 employees 라는 테이블 (분할되지 않음)를 검토합니다.

CREATE TABLE employees (
    id INT NOT NULL,
    fname VARCHAR(30),
    lname VARCHAR(30),
    hired DATE NOT NULL DEFAULT '1970-01-01',
    separated DATE NOT NULL DEFAULT '9999-12-31',
    job_code INT NOT NULL,
    store_id INT NOT NULL
);

RANGE COLUMNS 파티셔닝을 사용하여 다음과 같이 직원의 성과에 따라 각 행을 4 개의 파티션 중 하나에 저장이 테이블의 버전을 만들 수 있습니다.

CREATE TABLE employees_by_lname (
    id INT NOT NULL,
    fname VARCHAR(30),
    lname VARCHAR(30),
    hired DATE NOT NULL DEFAULT '1970-01-01',
    separated DATE NOT NULL DEFAULT '9999-12-31',
    job_code INT NOT NULL,
    store_id INT NOT NULL
)
PARTITION BY RANGE COLUMNS (lname)  (
    PARTITION p0 VALUES LESS THAN ('g'),
    PARTITION p1 VALUES LESS THAN ('m'),
    PARTITION p2 VALUES LESS THAN ('t'),
    PARTITION p3 VALUES LESS THAN (MAXVALUE)
);

또는 다음 ALTER TABLE 문을 실행하면 이전에 작성한 employees 테이블이 스키마를 사용하여 분할 할 수 있습니다.

ALTER TABLE employees PARTITION BY RANGE COLUMNS (lname)  (
    PARTITION p0 VALUES LESS THAN ('g'),
    PARTITION p1 VALUES LESS THAN ('m'),
    PARTITION p2 VALUES LESS THAN ('t'),
    PARTITION p3 VALUES LESS THAN (MAXVALUE)
);

참고

문자 집합 및 정렬 순서가 다르면 정렬 순서가 다르기 때문에 문자열 컬럼을 파티셔닝 컬럼으로 사용할 때 사용하는 문자 집합 및 정렬 순서가 지정된 행이 RANGE COLUMNS 의해 분할되는 테이블의 어떤 파티션에 저장되는지에 영향을 줄 수 있습니다. 또한 이러한 테이블이 작성된 후 지정된 데이터베이스, 테이블 또는 컬럼의 문자 세트 또는 데이터 정렬을 변경하면 행이 어떻게 배분되는지가 변화 할 수 있습니다. 예를 들어, 대 / 소문자를 구분 데이터 정렬을 사용하는 경우에는 'and' 는 'Andersen' 의 앞에 놓여 있지만, 대 / 소문자를 구분하지 않는 데이터 정렬을 사용하는 경우에는 그 반대가 true 합니다.

MySQL이 문자 집합 및 정렬 순서를 어떻게 처리할지에 대해서는 섹션 10.1 "문자 집합 지원" 을 참조하십시오.

마찬가지로, employees 테이블을 각 행이 몇 십 년간 (그 사이에 해당 직원이 고용 된) 기반의 파티션 중 하나에 포함되도록 다음과 같은 ALTER TABLE 문을 사용하여 분할 수 있습니다.

ALTER TABLE employees PARTITION BY RANGE COLUMNS (hired)  (
    PARTITION p0 VALUES LESS THAN ('1970-01-01'),
    PARTITION p1 VALUES LESS THAN ('1980-01-01'),
    PARTITION p2 VALUES LESS THAN ('1990-01-01'),
    PARTITION p3 VALUES LESS THAN ('2000-01-01'),
    PARTITION p4 VALUES LESS THAN ('2010-01-01'),
    PARTITION p5 VALUES LESS THAN (MAXVALUE)
);

PARTITION BY RANGE COLUMNS 구문에 대한 자세한 내용은 섹션 13.1.17 "CREATE TABLE 구문" 을 참조하십시오.