22.2 分区类型

2020-05-11 11:14:05

参考文档：https://dev.mysql.com/doc/refman/5.7/en/partitioning-*.html

本节讨论MySQL 5.7中可用的分区类型。这些包括此处列出的类型：

RANG 分区。这种类型的分区基于落在给定范围内的列值将行分区。
LIST 分区。与RANGE分区类似，不同之处在于字段匹配一个离散值选择分区。
HASH 分区。使用这种类型的分区，将根据用户定义的表达式返回的值选择分区，该表达式对要插入表中的行中的列值进行操作。该函数可以包含在MySQL中有效的任何表达式，该表达式产生非负整数值。此类型的扩展名为LINEAR HASH，也可用。
key 分区。这种类型的分区类似于通过HASH进行分区，除了仅提供一个或多个要评估的列，并且MySQL服务器提供其自己的散列函数。这些列可以包含非整数值，因为MySQL提供的散列函数保证整数结果，而不管列数据类型如何。此类型的扩展名为LINEAR KEY，也是可用的。

数据库分区的一个非常常见的用途是按日期隔离数据。一些数据库系统支持显式日期分区，MySQL在5.7中没有实现。但是，在MySQL中创建基于DATE，TIME或DATETIME列的分区方案或基于使用此类列的表达式并不困难。

通过KEY或LINEAR KEY进行分区时，可以使用DATE，TIME或DATETIME列作为分区列，而不对列值进行任何修改。例如，此表创建语句在MySQL中完全有效：

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 KEY(joined) PARTITIONS 6;

在MySQL 5.7中，还可以使用DATE或DATETIME列作为分区列，并使用RANGE COLUMNS和LIST COLUMNS分区。

但是，MySQL的其他分区类型需要一个产生整数值或NULL的分区表达式。如果您希望使用RANGE，LIST，HASH或LINEAR HASH的基于日期的分区，您可以简单地使用在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 p0 VALUES LESS THAN (1960),
PARTITION p1 VALUES LESS THAN (1970),
PARTITION p2 VALUES LESS THAN (1980),
PARTITION p3 VALUES LESS THAN (1990),
PARTITION p4 VALUES LESS THAN MAXVALUE
);

MySQL分区经过优化，可与TO_DAYS（），YEAR（）和TO_SECONDS（）函数一起使用。但是，您可以使用返回整数或NULL的其他日期和时间函数，例如WEEKDAY（），DAYOFYEAR（）或MONTH（）。有关此类函数的更多信息，请参见第12.7节“日期和时间函数”。

重要的是要记住 - 无论您使用何种分区 - 分区始终自动编号，并在创建时按顺序编号，从0开始。当新行插入分区表时，这些分区编号是用于识别正确的分区。例如，如果您的表使用4个分区，则这些分区的编号为0,1,2和3.对于RANGE和LIST分区类型，必须确保为每个分区编号定义了一个分区。对于HASH分区，用户提供的表达式必须求值为大于0的整数值。对于KEY分区，此问题由MySQL服务器内部使用的散列函数自动处理。

分区的名称通常遵循管理其他MySQL标识符的规则，例如表和数据库的标识符。但是，您应该注意分区名称不区分大小写。例如，以下CREATE TABLE语句失败，如下所示：

mysql> CREATE TABLE t2 (val INT)
-> PARTITION BY LIST(val)(
-> PARTITION mypart VALUES IN (1,3,5),
-> PARTITION MyPart VALUES IN (2,4,6) -> ); ERROR 1488 (HY000): Duplicate partition name mypart

发生故障是因为MySQL无法区分分区名称mypart和MyPart之间的区别。

当您指定表的分区数时，必须将其表示为不带前导零的正，非零整数文字，并且可能不是0.8E + 01或6-2等表达式，即使它的计算结果为整数值。不允许使用小数部分。

22.2.1 RANGE 分区

按范围分区的表以这样的方式分区，即每个分区包含分区表达式值位于给定范围内的行。范围应该是连续的但不重叠，并且使用VALUES LESS THAN运算符定义。对于接下来的几个示例，假设您要创建一个表，如下所示，以保存20个视频商店链的人事记录，编号为1到20：
CREATE TABLE employees (
id INT NOT NULL,
fname VARCHAR(30),
lname VARCHAR(30),
hired DATE NOT NULL DEFAULT '1970-01-01',
separated DATE NOT NULL DEFAULT '9999-12-31',
job_code INT NOT NULL,
store_id INT NOT NULL
);

根据您的需要，可以通过多种方式按范围对此表进行分区。一种方法是使用store_id列。例如，您可能决定通过添加PARTITION BY RANGE子句来对表进行4个分区，如下所示：

CREATE TABLE employees ( id INT NOT NULL, fname VARCHAR(30), lname VARCHAR(30), hired DATE NOT NULL DEFAULT '1970-01-01', separated DATE NOT NULL DEFAULT '9999-12-31', job_code INT NOT NULL, store_id INT NOT NULL )
PARTITION BY RANGE (store_id) (
PARTITION p0 VALUES LESS THAN (6),
PARTITION p1 VALUES LESS THAN (11),
PARTITION p2 VALUES LESS THAN (16),
PARTITION p3 VALUES LESS THAN (21) );

在该分区方案中，对应于在1到5工作的雇员的所有行存储在分区p0中，对于在6到10中使用的那些行存储在分区p1中，依此类推。请注意，每个分区都按顺序定义，从低到高。这是PARTITION BY RANGE语法的要求; 在这方面你可以认为它类似于C或Java中的一系列if ... elseif ...语句。

很容易确定包含数据的新行（72，'Mitchell'，'Wilson'，'1998-06-25'，NULL，13）被插入到分区p2中，但是当你增加21时会发生什么？在此方案下，没有规则涵盖store_id大于20的行，因此导致错误，因为服务器不知道将其放置在何处。您可以通过在CREATE TABLE语句中使用“catchall”VALUES LESS THAN子句来防止这种情况发生，该子句提供的所有值都大于显式命名的高值(上边界)：

CREATE TABLE employees ( id INT NOT NULL, fname VARCHAR(30), lname VARCHAR(30), hired DATE NOT NULL DEFAULT '1970-01-01', separated DATE NOT NULL DEFAULT '9999-12-31', job_code INT NOT NULL, store_id INT NOT NULL ) PARTITION BY RANGE (store_id) ( PARTITION p0 VALUES LESS THAN (6), PARTITION p1 VALUES LESS THAN (11), PARTITION p2 VALUES LESS THAN (16), PARTITION p3 VALUES LESS THAN MAXVALUE );

注意：
找不到匹配值时避免错误的另一种方法是使用IGNORE关键字作为INSERT语句的一部分。有关示例，请参见第22.2.2节“LIST分区”。有关IGNORE的信息，另请参见第13.2.5节“INSERT语法”。

MAXVALUE表示一个整数值，该值始终大于大可能的整数值（在数学语言中，它用作小上限）。现在，store_id列值大于或等于16（定义的高值）的任何行都存储在分区p3中。在将来的某个时刻 - 当数量增加到25,30或更多时 - 您可以使用ALTER TABLE语句为21-25,26-30等添加新分区（参见第22.3节），“分区管理”，有关如何执行此操作的详细信息）。

以同样的方式，您可以根据员工职务代码对表进行分区 - 即基于job_code列值的范围。例如 - 假设两位数的工作代码用于常规（店内）工作人员，三位数代码用于办公室和支持人员，四位数代码用于管理职位 - 您可以创建分区表使用以下语句：

