数据表分区策略及实现（二）

数据表分区策略及实现（二）：

本篇文章主要介绍数据表分区的管理、优化及复合分区三部分内容，对于数据表分区的原理、实现及验证的内容，可以查看文章《数据表分区策略及实现（一）》，访问地址：

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（新）