All You Need to Know About MySQL Partitions (Part 1)

database

Publish Date: 2020-03-24

Word Count: 5.7k

Read Times: 24 Min

什么是分区？为什么要分区？该怎么分区？分区有什么好处？使用分区应该注意什么？通过对MySQL文档的梳理，在这篇文章以及Part 2中详细介绍一下关于MySQL分区的方方面面。

1. Overview of Paritioning in MySQL

数据库作为系统的重要部分，承担着数据的存储任务。随着业务的不断扩展，数据也可能疯狂增长。这个时候对于数据库的访问可能会有一些瓶颈。为了减小表的规模，可以将一张大表分成几个小的部分。一种方式就是分表，将一张表分成一系列表；另一种就是分区。

分区也是将一张大表分成几个小的部分，但和分表不同的是，分区仍然是一张表，只不过在一张表下面有一些小的分区：

partition就是水平分区

那么分区有什么好处呢？

分区使得一张表可以存储的数据容量增大；
无用的数据通过删除分区的方式可以很方便地删除；
同样，对于新增数据，也可以很方便地添加分区来存储新数据；
使用分区之后，查询可以通过排除不可能的分区来减少待筛选数据的规模，减少查询时间。

总之，对于一些应用场景来说，使用分区可以给系统带来很大的好处。

2. Partitioning Types

2.1 Overview

MySQL中的分区就是水平分区。为了将一张大表分成几个分区，首先就应该确定一个分区的方式，即对于一条记录来说，如何确定这条记录放在哪个分区。在MySQL中，有下面几种分区方式。

2.2 RANGE Partitioning

Range分区，是根据指定字段的取值范围划定分区的，同一个分区包含同一个范围内的数据。分区范围应该是连续的并且不能有重合，这是显而易见的限制。如果不连续，那么就有可能有的记录找不到对应的分区；而有重叠的话，可能一条记录对应多个分区。

2.2.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来区分分店，取值范围是[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
)
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)
);

也就是说，分店ID是1到5的在p0分区，分店ID是6到10的在p1分区，依次类推。

对于一个给定的记录，是可以很容易确定对应的分区的。比如(72, 'Mitchell', 'Wilson', '1998-06-25', NULL, 13)，分店ID是13，根据规则这条记录就在p2分区里。

但是现在有个问题，如果又开了一家分店，分店ID是21，那么新分店的员工记录应该在哪个分区呢？

按照上面的定义，这样会出错。因为MySQL找不到对应的分区。

为了解决这个问题，可以使用MAXVALUE：

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
);

这样，所有分店ID大于15的记录都会被放在p3分区里。

不过，随着大于15的记录数越来越多，这样的分区方式可能会导致记录不均匀。可以通过ALTER TABLE来对表重新划分分区。

2.2.2 更多的例子

除此之外，还有其他的分区方式。比如，根据员工离职的年份来进行分区：

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
);

这里使用YEAR()函数将一个DATE类型的数据转换成数字，然后根据范围进行分区。

还可以对一个TIMESTAMP类型的字段进行分区，使用UNIX_TIMESTAMP()函数：

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)
);

2.2.3 Range分区的适用场景

Range分区适用于下面的场景：

需要时不时地删除旧数据。比如如果使用上面根据离职年份分区，想要删除所有1991年前离职的员工，可以直接ALTER TABLE employees DROP PARTITION p0;。这比使用DELETE有效多了。
想使用日期或时间类型的字段，以及以其他方式递增的类型的字段。比如各种统计数据，每天一次。
经常对分区字段进行查询。比如经常就store_id字段进行查询，如果使用分区的话，MySQL会快速地知道需要对哪些分区的数据进行查询。这在有很多数据的表中很有用。

需要注意的是，Range分区对范围的划分是基于数字类型的，其它类型的范围不支持（比如字符串，也可以有一个范围）。

2.3 LIST Partitioning