CREATE TABLE employees ( id INT NOT NULL, fname VARCHAR(30), lname VARCHAR(30), hired DATE NOT NULL DEFAULT '1970-01-01', separated DATE NOT NULL DEFAULT '9999-12-31', job_code INT NOT NULL, store_id INT NOT NULL )
PARTITION BY RANGE (job_code) (
PARTITION p0 VALUES LESS THAN (100),
PARTITION p1 VALUES LESS THAN (1000),
PARTITION p2 VALUES LESS THAN (10000) );

在这种情况下，与店内工人相关的所有行将存储在分区p0中，p1中存储办公室和支持人员相关的行，以及分区p2中存储管理者相关的那些行。

也可以在VALUES LESS THAN子句中使用表达式。但是，MySQL必须能够将表达式的返回值作为LESS THAN（<）比较的一部分进行评估。

您可以使用基于两个DATE列之一的表达式，而不是根据商店编号拆分表数据。例如，假设您希望根据每个员工离开公司的年份进行分区; 也就是YEAR的值（分开）。此处显示了实现此类分区方案的CREATE TABLE语句的示例：

CREATE TABLE employees ( id INT NOT NULL, fname VARCHAR(30), lname VARCHAR(30), hired DATE NOT NULL DEFAULT '1970-01-01', separated DATE NOT NULL DEFAULT '9999-12-31', job_code INT, store_id INT ) PARTITION BY RANGE ( YEAR(separated) ) (
PARTITION p0 VALUES LESS THAN (1991),
PARTITION p1 VALUES LESS THAN (1996),
PARTITION p2 VALUES LESS THAN (2001),
PARTITION p3 VALUES LESS THAN MAXVALUE );

在该方案中，对于在1991年之前离开的所有雇员，行存储在分区p0中; 对于那些在1991年到1995年离开的人存储在p1; 对于那些在1996年至2000年期间离开的人存储在在p2; 对于在2000年之后离开的存储在p3。

也可以使用UNIX_TIMESTAMP（）函数根据TIMESTAMP列的值对RANGE分区表，如下例所示：

CREATE TABLE quarterly_report_status ( report_id INT NOT NULL, report_status VARCHAR(20) NOT NULL, report_updated TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP ) PARTITION BY RANGE ( UNIX_TIMESTAMP(report_updated) ) (
PARTITION p0 VALUES LESS THAN ( UNIX_TIMESTAMP('2008-01-01 00:00:00') ),
PARTITION p1 VALUES LESS THAN ( UNIX_TIMESTAMP('2008-04-01 00:00:00') ),
PARTITION p2 VALUES LESS THAN ( UNIX_TIMESTAMP('2008-07-01 00:00:00') ),
PARTITION p3 VALUES LESS THAN ( UNIX_TIMESTAMP('2008-10-01 00:00:00') ),
PARTITION p4 VALUES LESS THAN ( UNIX_TIMESTAMP('2009-01-01 00:00:00') ),
PARTITION p5 VALUES LESS THAN ( UNIX_TIMESTAMP('2009-04-01 00:00:00') ),
PARTITION p6 VALUES LESS THAN ( UNIX_TIMESTAMP('2009-07-01 00:00:00') ),
PARTITION p7 VALUES LESS THAN ( UNIX_TIMESTAMP('2009-10-01 00:00:00') ),
PARTITION p8 VALUES LESS THAN ( UNIX_TIMESTAMP('2010-01-01 00:00:00') ),
PARTITION p9 VALUES LESS THAN (MAXVALUE) );

不允许涉及TIMESTAMP值的任何其他表达式。（参见Bug＃42849。）

当满足以下一个或多个条件时，范围分区特别有用：

您想要或需要删除“旧”数据。如果您正在使用前面显示的employees表的分区方案，则只需使用 ALTER TABLE employees DROP PARTITION p0;就可以删除与1991年之前停止为公司工作的员工相关的所有行。这比运行DELETE FROM查询（如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进行分区，可以使用多列来定义分区范围，这些分区范围既适用于分区中行的放置，也适用于在执行分区修剪时确定包含或排除特定分区。

基于时间间隔的分区方案。如果您希望在MySQL 5.7中基于时间范围或时间间隔实现分区方案，您有两种选择：

1.通过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 p0 VALUES LESS THAN (1960),
PARTITION p1 VALUES LESS THAN (1970),
PARTITION p2 VALUES LESS THAN (1980),
PARTITION p3 VALUES LESS THAN (1990),
PARTITION p4 VALUES LESS THAN MAXVALUE );

在MySQL 5.7中，还可以使用UNIX_TIMESTAMP（）函数根据TIMESTAMP列的值对RANGE分区表，如下例所示：

CREATE TABLE quarterly_report_status ( report_id INT NOT NULL, report_status VARCHAR(20) NOT NULL, report_updated TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP )
PARTITION BY RANGE ( UNIX_TIMESTAMP(report_updated) ) ( PARTITION p0 VALUES LESS THAN ( UNIX_TIMESTAMP('2008-01-01 00:00:00') ),
PARTITION p1 VALUES LESS THAN ( UNIX_TIMESTAMP('2008-04-01 00:00:00') ),
PARTITION p2 VALUES LESS THAN ( UNIX_TIMESTAMP('2008-07-01 00:00:00') ),
PARTITION p3 VALUES LESS THAN ( UNIX_TIMESTAMP('2008-10-01 00:00:00') ),
PARTITION p4 VALUES LESS THAN ( UNIX_TIMESTAMP('2009-01-01 00:00:00') ),
PARTITION p5 VALUES LESS THAN ( UNIX_TIMESTAMP('2009-04-01 00:00:00') ),
PARTITION p6 VALUES LESS THAN ( UNIX_TIMESTAMP('2009-07-01 00:00:00') ),
PARTITION p7 VALUES LESS THAN ( UNIX_TIMESTAMP('2009-10-01 00:00:00') ),
PARTITION p8 VALUES LESS THAN ( UNIX_TIMESTAMP('2010-01-01 00:00:00') ),
PARTITION p9 VALUES LESS THAN (MAXVALUE) );

在MySQL 5.7中，不允许涉及TIMESTAMP值的任何其他表达式。（参见Bug＃42849。）

注意：
在MySQL 5.7中，也可以使用UNIX_TIMESTAMP（timestamp_column）作为由LIST分区的表的分区表达式。但是，这样做通常是不切实际的。

2.使用DATE或DATETIME列作为分区列，通过RANGE COLUMNS对表进行分区。例如，可以直接使用 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 p0 VALUES LESS THAN ('1960-01-01'),
PARTITION p1 VALUES LESS THAN ('1970-01-01'),
PARTITION p2 VALUES LESS THAN ('1980-01-01'),
PARTITION p3 VALUES LESS THAN ('1990-01-01'),
PARTITION p4 VALUES LESS THAN MAXVALUE );

注意：
RANGE COLUMNS不支持使用除DATE或DATETIME以外的日期或时间类型的分区列。

22.2.2 LIST 分区

MySQL中的列表分区在很多方面类似于范围分区。与RANGE分区一样，必须明确定义每个分区。两种类型的分区之间的主要区别在于，在列表分区中，每个分区是根据一组值列表中的一个字段值的关系选择的，而不是在一组连续范围中定义和选择的。这是通过使用PARTITION BY LIST（expr）来完成的，其中expr是一个列值或一个基于列值的表达式并返回一个整数值，然后通过VALUES IN（value_list）定义每个分区，其中value_list是一个以逗号分隔的整数列表。

注意：

在MySQL 5.7中，当通过LIST进行分区时，可以仅匹配整数列表（并且可能是NULL - 参见第22.2.7节“MySQL分区如何处理NULL”）。

但是，当使用LIST COLUMN分区时，可以在值列表中使用其他字段类型，这将在本节后面介绍。

与范围定义的分区的情况不同，列表分区不需要以任何特定顺序声明。有关更详细的语法信息，请参见第13.1.18节“CREATE TABLE语法”。

