可以看到p_subpartition有三个分区p0,p1,p2；而这三个分区每一个又进一步分为2个分区,那么整个表都就分为6个小分区；

可以看到代表p_sobpartitionp0.myd的文件消失了,取代的是p_subpartition#p#p0#sp#s1.myd.

在MySQL 5.1中，对于已经通过RANGE或LIST分区了的表再进行子分区是可能的,子分区是分区表中每个分区的再次分割，子分区既可以使用HASH希分区,也可以使用KEY分区。这也被称为复合分区,composite partitioning.

1，如果一个分区中创建了子分区，其他分区也要有子分区

2，如果创建了了分区，每个分区中的子分区数必有相同

3，同一分区内的子分区，名字不相同，不同分区内的子分区名子可以相同（5.1.50不适用）

分区注意点

1、重新分区时，如果原分区里面存在maxvalue则新的分区里面也必须包含maxvalue否则就错误。

1. alter table p_range2x 
2. reorganize partition p1,p2 
3. into (partition p0 values less than (5), partition p1 values less than maxvalue); 
4. [Err] 1520 – Reorganize of range partitions cannot change total ranges except for last partition where it can extend the range 

2、分区删除时，数据也同样会被删除

alter table p_range drop partition p0;

3、如果range分区列表里面没有maxvalue则如有新数据大于现在分区range数据值那么这个数据是无法写入到数据库表的。

4、修改表名不需要 删除分区后在进行更改，修改表名后分区存储myd myi对应也会自动更改。

如果希望从所有分区删除所有的数据，但是又保留表的定义和表的分区模式，使用TRUNCATE TABLE命令。（请参见13.2.9节，“TRUNCATE语法”）。

如果希望改变表的分区而又不丢失数据，使用“ALTER TABLE … REORGANIZE PARTITION”语句。参见下面的内容，或者在13.1.2节，“ALTER TABLE语法” 中参考关于REORGANIZE PARTITION的信息。

5、对表进行分区时,不论采用哪种分区方式如果表中存在主键那么主键必须在分区列中,表分区的局限性.

6、list方式分区没有类似于range那种 less than maxvalue的写法,也就是说list分区表的所有数据都必须在分区字段的值列表集合中.

7、在MySQL 5.1版中,同一个分区表的所有分区必须使用同一个存储引擎,例如,不能对一个分区使用MyISAM,而对另一个使用InnoDB.

8、分区的名字是不区分大小写的,myp1与MYp1是相同的。

分区的管理

range与list分区的改变动作不能适用于hash与key方式的分区,删除与添加动作是都能使用的,以下面的例子,代码如下:

1. DROP TABLE IF EXISTS `p_list`; 
2.  
3. CREATE TABLE `p_list` ( 
4. `id` int(10) NOT NULL AUTO_INCREMENT, 
5. `typeid` mediumint(10) NOT NULL DEFAULT '0', 
6. `typename` char(20) DEFAULT NULL, 
7. PRIMARY KEY (`id`,`typeid`) 
8. ) ENGINE=MyISAM AUTO_INCREMENT=9 DEFAULT CHARSET=utf8 
9. /*!50100 PARTITION BY LIST (typeid) 
10. (PARTITION p0 VALUES IN (1,2,3,4) ENGINE = MyISAM, 
11. PARTITION p1 VALUES IN (5,6,7,8) ENGINE = MyISAM) */; 

range与list分区的管理

删除分区:ALTER TABLE tr DROP PARTITION p1;

需要注意的是删除分区后,该分区的所有数据都没有了,同时删除后存在一个重大影响也就是typeid为5,6,7,8的记录是不能写入到该表了的.

清空数据:如果想要保留表结构与分区结构可以使用 TRUNCATE TABLE 清空表.

更改分区保留数据:

1. ALTER TABLE tbl_name REORGANIZE PARTITION partition_list INTO (partition_definitions)；如果想保留数据进行分区的更改 
2.  
3. ALTER TABLE p_list REORGANIZE PARTITION p0 INTO ( 
4.  PARTITION s0 VALUES IN(1,2), 
5.  PARTITION s1 VALUES IN(3,4), 
6. ); 

这样就能进行分区的合并了,那怎么进行拆分呢?

