19.5 Partition Selection 分区查询

19.5 Partition Selection

MySQL 5.6 支持显示的查询分区和子分区,当执行一个语句, 应该坚持给定的where条件匹配的行。 分区查询类似于分区裁剪,

在特定的分区中检查匹配，但在2个关键方面不同：

1.被检查的分区是指定通过执行语句,不像分区裁剪,这是自动的。

1. 而分区裁剪只能用于查询,显示查询分区是查询和DML语句都支持

SQL语句支持显式分区查询如下:

SELECT

DELETE

INSERT

REPLACE

UPDATE

LOAD DATA.

LOAD XML.

本节的其余部分讨论显示的分区查询,因为它通常应用于查询，提供一些例子:

显示的分区查询 是实现使用分区选项, 对于所有的支持的语句:

PARTITION (partition_names)

  partition_names:
      partition_name, ...

此选项总是遵循 表的名字（分区所属的表)， partition_names 是一个逗号分隔的分区列表。

当分区选项被使用, 只有列出的分区或者子分区会被检索用于匹配的行。这个选项可以用于SELECT 语句,来确定哪些行属于给定的分区。

考虑一个名为雇员的分区表, 创建和填充使用语句如下:

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);
You can see which rows are stored in partition p1 like this:

      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分区所有的记录:

对分区表进行任何有效的查询, 可以重写用一个分区选项 来限制结果到一个或者多个需要的分区。你可以使用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)

语句使用分区查询可以被用于使用任何分区类型的, 当一个表时使用 [LINEAR] HASH or [LINEAR] KEY 分区和

分区的名字没有指定,MySQL 自动命名分区p0,p1,p2….pN-1， N是分区的数量。