[MySQL] Table Partitioning - 파티셔닝 관리

인프라쟁이 DBA
Databases/MySQL
2020. 8. 22.

■ 파티션 관리

MySQL 5.7은 분할된 테이블을 수정하는 여러 가지 방법을 제공합니다. 기존 파티션을 추가, 삭제, 재정의, 병합 또는 분할할 수 있습니다. 이러한 모든 조치는 ALTER TABLE 문에 대한 파티션 확장을 사용하여 수행 할 수 있습니다. 파티션된 테이블 및 파티션에 대한 정보를 얻는 방법도 있습니다. 다음 섹션에서 이러한 주제에 대해 설명합니다.

노트

MySQL 5.7에서 파티션된 테이블의 모든 파티션은 같은 수의 서브 파티션을 가져야하며 테이블이 생성되면 서브 파티션을 변경할 수 없습니다.

테이블의 파티셔닝 구성표를 변경하려면 partition_options절과 함께 ALTER TABLE문만 사용해야 합니다. 이 절은 파티션된 테이블을 작성하기 위해 CREATE TABLE에서 사용된것과 동일한 구문을 가지며 항상 키워드 PARTITION BY로 시작합니다. 다음 CREATE TABLE 문을 사용하여 범위별로 파티션된 테이블이 있다고 가정합니다.

CREATE TABLE trb3 (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)
    );

이 테이블을 키의 기준으로 id열 값을 사용하여 키별로 두개의 파티션으로 파티션되도록 파티션을 분할하려면 다음 명령문을 사용할 수 있습니다.

이는 테이블을 삭제하고 키(id)파티션 2에 의한 CREATE Table trb3 Partition by KEY(id) Partitions 2를 사용하여 다시 생성하는 것과 동일한데 이런 DDL은 테이블 구조에 영향을 미칩니다.

ALTER TABLE ... ENGINE = ... 문법은 테이블에서 사용하는 스토리지 엔진만 변경하고 테이블의 파티셔닝 구성표는 그대로 둡니다. ALTER TABLE ... REMOVE PARTITIONING을 사용하여 테이블의 파티셔닝을 제거할 수 있습니다.

중요사항

지정된 ALTER TABLE 문에는 단일 PARTITION BY, ADD PARTITION, DROP PARTITION, REORGANIZE PARTITION 또는 COALESCE PARTITION절만 사용할 수 있습니다. 예를 들어 파티션을 삭제하고 테이블의 나머지 파티션을 재구성하려면 두개의 별도 ALTER TABLE 문 (하나는 DROP PARTITION을 사용하고 다른 하나는 REORGANIZE PARTITION을 사용)에서 수행해야합니다.

MySQL 5.7에서는 ALTER TABLE ... TRUNCATE PARTITION을 사용하여 하나 이상의 선택된 파티션에서 모든 행을 삭제할 수 있습니다.

■ Management of RANGE and LIST Partitions

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),
    ->         PARTITION p4 VALUES LESS THAN (2010),
    ->         PARTITION p5 VALUES LESS THAN (2015)
    ->     );
Query OK, 0 rows affected (0.28 sec)

mysql> INSERT INTO tr VALUES
    ->     (1, 'desk organiser', '2003-10-15'),
    ->     (2, 'alarm clock', '1997-11-05'),
    ->     (3, 'chair', '2009-03-10'),
    ->     (4, 'bookcase', '1989-01-10'),
    ->     (5, 'exercise bike', '2014-05-09'),
    ->     (6, 'sofa', '1987-06-05'),
    ->     (7, 'espresso maker', '2011-11-22'),
    ->     (8, 'aquarium', '1992-08-04'),
    ->     (9, 'study desk', '2006-09-16'),
    ->     (10, 'lava lamp', '1998-12-25');
Query OK, 10 rows affected (0.05 sec)
Records: 10  Duplicates: 0  Warnings: 0

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

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

다음과 같이 파티션 선택을 사용하여 이 정보를 얻을 수도 있습니다.

mysql> SELECT * FROM tr PARTITION (p2);
+------+-------------+------------+
| id   | name        | purchased  |
+------+-------------+------------+
|    2 | alarm clock | 1997-11-05 |
|   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)

노트

NDBCLUSTER 스토리지 엔진은 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 문을 사용합니다.

데이터 손실없이 테이블의 파티셔닝을 변경하려면 ALTER TABLE ... REORGANIZE PARTITION을 대신 사용합니다.