对于下面的示例，我们假设要分区的表的基本定义由此处显示的CREATE TABLE语句提供：
CREATE TABLE employees ( id INT NOT NULL, fname VARCHAR(30), lname VARCHAR(30), hired DATE NOT NULL DEFAULT '1970-01-01', separated DATE NOT NULL DEFAULT '9999-12-31', job_code INT, store_id INT );

这是用作第22.2.1节“RANGE分区”中示例基础的相同表格。）

假设在4个特许经营店中分布有20个视频商店，如下表所示。

要对属于同一区域的存储的行存储在同一分区中的方式对此表进行分区，可以使用此处显示的CREATE TABLE语句：

CREATE TABLE employees ( id INT NOT NULL, fname VARCHAR(30), lname VARCHAR(30), hired DATE NOT NULL DEFAULT '1970-01-01', separated DATE NOT NULL DEFAULT '9999-12-31', job_code INT, store_id INT )
PARTITION BY LIST(store_id) (
PARTITION pNorth VALUES IN (3,5,6,9,17),
PARTITION pEast VALUES IN (1,2,10,11,19,20),
PARTITION pWest VALUES IN (4,12,13,14,18),
PARTITION pCentral VALUES IN (7,8,15,16)
);

这样可以轻松地将与特定区域相关的员工记录添加到表中或从表中删除。例如，假设西部地区的所有商店都出售给另一家公司。在MySQL 5.7中，可以使用查询ALTER TABLE employees TRUNCATE PARTITION pWest删除与在该区域中的商店工作的员工相关的所有行，这可以比等效的DELETE语句更有效地执行DELETE FROM employees WHERE store_id IN（4,12，13,14,18）;（使用ALTER TABLE员工DROP PARTITION pWest也会删除所有这些行，但也会从表的定义中删除分区pWest;您需要使用ALTER TABLE ... ADD PARTITION语句来恢复表的原始分区。）

与RANGE分区一样，可以将LIST分区与hash或key分区相结合，以生成复合分区（子分区）。请参见第22.2.6节“子分区”。

与RANGE分区的情况不同，没有“catch-all”，例如MAXVALUE; 分区表达式的所有预期值都应在PARTITION ... VALUES IN（...）子句中覆盖。包含不匹配的分区列值的INSERT语句将失败并显示错误，如以下示例所示：

mysql> CREATE TABLE h2 (
-> c1 INT,
-> c2 INT -> )
-> PARTITION BY LIST(c1) (
-> PARTITION p0 VALUES IN (1, 4, 7),
-> PARTITION p1 VALUES IN (2, 5, 8)
-> );
Query OK, 0 rows affected (0.11 sec)
mysql> INSERT INTO h2 VALUES (3, 5); ERROR 1525 (HY000): Table has no partition for value 3

使用单个INSERT语句插入多行时，行为取决于表是否使用事务存储引擎。对于InnoDB表，该语句被视为单个事务，因此任何不匹配的值的存在都会导致语句完全失败，并且不会插入任何行。对于使用非事务性存储引擎（如MyISAM）的表，将插入包含不匹配值的行之前的行，但不会插入任何后续行。

您可以使用IGNORE关键字忽略此类错误。如果这样做，则不会插入包含不匹配的分区列值的行，但会插入具有匹配值的任何行，并且不会报告任何错误：

mysql> TRUNCATE h2;
Query OK, 1 row affected (0.00 sec)
mysql> SELECT * FROM h2;
Empty set (0.00 sec)
mysql> INSERT IGNORE INTO h2 VALUES (2, 5), (6, 10), (7, 5), (3, 1), (1, 9);
Query OK, 3 rows affected (0.00 sec) Records: 5 Duplicates: 2 Warnings: 0
mysql> SELECT * FROM h2;
+------+------+
| c1 | c2 |
+------+------+
| 7 | 5 |
| 1 | 9 |
| 2 | 5 |
+------+------+
3 rows in set (0.00 sec)

MySQL 5.7为LIST COLUMNS分区提供支持。这是LIST分区的一种变体，它允许您使用除整数类型以外的类型列来分区列，以及使用多个列作为分区键。

22.2.3 COLUMNS 分区

接下来的两节讨论COLUMNS分区，它们是RANGE和LIST分区的变体。 COLUMNS分区允许在分区键中使用多个列。所有这些列都被考虑在内，以在分区中放置行，以及确定在分区修剪中检查哪些分区的匹配行。

此外，RANGE COLUMNS分区和LIST COLUMNS分区都支持使用非整数字段来定义值范围或列表成员。允许的数据类型显示在以下列表中：

所有整数类型：TINYINT，SMALLINT，MEDIUMINT，INT（INTEGER）和BIGINT。（这与RANGE和LIST的分区相同。）

其他数字数据类型（如DECIMAL或FLOAT）不支持作为分区列。

DATE 和DATETIME

其他与日期和时间相关的数据类型不支持作为分区字段。

以下字符串类型：CHAR，VARCHAR，BINARY和VARBINARY。

不支持TEXT和BLOB列作为分区列。

22.2.3.1 RANGE COLUMNS 分区

范围字段分区与范围分区类似，但允许您使用基于多个列值的范围来定义分区。此外，您可以使用整数类型以外的类型列来定义范围。

RANGE COLUMNS分区在以下方面与RANGE分区显着不同：

RANGE COLUMNS不接受表达式，只接受列的名称。
RANGE COLUMNS接受一列或多列。RANGE COLUMNS分区基于元组之间的比较（字段值列表），而不是标量值之间的比较。放置在 RANGE COLUMNS分区中行也基于元组之间的比较; 这将在本节后面进一步讨论。
RANGE COLUMNS分区列不限于整数列; string，DATE和DATETIME列也可用作分区列。

创建由RANGE COLUMNS分区的表的基本语法如下所示：

CREATE TABLE table_name
PARTITIONED BY RANGE COLUMNS(column_list) (
PARTITION partition_name VALUES LESS THAN (value_list)[, PARTITION partition_name VALUES LESS THAN (value_list)][, ...
] )
column_list:
column_name[, column_name][, ...]
value_list:
value[, value][, ...]

注意：
此处显示的并非所有可在创建分区表时使用的CREATE TABLE选项。

在刚刚显示的语法中，column_list是一个或多个列的列表（有时称为分区字段列表），value_list是值列表（即，它是分区定义值列表）。必须为每个分区定义提供value_list，并且每个value_list必须具有与column_list具有字段相同的值的数目。一般来说，如果在COLUMNS子句中使用N个字段，则还必须为每个VALUES LESS THAN子句提供N个值的列表。

分区字段列表和定义每个分区的值列表中的元素必须以相同的顺序出现。此外，值列表中的每个元素必须与字段列表中的相应元素具有相同的数据类型。但是，分区列列表中的列名称和值列表的顺序不必与CREATE TABLE语句主要部分中的表列定义的顺序相同。与RANGE分区的表一样，您可以使用MAXVALUE来表示一个值，以便插入到给定列中的任何合法值始终小于此值。下面是一个CREATE TABLE语句的示例，它有助于说明所有这些要点：

mysql> CREATE TABLE rcx (
-> a INT,
-> b INT,
-> c CHAR(3),
-> d INT
-> )
-> PARTITION BY RANGE COLUMNS(a,d,c)(
-> PARTITION p0 VALUES LESS THAN (5,10,'ggg'),
-> PARTITION p1 VALUES LESS THAN (10,20,'mmm'),
-> PARTITION p2 VALUES LESS THAN (15,30,'sss'),
-> PARTITION p3 VALUES LESS THAN (MAXVALUE,MAXVALUE,MAXVALUE) -> );
Query OK, 0 rows affected (0.15 sec)

