26.3.1 Management of RANGE and LIST Partitions

26.3.1 RANGE 및 LIST 파티션 관리

range 파티션과 list 파티션에서 파티션을 추가하고 제거하는 작업은 유사한 방식으로 처리되므로, 이 절에서는 두 종류의 파티셔닝 관리에 대해 함께 논의합니다. hash 또는 key 기준으로 파티셔닝된 테이블을 다루는 방법에 대한 정보는 Section 26.3.2, “Management of HASH and KEY Partitions”을 참조하십시오.

RANGE 또는 LIST 기준으로 파티셔닝된 테이블에서 파티션을 삭제하려면, DROP PARTITION 옵션을 사용한 ALTER TABLE 문을 사용할 수 있습니다. 다음과 같이 range 기준으로 파티셔닝된 테이블을 생성하고, CREATE TABLE 및 INSERT 문을 사용하여 10개의 레코드를 삽입했다고 가정해 봅시다:

1mysql> CREATE TABLE tr (id INT, name VARCHAR(50), purchased DATE)
2    ->     PARTITION BY RANGE( YEAR(purchased) ) (
3    ->         PARTITION p0 VALUES LESS THAN (1990),
4    ->         PARTITION p1 VALUES LESS THAN (1995),
5    ->         PARTITION p2 VALUES LESS THAN (2000),
6    ->         PARTITION p3 VALUES LESS THAN (2005),
7    ->         PARTITION p4 VALUES LESS THAN (2010),
8    ->         PARTITION p5 VALUES LESS THAN (2015)
9    ->     );
10Query OK, 0 rows affected (0.28 sec)
11
12mysql> INSERT INTO tr VALUES
13    ->     (1, 'desk organiser', '2003-10-15'),
14    ->     (2, 'alarm clock', '1997-11-05'),
15    ->     (3, 'chair', '2009-03-10'),
16    ->     (4, 'bookcase', '1989-01-10'),
17    ->     (5, 'exercise bike', '2014-05-09'),
18    ->     (6, 'sofa', '1987-06-05'),
19    ->     (7, 'espresso maker', '2011-11-22'),
20    ->     (8, 'aquarium', '1992-08-04'),
21    ->     (9, 'study desk', '2006-09-16'),
22    ->     (10, 'lava lamp', '1998-12-25');
23Query OK, 10 rows affected (0.05 sec)
24Records: 10  Duplicates: 0  Warnings: 0

다음과 같이 조회하면 어떤 항목이 파티션 p2에 삽입되었어야 하는지 확인할 수 있습니다:

1mysql> SELECT * FROM tr
2    ->     WHERE purchased BETWEEN '1995-01-01' AND '1999-12-31';
3+------+-------------+------------+
4| id   | name        | purchased  |
5+------+-------------+------------+
6|    2 | alarm clock | 1997-11-05 |
7|   10 | lava lamp   | 1998-12-25 |
8+------+-------------+------------+
92 rows in set (0.00 sec)

다음과 같이 파티션 선택을 사용해서도 이 정보를 얻을 수 있습니다:

1mysql> SELECT * FROM tr PARTITION (p2);
2+------+-------------+------------+
3| id   | name        | purchased  |
4+------+-------------+------------+
5|    2 | alarm clock | 1997-11-05 |
6|   10 | lava lamp   | 1998-12-25 |
7+------+-------------+------------+
82 rows in set (0.00 sec)

더 많은 정보는 Section 26.5, “Partition Selection”을 참조하십시오.

p2라는 이름의 파티션을 삭제하려면 다음 명령을 실행합니다:

1mysql> ALTER TABLE tr DROP PARTITION p2;
2Query OK, 0 rows affected (0.03 sec)

참고

NDBCLUSTER 스토리지 엔진은 ALTER TABLE ... DROP PARTITION을 지원하지 않습니다. 그러나 이 장에서 설명하는 다른 파티셔닝 관련 ALTER TABLE 확장은 지원합니다.

_파티션을 삭제하면, 해당 파티션에 저장되어 있던 모든 데이터도 함께 삭제된다는 점_을 꼭 기억해야 합니다. 이전 SELECT 쿼리를 다시 실행해 보면 이를 확인할 수 있습니다:

1mysql> SELECT * FROM tr WHERE purchased
2    -> BETWEEN '1995-01-01' AND '1999-12-31';
3Empty set (0.00 sec)

참고

