分区的管理和调整都有哪些

CREATE TABLE ts (id INT, purchased DATE)
    PARTITION BY RANGE(YEAR(purchased))
    SUBPARTITION BY HASH(TO_DAYS(purchased))
    (
        PARTITION p0 VALUES LESS THAN (1990)
        (
            SUBPARTITION s0a
                DATA DIRECTORY = '/disk0'
                INDEX DIRECTORY = '/disk1',
            SUBPARTITION s0b
                DATA DIRECTORY = '/disk2' 
                INDEX DIRECTORY = '/disk3'
        ),
        PARTITION p1 VALUES LESS THAN (2000)
        (
            SUBPARTITION s1a
                DATA DIRECTORY = '/disk4/data' 
                INDEX DIRECTORY = '/disk4/idx',
            SUBPARTITION s1b
                DATA DIRECTORY = '/disk5/data' 
                INDEX DIRECTORY = '/disk5/idx'
        ),
        PARTITION p2 VALUES LESS THAN MAXVALUE
        (
            SUBPARTITION s2a,
            SUBPARTITION s2b
        )
    );

• DATA DIRECTORY表示数据的物理文件的存放目录
• INDEX DIRECTORY表示索引的物理文件的存放目录

分区的管理

MySQL提供了许多修改分区表的方式。添加、删除、重新定义、合并或拆分已经存在的分区是可能的。

所有这些操作都可以通过使用ALTER TABLE命令的分区扩展来实现。

新增分区

为已创建的未分区表创建分区：

• RANGE：ALTER TABLE tb PARTITION BY RANGE (expr) ( range_partitions_exprs(n>0) );
• LIST：ALTER TABLE tb PARTITION BY LIST (expr) ( list_partitions_exprs(n>0) );
• HASH：ALTER TABLE tb PARTITION BY HASH(expr) PARTITIONS 2;
• KEY：ALTER TABLE tb PARTITION BY KEY(expr) PARTITIONS 2;

为分区表添加n个分区：

• RANGE：ALTER TABLE tb ADD PARTITION ( range_partitions_exprs(n>0) );
• LIST：ALTER TABLE tb ADD PARTITION ( list_partitions_exprs(n>0) );
• HASH & KEY：ALTER TABLE tb ADD PARTITION PARTITIONS n;

调整分区

reorganize

数据不丢失的前提下，将m个分区合并为n个分区（m>n），即减量重新组织分区

• RANGE：ALTER TABLE tb REORGANIZE PARTITION s0,s1,... INTO ( range_partitions_exprs(n) )
• LIST：ALTER TABLE tb REORGANIZE PARTITION s0,s1,... INTO ( list_partitions_exprs(n) )
• HASH & KEY：ALTER TABLE clients COALESCE PARTITION n; (n小于原有分区数)

数据不丢失的前提下，将分区表的m个分区拆分为n个分区（m<n），即增量重新组织分区

• RANGE：ALTER TABLE tb REORGANIZE PARTITION p0,p1,... INTO ( range_partitions_exprs(n) )
• LIST：ALTER TABLE tb REORGANIZE PARTITION p0,p1,... INTO ( list_partitions_exprs(n) )

不能使用REORGANIZE PARTITION来改变表的分区类型；也就是说。

重建分区，即先删除分区中的所有记录，然后重新插入。可用于整理分区碎片。

• ALTER TABLE tb REBUILD PARTITION p0, p1;

优化分区，整理分区碎片 optimize

• ALTER TABLE tb OPTIMIZE PARTITION p0, p1;

如从分区中删除了大量的行，或者对一个带有可变长度字段（VARCHAR、BLOB、TEXT类型）的行作了许多修改，可以使用优化分区来收回没有使用的空间，并整理分区数据文件的碎片。

修复分区，修补被破坏的分区。

• ALTER TABLE tb REPAIR PARTITION p0,p1;

检查分区，这个命令可以告诉你分区中的数据或索引是否已经被破坏，如果被破坏，请使用修复分区来修补

• ALTER TABLE tb CHECK PARTITION p1;

删除分区

删除一个分区，以及分区内的所有数据：

• ALTER TABLE tb DROP PARTITION p2;

删除一个分区，但保留分区内的所有数据(MySQL 5.5引入)： truncate

• ALTER TABLE tb TRUNCATE PARTITION p2;

查看分区

查看某个schema下某个表的分区信息

• SELECT * FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_SCHEMA = 'xxx' AND TABLE_NAME LIKE 'xxxx';

分析某个分区,主要看行数和名称以及状态

• ALTER TABLE tb ANALYZE PARTITION p3;