이제 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=InnoDB DEFAULT CHARSET=latin1
/*!50100 PARTITION BY RANGE ( YEAR(purchased))
(PARTITION p0 VALUES LESS THAN (1990) ENGINE = InnoDB,
 PARTITION p1 VALUES LESS THAN (1995) ENGINE = InnoDB,
 PARTITION p3 VALUES LESS THAN (2005) ENGINE = InnoDB,
 PARTITION p4 VALUES LESS THAN (2010) ENGINE = InnoDB,
 PARTITION p5 VALUES LESS THAN (2015) ENGINE = InnoDB) */
1 row in set (0.00 sec)

'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  |
+------+----------------+------------+
|    1 | desk organiser | 2003-10-15 |
|   11 | pencil holder  | 1995-07-12 |
+------+----------------+------------+
2 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 구문이 사용됩니다. 그러나 이후 테이블 사용에 미치는 영향에는 한가지 중요한 차이점이 있습니다. 삭제된 파티션을 정의하는 값 목록에 포함된 값이있는 행을 더 이상 테이블에 삽입 할 수 없습니다.

이전에 파티션된 테이블에 새 범위 또는 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 (1980),
    PARTITION p1 VALUES LESS THAN (1990),
    PARTITION p2 VALUES LESS THAN (2000)
);

회원의 최소 연령은 16 세라고 가정합니다. 달력이 2015년 말에 가까워짐에 따라 2000년 이후에 태어난 회원이 곧 입학할 수 있음을 알게됩니다. 다음과 같이 2000년에서 2010년 사이에 태어난 새 회원을 수용하도록 구성원 테이블을 수정할 수 있습니다.

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

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

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

첫 번째 파티션을 다음과 같이 범위를 분할하는 두 개의 새로운 파티션으로 재구성하여이 문제를 해결할 수 있습니다.

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

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 (1970) ENGINE = InnoDB,
 PARTITION n1 VALUES LESS THAN (1980) ENGINE = InnoDB,
 PARTITION p1 VALUES LESS THAN (1990) ENGINE = InnoDB,
 PARTITION p2 VALUES LESS THAN (2000) ENGINE = InnoDB,
 PARTITION p3 VALUES LESS THAN (2010) ENGINE = InnoDB) */
1 row in set (0.00 sec)
See also Section 13.1.8.1, “ALTER TABLE Partition Operations”.

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

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)
);

그림과 같이 데이터 열 값이 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

데이터열 값이 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 파티셔닝과 관련된 몇가지 간단한 예를 먼저 살펴 보겠습니다. 이제 다음과 같이 정의 된 구성원 테이블을 있다고 가정합니다.

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 (1970) ENGINE = InnoDB,
 PARTITION n1 VALUES LESS THAN (1980) ENGINE = InnoDB,
 PARTITION p1 VALUES LESS THAN (1990) ENGINE = InnoDB,
 PARTITION p2 VALUES LESS THAN (2000) ENGINE = InnoDB,
 PARTITION p3 VALUES LESS THAN (2010) ENGINE = InnoDB) */
1 row in set (0.00 sec)

1960년 이전에 태어난 멤버를 나타내는 모든 행을 별도의 파티션으로 이동한다고 가정합니다. 이미 살펴본 것처럼 ALTER TABLE ... ADD PARTITION을 사용하여이 작업을 수행할 수 없습니다. 그러나 ALTER TABLE의 다른 파티션 관련 확장을 사용하여 이를 수행 할 수 있습니다.

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

실제로 이 명령은 파티션 p0을 두개의 새 파티션 s0과 s1로 분할합니다. 또한 두 PARTITION ... VALUES ... 절에 구현된 규칙에 따라 p0에 저장된 데이터를 새 파티션으로 이동하므로 s0에는 YEAR (dob)가 1960보다 작은 레코드만 포함됩니다. s1에는 YEAR (dob)가 1960 이상이지만 1970 미만인 행이 포함됩니다.

인접한 파티션을 병합하기 위해 REORGANIZE PARTITION절을 사용할 수도 있습니다. 다음과 같이 멤버 테이블에 대한 이전 명령문의 효과를 되돌릴 수 있습니다.

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 목록과 동일한 규칙을 따릅니다. REORGANIZE PARTITION을 사용할때 여러 파티션을 하나로 병합하거나 한 파티션을 여러 파티션으로 분할하는 데 제한되지 않습니다. 예를 들어 다음과 같이 member 테이블의 네개의 파티션을 모두 두개로 재구성 할 수 있습니다.

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을 사용할 수도 있습니다. LIST로 파티션된 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)
);

