MySQL5.6 RANGE 및 LIST 파티션 관리

19.3.1 RANGE 및 LIST 파티션 관리

RANGE 및 LIST 파티션은 파티션의 추가 및 삭제가 어떻게 처리되는지에 관해서는 비슷합니다. 따라서이 섹션에서는 두 가지 유형의 파티션 관리에 대해 설명합니다. 해시 또는 키를 파티션 된 테이블의 관리에 대해서는 섹션 19.3.2 "HASH 및 KEY 파티션 관리" 를 참조하십시오. RANGE 또는 LIST 파티션 삭제는 추가보다 단순하기 때문에 이것을 먼저 설명합니다.

RANGE 또는 LIST 로 파티션 된 테이블에서 파티션을 삭제하는 작업은 DROP PARTITION 절과 함께 ALTER TABLE 문을 사용하여 실행할 수 있습니다. 아주 기본적인 예제이지만 다음의 CREATE TABLE 및 INSERT 문을 사용하여 범위로 파티션 된 테이블을 이미 작성되어 10 개의 레코드를 채울 수 있다고합니다.

mysql> CREATE TABLE tr (id INT, name VARCHAR(50), purchased DATE)
    ->     PARTITION BY RANGE( YEAR(purchased) ) (
    ->         PARTITION p0 VALUES LESS THAN (1990),
    ->         PARTITION p1 VALUES LESS THAN (1995),
    ->         PARTITION p2 VALUES LESS THAN (2000),
    ->         PARTITION p3 VALUES LESS THAN (2005)
    ->     );
Query OK, 0 rows affected (0.01 sec)

mysql> INSERT INTO tr VALUES
    ->     (1, 'desk organiser', '2003-10-15'),
    ->     (2, 'CD player', '1993-11-05'),
    ->     (3, 'TV set', '1996-03-10'),
    ->     (4, 'bookcase', '1982-01-10'),
    ->     (5, 'exercise bike', '2004-05-09'),
    ->     (6, 'sofa', '1987-06-05'),
    ->     (7, 'popcorn maker', '2001-11-22'),
    ->     (8, 'aquarium', '1992-08-04'),
    ->     (9, 'study desk', '1984-09-16'),
    ->     (10, 'lava lamp', '1998-12-25');
Query OK, 10 rows affected (0.01 sec)

파티션 p2 에 삽입되어 있어야 할 항목을 다음과 같이 확인할 수 있습니다.

mysql> SELECT * FROM tr
    -> WHERE purchased BETWEEN '1995-01-01' AND '1999-12-31';
+------+-----------+------------+
| id   | name      | purchased  |
+------+-----------+------------+
|    3 | TV set    | 1996-03-10 |
|   10 | lava lamp | 1998-12-25 |
+------+-----------+------------+
2 rows in set (0.00 sec)

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

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

참고

NDB 스토리지 엔진은 ALTER TABLE ... DROP PARTITION 을 지원하지 않습니다. 그러나이 장에서 설명되는 ALTER TABLE 에 다른 파티션 관련 확장을 지원하고 있습니다.

파티션을 삭제하면 해당 파티션에 저장되어 있던 모든 데이터가 삭제되는 것을 기억하는 것이 매우 중요합니다. 이전 SELECT 쿼리를 다시 실행하여 이것이 진실임을 확인할 수 있습니다.

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

따라서 테이블에 ALTER TABLE ... DROP PARTITION 을 실행하려면 그 테이블의 DROP 권한이 필요합니다.

테이블 정의 및 파티셔닝 구성표를 유지하면서 모든 파티션에서 모든 데이터를 삭제하려면 TRUNCATE TABLE 문을 사용하십시오. ( 섹션 13.1.33 "TRUNCATE TABLE 구문" 을 참조하십시오).

데이터를 잃지 않고 테이블의 파티셔닝을 변경하려면 대신 ALTER TABLE ... REORGANIZE PARTITION 을 사용하십시오. REORGANIZE PARTITION 내용은 후속 설명 또는 섹션 13.1.7 "ALTER TABLE 구문" 을 참조하십시오.

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

mysql> SHOW CREATE TABLE tr\G
*************************** 1. row ***************************
       Table: tr