表rcx包含列a，b，c，d。提供给COLUMNS子句的分区列列表使用其中3列，顺序为a，d，c。用于定义分区的每个值列表包含相同顺序的3个值; 也就是说，每个值列表元组具有形式（INT，INT，CHAR（3）），其对应于列a，d和c（按此顺序）使用的数据类型。

将行放入分区是通过比较要插入的与COLUMNS子句中的列列表匹配的行的元组与VALUES LESS THAN子句中使用的元组来定义表的分区来确定的。因为我们比较元组（即列表或值集）而不是标量值，所以与RANGE COLUMNS分区一起使用的VALUES LESS THAN的语义与简单RANGE分区的情况略有不同。在RANGE分区中，生成表达式值的行等于VALUES LESS THAN中的限制值永远不会放在相应的分区中; 但是，在使用RANGE COLUMNS分区时，有时可能会将分区列列表的个元素的值与VALUES LESS THAN值列表中个元素的值相等的行放在相应的分区中。

考虑由此语句创建的RANGE分区表：

CREATE TABLE r1 ( a INT, b INT ) PARTITION BY RANGE (a) ( PARTITION p0 VALUES LESS THAN (5),
PARTITION p1 VALUES LESS THAN (MAXVALUE)
);

如果我们在这个表中插入3行，使得每行的a的列值为5，则所有3行都存储在分区p1中，因为a列值在每种情况下都不小于5，正如我们通过执行对INFORMATION_SCHEMA.PARTITIONS表的正确查询：

mysql> INSERT INTO r1 VALUES (5,10), (5,11), (5,12); Query OK, 3 rows affected (0.00 sec) Records: 3 Duplicates: 0 Warnings: 0
mysql> SELECT PARTITION_NAME,TABLE_ROWS
-> FROM INFORMATION_SCHEMA.PARTITIONS
-> WHERE TABLE_NAME = 'r1';

现在考虑一个类似的表rc1，它使用RANGE COLUMNS分区，同时在COLUMNS子句中引用的列a和b，如下所示创建：

CREATE TABLE rc1 (
a INT,
b INT )
PARTITION BY RANGE COLUMNS(a, b) (
PARTITION p0 VALUES LESS THAN (5, 12),
PARTITION p3 VALUES LESS THAN (MAXVALUE, MAXVALUE)
);

如果我们将完全相同的行插入到rc1中，就像我们刚插入到r1中那样，行的分布是完全不同的：

mysql> INSERT INTO rc1 VALUES (5,10), (5,11), (5,12); Query OK, 3 rows affected (0.00 sec) Records: 3 Duplicates: 0 Warnings: 0
mysql> SELECT PARTITION_NAME,TABLE_ROWS
-> FROM INFORMATION_SCHEMA.PARTITIONS
-> WHERE TABLE_NAME = 'rc1';

这是因为我们正在比较行而不是标量值。我们可以比较用于定义表rc1中的分区p0的VALUES THAN LESS THAN子句中插入的行值和限制行值，如下所示：

mysql> SELECT (5,10) < (5,12), (5,11) < (5,12), (5,12) < (5,12);

2元组（5,10）和（5,11）的评估小于（5,12），因此它们存储在分区p0中。由于（5,12）被认为不小于（5,12），并且存储在分区p1中。

前面示例中的SELECT语句也可以使用显式行构造函数编写，如下所示：

SELECT ROW(5,10) < ROW(5,12), ROW(5,11) < ROW(5,12), ROW(5,12) < ROW(5,12);

对于由RANGE COLUMNS仅使用单个分区列分区的表，分区中行的存储与RANGE分区的等效表的存储相同。以下CREATE TABLE语句使用1个分区列创建由RANGE COLUMNS分区的表：

CREATE TABLE rx ( a INT, b INT )
PARTITION BY RANGE COLUMNS (a) (
PARTITION p0 VALUES LESS THAN (5),
PARTITION p1 VALUES LESS THAN (MAXVALUE)
);

如果我们将行（5,10），（5,11）和（5,12）插入到此表中，我们可以看到它们的位置与我们之前创建和填充的表r的位置相同：

mysql> INSERT INTO rx VALUES (5,10), (5,11), (5,12); Query OK, 3 rows affected (0.00 sec) Records: 3 Duplicates: 0 Warnings: 0
mysql> SELECT PARTITION_NAME,TABLE_ROWS
-> FROM INFORMATION_SCHEMA.PARTITIONS
-> WHERE TABLE_NAME = 'rx';

还可以创建由RANGE COLUMNS分区的表，其中在连续的分区定义中重复一列或多列的限制值。只要用于定义分区的列值元组严格增加，就可以执行此操作。例如，以下每个CREATE TABLE语句都有效：

CREATE TABLE rc2 ( a INT, b INT
)
PARTITION BY RANGE COLUMNS(a,b) (
PARTITION p0 VALUES LESS THAN (0,10),
PARTITION p1 VALUES LESS THAN (10,20),
PARTITION p2 VALUES LESS THAN (10,30),
PARTITION p3 VALUES LESS THAN (MAXVALUE,MAXVALUE)
);

CREATE TABLE rc3 ( a INT, b INT
) PARTITION BY RANGE COLUMNS(a,b) (
PARTITION p0 VALUES LESS THAN (0,10),
PARTITION p1 VALUES LESS THAN (10,20),
PARTITION p2 VALUES LESS THAN (10,30),
PARTITION p3 VALUES LESS THAN (10,35),
PARTITION p4 VALUES LESS THAN (20,40),
PARTITION p5 VALUES LESS THAN (MAXVALUE,MAXVALUE)
);

下面的语句也会成功，即使乍一看它也不会成功，因为列b的限制值对于分区p0是25，而对于分区p1是20，并且对于分区p1而言，列c的限制值是100，分区p2为50：

CREATE TABLE rc4 ( a INT,
b INT,
c INT )
PARTITION BY RANGE COLUMNS(a,b,c) (
PARTITION p0 VALUES LESS THAN (0,25,50),
PARTITION p1 VALUES LESS THAN (10,20,100),
PARTITION p2 VALUES LESS THAN (10,30,50)
PARTITION p3 VALUES LESS THAN (MAXVALUE,MAXVALUE,MAXVALUE)
);

在设计由RANGE COLUMNS分区的表时，您始终可以通过使用mysql客户端比较所需的元组来测试连续的分区定义，如下所示：

mysql> SELECT (0,25,50) < (10,20,100), (10,20,100) < (10,30,50);

如果CREATE TABLE语句包含的分区定义不是严格按递增顺序排列，则会失败并显示错误，如下例所示：

mysql> CREATE TABLE rcf (
-> a INT,
-> b INT,
-> c INT ->
) -> PARTITION BY RANGE COLUMNS(a,b,c) (
-> PARTITION p0 VALUES LESS THAN (0,25,50),
-> PARTITION p1 VALUES LESS THAN (20,20,100),
-> PARTITION p2 VALUES LESS THAN (10,30,50),
-> PARTITION p3 VALUES LESS THAN (MAXVALUE,MAXVALUE,MAXVALUE) ->
); ERROR 1493 (HY000): VALUES LESS THAN value must be strictly increasing for each partition

当您收到此类错误时，可以通过在列列表之间进行“小于”比较来推断出哪些分区定义。在这种情况下，问题在于分区p2的定义，因为用于定义它的元组不小于用于定义分区p3的元组，如下所示：

--也就是说，p2 不是大的元组

mysql> SELECT (0,25,50) < (20,20,100), (20,20,100) < (10,30,50);

