MySQL 分区表-优快云博客

本文深入探讨MySQL分区表的基础知识，包括分区表的概念、四种主要分区类型（RANGE、LIST、HASH、KEY）及其应用场景，以及如何管理和维护分区表。同时，文章分析了分区表与索引之间的关系，讨论了索引在分区表上的局限性。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

2019独角兽企业重金招聘Python工程师标准>>>

本文主要论述 MySQL 分区表的基础知识，主要参考于《高性能MySQL》一书以及 MySQL 5.7 Reference Manual - Partitioning

什么是分区表

对用户来说，分区表是一个独立的逻辑表，但是底层由多个物理子表组成。分区实际上是对一组底层表的句柄对象的封装。对分区表的请求，都会通过句柄对象转化成对存储引擎的接口调用，再变为对一个或是多个物理子表的操作。
分区对于 SQL 层来说，是一个完全封装底层实现的黑盒子，对应用透明。
不过正如之前提到的，分区表实际上是由多个物理子表组成的，所以分区表在文件系统中可以看到多个使用**#**分隔符命名的表文件。下图为文件系统中的分区表，一个按创建日期月份进行分区的销售表。
分区表示例

分区类型

MySQL 的分区类型大致可以分为4类，分别是：RANGE partitioning ， LIST partitioning ， HASH partitioning ， KEY partitioning 。

RANGE Partitioning

RANGE 分区是通过给定指定范围，在同一个分区内的数据都是在同一个范围内的。
给定的分区范围应该是连续且不能互相覆盖，使用语法 VALUES LESS THAN(expr) 定义分区的选择条件。
以下为 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 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)，
    PARTITION pm VALUES LESS THAN MAXVALUE
);

以上例子为根据列 store_id 进行分区。根据上述例子，选择分区的逻辑大致如下：

if store_id < 6:
    return p0
elif store_id < 11:
    return p1
elif store_id < 16:
    return p2
elif store_id < 21:
    return p3
else:
    return pm

这里需要注意的是，VALUES LESS THAN 中指定的值在判断时候是不包含的，也就是说用 < 进行判断。还有就是在进行分区时，一定要注意 else 情况，即有可能出现不在给出的分区选择范围内的值。以上述例子为例，就是当有 store_id = 22 数据的时候，如果没有进行适当的处理，将会报错。所以在进行 RANGE 分区时，要思考这种情况。一般情况下，就时在最后添加一个 MAXVALUE 分区，如下：

PARTITION pm VALUES LESS THAN MAXVALUE

根据日期分区

通常 RANGE 分区的使用都是针对于日期，根据日期划分范围。不过 RANGE 分区给定的值只接受整数类型，所以根据日期分区需要使用如下的方式：

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

对于类型 timestamp 只能使用如下形式（MySQL 5.7 版本）：

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

RANGE COLUMNS partitioning

MySQL 5.5 后引入了 COLUMNS Partitioning 特性，利用该特性可以有效解决在按日期进行 RANGE 分区时只接受整数类型的问题。

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 分区与 RANGE 的区别不仅仅如此，以下为相关的特性。
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][, ...]

columns_list 指的是一个或多个列名，而 value_list 则是用于判断范围的一个或多个值。两者的长度需要一样，且两者按顺序对应。
具体的示例:

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)

当进行选择分区时，也是像 RANGE 分区一样进行 LESS THAN 的对比判断，不同的是，RANGE COLUMNS 可以指定多个列，所以对比的单位为元组，即相关的值组成一个元组，进行判断从而选择分区。
元组的比较的规则大致如下，元组A与元组B的元素按顺序逐个比较，如果元组A小于元组B的元素的数量比元组B小于元组A的元素多，即元组A小于元组B，而如果两个元组的这个小于数相等，则根据元素的位置作决定，元组中元素的顺序约靠前，权重越大。
示例如下：

mysql> SELECT (0,25,50) < (10,20,100), (10,20,100) < (10,30,50);
+-------------------------+--------------------------+
| (0,25,50) < (10,20,100) | (10,20,100) < (10,30,50) |
+-------------------------+--------------------------+
|                       1 |                        1 |
+-------------------------+--------------------------+
1 row in set (0.00 sec)

