MySQL 5.7支持显式选择分区和子分区,当执行语句时,应该检查是否匹配给定的行在哪里
条件。分区选择类似于分区修剪,因为只检查特定的分区是否匹配,但在两个关键方面有所不同:
要检查的分区是由语句的颁发者指定的,这与自动的分区修剪不同。
分区修剪只应用于查询,而对查询和许多DML语句都支持显式选择分区。
下面列出了支持显式分区选择的SQL语句:
本节的其余部分将讨论显式分区选择,因为它通常适用于刚刚列出的语句,并提供了一些示例。
显式分区选择使用分区
选择。对于所有受支持的语句,该选项使用如下所示的语法:
分区(partition_names)partition_names:partition_name,……
此选项始终紧跟分区所属的表的名称。partition_names
要使用的分区或子分区的逗号分隔列表。此列表中的每个名称必须是指定表的现有分区或子分区的名称;如果没有找到任何分区或子分区,则语句失败,并报错(分区的partition_name
'并不存在).中的分区和子分区partition_names
可以按任何顺序列出,也可以重叠。
当分区
选项时,仅检查列出的分区和子分区是否匹配行。此选项可用于选择
语句来确定哪些行属于给定分区。考虑一个名为员工
,使用如下所示的语句创建和填充:
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小于(5),PARTITION p1 VALUES小于(10),PARTITION p2 VALUES小于(15),PARTITION p3 VALUES小于MAXVALUE);插入员工价值观(”,“鲍勃”,“泰勒”,3、2),(“,“弗兰克”,威廉姆斯,1,2),(“、“艾伦”、“约翰逊”,3,4),(“,“吉姆”,“史密斯”,2、4),(“,“玛丽”,琼斯,1,1),(“,“琳达”,“黑”,2、3),(“、“Ed”、“琼斯”,2、1),(“、“6”、“威尔逊”,3,1),(“、“安迪”、“史密斯”,1,3),(“,“卢”,“水”,2、4),(“、“吉尔”、“石头”,1、4),(“,“罗杰”,“白色”,3、2),(“、“霍华德”、“安德鲁”,1,2),(“、“弗雷德”、“戈德堡”3,3),(“,“芭芭拉”,“布朗”,2、3),(“,“爱丽丝”,罗杰斯的2 2),(“,“马克”,“摩根”,3、3),(“、“凯伦”、“科尔”3,2);
您可以看到哪些行存储在分区中p1
是这样的:
mysql> SELECT * FROM employees (p1);+----+-------+--------+----------+---------------+ | id |帧| lname | store_id | department_id | +----+-------+--------+----------+---------------+ | 5玛丽琼斯| | | 1 | 1 | | 6 |黑色琳达| | 2 | 3 | | 7 Ed琼斯| | | 2 | 1 | | 6月8日| |威尔逊| 3 | 1 | | | 9安迪史密斯| | 1 | 3 | +----+-------+--------+----------+---------------+ 5行集(0.00秒)
结果与查询得到的结果相同从id在5到9之间的雇员中选择*
.
要从多个分区获取行,请以逗号分隔的列表形式提供它们的名称。例如,SELECT * FROM employees分区(p1, p2)
返回分区中的所有行p1
而且p2
同时从其余分区中排除行。
对分区表的任何有效查询都可以使用分区
选项,将结果限制为一个或多个所需分区。你可以使用在哪里
条件下,命令
而且限制
期权,等等。还可以使用with的聚合函数有
而且集团
选项。上运行时,以下每个查询都会产生有效的结果员工
表如下所示:
mysql> SELECT * FROM employees PARTITION (p0, p2) -> WHERE lname LIKE 'S%';+----+-------+-------+----------+---------------+ | id |帧| lname | store_id | department_id | +----+-------+-------+----------+---------------+ | 4史密斯吉姆| | | 2 | 4 | | 11吉尔石头| | 1 | 4 | | +----+-------+-------+----------+---------------+ 2行(0.00秒)mysql >选择id, CONCAT(帧、“lname)从员工分区名称- > (p0) ORDER BY lname;+----+----------------+ | id |名称 | +----+----------------+ | 3 |埃伦·约翰逊| | 4 |吉姆史密斯| | 1 |鲍勃·泰勒| | 2 |弗兰克·威廉姆斯 | +----+----------------+ 4行集(0.06秒)mysql >选择store_id,计数(department_id) c - >从员工分区(p1, p2, p3) - > GROUP BY store_id c > 4;+---+----------+ | c | store_id | +---+----------+ | 5 | 2 | | 5 | 3 | +---+----------+ 2行集(0.00秒)
使用分区选择的语句可以用于使用MySQL 5.7中支持的任何分区类型的表。创建表时使用(线性)散列
或(线性)的关键
如果不指定分区名称,MySQL将自动命名分区p0
,p1
,p2
、……p
,在那里n - 1
N
是分区的数量。对于没有显式命名的子分区,MySQL会自动分配给每个分区中的子分区p
的名字X
p
,X
sp0p
,X
sp1p
、……X
sp2p
,在那里X
spm - 1
米
是子分区的数量。当对该表执行a选择
(或其他允许显式分区选择的SQL语句),您可以在分区
选项,如下所示:
mysql >创建表employees_sub (- > id INT NOT NULL AUTO_INCREMENT >帧VARCHAR(25)不是NULL,——> lname VARCHAR(25)不是NULL,——> store_id INT NOT NULL,——> department_id INT NOT NULL, pk - >主键(id、lname ) -> ) -> 分区范围(id) - > SUBPARTITION通过键(lname) - > SUBPARTITIONS 2(- >分区p0值小于(5),- - - - - - >分区p1值小于(10),- - - - - - >分区p2值小于(15),- - - - - - >分区p3值小于MAXVALUE - >);INSERT INTO employees_sub#重新使用employees_sub# employee表中的数据——> SELECT * FROM employees查询OK, 18 rows affected (0.09 sec) Records: 18 duplicate: 0 warning: 0 mysql> SELECT id, CONCAT(fname, ' ', lname) AS name -> FROM employees_sub PARTITION (p2sp1);+----+---------------+ | id |名称 | +----+---------------+ | 10 |卢水| | | 14弗雷德戈德堡 | +----+---------------+ 2行集(0.00秒)
你也可以用a分区
选项中的选择
部分插入……选择
语句,如下所示:
mysql> CREATE TABLE employees_copy LIKE employeesINSERT INTO employees_copy -> SELECT * FROM employees PARTITION (p2);查询OK, 5 rows affected (0.04 sec) Records: 5 duplicate: 0 warning: 0 mysql> SELECT * FROM employees_copy;+----+--------+----------+----------+---------------+ | id |帧| lname | store_id | department_id | +----+--------+----------+----------+---------------+ | 10 |卢水| | 2 | 4 | | 11 |吉尔石头| | 1 | 4 | | | 12罗杰白色| | 3 | 2 | | 13霍华德| |安德鲁斯| 1 | 2 | | | 14弗雷德·戈德堡| | 3 | 3 | +----+--------+----------+----------+---------------+ 5行集(0.00秒)
分区选择也可以用于连接。假设我们使用下面所示的语句创建并填充两个表:
CREATE TABLE (id INT NOT NULL AUTO_INCREMENT PRIMARY KEY, city VARCHAR(30) NOT NULL)(“,”Nambucca插入存储值 '), ('', ' Uranga '), ('', ' Bellingen '), ('', ' 格拉夫顿”);CREATE TABLE department (id INT NOT NULL AUTO_INCREMENT PRIMARY KEY, name VARCHAR(30) NOT NULL)INSERT INTO departments VALUES (", 'Sales'), (", 'Customer Service'), (", 'Delivery'), (", 'Accounting');
您可以显式地从连接中的任何或所有表中选择分区(或子分区,或两者都选择)。(分区
选项,用于从给定的表中选择分区,紧跟着表的名称,在所有其他选项之前,包括任何表别名。)例如,下面的查询获取在销售或交付部门(分区)工作的所有员工的名称、员工ID、部门和城市p1
的部门
在Nambucca和Bellingen的任何一个城市的商店(分区)p0
的商店
表):
mysql> SELECT -> e.id AS 'Employee ID', CONCAT(fname, ' ', e.lname) AS Name, -> s.city AS City, d.l ame AS department -> FROM employees AS e -> JOIN stores PARTITION (p1) AS s ON e.s store_id=s。id -> JOIN departments PARTITION (p0) AS d ON e.d department_id= d.d -> ORDER BY e.lname;+-------------+---------------+-----------+------------+ | 雇员ID | | |城市部门名称 | +-------------+---------------+-----------+------------+ | 14 |弗雷德·戈德堡| Bellingen交付| | | 5 |玛丽琼斯| Nambucca |销售| | 17 | | Morgan Bellingen交付| | | 9 Andy Smith | | Nambucca交付| | | 6月8日| |威尔逊Bellingen |销售 | +-------------+---------------+-----------+------------+ 5行集(0.00秒)
有关MySQL中联接的一般信息,请参见第13.2.9.2节“JOIN子句”.
当分区
选项与删除
语句中,只检查与选项一起列出的那些分区(以及子分区,如果有的话)要删除的行。任何其他分区将被忽略,如下所示:
mysql> SELECT * FROM employees WHERE fname LIKE 'j%'+----+-------+--------+----------+---------------+ | id |帧| lname | store_id | department_id | +----+-------+--------+----------+---------------+ | 4史密斯吉姆| | | 2 | 4 | | | | 6月8威尔逊| 3 | 1 | | 11吉尔石头| | 1 | 4 | | +----+-------+--------+----------+---------------+ 3行集(0.00秒)mysql >删除从员工分区(p0, p1) - >在帧像“j %”;mysql> SELECT * FROM employees WHERE fname LIKE 'j%';+----+-------+-------+----------+---------------+ | id |帧| lname | store_id | department_id | +----+-------+-------+----------+---------------+ | 11吉尔石头| | 1 | 4 | | +----+-------+-------+----------+---------------+ 1行集(0.00秒)
分区中只有两行p0
而且p1
匹配的在哪里
条件被删除。从结果中可以看出选择
第二次运行时,表中仍有一行与在哪里
条件,但位于不同的分区中(p2
).
更新
使用显式分区选择的语句的行为与此相同;属性所引用的分区中的行分区
选项在确定要更新的行时被考虑,可以通过执行以下语句看到:
mysql> UPDATE employees PARTITION (p0) -> SET store_id = 2 WHERE fname = 'Jill';查询OK, 0 rows affected (0.00 sec) rows matched: 0 Changed: 0 warning: 0 mysql> SELECT * FROM employees WHERE fname = 'Jill';+----+-------+-------+----------+---------------+ | id |帧| lname | store_id | department_id | +----+-------+-------+----------+---------------+ | 11吉尔石头| | 1 | 4 | | +----+-------+-------+----------+---------------+ 1行组(0.00秒)mysql >更新员工分区(p2) - >设置store_id = 2帧=“吉尔”;查询OK, 1行影响(0.09秒)Rows matched: 1 Changed: 1 warning: 0 mysql> SELECT * FROM employees WHERE fname = 'Jill';+----+-------+-------+----------+---------------+ | id |帧| lname | store_id | department_id | +----+-------+-------+----------+---------------+ | 11吉尔石头| | 2 | 4 | | +----+-------+-------+----------+---------------+ 1行集(0.00秒)
同样地,当分区
与删除
时,只检查分区中的行或分区列表中命名的分区是否被删除。
对于插入行的语句,其行为的不同之处在于未能找到合适的分区将导致语句失败。两者都是如此插入
而且取代
语句,如下所示:
mysql> INSERT INTO employees PARTITION (p2) VALUES (20, 'Jan', 'Jones', 1,3);错误1729 (HY000): mysql> INSERT INTO employees partition (p3) VALUES (20, 'Jan', 'Jones', 1,3);mysql> REPLACE INTO employees PARTITION (p0) VALUES (20, 'Jan', 'Jones', 3,2);错误1729 (HY000):发现一行不匹配给定的分区集mysql> REPLACE INTO employees partition (p3) VALUES (20, 'Jan', 'Jones', 3,2);查询OK,影响2行(0.09秒)
属性将多行写入已分区表的语句InnoDB
存储引擎:如果下面列表中的任意一行值
属性中指定的一个分区partition_names
列表,整个语句失败,没有行被写入。这是为插入
语句中,重复使用员工
之前创建的表:
mysql> ALTER TABLE employees -> REORGANIZE PARTITION p3 INTO (-> PARTITION p3 VALUES小于(20),-> PARTITION p4 VALUES小于(25),-> PARTITION p5 VALUES小于MAXVALUE ->);查询OK, 6 rows affected (2.09 sec) Records: 6 duplicate: 0 warning: 0 mysql> SHOW CREATE TABLE employees\G ***************************行***************************表:employees创建表: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分区范围(id)(分区p0值小于(5)引擎= InnoDB分区p1值小于(10)引擎= InnoDB分区p2值小于(15)引擎= InnoDB分区p3值小于(20)引擎= InnoDB分区p4值小于(25)引擎= InnoDB分区p5值小于MAXVALUE引擎= InnoDB) * / 1行组(0.00秒)mysql >插入员工分区(p3, p4)值- >(24岁的蒂姆,“格林”,3,1),(26岁,“琳达”,“工厂”,2,1);错误1729 (HY000):发现一行不匹配给定的分区集mysql> INSERT INTO employees partition (p3, p4)。p5)值- >(24岁的蒂姆,“格林”,3,1),(26岁,“琳达”,“工厂”,2,1);查询OK, 2行受影响(0.06秒)记录:2重复:0警告:0
在MySQL 5.7.1及以后版本中,对于使用提供自动分区的存储引擎的表禁用分区选择,例如NDB
.(错误# 14827952)