mysql分区partition详解

分区管理  论坛

1. RANGE和LIST分区的管理

针对非整形字段进行RANG\LIST分区建议使用COLUMNS分区。 

 

RANGE COLUMNS是RANGE分区的一种特殊类型，它与RANGE分区的区别如下：
1. RANGE COLUMNS不接受表达式，只能是列名。而RANGE分区则要求分区的对象是整数。
2. RANGE COLUMNS允许多个列，在底层实现上，它比较的是元祖（多个列值组成的列表），而RANGE比较的是标量，即数值的大小。
3. RANGE COLUMNS不限于整数对象，date，datetime，string都可作为分区列。

 

 

CREATE TABLE rcx (
    a INT,
    b INT,
    c CHAR(3),
    d INT
)
PARTITION BY RANGE COLUMNS(a,d,c) (
    PARTITION p0 VALUES LESS THAN (5,10,'ggg'),
    PARTITION p1 VALUES LESS THAN (10,20,'mmmm'),
    PARTITION p2 VALUES LESS THAN (15,30,'sss'),
    PARTITION p3 VALUES LESS THAN (MAXVALUE,MAXVALUE,MAXVALUE)
);

 

添加

CREATE TABLE IF NOT EXISTS `t1` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT 'xxid',
  `store_id` int(10) unsigned NOT NULL,
  `customer_surname` varchar(30) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COMMENT='xx表' AUTO_INCREMENT=1 partition by range(id)(
    partition p0 values less than(5), 
    partition p1 values less than(10), 
    partition p2 values less than(15)
);

追加

alter table t1 add partition(
    partition p3 values less than(20)
);

修改分区为key类型的分区

#这和先删除这个表、然后使用“CREATE TABLE t1 PARTITION BY KEY(id) PARTITIONS 2；”重新创建这个表具有同样的效果
ALTER TABLE t1 partition by key(id) partition 2；

如果希望改变表的分区而又不丢失数据，使用“ALTER TABLE ... REORGANIZE PARTITION”语句

#分解分区  将p0拆分为s0和s1
ALTER TABLE members REORGANIZE PARTITION p0 INTO (
    PARTITION s0 VALUES LESS THAN (3),
    PARTITION s1 VALUES LESS THAN (5)
)；

#合并分区  将s0和s1合并为p0
ALTER TABLE members REORGANIZE PARTITION s0,s1 INTO (
    PARTITION p0 VALUES LESS THAN (5)
)；

#合并分区  将s0和s1合并为 r0 r1
ALTER TABLE members REORGANIZE PARTITION s0,s1 INTO (
    PARTITION s0 VALUES LESS THAN (2),
    PARTITION p0 VALUES LESS THAN (5)
)；

删除分区

#当删除了一个分区，也同时删除了该分区中所有的数据
ALTER TABLE t1 drop partition p0,p1,p2,p3;
#如果希望从所有分区删除所有的数据，但是又保留表的定义和表的分区模式，使用TRUNCATE TABLE命令

2. HASH和KEY分区的管理

增加：

CREATE TABLE clients(
    id INT NOT NULL,
    firstname VARCHAR(30),
    lastname VARCHAR(30),
    hired DATE NOT NULL DEFAULT '1970-01-01',
    separated DATE NOT NULL DEFAULT '9999-12-31',    
    signed DATE
)
PARTITION BY HASH(MONTH(signed)) 
PARTITIONS 12；
//将hash类型的分区分为12个
//基于时间类型的字段的转换函数mysql提供了"YEAR(),MONTH(),DAY(),TO_DAYS(),TO_SECONDS(),WEEKDAY(),DAYOFYEAR()

 

修改：coalesce

#将12个分区改为6个
ALTER TABLE clients COALESCE PARTITION 6；

 

删除

???

 

 

3. 分区维护

。对于分区表，MySQL不支持命令CHECK TABLE，OPTIMIZE TABLE，ANALYZE TABLE，或REPAIR TABLE。作为替代，可以使用ALTER TABLE 的许多扩展来在一个或多个分区上直接地执行这些操作，如下面列出的那样

重建分区：

ALTER TABLE t1 REBUILD PARTITION (p0, p1)；

 优化分区：如果从分区中删除了大量的行，或者对一个带有可变长度的行（也就是说，有VARCHAR，BLOB，或TEXT类型的列）作了许多修改，可以使用“ALTER TABLE ... OPTIMIZE PARTITION”来收回没有使用的空间，并整理分区数据文件的碎片

4. 获取关于分区的信息

#在一个给定的分区表上使用“OPTIMIZE PARTITION”等同于在那个分区上运行CHECK PARTITION，ANALYZE PARTITION，和REPAIR PARTITION。 
ALTER TABLE t1 OPTIMIZE PARTITION (p0, p1)；

分析分区：读取并保存分区的键分布

ALTER TABLE t1 ANALYZE PARTITION (p3)；

修补分区： 修补被破坏的分区。

ALTER TABLE t1 REPAIR PARTITION (p0,p1);

检查分区： 可以使用几乎与对非分区表使用CHECK TABLE 相同的方式检查分区

#这个命令可以告诉你表t1的分区p1中的数据或索引是否已经被破坏。如果发生了这种情况，使用“ALTER TABLE ... REPAIR PARTITION”来修补该分区
ALTER TABLE trb3 CHECK PARTITION (p1)；
#还可以使用mysqlcheck或myisamchk 应用程序，在对表进行分区时所产生的、单独的MYI文件上进行操作，来完成这些任务。请参见8.7节，“mysqlcheck：表维护和维修程序”

 

4.获取关于分区的信息

查看建表结构

SHOW CREATE TABLE  表名;#当前，对于按HASH或KEY分区的表，PARTITIONS子句并不显示

 查看是否支持分区

SHOW VARIABLES LIKE '%partition%';

表的各种参数状态

SHOW TABLE STATUS

 查看分区情况：

select
partition_name part,
partition_expression expr,
partition_description descr,
table_rows
from information_schema.partitions where
table_schema = schema()
and table_name='填入你要查看的表名';

通过数据字典查看分区信息

select * from information_schema.partitions;

通过此语句扫描分区及其使用情况

explain partitions select * from mdl_user;