26.2.1 RANGE Partitioning

26.2.1 RANGE Partitioning

range로 파티셔닝된 테이블은, 파티셔닝 표현식 값이 주어진 range 안에 있는 row들을 각 파티션이 포함하도록 분할됩니다. range들은 서로 인접해야 하지만 겹치지 않아야 하며, VALUES LESS THAN 연산자를 사용하여 정의됩니다. 다음 몇 개의 예제에서는, 1부터 20까지 번호가 매겨진 20개의 비디오 매장 체인의 인사 기록을 보관하기 위해 다음과 같은 테이블을 생성한다고 가정합니다:

1CREATE TABLE employees (
2    id INT NOT NULL,
3    fname VARCHAR(30),
4    lname VARCHAR(30),
5    hired DATE NOT NULL DEFAULT '1970-01-01',
6    separated DATE NOT NULL DEFAULT '9999-12-31',
7    job_code INT NOT NULL,
8    store_id INT NOT NULL
9);

참고

여기서 사용되는 employees 테이블에는 primary key나 unique key가 없습니다. 현재 설명의 목적을 위해 예제는 보이는 그대로 동작하지만, 실제로는 테이블에 primary key, unique key 또는 이 둘 모두가 존재할 가능성이 매우 크며, 허용 가능한 파티셔닝 column 선택은 (존재하는 경우) 이러한 key에 사용되는 column에 따라 달라진다는 점을 유념해야 합니다. 이러한 이슈들에 대한 논의는 Section 26.6.1, “Partitioning Keys, Primary Keys, and Unique Keys”를 참조하십시오.

이 테이블은 필요에 따라 여러 가지 방식으로 range 파티셔닝 할 수 있습니다. 한 가지 방법은 store_id column을 사용하는 것입니다. 예를 들어, 다음과 같이 PARTITION BY RANGE 절을 추가하여 테이블을 4개로 파티셔닝 하도록 결정할 수 있습니다:

1CREATE TABLE employees (
2    id INT NOT NULL,
3    fname VARCHAR(30),
4    lname VARCHAR(30),
5    hired DATE NOT NULL DEFAULT '1970-01-01',
6    separated DATE NOT NULL DEFAULT '9999-12-31',
7    job_code INT NOT NULL,
8    store_id INT NOT NULL
9)
10PARTITION BY RANGE (store_id) (
11    PARTITION p0 VALUES LESS THAN (6),
12    PARTITION p1 VALUES LESS THAN (11),
13    PARTITION p2 VALUES LESS THAN (16),
14    PARTITION p3 VALUES LESS THAN (21)
15);

이 파티셔닝 스킴에서, 매장 1부터 5까지에서 근무하는 직원에 해당하는 모든 row는 파티션 p0에 저장되고, 매장 6부터 10까지에서 근무하는 직원은 파티션 p1에 저장되며, 그 이후도 같은 방식입니다. 각 파티션은 가장 낮은 값에서 가장 높은 값까지 순서대로 정의됩니다. 이는 PARTITION BY RANGE 구문의 요구 사항입니다. 이 점에서 C 또는 Java의 일련의 if ... elseif ... 문과 유사하다고 생각할 수 있습니다.

(72, 'Mitchell', 'Wilson', '1998-06-25', DEFAULT, 7, 13) 데이터를 포함하는 새 row가 파티션 p2에 삽입된다는 것을 판단하는 것은 쉽지만, 체인에 21번째 매장이 추가되면 어떻게 될까요? 이 스킴에서는 store_id가 20보다 큰 row를 처리하는 규칙이 없으므로, 서버가 이를 어디에 저장해야 할지 알지 못해 오류가 발생합니다. 이를 방지하려면, CREATE TABLE 문에서 가장 큰 명시적 값보다 큰 모든 값을 처리하는 “catchall” VALUES LESS THAN 절을 사용하면 됩니다:

1CREATE TABLE employees (
2    id INT NOT NULL,
3    fname VARCHAR(30),
4    lname VARCHAR(30),
5    hired DATE NOT NULL DEFAULT '1970-01-01',
6    separated DATE NOT NULL DEFAULT '9999-12-31',
7    job_code INT NOT NULL,
8    store_id INT NOT NULL
9)
10PARTITION BY RANGE (store_id) (
11    PARTITION p0 VALUES LESS THAN (6),
12    PARTITION p1 VALUES LESS THAN (11),
13    PARTITION p2 VALUES LESS THAN (16),
14    PARTITION p3 VALUES LESS THAN MAXVALUE
15);