使用RANGE COLUMNS时，MAXVALUE也可能出现在多个VALUES LESS THAN子句中的同一列中。但是，连续分区定义中各列的限制值应该增加，不能跨分区，其中MAXVALUE用作所有列值的上限，并且此分区定义应出现在 PARTITION ... VALUES LESS THAN。此外，您不能将MAXVALUE用作多个分区定义中列的限制值。

如前所述，RANGE COLUMNS分区也可以使用非整数列作为分区列。（有关这些内容的完整列表，请参见第22.2.3节“字段分区”。）考虑使用以下语句创建的名为employees（未分区）的表：

CREATE TABLE employees (
id INT NOT NULL,
fname VARCHAR(30),
lname VARCHAR(30),
hired DATE NOT NULL DEFAULT '1970-01-01',
separated DATE NOT NULL DEFAULT '9999-12-31',
job_code INT NOT NULL, store_id INT NOT NULL
);
使用RANGE COLUMNS分区，您可以创建此表的一个版本，根据员工的姓氏将行存储在四个分区中，如下所示：

CREATE TABLE employees_by_lname (
id INT NOT NULL,
fname VARCHAR(30),
lname VARCHAR(30),
hired DATE NOT NULL DEFAULT '1970-01-01',
separated DATE NOT NULL DEFAULT '9999-12-31',
job_code INT NOT NULL,
store_id INT NOT NULL )
PARTITION BY RANGE COLUMNS (lname) (
PARTITION p0 VALUES LESS THAN ('g'),
PARTITION p1 VALUES LESS THAN ('m'),
PARTITION p2 VALUES LESS THAN ('t'),
PARTITION p3 VALUES LESS THAN (MAXVALUE) );

或者，您可以通过执行以下ALTER TABLE语句，使用此方案对以前创建的employees表进行分区：

ALTER TABLE employees PARTITION BY RANGE COLUMNS (lname) ( PARTITION p0 VALUES LESS THAN ('g'),
PARTITION p1 VALUES LESS THAN ('m'),
PARTITION p2 VALUES LESS THAN ('t'),
PARTITION p3 VALUES LESS THAN (MAXVALUE) );

注意：

由于不同的字符集和排序规则具有不同的排序顺序，因此当使用字符串列作为分区列时，使用的字符集和排序规则可能影响由RANGE COLUMNS分区的表给定的行存储在哪个分区中。此外，在创建此类表之后更改给定数据库，表或列的字符集或排序规则可能会导致更改行的分布方式。例如，当使用区分大小写的排序规则时，'and' 排序在'Andersen'之前，但是当使用不区分大小写的排序规则时，则相反。

类似地，您可以使employees表以这样的方式进行分区：根据使用此处显示的ALTER TABLE语句雇用相应员工的十年，每行存储在多个分区之一中：

ALTER TABLE employees PARTITION BY RANGE COLUMNS (hired) ( PARTITION p0 VALUES LESS THAN ('1970-01-01'),
PARTITION p1 VALUES LESS THAN ('1980-01-01'),
PARTITION p2 VALUES LESS THAN ('1990-01-01'),
PARTITION p3 VALUES LESS THAN ('2000-01-01'),
PARTITION p4 VALUES LESS THAN ('2010-01-01'),
PARTITION p5 VALUES LESS THAN (MAXVALUE) );

22.2.3.2 LIST COLUMNS 分区

MySQL 5.7为LIST COLUMNS分区提供支持。这是LIST分区的一种变体，它允许使用多个列作为分区键，并将整数类型以外的数据类型列用作分区列; 您可以使用字符串类型，DATE和DATETIME列。

假设您的业务在12个城市拥有客户，出于销售和营销目的，您将组织成3个城市的4个区域，如下表所示：

通过LIST COLUMNS分区，您可以为客户数据创建一个表，根据客户所在城市的名称将行分配给与这些区域对应的4个分区中的任意一个，如下所示：

CREATE TABLE customers_1 ( first_name VARCHAR(25), last_name VARCHAR(25), street_1 VARCHAR(30), street_2 VARCHAR(30), city VARCHAR(15), renewal DATE )
PARTITION BY LIST COLUMNS(city) (
PARTITION pRegion_1 VALUES IN('Oskarshamn', 'Högsby', 'Mönsterås'),
PARTITION pRegion_2 VALUES IN('Vimmerby', 'Hultsfred', 'Västervik'),
PARTITION pRegion_3 VALUES IN('Nässjö', 'Eksjö', 'Vetlanda'), PARTITION pRegion_4 VALUES IN('Uppvidinge', 'Alvesta', 'Växjo') );

与RANGE COLUMNS分区一样，您不需要在COLUMNS（）子句中使用表达式将列值转换为整数。（实际上，COLUMNS（）不允许使用列名以外的表达式。）

也可以使用DATE和DATETIME列，如以下示例所示，该示例使用与先前显示的customers_1表相同的名称和列，但使用基于 renewal 的LIST COLUMNS分区，将行存储在4个分区之一中，具体取决于2010年2月的周：

CREATE TABLE customers_2 ( first_name VARCHAR(25), last_name VARCHAR(25), street_1 VARCHAR(30), street_2 VARCHAR(30), city VARCHAR(15), renewal DATE )
PARTITION BY LIST COLUMNS(renewal) (
PARTITION pWeek_1 VALUES IN('2010-02-01', '2010-02-02', '2010-02-03', '2010-02-04', '2010-02-05', '2010-02-06', '2010-02-07'),
PARTITION pWeek_2 VALUES IN('2010-02-08', '2010-02-09', '2010-02-10', '2010-02-11', '2010-02-12', '2010-02-13', '2010-02-14'),
PARTITION pWeek_3 VALUES IN('2010-02-15', '2010-02-16', '2010-02-17', '2010-02-18', '2010-02-19', '2010-02-20', '2010-02-21'),
PARTITION pWeek_4 VALUES IN('2010-02-22', '2010-02-23', '2010-02-24', '2010-02-25', '2010-02-26', '2010-02-27', '2010-02-28')
);

如果涉及的日期数量变得非常大，则定义和维护变得很麻烦; 在这种情况下，采用RANGE或RANGE COLUMNS分区通常更为实际。在这种情况下，由于我们希望用作分区键的列是DATE列，我们使用RANGE COLUMNS分区，如下所示：

CREATE TABLE customers_3 ( first_name VARCHAR(25), last_name VARCHAR(25), street_1 VARCHAR(30), street_2 VARCHAR(30), city VARCHAR(15), renewal DATE )
PARTITION BY RANGE COLUMNS(renewal) (
PARTITION pWeek_1 VALUES LESS THAN('2010-02-09'),
PARTITION pWeek_2 VALUES LESS THAN('2010-02-15'),
PARTITION pWeek_3 VALUES LESS THAN('2010-02-22'),
PARTITION pWeek_4 VALUES LESS THAN('2010-03-01') );

22.2.4 HASH 分区

通过HASH进行分区主要用于确保在预定数量的分区之间均匀分布数据。使用范围或列表分区时，必须明确指定要存储给定列值或列值集的分区; 通过hash分区，MySQL会为您处理此问题，并且您只需要根据要散列的列值和分区表要划分的分区数指定列值或表达式。

要使用HASH分区对表进行分区，必须在CREATE TABLE语句中附加PARTITION BY HASH（expr）子句，其中expr是一个返回整数的表达式。这可以简单地是列的名称，其类型是MySQL的整数类型之一。此外，您有可能希望使用PARTITIONS num，其中num是一个正整数，表示要将表分成的分区数。

注意：
为简单起见，以下示例中的表不使用任何键。您应该知道，如果表具有任何键，则此表的分区表达式中使用的每个列都必须是每个键的一部分，包括主键。

以下语句创建一个在store_id列上使用hash的表，该表分为4个分区：

