Mysql分区与管理实践过程


为什么要对表进行分区?

为了改善大型表以及具有各种访问模式的表的可伸缩性,可管理性和提高数据库效率。
分区的一些优点包括:
1、与单个磁盘或文件系统分区相比,可以存储更多的数据。
2、通过删除与增加那些数据有关的分区,很容易地删除或增加那些数据。
3、一些查询可以得到极大的优化。
4、通过跨多个磁盘甚至服务器来分散数据查询。来获得更大的查询吞吐量。
5、Mysql5.5之后支持所有函数的分区优化,限定只查指定的分区。
例:统计查询性别为女的数据;
 
  1. select count(*) from emplpoyees where gender='F'
 
 
RANGE分区:
基于属于一个给定连续区间的列值,把多行分配给分区。这些敬意要连续且不能相互重叠,使用VALUES LESS THAN操作符来进行定义.
大致格式如下:【这是在建完表后添加分区的方法】
 
  1. ALTER TABLE emplpoyees 
  2. PARTITION BY RANGE (store_id) ( 
  3.     PARTITION p0 VALUES LESS THAN (6), 
  4.     PARTITION p1 VALUES LESS THAN (11), 
  5.     PARTITION p2 VALUES LESS THAN (16), 
  6.     PARTITION p3 VALUES LESS THAN (21) 
  7.     PARTITION p3 VALUES LESS THAN (MAXVALUE) 
  8. ); 
 
案例:【建表时创建分区】
将用户按照年龄每隔10岁进行分区,注意分区名字基本上遵循其它Mysql标识符应当遵循的原则。分区名是不区分大小写的。
 
  1. mysql> create database tube; 
  2. mysql> use tube 
  3. mysql> create table t1 (id int,name varchar(20),age int) 
  4.     -> partition by range (age) 
  5.     -> ( 
  6.     -> partition p01 values less than (10), 
  7.     -> partition p02 values less than (20), 
  8.     -> partition p03 values less than (30), 
  9.     -> partition p04 values less than (maxvalue) 
  10.     -> ); 
  11. Query OK, 0 rows affected (0.34 sec) 
 
查看数据目录可看出已经分区成功了;
 
  1. [root@agent mysql]# cd tube/ 
  2. [root@agent tube]# ll 
  3. total 404 
  4. -rw-rw---- 1 mysql mysql    61 Jan  5 15:38 db.opt 
  5. -rw-rw---- 1 mysql mysql  8614 Jan  5 15:40 t1.frm 
  6. -rw-rw---- 1 mysql mysql    36 Jan  5 15:40 t1.par 
  7. -rw-rw---- 1 mysql mysql 98304 Jan  5 15:40 t1#P#p01.ibd 
  8. -rw-rw---- 1 mysql mysql 98304 Jan  5 15:40 t1#P#p02.ibd 
  9. -rw-rw---- 1 mysql mysql 98304 Jan  5 15:40 t1#P#p03.ibd 
  10. -rw-rw---- 1 mysql mysql 98304 Jan  5 15:40 t1#P#p04.ibd 
  11. [root@agent tube]# file t1.par  
  12. t1.par: data 
此文件是保存分区信息的,所有分区的信息都在这里声明出来。
 
LIST分区
 
1、类似于按RANGE分区,区别在于LIST分区是基于列值匹配一个离散值集合中的某个值来进行选择。
2、LIST分区是通过使用"PARTITION BY LIST(expr)"来实现,其中“expr”是某列值或一个基于某个列值、并返回一个整数值的表达式,然后通过“VALUES IN(values_list)”
的方式来定义每个分区,其中“value_list”是一个通过逗号分隔的整数列表。
 
LIST分区案例:
对于一个综合性的网店来说,商品分为诸多种类,我们可以按照商品ID进行RANGE分区,也可以按照商品的类型划分分区。在这个例子中,LIST分区给了我们更多的选择。
 
  1. mysql> create table t2 (id int,cid int,name varchar(20),pos_date datetime) 
  2.     -> partition by list(cid) 
  3.     -> ( 
  4.     -> partition p01 values in (1,4,12), 
  5.     -> partition p02 values in (2,6,9), 
  6.     -> partition p03 values in (3,5,7,8,10,11) 
  7.     -> ); 
  8. Query OK, 0 rows affected (0.16 sec) 
查看数据目录可看出已经分区成功了;
 
  1. [root@agent tube]# ll t2* 
  2. -rw-rw---- 1 mysql mysql  8652 Jan  5 16:02 t2.frm 
  3. -rw-rw---- 1 mysql mysql    32 Jan  5 16:02 t2.par 
  4. -rw-rw---- 1 mysql mysql 98304 Jan  5 16:02 t2#P#p01.ibd 
  5. -rw-rw---- 1 mysql mysql 98304 Jan  5 16:02 t2#P#p02.ibd 
  6. -rw-rw---- 1 mysql mysql 98304 Jan  5 16:02 t2#P#p03.ibd 
