mysql 创建和修改表分区

用alter可以操作表增加分区

1. ALTER TABLE `xxx_table`

2. PARTITION BY RANGE (id)

3. (

4. PARTITION p1 VALUES LESS THAN (10000),

5. PARTITION p2  VALUES LESS THAN (20000),

6. PARTITION p3  VALUES LESS THAN (30000),

7. PARTITION p4  VALUES LESS THAN MAXVALUE

8. )

9.  

测试的时候可以这样,加快速度,一般还是要创建个新表,把原表的数据复制到新表里

 

分区知识补充:

• 查看分区是否起作用

explain partitions select * from xxx_table

• 从分区查找

select * from xxx_table PARTITION(p1)

• 增加分区

1. ALTER TABLE xxx_table ADD PARTITION(

2. PARTITION p1 VALUES LESS THAN ('2019-04-01 00:00:00')

3. );

• 合并分区

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