CREATE TABLE employees ( id INT NOT NULL, fname VARCHAR(30), lname VARCHAR(30), hired DATE NOT NULL DEFAULT '1970-01-01', separated DATE NOT NULL DEFAULT '9999-12-31', job_code INT, store_id INT )
PARTITION BY HASH(store_id) PARTITIONS 4;

如果未包含PARTITIONS子句，则分区数默认为1。

使用PARTITIONS关键字后面没有数字会导致语法错误。

您还可以使用为expr返回整数的SQL表达式。例如，您可能希望根据雇用员工的年份进行分区。这可以如下所示完成：

CREATE TABLE employees ( id INT NOT NULL, fname VARCHAR(30), lname VARCHAR(30), hired DATE NOT NULL DEFAULT '1970-01-01', separated DATE NOT NULL DEFAULT '9999-12-31', job_code INT, store_id INT )
PARTITION BY HASH( YEAR(hired) ) PARTITIONS 4;

expr必须返回一个非常量的非随机整数值（换句话说，它应该是变化的但是确定性的），并且不得包含第22.6节“分区的限制和限制”中所述的任何禁止的构造。您还应该记住，每次插入或更新（或可能删除）行时都会评估此表达式; 这意味着非常复杂的表达式可能会导致性能问题，尤其是在执行一次影响大量行的操作（例如批量插入）时。

有效的散列函数是在单个表列上操作的函数，其值随字段值保持一致的增加或减少，因为这允许在分区范围上“修剪”。也就是说，表达式越接近它所基于的列的值，MySQL就越有效地使用表达式进行散列分区。

例如，如果date_col是DATE类型的列，那么表达式TO_DAYS（date_col）将直接与date_col的值相关，因为对于date_col值的每次更改，表达式的值都以一致的方式更改。表达式YEAR（date_col）相对于date_col的方差不如TO_DAYS（date_col）那样直接，因为并非date_col中的每个可能的更改都会在YEAR（date_col）中产生等效的更改。即便如此，YEAR（date_col）是散列函数的一个很好的候选者，因为它直接与date_col的一部分有所不同，date_col中一些不能同变化在YEAR（date_col）中产生不成比例的变化。

相比之下，假设您有一个名为int_col的列，其类型为INT。现在考虑表达式POW（5-int_col，3）+ 6.这对于哈希函数来说是一个糟糕的选择，因为int_col值的变化不能保证表达式值的比例变化。将int_col值的给定量变化会在表达式的值中产生不同度的更改。例如，将int_col从5更改为6会在表达式的值中生成-1的更改，但将int_col的值从6更改为7会在表达式值中更改-7。

换句话说，字段值与表达式值的关系曲线越接近直线，如式y = cx所示，其中c是某个非零常数，表达式越适合于哈希。事实是表达式越不均匀，它倾向于产生的分区之间的数据分布越不均匀。

理论上，对于涉及多个字段值的表达式，剪裁也是可能的，但是确定哪个表达式是合适的可能是非常困难和耗时的。因此，不特别推荐使用涉及多个字段的hash列表达式。

当使用PARTITION BY HASH时，MySQL根据表达式结果的模数确定要使用的num分区。换句话说，对于给定的表达式expr，存储记录的分区是分区号N，其中N = MOD（expr，num）。假设表t1定义如下，因此它有4个分区：

CREATE TABLE t1 (col1 INT, col2 CHAR(5), col3 DATE) PARTITION BY HASH( YEAR(col3) ) PARTITIONS 4;

如果将记录插入到col3值为'2005-09-15'的t1中，则存储它的分区将按如下方式确定：
MOD(YEAR('2005-09-01'),4) = MOD(2005,4) = 1

22.2.4.1 LINEAR HASH分区

MySQL还支持线性散列，这与常规散列不同，线性散列使用线性二次幂算法，而常规散列使用散列函数值的模数。

从语法上讲，线性散列分区和常规散列之间的区别是在PARTITION BY子句中添加了LINEAR关键字，如下所示：

CREATE TABLE employees ( id INT NOT NULL, fname VARCHAR(30), lname VARCHAR(30), hired DATE NOT NULL DEFAULT '1970-01-01', separated DATE NOT NULL DEFAULT '9999-12-31', job_code INT, store_id INT )
PARTITION BY LINEAR HASH( YEAR(hired) ) PARTITIONS 4;

给定表达式expr，使用线性散列时存储记录的num分区中的分区号N，其中N是根据以下算法导出的：

1.找到大于num的2的下一个幂。我们称这个值为V; 它可以计算为：
V = POWER(2, CEILING(LOG(2, num)))

（假设num为13.则LOG（2,13）为3.7004397181411。CEILING（3.7004397181411）为4，V = POWER（2,4），即16。）
2.设置N= F(column_list) & (V - 1)

3.while N>=num:

set V=V/2
set N & (V - 1)

假设使用此语句创建使用线性哈希分区并具有6个分区的表t1：
CREATE TABLE t1 (col1 INT, col2 CHAR(5), col3 DATE)
PARTITION BY LINEAR HASH( YEAR(col3) )
PARTITIONS 6;

现在假设您要将两个记录col3字段值“2003-04-14”和“1998-10-19”插入到t1中。个的分区号确定如下：