mysql> SELECT (0,25,50) < (20,20,100), (20,20,100) < (10,30,50);
+-------------------------+--------------------------+
| (0,25,50) < (20,20,100) | (20,20,100) < (10,30,50) |
+-------------------------+--------------------------+
|                       1 |                        0 |
+-------------------------+--------------------------+
1 row in set (0.00 sec)

LIST Partitioning

LIST 分区与 RANGE 分区是有点类似的，都是通过给出的控制条件进行明确的分区，不同的是 LIST 分区的选择是基于集合的，定义多个集合，通过 Set::contains 的方式去选择分区。所以 LIST 分区的语法是 VALUES IN (value_list)，注意的是 value_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)
);

以上例子根据 store_id 进行分区，所以大致的选择分区逻辑如下：

if store_id in pNorth_list:
    return pNorth
elif store_id in pEast_list:
    return pEast
elif store_id in pWest_list:
    return pWest
elif store_id in pCentral_list:
    return pCentral
else:
    raise Exception()

通过分区逻辑的伪代码可以发现，当无法匹配到分区时，将会抛出异常，即 MySQL 将会报错。

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

LIST 分区与 RANGE 分区的另一个不同之处就在于，LIST 分区没有办法 catch all ，当有值无法选择分区时，将会报错。
这个问题选择分区时，也就是说在 INSERT 时会触发，而如果这个 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)

LIST COLUMNS partitioning

其实 LIST 分区的选择限制于整型是十分鸡肋的，像利用这种集合判断进行选择的逻辑，更多的是用在字符上。所以比起 LIST 分区，更好的选择是使用 LIST COLUMNS 分区，跟 RANGE COLUMNS 分区一样是 MySQL 5.5 后更新的特性。
以下为官方手册给出的示例：
有以下分区需求

Region	Cities
1	Oskarshamn, Högsby, Mönsterås
2	Vimmerby, Hultsfred, Västervik
3	Nässjö, Eksjö, Vetlanda
4	Uppvidinge, Alvesta, Växjo

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

HASH Partitioning

HASH 分区主要被用于能够明确确区数量，并将数据分布于这些分区中。HASH 分区基于指定列值或是表达式，然后通过计算选择分区。
使用 HASH 分区，需要在 CREATE TABLE 语句后跟着 PARTITION BY HASH (expr) ，expr 需要是整数类型的。而且最好是需要通过 PARTITIONS num 指定好分区的数量，num 需要是一个正数，分区的选择是基于分区的数量进行计算的。如果不指定分区的数量，默认只会创建一个分区 p0 ，即所有数据都会放在分区 p0 。

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;

使用表达式作为分区值：

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 分区是基于模计算进行分区选择，假设 N 为分区的标识，N 的结果通过如下计算获取：

N = MOD(expr, num)

有以下具体例子：

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

如果插入向表 t1 插入一条 col3 为 '2005-09-15' 的记录，分区的选择如下：

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

LINEAR HASH Partitioning

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

而选择分区的算法如下：
第一步：

V = POWER(2, CEILING(LOG(2, num)))

第二步：

N = F(column_list) & (V - 1)

第三步：

 While N >= num:
    Set V = V / 2
    Set N = N & (V - 1)

具体的选择分区示例如下：

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

向表 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)

那么如何选择 LINER HASH 分区和 HASH 分区呢？
LINER HASH 分区的优点在于添加、删除、合并和分割的速度更快，这在处理包含大量(tb)数据的表时非常有用。但是它的缺点就在于数据分布不大均匀（与 HASH 分区对比）。

KEY Partitioning

KEY 分区其实和 HASH 分区十分相似，除了 HASH 分区在选择分区上的依据是用户定义的表达式（准确来说是表达式返回的整数），而 KEY 分区在选择分区上依据的是提供的列的值的哈希值（用 MySQL 内置的哈希函数，或是说具有哈希作用的函数）。
KEY 分区的语法规则为 CREATE TABLE ... PARTITION BY KEY ，还有就是像 HASH 分区一样通过关键字 PARTITIONS 提供分区数，不然默认也是只有一个分区。
KEY 分区还有一个特性，提供给分区语句的参数可以是 0 个或者是多个。如果主键，用作分区的列必须是主键或是主键的一部分，如果没有逐渐，那么就是针对于 UNIQUE KEY 。而如果分区语句中没有指定列，那么默认将使用主键或是 UNIQUE KEY （如果主键不存在）。
示例如下：

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

