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

인프라쟁이 DBA
Databases/MySQL
2020. 9. 2.

■ 파티션 선택

MySQL 5.7은 명령문을 실행할때 주어진 WHERE 조건과 일치하는 행을 검사해야하는 파티션 및 하위 파티션의 명시적 선택을 지원합니다. 파티션 선택은 특정 파티션 만 일치하는지 검사하지만 두 가지 주요 측면에서 다르다는 점에서 파티션 정리와 유사합니다.

1. 점검할 파티션은 파티션 프루닝과 달리 자동으로 명령문을 발행하여 지정합니다.

2. 파티션 프루닝은 쿼리에만 적용되는 반면, 파티션의 명시적 선택은 쿼리와 다수의 DML문 모두에 대해 지원됩니다.

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

1. SELECT

2. DELETE

3. INSERT

4. REPLACE

5. UPDATE

6. LOAD DATA.

7. LOAD XML.

이 절의 나머지 부분에서는 방금 나열된 명령문에 적용되는 명시적 파티션 선택에 대해 설명하고 몇가지 예를 통해서 알아봅니다.

명시적 파티션 선택은 PARTITION 옵션을 사용하여 구현됩니다. 지원되는 모든 명령문에 대해 이 옵션은 다음에 표시된 구문을 사용합니다.

PARTITION (partition_names)

partition_names:

partition_name, ...

이 옵션은 항상 파티션이 속한 테이블의 이름을 따릅니다. partition_names는 사용할 쉼표로 구분된 파티션 또는 하위 파티션 목록입니다. 이 목록의 각 이름은 지정된 테이블의 기존 파티션 또는 하위 파티션 이름이어야합니다. 파티션 또는 하위 파티션이 없으면 명령문이 오류와 함께 실패합니다 (파티션 'partition_name'이 존재하지 않음). partition_names로 명명된 파티션과 하위 파티션은 순서에 상관없이 나열될 수 있으며 겹칠 수 있습니다.

PARTITION옵션을 사용하면 나열된 파티션과 하위 파티션만 일치하는 행을 검사합니다. 이 옵션을 SELECT 문에서 사용하여 주어진 파티션에 속하는 행을 판별할 수 있습니다. 여기에 표시된 명령문을 사용하여 작성되고 채워진 employee라는 파티션된 테이블을 만들어봅니다.

SET @@SQL_MODE = '';

CREATE TABLE employees  (
    id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    fname VARCHAR(25) NOT NULL,
    lname VARCHAR(25) NOT NULL,
    store_id INT NOT NULL,
    department_id INT NOT NULL
)
    PARTITION BY RANGE(id)  (
        PARTITION p0 VALUES LESS THAN (5),
        PARTITION p1 VALUES LESS THAN (10),
        PARTITION p2 VALUES LESS THAN (15),
        PARTITION p3 VALUES LESS THAN MAXVALUE
);

INSERT INTO employees VALUES
    ('', 'Bob', 'Taylor', 3, 2), ('', 'Frank', 'Williams', 1, 2),
    ('', 'Ellen', 'Johnson', 3, 4), ('', 'Jim', 'Smith', 2, 4),
    ('', 'Mary', 'Jones', 1, 1), ('', 'Linda', 'Black', 2, 3),
    ('', 'Ed', 'Jones', 2, 1), ('', 'June', 'Wilson', 3, 1),
    ('', 'Andy', 'Smith', 1, 3), ('', 'Lou', 'Waters', 2, 4),
    ('', 'Jill', 'Stone', 1, 4), ('', 'Roger', 'White', 3, 2),
    ('', 'Howard', 'Andrews', 1, 2), ('', 'Fred', 'Goldberg', 3, 3),
    ('', 'Barbara', 'Brown', 2, 3), ('', 'Alice', 'Rogers', 2, 2),
    ('', 'Mark', 'Morgan', 3, 3), ('', 'Karen', 'Cole', 3, 2);

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

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

결과는 쿼리 5에서 9 사이의 직원 ID SELECT * FROM에서 얻은 것과 동일합니다.

여러 파티션에서 행을 얻으려면 해당 이름을 쉼표로 구분된 목록으로 작성합니다. 예를 들어, SELECT * FROM employee PARTITION (p1, p2)은 나머지 파티션에서 행을 제외하고 파티션 p1및 p2에서 모든 행을 리턴합니다.

파티션된 테이블에 대한 유효한 쿼리는 PARTITION 옵션으로 다시 작성하여 결과를 하나 이상의 원하는 파티션으로 제한할 수 있습니다. WHERE 조건, ORDER BY 및 LIMIT 옵션등을 사용할 수 있습니다. HAVING 및 GROUP BY옵션과 함께 집계 함수를 사용할 수도 있습니다. 다음 각 쿼리는 앞에서 정의한 것처럼 직원 테이블에서 실행될때 유효한 결과를 생성합니다.

