数据表分区策略及实现(二):
本篇文章主要介绍数据表分区的管理、优化及复合分区三部分内容,对于数据表分区的原理、实现及验证的内容,可以查看文章《数据表分区策略及实现(一)》,访问地址:
http://blog.csdn.net/why_2012_gogo/article/details/51492573而这里我们主要介绍表分区的添加、删除、修改以及查询,分区的优化以及复合分区。
· 分区管理
· 复合分区
· 注意事项
一、分区管理
这里继续以上一篇文章《数据表分区策略及实现(一)》中的数据表t_product_item为例进行分区管理操作,具体如下:
1、准备工作
建分区表:
CREATE TABLE t_product_item (
id int(7) not null,
title varchar(40) not null,
subtitle varchar(60) null,
price double not null,
imgurl varchar(70) not null,
producttype int(2) not null,
createtime datetime not null
DEFAULT CURRENT_TIMESTAMP
ON UPDATECURRENT_TIMESTAMP
)
ENGINE=InnoDB DEFAULT CHARSET=utf8
PARTITION BY RANGE(producttype) (
PARTITION p0 VALUES LESS THAN(2),
PARTITION p1 VALUES LESS THAN(4),
PARTITION p2 VALUES LESS THAN(6),
PARTITION p3 VALUES LESS THAN MAXVALUE
);
另外,查看分区的结构和数据情况,统一使用下面的语句:
select
partition_name part,
partition_expression expr,
partition_description descr,
table_rows
from information_schema.partitions where
table_schema = schema()
and table_name='t_product_item';
插入数据:
DELIMITER $$
USE study $$
DROP PROCEDURE IF EXISTS proc_insert_data$$
CREATE PROCEDURE proc_insert_data(IN loop_times INT)
BEGIN
DECLARE var INT DEFAULT 0;
WHILE (var<loop_times) DO
SET var=var + 1;
INSERT INTO t_product_item (id,title,subtitle,price,imgurl,producttype)
VALUES(var,'AAAAA','BBBBB',99.99,'http://null',0);
END WHILE;
END$$
--插入200万条数据
CALL proc_insert_data(2000000);
分表结果:
从上图刊出,分区p0承载了基本全部的数据,因为分区表是按照producttype进行范围分区,所以其它分区中并不存在数据。细心的同学会发现,我们明明插入了200万条数据,确实我们使用select count(*) 查看时确实也是200万条,那么为什么p0分区不是200万条数据?是因为我们使用了存储过程不断循环200万次,造成了较少部分数据没有命中到p0分区中,但是其依然保存在数据表中并未丢失哦。
分摊数据:
接下来,我们将p0中的数据分摊到其它三个分区中,具体操作就是使用update修改producttype的值使其分别对应各个分区中,分摊结果如下:
2、新增分区
为分区增加分区比较简单,就是使用ALTER TABLE <tablename> ADD PARTITION (p4 VALUES LESS THAN(8))即可,比如这里我们添加一个匹配producttype=6,7的的分区:
ALTER TABLE t_product_item ADD PARTITION (PARTITION p4 VALUES LESS THAN(8))
如果原分区定义LESS THAN MAXVALUE,执行上面的语句会报错,具体问题解释请参看注意事项的第1个问题描述。那么此中情况如何解决呢?答案是可对该表的分区重新定义,其中的数据不会丢失,具体如下:
ALTER TABLE t_product_item PARTITION BYRANGE(producttype)
(
PARTITION p0 VALUES LESS THAN(2),
PARTITION p1 VALUES LESSTHAN(4),
PARTITION p2 VALUES LESS THAN(6),
PARTITION p3 VALUES LESS THAN(8),
PARTITION p4 VALUES LESS THAN MAXVALUE
)
执行结果:
从上图可以看出,我们已经新增一个分区p3,同时各个分区的数据会根据producttype分区策略动态的分配到各个匹配的分区中。此时,分区p4中并没有匹配的数据,所以其内部数据为空。
3、分区拆分
这里我们将p3分区,才分为p3_01和p3_02两个分区,具体如下:
ALTER TABLE t_product_item REORGANIZE PARTITION p3 INTO
(
PARTITION p3_01 VALUES LESS THAN(7),
PARTITION p3_02 VALUES LESS THAN(8)
);
执行结果:
4、分区合并
合并分区与拆分分区类似,这里我们将p3_01和p3_02合并为一个分区p3,如下:
ALTER TABLE t_product_item REORGANIZE PARTITION p3_01,p3_02 INTO
(
PARTITION p3 VALUES LESS THAN(8)
);
执行结果:
NOTE:
这里有个注意事项,请查看注意事项的第2条描述。
5、重建分区
重建分区和重新定义分区不同,重建分区是先删除分区中的数据,然后重新插入数据,可以整理分区中的碎片数据,具体如下:
ALTER TABLE t_product_item REBUILD PARTITION p2,p3;
执行结果:
重建分区之后,分区中的数据被删除,但并不是数据被从数据表中删除,需要我们重新分配数据即可。
6、分区分析
读取并保存分区的键分布,具体如下:
ALTER TABLE t_product_item ANALYZE PARTITION p1,p2,p3
执行结果:
7、检查分区
可以使用几乎与对非分区表使用CHECK TABLE 相同的方式检查分区,具体如下:
ALTER TABLE t_product_item CHECK PARTITION p1,p2,p3;
这个命令可以知道表t_product_item的分区p1,p2中的数据或索引是否被破坏,如果发生了这种情况,使用“ALTER TABLE ... REPAIR PARTITION”来修补该分区。
执行结果:
与上面分析的结果相似,只不过op类型为check,如果有数据或是索引损坏,会在该结果中显示出来。
8、修补分区
承接上面的CHECK出来的问题进行修复,具体操作如下:
ALTER TABLE t_product_item REPAIR PARTITION p0,p1,p2;
执行结果:
与上面分析的结果相似,只不过op类型为repair,修复的结果会在该结果中显示出来,修复状态为ok。
9、分区优化
如果分区中删除了大量的行,或者对一个带有可变长度的行(比如:VARCHAR,BLOB,或TEXT类型的列)作了大量修改,那么可以使用“ALTER TABLE ... OPTIMIZE PARTITION”来收回没有使用的空间,并整理分区数据文件的碎片。
ALTER TABLE t_product_item OPTIMIZE PARTITION p1,p2;
执行结果:
NOTE:
上图的note提示内容为Mysql5.5.30已经修复的bugs,可以查看:
http://dev.mysql.com/doc/relnotes/mysql/5.5/en/news-5-5-30.html
10、删除分区
删除单个分区:
ALTER TABLE t_product_item DROP PARTITION p0
删除多个分区:
ALTER TABLE t_product_item DROP PARTITION p0,p1,p2
二、复合分区
复合分区就是几种分区策略结合的分区类型,一般情况下,我们只需要将结合两种分区类型,比如,RANGE+HASH组合,那么下面我们就以这个组合为例说明。
首先,我们以重定义分区方式对数据表t_product_item进行RANGE+HASH组合方式定义分区结构,具体如下:
ALTER TABLE t_product_item
CREATE TABLE t_product_item (
id int(7) notnull,
title varchar(40) not null,
subtitle varchar(60) null,
price double not null,
imgurl varchar(70) not null,
producttype int(2) not null,
createtime datetime not null
DEFAULT CURRENT_TIMESTAMP
ON UPDATECURRENT_TIMESTAMP
)
ENGINE=InnoDB DEFAULT CHARSET=utf8
PARTITION BY RANGE(producttype)
SUBPARTITION BYHASH(YEAR(createtime))
SUBPARTITIONS 6
(
PARTITION p0 VALUES LESS THAN(2),
PARTITION p1 VALUES LESSTHAN(4),
PARTITION p2 VALUES LESS THAN(6),
PARTITION p3 VALUES LESS THAN(8),
PARTITION p4 VALUES LESS THAN MAXVALUE
);
上面使用RANGE和HASH组合定义了一个按范围p0~p4的分区,同时结合了一个6个按年HASH的子分区,定义结果:
现在,我们为其插入数据,并通过分别修改producttype和createtime来分配数据到对应分区,执行结果如下:
上图中共创建了4个RANGE范围分区,6个子HASH分区,一共分区个数:4*6=24个分区,分别按照范围和日期hash模数分区,图中p0的1000条数据即为2015-02-15日HASH分区的数据,而p0和p1的数据是按照范围匹配的分区数据。
三、注意事项
1、添加分区
当我们使用了THAN MAXVALUE的分区添加分区时,回报如下错误:
问题:
error : MAXVALUE can only be used in last partition definition
原因很简单,因为新增分区时,是在原有分区列表末尾继续添加,而THAN MAXVALUE正好位于末尾,同时其也包含了要添加的6,7的范围,所提系统会任务冲突。
解决:
为数据表重新定义分区即可,可查看上面新增分区部分即可。
2、分区拆分合并
分区的拆分及合并操作之后,分区内的数据需要重新分配,否则默认数据为空。
3、重建分区
分区重建其实是删除原分区中数据,清理不需要的数据碎片,此时重建的分区内数据为空,需要重新分配数据到对应的分区中即可。
4、分区键
分区键必须是INT类型,或者通过表达式返回INT类型,可以为NULL。唯一的例外是当分区类型为KEY分区的时候,可以使用其他类型的列作为分区键( BLOB or TEXT 列除外)。
5、索引
对分区表的分区键创建索引,那么这个索引也将被分区,分区键没有全局索引一说。
6、子分区
只有RANG和LIST分区能进行子分区,HASH和KEY分区不能进行子分区。另外,不可以删除HASH或者KEY分区,同时,临时表也不能被分区。
好了,到这里已经介绍完了关于数据表分区的管理、复合分区以及一些注意事项,如有纰漏或建议,请在下面讨论或是qq群中指出,谢谢。
技术讨论群:
276592700(新)