ALTER TABLE ... REORGANIZE PARTITION을 사용하여 RANGE 또는 LIST로 파티션된 테이블을 다시 파티션할 때 명심해야 할 몇 가지 핵심 사항은 다음과 같습니다.

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

새로운 RANGE 파티셔닝 체계는 범위가 겹칠 수 없습니다. 새로운 LIST 파티셔닝 구성표는 겹치는 값 세트를 가질 수 없습니다.

+ partition_definitions 목록의 파티션 조합은 partition_list에 이름이 지정된 결합된 파티션과 동일한 범위 또는 값 세트를 고려해야합니다.

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

+ RANGE로 파티션된 테이블의 경우 인접한 파티션만 재구성 할 수 있습니다. 범위 파티션을 건너뛸 수 없습니다.

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

+ REORGANIZE PARTITION을 사용하여 테이블에서 사용되는 파티셔닝 유형을 변경할 수 없습니다 (예를 들어, RANGE 파티션을 HASH 파티션 또는 그 반대로 변경할 수 없음). 또한 이 명령문을 사용하여 파티셔닝 표현식 또는 컬럼을 변경할 수 없습니다. 테이블을 삭제하거나 다시 만들지 않고 이러한 작업중 하나를 수행하려면 다음과 같이 ALTER TABLE ... PARTITION BY ...를 사용할 수 있습니다.

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

■ HASH와 KEY 파티션의 관리

해시 또는 키로 파티션된 테이블은 파티션 설정 변경과 관련하여 서로 매우 유사하며 범위 또는 목록으로 파티션된 테이블과 여러 가지면에서 다릅니다. 따라서 이 섹션에서는 해시 또는 키 만으로 분할된 테이블 수정에 대해 설명합니다.

RANGE 또는 LIST로 파티션 된 테이블에서와 동일한 방식으로 HASH 또는 KEY로 파티션 된 테이블에서 파티션을 삭제할 수 없습니다. 그러나 ALTER TABLE ... COALESCE PARTITION문을 사용하여 HASH 또는 KEY파티션을 병합할 수 있습니다. 클라이언트에 대한 데이터가 포함된 테이블이 12개의 파티션으로 나누어져 있다고 가정합니다. 클라이언트 테이블은 다음과 같이 정의됩니다.

CREATE TABLE clients (
    id INT,
    fname VARCHAR(30),
    lname VARCHAR(30),
    signed DATE
)
PARTITION BY HASH( MONTH(signed) )
PARTITIONS 12;

파티션수를 12개에서 8개로 줄이려면 다음 ALTER TABLE 명령을 실행합니다.

mysql> ALTER TABLE clients COALESCE PARTITION 4;
Query OK, 0 rows affected (0.02 sec)

COALESCE는 HASH, KEY, LINEAR HASH 또는 LINEAR KEY로 분할된 테이블과 동일하게 작동합니다. 다음은 LINEAR KEY로 테이블을 분할한다는 점만 다르다는 점에서 이전 예제와 비슷한 예입니다.

mysql> CREATE TABLE clients_lk (
    ->     id INT,
    ->     fname VARCHAR(30),
    ->     lname VARCHAR(30),
    ->     signed DATE
    -> )
    -> PARTITION BY LINEAR KEY(signed)
    -> PARTITIONS 12;
Query OK, 0 rows affected (0.03 sec)

mysql> ALTER TABLE clients_lk COALESCE PARTITION 4;
Query OK, 0 rows affected (0.06 sec)
Records: 0  Duplicates: 0  Warnings: 0

COALESCE PARTITION 다음의 숫자는 나머지로 병합할 파티션의 수입니다. 즉, 테이블에서 제거할 파티션의 수입니다.

테이블에 정의된 파티션보다 많은 파티션을 제거하려고하면 다음과 같은 오류가 발생합니다.

mysql> ALTER TABLE clients COALESCE PARTITION 18;
ERROR 1478 (HY000): Cannot remove all partitions, use DROP TABLE instead

clients 테이블의 파티션 수를 12에서 18로 늘리려면 다음과 같이 ALTER TABLE ... ADD PARTITION을 사용하십시오.

ALTER TABLE clients ADD PARTITION PARTITIONS 6;

■ 테이블과 파티션 및 하위 파티션 교환

