mysql partition

[clms2]

需注意：原表需要有且仅有一个作为区分分区字段的键(分区字段需包含在唯一/主键索引里):

• alter table order_subscribe add primark key(id,created_at)
• 在现有表上进行分区：

ALTER TABLE order_subscribe PARTITION BY RANGE COLUMNS(created_at)
(   
PARTITION p1 VALUES LESS THAN ('2018-02-01 00:00:00'),
PARTITION p2 VALUES LESS THAN ('2018-03-01 00:00:00'),
PARTITION p3 VALUES LESS THAN ('2018-04-01 00:00:00'),
PARTITION p4 VALUES LESS THAN ('2018-05-01 00:00:00'),
PARTITION p5 VALUES LESS THAN ('2018-06-01 00:00:00'),
PARTITION p6 VALUES LESS THAN ('2018-07-01 00:00:00'),
PARTITION p7 VALUES LESS THAN ('2018-08-01 00:00:00'),
PARTITION p8 VALUES LESS THAN ('2018-09-01 00:00:00'),
PARTITION p9 VALUES LESS THAN ('2018-10-01 00:00:00'),
PARTITION p10 VALUES LESS THAN MAXVALUE
);

• 在现有分区上进行再次分区:

ALTER TABLE  order_subscribe REORGANIZE PARTITION p0 INTO (
	PARTITION p1 VALUES LESS THAN ('2018-05-01 00:00:00'),
	PARTITION p2 VALUES LESS THAN ('2018-06-01 00:00:00')
);

• 有大量数据删除时优化分区：ALTER TABLE order_subscribe OPTIMIZE PARTITION
• 又想还原回原来的单表形式：ALTER TABLE order_subscribe REMOVE PARTITIONING