HASH分区
1、基于用户定义的表达式的返回值来进行选择的分区,该表达式使用将要插入到表中的这些行的列值进行计算。这个函数可以包含Mysql中有效的、产生非负整数值的任何表达式。
2、要使用HASH分区来分割一个表,要在CREATE TABLE语句上添加一个“PARTITION BY HASH(expr)”子句,其中“expr”是一个返回一个整数的表达式。它可仅仅是字段类型为Mysql整型的一列的名字。
3、使用HASH分区的优点在于数据分布较为均匀。
 
HASH分区案例:
在Mysql Cluster中,分区行为是自动的,默认情况下,分区的数据和ndb node数量相同。通常节点数很多的情况下会通过配置分区数和node group搭配进行调整。
 
  1. mysql> create table t3(id int,cid int,name varchar(20),pos_date datetime) 
  2.     -> partition by hash (cid) 
  3.     -> partitions 4; 
  4. Query OK, 0 rows affected (0.09 sec) 
注意上面语句有点不同,没什么分隔符,并且这个partitions 为复数格式,后面带s的。
 
查看数据目录可看出已经分区成功了;
 
  1. [root@agent tube]# ll t3* 
  2. -rw-rw---- 1 mysql mysql  8652 Jan  5 16:11 t3.frm 
  3. -rw-rw---- 1 mysql mysql    32 Jan  5 16:11 t3.par 
  4. -rw-rw---- 1 mysql mysql 98304 Jan  5 16:11 t3#P#p0.ibd 
  5. -rw-rw---- 1 mysql mysql 98304 Jan  5 16:11 t3#P#p1.ibd 
  6. -rw-rw---- 1 mysql mysql 98304 Jan  5 16:11 t3#P#p2.ibd 
  7. -rw-rw---- 1 mysql mysql 98304 Jan  5 16:11 t3#P#p3.ibd 
LINEAR HASH 分区
1、线性与常规哈希的区别在于,线性哈希功能使用的一个线性的2的幂(powers-of-two)运算法则,而常规哈希使用的是求哈希函数值的模数。
2、按照线性哈希分区的优点在于增加、删除、合并和拆分分区将变得更加快捷,有利于处理含有极其大量(1T)数据的表。不过,Mysql的线性哈希算法导致相比较常规哈希,数据可能分布得不那么均衡,空间产生“hostpot nodes”。
 
LINEAR HASH分区案例:
线性哈希分区和常规哈希分区在语法上的唯一区别在于,在“PARTITION BY”子语中添加"LINEAR" 关键字。
 
  1. mysql> create table t4(id int,cid int,name varchar(20),pos_date datetime) 
  2.     -> partition by linear hash(cid) 
  3.     -> partitions 4; 
  4. Query OK, 0 rows affected (0.14 sec) 
查看数据目录可看出已经分区成功了;
 
  1. [root@agent tube]# ll t4* 
  2. -rw-rw---- 1 mysql mysql  8652 Jan  5 16:29 t4.frm 
  3. -rw-rw---- 1 mysql mysql    32 Jan  5 16:29 t4.par 
  4. -rw-rw---- 1 mysql mysql 98304 Jan  5 16:29 t4#P#p0.ibd 
  5. -rw-rw---- 1 mysql mysql 98304 Jan  5 16:29 t4#P#p1.ibd 
  6. -rw-rw---- 1 mysql mysql 98304 Jan  5 16:29 t4#P#p2.ibd 
  7. -rw-rw---- 1 mysql mysql 98304 Jan  5 16:29 t4#P#p3.ibd 
KEY分区 (使用比较少)
按照KEY进行分区类似于按照HASH分区,除了HASH分区使用的用户定义的表达式,而KEY分区的哈希函数是由Mysql服务器提供。Mysql簇(Cluster)使用函数MD5()来实现KEY分区;对于使用其它存储引擎的表,服务器使用其自己内部的哈希函数,这些函数是基于与PASSWORD()一样的运算法则。
 
KEY分区案例:
 
  1. mysql> create table t5(id int,cid int,name varchar(20),pos_date datetime) 
  2.     -> partition by linear key(cid) 
  3.     -> partitions 4; 
  4. Query OK, 0 rows affected (0.12 sec) 
多列分区(Mysql5.5后新增功能,之前的分区都是基于某个字段或列。)
 