MySQL 5.7에서는 ALTER TABLE pt EXCHANGE PARTITION p WITH TABLE nt를 사용하여 테이블 파티션 또는 서브 파티션을 테이블과 교환할 수 있습니다. 여기서 pt는 파티션된 테이블이고 p는 파티션되지 않은 테이블 nt와 교환 할 pt의 파티션 또는 서브 파티션입니다. 단, 다음과 같은 문장이 참인 경우에 그렇습니다.:

1. 테이블 nt 자체는 파티션되지 않았습니다.

2. 테이블 nt는 임시 테이블이 아닙니다.

3. 테이블 pt와 nt의 구조는 달리 동일합니다.

4. 테이블 nt에는 외래키 참조가 없고 다른 테이블에는 nt를 참조하는 외래 키가 없습니다.

5. p에 대한 파티션 정의의 경계를 벗어나는 nt에는 행이 없습니다. WITHOUT VALIDATION 옵션이 사용 된 경우에는 이 조건이 적용되지 않습니다. [{WITH | WITHOUT} VALIDATION] 옵션은 MySQL 5.7.5에서 추가되었습니다.

6. InnoDB 테이블의 경우 두 테이블 모두 동일한 행 형식을 사용합니다. InnoDB 테이블의 행 형식을 확인하려면 INFORMATION_SCHEMA.INNODB_SYS_TABLES를 조회합니다.

7. nt에는 DATA DIRECTORY 옵션을 사용하는 파티션이 없습니다. 이 제한은 MySQL 5.7.25 이상에서 InnoDB 테이블에 대해 해제되었습니다.

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

또한 ALTER TABLE ... EXCHANGE PARTITION명령 다음 어떤일이 일어나는지에 대해서도 알고 있어야합니다.

+ ALTER TABLE ... EXCHANGE PARTITION을 실행하면 분할 된 테이블이나 교환 할 테이블에서 트리거가 호출되지 않습니다.

+ 교환 된 테이블의 AUTO_INCREMENT 컬럼이 재설정됩니다.

+ ALTER TABLE ... EXCHANGE PARTITION과 함께 사용하면 IGNORE 키워드가 적용되지 않습니다.

ALTER TABLE ... EXCHANGE PARTITION문의 구문이 여기에 표시됩니다. 여기서 pt는 파티션된 테이블, p는 교환할 파티션 또는 서브 파티션, nt는 p와 교환 될 비 파티션 테이블입니다.

ALTER TABLE pt
    EXCHANGE PARTITION p
    WITH TABLE nt;

선택적으로 WITH VALIDATION 또는 WITHOUT VALIDATION절을 추가할 수 있습니다. WITHOUT VALIDATION이 지정되면 ALTER TABLE ... EXCHANGE PARTITION작업은 파티션을 파티션되지 않은 테이블로 교환할때 행 단위 유효성 검증을 수행하지 않으므로 데이터베이스 관리자는 행이 파티션 정의의 경계 내에 있는지 확인해야 할 책임이 있습니다. WITH VALIDATION이 기본 동작이며 명시적으로 지정할 필요는 없습니다. [{WITH | WITHOUT} VALIDATION] 옵션은 MySQL 5.7.5에서 추가되었습니다.

단일 ALTER TABLE EXCHANGE PARTITION문에서 하나의 파티션 또는 서브 파티션을 하나의 파티션되지 않은 테이블과 교환할 수 있습니다. 여러 파티션 또는 하위 파티션을 교환하려면 여러 ALTER TABLE EXCHANGE PARTITION 문을 사용합니다. EXCHANGE PARTITION은 다른 ALTER TABLE옵션과 결합될 수 없습니다. 분할된 테이블에서 사용되는 분할 및 (해당되는 경우) 하위 분할은 MySQL 5.7에서 지원되는 모든유형 또는 유형일 수 있습니다.

▶︎ 파티션되지 않은 테이블을 파티션으로 교환

파티션 된 테이블 e가 다음 SQL 문을 사용하여 작성되고 채워 졌다고 가정합니다.

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

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

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 테이블을 쿼리하면 "missing"행을 찾을 수 있습니다.

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

파티션과 교환할 테이블이 반드시 비어있을 필요는 없습니다. 이를 설명하기 위해 먼저 테이블 e에 새 행을 삽입하여 50보다 작은 id열 값을 선택하고 PARTITIONS 테이블을 쿼리하여이 행을 p0파티션에 저장했는지 확인합니다.

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

WITHOUT VALIDATION 옵션만 있으면 이 명령문은 수행됩니다.
mysql> ALTER TABLE e EXCHANGE PARTITION p0 WITH TABLE e2 WITHOUT VALIDATION;
Query OK, 0 rows affected (0.02 sec)

