데브러너 (DevRunner)

26.3.3 테이블과의 파티션 및 서브파티션 교환

MySQL 9.5에서는 ALTER TABLE pt EXCHANGE PARTITION p WITH TABLE nt를 사용하여 테이블 파티션 또는 서브파티션을 테이블과 교환할 수 있습니다. 이때 _pt_는 파티션된 테이블이고, _p_는 _pt_의 파티션 또는 서브파티션이며, 파티션되지 않은 테이블인 _nt_와 교환됩니다. 다음 조건들이 모두 참이어야 합니다:

테이블 nt 자체는 파티션되지 않아야 합니다.
테이블 _nt_는 임시 테이블이 아니어야 합니다.
테이블 _pt_와 _nt_의 구조는 그 외에는 동일해야 합니다.
테이블 nt는 어떤 외래 키 참조도 포함하지 않아야 하며, 다른 어떤 테이블도 nt를 참조하는 외래 키를 가져서는 안 됩니다.
_nt_에는 파티션 _p_의 파티션 정의 경계를 벗어나는 행이 없어야 합니다. 이 조건은 WITHOUT VALIDATION을 사용하는 경우에는 적용되지 않습니다.
두 테이블은 동일한 문자 집합과 정렬을 사용해야 합니다.
InnoDB 테이블의 경우, 두 테이블은 동일한 행 포맷을 사용해야 합니다. InnoDB 테이블의 행 포맷을 알아보려면 INFORMATION_SCHEMA.INNODB_TABLES를 조회합니다.
파티션 p에 대한 파티션 수준 MAX_ROWS 설정은 테이블 nt에 대해 설정된 테이블 수준 MAX_ROWS 값과 동일해야 합니다. p에 대한 파티션 수준 MIN_ROWS 설정 역시 nt에 대해 설정된 테이블 수준 MIN_ROWS 값과 동일해야 합니다.

이는 pt에 테이블 수준 MAX_ROWS 또는 MIN_ROWS 옵션이 명시적으로 설정되어 있는지 여부와 관계없이 항상 참이어야 합니다.
두 테이블 pt와 nt 간에는 AVG_ROW_LENGTH가 달라질 수 없습니다.
테이블과 이와 교환될 파티션 간에는 INDEX DIRECTORY가 달라질 수 없습니다.
두 테이블 중 어느 쪽에서도 테이블 또는 파티션 TABLESPACE 옵션을 사용할 수 없습니다.

ALTER, INSERT, CREATE 권한이 일반적으로 ALTER TABLE 문에 필요할 뿐 아니라, ALTER TABLE ... EXCHANGE PARTITION을 수행하려면 DROP 권한도 있어야 합니다.

또한 ALTER TABLE ... EXCHANGE PARTITION의 다음과 같은 영향에 대해서도 알아 두어야 합니다:

ALTER TABLE ... EXCHANGE PARTITION을 실행해도 파티션된 테이블이나 교환 대상 테이블에서 어떤 트리거도 호출되지 않습니다.
교환된 테이블의 모든 AUTO_INCREMENT 열은 리셋됩니다.
IGNORE 키워드는 ALTER TABLE ... EXCHANGE PARTITION와 함께 사용되더라도 아무 효과가 없습니다.

ALTER TABLE ... EXCHANGE PARTITION의 구문은 다음과 같으며, 여기서 _pt_는 파티션된 테이블, _p_는 교환할 파티션(또는 서브파티션), _nt_는 _p_와 교환할 비파티션 테이블입니다:

1ALTER TABLE pt
2    EXCHANGE PARTITION p
3    WITH TABLE nt;

선택적으로 WITH VALIDATION 또는 WITHOUT VALIDATION을 덧붙일 수 있습니다. WITHOUT VALIDATION이 지정되면, ALTER TABLE ... EXCHANGE PARTITION 작업은 파티션과 비파티션 테이블을 교환할 때 행 단위 검증을 수행하지 않으며, 이 경우 데이터베이스 관리자가 행이 파티션 정의 경계 안에 있는 것을 보장할 책임을 집니다. WITH VALIDATION이 기본값입니다.

