月份：2025年11月

子分区
子分区（也称为复合分区）是分区表中每个分区的进一步划分。请看下面的CREATE TABLE语句：

mysql> 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
    -> );
Query OK, 0 rows affected (0.02 sec)

表ts有3个范围分区。每个分区（p0、p1和p2）又进一步划分为2个子分区。实际上，整个表被分为3 * 2 = 6个分区。但是，由于PARTITION BY RANGE子句的作用，前2个只存储所购列中值小于1990的记录。

在MySQL 5.7中，可以对按范围或列表分区的表进行子分区。子分区可以使用哈希分区或键分区。这也称为复合分区。

也可以使用子分区子句显式定义子分区，为各个子分区指定选项。例如，在前面的例子中，创建相同的表ts的一种更冗长的方式是：

mysql> 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
    -> )
    -> );
Query OK, 0 rows affected (0.03 sec)

下面列出了一些语法注意事项：
.每个分区必须有相同数量的子分区。

.如果您在分区表的任何分区上使用SUBPARTITION显式地定义了任何子分区，则必须定义所有子分区。换句话说，下面的语句会失败：

mysql> CREATE TABLE ts1 (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),
    -> PARTITION p2 VALUES LESS THAN MAXVALUE (
    -> SUBPARTITION s2,
    -> SUBPARTITION s3
    -> )
    -> );
ERROR 1064 (42000): Wrong number of subpartitions defined, mismatch with previous setting near '
PARTITION p2 VALUES LESS THAN MAXVALUE (
SUBPARTITION s2,
SUBPARTITION s3
)
)' at line 8

即使包含SUBPARTITIONS 2子句，这条语句仍然会失败。

.每个子分区子句必须（至少）包含子分区的名称。否则，您可以为子分区设置任何想要的选项，或者允许它假定该选项的默认设置。

.子分区名在整个表中必须是唯一的。例如，下面的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的数据和索引使用一个单独的磁盘。许多其他的变化是可能的；另一个例子可能是：

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 s0a
DATA DIRECTORY = '/disk0'
INDEX DIRECTORY = '/disk1',
SUBPARTITION s0b
DATA DIRECTORY = '/disk2'
INDEX DIRECTORY = '/disk3'
),
PARTITION p1 VALUES LESS THAN (2000) (
SUBPARTITION s1a
DATA DIRECTORY = '/disk4/data'
INDEX DIRECTORY = '/disk4/idx',
SUBPARTITION s1b
DATA DIRECTORY = '/disk5/data'
INDEX DIRECTORY = '/disk5/idx'
),
PARTITION p2 VALUES LESS THAN MAXVALUE (
SUBPARTITION s2a,
SUBPARTITION s2b
)
);

这里，存储如下：
.购买日期在1990年之前的行占用了大量的空间，因此以4种方式拆分，一个单独的磁盘专门用于存储数据和两个子分区（s0a和s0b）的索引，这 两个分区组成了p0分区。换句话说：
子分区s0a的数据存储在/disk0上。

.子分区s0a的数据存储在/disk0上。
.子分区s0a的索引存储在/disk1上。
.子分区s0b的数据存储在/disk2上。
.子分区s0b的索引存储在/disk3上。

.包含从1990年到1999年的日期的行（分区p1）不需要像1990年以前的行那样多的空间。它们被划分为2个磁盘（/disk4和/disk5），而不是像以 前那样存储在p0中的4个磁盘。

.属于p1的第一个子分区（s1a）的数据和索引存储在/disk4中，其中数据存储在/disk4/ Data中，索引存储在/disk4/idx中。
.属于p1的第二个子分区（s1b）的数据和索引存储在/disk5中——数据存储在/disk5/ Data中，索引存储在/disk5/idx中。

.反映2000年至今日期的行（分区p2）所占空间与前两个范围相比都没有那么大。目前，将所有这些都存储在默认位置就足够了。

将来，当从2000年开始的十年的购买数量增长到默认位置不再提供足够的空间时，可以使用ALTER TABLE … REORGANIZE PARTITION语句移动相 应的行。

键分区
按键分区类似于按哈希分区，不同的是，哈希分区采用了用户定义的表达式，用于键分区的哈希函数由MySQL服务器提供。NDB集群使用MD5（） 来实现这个目的。对于使用其他存储引擎的表，服务器使用自己的内部哈希函数，该函数基于与PASSWORD（）相同的算法。

CREATE TABLE … PARTITION BY KEY的语法规则类似于按哈希分区的表。主要区别如下：
.使用KEY而不是HASH

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

mysql> CREATE TABLE k1 (
    -> id INT NOT NULL PRIMARY KEY,
    -> name VARCHAR(20)
    -> )
    -> PARTITION BY KEY()
    -> PARTITIONS 2;
Query OK, 0 rows affected (0.01 sec)

如果没有主键，但有一个唯一键，那么这个唯一键用于分区键：

mysql> CREATE TABLE k1 (
    -> id INT NOT NULL,
    -> name VARCHAR(20),
    -> UNIQUE KEY (id)
    -> )
    -> PARTITION BY KEY()
    -> PARTITIONS 2;
Query OK, 0 rows affected (0.01 sec)

但是，如果唯一键列没有定义为not NULL，那么前面的语句将失败。

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

与其他分区类型不同的是，用于按键分区的列并不局限于整数值或NULL值。例如，下面的CREATE TABLE语句是有效的：

mysql> CREATE TABLE tm1 (
    -> s1 CHAR(32) PRIMARY KEY
    -> )
    -> PARTITION BY KEY(s1)
    -> PARTITIONS 10;
