用alter可以操作表增加分区
ALTER TABLE `xxx_table`
PARTITION BY RANGE (id)
(
PARTITION p1 VALUES LESS THAN (10000),
PARTITION p2 VALUES LESS THAN (20000),
PARTITION p3 VALUES LESS THAN (30000),
PARTITION p4 VALUES LESS THAN MAXVALUE
)
测试的时候可以这样,加快速度,一般还是要创建个新表,把原表的数据复制到新表里
分区知识补充:
- 查看分区是否起作用
explain partitions select * from xxx_table
- 从分区查找
select * from xxx_table PARTITION(p1)
- 增加分区
ALTER TABLE xxx_table ADD PARTITION(
PARTITION p1 VALUES LESS THAN ('2019-04-01 00:00:00')
);
- 合并分区
ALTER TABLE xxx_table REORGANIZE PARTITION p1,p2 INTO (PARTITION p3 VALUES less than('2019-01-01 00:00:00'));
- 删除分区(会把里面的数据也删除掉)
alter table xxx_table drop partition p1
- 删除分区,保留数据
alter table xxx_table remove partitioning