하나의 ALTER TABLE EXCHANGE PARTITION 문에서 오직 하나의 파티션 또는 서브파티션만을 하나의 비파티션 테이블과 교환할 수 있습니다. 여러 파티션 또는 서브파티션을 교환하려면 여러 개의 ALTER TABLE EXCHANGE PARTITION 문을 사용해야 합니다. EXCHANGE PARTITION은 다른 ALTER TABLE 옵션과 결합할 수 없습니다. 파티션된 테이블에서 사용하는 파티셔닝 및 (해당하는 경우) 서브파티셔닝 유형은 MySQL 9.5에서 지원되는 어떤 유형이든 사용할 수 있습니다.

Nonpartitioned Table과의 파티션 교환

파티션된 테이블 e가 다음 SQL 문을 사용해 생성되고 데이터가 채워져 있다고 가정해 봅니다:

1CREATE TABLE e (
2    id INT NOT NULL,
3    fname VARCHAR(30),
4    lname VARCHAR(30)
5)
6    PARTITION BY RANGE (id) (
7        PARTITION p0 VALUES LESS THAN (50),
8        PARTITION p1 VALUES LESS THAN (100),
9        PARTITION p2 VALUES LESS THAN (150),
10        PARTITION p3 VALUES LESS THAN (MAXVALUE)
11);
12
13INSERT INTO e VALUES
14    (1669, "Jim", "Smith"),
15    (337, "Mary", "Jones"),
16    (16, "Frank", "White"),
17    (2005, "Linda", "Black");

이제 e의 비파티션 복사본인 e2를 생성합니다. 이는 다음과 같이 mysql 클라이언트를 사용해 수행할 수 있습니다:

1mysql> CREATE TABLE e2 LIKE e;
2Query OK, 0 rows affected (0.04 sec)
3
4mysql> ALTER TABLE e2 REMOVE PARTITIONING;
5Query OK, 0 rows affected (0.07 sec)
6Records: 0  Duplicates: 0  Warnings: 0

Information Schema의 PARTITIONS 테이블을 다음과 같이 조회하면, 테이블 e의 어떤 파티션에 행이 들어 있는지 확인할 수 있습니다:

1mysql> SELECT PARTITION_NAME, TABLE_ROWS
2           FROM INFORMATION_SCHEMA.PARTITIONS
3           WHERE TABLE_NAME = 'e';
4+----------------+------------+
5| PARTITION_NAME | TABLE_ROWS |
6+----------------+------------+
7| p0             |          1 |
8| p1             |          0 |
9| p2             |          0 |
10| p3             |          3 |
11+----------------+------------+
122 rows in set (0.00 sec)

참고

파티션된 InnoDB 테이블의 경우, Information Schema PARTITIONS 테이블의 TABLE_ROWS 열에 표시되는 행 수는 SQL 최적화에 사용되는 추정값일 뿐이며 항상 정확한 것은 아닙니다.

테이블 e의 파티션 p0을 테이블 e2와 교환하려면 다음과 같이 ALTER TABLE을 사용할 수 있습니다:

1mysql> ALTER TABLE e EXCHANGE PARTITION p0 WITH TABLE e2;
2Query OK, 0 rows affected (0.04 sec)

보다 정확히 말하면, 방금 실행한 문은 파티션에 있는 행을 테이블에 있는 행과 서로 교환합니다. 이전과 같이 Information Schema의 PARTITIONS 테이블을 조회하면, 파티션 p0에 있던 테이블 행이 더 이상 존재하지 않는 것을 확인할 수 있습니다:

1mysql> SELECT PARTITION_NAME, TABLE_ROWS
2           FROM INFORMATION_SCHEMA.PARTITIONS
3           WHERE TABLE_NAME = 'e';
4+----------------+------------+
5| PARTITION_NAME | TABLE_ROWS |
6+----------------+------------+
7| p0             |          0 |
8| p1             |          0 |
9| p2             |          0 |
10| p3             |          3 |
11+----------------+------------+
124 rows in set (0.00 sec)

테이블 e2를 조회해 보면, “사라진” 행이 이제 그곳에서 발견됩니다:

1mysql> SELECT * FROM e2;
2+----+-------+-------+
3| id | fname | lname |
4+----+-------+-------+
5| 16 | Frank | White |
6+----+-------+-------+
71 row in set (0.00 sec)

파티션과 교환될 테이블은 반드시 비어 있을 필요는 없습니다. 이를 보여 주기 위해, 먼저 테이블 e에 새 행을 삽입합니다. 이 행이 id 열 값을 50 미만으로 선택하여 파티션 p0에 저장되도록 한 다음, PARTITIONS 테이블을 조회해서 이를 확인합니다:

1mysql> INSERT INTO e VALUES (41, "Michael", "Green");
2Query OK, 1 row affected (0.05 sec)
3
4mysql> SELECT PARTITION_NAME, TABLE_ROWS
5           FROM INFORMATION_SCHEMA.PARTITIONS
6           WHERE TABLE_NAME = 'e';
7+----------------+------------+
8| PARTITION_NAME | TABLE_ROWS |
9+----------------+------------+
10| p0             |          1 |
11| p1             |          0 |
12| p2             |          0 |
13| p3             |          3 |
14+----------------+------------+
154 rows in set (0.00 sec)

이제 앞에서와 동일한 ALTER TABLE 문을 사용하여 다시 한 번 파티션 p0을 테이블 e2와 교환합니다:

1mysql> ALTER TABLE e EXCHANGE PARTITION p0 WITH TABLE e2;
2Query OK, 0 rows affected (0.28 sec)

다음 쿼리의 출력 결과를 보면, ALTER TABLE 문을 실행하기 전에 파티션 p0에 저장되어 있던 테이블 행과 테이블 e2에 저장되어 있던 테이블 행이 위치를 맞바꾼 것을 알 수 있습니다:

1mysql> SELECT * FROM e;
2+------+-------+-------+
3| id   | fname | lname |
4+------+-------+-------+
5|   16 | Frank | White |
6| 1669 | Jim   | Smith |
7|  337 | Mary  | Jones |
8| 2005 | Linda | Black |
9+------+-------+-------+
104 rows in set (0.00 sec)
11
12mysql> SELECT PARTITION_NAME, TABLE_ROWS
13           FROM INFORMATION_SCHEMA.PARTITIONS
14           WHERE TABLE_NAME = 'e';
15+----------------+------------+
16| PARTITION_NAME | TABLE_ROWS |
17+----------------+------------+
18| p0             |          1 |
19| p1             |          0 |
20| p2             |          0 |
21| p3             |          3 |
22+----------------+------------+
234 rows in set (0.00 sec)
24
25mysql> SELECT * FROM e2;
26+----+---------+-------+
27| id | fname   | lname |
28+----+---------+-------+
29| 41 | Michael | Green |
30+----+---------+-------+
311 row in set (0.00 sec)

Nonmatching Rows

ALTER TABLE ... EXCHANGE PARTITION 문을 실행하기 전에 비파티션 테이블에 있는 모든 행은 대상 파티션에 저장되기 위해 요구되는 조건을 만족해야 한다는 점을 염두에 두어야 합니다. 그렇지 않으면 문은 실패합니다. 이 동작을 보기 위해, 먼저 테이블 e의 파티션 p0의 파티션 정의 경계를 벗어나는 행을 e2에 삽입합니다. 예를 들어, id 열 값이 너무 큰 행을 삽입한 다음, 테이블을 파티션과 다시 교환해 보십시오:

1mysql> INSERT INTO e2 VALUES (51, "Ellen", "McDonald");
2Query OK, 1 row affected (0.08 sec)
3
4mysql> ALTER TABLE e EXCHANGE PARTITION p0 WITH TABLE e2;
5ERROR 1707 (HY000): Found row that does not match the partition

이 작업이 성공하도록 허용하는 것은 WITHOUT VALIDATION 옵션뿐입니다:

1mysql> ALTER TABLE e EXCHANGE PARTITION p0 WITH TABLE e2 WITHOUT VALIDATION;
2Query OK, 0 rows affected (0.02 sec)