DROP PARTITION은 네이티브 파티셔닝 인플레이스 API에서 지원되며, ALGORITHM={COPY|INPLACE}와 함께 사용할 수 있습니다. ALGORITHM=INPLACE와 함께 사용하는 DROP PARTITION은 파티션에 저장된 데이터를 삭제하고 파티션을 드롭합니다. 그러나 ALGORITHM=COPY 또는 old_alter_table=ON과 함께 사용하는 DROP PARTITION은 파티셔닝된 테이블을 재빌드하고, 드롭된 파티션의 데이터를 호환 가능한 PARTITION ... VALUES 정의를 가진 다른 파티션으로 옮기려고 시도합니다. 다른 파티션으로 옮길 수 없는 데이터는 삭제됩니다.

이 때문에, 어떤 테이블에 대해 ALTER TABLE ... DROP PARTITION을 실행하기 전에 그 테이블에 대한 DROP 권한이 있어야 합니다.

테이블 정의와 파티셔닝 스킴은 유지하면서 모든 파티션에서 모든 데이터를 삭제하려면 TRUNCATE TABLE 문을 사용하십시오. (자세한 내용은 Section 15.1.42, “TRUNCATE TABLE Statement”를 참조하십시오.)

데이터를 잃지 않고 테이블의 파티셔닝을 변경하려면, 대신 ALTER TABLE ... REORGANIZE PARTITION을 사용하십시오. 이에 대한 정보는 아래 또는 Section 15.1.11, “ALTER TABLE Statement”에서 REORGANIZE PARTITION을 참조하십시오.

이제 SHOW CREATE TABLE 문을 실행하면, 테이블의 파티셔닝 구성이 어떻게 변경되었는지 확인할 수 있습니다:

1mysql> SHOW CREATE TABLE tr\G
2*************************** 1. row ***************************
3       Table: tr
4Create Table: CREATE TABLE `tr` (
5  `id` int(11) DEFAULT NULL,
6  `name` varchar(50) DEFAULT NULL,
7  `purchased` date DEFAULT NULL
8) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
9/*!50100 PARTITION BY RANGE ( YEAR(purchased))
10(PARTITION p0 VALUES LESS THAN (1990) ENGINE = InnoDB,
11 PARTITION p1 VALUES LESS THAN (1995) ENGINE = InnoDB,
12 PARTITION p3 VALUES LESS THAN (2005) ENGINE = InnoDB,
13 PARTITION p4 VALUES LESS THAN (2010) ENGINE = InnoDB,
14 PARTITION p5 VALUES LESS THAN (2015) ENGINE = InnoDB) */
151 row in set (0.00 sec)

이제 purchased 컬럼 값이 '1995-01-01' 이상 '2004-12-31' 이하인 새 row를 변경된 테이블에 삽입하면, 그 row는 파티션 p3에 저장됩니다. 다음과 같이 확인할 수 있습니다:

1mysql> INSERT INTO tr VALUES (11, 'pencil holder', '1995-07-12');
2Query OK, 1 row affected (0.00 sec)
3
4mysql> SELECT * FROM tr WHERE purchased
5    -> BETWEEN '1995-01-01' AND '2004-12-31';
6+------+----------------+------------+
7| id   | name           | purchased  |
8+------+----------------+------------+
9|    1 | desk organiser | 2003-10-15 |
10|   11 | pencil holder  | 1995-07-12 |
11+------+----------------+------------+
122 rows in set (0.00 sec)
13
14mysql> ALTER TABLE tr DROP PARTITION p3;
15Query OK, 0 rows affected (0.03 sec)
16
17mysql> SELECT * FROM tr WHERE purchased
18    -> BETWEEN '1995-01-01' AND '2004-12-31';
19Empty set (0.00 sec)

ALTER TABLE ... DROP PARTITION으로 인해 테이블에서 삭제된 row의 수는, 동일한 DELETE 쿼리에서처럼 서버가 보고하지 않습니다.

LIST 파티션을 삭제하는 경우에도, RANGE 파티션을 삭제할 때와 완전히 동일한 ALTER TABLE ... DROP PARTITION 문법을 사용합니다. 그러나 이후 테이블 사용에 있어서 한 가지 중요한 차이가 있습니다. 삭제된 파티션을 정의했던 value 리스트에 포함된 값을 가진 row는 더 이상 테이블에 삽입할 수 없습니다. (예시는 Section 26.2.2, “LIST Partitioning”을 참조하십시오.)

이전에 파티셔닝된 테이블에 새 range 또는 list 파티션을 추가하려면, ALTER TABLE ... ADD PARTITION 문을 사용하십시오. RANGE 기준으로 파티셔닝된 테이블의 경우, 이를 사용하여 기존 파티션 목록의 끝에 새로운 range를 추가할 수 있습니다. 다음과 같이 구성원 정보를 저장하는 파티셔닝된 테이블 members가 정의되어 있다고 가정해 봅시다:

1CREATE TABLE members (
2    id INT,
3    fname VARCHAR(25),
4    lname VARCHAR(25),
5    dob DATE
6)
7PARTITION BY RANGE( YEAR(dob) ) (
8    PARTITION p0 VALUES LESS THAN (1980),
9    PARTITION p1 VALUES LESS THAN (1990),
10    PARTITION p2 VALUES LESS THAN (2000)
11);

또한 구성원의 최소 연령이 16세라고 가정합시다. 달력이 2015년 말에 가까워지면서, 곧 2000년(이후)에 태어난 사람들을 회원으로 받아들일 수 있도록 준비해야 한다는 것을 깨닫습니다. 다음과 같이 members 테이블을 수정하여 2000년부터 2010년까지 태어난 신규 구성원을 수용할 수 있습니다:

1ALTER TABLE members ADD PARTITION (PARTITION p3 VALUES LESS THAN (2010));

range 기준으로 파티셔닝된 테이블의 경우, ADD PARTITION을 사용해 파티션 목록의 high end에만 새 파티션을 추가할 수 있습니다. 다음과 같이 기존 파티션 사이 또는 앞에 새 파티션을 추가하려고 시도하면 오류가 발생합니다:

1mysql> ALTER TABLE members
2     >     ADD PARTITION (
3     >     PARTITION n VALUES LESS THAN (1970));
4ERROR 1463 (HY000): VALUES LESS THAN value must be strictly »
5   increasing for each partition

이 문제는 다음과 같이 첫 번째 파티션을 두 개의 새로운 파티션으로 재구성하여, 그 사이에 range를 나누는 방식으로 우회할 수 있습니다:

1ALTER TABLE members
2    REORGANIZE PARTITION p0 INTO (
3        PARTITION n0 VALUES LESS THAN (1970),
4        PARTITION n1 VALUES LESS THAN (1980)
5);

SHOW CREATE TABLE을 사용하면 ALTER TABLE 문이 원하는 효과를 냈음을 확인할 수 있습니다:

1mysql> SHOW CREATE TABLE members\G
2*************************** 1. row ***************************
3       Table: members
4Create Table: CREATE TABLE `members` (
5  `id` int(11) DEFAULT NULL,
6  `fname` varchar(25) DEFAULT NULL,
7  `lname` varchar(25) DEFAULT NULL,
8  `dob` date DEFAULT NULL
9) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
10/*!50100 PARTITION BY RANGE ( YEAR(dob))
11(PARTITION n0 VALUES LESS THAN (1970) ENGINE = InnoDB,
12 PARTITION n1 VALUES LESS THAN (1980) ENGINE = InnoDB,
13 PARTITION p1 VALUES LESS THAN (1990) ENGINE = InnoDB,
14 PARTITION p2 VALUES LESS THAN (2000) ENGINE = InnoDB,
15 PARTITION p3 VALUES LESS THAN (2010) ENGINE = InnoDB) */
161 row in set (0.00 sec)

Section 15.1.11.1, “ALTER TABLE Partition Operations”도 참조하십시오.

ALTER TABLE ... ADD PARTITION은 LIST 기준으로 파티셔닝된 테이블에 새 파티션을 추가하는 데에도 사용할 수 있습니다. 예를 들어, 다음 CREATE TABLE 문으로 정의된 테이블 tt가 있다고 가정해 봅시다:

1CREATE TABLE tt (
2    id INT,
3    data INT
4)
5PARTITION BY LIST(data) (
6    PARTITION p0 VALUES IN (5, 10, 15),
7    PARTITION p1 VALUES IN (6, 12, 18)
8);

다음과 같이 data 컬럼 값이 7, 14, 21인 row를 저장할 새 파티션을 추가할 수 있습니다:

1ALTER TABLE tt ADD PARTITION (PARTITION p2 VALUES IN (7, 14, 21));

기존 파티션의 value 리스트에 이미 포함된 값을 포괄하는 새로운 LIST 파티션은 추가할 수 없다는 점을 기억하십시오. 그렇게 시도하면 오류가 발생합니다:

1mysql> ALTER TABLE tt ADD PARTITION
2     >     (PARTITION np VALUES IN (4, 8, 12));
3ERROR 1465 (HY000): Multiple definition of same constant »
4                    in list partitioning

