26.2.2 LIST Partitioning

26.2.2 LIST Partitioning

MySQL에서 list partitioning은 많은 면에서 range partitioning과 비슷합니다. RANGE에 의한 partitioning과 마찬가지로, 각 partition은 명시적으로 정의되어야 합니다. 두 partitioning 방식의 주요 차이점은, list partitioning에서는 각 partition이 연속된 값 범위 집합 중 하나가 아니라, 값 list 집합 중 하나에 열 값이 속하는지 여부에 따라 정의되고 선택된다는 점입니다. 이는 PARTITION BY LIST(expr)을 사용하여 수행되며, _expr_은 열 값이거나 열 값을 기반으로 하고 정수 값을 반환하는 표현식이고, 이어서 각 partition을 VALUES IN (value_list)를 사용하여 정의합니다. 여기서 _value_list_는 정수의 쉼표로 구분된 리스트입니다.

참고

MySQL 9.5에서는 partitioning by LIST를 사용할 때 정수의 리스트(그리고 경우에 따라서는 NULL—자세한 내용은 Section 26.2.7, “How MySQL Partitioning Handles NULL”을 참조하십시오)에만 매칭하는 것이 가능합니다.

그러나 이 절의 뒷부분에서 설명하는 LIST COLUMN partitioning을 사용할 때에는 value 리스트에 다른 컬럼 타입도 사용할 수 있습니다.

range로 정의된 partition과는 달리, list partition은 특정한 순서로 선언될 필요가 없습니다. 보다 자세한 문법 정보는 Section 15.1.24, “CREATE TABLE Statement”를 참조하십시오.

다음 예제들에서는, partitioning 대상이 되는 테이블의 기본 정의가 여기 나와 있는 CREATE TABLE 구문에 의해 제공된다고 가정합니다:

1CREATE TABLE employees (
2    id INT NOT NULL,
3    fname VARCHAR(30),
4    lname VARCHAR(30),
5    hired DATE NOT NULL DEFAULT '1970-01-01',
6    separated DATE NOT NULL DEFAULT '9999-12-31',
7    job_code INT,
8    store_id INT
9);

(이것은 Section 26.2.1, “RANGE Partitioning”의 예제에서 기반으로 사용한 것과 동일한 테이블입니다. 다른 partitioning 예제들과 마찬가지로, default_storage_engine이 InnoDB라고 가정합니다.)

다음 테이블에 표시된 것처럼, 4개의 프랜차이즈에 분산된 20개의 비디오 스토어가 있다고 가정합니다.

Region	Store ID Numbers
North	3, 5, 6, 9, 17
East	1, 2, 10, 11, 19, 20
West	4, 12, 13, 14, 18
Central	7, 8, 15, 16

같은 region에 속한 store의 행들이 같은 partition에 저장되도록 이 테이블을 partition하려면, 여기 나와 있는 CREATE TABLE 구문을 사용할 수 있습니다:

1CREATE TABLE employees (
2    id INT NOT NULL,
3    fname VARCHAR(30),
4    lname VARCHAR(30),
5    hired DATE NOT NULL DEFAULT '1970-01-01',
6    separated DATE NOT NULL DEFAULT '9999-12-31',
7    job_code INT,
8    store_id INT
9)
10PARTITION BY LIST(store_id) (
11    PARTITION pNorth VALUES IN (3,5,6,9,17),
12    PARTITION pEast VALUES IN (1,2,10,11,19,20),
13    PARTITION pWest VALUES IN (4,12,13,14,18),
14    PARTITION pCentral VALUES IN (7,8,15,16)
15);