V = POWER(2, CEILING( LOG(2,6) )) = 8
N = YEAR('2003-04-14') & (8 - 1) = 2003 & 7 = 3 (3 >= 6 is FALSE: record stored in partition #3)

存储第二条记录的分区number计算如下所示：

V = 8
N = YEAR('1998-10-19') & (8 - 1) = 1998 & 7 = 6
(6 >= 6 is TRUE: additional step required)
N = 6 & ((8 / 2) - 1) = 6 & 3 = 2 (2 >= 6 is FALSE: record stored in partition #2)

通过线性哈希进行分区的优点是分区的添加，删除，合并和拆分要快得多，这在处理包含极大量（兆兆字节）数据的表时非常有用。缺点是与使用常规散列分区获得的分布相比，数据不太可能在分区之间均匀分布。

22.2.5 Key 分区

按key分区类似于通过hash进行分区，除了hash分区采用用户定义的表达式之外，key分区的hash函数由MySQL服务器提供。 NDB Cluster使用MD5（）来实现此目的; 对于使用其他存储引擎的表，服务器使用自己的内部hash函数，该函数基于与PASSWORD（）相同的算法。

CREATE TABLE ... PARTITION BY KEY的语法规则类似于创建由hash分区的表的规则。这里列出了主要差异：

使用KEY而不是HASH。
KEY只接受零个或多个列名称的列表。用作分区键的任何列必须包含表的主键的部分或全部。如果没有将列名指定为分区键，则使用表的主键（如果有）。例如，以下CREATE TABLE语句在MySQL 5.7中有效：

CREATE TABLE k1 ( id INT NOT NULL PRIMARY KEY, name VARCHAR(20) ) PARTITION BY KEY() PARTITIONS 2;

如果没有主键但有键，则键用于分区键：

CREATE TABLE k1 ( id INT NOT NULL, name VARCHAR(20), UNIQUE KEY (id) ) PARTITION BY KEY() PARTITIONS 2;

但是，如果键列未定义为NOT NULL，则之前的语句将失败。

在这两种情况下，分区键都是id列，即使它未显示在SHOW CREATE TABLE的输出中或INFORMATION_SCHEMA.PARTITIONS表的PARTITION_EXPRESSION列中。

与其他分区类型的情况不同，用于按KEY分区的列不限于整数或NULL值。例如，以下CREATE TABLE语句有效：

CREATE TABLE tm1 ( s1 CHAR(32) PRIMARY KEY )
PARTITION BY KEY(s1) PARTITIONS 10;

如果要指定不同的分区类型，则前面的语句。（在这种情况下，简单地使用PARTITION BY KEY（）也是有效的，并且与PARTITION BY KEY（s1）具有相同的效果，因为s1是表的主键。）

也可以通过线性key对表进行分区。这是一个简单的例子：
CREATE TABLE tk ( col1 INT NOT NULL, col2 CHAR(5), col3 DATE ) PARTITION BY LINEAR KEY (col1) PARTITIONS 3;

使用LINEAR对KEY分区的影响与对HASH分区的影响相同，分区号是使用二次幂算法而不是模运算得出的。有关此算法及其含义的说明，请参见第22.2.4.1节“线性HASH分区”。

22.2.6 子分区

子分区 - 也称为复合分区 - 是分区表中每个分区的进一步划分。请考虑以下CREATE TABLE语句：
CREATE TABLE ts (id INT, purchased DATE)
PARTITION BY RANGE( YEAR(purchased) )
SUBPARTITION BY HASH( TO_DAYS(purchased) )
SUBPARTITIONS 2 (
PARTITION p0 VALUES LESS THAN (1990),
PARTITION p1 VALUES LESS THAN (2000),
PARTITION p2 VALUES LESS THAN MAXVALUE );

表ts有3个RANGE分区。这些分区被进一步分为2个子分区 p0,p1,p2。实际上，整个表分为3 * 2 = 6个分区。但是，由于PARTITION BY RANGE子句的操作，前两个子句仅存储 purchased 列中值小于1990的记录。

在MySQL 5.7中，可以对由RANGE或LIST分区的子表进行分区。子分区可以使用HASH或KEY分区。这也称为复合分区。

注意：
SUBPARTITION by KEY 和 SUBPARTITION by HASH 的SUBPARTITION通常遵循与PARTITION BY HASH和PARTITION BY KEY相同的语法规则。一个例外是SUBPARTITION BY KEY（与PARTITION BY KEY不同）当前不支持默认列，因此必须指定用于此目的的列，即使该表具有显式主键也是如此。这是我们正在努力解决的一个已知问题;

也可以使用SUBPARTITION子句显式定义子分区，以指定各个子分区的选项。例如，创建与上一个示例中显示的相同的表ts的更详细的方式是：

CREATE TABLE ts (id INT, purchased DATE)
PARTITION BY RANGE( YEAR(purchased) )
SUBPARTITION BY HASH( TO_DAYS(purchased) )
( PARTITION p0 VALUES LESS THAN (1990)
( SUBPARTITION s0, SUBPARTITION s1 ),
PARTITION p1 VALUES LESS THAN (2000)
( SUBPARTITION s2, SUBPARTITION s3 ),
PARTITION p2 VALUES LESS THAN MAXVALUE
( SUBPARTITION s4, SUBPARTITION s5 ) );

这里列出了一些注释的语法项：

每个分区必须具有相同数量的子分区。
如果在分区表的任何分区上使用SUBPARTITION显式定义任何子分区，则必须全部定义它们。换句话说，以下语句将失败：

每个SUBPARTITION子句必须包含（至少）子分区的名称。否则，您可以为子分区设置任何所需选项，或允许其采用该选项的默认设置。

子分区名称在整个表中必须是的。例如，以下CREATE TABLE语句在MySQL 5.7中有效：

CREATE TABLE ts (id INT, purchased DATE) PARTITION BY RANGE( YEAR(purchased) ) SUBPARTITION BY HASH( TO_DAYS(purchased) ) ( PARTITION p0 VALUES LESS THAN (1990) ( SUBPARTITION s0, SUBPARTITION s1 ), PARTITION p1 VALUES LESS THAN (2000) ( SUBPARTITION s2, SUBPARTITION s3 ), PARTITION p2 VALUES LESS THAN MAXVALUE ( SUBPARTITION s4, SUBPARTITION s5 ) );

子分区可以与特别大的MyISAM表一起使用，以跨多个磁盘分发数据和索引。假设您有6个磁盘挂载为/disk0，/disk1，/disk2，依此类推。现在考虑以下示例：

CREATE TABLE ts (id INT, purchased DATE) ENGINE = MYISAM PARTITION BY RANGE( YEAR(purchased) ) SUBPARTITION BY HASH( TO_DAYS(purchased) ) ( PARTITION p0 VALUES LESS THAN (1990) ( SUBPARTITION s0 DATA DIRECTORY = '/disk0/data' INDEX DIRECTORY = '/disk0/idx', SUBPARTITION s1 DATA DIRECTORY = '/disk1/data' INDEX DIRECTORY = '/disk1/idx' ), PARTITION p1 VALUES LESS THAN (2000) ( SUBPARTITION s2 DATA DIRECTORY = '/disk2/data' INDEX DIRECTORY = '/disk2/idx', SUBPARTITION s3 DATA DIRECTORY = '/disk3/data' INDEX DIRECTORY = '/disk3/idx' ), PARTITION p2 VALUES LESS THAN MAXVALUE ( SUBPARTITION s4 DATA DIRECTORY = '/disk4/data' INDEX DIRECTORY = '/disk4/idx', SUBPARTITION s5 DATA DIRECTORY = '/disk5/data' INDEX DIRECTORY = '/disk5/idx' ) );

在这种情况下，单独的磁盘用于数据和每个RANGE的索引。

---在大多数情况下，磁盘条带化已经交给存储来做了。

当NO_DIR_IN_CREATE服务器SQL模式生效时，分区定义中不允许使用DATA DIRECTORY和INDEX DIRECTORY选项。在MySQL 5.7中，在定义子分区时也不允许使用这些选项（Bug＃42954）。

22.2.7 MYSQL 分区如何处理NULL

MySQL中的分区不会禁止NULL作为分区表达式的值，无论它是列值还是用户提供的表达式的值。即使允许使用NULL作为必须产生整数的表达式的值，但是要记住NULL不是数字。 MySQL的分区实现将NULL视为小于任何非NULL值，就像ORDER BY一样。

这意味着NULL的处理在不同类型的分区之间变化，并且如果您没有为此做好准备，可能会产生您不期望的行为。在这种情况下，我们将在本节中讨论每个MySQL分区类型在确定应存储行的分区时如何处理NULL值，并为每个分区提供例子。

使用RANGE分区处理NULL。如果将行插入由RANGE分区的表中，以便用于确定分区的列值为NULL，则将该行插入低分区。在名为p的数据库中考虑这两个表，创建如下：

mysql> CREATE TABLE t1 ( -> c1 INT, -> c2 VARCHAR(20) -> ) -> PARTITION BY RANGE(c1) ( -> PARTITION p0 VALUES LESS THAN (0), -> PARTITION p1 VALUES LESS THAN (10), -> PARTITION p2 VALUES LESS THAN MAXVALUE -> );
Query OK, 0 rows affected (0.09 sec)
mysql> CREATE TABLE t2 ( -> c1 INT, -> c2 VARCHAR(20) -> ) -> PARTITION BY RANGE(c1) ( -> PARTITION p0 VALUES LESS THAN (-5), -> PARTITION p1 VALUES LESS THAN (0), -> PARTITION p2 VALUES LESS THAN (10), -> PARTITION p3 VALUES LESS THAN MAXVALUE -> ); Query OK, 0 rows affected (0.09 sec)

您可以使用以下查询针对INFORMATION_SCHEMA数据库中的PARTITIONS表查看这两个CREATE TABLE语句创建的分区：

mysql> SELECT TABLE_NAME, PARTITION_NAME, TABLE_ROWS, AVG_ROW_LENGTH, DATA_LENGTH > FROM INFORMATION_SCHEMA.PARTITIONS > WHERE TABLE_SCHEMA = 'p' AND TABLE_NAME LIKE 't_';

（有关此表的更多信息，请参见第24.16节“INFORMATION_SCHEMA PARTITIONS表”。）现在让我们使用在用作分区键的列中包含NULL的单行填充这些表，并使用一对SELECT语句验证行是否插入：

mysql> INSERT INTO t1 VALUES (NULL, 'mothra'); Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO t2 VALUES (NULL, 'mothra'); Query OK, 1 row affected (0.00 sec)
mysql> SELECT * FROM t1;

您可以通过对INFORMATION_SCHEMA.PARTITIONS重新运行上一个查询并检查输出来查看用于存储插入行的分区：

mysql> SELECT TABLE_NAME, PARTITION_NAME, TABLE_ROWS, AVG_ROW_LENGTH, DATA_LENGTH > FROM INFORMATION_SCHEMA.PARTITIONS > WHERE TABLE_SCHEMA = 'p' AND TABLE_NAME LIKE 't_';

您还可以通过删除这些分区来证明这些行存储在每个表的低分区中，然后重新运行SELECT语句：

mysql> ALTER TABLE t1 DROP PARTITION p0; Query OK, 0 rows affected (0.16 sec)
mysql> ALTER TABLE t2 DROP PARTITION p0; Query OK, 0 rows affected (0.16 sec)
mysql> SELECT * FROM t1;
Empty set (0.00 sec)
mysql> SELECT * FROM t2;
Empty set (0.00 sec)

对于使用SQL函数的表达式分区，也会以这种方式处理NULL。假设我们使用CREATE TABLE语句定义一个表，例如：

CREATE TABLE tndate ( id INT, dt DATE )
PARTITION BY RANGE( YEAR(dt) ) (
PARTITION p0 VALUES LESS THAN (1990),
PARTITION p1 VALUES LESS THAN (2000),
PARTITION p2 VALUES LESS THAN MAXVALUE );

与其他MySQL函数一样，YEAR（NULL）返回NULL。将dt列值为NULL的行视为分区表达式求值为小于任何其他值的值，因此将其插入到分区p0中。

list 分区处理NULL

当且仅当使用包含NULL的值列表定义其中一个分区时，由LIST分区的表才允许NULL值。与此相反的是，由LIST分区的表（未在值列表中显式使用NULL）拒绝导致分区表达式为NULL值的行，如下例所示：

mysql> CREATE TABLE ts1 (
-> c1 INT,
-> c2 VARCHAR(20)
-> )
-> PARTITION BY LIST(c1) (
-> PARTITION p0 VALUES IN (0, 3, 6),
-> PARTITION p1 VALUES IN (1, 4, 7),
-> PARTITION p2 VALUES IN (2, 5, 8)
-> ); Query OK, 0 rows affected (0.01 sec)
mysql> INSERT INTO ts1 VALUES (9, 'mothra'); ERROR 1504 (HY000): Table has no partition for value 9
mysql> INSERT INTO ts1 VALUES (NULL, 'mothra'); ERROR 1504 (HY000): Table has no partition for value NULL

只有c1值介于0和8之间的行才能插入到ts1中。 NULL超出此范围，就像数字9一样。我们可以创建表ts2和ts3，其值列表包含NULL，如下所示：

mysql> CREATE TABLE ts2 (
-> c1 INT,
-> c2 VARCHAR(20) -> )
-> PARTITION BY LIST(c1) (
-> PARTITION p0 VALUES IN (0, 3, 6),
-> PARTITION p1 VALUES IN (1, 4, 7),
-> PARTITION p2 VALUES IN (2, 5, 8),
-> PARTITION p3 VALUES IN (NULL)
-> );
Query OK, 0 rows affected (0.01 sec)
mysql> CREATE TABLE ts3 (
-> c1 INT,
-> c2 VARCHAR(20)
-> )
-> PARTITION BY LIST(c1) (
-> PARTITION p0 VALUES IN (0, 3, 6),
-> PARTITION p1 VALUES IN (1, 4, 7, NULL),
-> PARTITION p2 VALUES IN (2, 5, 8)
-> );
Query OK, 0 rows affected (0.01 sec)

在为分区定义值列表时，您可以（并且应该）像处理任何其他值一样处理NULL。例如，VALUES IN（NULL）和VALUES IN（1,4,7，NULL）都是有效的，VALUES IN（1，NULL，4,7），VALUES IN（NULL，1,4,7），等等。您可以将列c1中具有NULL的行插入到每个表ts2和ts3中：

mysql> INSERT INTO ts2 VALUES (NULL, 'mothra'); Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO ts3 VALUES (NULL, 'mothra'); Query OK, 1 row affected (0.00 sec)

通过针对INFORMATION_SCHEMA.PARTITIONS发出适当的查询，您可以确定哪些分区用于存储刚刚插入的行（我们假设，如前面的示例中所示，分区表是在p数据库中创建的）：

mysql> SELECT TABLE_NAME, PARTITION_NAME, TABLE_ROWS, AVG_ROW_LENGTH, DATA_LENGTH > FROM INFORMATION_SCHEMA.PARTITIONS > WHERE TABLE_SCHEMA = 'p' AND TABLE_NAME LIKE 'ts_';

如本节前面所示，您还可以通过删除这些分区然后执行SELECT来验证哪些分区用于存储行。

HASH 和 KEY 分区处理 NULL

对于由HASH或KEY分区的表，NULL处理方式略有不同。在这些情况下，产生NULL值的任何分区表达式都被视为其返回值为零。我们可以通过检查创建由HASH分区的表并使用包含适当值的记录填充它的文件系统的影响来验证此行为。假设您使用以下语句创建了一个表（也在p数据库中）：

mysql> CREATE TABLE th ( -> c1 INT, -> c2 VARCHAR(20) -> ) -> PARTITION BY HASH(c1) -> PARTITIONS 2; Query OK, 0 rows affected (0.00 sec)

可以使用此处显示的查询查看属于此表的分区：

mysql> SELECT TABLE_NAME,PARTITION_NAME,TABLE_ROWS,AVG_ROW_LENGTH,DATA_LENGTH > FROM INFORMATION_SCHEMA.PARTITIONS
> WHERE TABLE_SCHEMA = 'p' AND TABLE_NAME ='th';

每个分区的TABLE_ROWS为0.现在在其c1列值为NULL和0的位置插入两行，并验证是否插入了这些行，如下所示：

mysql> INSERT INTO th VALUES (NULL, 'mothra'), (0, 'gigan'); Query OK, 1 row affected (0.00 sec)
mysql> SELECT * FROM th;
+------+---------+
| c1 | c2 |
+------+---------+
| NULL | mothra |
+------+---------+
| 0 | gigan |
+------+---------+
2 rows in set (0.01 sec)

回想一下，对于任何整数N，NULL MOD N的值始终为NULL。对于由HASH或KEY分区的表，将此结果视为确定正确的分区0.再次检查INFORMATION_SCHEMA.PARTITIONS表，我们可以看到两行都插入到分区p0中：

mysql> SELECT TABLE_NAME, PARTITION_NAME, TABLE_ROWS, AVG_ROW_LENGTH, DATA_LENGTH > FROM INFORMATION_SCHEMA.PARTITIONS > WHERE TABLE_SCHEMA = 'p' AND TABLE_NAME ='th';

通过在表的定义中使用PARTITION BY KEY代替PARTITION BY HASH重复后一个示例

分享好友

分享这个小栈给你的朋友们，一起进步吧。

MySQL干货资料

创建时间：2020-05-06 14:18:32

每天都有干货输出哦

展开

订阅须知

• 所有用户可根据关注领域订阅专区或所有专区

• 付费订阅：虚拟交易，一经交易不退款；若特殊情况，可3日内客服咨询

• 专区发布评论属默认订阅所评论专区（除付费小栈外）

技术专家

查看更多

飘絮絮絮丶
专家