26.5 Partition Selection

26.5 Partition Selection

주어진 WHERE 조건과 일치하는 행에 대해 파티션과 서브파티션을 명시적으로 선택하는 기능이 지원됩니다. 파티션 선택은 오직 특정 파티션만 일치 여부를 검사한다는 점에서 파티션 프루닝과 비슷하지만, 두 가지 중요한 차이점이 있습니다:

1. 검사할 파티션은 파티션 프루닝과 달리 문장을 실행하는 사용자가 직접 지정합니다. 파티션 프루닝은 자동으로 수행됩니다.

2. 파티션 프루닝은 쿼리에만 적용되는 반면, 파티션 선택은 쿼리뿐만 아니라 여러 DML 문에서도 지원됩니다.

명시적인 파티션 선택을 지원하는 SQL 문은 다음과 같습니다:

• SELECT

• DELETE

• INSERT

• REPLACE

• UPDATE

• LOAD DATA.

• LOAD XML.

이 절의 나머지 부분에서는 앞에서 나열한 문장들에 일반적으로 적용되는 명시적인 파티션 선택에 대해 설명하고, 몇 가지 예를 제공합니다.

명시적인 파티션 선택은 PARTITION 옵션을 사용하여 구현됩니다. 지원되는 모든 문에서 이 옵션은 다음과 같은 구문을 사용합니다:

1      PARTITION (partition_names)
2
3      partition_names:
4          partition_name, ...