COLUMNS关键字允许字符串和日期列作为分区定义列,同时还允许使用多个列定义一个分区。
 
多列分区基本语法:
 
  1. mysql> create table t6(a int,b int,c int) 
  2.     -> partition by range columns(a,b) 
  3.     -> ( 
  4.     -> partition p01 values less than(10,10), 
  5.     -> partition p02 values less than(10,20), 
  6.     -> partition p03 values less than(10,30), 
  7.     -> partition p04 values less than(10,maxvalue), 
  8.     -> partition p05 values less than(maxvalue,maxvalue) 
  9.     -> ); 
  10. Query OK, 0 rows affected (0.66 sec) 
 
多列分区案例:
第一个分区用来存储雇佣于1990年以前的女职员,第二个分区存储雇佣于1990-2000年之间的女职员,第三个分区存储所有剩下的女职员。对于分区p04到p06,我们策略是一样的,只不过存储的是男职员。最后一个分区是控制情况。
 
  1. mysql> create table t7( 
  2.     -> emp_no int, 
  3.     -> birth_date date, 
  4.     -> first_name varchar(20), 
  5.     -> last_name varchar(20), 
  6.     -> gender char(1), 
  7.     -> hire_date date 
  8.     -> )engine=myisam 
  9.     -> partition by range columns(gender,hire_date) 
  10.     -> ( 
  11.     -> partition p01 values less than('F','1990-01-01'), 
  12.     -> partition p02 values less than('F','2000-01-01'), 
  13.     -> partition p03 values less than('F',maxvalue), 
  14.     -> partition p04 values less than('M','1990-01-01'), 
  15.     -> partition p05 values less than('M','2000-01-01'), 
  16.     -> partition p06 values less than('M',maxvalue), 
  17.     -> partition p07 values less than(maxvalue,maxvalue) 
  18.     -> ); 
  19. Query OK, 0 rows affected (0.13 sec) 
查看数据目录可看出已经分区成功了;
 
  1. [root@agent tube]# ll t7* 
  2. -rw-rw---- 1 mysql mysql 8762 Jan  5 16:53 t7.frm 
  3. -rw-rw---- 1 mysql mysql   52 Jan  5 16:53 t7.par 
  4. -rw-rw---- 1 mysql mysql    0 Jan  5 16:53 t7#P#p01.MYD 
  5. -rw-rw---- 1 mysql mysql 1024 Jan  5 16:53 t7#P#p01.MYI 
  6. -rw-rw---- 1 mysql mysql    0 Jan  5 16:53 t7#P#p02.MYD 
  7. -rw-rw---- 1 mysql mysql 1024 Jan  5 16:53 t7#P#p02.MYI 
  8. -rw-rw---- 1 mysql mysql    0 Jan  5 16:53 t7#P#p03.MYD 
  9. -rw-rw---- 1 mysql mysql 1024 Jan  5 16:53 t7#P#p03.MYI 
  10. -rw-rw---- 1 mysql mysql    0 Jan  5 16:53 t7#P#p04.MYD 
  11. -rw-rw---- 1 mysql mysql 1024 Jan  5 16:53 t7#P#p04.MYI 
  12. -rw-rw---- 1 mysql mysql    0 Jan  5 16:53 t7#P#p05.MYD 
  13. -rw-rw---- 1 mysql mysql 1024 Jan  5 16:53 t7#P#p05.MYI 
  14. -rw-rw---- 1 mysql mysql    0 Jan  5 16:53 t7#P#p06.MYD 
  15. -rw-rw---- 1 mysql mysql 1024 Jan  5 16:53 t7#P#p06.MYI 
  16. -rw-rw---- 1 mysql mysql    0 Jan  5 16:53 t7#P#p07.MYD 
  17. -rw-rw---- 1 mysql mysql 1024 Jan  5 16:53 t7#P#p07.MYI 
子分区
1、子分区是分区表中每个分区的再次分割
2、子分区可以用于特别大的表,在多个磁盘间分配数据和索引。
 
  1. mysql> create table t8( 
  2.     -> id int, 
  3.     -> udate date) 
  4.     -> partition by range(year(udate)) 
  5.     -> subpartition by hash (to_days(udate)) 
  6.     -> subpartitions 2 
  7.     -> ( 
  8.     -> partition p01 values less than(1990), 
  9.     -> partition p02 values less than(2000), 
  10.     -> partition p03 values less than(maxvalue) 
  11.     -> ); 
  12. Query OK, 0 rows affected (0.22 sec) 
