MySQL5.6 파티션과 서브 파티션 테이블로 교체

19.3.3 파티션과 서브 파티션 테이블로 교체

MySQL 5.6에서는 ALTER TABLE pt EXCHANGE PARTITION p WITH TABLE nt 를 사용하여 테이블 파티션 또는 서브 파티션 테이블과 교환 할 수 있습니다. 여기서 pt 는 분할 된 테이블, p 는 분할되지 않은 테이블 nt 로 교체 pt 파티션 또는 서브 파티션입니다 (다음 설명이 true 인 경우).

테이블 nt 자체는 분할되지 않는다.
테이블 nt 임시 테이블이 아니다.
테이블 pt 및 nt 의 구조는 그렇지 점에서 동일하다.
테이블 nt 는 외부 키 참조를 포함하지 않고, 다른 어떤 테이블도 nt 를 참조하는 외부 키를 가지지 않는다.
nt 내에 p 파티션 정의의 경계 밖에 존재하는 행이 없다.

ALTER TABLE 문에 일반적으로 필요한 ALTER , INSERT 및 CREATE 권한 이외에, 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 와 교환되는 분할되지 않은 테이블입니다.

ALTER TABLE pt 
    EXCHANGE PARTITION p 
    WITH TABLE nt;

단일 ALTER TABLE EXCHANGE PARTITION 문은 하나의 파티션 또는 서브 파티션 만 하나의 분할되지 않은 테이블 만 교체 할 수 있습니다. 여러 파티션 또는 서브 파티션을 교환하려면 여러 ALTER TABLE EXCHANGE PARTITION 문을 사용하십시오. EXCHANGE PARTITION 은 다른 ALTER TABLE 옵션과 함께 사용할 수 없습니다. 파티션 된 테이블에서 사용되는 파티셔닝 및 (해당하는 경우) 서브 파티셔닝은 MySQL 5.6에서 지원되는 모든 유형을 선택할 수 있습니다.

파티션을 분할되지 않은 테이블로 교체

다음 SQL 문을 사용하여 파티션 된 테이블 e 를 만들고 이입되어 있다고합니다.

CREATE TABLE e (
    id INT NOT NULL,
    fname VARCHAR(30),
    lname VARCHAR(30)
)
    PARTITION BY RANGE (id) (
        PARTITION p0 VALUES LESS THAN (50),
        PARTITION p1 VALUES LESS THAN (100),
        PARTITION p2 VALUES LESS THAN (150),
        PARTITION p3 VALUES LESS THAN (MAXVALUE)
);

INSERT INTO e VALUES 
    (1669, "Jim", "Smith"),
    (337, "Mary", "Jones"),
    (16, "Frank", "White"),
    (2005, "Linda", "Black");

여기서, e2 라는, e 의 분할되지 않은 복사본을 만듭니다. 이것은 mysql 클라이언트를 사용하여 다음과 같이 할 수 있습니다.

mysql> CREATE TABLE e2 LIKE e;
Query OK, 0 rows affected (1.34 sec)

mysql> ALTER TABLE e2 REMOVE PARTITIONING;
Query OK, 0 rows affected (0.90 sec)
Records: 0  Duplicates: 0  Warnings: 0

테이블 e 어떤 파티션에 행이 포함되는지는 다음과 같이 INFORMATION_SCHEMA.PARTITIONS 테이블을 쿼리하여 확인할 수 있습니다.

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

참고

파티션 된 InnoDB 테이블의 경우 INFORMATION_SCHEMA.PARTITIONS 테이블 TABLE_ROWS 컬럼에 표시되는 행수는 SQL 최적화에 사용되는 예상 값이며, 항상 정확하게는 아닙니다.

테이블 e 의 파티션 p0 테이블 e2 로 교체하려면 다음과 같은 ALTER TABLE 문을 사용할 수 있습니다.

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

더 정확히 말하면, 여기에서 발행 한 문은 파티션에서 발견 행이 테이블에서 찾을 것으로 교체됩니다. 이것이 어떻게 행해졌는지는 예전처럼 INFORMATION_SCHEMA.PARTITIONS 테이블을 조회하여 관찰 할 수 있습니다. 파티션 p0 에서 이전 발견 된 테이블 행이 존재하지 않게되어 있습니다.

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