List分区和Range分区有很多相似的地方。和Range分区一样，每一个List分区必须显式定义出来。两者的主要不同在于，Range分区是根据一个连续且不重叠的范围进行划分的，而List分区是根据一个可选集合划分的，不需要连续，但需要不重叠。

还以上面员工的例子为例：

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
);

假如一共有20家连锁店，按区位分布如下：

Region	Store ID Numbers
North	3, 5, 6, 9, 17
East	1, 2, 10, 11, 19, 20
West	4, 12, 13, 14, 18
Central	7, 8, 15, 16

这样如果想把在同一个区位的所有分店的员工记录放在一个分区里的话，使用Range分区是实现不了的，因为分区的定义是不连续的。不过可以使用List分区：

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)
);

这样就可以将所有在一个区位的所有记录放在一个分区里了，如果想要删除一个区位下的所有记录的话，就可以直接使用ALTER TABLE employees TRUNCATE PARTITION pWest来删除。

如果插入一条store_id不在定义中的记录的话，那么插入就会失败。同时，如果在一条INSERT语句中插入多条记录，其中有一条记录的store_id不在定义中的话，那么所有的插入都失败，即使其他的合法。

为了让合法的能成功，可以使用IGNORE：

INSERT IGNORE INTO h2 VALUES (2, 5), (6, 10), (7, 5), (3, 1), (1, 9);

List分区使用的分区字段需要是数字类型或者能通过函数转换成数字类型，分区定义中的可选值不能是别的类型（比如字符串）。

2.4 COLUMNS Partitioning

Columns分区是Range分区和List分区的变型。Range分区和List分区使用一个字段的取值来决定将数据存在哪个分区，同时字段的类型需要是数字类型的（或能转换成数字类型的比如日期和时间）。但是Columns分区使用多个字段，来决定记录放在哪个分区中。

同时，字段的类型也不局限于数字类型，还可以使用字符串类型：

所有的数字类型：TINYINT, SMALLINT, MEDIUMINT, INT(INTEGER)和BIGINT（和Range分区与List分区一样），不过对DECIMAL和FLOAT类型不支持；
日期和时间类型：DATE和DATETIME；
字符串类型：CHAR, VARCHAR, BINARY, VARBINARY，不过对TEXT和BLOB不支持。

2.4.1 RANGE COLUMNS Partitioning

Range columns分区和Range分区类似，支持使用多个字段确定分区，同时除了整数类型字段外还支持字符串类型。此外，两者的另一个区别在于，Range分区可以对字段使用一个表达式，比如year(birthday)，而Range columns分区不支持，只能使用字段名。

使用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][, ...]

定义中使用column_list表示分区定义使用的所有字段列表，value_list列出了每个分区对应字段的取值，对于每一个分区，都要有一个value_list。

同时column_list的元素个数需要和value_list一致，这样才能对每个字段都确定一个取值的范围；而且，column_list中的字段顺序要和value_list中的取值一一对应。

和Range分区一样，这里我们也可以使用MAXVALUE来标识最大值。

一个简单的例子：

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)
);

这里，表rcx有四个字段，使用了其中的三个来定义分区，在分区定义中每个对每个字段都确定了范围。

由于使用多个字段来确定分区，所以Range columns决定一条记录的具体分区和Range分区不太一样。在Range分区中，表达式得到的结果如果在分区范围的边界上，那么这条记录是不会放在对应的分区里的（比如PARTITION p0 LESS THAN (5)，如果对应的值刚好是5的话，就不会放在p0里了，会放在下一个分区中）。不过，在Range columns分区中就不太一样了，因为Range columns分区是对多元组（tuple）进行比较的，而不是简单的标量值。

对于一个下面定义的Range分区表来说：

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

如果我们插入三条记录：(5, 10), (5, 11), (5, 12)，对具体分区起作用的是字段a，所以只比较a的值就可以了。由于这三天记录的a值都大于5，所以这三条记录都放在p1中。

我们可以使用下面的查询语句来查看一张表中每个分区的记录数：

SELECT PARTITION_NAME,TABLE_ROWS FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_NAME = 'r1';