mysql> SELECT * FROM employees PARTITION (p0, p2)
    ->     WHERE lname LIKE 'S%';
+----+-------+-------+----------+---------------+
| id | fname | lname | store_id | department_id |
+----+-------+-------+----------+---------------+
|  4 | Jim   | Smith |        2 |             4 |
| 11 | Jill  | Stone |        1 |             4 |
+----+-------+-------+----------+---------------+
2 rows in set (0.00 sec)

mysql> SELECT id, CONCAT(fname, ' ', lname) AS name
    ->     FROM employees PARTITION (p0) ORDER BY lname;
+----+----------------+
| id | name           |
+----+----------------+
|  3 | Ellen Johnson  |
|  4 | Jim Smith      |
|  1 | Bob Taylor     |
|  2 | Frank Williams |
+----+----------------+
4 rows in set (0.06 sec)

mysql> SELECT store_id, COUNT(department_id) AS c
    ->     FROM employees PARTITION (p1,p2,p3)
    ->     GROUP BY store_id HAVING c > 4;
+---+----------+
| c | store_id |
+---+----------+
| 5 |        2 |
| 5 |        3 |
+---+----------+
2 rows in set (0.00 sec)

파티션 선택을 사용하는 명령문은 MySQL 5.7에서 지원되는 파티션 유형을 사용하는 테이블과 함께 사용할 수 있습니다. [LINEAR] HASH 또는 [LINEAR] KEY파티션을 사용하여 테이블을 생성하고 파티션 이름을 지정하지 않으면 MySQL은 자동으로 파티션 이름을 p0, p1, p2, ..., pN-1로 지정합니다. 여기서 N은 파티션의 숫자입니다. 명시적으로 이름이 지정되지 않은 하위 파티션의 경우 MySQL은 각 파티션 pX의 하위 파티션에 이름 pXsp0, pXsp1, pXsp2, ..., pXspM-1을 자동으로 할당합니다. 여기서 M은 하위 파티션 수입니다. 이 테이블에 대해 SELECT(또는 명시적 파티션 선택이 허용되는 다른 SQL 문)를 실행할 때 다음과 같이 PARTITION 옵션에서 생성된 이름을 사용할 수 있습니다.

mysql> CREATE TABLE employees_sub  (
    ->     id INT NOT NULL AUTO_INCREMENT,
    ->     fname VARCHAR(25) NOT NULL,
    ->     lname VARCHAR(25) NOT NULL,
    ->     store_id INT NOT NULL,
    ->     department_id INT NOT NULL,
    ->     PRIMARY KEY pk (id, lname)
    -> )
    ->     PARTITION BY RANGE(id)
    ->     SUBPARTITION BY KEY (lname)
    ->     SUBPARTITIONS 2 (
    ->         PARTITION p0 VALUES LESS THAN (5),
    ->         PARTITION p1 VALUES LESS THAN (10),
    ->         PARTITION p2 VALUES LESS THAN (15),
    ->         PARTITION p3 VALUES LESS THAN MAXVALUE
    -> );
Query OK, 0 rows affected (1.14 sec)

mysql> INSERT INTO employees_sub   # re-use data in employees table
    ->     SELECT * FROM employees;
Query OK, 18 rows affected (0.09 sec)
Records: 18  Duplicates: 0  Warnings: 0

mysql> SELECT id, CONCAT(fname, ' ', lname) AS name
    ->     FROM employees_sub PARTITION (p2sp1);
+----+---------------+
| id | name          |
+----+---------------+
| 10 | Lou Waters    |
| 14 | Fred Goldberg |
+----+---------------+
2 rows in set (0.00 sec)

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

mysql> CREATE TABLE employees_copy LIKE employees;
Query OK, 0 rows affected (0.28 sec)

mysql> INSERT INTO employees_copy
    ->     SELECT * FROM employees PARTITION (p2);
Query OK, 5 rows affected (0.04 sec)
Records: 5  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM employees_copy;
+----+--------+----------+----------+---------------+
| id | fname  | lname    | store_id | department_id |
+----+--------+----------+----------+---------------+
| 10 | Lou    | Waters   |        2 |             4 |
| 11 | Jill   | Stone    |        1 |             4 |
| 12 | Roger  | White    |        3 |             2 |
| 13 | Howard | Andrews  |        1 |             2 |
| 14 | Fred   | Goldberg |        3 |             3 |
+----+--------+----------+----------+---------------+
5 rows in set (0.00 sec)