테이블 e2 를 조회하면 "누락 된"행이 거기에서 찾을 수 있습니다.

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

파티션과 교환되는 테이블은 반드시 비어 필요가 없습니다. 이를 입증하기 위해 먼저 새 행을 테이블 e 에 삽입하고이 행이 파티션 p0 에 저장되어 있는지 확인합니다 (50 작은 id 컬럼 값을 선택하고이를 나중에 PARTITIONS 테이블을 조회하여 확인합니다).

                                                            
mysql> INSERT INTO e VALUES (41, "Michael", "Green");              
Query OK, 1 row affected (0.05 sec)                                

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

여기에서 전과 같은 ALTER TABLE 문을 사용하여 다시 파티션 p0 테이블 e2 로 교체합니다.

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

다음 쿼리의 출력은 ALTER TABLE 문을 발행하기 전에 파티션 p0 에 저장되어 있던 테이블 행 및 테이블 e2 에 저장되어 있던 테이블 행의 배치가 바뀐 것을 보여줍니다.

mysql> SELECT * FROM e;
+------+-------+-------+
| id   | fname | lname |
+------+-------+-------+
|   16 | Frank | White |
| 1669 | Jim   | Smith |
|  337 | Mary  | Jones |
| 2005 | Linda | Black |
+------+-------+-------+
4 rows in set (0.00 sec)

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

mysql> SELECT * FROM e2;
+----+---------+-------+
| id | fname   | lname |
+----+---------+-------+
| 41 | Michael | Green |
+----+---------+-------+
1 row in set (0.00 sec)

일치하지 않는 행

ALTER TABLE ... EXCHANGE PARTITION 문을 발행하기 전에 분할되지 않은 테이블에서 찾을 줄은 그들이 대상 파티션에 저장되는 데 필요한 조건을 충족해야하며, 그렇지 않은 경우는 문 가 실패하는 것을 기억하십시오. 이것이 어떻게 발생 하는지를 확인하기 위해 먼저 테이블 e 파티션 p0 파티션 정의의 경계 외부의 행을 e2 에 삽입합니다. 예를 들어, id 컬럼 값이 너무 행을 삽입 한 다음 테이블을 파티션으로 다시 교환 해보십시오.

mysql> INSERT INTO e2 VALUES (51, "Ellen", "McDonald");
Query OK, 1 row affected (0.08 sec)

mysql> ALTER TABLE e EXCHANGE PARTITION p0 WITH TABLE e2;
ERROR 1707 (HY000): Found row that does not match the partition

IGNORE 키워드는 사용할 수 있지만, 다음과 같이 EXCHANGE PARTITION 에서 사용될 때 효과가 없습니다.

mysql> ALTER IGNORE TABLE e EXCHANGE PARTITION p0 WITH TABLE e2;
ERROR 1707 (HY000): Found row that does not match the partition

서브 파티션을 분할되지 않은 테이블로 교체

ALTER TABLE ... EXCHANGE PARTITION 문을 사용하여 하위 파티션 된 테이블의 서브 파티션 ( 섹션 19.2.6 "서브 파티셔닝" 를 참조하십시오)을 분할되지 않은 테이블로 교체 할 수도 수 있습니다. 다음 예제에서는 먼저 RANGE 로 파티션되어 KEY 에 의해 서브 파티션 된 테이블 es 를 만들고 테이블 e 와 마찬가지로이 테이블에 이입하고이 테이블 빈 분할되지 않은 복사 es2 를 만듭니다.

mysql> CREATE TABLE es (
    ->     id INT NOT NULL,
    ->     fname VARCHAR(30),
    ->     lname VARCHAR(30)
    -> )
    ->     PARTITION BY RANGE (id)
    ->     SUBPARTITION BY KEY (lname)
    ->     SUBPARTITIONS 2 (
    ->         PARTITION p0 VALUES LESS THAN (50),
    ->         PARTITION p1 VALUES LESS THAN (100),
    ->         PARTITION p2 VALUES LESS THAN (150),
    ->         PARTITION p3 VALUES LESS THAN (MAXVALUE)
    ->     );
Query OK, 0 rows affected (2.76 sec)

mysql> INSERT INTO es VALUES
    ->     (1669, "Jim", "Smith"),
    ->     (337, "Mary", "Jones"),
    ->     (16, "Frank", "White"),
    ->     (2005, "Linda", "Black");