好的，让我们来看看下面的Range columns的例子：

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)
);

使用两个字段a和b来确定分区。这时，对于给定的记录来说，比较的就不是一个简单的标量值了，而是一个二元组。

同样，如果我们插入这样的三条记录：(5, 10), (5, 11), (5, 12)，那么每条记录会放在哪个分区呢？

首先可以使用MySQL的client来比较一下这些二元组：

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

我们的分区定义范围的界线是(5, 12)，所以需要将所有待插入的记录去和这个二元组比较。结果如下：

+-----------------+-----------------+-----------------+
| (5,10) < (5,12) | (5,11) < (5,12) | (5,12) < (5,12) |
+-----------------+-----------------+-----------------+
|               1 |               1 |               0 |
+-----------------+-----------------+-----------------+

可以看出，前两条记录会放在p0中，后一条放在p1中。这个例子展示了Range columns分区和Range分区对于确定分区的方式的不同。

不过，如果Range columns分区只使用一个字段，那么分区的确定方式就和Range分区一致了。

不过为啥只有一个字段来决定分区不使用Range分区而使用Range columns分区呢？这是因为，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来创建分区：

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分区的范围界线中，一个或多个字段的界限值可以是一样的，只要能保证分区定义的多元组是严格递增的：

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)
 );

这两个表的分区定义，都是合法的。

来看看这个定义：

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)
 );

乍一看好像不合法，但确实是合法的。我们可以对这些界线多元组进行比较：

select (0,25,50)<(10,20,100), (10,20,100)<(10,30,50);

结果都为1。说明这三个三元组是严格递增的，所以可以在定义中使用。

但是下面这个就不合法了：

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)
);

同样可是使用上面的判断方式，结果(20,20,100)并不小于(10,30,50)。

那MySQL到底是怎么比较多元组的呢？我猜测是，按照多元组从左到右的顺序进行，比如(a1,a2,a3)和(b1,b2,b3)，先比较下标是1的，如果a1<b1，那么(a1,a2,a3) < (b1,b2,b3)；否则比较下标是2的，一次类推。

不知道对不对，欢迎打脸。

2.4.2 LIST COLUMNS Partitioning

List columns分区与List分区的区别，和Range columns分区与Range分区的区别是一样的。即，List columns分区可以使用多个字段，也可以使用字符串类型的字段。比如下面的一个分区定义：

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')
);

将同一个区位的几个城市的记录放在一个分区中，这样的定义List分区是实现不了的。

2.5 HASH Partitioning

前面介绍的Range分区和List分区，以及两者的变型，需要我们手动指定分区的规则。而在Hash分区中，我们只需要指定Hash操作的字段以及划分的分区数就可以了，MySQL会自动计算每条记录所属的分区。

2.5.1 普通HASH Partitioning

Hash分区的一个主要用途是将所有的数据均匀分配到给定数量的分区中，以此来将一个表可容纳的记录数增加。

一个简单的例子：

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;

这里使用store_id字段计算Hash，一共划分成四个分区。如果不指定PARTITIONS 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( YEAR(hired) )
PARTITIONS 4;

使用的字段需要是整数类型，或者表达式返回一个整数类型。

Hash分区使用mod来计算。比如上面这个例子，如果一条记录的YEAR(hired)值是2020，那么对应的分区就是：

MOD(2020, 4) = 0

2.5.2 LINER HASH Partitioning

Liner Hash分区和Hash分区的不同在于Hash的计算方式，Liner Hash分区使用线性二次幂算法，而Hash分区使用的是取模。

两者定义的区别也很简单：

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;

给定一条记录，可以通过下面的算法得到这条记录存储在哪个分区（num表示总分区数，N表示具体的分区）。

找到下一个大于大于num2次幂的数记作V；
令N = F(column_list) & (V-1)；
while N>=num:
- 令V = V/2；
- 令N = N & (V-1)。

一个简单的例子：

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

分区数num=6，加入两条待插入记录的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)