이렇게 하면 특정 region과 관련된 employee 레코드들을 테이블에 추가하거나 테이블에서 제거하는 작업이 쉬워집니다. 예를 들어, West region에 있는 모든 store가 다른 회사에 매각되었다고 가정해 봅니다. MySQL 9.5에서는 해당 region의 store에서 근무하는 employee와 관련된 모든 행을 ALTER TABLE employees TRUNCATE PARTITION pWest 쿼리로 삭제할 수 있으며, 이는 동일한 효과를 내는 DELETE 구문인 DELETE FROM employees WHERE store_id IN (4,12,13,14,18);보다 훨씬 효율적으로 실행될 수 있습니다. (ALTER TABLE employees DROP PARTITION pWest를 사용해도 이러한 행들을 모두 삭제할 수 있지만, 이 경우 테이블의 정의에서 partition pWest도 제거됩니다. 테이블의 원래 partitioning 스킴을 복원하려면 ALTER TABLE ... ADD PARTITION 구문을 사용해야 합니다.)

RANGE partitioning과 마찬가지로, LIST partitioning을 hash 또는 key에 의한 partitioning과 결합하여 composite partitioning(subpartitioning)을 생성할 수 있습니다. 자세한 내용은 Section 26.2.6, “Subpartitioning”을 참조하십시오.

RANGE partitioning과는 달리, MAXVALUE와 같은 “포괄용(catch-all)”은 없습니다. partitioning 표현식에 대한 모든 예상 값은 PARTITION ... VALUES IN (...) 절에 포함되어야 합니다. 일치하지 않는 partitioning 컬럼 값을 포함하는 INSERT 구문은 다음 예제에 나와 있는 것처럼 에러와 함께 실패합니다:

1mysql> CREATE TABLE h2 (
2    ->   c1 INT,
3    ->   c2 INT
4    -> )
5    -> PARTITION BY LIST(c1) (
6    ->   PARTITION p0 VALUES IN (1, 4, 7),
7    ->   PARTITION p1 VALUES IN (2, 5, 8)
8    -> );
9Query OK, 0 rows affected (0.11 sec)
10
11mysql> INSERT INTO h2 VALUES (3, 5);
12ERROR 1525 (HY000): Table has no partition for value 3

단일 InnoDB 테이블에 대해 단일 INSERT 구문을 사용하여 여러 행을 insert할 때, InnoDB는 그 구문을 단일 트랜잭션으로 처리하므로, 일치하지 않는 값이 하나라도 존재하면 구문 전체가 실패하고, 그 결과 어떤 행도 insert되지 않습니다.

IGNORE 키워드를 사용하면 이러한 유형의 에러를 무시할 수 있지만, 다음과 같이 일치하지 않는 partitioning 컬럼 값을 포함하는 각 행에 대해 warning이 발생합니다.

1mysql> TRUNCATE h2;
2Query OK, 1 row affected (0.00 sec)
3
4mysql> TABLE h2;
5Empty set (0.00 sec)
6
7mysql> INSERT IGNORE INTO h2 VALUES (2, 5), (6, 10), (7, 5), (3, 1), (1, 9);
8Query OK, 3 rows affected, 2 warnings (0.01 sec)
9Records: 5  Duplicates: 2  Warnings: 2
10
11mysql> SHOW WARNINGS;
12+---------+------+------------------------------------+
13| Level   | Code | Message                            |
14+---------+------+------------------------------------+
15| Warning | 1526 | Table has no partition for value 6 |
16| Warning | 1526 | Table has no partition for value 3 |
17+---------+------+------------------------------------+
182 rows in set (0.00 sec)

다음 TABLE 구문의 출력에서 볼 수 있듯이, 일치하지 않는 partitioning 컬럼 값을 포함하는 행은 조용히 거부되었고, 일치하지 않는 값을 포함하지 않은 행만 테이블에 insert되었습니다:

1mysql> TABLE h2;
2+------+------+
3| c1   | c2   |
4+------+------+
5|    7 |    5 |
6|    1 |    9 |
7|    2 |    5 |
8+------+------+
93 rows in set (0.00 sec)

MySQL은 또한 LIST COLUMNS partitioning을 지원합니다. 이는 partitioning 컬럼에 정수 이외의 타입을 사용할 수 있고, 여러 개의 컬럼을 partitioning 키로 사용할 수 있게 해 주는 LIST partitioning의 변형입니다. 자세한 내용은 Section 26.2.3.2, “LIST COLUMNS partitioning”을 참조하십시오.