mysql 分区表的常见操作

将普通表转换为分区表

1、用alter table table_name partition by命令重建分区表

alter table jxfp_data_bak PARTITION BY KEY(SH) PARTITIONS 8;

分区表操作

CREATE TABLE t1 (
    id INT,
    year_col INT
)
PARTITION BY RANGE (year_col) (
    PARTITION p0 VALUES LESS THAN (1991),
    PARTITION p1 VALUES LESS THAN (1995),
    PARTITION p2 VALUES LESS THAN (1999)
);

1、ADD PARTITION （新增分区）

ALTER TABLE t1 ADD PARTITION (PARTITION p3 VALUES LESS THAN (2002));

2、DROP PARTITION （删除分区）

ALTER TABLE t1 DROP PARTITION p0, p1;

3、TRUNCATE PARTITION（截取分区）

ALTER TABLE t1 TRUNCATE PARTITION p0;

ALTER TABLE t1 TRUNCATE PARTITION p1, p3;

4、COALESCE PARTITION（合并分区）

CREATE TABLE t2 (
    name VARCHAR (30),
    started DATE
)
PARTITION BY HASH( YEAR(started) )
PARTITIONS 6;

ALTER TABLE t2 COALESCE PARTITION 2;

5、REORGANIZE PARTITION（拆分/重组分区）

1）拆分分区

ALTER TABLE table ALGORITHM=INPLACE, REORGANIZE PARTITION;

ALTER TABLE employees ADD PARTITION (
    PARTITION p5 VALUES LESS THAN (2010),
    PARTITION p6 VALUES LESS THAN MAXVALUE
);

2）重组分区

ALTER TABLE members REORGANIZE PARTITION s0,s1 INTO (
    PARTITION p0 VALUES LESS THAN (1970)
);
ALTER TABLE tbl_name
    REORGANIZE PARTITION partition_list
    INTO (partition_definitions);
ALTER TABLE members REORGANIZE PARTITION p0,p1,p2,p3 INTO (
    PARTITION m0 VALUES LESS THAN (1980),
    PARTITION m1 VALUES LESS THAN (2000)
);
ALTER TABLE tt ADD PARTITION (PARTITION np VALUES IN (4, 8));
ALTER TABLE tt REORGANIZE PARTITION p1,np INTO (
    PARTITION p1 VALUES IN (6, 18),
    PARTITION np VALUES in (4, 8, 12)
);

6、ANALYZE 、CHECK PARTITION（分析与检查分区）

1）ANALYZE 读取和存储分区中值的分布情况

ALTER TABLE t1 ANALYZE PARTITION p1, ANALYZE PARTITION p2;

ALTER TABLE t1 ANALYZE PARTITION p1, p2;

2）CHECK 检查分区是否存在错误

ALTER TABLE t1 ANALYZE PARTITION p1, CHECK PARTITION p2;

7、REPAIR分区

修复被破坏的分区

ALTER TABLE t1 REPAIR PARTITION p0,p1;

8、OPTIMIZE

该命令主要是用于回收空闲空间和分区的碎片整理。对分区执行该命令,相当于依次对分区执行 CHECK PARTITION, ANALYZE PARTITION,REPAIR PARTITION命令。

譬如:

ALTER TABLE t1 OPTIMIZE PARTITION p0, p1;

9、REBUILD分区

重建分区,它相当于先删除分区中的数据,然后重新插入。这个主要是用于分区的碎片整理。

ALTER TABLE t1 REBUILD PARTITION p0, p1;

10、EXCHANGE PARTITION（分区交换）

分区交换的语法如下:

ALTER TABLE pt EXCHANGE PARTITION p WITH TABLE nt

其中,pt是分区表,p是pt的分区(注:也可以是子分区),nt是目标表。

其实,分区交换的限制还是蛮多的:

1） nt不能为分区表

2）nt不能为临时表

3）nt和pt的结构必须一致

4）nt不存在任何外键约束,即既不能是主键,也不能是外键。

5）nt中的数据不能位于p分区的范围之外。

具体可参考MySQL的官方文档

11、迁移分区（DISCARD 、IMPORT ）

ALTER TABLE t1 DISCARD PARTITION p2, p3 TABLESPACE;

ALTER TABLE t1 IMPORT PARTITION p2, p3 TABLESPACE;