ALTER TABLE p_list REORGANIZE PARTITION s0,s1 INTO (

使用 REORGANIZE PARTITION进行数据的合并与拆分，数据是没有丢失的,在使用REORGANIZE进行重新分区时,需要注意几点:

1、用来确定新分区模式的PARTITION子句使用与用在CREATE TABLE中确定分区模式的PARTITION子句相同的规则。(partition 分区子句必须与创建原分区时的规则相同)

2、partition_definitions 列表中分区的合集应该与在partition_list 中命名分区的合集占有相同的区间或值集合。 (不管是合并还是拆分，s0,s1到p0；p0到s0,s1 里面的区间或者值都必须相同)

3、对于按照RANGE分区的表，只能重新组织相邻的分区；不能跳过RANGE分区。(比如按range年份 p0 1990,p1 2000 ,p2 2013三个分区；在合并时partition p0,p2 into(),这样是不行的，因为这两个分区不是相邻的分区；)

4、不能使用REORGANIZE PARTITION来改变表的分区类型；也就是说，例如，不能把RANGE分区变为HASH分区，反之亦然。也不能使用该命令来改变分区表达式或列。

增加分区:

ALTER TABLE p_list ADD PARTITION (PARTITION p2 VALUES IN (9, 10, 11))；

但是不能使用:ALTER TABLE p_list ADD PARTITION (PARTITION p2 VALUES IN (9, 14))；

这样mysql 会产生错误1465 (HY000): 在LIST分区中，同一个常数的多次定义.

hash与key分区的管理在改变分区设置方面，按照HASH分区或KEY分区的表彼此非常相似，但是它们又与按照RANGE或LIST分区的表在很多方面有差别。

关于添加和删除按照RANGE或LIST进行分区的表的分区

不能使用与从按照RANGE或LIST分区的表中删除分区相同的方式，来从HASH或KEY分区的表中删除分区。但是，可以使用“ALTER TABLE ... COALESCE PARTITION”命令来合并HASH或KEY分区。

查看源代码打印帮助1 DROP TABLE IF EXISTS `p_hash`;  2    3 CREATE TABLE `p_hash` (  4 `id` int(10) NOT NULL AUTO_INCREMENT,  5 `storeid` mediumint(10) NOT NULL DEFAULT '0',  6 `storename` char(255) DEFAULT NULL,  7 PRIMARY KEY (`id`,`storeid`)  8 ) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8  9 /*!50100 PARTITION BY HASH (storeid)  10 PARTITIONS 4 */; 

如p_hash的分区数为4个；要减少分区数为2个:ALTER TABLE p_hash COALESCE PARTITION 2；

对于按照HASH，KEY，LINEAR HASH，或LINEAR KEY分区的表， COALESCE能起到同样的作用。COALESCE不能用来增加分区的数量，如果你尝试这么做，结果会出现类似于下面的错误：

mysql> ALTER TABLE clients COALESCE PARTITION 18;

错误1478 (HY000): 不能移动所有分区，使用DROP TABLE代替要增加顾客表的分区数量从12到18，使用“ALTER TABLE … ADD PARTITION”,具体如下：

ALTER TABLE clients ADD PARTITION PARTITIONS 18；注释：“ALTER TABLE … REORGANIZE PARTITION”不能用于按照HASH或HASH分区的表。

分区维护

重建分区:这和先删除保存在分区中的所有记录，然后重新插入它们，具有同样的效果,它可用于整理分区碎片。

ALTER TABLE t1 REBUILD PARTITION (p0, p1)；

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

ALTER TABLE t1 OPTIMIZE PARTITION (p0, p1)；

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

ALTER TABLE t1 ANALYZE PARTITION (p3)；

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

ALTER TABLE t1 REPAIR PARTITION (p0,p1);

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

ALTER TABLE trb3 CHECK PARTITION (p1)；

这个命令可以告诉你表t1的分区p1中的数据或索引是否已经被破坏,如果发生了这种情况,使用“ALTER TABLE ... REPAIR PARTITION”来修补该分区,获取分区信息

在mysql服务器信息数据库里面的partitions存放着服务器所有表的分区信息,代码如下 :

explain partitions命令

1. explain partitions select * from p_hash 
2. +----+-------------+--------+-------------+------+---------------+------+---------+------+------+-------+ 
3. | id | select_type | table  | partitions  | type | possible_keys | key  | key_len | ref  | rows | Extra | 
4. +----+-------------+--------+-------------+------+---------------+------+---------+------+------+-------+  //phpfensi.com 
5. |  1 | SIMPLE&nbsp&nbsp  | p_hash | p0,p1,p2,p3 | ALL  | NULL&nbsp&nbsp&nbsp&nbsp  | NULL | NULL&nbsp  | NULL |&nbsp 10 |&nbsp&nbsp&nbsp | 
6. +----+-------------+--------+-------------+------+---------------+------+---------+------+------+-------+  

-- 获取到p_list表的分区详细信息。

select * from information_schema.`PARTITIONS` where TABLE_NAME = 'p_list';

-- 分区的创建信息:show create table p_list;

Tags: mysql表分区 mysql分区创建

分享到：