(이 장의 다른 예제와 마찬가지로, 기본 스토리지 엔진이 InnoDB라고 가정합니다.)

일치하는 값이 발견되지 않을 때 오류를 피하는 또 다른 방법은 INSERT 문에 IGNORE 키워드를 사용하는 것입니다. 예제는 Section 26.2.2, “LIST Partitioning”을 참조하십시오.

MAXVALUE는 항상 가능한 가장 큰 정수 값보다 큰 정수 값을 나타냅니다(수학적 용어로는 least upper bound 역할을 합니다). 이제 store_id column 값이 16(정의된 가장 큰 값) 이상인 모든 row는 파티션 p3에 저장됩니다. 나중에—매장 수가 25, 30 또는 그 이상으로 증가했을 때—ALTER TABLE 문을 사용하여 매장 21-25, 26-30 등의 새 파티션을 추가할 수 있습니다(이를 수행하는 방법에 대한 자세한 내용은 Section 26.3, “Partition Management”를 참조하십시오).

유사한 방식으로, employee job code—즉, job_code column 값의 range—를 기준으로 테이블을 파티셔닝 할 수도 있습니다. 예를 들어, 두 자리 job code는 일반(매장 내) 근로자에게, 세 자리 코드는 사무 및 지원 인력에게, 네 자리 코드는 관리직에게 사용된다고 가정하면, 다음 문을 사용하여 파티셔닝 된 테이블을 생성할 수 있습니다:

1CREATE TABLE employees (
2    id INT NOT NULL,
3    fname VARCHAR(30),
4    lname VARCHAR(30),
5    hired DATE NOT NULL DEFAULT '1970-01-01',
6    separated DATE NOT NULL DEFAULT '9999-12-31',
7    job_code INT NOT NULL,
8    store_id INT NOT NULL
9)
10PARTITION BY RANGE (job_code) (
11    PARTITION p0 VALUES LESS THAN (100),
12    PARTITION p1 VALUES LESS THAN (1000),
13    PARTITION p2 VALUES LESS THAN (10000)
14);

이 경우, 매장 내 근로자와 관련된 모든 row는 파티션 p0에 저장되고, 사무 및 지원 인력과 관련된 row는 p1에, 관리자와 관련된 row는 파티션 p2에 저장됩니다.

VALUES LESS THAN 절에서 표현식을 사용하는 것도 가능합니다. 그러나 MySQL은 이 표현식의 반환 값을 LESS THAN (<) 비교의 일부로 평가할 수 있어야 합니다.

store 번호에 따라 테이블 데이터를 분할하는 대신, 두 개의 DATE column 중 하나를 기반으로 하는 표현식을 사용할 수 있습니다. 예를 들어, 각 employee가 회사를 떠난 연도—즉, YEAR(separated)의 값—를 기준으로 파티셔닝 하려고 한다고 가정해 봅시다. 이러한 파티셔닝 스킴을 구현하는 CREATE TABLE 문 예제는 다음과 같습니다:

1CREATE TABLE employees (
2    id INT NOT NULL,
3    fname VARCHAR(30),
4    lname VARCHAR(30),
5    hired DATE NOT NULL DEFAULT '1970-01-01',
6    separated DATE NOT NULL DEFAULT '9999-12-31',
7    job_code INT,
8    store_id INT
9)
10PARTITION BY RANGE ( YEAR(separated) ) (
11    PARTITION p0 VALUES LESS THAN (1991),
12    PARTITION p1 VALUES LESS THAN (1996),
13    PARTITION p2 VALUES LESS THAN (2001),
14    PARTITION p3 VALUES LESS THAN MAXVALUE
15);

이 스킴에서, 1991년 이전에 회사를 떠난 모든 employee의 row는 파티션 p0에 저장되고, 1991년부터 1995년까지 회사를 떠난 employee는 p1에, 1996년부터 2000년까지 회사를 떠난 employee는 p2에, 2000년 이후에 회사를 떠난 근로자는 p3에 저장됩니다.