data 컬럼 값이 12인 row는 이미 파티션 p1에 할당되어 있으므로, value 리스트에 12를 포함하는 새 파티션을 테이블 tt에 생성할 수 없습니다. 이를 달성하려면 p1을 드롭한 다음, 수정된 정의로 np와 새로운 p1을 추가할 수 있습니다. 그러나 앞에서 논의했듯이, 이렇게 하면 p1에 저장된 모든 데이터를 잃게 되며, 이는 종종 원치 않는 결과입니다. 또 다른 해결책으로는 새로운 파티셔닝을 가진 테이블 복사본을 만들고 CREATE TABLE ... SELECT ...을 사용해 데이터를 복사한 다음, 이전 테이블을 드롭하고 새 테이블의 이름을 변경하는 방법이 있을 수 있으나, 많은 양의 데이터를 다룰 때는 시간이 매우 오래 걸릴 수 있습니다. 또한 고가용성이 요구되는 상황에서는 이 방법이 현실적이지 않을 수 있습니다.

다음과 같이 하나의 ALTER TABLE ... ADD PARTITION 문으로 여러 파티션을 추가할 수도 있습니다:

1CREATE TABLE employees (
2  id INT NOT NULL,
3  fname VARCHAR(50) NOT NULL,
4  lname VARCHAR(50) NOT NULL,
5  hired DATE NOT NULL
6)
7PARTITION BY RANGE( YEAR(hired) ) (
8  PARTITION p1 VALUES LESS THAN (1991),
9  PARTITION p2 VALUES LESS THAN (1996),
10  PARTITION p3 VALUES LESS THAN (2001),
11  PARTITION p4 VALUES LESS THAN (2005)
12);
13
14ALTER TABLE employees ADD PARTITION (
15    PARTITION p5 VALUES LESS THAN (2010),
16    PARTITION p6 VALUES LESS THAN MAXVALUE
17);

다행히도, MySQL 파티셔닝 구현은 데이터를 잃지 않고 파티션을 재정의할 수 있는 방법을 제공합니다. 먼저 RANGE 파티셔닝과 관련된 두 개의 간단한 예제를 살펴보겠습니다. 현재 다음과 같이 정의된 members 테이블을 다시 떠올려 봅시다:

1mysql> SHOW CREATE TABLE members\G
2*************************** 1. row ***************************
3       Table: members
4Create Table: CREATE TABLE `members` (
5  `id` int(11) DEFAULT NULL,
6  `fname` varchar(25) DEFAULT NULL,
7  `lname` varchar(25) DEFAULT NULL,
8  `dob` date DEFAULT NULL
9) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
10/*!50100 PARTITION BY RANGE ( YEAR(dob))
11(PARTITION n0 VALUES LESS THAN (1970) ENGINE = InnoDB,
12 PARTITION n1 VALUES LESS THAN (1980) ENGINE = InnoDB,
13 PARTITION p1 VALUES LESS THAN (1990) ENGINE = InnoDB,
14 PARTITION p2 VALUES LESS THAN (2000) ENGINE = InnoDB,
15 PARTITION p3 VALUES LESS THAN (2010) ENGINE = InnoDB) */
161 row in set (0.00 sec)

1960년 이전에 태어난 구성원을 나타내는 모든 row를 별도의 파티션으로 옮기고 싶다고 가정해 봅시다. 이미 보았듯이, 이는 ALTER TABLE ... ADD PARTITION을 사용해서는 수행할 수 없습니다. 그러나 다음과 같이 또 다른 파티션 관련 ALTER TABLE 확장을 사용하여 이를 달성할 수 있습니다:

1ALTER TABLE members REORGANIZE PARTITION n0 INTO (
2    PARTITION s0 VALUES LESS THAN (1960),
3    PARTITION s1 VALUES LESS THAN (1970)
4);

실제로 이 명령은 파티션 n0을 두 개의 새 파티션 s0와 s1로 분할합니다. 또한 두 개의 PARTITION ... VALUES ... 절에 구현된 규칙에 따라, n0에 저장되어 있던 데이터를 새 파티션으로 이동시키므로, s0에는 YEAR(dob)가 1960 미만인 레코드만, s1에는 YEAR(dob)가 1960 이상 1970 미만인 row만 포함됩니다.

REORGANIZE PARTITION 절은 인접한 파티션을 병합하는 데에도 사용할 수 있습니다. 다음과 같이 이전 문장의 효과를 members 테이블에서 되돌릴 수 있습니다:

1ALTER TABLE members REORGANIZE PARTITION s0,s1 INTO (
2    PARTITION p0 VALUES LESS THAN (1970)
3);

REORGANIZE PARTITION을 사용해 파티션을 분할하거나 병합할 때는 데이터가 손실되지 않습니다. 위 문장을 실행하는 동안, MySQL은 파티션 s0와 s1에 저장되어 있던 모든 레코드를 파티션 p0으로 이동합니다.