LINER KEY Partitioning

特性和 LINER HASH 分区基本一致，区别在于 KEY 和 HASH 分区的区别。

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

管理分区

在日常使用中，对分区表的管理也是必不可少的。

Management of RANGE and LIST Partitions

RANGE 分区和 LIST 分区的在管理上的方式相似，所以这里一起论述这两种分区的管理方式。
对分区表进行管理时，会希望查看指定分区中由哪些数据，可以使用下列语句：

mysql> SELECT * FROM tr PARTITION (p2);
+------+-------------+------------+
| id   | name        | purchased  |
+------+-------------+------------+
|    2 | alarm clock | 1997-11-05 |
|   10 | lava lamp   | 1998-12-25 |
+------+-------------+------------+
2 rows in set (0.00 sec)

删除分区

有时我们会想将符合一定条件的记录进行删除，典型的一个例子就是想要删除存放时间已经很长，效用过期或是说没由用处的数据。针对这一场景，如果使用了 RANGE 分区针对记录的相关时间列进行分区的话，当想要删除过期时间时，只要丢弃掉指定的分区就行，当分区被删除时，在对应分区中的记录也随之删除。这个特性与前面提到的分区表由若干个物理子表组成的特性是相对应的，丢弃指定分区，其实就是丢弃掉指定的分区子表，那么在该子表上的记录也随之被丢弃。

mysql> ALTER TABLE tr DROP PARTITION p3;
Query OK, 0 rows affected (0.03 sec)

添加分区

有删除分区的需求，自然也会有添加分区的需求。相关示例如下：

ALTER TABLE members ADD PARTITION (PARTITION p3 VALUES LESS THAN (2010));

在添加分区上， RANGE 分区和 LIST 分区都有不同的限制。
对于 RANGE 分区，添加分区只能采用“尾端添加”的方式，也就是类似于 List::append 的方式，否则将无法成功添加分区。

CREATE TABLE members (
    id INT,
    fname VARCHAR(25),
    lname VARCHAR(25),
    dob DATE
)
PARTITION BY RANGE( YEAR(dob) ) (
    PARTITION p0 VALUES LESS THAN (1980),
    PARTITION p1 VALUES LESS THAN (1990),
    PARTITION p2 VALUES LESS THAN (2000)
);

mysql> ALTER TABLE members
     >     ADD PARTITION (
     >     PARTITION n VALUES LESS THAN (1970));
ERROR 1463 (HY000): VALUES LESS THAN value must be strictly »
   increasing for each partition

而 RANGE 分区一般都会有 catch-all 分区，即 LESS THAN (MAXVALUE) ，所以添加分区操作对 RANGE 分区来稍显鸡肋。

而对于 LIST 分区，它的限制在于，所添加的新分区，指定的集合中的元素不能和已经存在的分区的集合元素有重复。

CREATE TABLE tt (
    id INT,
    data INT
)
PARTITION BY LIST(data) (
    PARTITION p0 VALUES IN (5, 10, 15),
    PARTITION p1 VALUES IN (6, 12, 18)
);

mysql> ALTER TABLE tt ADD PARTITION 
     >     (PARTITION np VALUES IN (4, 8, 12));
ERROR 1465 (HY000): Multiple definition of same constant »
                    in list partitioning

上述例子报错的原因在于，新分区 np 所指定的集合元素中有 12 ，和已存在的分区 p1 中的集合元素重复了。

重新组织分区

上面提到， ADD PARTITION 对于 RANGE 分区来说是鸡肋的，但真的遇到需要对 RANGE 分区添加分区怎么办？这种情况下，就需要使用分区表的重新组织操作。
重新组织分区的语法规则如下：

ALTER TABLE tbl_name
    REORGANIZE PARTITION partition_list
    INTO (partition_definitions);

对于 RANGE 分区，重新组织分区的示例如下：