이 옵션은 항상 파티션이 속한 테이블 이름 뒤에 위치합니다. partition_names는 사용될 파티션 또는 서브파티션들의 쉼표로 구분된 목록입니다. 이 목록의 각 이름은 지정된 테이블의 존재하는 파티션 또는 서브파티션 이름이어야 합니다. 만약 하나라도 파티션 또는 서브파티션을 찾을 수 없는 경우, 이 문장은 에러 (partition 'partition_name' doesn't exist)와 함께 실패합니다. _partition_names_에 나열된 파티션과 서브파티션은 어떤 순서로든 나열될 수 있으며, 서로 겹칠 수도 있습니다.

PARTITION 옵션이 사용되면, 나열된 파티션과 서브파티션만이 일치하는 행을 찾기 위해 검사됩니다. 이 옵션은 특정 파티션에 속하는 행을 확인하기 위해 SELECT 문에서 사용할 수 있습니다. 다음과 같은 문장으로 생성 및 데이터가 채워진 employees라는 파티션 테이블을 가정해 보겠습니다:

1SET @@SQL_MODE = '';
2
3CREATE TABLE employees  (
4    id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
5    fname VARCHAR(25) NOT NULL,
6    lname VARCHAR(25) NOT NULL,
7    store_id INT NOT NULL,
8    department_id INT NOT NULL
9)
10    PARTITION BY RANGE(id)  (
11        PARTITION p0 VALUES LESS THAN (5),
12        PARTITION p1 VALUES LESS THAN (10),
13        PARTITION p2 VALUES LESS THAN (15),
14        PARTITION p3 VALUES LESS THAN MAXVALUE
15);
16
17INSERT INTO employees VALUES
18    ('', 'Bob', 'Taylor', 3, 2), ('', 'Frank', 'Williams', 1, 2),
19    ('', 'Ellen', 'Johnson', 3, 4), ('', 'Jim', 'Smith', 2, 4),
20    ('', 'Mary', 'Jones', 1, 1), ('', 'Linda', 'Black', 2, 3),
21    ('', 'Ed', 'Jones', 2, 1), ('', 'June', 'Wilson', 3, 1),
22    ('', 'Andy', 'Smith', 1, 3), ('', 'Lou', 'Waters', 2, 4),
23    ('', 'Jill', 'Stone', 1, 4), ('', 'Roger', 'White', 3, 2),
24    ('', 'Howard', 'Andrews', 1, 2), ('', 'Fred', 'Goldberg', 3, 3),
25    ('', 'Barbara', 'Brown', 2, 3), ('', 'Alice', 'Rogers', 2, 2),
26    ('', 'Mark', 'Morgan', 3, 3), ('', 'Karen', 'Cole', 3, 2);

다음과 같이 해서 파티션 p1에 저장된 행들을 확인할 수 있습니다:

1mysql> SELECT * FROM employees PARTITION (p1);
2+----+-------+--------+----------+---------------+
3| id | fname | lname  | store_id | department_id |
4+----+-------+--------+----------+---------------+
5|  5 | Mary  | Jones  |        1 |             1 |
6|  6 | Linda | Black  |        2 |             3 |
7|  7 | Ed    | Jones  |        2 |             1 |
8|  8 | June  | Wilson |        3 |             1 |
9|  9 | Andy  | Smith  |        1 |             3 |
10+----+-------+--------+----------+---------------+
115 rows in set (0.00 sec)

이 결과는 SELECT * FROM employees WHERE id BETWEEN 5 AND 9 쿼리로 얻는 결과와 동일합니다.

여러 파티션에서 행을 가져오려면 그들의 이름을 쉼표로 구분된 목록으로 제공하면 됩니다. 예를 들어, SELECT * FROM employees PARTITION (p1, p2)는 파티션 p1과 p2의 모든 행을 반환하면서 나머지 파티션의 행은 제외합니다.

파티션된 테이블에 대해 유효한 모든 쿼리는 원하는 하나 이상의 파티션으로 결과를 제한하기 위해 PARTITION 옵션을 사용하여 다시 작성할 수 있습니다. WHERE 조건, ORDER BY와 LIMIT 옵션 등을 사용할 수 있습니다. 또한 집계 함수를 HAVING 및 GROUP BY 옵션과 함께 사용할 수도 있습니다. 다음 쿼리들은 앞에서 정의한 employees 테이블에서 실행했을 때 모두 유효한 결과를 생성합니다:

1mysql> SELECT * FROM employees PARTITION (p0, p2)
2    ->     WHERE lname LIKE 'S%';
3+----+-------+-------+----------+---------------+
4| id | fname | lname | store_id | department_id |
5+----+-------+-------+----------+---------------+
6|  4 | Jim   | Smith |        2 |             4 |
7| 11 | Jill  | Stone |        1 |             4 |
8+----+-------+-------+----------+---------------+
92 rows in set (0.00 sec)
10
11mysql> SELECT id, CONCAT(fname, ' ', lname) AS name
12    ->     FROM employees PARTITION (p0) ORDER BY lname;
13+----+----------------+
14| id | name           |
15+----+----------------+
16|  3 | Ellen Johnson  |
17|  4 | Jim Smith      |
18|  1 | Bob Taylor     |
19|  2 | Frank Williams |
20+----+----------------+
214 rows in set (0.06 sec)
22
23mysql> SELECT store_id, COUNT(department_id) AS c
24    ->     FROM employees PARTITION (p1,p2,p3)
25    ->     GROUP BY store_id HAVING c > 4;
26+---+----------+
27| c | store_id |
28+---+----------+
29| 5 |        2 |
30| 5 |        3 |
31+---+----------+
322 rows in set (0.00 sec)

파티션 선택을 사용하는 문장은 지원되는 어떤 파티셔닝 타입을 사용하는 테이블에도 적용할 수 있습니다. 테이블이 [LINEAR] HASH 또는 [LINEAR] KEY 파티셔닝을 사용하여 생성되고 파티션의 이름이 지정되지 않은 경우, MySQL은 파티션들의 이름을 자동으로 p0, p1, p2, ..., pN-1로 지정합니다. 여기서 _N_은 파티션의 개수입니다. 명시적으로 이름이 지정되지 않은 서브파티션의 경우, MySQL은 각 파티션 pX의 서브파티션에 대해 자동으로 pXsp0, pXsp1, pXsp2, ..., pXspM-1과 같은 이름을 지정합니다. 여기서 _M_은 서브파티션의 개수입니다. 이 테이블에 대해 명시적인 파티션 선택이 허용된 SELECT(또는 다른 SQL 문)를 실행할 때, 다음 예에서처럼 PARTITION 옵션에 이러한 자동 생성된 이름을 사용할 수 있습니다:

1mysql> CREATE TABLE employees_sub  (
2    ->     id INT NOT NULL AUTO_INCREMENT,
3    ->     fname VARCHAR(25) NOT NULL,
4    ->     lname VARCHAR(25) NOT NULL,
5    ->     store_id INT NOT NULL,
6    ->     department_id INT NOT NULL,
7    ->     PRIMARY KEY pk (id, lname)
8    -> )
9    ->     PARTITION BY RANGE(id)
10    ->     SUBPARTITION BY KEY (lname)
11    ->     SUBPARTITIONS 2 (
12    ->         PARTITION p0 VALUES LESS THAN (5),
13    ->         PARTITION p1 VALUES LESS THAN (10),
14    ->         PARTITION p2 VALUES LESS THAN (15),
15    ->         PARTITION p3 VALUES LESS THAN MAXVALUE
16    -> );
17Query OK, 0 rows affected (1.14 sec)
18
19mysql> INSERT INTO employees_sub   # reuse data in employees table
20    ->     SELECT * FROM employees;
21Query OK, 18 rows affected (0.09 sec)
22Records: 18  Duplicates: 0  Warnings: 0
23
24mysql> SELECT id, CONCAT(fname, ' ', lname) AS name
25    ->     FROM employees_sub PARTITION (p2sp1);
26+----+---------------+
27| id | name          |
28+----+---------------+
29| 10 | Lou Waters    |
30| 14 | Fred Goldberg |
31+----+---------------+
322 rows in set (0.00 sec)

또한 다음과 같이 INSERT ... SELECT 문에서 SELECT 부분에 PARTITION 옵션을 사용할 수도 있습니다:

1mysql> CREATE TABLE employees_copy LIKE employees;
2Query OK, 0 rows affected (0.28 sec)
3
4mysql> INSERT INTO employees_copy
5    ->     SELECT * FROM employees PARTITION (p2);
6Query OK, 5 rows affected (0.04 sec)
7Records: 5  Duplicates: 0  Warnings: 0
8
9mysql> SELECT * FROM employees_copy;
10+----+--------+----------+----------+---------------+
11| id | fname  | lname    | store_id | department_id |
12+----+--------+----------+----------+---------------+
13| 10 | Lou    | Waters   |        2 |             4 |
14| 11 | Jill   | Stone    |        1 |             4 |
15| 12 | Roger  | White    |        3 |             2 |
16| 13 | Howard | Andrews  |        1 |             2 |
17| 14 | Fred   | Goldberg |        3 |             3 |
18+----+--------+----------+----------+---------------+
195 rows in set (0.00 sec)

파티션 선택은 조인에서도 사용할 수 있습니다. 다음 문장들로 두 개의 테이블을 생성하고 데이터로 채운다고 가정합니다:

1CREATE TABLE stores (
2    id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
3    city VARCHAR(30) NOT NULL
4)
5    PARTITION BY HASH(id)
6    PARTITIONS 2;
7
8INSERT INTO stores VALUES
9    ('', 'Nambucca'), ('', 'Uranga'),
10    ('', 'Bellingen'), ('', 'Grafton');
11
12CREATE TABLE departments  (
13    id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
14    name VARCHAR(30) NOT NULL
15)
16    PARTITION BY KEY(id)
17    PARTITIONS 2;
18
19INSERT INTO departments VALUES
20    ('', 'Sales'), ('', 'Customer Service'),
21    ('', 'Delivery'), ('', 'Accounting');

조인에 사용되는 하나 또는 여러 테이블에 대해서 파티션(또는 서브파티션, 또는 둘 다)을 명시적으로 선택할 수 있습니다. (특정 테이블에서 파티션을 선택하는 데 사용되는 PARTITION 옵션은 해당 테이블 이름 바로 뒤에 오며, 테이블 별칭을 포함한 그 밖의 모든 옵션보다 앞에 위치합니다.) 예를 들어, 다음 쿼리는 Sales 또는 Delivery 부서(departments 테이블의 파티션 p1)에서 근무하면서, Nambucca와 Bellingen 두 도시 중 하나에 있는 매장(stores 테이블의 파티션 p0)에서 일하는 모든 직원의 이름, 직원 ID, 부서, 도시를 가져옵니다:

1mysql> SELECT
2    ->     e.id AS 'Employee ID', CONCAT(e.fname, ' ', e.lname) AS Name,
3    ->     s.city AS City, d.name AS department
4    -> FROM employees AS e
5    ->     JOIN stores PARTITION (p1) AS s ON e.store_id=s.id
6    ->     JOIN departments PARTITION (p0) AS d ON e.department_id=d.id
7    -> ORDER BY e.lname;
8+-------------+---------------+-----------+------------+
9| Employee ID | Name          | City      | department |
10+-------------+---------------+-----------+------------+
11|          14 | Fred Goldberg | Bellingen | Delivery   |
12|           5 | Mary Jones    | Nambucca  | Sales      |
13|          17 | Mark Morgan   | Bellingen | Delivery   |
14|           9 | Andy Smith    | Nambucca  | Delivery   |
15|           8 | June Wilson   | Bellingen | Sales      |
16+-------------+---------------+-----------+------------+
175 rows in set (0.00 sec)

MySQL에서 조인에 대한 일반적인 정보는 Section 15.2.13.2, “JOIN Clause”를 참조하십시오.

PARTITION 옵션이 DELETE 문과 함께 사용되면, 옵션에 나열된 파티션(및 존재하는 경우 서브파티션)만 삭제할 행을 찾기 위해 검사됩니다. 나머지 파티션은 다음 예와 같이 무시됩니다:

1mysql> SELECT * FROM employees WHERE fname LIKE 'j%';
2+----+-------+--------+----------+---------------+
3| id | fname | lname  | store_id | department_id |
4+----+-------+--------+----------+---------------+
5|  4 | Jim   | Smith  |        2 |             4 |
6|  8 | June  | Wilson |        3 |             1 |
7| 11 | Jill  | Stone  |        1 |             4 |
8+----+-------+--------+----------+---------------+
93 rows in set (0.00 sec)
10
11mysql> DELETE FROM employees PARTITION (p0, p1)
12    ->     WHERE fname LIKE 'j%';
13Query OK, 2 rows affected (0.09 sec)
14
15mysql> SELECT * FROM employees WHERE fname LIKE 'j%';
16+----+-------+-------+----------+---------------+
17| id | fname | lname | store_id | department_id |
18+----+-------+-------+----------+---------------+
19| 11 | Jill  | Stone |        1 |             4 |
20+----+-------+-------+----------+---------------+
211 row in set (0.00 sec)

WHERE 조건과 일치하는 행 중에서 파티션 p0 및 p1에 있는 두 행만 삭제되었습니다. SELECT를 두 번째로 실행했을 때의 결과에서 알 수 있듯이, WHERE 조건과 일치하지만 다른 파티션(p2)에 존재하는 행이 테이블에 남아 있습니다.

명시적인 파티션 선택을 사용하는 UPDATE 문도 같은 방식으로 동작합니다. PARTITION 옵션에 의해 참조되는 파티션에 있는 행만 업데이트할 행을 결정할 때 고려되며, 다음 문장들을 실행하면 이를 확인할 수 있습니다:

1mysql> UPDATE employees PARTITION (p0)
2    ->     SET store_id = 2 WHERE fname = 'Jill';
3Query OK, 0 rows affected (0.00 sec)
4Rows matched: 0  Changed: 0  Warnings: 0
5
6mysql> SELECT * FROM employees WHERE fname = 'Jill';
7+----+-------+-------+----------+---------------+
8| id | fname | lname | store_id | department_id |
9+----+-------+-------+----------+---------------+
10| 11 | Jill  | Stone |        1 |             4 |
11+----+-------+-------+----------+---------------+
121 row in set (0.00 sec)
13
14mysql> UPDATE employees PARTITION (p2)
15    ->     SET store_id = 2 WHERE fname = 'Jill';
16Query OK, 1 row affected (0.09 sec)
17Rows matched: 1  Changed: 1  Warnings: 0
18
19mysql> SELECT * FROM employees WHERE fname = 'Jill';
20+----+-------+-------+----------+---------------+
21| id | fname | lname | store_id | department_id |
22+----+-------+-------+----------+---------------+
23| 11 | Jill  | Stone |        2 |             4 |
24+----+-------+-------+----------+---------------+
251 row in set (0.00 sec)

같은 방식으로, PARTITION이 DELETE와 함께 사용될 때에는 파티션 목록에 이름이 나열된 파티션(또는 파티션들)에 있는 행만 삭제 대상으로 검사됩니다.

행을 삽입하는 문장의 경우, 적절한 파티션을 찾지 못하면 문장이 실패한다는 점에서 동작이 다릅니다. 이는 INSERT와 REPLACE 문 모두에 대해 다음 예와 같이 적용됩니다:

1mysql> INSERT INTO employees PARTITION (p2) VALUES (20, 'Jan', 'Jones', 1, 3);
2ERROR 1729 (HY000): Found a row not matching the given partition set
3mysql> INSERT INTO employees PARTITION (p3) VALUES (20, 'Jan', 'Jones', 1, 3);
4Query OK, 1 row affected (0.07 sec)
5
6mysql> REPLACE INTO employees PARTITION (p0) VALUES (20, 'Jan', 'Jones', 3, 2);
7ERROR 1729 (HY000): Found a row not matching the given partition set
8
9mysql> REPLACE INTO employees PARTITION (p3) VALUES (20, 'Jan', 'Jones', 3, 2);
10Query OK, 2 rows affected (0.09 sec)

InnoDB 스토리지 엔진을 사용하는 파티션 테이블에 여러 행을 쓰는 문장의 경우: VALUES 뒤의 목록에 있는 행들 중 하나라도 partition_names 목록에 지정된 파티션 중 하나에 쓸 수 없으면, 전체 문장이 실패하며 어떤 행도 쓰이지 않습니다. 이는 앞에서 생성한 employees 테이블을 재사용한 다음 예에서 INSERT 문에 대해 보여 줍니다:

1mysql> ALTER TABLE employees
2    ->     REORGANIZE PARTITION p3 INTO (
3    ->         PARTITION p3 VALUES LESS THAN (20),
4    ->         PARTITION p4 VALUES LESS THAN (25),
5    ->         PARTITION p5 VALUES LESS THAN MAXVALUE
6    ->     );
7Query OK, 6 rows affected (2.09 sec)
8Records: 6  Duplicates: 0  Warnings: 0
9
10mysql> SHOW CREATE TABLE employees\G
11*************************** 1. row ***************************
12       Table: employees
13Create Table: CREATE TABLE `employees` (
14  `id` int(11) NOT NULL AUTO_INCREMENT,
15  `fname` varchar(25) NOT NULL,
16  `lname` varchar(25) NOT NULL,
17  `store_id` int(11) NOT NULL,
18  `department_id` int(11) NOT NULL,
19  PRIMARY KEY (`id`)
20) ENGINE=InnoDB AUTO_INCREMENT=27 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
21/*!50100 PARTITION BY RANGE (id)
22(PARTITION p0 VALUES LESS THAN (5) ENGINE = InnoDB,
23 PARTITION p1 VALUES LESS THAN (10) ENGINE = InnoDB,
24 PARTITION p2 VALUES LESS THAN (15) ENGINE = InnoDB,
25 PARTITION p3 VALUES LESS THAN (20) ENGINE = InnoDB,
26 PARTITION p4 VALUES LESS THAN (25) ENGINE = InnoDB,
27 PARTITION p5 VALUES LESS THAN MAXVALUE ENGINE = InnoDB) */
281 row in set (0.00 sec)
29
30mysql> INSERT INTO employees PARTITION (p3, p4) VALUES
31    ->     (24, 'Tim', 'Greene', 3, 1),  (26, 'Linda', 'Mills', 2, 1);
32ERROR 1729 (HY000): Found a row not matching the given partition set
33
34mysql> INSERT INTO employees PARTITION (p3, p4, p5) VALUES
35    ->     (24, 'Tim', 'Greene', 3, 1),  (26, 'Linda', 'Mills', 2, 1);
36Query OK, 2 rows affected (0.06 sec)
37Records: 2  Duplicates: 0  Warnings: 0

앞서 설명한 내용은 여러 행을 쓰는 INSERT 문과 REPLACE 문 모두에 대해 동일하게 적용됩니다.

자동 파티셔닝을 제공하는 NDB 같은 스토리지 엔진을 사용하는 테이블에 대해서는 파티션 선택이 비활성화됩니다.