파티션이 파티션 정의에 맞지 않는 행을 포함하는 테이블과 교환된 경우, 일치하지 않는 행을 수정하는 것은 데이터베이스 관리자의 책임이며, 이는 REPAIR TABLE 또는 ALTER TABLE ... REPAIR PARTITION을 사용해 수행할 수 있습니다.

Row-By-Row Validation 없이 파티션 교환

행이 많은 테이블과 파티션을 교환할 때 시간이 많이 소요되는 검증을 피하기 위해, ALTER TABLE ... EXCHANGE PARTITION 문에 WITHOUT VALIDATION을 덧붙여 행 단위 검증 단계를 건너뛸 수 있습니다.

다음 예제는 검증을 사용하는 경우와 사용하지 않는 경우, 비파티션 테이블과 파티션을 교환하는 데 걸리는 실행 시간 차이를 비교합니다. 파티션된 테이블(테이블 e)은 각각 100만 개의 행을 가진 두 개의 파티션을 포함합니다. 테이블 e의 p0에 있는 행을 제거한 다음, p0을 100만 개의 행을 가진 비파티션 테이블과 교환합니다. WITH VALIDATION 작업은 0.74초가 걸립니다. 이에 비해 WITHOUT VALIDATION 작업은 0.01초가 걸립니다.

1# Create a partitioned table with 1 million rows in each partition
2
3CREATE TABLE e (
4    id INT NOT NULL,
5    fname VARCHAR(30),
6    lname VARCHAR(30)
7)
8    PARTITION BY RANGE (id) (
9        PARTITION p0 VALUES LESS THAN (1000001),
10        PARTITION p1 VALUES LESS THAN (2000001),
11);
12
13mysql> SELECT COUNT(*) FROM e;
14| COUNT(*) |
15+----------+
16|  2000000 |
17+----------+
181 row in set (0.27 sec)
19
20# View the rows in each partition
21
22SELECT PARTITION_NAME, TABLE_ROWS FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_NAME = 'e';
23+----------------+-------------+
24| PARTITION_NAME | TABLE_ROWS  |
25+----------------+-------------+
26| p0             |     1000000 |
27| p1             |     1000000 |
28+----------------+-------------+
292 rows in set (0.00 sec)
30
31# Create a nonpartitioned table of the same structure and populate it with 1 million rows
32
33CREATE TABLE e2 (
34    id INT NOT NULL,
35    fname VARCHAR(30),
36    lname VARCHAR(30)
37);
38
39mysql> SELECT COUNT(*) FROM e2;
40+----------+
41| COUNT(*) |
42+----------+
43|  1000000 |
44+----------+
451 row in set (0.24 sec)
46
47# Create another nonpartitioned table of the same structure and populate it with 1 million rows
48
49CREATE TABLE e3 (
50    id INT NOT NULL,
51    fname VARCHAR(30),
52    lname VARCHAR(30)
53);
54
55mysql> SELECT COUNT(*) FROM e3;
56+----------+
57| COUNT(*) |
58+----------+
59|  1000000 |
60+----------+
611 row in set (0.25 sec)
62
63# Drop the rows from p0 of table e
64
65mysql> DELETE FROM e WHERE id < 1000001;
66Query OK, 1000000 rows affected (5.55 sec)
67
68# Confirm that there are no rows in partition p0
69
70mysql> SELECT PARTITION_NAME, TABLE_ROWS FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_NAME = 'e';
71+----------------+------------+
72| PARTITION_NAME | TABLE_ROWS |
73+----------------+------------+
74| p0             |          0 |
75| p1             |    1000000 |
76+----------------+------------+
772 rows in set (0.00 sec)
78
79# Exchange partition p0 of table e with the table e2 'WITH VALIDATION'
80
81mysql> ALTER TABLE e EXCHANGE PARTITION p0 WITH TABLE e2 WITH VALIDATION;
82Query OK, 0 rows affected (0.74 sec)
83
84# Confirm that the partition was exchanged with table e2
85
86mysql> SELECT PARTITION_NAME, TABLE_ROWS FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_NAME = 'e';
87+----------------+------------+
88| PARTITION_NAME | TABLE_ROWS |
89+----------------+------------+
90| p0             |    1000000 |
91| p1             |    1000000 |
92+----------------+------------+
932 rows in set (0.00 sec)
94
95# Once again, drop the rows from p0 of table e
96
97mysql> DELETE FROM e WHERE id < 1000001;
98Query OK, 1000000 rows affected (5.55 sec)
99
100# Confirm that there are no rows in partition p0
101
102mysql> SELECT PARTITION_NAME, TABLE_ROWS FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_NAME = 'e';
103+----------------+------------+
104| PARTITION_NAME | TABLE_ROWS |
105+----------------+------------+
106| p0             |          0 |
107| p1             |    1000000 |
108+----------------+------------+
1092 rows in set (0.00 sec)
110
111# Exchange partition p0 of table e with the table e3 'WITHOUT VALIDATION'
112
113mysql> ALTER TABLE e EXCHANGE PARTITION p0 WITH TABLE e3 WITHOUT VALIDATION;
114Query OK, 0 rows affected (0.01 sec)
115
116# Confirm that the partition was exchanged with table e3
117
118mysql> SELECT PARTITION_NAME, TABLE_ROWS FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_NAME = 'e';
119+----------------+------------+
120| PARTITION_NAME | TABLE_ROWS |
121+----------------+------------+
122| p0             |    1000000 |
123| p1             |    1000000 |
124+----------------+------------+
1252 rows in set (0.00 sec)