파티션이 파티션 정의와 일치하지 않는 행을 포함하는 테이블과 교환될 때 데이터베이스 관리자는 일치하지 않는 행을 수정해야합니다. REPAIR TABLE 또는 ALTER TABLE ... REPAIR PARTITION을 사용하여 수행할 수 있습니다.

▶︎ 행 단위 유효성 검사없이 파티션 교환

많은 행이 있는 테이블로 파티션을 교환할 때 시간 소모적인 유효성 검증을 피하기 위해 ALTER TABLE ... EXCHANGE PARTITION문에 WITHOUT VALIDATION을 추가하여 행별 유효성 검증 단계를 건너 뛸 수 있습니다.

다음 예는 파티션이 파티션되지 않은 테이블과 유효성 검사 유무에 관계없이 파티션을 교환할 때 실행 시간의 차이를 비교합니다. 파티션된 테이블 (표 e)에는 각각 1 백만 행의 두 파티션이 있습니다. 테이블 e의 p0에서 행이 제거되고 p0은 1백만 행의 파티션되지 않은 테이블로 교환됩니다. WITH VALIDATION 작업은 0.74 초가 걸립니다. 이에 비해 WITHOUT VALIDATION 작업은 0.01 초가 걸립니다.

▷ 파티션 교환방법 시작

* 각 파티션에 백만 개의 행이있는 파티션된 테이블 작성

CREATE TABLE e (
    id INT NOT NULL,
    fname VARCHAR(30),
    lname VARCHAR(30)
)
    PARTITION BY RANGE (id) (
        PARTITION p0 VALUES LESS THAN (1000001),
        PARTITION p1 VALUES LESS THAN (2000001),
);

SELECT COUNT(*) FROM e;
| COUNT(*) |
+----------+
|  2000000 |
+----------+
1 row in set (0.27 sec)

* 각 파티션의 행을 봅니다.

SELECT PARTITION_NAME, TABLE_ROWS FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_NAME = 'e';
+----------------+-------------+
| PARTITION_NAME | TABLE_ROWS  |
+----------------+-------------+
| p0             |     1000000 |
| p1             |     1000000 |
+----------------+-------------+
2 rows in set (0.00 sec)

* 동일한 구조의 파티션되지 않은 테이블을 만들고 백만개의 행으로 채 웁니다.

CREATE TABLE e2 (
    id INT NOT NULL,
    fname VARCHAR(30),
    lname VARCHAR(30)
);

mysql> SELECT COUNT(*) FROM e2;
+----------+
| COUNT(*) |
+----------+
|  1000000 |
+----------+
1 row in set (0.24 sec)

* 동일한 구조로 파티션되지 않은 다른 테이블을 만들고 백만 개의 행으로 채 웁니다.

CREATE TABLE e3 (
    id INT NOT NULL,
    fname VARCHAR(30),
    lname VARCHAR(30)
);

mysql> SELECT COUNT(*) FROM e3;
+----------+
| COUNT(*) |
+----------+
|  1000000 |
+----------+
1 row in set (0.25 sec)

* 테이블 e의 파티션 p0에서 행을 삭제

mysql> DELETE FROM e WHERE id < 1000001;
Query OK, 1000000 rows affected (5.55 sec)

* 파티션 p0에 행이 없는지 확인

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

* 테이블 e2 'WITH VALIDATION'과 테이블 e의 교환 파티션 p0

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

* 파티션이 테이블 e2와 교환되었는지 확인

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

* 다시 한 번 표 e의 p0에서 행을 삭제하십시오.

mysql> DELETE FROM e WHERE id < 1000001;
Query OK, 1000000 rows affected (5.55 sec)

* 파티션 p0에 행이 없는지 확인

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

* 테이블 e3 'WITHOUT VALIDATION'과 테이블 e의 교환 파티션 p0

mysql> ALTER TABLE e EXCHANGE PARTITION p0 WITH TABLE e3 WITHOUT VALIDATION;
Query OK, 0 rows affected (0.01 sec)

* 파티션이 e3 테이블과 교환되었는지 확인

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

파티션이 파티션 정의와 일치하지 않는 행을 포함하는 테이블과 교환되는 경우 데이터베이스 관리자는 일치하지 않는 행을 수정해야합니다. REPAIR TABLE 또는 ALTER TABLE ... REPAIR PARTITION을 사용하여 수행 할 수 있습니다. .