CREATE TABLE members (
    id INT,
    fname VARCHAR(25),
    lname VARCHAR(25),
    dob DATE
)
PARTITION BY RANGE( YEAR(dob) ) (
    PARTITION p0 VALUES LESS THAN (1980),
    PARTITION p1 VALUES LESS THAN (1990),
    PARTITION p2 VALUES LESS THAN (2000)
);

ALTER TABLE members
    REORGANIZE PARTITION p0 INTO (
        PARTITION n0 VALUES LESS THAN (1970),
        PARTITION n1 VALUES LESS THAN (1980)
);

利用重新组织分区的方法，可以做到添加分区的目的，而且没有 ADD PARTITION 的只能添加尾部分区的限制。
但是需要注意的是，对于 RANGE 分区的重新组织分区，不可以改变指定原分区的范围。有以下示例：

CREATE TABLE members (
    id INT,
    fname VARCHAR(25),
    lname VARCHAR(25),
    dob DATE
)
PARTITION BY RANGE( YEAR(dob) ) (
    PARTITION p0 VALUES LESS THAN (1980),
    PARTITION p1 VALUES LESS THAN (1990),
    PARTITION p2 VALUES LESS THAN (2000),
    PARTITION pm VALUES LESS THAN (MAXVALUE)
);

常见的分区方式，利用 VALUES LESS THAN (MAXVALUE) 做到 catch-all，而这时，需要添加新的年份 2001 年，使用重新组织分区实现：

ALTER TABLE members
    REORGANIZE PARTITION pm INTO (
        PARTITION p3 VALUES LESS THAN (2001)
);

这时将会报错，因为这种方式改变了原分区 pm 的范围，而原分区 pm 的范围为 2000~MAXVALUE ，如果上述语句执行成功，范围将变成 2000~2001 ，这是不允许的，使用的应该是以下语句：

ALTER TABLE members
    REORGANIZE PARTITION pm INTO (
        PARTITION p3 VALUES LESS THAN (2001),
        PARTITION pm VALUES LESS THAN (MAXVALUE)
);

而 LIST 分区可以利用重新组织分区做到集合元素的再划分。

PARTITION BY LIST(data) (
    PARTITION p0 VALUES IN (5, 10, 15),
    PARTITION p1 VALUES IN (6, 12, 18)
);

ALTER TABLE tt ADD PARTITION (PARTITION np VALUES IN (4, 8));
ALTER TABLE tt REORGANIZE PARTITION p1,np INTO (
    PARTITION p1 VALUES IN (6, 18),
    PARTITION np VALUES in (4, 8, 12)
);

当然也可以不用这么复杂的方式去改变分区，可以直接使用以下语句改变分区：

ALTER TABLE table
    PARTITION ...

这种方式甚至还可以改变分区的类型，如将 RANGE 分区改边为 HASH 分区，重新指定 RANGE 分区的各个分区的范围当然也不再话下。但是要记住分区表底层实现的原理，假如贸然利用这种方式改变整个分区，带来的问题是不言而喻的，特别是对于已经存储了大量记录的分区表。
所以最好的方式还是利用重新组织分区的方法，将分区“变化”涉及的范围尽量缩小。

Management of HASH and KEY Partitions

由于 HASH 分区和 KEY 分区的管理在大体上是一样的，所以这里也将这两类分区的管理放在一起论述。
HASH 、 KEY 分区与 RANGE 、 LIST 分区的一个区别在于，HASH 分区以及 KEY 分区是不允许利用 DROP PARTITION partition_name 的方式丢弃分区。
那么想减少分区的时候要怎么做呢？HASH 分区和 KEY 分区以合并的方式来实现这个需求，语法规则为：ALTER TABLE ... COALESCE PARTITION 。
具体示例如下：

CREATE TABLE clients (
    id INT,
    fname VARCHAR(30),
    lname VARCHAR(30),
    signed DATE
)
PARTITION BY HASH( MONTH(signed) )
PARTITIONS 12;

mysql> ALTER TABLE clients COALESCE PARTITION 4;
Query OK, 0 rows affected (0.02 sec)

上述示例将原先12个分区压缩成4个分区。KEY 分区的压缩的方法也大致如此。

而要注意的是，COALESCE 是合并的意思，所以如果是说想增加分区的数量，使用这个方法是行不通的。

