19.5 파티션 선택
MySQL 5.6은 문을 실행할 때 지정된 WHERE 조건과 일치하는 행을 확인해야 파티션 및 하위 파티션의 명시 적 선택을 지원합니다. 파티션 선택은 단일 파티션에서 일치가 확인되는 시점에서 파티션 가지 치기와 비슷하지만 두 가지 중요한 점에서 다릅니다.
체크 된 파티션은 파티션 가지 치기와 달리 (자동), 문 발행자가 지정합니다.
파티션 가지 치기는 쿼리에만 적용되지만 명시 적 파티션 선택은 쿼리 및 일부 DML 문에서 모두 지원됩니다.
명시적인 파티션 선택을 지원하는 SQL 문을 다음 나열합니다.
SELECTDELETEINSERTREPLACEUPDATELOAD DATA.LOAD XML.
이 섹션의 나머지 부분에서는 상기 열거 한 문에 일반적으로 적용되는 명시 적 파티션 선택에 대해 설명하고 몇 가지 예를 보여줍니다.
명시 적 파티션 선택은 PARTITION 옵션을 사용하여 구현됩니다. 지원되는 모든 문에이 옵션은 다음과 같은 구문을 사용합니다.
PARTITION (partition_names)partition_names:partition_name, ...
이 옵션은 항상 파티션이 속한 테이블의 이름 뒤에 계속합니다. partition_names 사용되는 파티션 또는 서브 파티션의 쉼표로 구분 된 목록입니다. 이 목록의 각 이름은 지정된 테이블의 기존 파티션 또는 서브 파티션의 이름이어야합니다. 파티션 또는 서브 파티션이 없으면 문은 오류 (partition ' partition_name 'does not exist)에 실패합니다. partition_names 에 지정된 파티션 또는 서브 파티션은 임의의 순서로 나열 할 수 중복 있어도 괜찮습니다.
PARTITION 옵션을 사용하면 나열된 파티션 및 하위 파티션 만 일치하는 행이 선택됩니다. 이 옵션을 SELECT 문에 사용하면 지정한 파티션에 속하는 행을 확인할 수 있습니다. 다음과 같은 명령문을 사용하여 만들고 이입 된 employees 라는 파티션 된 테이블이 있다고합니다.
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)
이 결과는 쿼리 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 테이블에서 실행할 때 올바른 결과를 생성합니다.
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.6에서 지원되는 파티셔닝 유형을 사용하는 테이블로 사용할 수 있습니다. 테이블이 [LINEAR] HASH 또는 [LINEAR] KEY 파티셔닝을 사용하여 작성되어 있지만, 파티션의 이름이 지정되어 있지 않은 경우, MySQL은 파티션에 p0 , p1 , p2 , ..., p 이라는 이름을 자동으로 지정합니다. 여기서 N-1N 은 파티션의 수입니다. 명시 적으로 이름이 지정되지 않은 서브 파티션의 경우 MySQL은 각 파티션 p 의 하위 파티션에 Xp , X sp0p , X sp1p , ..., X sp2p 이라는 이름을 자동으로 할당합니다. 여기서 X sp M-1M 은 서브 파티션의 수입니다. 이 테이블에서 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)
다음과 같이 PARTITION 옵션을 INSERT ... SELECT 문 SELECT 부분에 사용할 수 있습니다.
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 ( stores 테이블의 파티션 p0 ) 중 하나의 점포의 영업 부서 또는 배송 부서 ( departments 테이블의 파티션 p1 )에서 근무하는 모든 직원의 이름, 직원 ID 부서 및 도시를 가져옵니다.
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)
MySQL의 조인 일반 정보 섹션 13.2.9.2 "JOIN" 을 참조하십시오.
DELETE 문에서 PARTITION 옵션을 사용하면 옵션에 나열되어있는 파티션 (및 하위 파티션 (있는 경우))에서만 삭제되는 행이 선택됩니다. 다음과 같이 다른 파티션은 무시됩니다.
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)
WHERE 조건과 일치하는 파티션 p0 및 p1 의 두 행만 삭제되었습니다. 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)
마찬가지로, DELETE 함께 PARTITION 을 사용하면 파티션 목록에 지정된 파티션의 행만 삭제를 확인합니다.
행을 삽입하는 문의 동작은 올바른 파티션을 찾지 못하면 문이 실패하는 점이 다릅니다. 이것은 다음과 같이 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 목록에 지정된 파티션 중 하나에 쓸 수 없을 때에는 문 전체 실패 일행도 기록되지 않습니다. 이에 대해서는 다음의 예 ( employees 테이블을 재사용)의 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.6.10 이후에는 자동 파티셔닝을 제공하는 스토리지 엔진 ( NDB )을 사용하는 테이블의 경우 파티션 선택은 무효가됩니다. (Bug # 14827952).