Nonpartitioned Table과의 서브파티션 교환

(Section 26.2.6, “Subpartitioning”을 참조) 서브파티션된 테이블의 서브파티션 역시 ALTER TABLE ... EXCHANGE PARTITION 문을 사용하여 비파티션 테이블과 교환할 수 있습니다. 다음 예제에서는 먼저 테이블 es를 생성하는데, 이 테이블은 RANGE로 파티셔닝되고 KEY로 서브파티셔닝되며, 테이블 e와 같이 데이터를 채운 다음, 다음과 같이 비어 있는 비파티션 복사본 es2를 생성합니다:

1mysql> CREATE TABLE es (
2    ->     id INT NOT NULL,
3    ->     fname VARCHAR(30),
4    ->     lname VARCHAR(30)
5    -> )
6    ->     PARTITION BY RANGE (id)
7    ->     SUBPARTITION BY KEY (lname)
8    ->     SUBPARTITIONS 2 (
9    ->         PARTITION p0 VALUES LESS THAN (50),
10    ->         PARTITION p1 VALUES LESS THAN (100),
11    ->         PARTITION p2 VALUES LESS THAN (150),
12    ->         PARTITION p3 VALUES LESS THAN (MAXVALUE)
13    ->     );
14Query OK, 0 rows affected (2.76 sec)
15
16mysql> INSERT INTO es VALUES
17    ->     (1669, "Jim", "Smith"),
18    ->     (337, "Mary", "Jones"),
19    ->     (16, "Frank", "White"),
20    ->     (2005, "Linda", "Black");
21Query OK, 4 rows affected (0.04 sec)
22Records: 4  Duplicates: 0  Warnings: 0
23
24mysql> CREATE TABLE es2 LIKE es;
25Query OK, 0 rows affected (1.27 sec)
26
27mysql> ALTER TABLE es2 REMOVE PARTITIONING;
28Query OK, 0 rows affected (0.70 sec)
29Records: 0  Duplicates: 0  Warnings: 0

테이블 es를 생성할 때 어떤 서브파티션도 명시적으로 이름을 주지 않았지만, INFORMATION_SCHEMA의 PARTITIONS 테이블을 조회할 때 SUBPARTITION_NAME 열을 포함시키면 다음과 같이 생성된 이름을 얻을 수 있습니다:

1mysql> SELECT PARTITION_NAME, SUBPARTITION_NAME, TABLE_ROWS
2    ->     FROM INFORMATION_SCHEMA.PARTITIONS
3    ->     WHERE TABLE_NAME = 'es';
4+----------------+-------------------+------------+
5| PARTITION_NAME | SUBPARTITION_NAME | TABLE_ROWS |
6+----------------+-------------------+------------+
7| p0             | p0sp0             |          1 |
8| p0             | p0sp1             |          0 |
9| p1             | p1sp0             |          0 |
10| p1             | p1sp1             |          0 |
11| p2             | p2sp0             |          0 |
12| p2             | p2sp1             |          0 |
13| p3             | p3sp0             |          3 |
14| p3             | p3sp1             |          0 |
15+----------------+-------------------+------------+
168 rows in set (0.00 sec)

