关注我「程序猿集锦」，获取更多分享。

• 分区表的管理
• range和list分区
• 添加分区
• 删除分区
• 修改分区
• 合并或拆分分区
• hash和key分区
• 添加分区
• 删除分区
• 修改分区
• 合并或拆分分区
• 分区的维护
• 重建分区
• 优化分区
• 分析分区
• 检查分区
• 修复分区
• 分区表的注意事项
• 总结

关于分区表的的分类和示例，请参考上篇文章。

分区表的管理

range和list分区

对于range和list两种类型的分区表的管理，接下来的各种演示示例：添加分区、删除分区、修改分区、合并拆分分区的各种操作，都是基于下面的这个range范围分区表来做演示，list类型的分区操作类似。

这个range范围分区表的建表语句和初始化数据如下所示：

/*创建range范围分区表*/
drop table if exists range_partition_table;
create table range_partition_table(
	id int auto_increment,
  code varchar(16),
  create_date date,
  primary key(id, create_date)
) partition by range columns(create_date) (
	partition p1 values less than('2020-02-01'), /*p1分区不包含2020-02-01这一天的数据*/
  partition p2 values less than('2020-03-01'),
  partition p3 values less than('2020-04-01'),
  partition p4 values less than('2020-05-01'),
  partition px values less than maxvalue
);

/*插入测试数据*/
insert into range_partition_table(id, code, create_date) values (null, 'A', '2020-01-04');
insert into range_partition_table(id, code, create_date) values (null, 'B', '2020-01-31');
insert into range_partition_table(id, code, create_date) values (null, 'C', '2020-02-01');
insert into range_partition_table(id, code, create_date) values (null, 'D', '2020-02-29');
insert into range_partition_table(id, code, create_date) values (null, 'E', '2020-03-01');
insert into range_partition_table(id, code, create_date) values (null, 'F', '2020-03-31');
insert into range_partition_table(id, code, create_date) values (null, 'G', '2020-04-01');
insert into range_partition_table(id, code, create_date) values (null, 'H', '2020-04-30');
insert into range_partition_table(id, code, create_date) values (null, 'I', '2020-05-01');
insert into range_partition_table(id, code, create_date) values (null, 'J', '2020-05-31');
insert into range_partition_table(id, code, create_date) values (null, 'K', '2020-06-01');
insert into range_partition_table(id, code, create_date) values (null, 'L', '2020-06-30');
insert into range_partition_table(id, code, create_date) values (null, 'M', '2020-07-01');

原始range分区表的详细信息如下图所示：

添加分区

这里管理分区部分，我们都使用range范围分区来做演示，

如果要添加一个分区，则只需如下的SQL语句

alter table range_partition_table add partition (partition p5 values less than ('2020-06-01'));

在添加分区的时候，需要注意如果你的分区表类型是range范围分区，并且制定了最后一个范围分区的范围边界为maxvalue，那么你不能直接在这样的一个range范围分区表上增加分区，否则会出现如下的错误：

mysql> alter table range_partition_table add partition (partition p5 values less than ('2020-06-01'));
ERROR 1493 (HY000): VALUES LESS THAN value must be strictly increasing for each partition

对于这样的情况，你只能针对倒数第二个分区拆分为一个新的分区。或者是删除掉最后一个分区。然后执行add partition的操作，但是这样将会丢失最后一个分区的数据，所以不建议这么做。最好的办法是在创建range范围分区的时候，不要使用maxvalue作为最后一个分区的边界值，当我们发现分区不够的时候，就可以直接使用add partition语句增加分区了。如下所示：

