范围分区
按范围分区的表的分区方式是,每个分区都包含行,分区表达式的值在给定范围内。范围应该是连续的,但不能重叠,并且是使用VALUES LESS THAN
运算符定义的。对于接下来的几个示例,假设您正在创建一个表,如下所示,以保存20个视频商店链(编号1至20)的人员记录:
CREATE TABLE employees ( id INT NOT NULL, fname VARCHAR(30), lname VARCHAR(30), hired DATE NOT NULLDEFAULT '1970-01-01', separated DATE NOT NULLDEFAULT '9999-12-31', job_code INT NOT NULL, store_id INT NOT NULL );
注意
employees
此处使用的表没有主键或唯一键。尽管出于本讨论的目的而显示了示例,但您应记住,表在实践中极有可能具有主键,唯一键或两者兼有,并且分区列的允许选择取决于用于这些列的列。键(如果有)。有关这些问题的讨论,请参见“分区键,主键和唯一键”。
可以根据需要以多种方式对该表进行分区。一种方法是使用store_id
列。例如,您可能决定通过添加PARTITION BY RANGE
如下所示的子句来对表进行4种分区:
CREATE TABLE employees ( id INT NOT NULL, fname VARCHAR(30), lname VARCHAR(30), hired DATE NOT NULLDEFAULT '1970-01-01', separated DATE NOT NULLDEFAULT '9999-12-31', job_code INT NOT NULL, store_id INT NOT NULL )PARTITION BY RANGE (store_id) (PARTITION p0VALUES LESS THAN (6),PARTITION p1VALUES LESS THAN (11),PARTITION p2VALUES LESS THAN (16),PARTITION p3VALUES LESS THAN (21) );
在此分区方案中,与在商店1到5上工作的雇员相对应的所有行都存储在分区中p0
,而在商店6到10中所用的那些行则存储在分区中p1
,依此类推。每个分区的定义顺序是从最低到最高。这是PARTITION BY RANGE
语法要求。if ... elseif ...
在这方面,您可以认为它类似于C或Java中的一系列语句。
这是很容易确定包含数据的新行(72,'Mitchell','Wilson','1998-06-25', NULL, 13)
插入分区p2
,但是当你的链增加了21会发生什么ST店?在这种方案下,没有规则覆盖store_id
大于20 的行,因此会导致错误,因为服务器不知道将其放置在何处。您可以通过在语句中使用“ catchall ”VALUES LESS THAN
子句来防止这种情况发生,该子句CREATE TABLE
提供的所有值都大于显式命名的最大值:
CREATE TABLE employees ( id INT NOT NULL, fname VARCHAR(30), lname VARCHAR(30), hired DATE NOT NULLDEFAULT '1970-01-01', separated DATE NOT NULLDEFAULT '9999-12-31', job_code INT NOT NULL, store_id INT NOT NULL )PARTITION BY RANGE (store_id) (PARTITION p0VALUES LESS THAN (6),PARTITION p1VALUES LESS THAN (11),PARTITION p2VALUES LESS THAN (16),PARTITION p3VALUES LESS THAN MAXVALUE );
(与本章中的其他示例一样,我们假定默认存储引擎为InnoDB
。)
注意当找不到匹配值时,避免错误的另一种方法是将
IGNORE
关键字用作INSERT
语句的一部分。有关示例,请参见“列表分区”。另请参见第13.2.6,“INSERT语句”,有关的一般信息IGNORE
。
MAXVALUE
表示始终大于最大可能整数值的整数值(在数学语言中,它用作最小上限)。现在,任何store_id
列值大于或等于16(定义的最大值)的行都存储在partition中p3
。在将来的某个时候(商店数量增加到25、30或更多),您可以使用ALTER TABLE
语句为21-25、26-30等商店添加新分区(请参见“分区管理”,以获取详细信息。
以几乎相同的方式,您可以根据员工的职务代码(即,根据job_code
列值的范围)对表进行分区。例如,假设常规(店内)工人使用两位数字的工作代码,办公室和支持人员使用三位数字的代码,管理职位使用四位数字的代码,则可以创建分区表使用以下语句:
CREATE TABLE employees ( id INT NOT NULL, fname VARCHAR(30), lname VARCHAR(30), hired DATE NOT NULLDEFAULT '1970-01-01', separated DATE NOT NULLDEFAULT '9999-12-31', job_code INT NOT NULL, store_id INT NOT NULL )PARTITION BY RANGE (job_code) (PARTITION p0VALUES LESS THAN (100),PARTITION p1VALUES LESS THAN (1000),PARTITION p2VALUES LESS THAN (10000) );
在这种情况下,与店内工作人员相关的所有行都将存储在分区中p0
,与行内办公室和支持人员有关的行将与分区p1
中的经理有关p2
。
也可以在VALUES LESS THAN
子句中使用表达式。但是,MySQL必须能够在LESS THAN
(<
)比较中评估表达式的返回值。
您可以使用基于两DATE
列之一的表达式,而不是根据商店编号来拆分表数据。例如,让我们假设您希望根据每个员工离开公司的年份进行划分;即的值YEAR(separated)
。CREATE TABLE
此处显示了实现这种分区方案的语句示例:
CREATE TABLE employees ( id INT NOT NULL, fname VARCHAR(30), lname VARCHAR(30), hired DATE NOT NULLDEFAULT '1970-01-01', separated DATE NOT NULLDEFAULT '9999-12-31', job_code INT, store_id INT )PARTITION BY RANGE ( YEAR(separated) ) (PARTITION p0VALUES LESS THAN (1991),PARTITION p1VALUES LESS THAN (1996),PARTITION p2VALUES LESS THAN (2001),PARTITION p3VALUES LESS THAN MAXVALUE );
在此方案中,对于1991年之前离职的所有员工,行都存储在partition中p0
;对于那些谁留在1991年至1995年,在p1
;对于那些谁留在1996年年内至2000年,在p2
;对于2000年以后离开的任何工人,请在p3
。
也可以使用函数根据列RANGE
的值通过来对表进行分区,如本示例所示:TIMESTAMP
UNIX_TIMESTAMP()
CREATE TABLE quarterly_report_status ( report_id INT NOT NULL, report_status VARCHAR(20) NOT NULL, report_updated TIMESTAMP NOT NULLDEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP )PARTITION BY RANGE ( UNIX_TIMESTAMP(report_updated) ) (PARTITION p0VALUES LESS THAN ( UNIX_TIMESTAMP('2008-01-01 00:00:00') ),PARTITION p1VALUES LESS THAN ( UNIX_TIMESTAMP('2008-04-01 00:00:00') ),PARTITION p2VALUES LESS THAN ( UNIX_TIMESTAMP('2008-07-01 00:00:00') ),PARTITION p3VALUES LESS THAN ( UNIX_TIMESTAMP('2008-10-01 00:00:00') ),PARTITION p4VALUES LESS THAN ( UNIX_TIMESTAMP('2009-01-01 00:00:00') ),PARTITION p5VALUES LESS THAN ( UNIX_TIMESTAMP('2009-04-01 00:00:00') ),PARTITION p6VALUES LESS THAN ( UNIX_TIMESTAMP('2009-07-01 00:00:00') ),PARTITION p7VALUES LESS THAN ( UNIX_TIMESTAMP('2009-10-01 00:00:00') ),PARTITION p8VALUES LESS THAN ( UNIX_TIMESTAMP('2010-01-01 00:00:00') ),PARTITION p9VALUES LESS THAN (MAXVALUE ) );
TIMESTAMP
不允许使用任何其他涉及值的表达式。(请参见Bug#42849。)
当满足以下一个或多个条件时,范围分区特别有用:
- 您想要或需要删除“旧”数据。如果您使用的是
employees
表中先前显示的分区方案,则只需ALTER TABLE employees DROP PARTITION p0;
删除与在1991年之前停止在公司工作的员工有关的所有行。(请参见“ ALTER TABLE语句”和)(“分区管理”,以了解更多信息。)对于具有很多行的表,这比运行DELETE
诸如之类的查询要有效得多DELETE FROM employees WHERE YEAR(separated)<= 1990;
。 - 您要使用包含日期或时间值或包含其他系列值的列。
- 您经常运行直接取决于用于分区表的列的查询。例如,当执行诸如的查询时
EXPLAIN SELECT COUNT(*)FROM employees WHERE separated BETWEEN '2000-01-01' AND '2000-12-31' GROUP BY store_id;
,MySQL可以快速确定仅p2
需要扫描分区,因为其余分区不能包含满足该WHERE
子句的任何记录。有关如何完成此操作的更多信息,请参见“分区修剪”。
这种分区的一种变体是RANGE COLUMNS
分区。通过RANGE COLUMNS
进行分区,可以使用多个列来定义分区范围,该范围既适用于分区中行的放置,又可以用于在执行分区修剪时确定特定分区的包含或排除。有关更多信息,请参见“ RANGE COLUMNS分区”。
基于时间间隔的分区方案。如果希望在MySQL 8.0中基于范围或时间间隔实现分区方案,则有两个选择:
由分区表
RANGE
,以及用于分隔表达,使用上的一个功能的操作DATE
,TIME
或DATETIME
柱并返回一个整数值,如下所示:CREATE TABLE members ( firstname VARCHAR(25) NOT NULL, lastname VARCHAR(25) NOT NULL, username VARCHAR(16) NOT NULL, email VARCHAR(35), joined DATE NOT NULL )PARTITION BY RANGE ( YEAR(joined) ) (PARTITION p0VALUES LESS THAN (1960),PARTITION p1VALUES LESS THAN (1970),PARTITION p2VALUES LESS THAN (1980),PARTITION p3VALUES LESS THAN (1990),PARTITION p4VALUES LESS THAN MAXVALUE );在MySQL 8.0中,还可以使用函数
RANGE
基于TIMESTAMP
列的值对表进行分区UNIX_TIMESTAMP()
,如以下示例所示:CREATE TABLE quarterly_report_status ( report_id INT NOT NULL, report_status VARCHAR(20) NOT NULL, report_updated TIMESTAMP NOT NULLDEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP )PARTITION BY RANGE ( UNIX_TIMESTAMP(report_updated) ) (PARTITION p0VALUES LESS THAN ( UNIX_TIMESTAMP('2008-01-01 00:00:00') ),PARTITION p1VALUES LESS THAN ( UNIX_TIMESTAMP('2008-04-01 00:00:00') ),PARTITION p2VALUES LESS THAN ( UNIX_TIMESTAMP('2008-07-01 00:00:00') ),PARTITION p3VALUES LESS THAN ( UNIX_TIMESTAMP('2008-10-01 00:00:00') ),PARTITION p4VALUES LESS THAN ( UNIX_TIMESTAMP('2009-01-01 00:00:00') ),PARTITION p5VALUES LESS THAN ( UNIX_TIMESTAMP('2009-04-01 00:00:00') ),PARTITION p6VALUES LESS THAN ( UNIX_TIMESTAMP('2009-07-01 00:00:00') ),PARTITION p7VALUES LESS THAN ( UNIX_TIMESTAMP('2009-10-01 00:00:00') ),PARTITION p8VALUES LESS THAN ( UNIX_TIMESTAMP('2010-01-01 00:00:00') ),PARTITION p9VALUES LESS THAN (MAXVALUE ) );在MySQL 8.0中,
TIMESTAMP
不允许使用任何其他涉及值的表达式。(请参见Bug#42849。)注意
在MySQL 8.0中,也可能
UNIX_TIMESTAMP(timestamp_column)
用作由进行分区的表的分区表达式LIST
。但是,这样做通常是不实际的。RANGE COLUMNS
使用DATE
或DATETIME
列作为分区列,按来对表进行分区。例如,members
可以使用joined
列直接定义表,如下所示:CREATE TABLE members ( firstname VARCHAR(25) NOT NULL, lastname VARCHAR(25) NOT NULL, username VARCHAR(16) NOT NULL, email VARCHAR(35), joined DATE NOT NULL )PARTITION BY RANGE COLUMNS (joined) (PARTITION p0VALUES LESS THAN ('1960-01-01'),PARTITION p1VALUES LESS THAN ('1970-01-01'),PARTITION p2VALUES LESS THAN ('1980-01-01'),PARTITION p3VALUES LESS THAN ('1990-01-01'),PARTITION p4VALUES LESS THAN MAXVALUE );
注意而不使用
DATE
或DATETIME
不支持使用日期或时间类型的分区列RANGE COLUMNS
。