MySQL分区表

1，MySQL分区表作用

当单表的数据量较大，需要进行定期的数据归档删除时，或者提高查询性能，都可以采用分区；

2，分区表设置

分区的种类

range分区
list分区
hash分区
key分区

range分区

区间分区，常用与跟日期相关的，例如：

create table table_demo (
id int,
create_date datetime
)
partition by range (to_days(create_date)) (
partition p1 values less than (to_days('20200401')),
partition p2 values less than (to_days('20200402'))
)

注意：

range接受整型，所以需要用to_days转换；
如何类型是timestamp，则用unix_timestamp转换；

list分区

和range分区的区别在于，range使用区间，list使用枚举列表，例如：

create table table_demo (
id int,
register_channel int
)
partition by list (register_channel) (
partition p1 values in (1,2,3),
partition p2 values in (4,5,6)
)

注意：
同range一样， list只接受整型

hash分区

对于没有明显特征的值做分区，采用此类进行分区，例如对id进行hash

PARTITION BY HASH(id)
PARTITIONS 4;

注意
hash也是同样只接受整型，然后使用mod partitons计算分区
hash还有另外一种，叫做linear hash，唯一的区别在于其分区算法不是采用mod还是采用linear hash算法
linear hash算法
公式：x mod 2n = x mod n 或者 x mod n +n
例如：5 mod 8 = 5 mod 4 或者 5 mod 4 + 4 = 1 或者 5
这种算法可以方便分区的扩容操作

key分区

跟hash比较类似，但是区别在于：

hash只能使用单列作为分区键，key可以多列；
hash只接受整型，因此非整型需要通过expr计算，key不需要是整型，key会对分区键进行md5算法计算；
key分区默认采用主键作为分区键
PARTITION BY KEY()
PARTITIONS 2;

总结
除了key分区，其他分区方式都只能接受整型，因此可能需要对列采用表达式计算expr(column)
分区键特征不明显可以采用hash，需要考虑分区数变化的采用linear hash，时间相关的可以采用range

3，分区表使用

如何管理RANGE和LIST分区

以该分区表为例

CREATE TABLE members (
id INT,
fname VARCHAR(25),
lname VARCHAR(25),
dob DATE
)
PARTITION BY RANGE( YEAR(dob) ) (
PARTITION p0 VALUES LESS THAN (1970),
PARTITION p1 VALUES LESS THAN (1980),
PARTITION p2 VALUES LESS THAN (1990)
);
1. 删除分区

ALTER TABLE members DROP PARTITION p1;
注意：如果删掉了某分区，则该分区内的数据将全部丢失，不仅如此，在用show create table members\G;命令查看该表的创建语句时将无法看到被删除分区的任何信息。

对于RANGE分区来说，如果删除了p1分区，在插入数据时，如果日期在1970到1980区间之内，则该数据将会被分配到下一个分区，即p2,。

对于LIST分区，如果删除了某一分区，在插入数据时，如果数据属于这一分区，则插入会报错。

如果只是删除数据而不删除该分区的信息，可使用truncate命令

ALTER TABLE members TRUNCATE PARTITION p1;

2. 添加分区

ALTER TABLE members ADD PARTITION (PARTITION p3 VALUES LESS THAN (2000));
注意：使用ADD命令来添加分区，只能在分区列表的末尾添加，具体到本例中，只能添加到1990年以后。

当然，在实际生产环境中，这样的局限性太大，譬如我想将p0分区之前添加一个分区，区间为1960，或者将p1之间再添加一个1975的分区，这时候，用ADD将无法满足此类需求，可使用ALTER TABLE ... REORGANIZE PARTITION命令。

譬如：

ALTER TABLE members REORGANIZE PARTITION p0 INTO (
PARTITION s0 VALUES LESS THAN (1960),
PARTITION s1 VALUES LESS THAN (1970)
);
REORGANIZE命令其实是相当灵活的，不仅能拆分分区，还可以用来合并分区，譬如：

ALTER TABLE members REORGANIZE PARTITION p0,p1,p2,p3 INTO (
PARTITION m0 VALUES LESS THAN (1980),
PARTITION m1 VALUES LESS THAN (2000)
);
注意：

1> 不能使用REORGANIZE PARTITION命令修改表的分区类型，只能通过ALTER TABLE ... PARTITION BY ....语句，譬如：

ALTER TABLE members
PARTITION BY HASH( YEAR(dob) )
PARTITIONS 8;
2> REORGANIZE PARTITION语法如下：

ALTER TABLE tbl_name
REORGANIZE PARTITION partition_list
INTO (partition_definitions);
partition_definitions中分区的范围必须要涵盖partition_list中的分区范围。

如何管理HASH和KEY分区

以该分区表为例

CREATE TABLE clients (
id INT,
fname VARCHAR(30),
lname VARCHAR(30),
signed DATE
)
PARTITION BY HASH( MONTH(signed) )
PARTITIONS 12;
对于HASH分区和KEY分区，并不支持上述RANGE和LIST分区的语法，譬如DROP，TRUNCATE，REORGANIZE分区。

事实上，它只支持一种类型的“分区调整”。

ALTER TABLE clients COALESCE PARTITION 4;
该命令的作用在于将clients表的分区剪裁4个，从12个剪裁到8个。

ALTER TABLE clients ADD PARTITION PARTITIONS 6;
同样，该命令为clients表增加了6个分区，从12个到18个。