또한, UNIX_TIMESTAMP() 함수를 사용하여 TIMESTAMP column 값에 기반해 RANGE로 테이블을 파티셔닝 하는 것도 가능합니다. 예시는 다음과 같습니다:

1CREATE TABLE quarterly_report_status (
2    report_id INT NOT NULL,
3    report_status VARCHAR(20) NOT NULL,
4    report_updated TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
5)
6PARTITION BY RANGE ( UNIX_TIMESTAMP(report_updated) ) (
7    PARTITION p0 VALUES LESS THAN ( UNIX_TIMESTAMP('2008-01-01 00:00:00') ),
8    PARTITION p1 VALUES LESS THAN ( UNIX_TIMESTAMP('2008-04-01 00:00:00') ),
9    PARTITION p2 VALUES LESS THAN ( UNIX_TIMESTAMP('2008-07-01 00:00:00') ),
10    PARTITION p3 VALUES LESS THAN ( UNIX_TIMESTAMP('2008-10-01 00:00:00') ),
11    PARTITION p4 VALUES LESS THAN ( UNIX_TIMESTAMP('2009-01-01 00:00:00') ),
12    PARTITION p5 VALUES LESS THAN ( UNIX_TIMESTAMP('2009-04-01 00:00:00') ),
13    PARTITION p6 VALUES LESS THAN ( UNIX_TIMESTAMP('2009-07-01 00:00:00') ),
14    PARTITION p7 VALUES LESS THAN ( UNIX_TIMESTAMP('2009-10-01 00:00:00') ),
15    PARTITION p8 VALUES LESS THAN ( UNIX_TIMESTAMP('2010-01-01 00:00:00') ),
16    PARTITION p9 VALUES LESS THAN (MAXVALUE)
17);