다음 ALTER TABLE 문은 테이블 es의 서브파티션 p3sp0을 비파티션 테이블 es2와 교환합니다:

1mysql> ALTER TABLE es EXCHANGE PARTITION p3sp0 WITH TABLE es2;
2Query OK, 0 rows affected (0.29 sec)

다음 쿼리를 실행하면 행이 교환되었음을 확인할 수 있습니다:

1mysql> SELECT PARTITION_NAME, SUBPARTITION_NAME, TABLE_ROWS
2    ->     FROM INFORMATION_SCHEMA.PARTITIONS
3    ->     WHERE TABLE_NAME = 'es';
4+----------------+-------------------+------------+
5| PARTITION_NAME | SUBPARTITION_NAME | TABLE_ROWS |
6+----------------+-------------------+------------+
7| p0             | p0sp0             |          1 |
8| p0             | p0sp1             |          0 |
9| p1             | p1sp0             |          0 |
10| p1             | p1sp1             |          0 |
11| p2             | p2sp0             |          0 |
12| p2             | p2sp1             |          0 |
13| p3             | p3sp0             |          0 |
14| p3             | p3sp1             |          0 |
15+----------------+-------------------+------------+
168 rows in set (0.00 sec)
17
18mysql> SELECT * FROM es2;
19+------+-------+-------+
20| id   | fname | lname |
21+------+-------+-------+
22| 1669 | Jim   | Smith |
23|  337 | Mary  | Jones |
24| 2005 | Linda | Black |
25+------+-------+-------+
263 rows in set (0.00 sec)

테이블이 서브파티셔닝된 경우, 다음 예에서 보듯이 파티션 전체가 아니라 오직 테이블의 서브파티션만을 비파티션 테이블과 교환할 수 있습니다:

1mysql> ALTER TABLE es EXCHANGE PARTITION p3 WITH TABLE es2;
2ERROR 1704 (HY000): Subpartitioned table, use subpartition instead of partition

테이블 구조는 엄격한 방식으로 비교됩니다. 파티션된 테이블과 비파티션 테이블의 열 및 인덱스의 개수, 순서, 이름, 타입이 정확히 일치해야 합니다. 추가로, 두 테이블 모두 동일한 스토리지 엔진을 사용해야 합니다:

1mysql> CREATE TABLE es3 LIKE e;
2Query OK, 0 rows affected (1.31 sec)
3
4mysql> ALTER TABLE es3 REMOVE PARTITIONING;
5Query OK, 0 rows affected (0.53 sec)
6Records: 0  Duplicates: 0  Warnings: 0
7
8mysql> SHOW CREATE TABLE es3\G
9*************************** 1. row ***************************
10       Table: es3
11Create Table: CREATE TABLE `es3` (
12  `id` int(11) NOT NULL,
13  `fname` varchar(30) DEFAULT NULL,
14  `lname` varchar(30) DEFAULT NULL
15) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
161 row in set (0.00 sec)
17
18mysql> ALTER TABLE es3 ENGINE = MyISAM;
19Query OK, 0 rows affected (0.15 sec)
20Records: 0  Duplicates: 0  Warnings: 0
21
22mysql> ALTER TABLE es EXCHANGE PARTITION p3sp0 WITH TABLE es3;
23ERROR 1497 (HY000): The mix of handlers in the partitions is not allowed in this version of MySQL

이 예제에서의 ALTER TABLE ... ENGINE ... 문은 그 이전의 ALTER TABLE이 테이블 es3에서 파티셔닝을 제거했기 때문에 동작합니다.

26.3.3 Exchanging Partitions and Subpartitions with Tables

26.3.3 테이블과의 파티션 및 서브파티션 교환

Nonpartitioned Table과의 파티션 교환

Nonmatching Rows

Row-By-Row Validation 없이 파티션 교환

Nonpartitioned Table과의 서브파티션 교환