查看数据目录可看出已经分区成功了;但是命名有所区别。
 
  1. [root@agent tube]# ls|grep t8 
  2. t8.frm 
  3. t8.par 
  4. t8#P#p01#SP#p01sp0.ibd 
  5. t8#P#p01#SP#p01sp1.ibd 
  6. t8#P#p02#SP#p02sp0.ibd 
  7. t8#P#p02#SP#p02sp1.ibd 
  8. t8#P#p03#SP#p03sp0.ibd 
  9. t8#P#p03#SP#p03sp1.ibd 
 
子分区案例:
1、将每个子分区保存在不同的存储上,优化I/O性能。
创建四个目录,并授权mysql权限,这几个目录当作四块磁盘;
 
  1. [root@agent tube]# mkdir /var/{a,b,c,d}  
  2. [root@agent tube]# mkdir /var/{a,b,c,d}/data 
  3. [root@agent tube]# mkdir /var/{a,b,c,d}/idx 
  4. [root@agent tube]# chown -R mysql. /var/{a,b,c,d} 
  5.  
  6. [root@agent tube]# ll -d /var/{a,b,c,d} 
  7. drwxr-xr-x 4 mysql mysql 4096 Jan  5 20:48 /var/a 
  8. drwxr-xr-x 4 mysql mysql 4096 Jan  5 20:48 /var/b 
  9. drwxr-xr-x 4 mysql mysql 4096 Jan  5 20:48 /var/c 
  10. drwxr-xr-x 4 mysql mysql 4096 Jan  5 20:48 /var/d 
建表过程:
 
  1. mysql> create table t9(id int,udate date) 
  2.     -> partition by range (year(udate)) 
  3.     -> subpartition by hash (to_days(udate)) 
  4.     -> ( 
  5.     -> partition p01 values less than(1990) 
  6.     -> ( 
  7.     -> subpartition s0 
  8.     -> data directory='/var/a/data' 
  9.     -> index directory='/var/a/idx'
  10.     -> subpartition s1 
  11.     -> data directory='/var/b/data' 
  12.     -> index directory='/var/b/idx' 
  13.     -> ), 
  14.     -> partition p02 values less than(2000) 
  15.     -> ( 
  16.     -> subpartition s2 
  17.     -> data directory='/var/c/data' 
  18.     -> index directory='/var/c/idx'
  19.     -> subpartition s3 
  20.     -> data directory='/var/d/data' 
  21.     -> index directory='/var/d/idx' 
  22.     -> ) 
  23.     -> ); 
  24. Query OK, 0 rows affected, 4 warnings (0.22 sec) 
查看数据目录可看出已经分区成功了,这里几个文件后缀是以.isl结尾的,其它这些文件记录了数据存放目录。
通过more可以查看到目录。
 
  1. [root@agent tube]# more t9#P#p01#SP#s0.isl 
  2. /var/a/data/tube/t9#P#p01#SP#s0.ibd 
此路径正是刚才新建的目录位置;
 
  1. [root@agent tube]# ls |grep t9 
  2. t9.frm 
  3. t9.par 
  4. t9#P#p01#SP#s0.isl 
  5. t9#P#p01#SP#s1.isl 
  6. t9#P#p02#SP#s2.isl 
  7. t9#P#p02#SP#s3.isl 
而数据存放的目录/var/{a,b,c,d}目录结构:
 
  1. [root@agent tube]# find /var/{a,b,c,d} -type f   
  2. /var/a/data/tube/t9#P#p01#SP#s0.ibd 
  3. /var/b/data/tube/t9#P#p01#SP#s1.ibd 
  4. /var/c/data/tube/t9#P#p02#SP#s2.ibd 
  5. /var/d/data/tube/t9#P#p02#SP#s3.ibd 
假若上面的四个目录就是真正的四块硬盘,那么这样一来就可以有效减少磁盘IO压力。
 
了分区语法要求:
1、每个分区必须有相同数量的子分区、
2、如果在一个分区表上的任何分区上使用SUBPARTITION来明确定义任何子分区,那么就必须定义所有的子分区。
 
分区管理,切记切记不要在生产库上直接修改表分区类型,因为生产库上已经存在大量的数据记录,哪怕不丢数据,修改也会导致锁表等情况,导致业务无法使用。
 