Query OK, 0 rows affected (0.04 sec)

如果指定了不同的分区类型，上述语句将无效。（在这种情况下，简单地使用按键分区（）也是有效的，并且与按键分区（s1）的效果相同，因 为s1是表的主键。）

也可以通过线性键对表进行分区。这里有一个简单的例子：

mysql> CREATE TABLE tk (
    -> col1 INT NOT NULL,
    -> col2 CHAR(5),
    -> col3 DATE
    -> )
    -> PARTITION BY LINEAR KEY (col1)
    -> PARTITIONS 3;
Query OK, 0 rows affected (0.02 sec)

使用LINEAR对键分区的效果与对哈希分区的效果相同，分区数是使用2的幂算法而不是模运算得到的。

哈希分区
哈希分区主要用于确保数据在预定数量的分区中均匀分布。使用范围分区或列表分区时，用户必须明确指定给定的列值或一组列值存储在哪个分 区中；使用哈希分区时，MySQL会帮你处理这个问题，你只需要根据要哈希的列值和要划分的分区数指定一个列值或表达式。

要使用哈希分区对表进行分区，需要在CREATE table语句中添加一个partition BY HASH （expr）子句，其中expr是一个返回整数的表达式。这 可以是MySQL整数类型的列的名称。此外，你很可能想在接下来使用分区num，其中num是一个正整数，表示表被划分的分区数。

为简单起见，下面示例中的表不使用任何键。你应该注意，如果一张表有任何唯一键，那么在这个表的分区表达式中使用的每一列都必须是每个 唯一键的一部分，包括主键。

下面的语句创建了一个在store_id列上使用哈希的表，它被分为4个分区：

mysql> 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;
Query OK, 0 rows affected (0.01 sec)

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

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

你也可以使用在expr中返回整数的SQL表达式。例如，你可能想根据雇员受雇的年份进行分区。可以这样做：

mysql> 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;
Query OK, 0 rows affected (0.02 sec)

expr必须返回一个非常量、非随机的整数值（换句话说，它应该是可变的，但是确定的），而且不能包含任何禁止使用的结构。你还应该记住， 每当插入或更新（或删除）一行时，这个表达式都会被计算；这意味着非常复杂的表达式可能会导致性能问题，特别是在执行一次影响很多行的 操作（如批量插入）时。

最高效的散列函数是对表中的单个列进行操作，其值随列值的增加或减少而增加或减少，因为这允许在分区范围上进行“修剪”。也就是说，该 表达式与所基于的列的值变化越密切，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是某个非零常数，表达式越适合散列。这是因为表达式越非线性 ，其产生的分区之间的数据分布就越不均匀。

理论上，对于涉及多个列值的表达式，也可以进行“修剪”，但确定哪一个表达式适合是非常困难和耗时的。因此，不推荐使用涉及多列的散列 表达式。

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

mysql> CREATE TABLE t1 (col1 INT, col2 CHAR(5), col3 DATE)
    -> PARTITION BY HASH( YEAR(col3) )
    -> PARTITIONS 4;
Query OK, 0 rows affected (0.03 sec)

如果你在t1中插入一条col3值为‘2005-09-15’的记录，那么存储它的分区如下所示：

MOD(YEAR('2005-09-01'),4)
= MOD(2005,4)
= 1

MySQL 5.7还支持哈希分区的一种变体，称为线性哈希，它采用一种更复杂的算法来确定插入到分区表中的新行的位置。

每次插入或更新记录时，都会对用户提供的表达式进行计算。根据具体情况，还可以在删除记录时对其进行评估。

线性哈希分区
MySQL还支持线性哈希，它与常规哈希的不同之处在于，线性哈希利用线性2次幂算法，而常规哈希使用哈希函数值的模数。

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

mysql> 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;
Query OK, 0 rows affected (0.03 sec)

给定表达式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.当N >= num：
.设V = V / 2
.设N = N & （V – 1）

假设表t1使用线性哈希分区，有6个分区，使用下面的语句创建：

mysql> CREATE TABLE t1 (col1 INT, col2 CHAR(5), col3 DATE)
    -> PARTITION BY LINEAR HASH( YEAR(col3) )
    -> PARTITIONS 6;
Query OK, 0 rows affected (0.03 sec)

现在假设你想在t1中插入两条col3列值为‘2003-04-14’和‘1998-10-19’的记录。第一个分区的分区号确定如下：

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)

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

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)

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

列表列分区
MySQL 5.7支持列表列分区。这是列表分区的一个变体，允许使用多个列作为分区键，并允许非整数类型的数据类型用作分区列；你可以使用字 符串类型、DATE和DATETIME列。

使用列表列分区，用户可以为客户数据创建一个表，根据客户所在城市的名称，将一行分配给对应于这些区域的4个分区中的任何一个，如下所示：

mysql> 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')
    -> );
Query OK, 0 rows affected (0.04 sec)

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

也可以使用DATE和DATETIME列，如下面的例子所示，它们的名称和列与之前的customers_1表相同，但使用基于renew列的列表列分区将行存储在 4个分区中的一个中，具体取决于2010年2月客户账户计划更新的那一周：

mysql> 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')
    -> );
Query OK, 0 rows affected (0.02 sec)

这是可行的，但如果涉及的日期数量非常大，则定义和维护起来就很麻烦。在这种情况下，采用范围或范围列分区通常更实用。在本例中，由于 我们希望用作分区键的列是日期列，所以我们使用范围列分区，如下所示：

mysql> 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')
    -> );
Query OK, 0 rows affected (0.03 sec)

此外（与范围列分区一样），你可以在COLUMNS（）子句中使用多个列。