Query OK, 4 rows affected (0.04 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> CREATE TABLE es2 LIKE es;
Query OK, 0 rows affected (1.27 sec)

mysql> ALTER TABLE es2 REMOVE PARTITIONING;
Query OK, 0 rows affected (0.70 sec)
Records: 0  Duplicates: 0  Warnings: 0

테이블 es 를 만들 때 서브 파티션의 이름을 명시 적으로 지정하지 않았더라도, PARTITIONS 테이블에서 선택하면 다음과 같이 INFORMATION_SCHEMA 에서 테이블의 SUBPARTITION_NAME 을 캡처하여 그 생성 된 이름 를 얻을 수 있습니다.

mysql> SELECT PARTITION_NAME, SUBPARTITION_NAME, TABLE_ROWS
    ->     FROM INFORMATION_SCHEMA.PARTITIONS
    ->     WHERE TABLE_NAME = 'es';
+----------------+-------------------+------------+
| PARTITION_NAME | SUBPARTITION_NAME | TABLE_ROWS |
+----------------+-------------------+------------+
| p0             | p0sp0             |          1 |
| p0             | p0sp1             |          0 |
| p1             | p1sp0             |          0 |
| p1             | p1sp1             |          0 |
| p2             | p2sp0             |          0 |
| p2             | p2sp1             |          0 |
| p3             | p3sp0             |          3 |
| p3             | p3sp1             |          0 |
+----------------+-------------------+------------+
8 rows in set (0.00 sec)

다음 ALTER TABLE 문은 테이블 es 서브 파티션 p3sp0 을 분할되지 않은 테이블 es2 로 교체합니다.

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

다음 쿼리를 발행하여 그 행이 교환 된 것을 확인할 수 있습니다.

mysql> SELECT PARTITION_NAME, SUBPARTITION_NAME, TABLE_ROWS
    ->     FROM INFORMATION_SCHEMA.PARTITIONS
    ->     WHERE TABLE_NAME = 'es';
+----------------+-------------------+------------+
| PARTITION_NAME | SUBPARTITION_NAME | TABLE_ROWS |
+----------------+-------------------+------------+
| p0             | p0sp0             |          1 |
| p0             | p0sp1             |          0 |
| p1             | p1sp0             |          0 |
| p1             | p1sp1             |          0 |
| p2             | p2sp0             |          0 |
| p2             | p2sp1             |          0 |
| p3             | p3sp0             |          0 |
| p3             | p3sp1             |          0 |
+----------------+-------------------+------------+
8 rows in set (0.00 sec)

mysql> SELECT * FROM es2;
+------+-------+-------+
| id   | fname | lname |
+------+-------+-------+
| 1669 | Jim   | Smith |
|  337 | Mary  | Jones |
| 2005 | Linda | Black |
+------+-------+-------+
3 rows in set (0.00 sec)

테이블이 서브 분할되는 경우 다음과 같이 분할되지 않은 테이블과 교환 할 수있는 테이블의 파티션 전체가 아닌 하위 파티션뿐입니다.

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

MySQL에서 사용되는 테이블 구조의 비교는 매우 엄격합니다. 컬럼의 수, 순서, 이름 및 형태, 또한 분할 된 테이블과 분할되지 않은 테이블의 인덱스가 정확히 일치해야합니다. 또한 두 테이블이 동일한 스토리지 엔진을 사용하고있을 필요가 있습니다.

mysql> CREATE TABLE es3 LIKE e;
Query OK, 0 rows affected (1.31 sec)

mysql> ALTER TABLE es3 REMOVE PARTITIONING;
Query OK, 0 rows affected (0.53 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> SHOW CREATE TABLE es3\G
*************************** 1. row ***************************
       Table: es3
Create Table: CREATE TABLE `es3` (
  `id` int(11) NOT NULL,
  `fname` varchar(30) DEFAULT NULL,
  `lname` varchar(30) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

mysql> ALTER TABLE es3 ENGINE = MyISAM;
Query OK, 0 rows affected (0.15 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> ALTER TABLE es EXCHANGE PARTITION p3sp0 WITH TABLE es3;
ERROR 1497 (HY000): The mix of handlers in the partitions is not allowed in this version of MySQL