一、修改表分区类型:
t1:原来是range分区,并且有四个分区;
 
  1. mysql> show create table t1\G 
  2. *************************** 1. row *************************** 
  3.        Table: t1 
  4. Create Table: CREATE TABLE `t1` ( 
  5.   `id` int(11) DEFAULT NULL, 
  6.   `name` varchar(20) DEFAULT NULL, 
  7.   `age` int(11) DEFAULT NULL 
  8. ENGINE=InnoDB DEFAULT CHARSET=utf8 
  9. /*!50100 PARTITION BY RANGE (age) 
  10. (PARTITION p01 VALUES LESS THAN (10) ENGINE = InnoDB
  11.  PARTITION p02 VALUES LESS THAN (20) ENGINE = InnoDB
  12.  PARTITION p03 VALUES LESS THAN (30) ENGINE = InnoDB
  13.  PARTITION p04 VALUES LESS THAN MAXVALUE ENGINE = InnoDB) */ 
  14. 1 row in set (0.03 sec) 
 
修改为hash分区,二个分区;
 
  1. mysql> alter table t1 partition by hash(age) partitions 2; 
  2. Query OK, 0 rows affected (0.30 sec) 
  3. Records: 0  Duplicates: 0  Warnings: 0 
  4.  
  5. mysql> show create table t1\G 
  6. *************************** 1. row *************************** 
  7.        Table: t1 
  8. Create Table: CREATE TABLE `t1` ( 
  9.   `id` int(11) DEFAULT NULL, 
  10.   `name` varchar(20) DEFAULT NULL, 
  11.   `age` int(11) DEFAULT NULL 
  12. ) ENGINE=InnoDB DEFAULT CHARSET=utf8 
  13. /*!50100 PARTITION BY HASH (age) 
  14. PARTITIONS 2 */ 
  15. 1 row in set (0.01 sec) 
再次把分区改回去呢?
 
  1. mysql> alter table t1 partition by range(age) 
  2.     -> ( 
  3.     -> partition p01 values less than(10), 
  4.     -> partition p02 values less than(20), 
  5.     -> partition p03 values less than(30), 
  6.     -> partition p04 values less than(maxvalue) 
  7.     -> ); 
  8. Query OK, 0 rows affected (0.16 sec) 
  9. Records: 0  Duplicates: 0  Warnings: 0 
再次查看表属性:
 
  1. mysql> show create table t1\G 
  2. *************************** 1. row *************************** 
  3.        Table: t1 
  4. Create Table: CREATE TABLE `t1` ( 
  5.   `id` int(11) DEFAULT NULL, 
  6.   `name` varchar(20) DEFAULT NULL, 
  7.   `age` int(11) DEFAULT NULL 
  8. ) ENGINE=InnoDB DEFAULT CHARSET=utf8 
  9. /*!50100 PARTITION BY RANGE (age) 
  10. (PARTITION p01 VALUES LESS THAN (10) ENGINE = InnoDB, 
  11.  PARTITION p02 VALUES LESS THAN (20) ENGINE = InnoDB, 
  12.  PARTITION p03 VALUES LESS THAN (30) ENGINE = InnoDB, 
  13.  PARTITION p04 VALUES LESS THAN MAXVALUE ENGINE = InnoDB) */ 
  14. 1 row in set (0.04 sec) 
二、RANGE&LIST分区之删除指定分区
删除方法很简单,但是注意删除分区也同时删除了该分区中所有的数据。
例子:
删除表分区:(同时也会删除分区内数据)
 
  1. alter table t1 drop partition p01; 
只清空分区数据不删除分区:
 
  1. alter table t1 truncate partition p01; 
清空表分区在生产线上是常见的。
 
三、RANGE分区之增加分区
要增加一个新的RANGE或LIST分区到一个前面已经分区了的表,使用"ALTER TABLE ... ADD PARTITION"语句,对于使用RANGE分区的表,可以用这个语句添加新的分区到已有分区的序列的前面或后面。
注意:对于RANGE分区的表,只可使用ADD PARTITION添加新的分区到分区列表的高端。
例如下:(RANGE表分区后不能带MAXVALUE分区,否则无法增加分区,分报错:ERROR 1481 (HY000): MAXVALUE can only be used in last partition definition)
 
 
  1. mysql> show create table t1\G 
  2. *************************** 1. row *************************** 
  3.        Table: t1 
  4. Create Table: CREATE TABLE `t1` ( 
  5.   `id` int(11) DEFAULT NULL, 
  6.   `name` varchar(20) DEFAULT NULL, 
  7.   `age` int(11) DEFAULT NULL 
  8. ENGINE=InnoDB DEFAULT CHARSET=utf8 
  9. /*!50100 PARTITION BY RANGE (age) 
  10. (PARTITION p01 VALUES LESS THAN (10) ENGINE = InnoDB
  11.  PARTITION p02 VALUES LESS THAN (20) ENGINE = InnoDB
  12.  PARTITION p03 VALUES LESS THAN (30) ENGINE = InnoDB) */ 
  13. 1 row in set (0.02 sec) 
  14.  
  15. mysql> alter table t1 add partition(partition p04 values less than(40)); 
  16. Query OK, 0 rows affected (0.10 sec) 
  17. Records: 0  Duplicates: 0  Warnings: 0 
成功增加一个分区。
 
 
LIST分区之增加分区
对于LIST分区增加分区的语法和RANGE类似。
注意:增加新分区时,不可以包含现在分区值列表中的任意值。
 
例子:
 
  1. mysql> show create table t2\G 
  2. *************************** 1. row *************************** 
  3.        Table: t2 
  4. Create Table: CREATE TABLE `t2` ( 
  5.   `id` int(11) DEFAULT NULL, 
  6.   `cid` int(11) DEFAULT NULL, 
  7.   `name` varchar(20) DEFAULT NULL, 
  8.   `pos_date` datetime DEFAULT NULL 
  9. ) ENGINE=InnoDB DEFAULT CHARSET=utf8 
  10. /*!50100 PARTITION BY LIST (cid) 
  11. (PARTITION p01 VALUES IN (1,4,12) ENGINE = InnoDB, 
  12.  PARTITION p02 VALUES IN (2,6,9) ENGINE = InnoDB) */ 
  13. 1 row in set (0.00 sec) 
 
上面分区中没有被分区到的序列(3,5,7,8等。)
增加分区方法:
 
  1. mysql> alter table t2 add partition(partition p03 values in(3,5,7));  
  2. Query OK, 0 rows affected (0.06 sec) 
  3. Records: 0  Duplicates: 0  Warnings: 0 
 
 
四、分区重组
1、使用REORGANIZE可以对现有的分区进行重组。这样可以实现将一个已存在的分区重分成多个分区,也可以实现将多个分区合并成一个分区。
2、注意:新分区模式不能有任何重叠的区间(适用于按照RANGE分区的表)或值集合(适用于重新组织按照LIST分区的表),也必须覆盖原有区间。
3、另外,对于按照RANGE分区的表,只能重新组织相邻的分区;不能跳过RANGE分区。
 
例子:(拆分新的子分区)
 
  1. mysql> show create table t1\G 
  2. *************************** 1. row *************************** 
  3.        Table: t1 
  4. Create Table: CREATE TABLE `t1` ( 
  5.   `id` int(11) DEFAULT NULL, 
  6.   `name` varchar(20) DEFAULT NULL, 
  7.   `age` int(11) DEFAULT NULL 
  8. ENGINE=InnoDB DEFAULT CHARSET=utf8 
  9. /*!50100 PARTITION BY RANGE (age) 
  10. (PARTITION p01 VALUES LESS THAN (10) ENGINE = InnoDB
  11.  PARTITION p02 VALUES LESS THAN (20) ENGINE = InnoDB
  12.  PARTITION p03 VALUES LESS THAN (30) ENGINE = InnoDB
  13.  PARTITION p04 VALUES LESS THAN (40) ENGINE = InnoDB) */ 
  14. 1 row in set (0.00 sec) 
  15.  
  16. 在原来p01分区基础上拆分现个小分区。 
  17. mysql> alter table t1 reorganize partition p01 into( 
  18.     -> partition s0 values less than(5), 
  19.     -> partition s1 values less than(10)  
  20.     -> ); 
  21. Query OK, 0 rows affected (0.12 sec) 
  22. Records: 0  Duplicates: 0  Warnings: 0 
文件目录也相对改变了;
 
  1. [root@agent tube]# ll |grep t1 
  2. -rw-rw---- 1 mysql mysql  8614 Jan  6 08:41 t1.frm 
  3. -rw-rw---- 1 mysql mysql    44 Jan  6 08:41 t1.par 
  4. -rw-rw---- 1 mysql mysql 98304 Jan  5 21:15 t1#P#p02.ibd 
  5. -rw-rw---- 1 mysql mysql 98304 Jan  5 21:15 t1#P#p03.ibd 
  6. -rw-rw---- 1 mysql mysql 98304 Jan  5 21:49 t1#P#p04.ibd 
  7. -rw-rw---- 1 mysql mysql 98304 Jan  6 08:41 t1#P#s0.ibd 
  8. -rw-rw---- 1 mysql mysql 98304 Jan  6 08:41 t1#P#s1.ibd 
例子:(合并子分区,把上面的分区再次合并)
 
  1. mysql> alter table t1 reorganize partition s0,s1 into
  2.     -> partition p01 values less than(10) 
  3.     -> ); 
  4. Query OK, 0 rows affected (0.08 sec) 
  5. Records: 0  Duplicates: 0  Warnings: 0 
再次查看表属性:已经变成原来的样子;
 
  1. mysql> show create table t1\G 
  2. *************************** 1. row *************************** 
  3.        Table: t1 
  4. Create Table: CREATE TABLE `t1` ( 
  5.   `id` int(11) DEFAULT NULL, 
  6.   `name` varchar(20) DEFAULT NULL, 
  7.   `age` int(11) DEFAULT NULL 
  8. ) ENGINE=InnoDB DEFAULT CHARSET=utf8 
  9. /*!50100 PARTITION BY RANGE (age) 
  10. (PARTITION p01 VALUES LESS THAN (10) ENGINE = InnoDB, 
  11.  PARTITION p02 VALUES LESS THAN (20) ENGINE = InnoDB, 
  12.  PARTITION p03 VALUES LESS THAN (30) ENGINE = InnoDB, 
  13.  PARTITION p04 VALUES LESS THAN (40) ENGINE = InnoDB) */ 
  14. 1 row in set (0.02 sec) 
目录文件也相应改变了:
 
  1. [root@agent tube]# ll |grep t1 
  2. -rw-rw---- 1 mysql mysql  8614 Jan  6 08:44 t1.frm 
  3. -rw-rw---- 1 mysql mysql    36 Jan  6 08:44 t1.par 
  4. -rw-rw---- 1 mysql mysql 98304 Jan  6 08:44 t1#P#p01.ibd 
  5. -rw-rw---- 1 mysql mysql 98304 Jan  5 21:15 t1#P#p02.ibd 
  6. -rw-rw---- 1 mysql mysql 98304 Jan  5 21:15 t1#P#p03.ibd 
  7. -rw-rw---- 1 mysql mysql 98304 Jan  5 21:49 t1#P#p04.ibd 
HASH&KEY分区之管理分区数量
对于HASH分区和KEY分区,可以使用COALESCE缩减分区的数量,使用"ALTER TABLE ... ADD PARTITION" 增加分区数量。
 
例子:(原来HASH分区,有4个分区)
 
  1. mysql> show create table t3\G 
  2. *************************** 1. row *************************** 
  3.        Table: t3 
  4. Create Table: CREATE TABLE `t3` ( 
  5.   `id` int(11) DEFAULT NULL, 
  6.   `cid` int(11) DEFAULT NULL, 
  7.   `name` varchar(20) DEFAULT NULL, 
  8.   `pos_date` datetime DEFAULT NULL 
  9. ) ENGINE=InnoDB DEFAULT CHARSET=utf8 
  10. /*!50100 PARTITION BY HASH (cid) 
  11. PARTITIONS 4 */ 
  12. 1 row in set (0.01 sec) 
把上面的4个分区缩减为2个分区;
 
  1. mysql> alter table t3 coalesce partition 2; 
  2. Query OK, 0 rows affected (0.12 sec) 
  3. Records: 0  Duplicates: 0  Warnings: 0 
查看效果:(成功缩减分区)
 
  1. mysql> show create table t3\G 
  2. *************************** 1. row *************************** 
  3.        Table: t3 
  4. Create Table: CREATE TABLE `t3` ( 
  5.   `id` int(11) DEFAULT NULL, 
  6.   `cid` int(11) DEFAULT NULL, 
  7.   `name` varchar(20) DEFAULT NULL, 
  8.   `pos_date` datetime DEFAULT NULL 
  9. ) ENGINE=InnoDB DEFAULT CHARSET=utf8 
  10. /*!50100 PARTITION BY HASH (cid) 
  11. PARTITIONS 2 */ 
  12. 1 row in set (0.02 sec) 
 
例子:增加分区的方法(把上面缩减后2个分区再加2个分区,就等于有4个分区了)
 
  1. mysql> alter table t3 add partition partitions 2; 
  2. Query OK, 0 rows affected (0.12 sec) 
  3. Records: 0  Duplicates: 0  Warnings: 0 
查看效果:(成功增加2个分区了,目前4个分区)
 
  1. mysql> show create table t3\G 
  2. *************************** 1. row *************************** 
  3.        Table: t3 
  4. Create Table: CREATE TABLE `t3` ( 
  5.   `id` int(11) DEFAULT NULL, 
  6.   `cid` int(11) DEFAULT NULL, 
  7.   `name` varchar(20) DEFAULT NULL, 
  8.   `pos_date` datetime DEFAULT NULL 
  9. ) ENGINE=InnoDB DEFAULT CHARSET=utf8 
  10. /*!50100 PARTITION BY HASH (cid) 
  11. PARTITIONS 4 */ 
  12. 1 row in set (0.00 sec) 
 
重建分区
对于分区表,Mysql就不再支持check table ,optimize table ,analyze table或repair table (这些命令针对Myisam引擎,修复表用,解决碎片问题)。
那么如果真出现了分区问题,那么如何解决问题呢?REBUILD 
过程繁琐,大致思路:
把保存在原来分区上的记录导出,再把分区删除,再把导出的数据记录导入,这样能有效整理分区碎片问题。
例子:(假设t1表分区p01和p02有问题,要重建)
 
  1. mysql> show create table t1\G 
  2. *************************** 1. row *************************** 
  3.        Table: t1 
  4. Create Table: CREATE TABLE `t1` ( 
  5.   `id` int(11) DEFAULT NULL, 
  6.   `name` varchar(20) DEFAULT NULL, 
  7.   `age` int(11) DEFAULT NULL 
  8. ) ENGINE=InnoDB DEFAULT CHARSET=utf8 
  9. /*!50100 PARTITION BY RANGE (age) 
  10. (PARTITION p01 VALUES LESS THAN (10) ENGINE = InnoDB, 
  11.  PARTITION p02 VALUES LESS THAN (20) ENGINE = InnoDB, 
  12.  PARTITION p03 VALUES LESS THAN (30) ENGINE = InnoDB, 
  13.  PARTITION p04 VALUES LESS THAN (40) ENGINE = InnoDB) */ 
  14. 1 row in set (0.00 sec) 
执行重建命令:
 
  1. mysql> alter table t1 rebuild partition p01,p02; 
  2. Query OK, 0 rows affected (0.09 sec) 
  3. Records: 0  Duplicates: 0  Warnings: 0 
 
注意:重建过程就等于把分区删除再建立分区,要点像格式化数据一样。数据会被清空。不到万不得而不要使用。
 
 
优化分区:
1、在分区中删除了大量的行(记录),或者对一个带有可变长度的行(也就是说有varchar,blob,或者text类型的列)作了许多修改,可以用"ALTER  TABLE ... OPTIMIZE PARTITION"来回收没有使用的空间。并整理分区数据碎片问题。
例子:
 
  1. mysql> show create table t1\G 
  2. *************************** 1. row *************************** 
  3.        Table: t1 
  4. Create Table: CREATE TABLE `t1` ( 
  5.   `id` int(11) DEFAULT NULL, 
  6.   `name` varchar(20) DEFAULT NULL, 
  7.   `age` int(11) DEFAULT NULL 
  8. ) ENGINE=InnoDB DEFAULT CHARSET=utf8 
  9. /*!50100 PARTITION BY RANGE (age) 
  10. (PARTITION p01 VALUES LESS THAN (10) ENGINE = InnoDB, 
  11.  PARTITION p02 VALUES LESS THAN (20) ENGINE = InnoDB, 
  12.  PARTITION p03 VALUES LESS THAN (30) ENGINE = InnoDB, 
  13.  PARTITION p04 VALUES LESS THAN (40) ENGINE = InnoDB) */ 
  14. 1 row in set (0.00 sec) 
在Innodb引擎中执行后返回效果:
 
  1. mysql> alter table t1 optimize partition p01,p02; 
  2. +---------+----------+----------+---------------------------------------------------------------------------------------------+ 
  3. | Table   | Op       | Msg_type | Msg_text                                                                                    | 
  4. +---------+----------+----------+---------------------------------------------------------------------------------------------+ 
  5. | tube.t1 | optimize | note     | Table does not support optimize on partitions. All partitions will be rebuilt and analyzed. | 
  6. | tube.t1 | optimize | status   | OK                                                                                          | 
  7. +---------+----------+----------+---------------------------------------------------------------------------------------------+ 
  8. 2 rows in set (0.49 sec) 
 
 
检查分区:
 
  1. mysql> alter table t1 check partition p01,p02; 
  2. +---------+-------+----------+----------+ 
  3. | Table   | Op    | Msg_type | Msg_text | 
  4. +---------+-------+----------+----------+ 
  5. | tube.t1 | check | status   | OK       | 
  6. +---------+-------+----------+----------+ 
  7. 1 row in set (0.00 sec) 
修复分区:(一般在服务异常关闭,如断电造成的表分区损坏问题影响)
 
  1. mysql> alter table t1 repair partition p01,p02;      
  2. +---------+--------+----------+----------+ 
  3. | Table   | Op     | Msg_type | Msg_text | 
  4. +---------+--------+----------+----------+ 
  5. | tube.t1 | repair | status   | OK       | 
  6. +---------+--------+----------+----------+ 
  7. 1 row in set (0.00 sec) 
 
总结:
分区是一名DBA必备的知识,根据情况改变分区的策略,不要在生产环境改变分区。在大型互联网企业中,往往将分区与分布式存储、Replication等技术结合在一起使用。