Create Table: CREATE TABLE `tr` (
  `id` int(11) default NULL,
  `name` varchar(50) default NULL,
  `purchased` date default NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
PARTITION BY RANGE ( YEAR(purchased) ) (
  PARTITION p0 VALUES LESS THAN (1990) ENGINE = MyISAM,
  PARTITION p1 VALUES LESS THAN (1995) ENGINE = MyISAM,
  PARTITION p3 VALUES LESS THAN (2005) ENGINE = MyISAM
)
1 row in set (0.01 sec)

purchased 컬럼 값이 '1995-01-01' 에서 '2004-12-31' 까지 (포함)의 새로운 라인을 수정 된 테이블에 삽입하면 그 행은 파티션 p3 에 저장됩니다. 이 것을 다음과 같이 확인할 수 있습니다.

mysql> INSERT INTO tr VALUES (11, 'pencil holder', '1995-07-12');
Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM tr WHERE purchased
    -> BETWEEN '1995-01-01' AND '2004-12-31';
+------+----------------+------------+
| id   | name           | purchased  |
+------+----------------+------------+
|   11 | pencil holder  | 1995-07-12 |
|    1 | desk organiser | 2003-10-15 |
|    5 | exercise bike  | 2004-05-09 |
|    7 | popcorn maker  | 2001-11-22 |
+------+----------------+------------+
4 rows in set (0.00 sec)

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

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

ALTER TABLE ... DROP PARTITION 결과로 테이블에서 삭제 된 행 수는 동등한 DELETE 쿼리와 달리 서버에서보고되지 않습니다.

LIST 파티션을 삭제하려면 RANGE 파티션 제거에 사용하는 것과 동일한 ALTER TABLE ... DROP PARTITION 구문을 사용합니다. 그러나이 작업이 갖는 영향에 대해이 테이블을 나중에 사용할 때 중요한 차이가 하나 있습니다. 이 테이블에는 삭제 된 파티션을 정의하는 값 목록에 포함 된 값을 가진 행을 삽입 할 수 없습니다. (예를 들어, 섹션 19.2.2 "LIST 파티셔닝" 를 참조하십시오).

이미 파티션 된 테이블에 새로운 범위 또는 목록 파티션을 추가하려면 ALTER TABLE ... ADD PARTITION 문을 사용합니다. RANGE 로 파티션 된 테이블의 경우이를 사용하여 기존의 파티션 목록의 마지막에 새로운 영역을 추가 할 수 있습니다. 다음과 같이 정의 된 조직의 구성원 데이터가 포함 된 분할 된 테이블이 있다고합니다.

CREATE TABLE members (
    id INT,
    fname VARCHAR(25),
    lname VARCHAR(25),
    dob DATE
)
PARTITION BY RANGE( YEAR(dob) ) (
    PARTITION p0 VALUES LESS THAN (1970),
    PARTITION p1 VALUES LESS THAN (1980),
    PARTITION p2 VALUES LESS THAN (1990)
);

또한, 회원의 최소 연령은 16 세이다 고합니다. 달력이 2005 년 말에 다가 1990 년에 태어난 멤버 (또한 내년 이후는 그보다 뒤의 멤버)을 곧 받아 들일 것을 알게됩니다. 다음과 같이 members 테이블을 변경하는 것으로, 1990 년부터 1999 년까지 태어난 새로운 회원을 받아 들일 수 있습니다.

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

범위로 파티션 된 테이블에서 ADD PARTITION 을 사용하면 파티션 목록의 상단에만 새 파티션을 추가 할 수 있습니다. 이 방법으로 새 파티션을 기존의 파티션 사이 또는 이전에 추가하려고하면 다음과 같은 오류가 발생합니다.

mysql> ALTER TABLE members
     >     ADD PARTITION (
     >     PARTITION n VALUES LESS THAN (1960));
ERROR 1463 (HY000): VALUES LESS THAN value must be strictly »
   increasing for each partition

이 문제는 다음과 같이 첫 번째 파티션을 2 개로 개편하고 그들 사이의 범위를 분할하여 해결할 수 있습니다.

ALTER TABLE members
    REORGANIZE PARTITION p0 INTO (
        PARTITION n0 VALUES LESS THAN (1960),
        PARTITION n1 VALUES LESS THAN (1970)
);

SHOW CREATE TABLE 을 사용하여 ALTER TABLE 문이 의도 한 효과를 얻을 수있는 것을 확인할 수 있습니다.

mysql> SHOW CREATE TABLE members\G
*************************** 1. row ***************************
       Table: members
Create Table: CREATE TABLE `members` (
  `id` int(11) DEFAULT NULL,
  `fname` varchar(25) DEFAULT NULL,
  `lname` varchar(25) DEFAULT NULL,
  `dob` date DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1
/*!50100 PARTITION BY RANGE ( YEAR(dob))
(PARTITION n0 VALUES LESS THAN (1960) ENGINE = InnoDB,
 PARTITION n1 VALUES LESS THAN (1970) ENGINE = InnoDB,
 PARTITION p1 VALUES LESS THAN (1980) ENGINE = InnoDB,
 PARTITION p2 VALUES LESS THAN (1990) ENGINE = InnoDB,
 PARTITION p3 VALUES LESS THAN (2000) ENGINE = InnoDB) */
1 row in set (0.00 sec)

섹션 13.1.7.1 "ALTER TABLE 파티션 작업" 을 참조하십시오.

ALTER TABLE ... ADD PARTITION 을 사용하여 LIST 로 파티션 된 테이블에 새 파티션을 추가 할 수 있습니다. 다음 CREATE TABLE 문을 사용하여 테이블 tt 가 정의되어 있다고합니다.

CREATE TABLE tt (
    id INT,
    data INT
)
PARTITION BY LIST(data) (
    PARTITION p0 VALUES IN (5, 10, 15),
    PARTITION p1 VALUES IN (6, 12, 18)
);

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

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

기존의 파티션 값 목록에 이미 포함되어있는 값을 포함한 새로운 LIST 파티션은 추가 할 수 없습니다. 이를 시도하면 오류가 발생합니다.

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

data 컬럼 값이 12 인 행이 파티션 p1 에 이미 할당 된 때문에 값 목록에 12 가 포함 된 새 파티션 테이블 tt 만들 수 없습니다. 이를 위해 p1 을 제거하고 np 를 추가하고 정의를 변경 한 새로운 p1 을 추가 할 수 있습니다. 그러나 이미 설명했듯이, 이에 따라 p1 에 저장되어 있던 모든 데이터가 손실되기 때문에 이것이 실제로하고 싶은 것이 아니다 것이 많습니다. 다른 해결책이 될 수있는 것이 CREATE TABLE ... SELECT ... 를 사용하여 새 파티션 된 테이블의 복사본을 만들고 데이터를 거기에 복사하고 오래된 테이블 를 삭제하고 새 테이블 이름을 변경하는 것입니다 만, 이것은 대량의 데이터를 처리 할 때 많은 시간이 걸릴 수 있습니다. 고 가용성이 요구되는 상황에서는 실행되지 않을 수 있습니다.

다음과 같이 단일 ALTER TABLE ... ADD PARTITION 문에서 여러 파티션을 추가 할 수 있습니다.

CREATE TABLE employees (
  id INT NOT NULL,
  fname VARCHAR(50) NOT NULL,
  lname VARCHAR(50) NOT NULL,
  hired DATE NOT NULL
)
PARTITION BY RANGE( YEAR(hired) ) (
  PARTITION p1 VALUES LESS THAN (1991),
  PARTITION p2 VALUES LESS THAN (1996),
  PARTITION p3 VALUES LESS THAN (2001),
  PARTITION p4 VALUES LESS THAN (2005)
);

ALTER TABLE employees ADD PARTITION (
    PARTITION p5 VALUES LESS THAN (2010),
    PARTITION p6 VALUES LESS THAN MAXVALUE
);

다행히, MySQL의 파티셔닝 구현은 데이터를 손실없이 파티션을 다시 정의하는 방법을 제공합니다. 먼저 RANGE 파티셔닝을 사용하는 몇 가지 간단한 예를 살펴 보자. 다음과 같이 정의 된 members 테이블을 기억하십시오.

mysql> SHOW CREATE TABLE members\G
*************************** 1. row ***************************
       Table: members
Create Table: CREATE TABLE `members` (
  `id` int(11) default NULL,
  `fname` varchar(25) default NULL,
  `lname` varchar(25) default NULL,
  `dob` date default NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
PARTITION BY RANGE ( YEAR(dob) ) (
  PARTITION p0 VALUES LESS THAN (1970) ENGINE = MyISAM,
  PARTITION p1 VALUES LESS THAN (1980) ENGINE = MyISAM,
  PARTITION p2 VALUES LESS THAN (1990) ENGINE = MyISAM.
  PARTITION p3 VALUES LESS THAN (2000) ENGINE = MyISAM
)

1960 년 이전에 태어난 멤버를 나타내는 모든 행을 다른 파티션으로 이동합니다. 이미 설명했듯이, 이것은 ALTER TABLE ... ADD PARTITION 을 사용하여 수행 할 수 없습니다. 그러나 ALTER TABLE 에 다른 파티션 관련 확장을 사용하여 작업을 수행 할 수 있습니다.

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

사실,이 명령은 파티션 p0 을 2 개의 새 파티션 s0 와 s1 에 분할합니다. 또한 p0 에 저장되어 있던 데이터를 2 개의 PARTITION ... VALUES ... 절에 나와있는 규칙에 따라 새 파티션으로 이동하는 결과 s0 에는 YEAR(dob) 가 1960보다 작은 레코드 만 포함 되어 s1 은 YEAR(dob) 가 1960 이상 1970 미만의 행이 포함됩니다.

REORGANIZE PARTITION 절을 사용하여 인접한 파티션을 병합 할 수 있습니다. 다음과 같이 members 테이블을 이전 파티션을 복원 할 수 있습니다.

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

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

REORGANIZE PARTITION 일반적인 구문을 보여줍니다.

ALTER TABLE tbl_name
    REORGANIZE PARTITION partition_list
    INTO (partition_definitions);

여기에서 tbl_name 은 파티션 된 테이블의 이름 partition_list 변경하는 하나 이상의 기존 파티션의 이름의 쉼표로 구분 된 목록입니다. partition_definitions 은 CREATE TABLE 에서 사용되는 partition_definitions 목록 ( 섹션 13.1.17 "CREATE TABLE 구문" 을 참조하십시오)과 같은 규칙에 따라 새 파티션 정의의 쉼표로 구분 된 목록입니다. REORGANIZE PARTITION 을 사용하면 여러 파티션을 하나로 병합하거나 하나의 파티션을 다수로 분할 이외의 수 있습니다. 예를 들어, 다음과 같이 members 테이블 4 개의 모든 파티션을 2 개로 재구성 할 수 있습니다.

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

LIST 로 파티션 된 테이블에서 REORGANIZE PARTITION 을 사용할 수 있습니다. 목록에 의해 분할 된 tt 테이블에 새로운 파티션을 추가하는 작업이 기존의 파티션 중 하나의 값 목록에 이미 존재하는 값이 새 파티션에 포함되어있는 것이 원인으로 실패하는 문제로 돌아갑니다. 이것은 충돌하지 않는 값만 포함 된 파티션을 추가 한 후 새로운 파티션과 기존의 것을 재구성하고 기존에 저장되어 있던 값이 새것에 이동하도록하여 대처 수 있습니다.

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

RANGE 또는 LIST 에 의해 분할 된 테이블을 분할하고 재설치하기 위해 ALTER TABLE ... REORGANIZE PARTITION 을 사용할 때주의해야 할 몇 가지 중요한 점을 보여줍니다.

새로운 파티셔닝 구성표를 결정하는 데 사용되는 PARTITION 절은 CREATE TABLE 문에서 사용되는 것과 동일한 규칙이 적용됩니다.

가장 중요한 것은 새로운 파티셔닝 구성표에는 중복 범위 ( RANGE 로 파티션 된 테이블에 적용되는) 또는 값 세트 ( LIST 로 파티션 된 테이블을 재구성 할 때)을 지정할 수 없다 것을 기억해야합니다.
partition_definitions 목록 파티션 조합은 partition_list 에 지정된 파티션 조합의 범위 또는 값 세트 전체와 동일해야합니다.

예를 들어,이 섹션의 예제로 사용되는 members 테이블에서 파티션 p1 및 p2 가 1980 년부터 1999 년까지를 범위로하고 있습니다. 따라서이 두 파티션을 재구성하는 경우는 전체로 같은 해 범위를 포함한다.
RANGE 로 파티션 된 테이블의 경우, 인접한 파티션만을 재구성 할 수 있습니다. 범위 파티션을 비행 할 수 없습니다.

예를 들어,이 섹션의 예제로 사용되는 members 테이블을 ALTER TABLE members REORGANIZE PARTITION p0,p2 INTO ... 로 시작하는 문을 사용하여 재구성 할 수 없습니다. p0 는 1970 년 이전 연도를 범위로하고, p2 는 1990 년부터 1999 년까지 (포함)를 범위로하고,이 2 개의 인접한 파티션이 없기 때문입니다.
REORGANIZE PARTITION 을 사용하여 테이블의 파티션 유형을 변경할 수 없습니다. 즉, 예를 들어, RANGE 파티션을 HASH 파티션으로 변경할 수 없습니다 (반대의 경우도 불가). 이 명령을 사용하여 분할 식 또는 컬럼을 변경할 수 없습니다. 이러한 작업을 테이블을 삭제하고 다시 만들 필요없이 실현하기 위해 ALTER TABLE ... PARTITION BY ... 를 사용할 수 있습니다. 예 :
```
ALTER TABLE members
    PARTITION BY HASH( YEAR(dob) )
    PARTITIONS 8;
```