파티션 선택은 조인과 함께 사용할 수도 있습니다. 여기에 표시된 명령문을 사용하여 두 개의 테이블을 작성하고 데이터를 입력합니다.

CREATE TABLE stores (
    id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    city VARCHAR(30) NOT NULL
)
    PARTITION BY HASH(id)
    PARTITIONS 2;

INSERT INTO stores VALUES
    ('', 'Nambucca'), ('', 'Uranga'),
    ('', 'Bellingen'), ('', 'Grafton');

CREATE TABLE departments  (
    id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(30) NOT NULL
)
    PARTITION BY KEY(id)
    PARTITIONS 2;

INSERT INTO departments VALUES
    ('', 'Sales'), ('', 'Customer Service'),
    ('', 'Delivery'), ('', 'Accounting');

조인의 일부 또는 모든 테이블에서 파티션(또는 하위 파티션 또는 둘 다)을 명시 적으로 선택할 수 있습니다. 지정된 테이블에서 파티션을 선택하는데 사용되는 PARTITION옵션은 테이블 별칭을 포함하여 다른 모든 옵션보다 먼저 테이블 이름 바로 뒤에 옵니다. 예를 들어 다음 쿼리는 Nambucca 및 Bellingen시 (분할 구역)의 상점에있는 영업 또는 배달 부서 (department 테이블의 파티션 p1)에서 일하는 모든 직원의 이름, 직원 ID, 부서 및 도시를 가져옵니다. (store 테이블의 파티션 p0) :

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

PARTITION 옵션을 DELETE 문과 함께 사용하면 옵션과 함께 나열된 파티션 (및 서브 파티션) 만 삭제 될 행을 검사합니다. 다음과 같이 다른 파티션은 무시됩니다.

mysql> SELECT * FROM employees WHERE fname LIKE 'j%';
+----+-------+--------+----------+---------------+
| id | fname | lname  | store_id | department_id |
+----+-------+--------+----------+---------------+
|  4 | Jim   | Smith  |        2 |             4 |
|  8 | June  | Wilson |        3 |             1 |
| 11 | Jill  | Stone  |        1 |             4 |
+----+-------+--------+----------+---------------+
3 rows in set (0.00 sec)

mysql> DELETE FROM employees PARTITION (p0, p1)
    ->     WHERE fname LIKE 'j%';
Query OK, 2 rows affected (0.09 sec)

mysql> SELECT * FROM employees WHERE fname LIKE 'j%';
+----+-------+-------+----------+---------------+
| id | fname | lname | store_id | department_id |
+----+-------+-------+----------+---------------+
| 11 | Jill  | Stone |        1 |             4 |
+----+-------+-------+----------+---------------+

1 row in set (0.00 sec)

파티션 p0및 p1에서 WHERE 조건과 일치하는 두 행만 삭제되었습니다. SELECT가 두번째로 실행될때 결과에서 볼 수 있듯이 WHERE 조건과 일치하지만 다른 파티션 (p2)에 상주하는 행이 테이블에 남아 있습니다.

명시적 파티션 선택을 사용하는 UPDATE 문은 동일한 방식으로 작동합니다. PARTITION 옵션이 참조하는 파티션의 행만 다음 명령문을 실행하여 볼 수 있는 것처럼 업데이트할 행을 결정할 때 고려됩니다.

mysql> UPDATE employees PARTITION (p0) 
    ->     SET store_id = 2 WHERE fname = 'Jill';
Query OK, 0 rows affected (0.00 sec)
Rows matched: 0  Changed: 0  Warnings: 0

mysql> SELECT * FROM employees WHERE fname = 'Jill';
+----+-------+-------+----------+---------------+
| id | fname | lname | store_id | department_id |
+----+-------+-------+----------+---------------+
| 11 | Jill  | Stone |        1 |             4 |
+----+-------+-------+----------+---------------+
1 row in set (0.00 sec)

mysql> UPDATE employees PARTITION (p2)
    ->     SET store_id = 2 WHERE fname = 'Jill';