mysql> ALTER TABLE clients COALESCE PARTITION 18;
ERROR 1478 (HY000): Cannot remove all partitions, use DROP TABLE instead

想要增加分区，应该使用如下语句：

ALTER TABLE clients ADD PARTITION PARTITIONS 6;

经过上述语句操作，原先示例中12个分区将会变成 18（12+6）个分区。

分区的维护以及信息查看

除了上面提到的分区的管理方法，还有对于分区的维护以及分区信息获取知识需要掌握。这里不做过多论述，具体可以参考以下内容：

索引与分区表

本节主要论述索引与分区表的一些相关知识。

索引的局限与分区的优势

有一个非常大的数据表，这个表包含了很多年的历史数据，数据按照时间排序。现在希望从这个表中查询出最近几个月的数据，包含的数据量也是不少的。那么应该如何尽可能的高效地查出想要的数据呢？
首先可以肯定，由于数据量巨大，不可能每次查询都去扫描全表。这时，会想到利用索引，避免扫描全表。但是在大数据量的表中，索引在空间和维护上的消耗也是巨大的，而且在利用搜索时会发现，数据不是按照想要的方式聚集，最终会导致一个查询产生成千上万个的随机 I/O 。为什么呢？思考一下索引的搜索机制，通过索引检索数据时，会先搜索索引，然后再根据索引扫描的结果回表，查询所有符合条件的记录，这个过程在查询大量数据时会产生大量随机 I/O 。除非使用索引时可以做到索引覆盖查询，不然再计算上维护索引的消耗（磁盘空间、I/O操作），代价是沉重的。
使用索引也好，扫描全表也好，都行不通，那么怎么办呢？这时可以选择将两者折中，使用分区表。
分区表在一定程度上像索引一样，在搜索时先找出了感兴趣的数据，排除丢无关数据，只是分区的粒度更大。找出目标数据所在的分区后，原先非常庞大的待查数据量已经减少了许多，现在可以在指定分区上做顺序扫描，也可以根据分区建索引，还可以尝试将数据缓存到内存，等等的手段检索指定数据，避免出现上述提到的问题。

索引在分区表上的局限

MySQL 对分区表的实现为对若干底层表进行了封装，那么意味着分区表的索引也是按照分区的子表定义的，即没有全局索引。
分区表在进行相关操作时，例如 SELECT 或是 INSERT ，第一步要做的是选择出数据所在的分区，然后再在对应的分区子表中进行操作。
如果定了一个普通索引，而索引的列也不是分区表达式中指定的列，在 WHERE 只是单纯的使用该索引列作为条件时，将会逐个扫描分区表，在分区较多且各个分区的数据量不是特别多的情况下，效果是十分不理想的。解决的方法就是 WHERE 加上分区列相关的过滤条件，这样就可以很快的选出分区，并使用索引在分区子表中进行检索。
引起上面的提到的问题原因，其实在于选择分区这个步骤上，分区表对于普通表来说，是需要多花一步选择出数据所在的分区子表，所以如果分区过于多，将会影响到分区表的执行效率。适当的设置一定数量的分区即可。

分区键，主键，唯一键

在进行分区时，会发现到，分区表达式中选择的列不是主键或是主键的一部分，如果没有设置主键就是唯一键或唯一键的一部分，将会报错。因为分区所选用的列必须使用主键或是主键一部分，没有主键的话，就是唯一键或是唯一键的一部分。
为什么有这个限制呢？我推断是因为上面提到的，分区表没有全局索引，索引按照分区的子表定义。如果可以使用非主键或是唯一索引的列去作为分区键，将有以下问题：
试想以下场景，有一张用户表，使用 user_id 作为主键，然后假设利用 create_time 列作为分区键，但 create_time 不是主键的一部分，记录将根据 create_time 进行分区。那么问题来了，如何判断 user_id 是唯一的呢？因为分区表底层是若干个子表，索引按照子表定义，想要确保作为主键的 user_id 是唯一的，将需要扫描所有子表。当然还有的就是无法直接通过完整主键定位记录等。
分区表不作出上述提到的限制了，将会使主键或唯一键失去它们的意义。

转载于:https://my.oschina.net/bingzhong/blog/2003520