▶︎ 파티션되지 않은 테이블과 하위 파티션 교환

ALTER TABLE ... EXCHANGE PARTITION 문을 사용하여 분할된 테이블의 분할 파티션을(섹션 22.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를 작성할때 서브 파티션의 이름을 명시적으로 지정하지는 않았지만 여기에 표시된대로 해당 테이블에서 선택할때 INFORMATION_SCHEMA에서 PARTITIONS 테이블의 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 문은 서브 파티션 p3sp0 테이블 es를 파티션되지 않은 테이블 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

■ 파티션 유지 관리

MySQL 5.7의 파티션된 테이블에서 이러한 목적으로 사용되는 SQL 문을 사용하여 여러 테이블 및 파티션 유지 보수 태스크를 수행할 수 있습니다.

파티션된 테이블의 테이블 유지 보수는 파티션된 테이블에 지원되는 CHECK TABLE, OPTIMIZE TABLE, ANALYZE TABLE 및 REPAIR TABLE 문을 사용하여 수행할 수 있습니다.

다음 목록에 설명된대로 하나 이상의 파티션에서 이 유형의 작업을 직접 수행하기 위해 ALTER TABLE에 대한 여러 가지 확장을 사용할수 있습니다.

▶︎ 파티션 재 구축.

파티션을 재구축합니다. 이는 파티션에 저장된 모든 레코드를 삭제한 다음 다시 삽입하는 것과 동일한 효과를 갖습니다. 조각 모음을 수행하는 데 유용 할 수 있습니다.

예제:

ALTER TABLE t1 REBUILD PARTITION p0, p1;

▶︎ 파티션 최적화.

파티션에서 많은 수의 행을 삭제했거나 가변 길이 행(즉, VARCHAR, BLOB 또는 TEXT 컬럼이있는)이 있는 파티션된 테이블을 많이 변경한 경우, ALTER TABLE ... OPTIMIZE PARTITION 을 사용하여 사용하지 않는 공간을 회수하고 파티션 데이터 파일을 조각 모음할 수 있습니다.

예제:

ALTER TABLE t1 OPTIMIZE PARTITION p0, p1;

주어진 파티션에서 OPTIMIZE PARTITION을 사용하는 것은 해당 파티션에서 CHECK PARTITION, ANALYZE PARTITION 및 REPAIR PARTITION을 실행하는 것과 같습니다.

InnoDB를 포함한 일부 MySQL 스토리지 엔진은 각각의 파티션에 대해 최적화를 지원하지 않습니다. 이러한 경우 ALTER TABLE ... OPTIMIZE PARTITION은 전체 테이블을 분석 및 재 구축하여 적절한 경고를 발행합니다. 이 문제를 피하려면 ALTER TABLE ... REBUILD PARTITION 및 ALTER TABLE ...을 사용합니다.

▶︎ 파티션 분석.

파티션의 주요 분포에 대해 읽고 저장한다. 파티션 키에 따른 데이터 분포에 대해 어떻게 저장되어 있는지 분석한다.

예제:

ALTER TABLE t1 ANALYZE PARTITION p3;

▶︎ 파티션 복구.

손상된 파티션을 복구합니다.

예제:

ALTER TABLE t1 REPAIR PARTITION p0,p1;

일반적으로 파티션에 중복키 오류가 있으면 REPAIR PARTITION이 실패합니다. MySQL 5.7.2 이상에서이 옵션과 함께 ALTER IGNORE TABLE을 사용할 수 있습니다.이 경우 중복키가 존재하여 이동할 수 없는 모든 행이 파티션에서 제거됩니다.

▶︎ 파티션 확인.

파티션되지 않은 테이블에 CHECK TABLE을 사용할 수있는 것과 같은 방식으로 파티션에 오류가 있는지 확인할 수 있습니다.

예제:

ALTER TABLE trb3 CHECK PARTITION p1;

이 명령은 테이블 t1의 p1 파티션에있는 데이터 또는 인덱스가 손상되었는지 알려줍니다. 이 경우 ALTER TABLE ... REPAIR PARTITION을 사용하여 파티션을 복구합니다.

일반적으로 파티션에 중복키 오류가 있으면 CHECK PARTITION이 실패합니다. MySQL 5.7.2 이상에서이 옵션과 함께 ALTER IGNORE TABLE을 사용할 수 있습니다. 이 경우 명령문은 중복키 위반이 발견 된 파티션의 각 행 내용을 리턴합니다. 테이블에 대한 파티셔닝 표현식의 컬럼 값만보고됩니다.

방금 표시된 목록의 각 명령문은 파티션 이름 목록 대신 키워드 ALL을 지원합니다. ALL을 사용하면 명령문이 테이블의 모든 파티션에서 작동합니다.

파티션된 테이블에서는 mysqlcheck 및 myisamchk를 사용할 수 없습니다.

MySQL 5.7에서는 ALTER TABLE ... TRUNCATE PARTITION을 사용하여 파티션을 자를 수도 있습니다. 이 명령문은 TRUNCATE TABLE이 테이블에서 모든 행을 삭제하는 것과 거의 같은 방식으로 하나 이상의 파티션에서 모든 행을 삭제하는데 사용할 수 있습니다.

ALTER TABLE ... TRUNCATE PARTITION ALL 명령은 테이블의 모든 파티션을 절단합니다.

MySQL 5.7.2 이전에는 하위 파티션에서는 ANALYZE, CHECK, OPTIMIZE, REBUILD, REPAIR 및 TRUNCATE 작업이 허용되지 않았습니다.

■ 파티션 정보 얻기

이 섹션에서는 여러가지 방법으로 수행할 수 있는 기존 파티션에 대한 정보를 얻는 방법에 대해 설명합니다. 이러한 정보를 얻는 방법은 다음과 같습니다.

+ SHOW CREATE TABLE 문을 사용하여 파티션된 테이블 작성에 사용된 파티션 절을 확인합니다.

+ SHOW TABLE STATUS 문을 사용하여 테이블이 파티션되었는지 여부를 판별합니다.

+ INFORMATION_SCHEMA.PARTITIONS 테이블을 쿼리합니다.

+ EXPLAIN SELECT 문을 사용하여 주어진 SELECT에서 어떤 파티션이 사용되는지 확인합니다.

이 장의 다른 곳에서 논의된 것처럼 SHOW CREATE TABLE은 분할된 테이블을 만드는데 사용 된 PARTITION BY 절을 출력에 포함합니다. 예를 들면 다음과 같습니다.

mysql> SHOW CREATE TABLE trb3\G
*************************** 1. row ***************************
       Table: trb3
Create Table: CREATE TABLE `trb3` (
  `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 p2 VALUES LESS THAN (2000) ENGINE = MyISAM,
  PARTITION p3 VALUES LESS THAN (2005) ENGINE = MyISAM
)
1 row in set (0.00 sec)

파티션된 테이블에 대한 SHOW TABLE STATUS의 출력은 Create_options 열에 파티션된 문자열이 있다는 점을 제외하고는 파티션되지 않은 테이블의 출력과 동일합니다. 엔진열에는 테이블의 모든 파티션에서 사용되는 스토리지 엔진의 이름이 포함됩니다.

PARTITIONS테이블이 포함된 INFORMATION_SCHEMA에서 파티션에 대한 정보를 얻을 수도 있습니다.

EXPLAIN을 사용하여 지정된 SELECT 쿼리에 포함된 파티션된 테이블의 파티션을 판별 할 수 있습니다. EXPLAIN 출력의 partitions 열에는 쿼리와 레코드가 일치하는 파티션이 나열됩니다.

다음과 같이 trb1 테이블이 작성되고 채워 졌다고 가정합니다.

CREATE TABLE trb1 (id INT, name VARCHAR(50), purchased DATE)
    PARTITION BY RANGE(id)
    (
        PARTITION p0 VALUES LESS THAN (3),
        PARTITION p1 VALUES LESS THAN (7),
        PARTITION p2 VALUES LESS THAN (9),
        PARTITION p3 VALUES LESS THAN (11)
    );

INSERT INTO trb1 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');

다음과 같이 SELECT * FROM trb1;과 같은 쿼리에 사용된 파티션을 확인할 수 있습니다.

mysql> EXPLAIN SELECT * FROM trb1\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: trb1
   partitions: p0,p1,p2,p3
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 10
        Extra: Using filesort

이 경우 네 개의 파티션이 모두 검색됩니다. 그러나 분할 키를 사용하는 제한 조건이 쿼리에 추가되면 다음과 같이 일치하는 값을 포함하는 해당 분할 영역만 검색되는 것을 볼 수 있습니다.

mysql> EXPLAIN SELECT * FROM trb1 WHERE id < 5\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: trb1
   partitions: p0,p1
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 10
        Extra: Using where

EXPLAIN은 사용된 키와 가능한 키에 대한 정보도 제공합니다.

mysql> ALTER TABLE trb1 ADD PRIMARY KEY (id);
Query OK, 10 rows affected (0.03 sec)
Records: 10  Duplicates: 0  Warnings: 0

mysql> EXPLAIN SELECT * FROM trb1 WHERE id < 5\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: trb1
   partitions: p0,p1
         type: range
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 4
          ref: NULL
         rows: 7
        Extra: Using where

EXPLAIN PARTITIONS를 사용하여 파티션되지 않은 테이블에 대한 쿼리를 검사하면 오류가 발생하지 않지만 파티션 열의 값은 항상 NULL입니다.

EXPLAIN 출력의 행 열에는 테이블의 총 행 수가 표시됩니다.

□ 테이블 파티셔닝 링크

[MySQL] Table Partitioning - 소개

https://myinfrabox.tistory.com/93

[MySQL] Table Partitioning - 소개

■ 파티셔닝 소개 비 네이티브 파티셔닝이 있는 테이블을 사용하면 ER_WARN_DEPRECATED_SYNTAX경고가 발생합니다. MySQL 5.7.17에서 5.7.20까지, 서버는 시작시 비 기본 파티션을 사용하는 테이블을 식별하��

myinfrabox.tistory.com

[MySQL] Table Partitioning-파티셔닝 타입

https://myinfrabox.tistory.com/94

[MySQL] Table Partitioning-파티셔닝 타입

■ 파티셔닝 타입 이 섹션에서는 MySQL 5.7에서 사용할 수있는 파티셔닝 유형에 대해 설명합니다. 여기에 나열된 유형이 포함됩니다. + RANGE 파티셔닝. 이 유형의 파티셔닝은 주어진 범위 내에 속하

myinfrabox.tistory.com

[MySQL] Table Partitioning - 파티션 프루닝(파티션 정리)

https://myinfrabox.tistory.com/100

[MySQL] Table Partitioning - 파티션 프루닝(파티션 정리)

이 섹션에서는 파티션 정리(Partition 라고하는 최적화에 대해 설명합니다. 파티션 정리의 기본 개념은 비교적 간단하며“일치하는 값이 없는 파티션은 스캔하지 않습니다”라고 설명 할 수 있습�

myinfrabox.tistory.com

[MySQL] Table Partitioning-파티셔닝 선택

https://myinfrabox.tistory.com/102

[MySQL] Table Partitioning-파티셔닝 선택

■ 파티션 선택 MySQL 5.7은 명령문을 실행할때 주어진 WHERE 조건과 일치하는 행을 검사해야하는 파티션 및 하위 파티션의 명시적 선택을 지원합니다. 파티션 선택은 특정 파티션 만 일치하는지 검

myinfrabox.tistory.com

[MySQL] Table Partitioning-파티셔닝에 대한 제한 사항

https://myinfrabox.tistory.com/103

[MySQL] Table Partitioning-파티셔닝에 대한 제한 사항

■ 파티셔닝에 대한 제한 사항 이 글에서는 MySQL 파티셔닝 지원에 대한 현재 제한 사항에 대해 설명합니다. ▶︎ 금지된 문법. 파티셔닝 표현식에서는 다음 구성이 허용되지 않습니다. 저장 프로

myinfrabox.tistory.com

※도움이 되셨다면 광고클릭 한번 부탁드립니다.※

'Databases > MySQL' 카테고리의 다른 글

[MySQL] Table Partitioning-파티셔닝 선택 (0)	2020.09.02
[MySQL] Table Partitioning - 파티션 프루닝(파티션 정리) (0)	2020.08.30
[MySQL] Table Partitioning-파티셔닝 타입 (0)	2020.08.20
[MySQL] Table Partitioning - 소개 (0)	2020.08.17
[MySQL] Query Optimizer 제어 (0)	2020.08.14

[MySQL] Table Partitioning - 파티셔닝 관리

'Databases > MySQL' 카테고리의 다른 글

Copyright © MyInfraBox All Rights Reserved

Designed by JB FACTORY

'Databases > MySQL' 카테고리의 다른 글

'Databases/MySQL' 관련 글

[MySQL] Table Partitioning-파티셔닝 선택

[MySQL] Table Partitioning - 파티션 프루닝(파티션 정리)

[MySQL] Table Partitioning-파티셔닝 타입

[MySQL] Table Partitioning - 소개

Copyright © MyInfraBox All Rights Reserved

Designed by JB FACTORY

티스토리툴바