MySQL 5.7 分区选择

分区选择
MySQL 5.7支持显式选择分区和子分区，当执行语句时，应该检查是否符合给定的WHERE条件。分区选择与分区修剪类似，只检查特定的分区是否 匹配，但在两个关键方面有所不同。
1.要检查的分区由语句的发布者指定，这与自动进行分区修剪不同

2.虽然分区修剪仅适用于查询，但查询和许多DML语句都支持显式选择分区。

下面列出了支持显式分区选择的SQL语句：

SELECT
DELETE
INSERT
REPLACE
UPDATE
LOAD DATA.
LOAD XML.

显式分区选择是使用partition选项实现的。对于所有支持的语句，该选项使用如下所示的语法：

PARTITION (partition_names)
partition_names:
partition_name, ...

此选项始终跟随分区所属的表的名称。partition_names是要使用的分区或子分区的列表，用逗号分隔。该列表中的每个名称都必须是指定表的 现有分区或子分区的名称；如果没有找到任何分区或子分区，则语句失败并报错（partition ‘partition_name’ doesn’t exist）。以partition_names命名的分区和子分区可以以任意顺序列出，也可以重叠。

当使用PARTITION选项时，只检查列出的分区和子分区是否匹配行。这个选项可以在SELECT语句中使用，以确定哪些行属于给定的分区。
考虑一个名为employees的分区表，使用下面的语句创建和填充：

mysql> 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
    -> );
Query OK, 0 rows affected (0.02 sec)

mysql> 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);
Query OK, 18 rows affected, 18 warnings (0.00 sec)
Records: 18  Duplicates: 0  Warnings: 18

您可以看到哪些行存储在分区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.00 sec)
mysql> SELECT store_id, COUNT(department_id) AS c FROM employees PARTITION (p1,p2,p3) GROUP BY store_id HAVING c > 4;
+----------+---+
| store_id | c |
+----------+---+
|        2 | 5 |
|        3 | 5 |
+----------+---+
2 rows in set (0.00 sec)

您也可以在INSERT…SELECT的SELECT部分使用PARTITION选项，如下所示：

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

mysql> INSERT INTO employees_copy SELECT * FROM employees PARTITION (p2);
Query OK, 5 rows affected (0.00 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)

分区选择也可以用于连接。假设我们使用下面的语句创建并填充两个表：

mysql> CREATE TABLE stores (
    -> id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    -> city VARCHAR(30) NOT NULL
    -> )
    -> PARTITION BY HASH(id)
    -> PARTITIONS 2;
Query OK, 0 rows affected (0.01 sec)

mysql> INSERT INTO stores VALUES
    -> ('', 'Nambucca'), ('', 'Uranga'),
    -> ('', 'Bellingen'), ('', 'Grafton');
Query OK, 4 rows affected, 4 warnings (0.00 sec)
Records: 4  Duplicates: 0  Warnings: 4

mysql> CREATE TABLE departments (
    -> id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    -> name VARCHAR(30) NOT NULL
    -> )
    -> PARTITION BY KEY(id)
    -> PARTITIONS 2;
Query OK, 0 rows affected (0.01 sec)

mysql> INSERT INTO departments VALUES
    -> ('', 'Sales'), ('', 'Customer Service'),
    -> ('', 'Delivery'), ('', 'Accounting');
Query OK, 4 rows affected, 4 warnings (0.00 sec)
Records: 4  Duplicates: 0  Warnings: 4

您可以显式地从连接中的任何或所有表中选择分区（或子分区，或两者都选择）。(PARTITION选项用于从给定表中选择分区，该选项紧跟在表名 之后，位于所有其他选项（包括任何表别名）之前。例如，下面的查询获取在Nambucca和Bellingen两个城市（stores表的分区p0）的商店中 Sales或Delivery部门（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)

当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.00 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.00 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 1748 (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.00 sec)

mysql> REPLACE INTO employees PARTITION (p0) VALUES (20, 'Jan', 'Jones', 3, 2);
ERROR 1748 (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.01 sec)

对于在使用InnoDB存储引擎的分区表中写多行数据的语句：如果下列列表中的任何一行不能写入partition_names列表中指定的任何一个分区， 则整个语句失败，不会写入任何行。下面的例子展示了如何使用INSERT语句，重用了之前创建的employees表：

mysql> INSERT INTO employees PARTITION (p3, p4) VALUES (24, 'Tim', 'Greene', 3, 1), (26, 'Linda', 'Mills', 2, 1);
ERROR 1748 (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.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

对于写入多行的INSERT语句和REPLACE语句，上述情况都成立。

在MySQL 5.7.1及更高版本中，对于使用提供自动分区的存储引擎（如NDB）的表，禁用分区选择。(错误# 14827952)