TIMESTAMP 값을 포함하는 다른 표현식은 허용되지 않습니다(버그 #42849 참조).

range 파티셔닝은 다음 조건 중 하나 이상이 참일 때 특히 유용합니다:

• “오래된” 데이터를 삭제하려는(또는 그럴 필요가 있는) 경우. 앞에서 제시한 employees 테이블에 대한 파티셔닝 스킴을 사용하는 경우, ALTER TABLE employees DROP PARTITION p0;를 사용하여 1991년 이전에 회사를 떠난 employee와 관련된 모든 row를 간단히 삭제할 수 있습니다. (자세한 내용은 Section 15.1.11, “ALTER TABLE Statement” 및 Section 26.3, “Partition Management”를 참조하십시오.) row가 매우 많은 테이블의 경우, 이는 DELETE FROM employees WHERE YEAR(separated) <= 1990;와 같은 DELETE 쿼리를 실행하는 것보다 훨씬 더 효율적일 수 있습니다.

• date 또는 time 값이 포함된 column, 또는 다른 시퀀스에서 생성된 값이 포함된 column을 사용하려는 경우.

• 테이블을 파티셔닝 하는 데 사용된 column에 직접적으로 의존하는 쿼리를 자주 실행하는 경우. 예를 들어, EXPLAIN SELECT COUNT(*) FROM employees WHERE separated BETWEEN '2000-01-01' AND '2000-12-31' GROUP BY store_id;와 같은 쿼리를 실행할 때, MySQL은 나머지 파티션에는 WHERE 절을 만족하는 레코드가 포함될 수 없다는 것을 알기 때문에 파티션 p2만 스캔하면 된다는 것을 빠르게 판단할 수 있습니다. 이것이 어떻게 수행되는지에 대한 자세한 내용은 Section 26.4, “Partition Pruning”을 참조하십시오.

이러한 유형의 파티셔닝에 대한 변형이 RANGE COLUMNS 파티셔닝입니다. RANGE COLUMNS로 파티셔닝 하면, row를 파티션에 배치하는 데 적용되는 파티셔닝 range를 정의하고 파티션 프루닝을 수행할 때 특정 파티션을 포함하거나 제외하는 데 모두 사용될 수 있는 여러 column을 사용할 수 있습니다. 보다 자세한 정보는 Section 26.2.3.1, “RANGE COLUMNS partitioning”을 참조하십시오.

time interval에 기반한 partitioning scheme.

MySQL 9.5에서 time의 range 또는 interval에 기반한 파티셔닝 스킴을 구현하려면, 두 가지 옵션이 있습니다:

1. 테이블을 RANGE로 파티셔닝 하고, 파티셔닝 표현식으로는 DATE, TIME 또는 DATETIME column에서 동작하고 정수 값을 반환하는 함수을 사용합니다. 예시는 다음과 같습니다:

1CREATE TABLE members (
2       firstname VARCHAR(25) NOT NULL,
3       lastname VARCHAR(25) NOT NULL,
4       username VARCHAR(16) NOT NULL,
5       email VARCHAR(35),
6       joined DATE NOT NULL
7)
8PARTITION BY RANGE( YEAR(joined) ) (
9       PARTITION p0 VALUES LESS THAN (1960),
10       PARTITION p1 VALUES LESS THAN (1970),
11       PARTITION p2 VALUES LESS THAN (1980),
12       PARTITION p3 VALUES LESS THAN (1990),
13       PARTITION p4 VALUES LESS THAN MAXVALUE
14);

MySQL 9.5에서는 또한 UNIX_TIMESTAMP() 함수를 사용하여 TIMESTAMP column 값에 기반해 RANGE로 테이블을 파티셔닝 하는 것도 가능합니다. 예시는 다음과 같습니다:

1CREATE TABLE quarterly_report_status (
2       report_id INT NOT NULL,
3       report_status VARCHAR(20) NOT NULL,
4       report_updated TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
5)
6PARTITION BY RANGE ( UNIX_TIMESTAMP(report_updated) ) (
7       PARTITION p0 VALUES LESS THAN ( UNIX_TIMESTAMP('2008-01-01 00:00:00') ),
8       PARTITION p1 VALUES LESS THAN ( UNIX_TIMESTAMP('2008-04-01 00:00:00') ),
9       PARTITION p2 VALUES LESS THAN ( UNIX_TIMESTAMP('2008-07-01 00:00:00') ),
10       PARTITION p3 VALUES LESS THAN ( UNIX_TIMESTAMP('2008-10-01 00:00:00') ),
11       PARTITION p4 VALUES LESS THAN ( UNIX_TIMESTAMP('2009-01-01 00:00:00') ),
12       PARTITION p5 VALUES LESS THAN ( UNIX_TIMESTAMP('2009-04-01 00:00:00') ),
13       PARTITION p6 VALUES LESS THAN ( UNIX_TIMESTAMP('2009-07-01 00:00:00') ),
14       PARTITION p7 VALUES LESS THAN ( UNIX_TIMESTAMP('2009-10-01 00:00:00') ),
15       PARTITION p8 VALUES LESS THAN ( UNIX_TIMESTAMP('2010-01-01 00:00:00') ),
16       PARTITION p9 VALUES LESS THAN (MAXVALUE)
17);

MySQL 9.5에서는, TIMESTAMP 값을 포함하는 다른 표현식은 허용되지 않습니다(버그 #42849 참조).

참고

MySQL 9.5에서는, LIST로 파티셔닝 된 테이블의 파티셔닝 표현식으로 UNIX_TIMESTAMP(timestamp_column)을 사용하는 것도 가능합니다. 그러나 보통은 그렇게 하는 것이 실용적이지 않습니다.

2. 테이블을 RANGE COLUMNS로 파티셔닝 하되, 파티셔닝 column으로 DATE 또는 DATETIME column을 사용합니다. 예를 들어, members 테이블은 다음과 같이 joined column을 직접 사용하여 정의할 수 있습니다:

1CREATE TABLE members (
2       firstname VARCHAR(25) NOT NULL,
3       lastname VARCHAR(25) NOT NULL,
4       username VARCHAR(16) NOT NULL,
5       email VARCHAR(35),
6       joined DATE NOT NULL
7)
8PARTITION BY RANGE COLUMNS(joined) (
9       PARTITION p0 VALUES LESS THAN ('1960-01-01'),
10       PARTITION p1 VALUES LESS THAN ('1970-01-01'),
11       PARTITION p2 VALUES LESS THAN ('1980-01-01'),
12       PARTITION p3 VALUES LESS THAN ('1990-01-01'),
13       PARTITION p4 VALUES LESS THAN MAXVALUE
14);

참고

RANGE COLUMNS에서는 DATE 또는 DATETIME 이외의 date 또는 time type을 사용하는 파티셔닝 column은 지원되지 않습니다.