分区选择
WHERE
支持为符合给定条件的行显式选择分区和子分区。分区选择类似于分区修剪,因为只检查特定的分区是否匹配,但是在两个关键方面有所不同:
- 与要自动执行的分区修剪不同,要检查的分区由语句的发布者指定。
- 分区修剪仅适用于查询,而查询和许多DML语句均支持明确选择分区。
下面列出了支持显式分区选择的SQL语句:
SELECT
DELETE
INSERT
REPLACE
UPDATE
LOAD DATA
。LOAD XML
。
本节的其余部分讨论显式分区选择,因为它通常适用于刚刚列出的语句,并提供了一些示例。
显式分区选择是使用PARTITION
选项实现的。对于所有受支持的语句,此选项使用此处显示的语法:
PARTITION (partition_names) partition_names: partition_name, ...
此选项始终跟随一个或多个分区所属的表的名称。partition_names
是要使用的分区或子分区的逗号分隔列表。此列表中的每个名称都必须是指定表的现有分区或子分区的名称;如果未找到任何分区或子分区,则该语句失败,并显示错误(分区'partition_name
'不存在)。中命名的分区和子分区partition_names
可以按任何顺序列出,并且可以重叠。
使用该PARTITION
选项时,仅检查列出的分区和子分区的匹配行。可以在SELECT
语句中使用此选项来确定哪些行属于给定分区。考虑一个分区表employees
,该表使用以下所示的语句创建和填充:
SET @@SQL_MODE = '';CREATE TABLE employees ( id INT NOT NULLAUTO_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 p0VALUES LESS THAN (5),PARTITION p1VALUES LESS THAN (10),PARTITION p2VALUES LESS THAN (15),PARTITION p3VALUES LESS THAN MAXVALUE );INSERT INTO employeesVALUES ('', '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 employeesPARTITION (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 employeesPARTITION (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 employeesPARTITION (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 employeesPARTITION (p1,p2,p3) ->GROUP BY store_idHAVING c > 4; +--- +---------- + | c | store_id | +--- +---------- + | 5 | 2 | | 5 | 3 | +--- +---------- + 2 rows in set (0.00 sec)
使用分区选择的语句可以与使用任何受支持的分区类型的表一起使用。当使用创建表[LINEAR] HASH
或[LINEAR] KEY
分区和未指定分区的名字,MySQL的自动命名分区p0
,p1
,p2
,...,那里是分区的数量。对于子分区不明确命名时,MySQL会自动分配到子分区中的每个分区名,,,...,那里是子分区的数量。针对该表执行时pN-1
N
pX
pXsp0
pXsp1
pXsp2
pXspM-1
M
SELECT
(或其他允许显式分区选择的SQL语句),您可以在PARTITION
选项中使用这些生成的名称,如下所示:
mysql>CREATE TABLE employees_sub ( -> id INT NOT NULLAUTO_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 p0VALUES LESS THAN (5), ->PARTITION p1VALUES LESS THAN (10), ->PARTITION p2VALUES LESS THAN (15), ->PARTITION p3VALUES LESS THAN MAXVALUE -> ); Query OK, 0 rows affected (1.14 sec) mysql>INSERT INTO employees_sub # reuse 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_subPARTITION (p2sp1); +---- +--------------- + | id | name | +---- +--------------- + | 10 | Lou Waters | | 14 | Fred Goldberg | +---- +--------------- + 2 rows in set (0.00 sec)
您还PARTITION
可以在语句的SELECT
一部分中使用一个选项INSERT ... SELECT
,如下所示:
mysql>CREATE TABLE employees_copy LIKE employees; Query OK, 0 rows affected (0.28 sec) mysql>INSERT INTO employees_copy ->SELECT *FROM employeesPARTITION (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 NULLAUTO_INCREMENT PRIMARY KEY , city VARCHAR(30) NOT NULL )PARTITION BY HASH (id)PARTITIONS 2;INSERT INTO storesVALUES ('', 'Nambucca'), ('', 'Uranga'), ('', 'Bellingen'), ('', 'Grafton');CREATE TABLE departments ( id INT NOT NULLAUTO_INCREMENT PRIMARY KEY ,name VARCHAR(30) NOT NULL )PARTITION BY KEY (id)PARTITIONS 2;INSERT INTO departmentsVALUES ('', 'Sales'), ('', 'Customer Service'), ('', 'Delivery'), ('', 'Accounting');
您可以从联接中的任何表或所有表中明确选择分区(或子分区,或同时选择两个分区)。(PARTITION
用于从给定表中选择分区的选项紧随表名之后,紧跟所有其他选项(包括任何表别名)。)例如,以下查询获取所有雇员的姓名,员工ID,部门和城市谁在销售或运输部(分工作p1
了的departments
任一南布卡和贝林根(分区的城市在商店表)p0
中的stores
表):
mysql>SELECT -> e.idAS 'Employee ID', CONCAT(e.fname, ' ', e.lname)AS Name , -> s.cityAS City, d.name AS department ->FROM employeesAS e ->JOIN storesPARTITION (p1)AS sON e.store_id=s.id ->JOIN departmentsPARTITION (p0)AS dON 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中的联接的一般信息,请参见“ JOIN子句”。
当该PARTITION
选项与DELETE
语句一起使用时,仅检查与该选项一起列出的那些分区(以及子分区,如果有的话)以查找要删除的行。其他任何分区都将被忽略,如下所示:
mysql>SELECT *FROM employeesWHERE 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 employeesPARTITION (p0, p1) ->WHERE fname LIKE 'j%'; Query OK, 2 rows affected (0.09 sec) mysql>SELECT *FROM employeesWHERE 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 employeesPARTITION (p0) ->SET store_id = 2WHERE fname = 'Jill'; Query OK, 0 rows affected (0.00 sec) Rows matched: 0 Changed: 0 Warnings: 0 mysql>SELECT *FROM employeesWHERE fname = 'Jill'; +---- +------- +------- +---------- +--------------- + | id | fname | lname | store_id | department_id | +---- +------- +------- +---------- +--------------- + | 11 | Jill | Stone | 1 | 4 | +---- +------- +------- +---------- +--------------- + 1 row in set (0.00 sec) mysql>UPDATE employeesPARTITION (p2) ->SET store_id = 2WHERE fname = 'Jill'; Query OK, 1 row affected (0.09 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql>SELECT *FROM employeesWHERE 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 employeesPARTITION (p2)VALUES (20, 'Jan', 'Jones', 1, 3); ERROR 1729 (HY000): Found a row not matching the given partition set mysql>INSERT INTO employeesPARTITION (p3)VALUES (20, 'Jan', 'Jones', 1, 3); Query OK, 1 row affected (0.07 sec) mysql>REPLACE INTO employeesPARTITION (p0)VALUES (20, 'Jan', 'Jones', 3, 2); ERROR 1729 (HY000): Found a row not matching the given partition set mysql>REPLACE INTO employeesPARTITION (p3)VALUES (20, 'Jan', 'Jones', 3, 2); Query OK, 2 rows affected (0.09 sec)
对于使用InnoDB
存储引擎将多行写入分区表的语句:如果VALUES
无法将以下列表中的任何行写入partition_names
列表中指定的分区之一,则整个语句将失败并且不写入任何行。INSERT
在以下示例中的语句中显示了此信息,重新使用了employees
先前创建的表:
mysql>ALTER TABLE employees ->REORGANIZE PARTITION p3INTO ( ->PARTITION p3VALUES LESS THAN (20), ->PARTITION p4VALUES LESS THAN (25), ->PARTITION p5VALUES 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=utf8mb4 /*!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 employeesPARTITION (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 employeesPARTITION (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
写入多行的语句均适用。
对于使用提供自动分区的存储引擎的表,将禁用分区选择NDB
。