REORGANIZE PARTITION의 일반적인 문법은 다음과 같습니다:

1ALTER TABLE tbl_name
2    REORGANIZE PARTITION partition_list
3    INTO (partition_definitions);

여기서 tbl_name 은 파티셔닝된 테이블의 이름이고, partition_list 는 변경할 기존 파티션 하나 이상에 대한 이름을 쉼표로 구분한 목록입니다. partition_definitions 는 새로운 파티션 정의를 쉼표로 구분한 목록이며, 이는 CREATE TABLE에서 사용하는 partition_definitions 목록과 동일한 규칙을 따릅니다. REORGANIZE PARTITION을 사용할 때, 여러 파티션을 하나로 병합하거나 하나의 파티션을 여러 개로 분할하는 것으로 제한되지 않습니다. 예를 들어, 다음과 같이 members 테이블의 네 개 파티션을 두 개로 재구성할 수 있습니다:

1ALTER TABLE members REORGANIZE PARTITION p0,p1,p2,p3 INTO (
2    PARTITION m0 VALUES LESS THAN (1980),
3    PARTITION m1 VALUES LESS THAN (2000)
4);

REORGANIZE PARTITION은 LIST 기준으로 파티셔닝된 테이블에도 사용할 수 있습니다. 기존 파티션 중 하나의 value 리스트에 이미 존재하는 값을 새 파티션에 포함하려고 하다가 실패했던 list-파티셔닝 테이블 tt에 새 파티션을 추가하는 문제로 돌아가 봅시다. 충돌이 없는 값만 포함하는 파티션을 추가한 다음, 기존 파티션과 새 파티션을 재구성하여, 기존 파티션에 있던 값을 새 파티션으로 옮기는 방식으로 이 문제를 처리할 수 있습니다:

1ALTER TABLE tt ADD PARTITION (PARTITION np VALUES IN (4, 8));
2ALTER TABLE tt REORGANIZE PARTITION p1,np INTO (
3    PARTITION p1 VALUES IN (6, 18),
4    PARTITION np VALUES in (4, 8, 12)
5);

ALTER TABLE ... REORGANIZE PARTITION을 사용하여 RANGE 또는 LIST 기준으로 파티셔닝된 테이블을 재파티셔닝할 때 기억해야 할 몇 가지 핵심 사항은 다음과 같습니다:

• 새로운 파티셔닝 스킴을 결정하는 데 사용되는 PARTITION 옵션은 CREATE TABLE 문에서 사용하는 옵션과 동일한 규칙을 따릅니다.

  새로운 RANGE 파티셔닝 스킴에서는 range가 겹칠 수 없고, 새로운 LIST 파티셔닝 스킴에서는 value 집합이 겹칠 수 없습니다.

• partition_definitions 목록에 포함된 파티션들의 조합은, partition_list 에 나열된 파티션들이 전체적으로 담당하던 것과 동일한 range 또는 value 집합을 전반적으로 처리해야 합니다.

  예를 들어, 이 절의 예제로 사용된 members 테이블에서 파티션 p1과 p2는 함께 1980년부터 1999년까지의 연도를 포괄합니다. 이 두 파티션에 대한 어떤 재구성도 전체적으로 동일한 연도 범위를 포괄해야 합니다.

• RANGE 기준으로 파티셔닝된 테이블의 경우, 인접한 파티션만 재구성할 수 있으며, range 파티션을 건너뛸 수 없습니다.

  예를 들어, 예제 members 테이블에서 ALTER TABLE members REORGANIZE PARTITION p0,p2 INTO ...로 시작하는 문장을 사용해 재구성할 수는 없습니다. 그 이유는 p0가 1970년 이전의 연도를, p2가 1990년부터 1999년까지(포함)를 다루므로 이 둘은 인접한 파티션이 아니기 때문입니다. (이 경우 파티션 p1을 건너뛸 수 없습니다.)

• REORGANIZE PARTITION을 사용해서 테이블이 사용하는 파티셔닝 유형(예: RANGE 파티션을 HASH 파티션으로 변경하거나 그 반대)을 변경할 수는 없습니다. 또한 이 문장을 사용해 파티셔닝 표현식이나 컬럼을 변경할 수도 없습니다. 이러한 작업을 테이블을 드롭하고 다시 생성하지 않고 수행하려면, 다음과 같이 ALTER TABLE ... PARTITION BY ...를 사용할 수 있습니다:

1ALTER TABLE members
2      PARTITION BY HASH( YEAR(dob) )
3      PARTITIONS 8;