Query OK, 1 row affected (0.09 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> SELECT * FROM employees WHERE fname = 'Jill';
+----+-------+-------+----------+---------------+
| id | fname | lname | store_id | department_id |
+----+-------+-------+----------+---------------+
| 11 | Jill  | Stone |        2 |             4 |
+----+-------+-------+----------+---------------+
1 row in set (0.00 sec)

같은 방법으로 PARTITION을 DELETE와 함께 사용하면 파티션의 행 또는 파티션 목록에 이름이 지정된 파티션만 삭제를 검사합니다.

행을 삽입하는 명령문의 경우, 적합한 파티션을 찾지 못하면 명령문이 실패한다는 점에서 동작이 다릅니다. 다음과 같이 INSERT 및 REPLACE 문 모두에 해당됩니다.

mysql> INSERT INTO employees PARTITION (p2) VALUES (20, 'Jan', 'Jones', 1, 3);
ERROR 1729 (HY000): Found a row not matching the given partition set
mysql> INSERT INTO employees PARTITION (p3) VALUES (20, 'Jan', 'Jones', 1, 3);
Query OK, 1 row affected (0.07 sec)

mysql> REPLACE INTO employees PARTITION (p0) VALUES (20, 'Jan', 'Jones', 3, 2);
ERROR 1729 (HY000): Found a row not matching the given partition set

mysql> REPLACE INTO employees PARTITION (p3) VALUES (20, 'Jan', 'Jones', 3, 2);
Query OK, 2 rows affected (0.09 sec)

InnoDB 스토리지 엔진을 사용하는 파티션된 테이블에 여러 행을 쓰는 명령문의 경우 VALUES 다음에 있는 목록의 행을 partition_names 목록에 지정된 파티션 중 하나에 쓸 수 없으면 전체 명령문이 실패하고 행이 기록되지 않습니다. 이는 다음 예의 INSERT 문에 대해 표시되며 이전에 작성된 직원 테이블을 재사용합니다.

mysql> ALTER TABLE employees
    ->     REORGANIZE PARTITION p3 INTO (
    ->         PARTITION p3 VALUES LESS THAN (20),
    ->         PARTITION p4 VALUES LESS THAN (25),
    ->         PARTITION p5 VALUES LESS THAN MAXVALUE
    ->     );
Query OK, 6 rows affected (2.09 sec)
Records: 6  Duplicates: 0  Warnings: 0

mysql> SHOW CREATE TABLE employees\G
*************************** 1. row ***************************
       Table: employees
Create Table: CREATE TABLE `employees` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `fname` varchar(25) NOT NULL,
  `lname` varchar(25) NOT NULL,
  `store_id` int(11) NOT NULL,
  `department_id` int(11) NOT NULL,
  PRIMARY KEY (`id`
) ENGINE=InnoDB AUTO_INCREMENT=27 DEFAULT CHARSET=latin1
/*!50100 PARTITION BY RANGE (id)
(PARTITION p0 VALUES LESS THAN (5) ENGINE = InnoDB,
 PARTITION p1 VALUES LESS THAN (10) ENGINE = InnoDB,
 PARTITION p2 VALUES LESS THAN (15) ENGINE = InnoDB,
 PARTITION p3 VALUES LESS THAN (20) ENGINE = InnoDB,
 PARTITION p4 VALUES LESS THAN (25) ENGINE = InnoDB,
 PARTITION p5 VALUES LESS THAN MAXVALUE ENGINE = InnoDB) */
1 row in set (0.00 sec)

mysql> INSERT INTO employees PARTITION (p3, p4) VALUES
    ->     (24, 'Tim', 'Greene', 3, 1),  (26, 'Linda', 'Mills', 2, 1);
ERROR 1729 (HY000): Found a row not matching the given partition set

mysql> INSERT INTO employees PARTITION (p3, p4. p5) VALUES
    ->     (24, 'Tim', 'Greene', 3, 1),  (26, 'Linda', 'Mills', 2, 1);
Query OK, 2 rows affected (0.06 sec)
Records: 2  Duplicates: 0  Warnings: 0

위의 내용은 여러 행을 쓰는 INSERT 문과 REPLACE 문 모두에 해당됩니다.

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

□ 테이블 파티셔닝 링크

[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/98

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

■ 파티션 관리 MySQL 5.7은 분할된 테이블을 수정하는 여러 가지 방법을 제공합니다. 기존 파티션을 추가, 삭제, 재정의, 병합 또는 분할할 수 있습니다. 이러한 모든 조치는 ALTER TABLE 문에 대한 파

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/103

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

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

myinfrabox.tistory.com

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

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

[MySQL] START TRANSACTION, COMMIT, 그리고 ROLLBACK문 (0)	2020.09.04
[MySQL] Table Partitioning-파티셔닝에 대한 제한 사항 (0)	2020.09.03
[MySQL] Table Partitioning - 파티션 프루닝(파티션 정리) (0)	2020.08.30
[MySQL] Table Partitioning - 파티셔닝 관리 (0)	2020.08.22
[MySQL] Table Partitioning-파티셔닝 타입 (0)	2020.08.20

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

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

Copyright © MyInfraBox All Rights Reserved

Designed by JB FACTORY

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

'Databases/MySQL' 관련 글

[MySQL] START TRANSACTION, COMMIT, 그리고 ROLLBACK문

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

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

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

Copyright © MyInfraBox All Rights Reserved

Designed by JB FACTORY

티스토리툴바