相比Hash分区，Liner Hash分区的优点在于，添加、删除、合并和分裂分区很快，尤其是数据量很大的时候。缺点在于，它不像Hash分区那样数据分布均匀。

2.6 KEY Partitioning

Key分区和Hash分区类似，只不过Hash分区是用户自定义了一个表达式，而Key分区是MySQL自己实现的表达式，在NDB存储引擎中，使用的是md5()。

一个简单的例子：

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

这里KEY()没有指定具体的值，那么就使用主键，即id。如果没有主键有唯一键的话，就使用唯一键：

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

上面两个例子使用的都是字段id。

不过，如果唯一键没有指定为NOT NULL，就会定义失败。

也可以使用字符串类型：

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

使用Key分区，那么定义中使用的键就不能删除了，不然会出错。

像Hash分区一样，Key分区也可以指定Liner：

CREATE TABLE tk (
    col1 INT NOT NULL,
    col2 CHAR(5),
    col3 DATE
)
PARTITION BY LINEAR KEY (col1)
PARTITIONS 3;

这样，Liner Key分区就和Liner Hash分区类似了，优缺点也一样。

2.7 子分区：Subpartitioning

子分区也叫做复合分区，就是将分区表的分区继续分区。比如：

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
    );

这个表使用的是Range分区，一共有三个Range分区，其中对于每个分区又使用Hash分区方式分成两个子分区，这样一共有6个子分区。

只有Range分区和List分区能够继续划分子分区，并且子分区的分区方式是Hash分区或Key分区。

子分区定义如果使用Hash分区或Key分区的话，那么具体的格式和Hash分区与Key分区类似，不同在于Key分区，子分区如果使用Key分区的话需要指定使用的键。

在定义中也可以对每个子分区定义子分区的名字：

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
        )
    );

不过要注意，如果要定义的话，需要把所有的子分区的名字都定义了，同时不能有重复的子分区名字，即使两个子分区不在同一个分区下。

2.8 MySQL分区如何处理NULL值

分区中可以使用NULL值，在MySQL中，NULL值小于任何一个非NULL值。在不同的分区方式中，对于NULL值的处理是不同的。

2.8.1 RANGE分区

如果决定分区的字段可以为NULL值，那么对于这样的记录存在最小的那个分区里。比如：

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
);

如果插入一条c1=null的记录，那么这条记录存在p0中。

对于使用MySQL函数的分区，也是这么处理NULL值的。比如YEAR(NULL)=NULL，然后存在最小的那个分区里。

2.8.2 LIST分区

List分区可以接受NULL值，只要分区定义中给定了NULL值存储的分区即可。如果这样：

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)
);

那么插入一条c1=null的记录不会成功。如果这样：

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)
);

那么对于所有c1=null的记录都将存在p3中。

2.8.3 HASH分区和KEY分区

Hash分区和Key分区对于NULL的处理是将NULL当做0。比如：

CREATE TABLE th (
    c1 INT,
    c2 VARCHAR(20)
)
PARTITION BY HASH(c1)
PARTITIONS 2;

那么对于c1=null的记录将放在分区ID是0的分区里，和c1=0，c1=2效果一样。对于Key分区也是一样。

关于MySQL分区其余的部分，参考Part 2。

mysql partition

All You Need to Know About MySQL Partitions (Part 2)

在Part 1中，已经详细介绍了MySQL中的几种分区方式。在这里，详细介绍一下分区的各种操作。同样，这篇文章是对官方文档的梳理。 3. 分区管理对于一个一分区的表来说，分区管理包括添加分区、删除分区、合并分区、拆分分区以及，改变分区方

2020-03-24 database

mysql partition

Roman Empire

这篇文章是根据假期看的《罗马人的故事》记录的罗马流水线的第二部分：罗马帝国。罗马帝国从27BC开始到467AD西罗马帝国灭亡共500年的历史，不包括东罗马帝国（拜占庭帝国）。 1. 尤利乌斯·克劳狄乌斯王朝（27BC-68AD）凯撒绘制

2020-03-04 reading

history rome