mysql> /*删除掉最后一个包含maxvalue的范围分区后，再添加分区是可以成功的。*/
mysql> alter table range_partition_table drop partition px;
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> alter table range_partition_table add partition (partition p5 values less than ('2020-06-01'));
Query OK, 0 rows affected (0.04 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> /*建议不使用maxvalue设置最后一个分区的value边界值。或者使用如下的方式来拆分租后一个分区。*/
mysql> alter table range_partition_table reorganize partition px into (
    ->   partition p6 values less than ('2020-06-01'),
    ->   partition px values less than maxvalue
    -> );
Query OK, 0 rows affected (0.08 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql>

对于列表分区来说，可以就没有range范围分区遇到的这个问题了，可以直接使用add partition语句进行添加分区的操作。只要添加的分区中的value，不和已经存在的分区有任何交集就可以。

删除分区

如果想要删除一个或多个分区，可以使用如下的语句：

alter table range_partition_table drop partition p1; /*删除一个分区，数据和分区都会被干掉*/
alter table range_partition_table drop partition p1, p2; /*删除多个分区，数据和分区都会被干掉*/

当你删除一个分区的时候，这个分区内所存储的数据也会被一并删除，这对一个普通的非分区表执行drop table xx的效果是一样的。所以，在删除分区之前一定要想清楚是否确定要删除这个分区。同时当你想删除某个分区的时候，你还需要对这个分区表有drop的权限才可以。

删除分区的语句仅适用于range分区和list分区，不适用于hash分区和key分区。如果尝试对一个hash分区使用drop partition语句的操作，会出现如下错误信息：

mysql> alter table hash_partition_table drop partition p1;
ERROR 1512 (HY000): DROP PARTITION can only be used on RANGE/LIST partitions
mysql> 

如果你只是想删除数据，而不想删除分区，则只需如下的truncate partition语句：

alter table range_partition_table truncate partition p1; /*删除一个分区内的数据，分区会被保留*/

如果你要删除分区表中所有的数据，同时保留分区信息不被删除，使用如下的truncate table语句。

truncate table range_partition_table; /*把表中所有数据都干掉，分区信息会被保留*/

修改分区键

如果原先的涉及到分区表不够好，想重新选择一个新的分区键，或者是我们原先使用的分区类型不够好，先换一个分区类型。我们该如何操作？最简单方式并不是我们首先想到的重新创建一个新的分区表，然后后把数据导入到新的分区表中，再把旧表删除掉，在把新的分区表的名称改为旧的分区表的名称。这种方式当然可以，但是除了这种方式之外，我们还可以使用下面的语句来完成分区表的修改。

可以使用如下SQL语句把分区键进行更改，这样数据会根据新的分区键还有分区规则重新分布：

alter table range_partition_table
partition by range columns(id)(
	partition p1 values less than (5),
  partition p2 values less than (10),
  partition px values less than maxvalue
);

上述SQL执行完成后，表的分区结构如下所示：

基于修改后的SQL语句，我们还可以使用如下的SQL对分区表的类型进行修改，如下的SQL语句是把一个range范围分区表改为一个hash分区表。

alter table range_partition_table
partition by hash(id)
partitions 4;

上述的SQL执行完成后，表的分区信息如下所示：

在上面的实验中，我们把一个range分区的表，显示修改了它的分区键，接着又把这个range分区表改为了hash分区表。可以发现数据是不会丢失的，并且可以根据我们修改的时候指定的分区键和分区规则自动的对原先的range分区表中的数据进行重新分区存储。

合并或拆分分区

我们可能会有这样的需求：想把已经存在的两个分区合并为一个分区，或者是想把一个已经存在的分区拆分为两个分区。此时我们就不能直接使用add parition和drop partition两种命令的组合来实现，因为在我们执行drop partition的时候，会把数据也删除掉了。此时我们使用alter table xx reorganize partition语句来实现这样的需求。

我们下面的拆分分区和合并分区的操作都将基于前面我们提到的range范围分区表来演示。

把两个分区合并为一个分区的SQL语句如下，此时分区合并，并且数据不会丢失。两个分区内的数据会根据分区的规则，自动的合并在一个分区中。在合并分区的时候，不能把间隔的两个分区合并为一个分区。比如把p1和p3合并为一个分区，因为中间还有一个分区p2。所以不能间隔着分区操作。同时，使用reorganize命令不能把分区类型修改掉，比如把range分区修改为list分区。

alter table range_partition_table reorganize partition p1,p2 into (
  partition p1_and_p2 values less than('2020-03-01')
);

把一个分区拆分为两个分区的语句如下：

alter table range_partition_table reorganize partition p4 into (
  partition p4_1 values less than('2020-04-15'),
  partition p4_2 values less than('2020-05-01')
);

经过上面两步合并和拆分分区的操作之后，结果如下。我们可以看到分区p1和p2已经合并为一个分区p1_and_p2。分区p4也被拆分成了两个分区：p4_1和p4_2。但是每一个分区的数据量重下面的截图中可以看到还有些不对，但是我们把分区表执行一下分析命令之后就可以看到正确的统计结果了。

执行如下命令，再次查看修改后的分区详细信息如下：

analyze table range_partition_table;

hash和key分区

我们基于下面的这个hash分区表来做演示

drop table if exists hash_partition_table;
create table hash_partition_table(
	id int auto_increment,
  store_code int,
  create_date date,
  primary key(id, store_code)
) partition by hash(store_code)
partitions 4;

对hash分区表，插入测试数据如下所示：

insert into hash_partition_table(id, store_code, create_date) values(null, 10, now());
insert into hash_partition_table(id, store_code, create_date) values(null, 11, now());
insert into hash_partition_table(id, store_code, create_date) values(null, 12, now());
insert into hash_partition_table(id, store_code, create_date) values(null, 13, now());

查询hash分区后的详细信息如下：

添加分区

把原先4个hash分区的分区表，增加2个hash分区，使用如下的SQL语句：

alter table hash_partition_table add partition partitions 2;

添加2个hash分区后的结果如下所示，我们可以发现原的4个分区下载变成了6个分区

删除分区

基于上面添加后的分区表，把现在6个hash分区的分区表，减少4个hash分区，调整为2个分区，使用如下的SQL：

alter table hash_partition_table coalesce partition 4;

最后结果如下所示，可以看到分区已经调整为2个分区，并且数据也没有丢失，这就是hash、key分区和range、list分区一个比较明显的区别：删除分区后，hash、key分区中的数据不会丢失，而range、list分区中的数据会随着分区的删除而一起别删除。

hash分区和key分区的删除分区的操作，其实就是收缩分区的一个操作。原先的数据均匀的分布在n个分区中，如果要删除几个分区，就会触发数据重新分布的操作，把数据重新根据分区的规则再次均匀的分布到新的分区中。

修改分区

hash分区和key分区的修改分区，前面我们说的增加分区和删除分区的操作也是一种修改的行为，除此之外呢，我们可以使用如下的命令把hash分区修改为key分区，或者把key分区修改为hash分区，或者是把hash分区的分区键进行修改也是可以的。下面分别演示一下：

/*查看原先的hash分区表的结构*/
mysql> show create table hash_partition_table\G
*************************** 1. row ***************************
       Table: hash_partition_table
Create Table: CREATE TABLE `hash_partition_table` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `store_code` int(11) NOT NULL,
  `create_date` date DEFAULT NULL,
  PRIMARY KEY (`id`,`store_code`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
/*!50100 PARTITION BY HASH (store_code)
PARTITIONS 4 */
1 row in set (0.01 sec)

/*把原先的hash分区修改key分区，同时把分区键从store_code改为id，还修改的了分区的数目由4变成2。*/
mysql> alter table hash_partition_table partition by key(id) partitions 2;
Query OK, 4 rows affected (0.09 sec)
Records: 4  Duplicates: 0  Warnings: 0

/*查看修改后的结果，从下面的输出可以看到，已经从hash(store_code)的哈希分区改为key(id)的按键分区，
分区数目也变成了2个。*/
mysql> show create table hash_partition_table\G
*************************** 1. row ***************************
       Table: hash_partition_table
Create Table: CREATE TABLE `hash_partition_table` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `store_code` int(11) NOT NULL,
  `create_date` date DEFAULT NULL,
  PRIMARY KEY (`id`,`store_code`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb4
/*!50100 PARTITION BY KEY (id)
PARTITIONS 2 */
1 row in set (0.01 sec)

/*把分区类型从key分区再改回hash分区，同时修改了分区的数目有2个改为3个，但是没有修改分区键的列。*/
mysql> alter table hash_partition_table partition by hash(id) partitions 3;
Query OK, 4 rows affected (0.05 sec)
Records: 4  Duplicates: 0  Warnings: 0

/*查看修改后的结果，从下面的输出可以看到，已经从key(id)按键分区改为hash(id)哈希分区。
分区的数目也从2改为了3。*/
mysql> show create table hash_partition_table\G
*************************** 1. row ***************************
       Table: hash_partition_table
Create Table: CREATE TABLE `hash_partition_table` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `store_code` int(11) NOT NULL,
  `create_date` date DEFAULT NULL,
  PRIMARY KEY (`id`,`store_code`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb4
/*!50100 PARTITION BY HASH (id)
PARTITIONS 3 */
1 row in set (0.01 sec)

/*修改分区键，把分区键从id改为store_code，其他不变。*/
mysql> alter table hash_partition_table partition by hash(store_code) partitions 3;
Query OK, 4 rows affected (0.16 sec)
Records: 4  Duplicates: 0  Warnings: 0

/*查看修改后的结果，从下面的输出可以看到，分区键已经从hash(id)改为hash(store_code)。*/
mysql> show create table hash_partition_table\G
*************************** 1. row ***************************
       Table: hash_partition_table
Create Table: CREATE TABLE `hash_partition_table` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `store_code` int(11) NOT NULL,
  `create_date` date DEFAULT NULL,
  PRIMARY KEY (`id`,`store_code`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb4
/*!50100 PARTITION BY HASH (store_code)
PARTITIONS 3 */
1 row in set (0.01 sec)

mysql>

合并或拆分分区

hash分区和key分区，不涉及到拆分和合并分区的操作。它们的分区规则和特点决定了它们没有办法拆分或合并。只有增加和收缩的分区的操作。

收缩、减少分区的操作就相当于是合并分区的操作，比如原先有10个hash分区，现在减少到5个分区，此时的收缩分区的操作就相当于是合并分区的操作。

增加分区的操作就相当于是拆分分区的操作。比如原先有5个hash分区，现在增加到10个分区，此时的增加分区的操作就相当于是拆分分区的操作。

分区的维护

分析分区、检查分区、修复分区等操作。

分区的维护涉及到加检查分区、分析分区、修复分区等操作。在分区中的数不断地进行增删改查的时候，可能会造成数据空洞、磁盘碎片。定期地进行分区的维护工作是有必要的。对应提高分区表的性能也是很重要的。

重建分区

使用下面的语句重建分区。重建分区等同于先删除分区，然后重新创建分区，然后再插入分区中的数据。重建分区对于整理磁盘碎片很有效果。

alter table hash_partition_table rebuild partition p0, p1;

优化分区

使用下面的语句来优化分区。如果分区中的数据有大量的删除、修改的操作，执行这个命令可以有效的回收没有被使用的空间，并整理分区中的数据。

alter table hash_partition_table optimize partition p0, p1;

使用optimize partition的命令等同于以此执行了check partition、analyze partition、repair partition三个操作。

分析分区

使用下面的语句来分析分区，分析分区用于统计分区中信息，便于在选择执行计划的时候可以选择正确有效的执行计划。

alter table hash_partition_table analyze partition p0, p1;

检查分区

使用下面的语句来检查分区，当分区有异常问题的时候，使用这个命令可以检查出分区是否有问题。

alter table hash_partition_table check partition p0, p1;

修复分区

使用下面的语句来修复分区，这适用于分区损坏的情况。当检查出分区中有异常问题的时候，可以使用repair partition的命令来尝试修复分区。

alter table hash_partition_table repair partition p0, p1;

分区表的注意事项

分区表在使用的时候有很多注意事项，下面是我们需要注意的几点：

• 子分区只有range范围分区和list列表分区才支持有子分区，hash分区和key分区不支持子分区。
• range分区的每个分区包含那些分区表达式的值位于一个给定的连续区间内的行。这些区间要连续且不能相互重叠。
• list分区只支持整形字段或返回整形数的表达式，每个分区列表里的值列表必须整数。
• hash分区类型只支持整形字段或返回整形数的表达式。
• key类型只支持列名形式（可一个或多个列名），不支持表达式。
• 若表有primary key或unique key，则分区表的分区列必须包含在primary key或unique key列表里，这是为了确保主键的效率，否则同一主键区的东西一个在Ａ分区，一个在Ｂ分区，显然会比较麻烦。
• 一个分区表的所有分区必须使用相同的存储引擎，要和表的存储引擎要一致。如果有子分区，子分区的存储引擎也要和表的存储引擎一致。
• hash哈希分区和key按键分区，删除一个分区数据不会丢失，原先存储在被删除的分区中的数据会自动根据新的分区数重新均匀的分布到剩余保留的分区中。
  range范围分区和list列表分区，删除分区之后，相应分区数据会一并被删除。
• 一个分区表最多支持1024个分区，包括子分区的数目在内，总数不能超过1024。这个分区的数目应该可以支持大多数业务需求。
• innodb存储引擎的分区表，不能引用其他表的字段作为自己的外键，也不能别其他表引用自己表中的字段。即为：分区和外键的支持二选一。
• InnoDB不支持使用多个磁盘作为子分区，目前只有MyISAM支持。
• 慎重选择分区键，避免跨分区扫描，要结合实际的业务来觉得分区键。任何脱落的业务来谈分区键都是无意义的。

innodb存储的分区表，不支持使用optimize partition优化分区的命令，需要使用rebuild partiton和analyze partition来代替。如下所示：

总结

如果遇到性能上的瓶颈，我们可以考虑使用分区表的方式来尝试优化，因为这样在底层存储的时候是分库存储，但是在整体的逻辑上它还是属于同一个表，这样就避免了真正的物理上分库分表的方式来解决性能的问题。但是分区表的方式如果不是分磁盘存储各个分区，在性能上提升是有限的。

如果项目中已经引入的数据库中间件，建议还是直接使用物理层面上的分库分表。如果还没有引入数据库中间件，可以尝试使用分区表的方式来解决性能的瓶颈。

注意：在选择使用哪种分区、选择分区键的时候要根据实际的业务结合各种分区方式和分区的条件限制来仔细衡量，如果选择不当，性能不仅没有提升反而会有下降的现象。