Mysql分区与管理实践过程

  • Mysql分区与管理实践过程已关闭评论
  • 25 views
  • A+
所属分类：MySQL
收 藏

为什么要对表进行分区？

为了改善大型表以及具有各种访问模式的表的可伸缩性，可管理性和提高数据库效率。
分区的一些优点包括：
1、与单个磁盘或文件系统分区相比，可以存储更多的数据。
2、通过删除与增加那些数据有关的分区，很容易地删除或增加那些数据。
3、一些查询可以得到极大的优化。
4、通过跨多个磁盘甚至服务器来分散数据查询。来获得更大的查询吞吐量。
5、Mysql5.5之后支持所有函数的分区优化，限定只查指定的分区。
例：统计查询性别为女的数据；
select count(*) from emplpoyees where gender='F'; 
RANGE分区：
基于属于一个给定连续区间的列值，把多行分配给分区。这些敬意要连续且不能相互重叠，使用VALUES LESS THAN操作符来进行定义.
大致格式如下：【这是在建完表后添加分区的方法】
ALTER TABLE emplpoyees 
PARTITION BY RANGE (store_id) ( 
    PARTITION p0 VALUES LESS THAN (6), 
    PARTITION p1 VALUES LESS THAN (11), 
    PARTITION p2 VALUES LESS THAN (16), 
    PARTITION p3 VALUES LESS THAN (21) 
    PARTITION p3 VALUES LESS THAN (MAXVALUE) 
);
案例：【建表时创建分区】
将用户按照年龄每隔10岁进行分区，注意分区名字基本上遵循其它Mysql标识符应当遵循的原则。分区名是不区分大小写的。
mysql> create database tube; 
mysql> use tube 
mysql> create table t1 (id int,name varchar(20),age int) 
    -> partition by range (age) 
    -> ( 
    -> partition p01 values less than (10), 
    -> partition p02 values less than (20), 
    -> partition p03 values less than (30), 
    -> partition p04 values less than (maxvalue) 
    -> ); 
Query OK, 0 rows affected (0.34 sec)
查看数据目录可看出已经分区成功了；
[root@agent mysql]# cd tube/ 
[root@agent tube]# ll 
total 404 
-rw-rw---- 1 mysql mysql    61 Jan  5 15:38 db.opt 
-rw-rw---- 1 mysql mysql  8614 Jan  5 15:40 t1.frm 
-rw-rw---- 1 mysql mysql    36 Jan  5 15:40 t1.par 
-rw-rw---- 1 mysql mysql 98304 Jan  5 15:40 t1#P#p01.ibd 
-rw-rw---- 1 mysql mysql 98304 Jan  5 15:40 t1#P#p02.ibd 
-rw-rw---- 1 mysql mysql 98304 Jan  5 15:40 t1#P#p03.ibd 
-rw-rw---- 1 mysql mysql 98304 Jan  5 15:40 t1#P#p04.ibd 
[root@agent tube]# file t1.par  
t1.par: data
此文件是保存分区信息的，所有分区的信息都在这里声明出来。
LIST分区
1、类似于按RANGE分区，区别在于LIST分区是基于列值匹配一个离散值集合中的某个值来进行选择。
2、LIST分区是通过使用"PARTITION BY LIST(expr)"来实现，其中“expr”是某列值或一个基于某个列值、并返回一个整数值的表达式，然后通过“VALUES IN(values_list)”
的方式来定义每个分区，其中“value_list”是一个通过逗号分隔的整数列表。
LIST分区案例：
对于一个综合性的网店来说，商品分为诸多种类，我们可以按照商品ID进行RANGE分区，也可以按照商品的类型划分分区。在这个例子中，LIST分区给了我们更多的选择。
mysql> create table t2 (id int,cid int,name varchar(20),pos_date datetime) 
    -> partition by list(cid) 
    -> ( 
    -> partition p01 values in (1,4,12), 
    -> partition p02 values in (2,6,9), 
    -> partition p03 values in (3,5,7,8,10,11) 
    -> ); 
Query OK, 0 rows affected (0.16 sec)
查看数据目录可看出已经分区成功了；
[root@agent tube]# ll t2* 
-rw-rw---- 1 mysql mysql  8652 Jan  5 16:02 t2.frm 
-rw-rw---- 1 mysql mysql    32 Jan  5 16:02 t2.par 
-rw-rw---- 1 mysql mysql 98304 Jan  5 16:02 t2#P#p01.ibd 
-rw-rw---- 1 mysql mysql 98304 Jan  5 16:02 t2#P#p02.ibd 
-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搭配进行调整。
mysql> create table t3(id int,cid int,name varchar(20),pos_date datetime) 
    -> partition by hash (cid) 
    -> partitions 4; 
Query OK, 0 rows affected (0.09 sec)
注意上面语句有点不同，没什么分隔符，并且这个partitions 为复数格式，后面带s的。
查看数据目录可看出已经分区成功了；
[root@agent tube]# ll t3* 
-rw-rw---- 1 mysql mysql  8652 Jan  5 16:11 t3.frm 
-rw-rw---- 1 mysql mysql    32 Jan  5 16:11 t3.par 
-rw-rw---- 1 mysql mysql 98304 Jan  5 16:11 t3#P#p0.ibd 
-rw-rw---- 1 mysql mysql 98304 Jan  5 16:11 t3#P#p1.ibd 
-rw-rw---- 1 mysql mysql 98304 Jan  5 16:11 t3#P#p2.ibd 
-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" 关键字。
mysql> create table t4(id int,cid int,name varchar(20),pos_date datetime) 
    -> partition by linear hash(cid) 
    -> partitions 4; 
Query OK, 0 rows affected (0.14 sec)
查看数据目录可看出已经分区成功了；
[root@agent tube]# ll t4* 
-rw-rw---- 1 mysql mysql  8652 Jan  5 16:29 t4.frm 
-rw-rw---- 1 mysql mysql    32 Jan  5 16:29 t4.par 
-rw-rw---- 1 mysql mysql 98304 Jan  5 16:29 t4#P#p0.ibd 
-rw-rw---- 1 mysql mysql 98304 Jan  5 16:29 t4#P#p1.ibd 
-rw-rw---- 1 mysql mysql 98304 Jan  5 16:29 t4#P#p2.ibd 
-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分区案例：
mysql> create table t5(id int,cid int,name varchar(20),pos_date datetime) 
    -> partition by linear key(cid) 
    -> partitions 4; 
Query OK, 0 rows affected (0.12 sec)
多列分区（Mysql5.5后新增功能，之前的分区都是基于某个字段或列。）
COLUMNS关键字允许字符串和日期列作为分区定义列，同时还允许使用多个列定义一个分区。
多列分区基本语法：
mysql> create table t6(a int,b int,c int) 
    -> partition by range columns(a,b) 
    -> ( 
    -> partition p01 values less than(10,10), 
    -> partition p02 values less than(10,20), 
    -> partition p03 values less than(10,30), 
    -> partition p04 values less than(10,maxvalue), 
    -> partition p05 values less than(maxvalue,maxvalue) 
    -> ); 
Query OK, 0 rows affected (0.66 sec)
多列分区案例：
第一个分区用来存储雇佣于1990年以前的女职员，第二个分区存储雇佣于1990-2000年之间的女职员，第三个分区存储所有剩下的女职员。对于分区p04到p06，我们策略是一样的，只不过存储的是男职员。最后一个分区是控制情况。
mysql> create table t7( 
    -> emp_no int, 
    -> birth_date date, 
    -> first_name varchar(20), 
    -> last_name varchar(20), 
    -> gender char(1), 
    -> hire_date date 
    -> )engine=myisam 
    -> partition by range columns(gender,hire_date) 
    -> ( 
    -> partition p01 values less than('F','1990-01-01'), 
    -> partition p02 values less than('F','2000-01-01'), 
    -> partition p03 values less than('F',maxvalue), 
    -> partition p04 values less than('M','1990-01-01'), 
    -> partition p05 values less than('M','2000-01-01'), 
    -> partition p06 values less than('M',maxvalue), 
    -> partition p07 values less than(maxvalue,maxvalue) 
    -> ); 
Query OK, 0 rows affected (0.13 sec)
查看数据目录可看出已经分区成功了；
[root@agent tube]# ll t7* 
-rw-rw---- 1 mysql mysql 8762 Jan  5 16:53 t7.frm 
-rw-rw---- 1 mysql mysql   52 Jan  5 16:53 t7.par 
-rw-rw---- 1 mysql mysql    0 Jan  5 16:53 t7#P#p01.MYD 
-rw-rw---- 1 mysql mysql 1024 Jan  5 16:53 t7#P#p01.MYI 
-rw-rw---- 1 mysql mysql    0 Jan  5 16:53 t7#P#p02.MYD 
-rw-rw---- 1 mysql mysql 1024 Jan  5 16:53 t7#P#p02.MYI 
-rw-rw---- 1 mysql mysql    0 Jan  5 16:53 t7#P#p03.MYD 
-rw-rw---- 1 mysql mysql 1024 Jan  5 16:53 t7#P#p03.MYI 
-rw-rw---- 1 mysql mysql    0 Jan  5 16:53 t7#P#p04.MYD 
-rw-rw---- 1 mysql mysql 1024 Jan  5 16:53 t7#P#p04.MYI 
-rw-rw---- 1 mysql mysql    0 Jan  5 16:53 t7#P#p05.MYD 
-rw-rw---- 1 mysql mysql 1024 Jan  5 16:53 t7#P#p05.MYI 
-rw-rw---- 1 mysql mysql    0 Jan  5 16:53 t7#P#p06.MYD 
-rw-rw---- 1 mysql mysql 1024 Jan  5 16:53 t7#P#p06.MYI 
-rw-rw---- 1 mysql mysql    0 Jan  5 16:53 t7#P#p07.MYD 
-rw-rw---- 1 mysql mysql 1024 Jan  5 16:53 t7#P#p07.MYI
子分区
1、子分区是分区表中每个分区的再次分割
2、子分区可以用于特别大的表，在多个磁盘间分配数据和索引。
mysql> create table t8( 
    -> id int, 
    -> udate date) 
    -> partition by range(year(udate)) 
    -> subpartition by hash (to_days(udate)) 
    -> subpartitions 2 
    -> ( 
    -> partition p01 values less than(1990), 
    -> partition p02 values less than(2000), 
    -> partition p03 values less than(maxvalue) 
    -> ); 
Query OK, 0 rows affected (0.22 sec)
查看数据目录可看出已经分区成功了；但是命名有所区别。
[root@agent tube]# ls|grep t8 
t8.frm 
t8.par 
t8#P#p01#SP#p01sp0.ibd 
t8#P#p01#SP#p01sp1.ibd 
t8#P#p02#SP#p02sp0.ibd 
t8#P#p02#SP#p02sp1.ibd 
t8#P#p03#SP#p03sp0.ibd 
t8#P#p03#SP#p03sp1.ibd
子分区案例：
1、将每个子分区保存在不同的存储上，优化I/O性能。
创建四个目录，并授权mysql权限，这几个目录当作四块磁盘；
[root@agent tube]# mkdir /var/{a,b,c,d}  
[root@agent tube]# mkdir /var/{a,b,c,d}/data 
[root@agent tube]# mkdir /var/{a,b,c,d}/idx 
[root@agent tube]# chown -R mysql. /var/{a,b,c,d} 
[root@agent tube]# ll -d /var/{a,b,c,d} 
drwxr-xr-x 4 mysql mysql 4096 Jan  5 20:48 /var/a 
drwxr-xr-x 4 mysql mysql 4096 Jan  5 20:48 /var/b 
drwxr-xr-x 4 mysql mysql 4096 Jan  5 20:48 /var/c 
drwxr-xr-x 4 mysql mysql 4096 Jan  5 20:48 /var/d
建表过程：
mysql> create table t9(id int,udate date) 
    -> partition by range (year(udate)) 
    -> subpartition by hash (to_days(udate)) 
    -> ( 
    -> partition p01 values less than(1990) 
    -> ( 
    -> subpartition s0 
    -> data directory='/var/a/data' 
    -> index directory='/var/a/idx', 
    -> subpartition s1 
    -> data directory='/var/b/data' 
    -> index directory='/var/b/idx' 
    -> ), 
    -> partition p02 values less than(2000) 
    -> ( 
    -> subpartition s2 
    -> data directory='/var/c/data' 
    -> index directory='/var/c/idx', 
    -> subpartition s3 
    -> data directory='/var/d/data' 
    -> index directory='/var/d/idx' 
    -> ) 
    -> ); 
Query OK, 0 rows affected, 4 warnings (0.22 sec)
查看数据目录可看出已经分区成功了，这里几个文件后缀是以.isl结尾的，其它这些文件记录了数据存放目录。
通过more可以查看到目录。
[root@agent tube]# more t9#P#p01#SP#s0.isl 
/var/a/data/tube/t9#P#p01#SP#s0.ibd
此路径正是刚才新建的目录位置；
[root@agent tube]# ls |grep t9 
t9.frm 
t9.par 
t9#P#p01#SP#s0.isl 
t9#P#p01#SP#s1.isl 
t9#P#p02#SP#s2.isl 
t9#P#p02#SP#s3.isl
而数据存放的目录/var/{a,b,c,d}目录结构：
[root@agent tube]# find /var/{a,b,c,d} -type f   
/var/a/data/tube/t9#P#p01#SP#s0.ibd 
/var/b/data/tube/t9#P#p01#SP#s1.ibd 
/var/c/data/tube/t9#P#p02#SP#s2.ibd 
/var/d/data/tube/t9#P#p02#SP#s3.ibd
假若上面的四个目录就是真正的四块硬盘，那么这样一来就可以有效减少磁盘IO压力。
了分区语法要求：
1、每个分区必须有相同数量的子分区、
2、如果在一个分区表上的任何分区上使用SUBPARTITION来明确定义任何子分区，那么就必须定义所有的子分区。
分区管理，切记切记不要在生产库上直接修改表分区类型，因为生产库上已经存在大量的数据记录，哪怕不丢数据，修改也会导致锁表等情况，导致业务无法使用。
一、修改表分区类型：
t1:原来是range分区，并且有四个分区；
mysql> show create table t1\G 
*************************** 1. row *************************** 
       Table: t1 
Create Table: CREATE TABLE `t1` ( 
  `id` int(11) DEFAULT NULL, 
  `name` varchar(20) DEFAULT NULL, 
  `age` int(11) DEFAULT NULL 
) ENGINE=InnoDB DEFAULT CHARSET=utf8 
/*!50100 PARTITION BY RANGE (age) 
(PARTITION p01 VALUES LESS THAN (10) ENGINE = InnoDB, 
 PARTITION p02 VALUES LESS THAN (20) ENGINE = InnoDB, 
 PARTITION p03 VALUES LESS THAN (30) ENGINE = InnoDB, 
 PARTITION p04 VALUES LESS THAN MAXVALUE ENGINE = InnoDB) */ 
1 row in set (0.03 sec)
修改为hash分区，二个分区；
mysql> alter table t1 partition by hash(age) partitions 2; 
Query OK, 0 rows affected (0.30 sec) 
Records: 0  Duplicates: 0  Warnings: 0 
 
mysql> show create table t1\G 
*************************** 1. row *************************** 
       Table: t1 
Create Table: CREATE TABLE `t1` ( 
  `id` int(11) DEFAULT NULL, 
  `name` varchar(20) DEFAULT NULL, 
  `age` int(11) DEFAULT NULL 
) ENGINE=InnoDB DEFAULT CHARSET=utf8 
/*!50100 PARTITION BY HASH (age) 
PARTITIONS 2 */ 
1 row in set (0.01 sec)
再次把分区改回去呢？
mysql> alter table t1 partition by range(age) 
    -> ( 
    -> partition p01 values less than(10), 
    -> partition p02 values less than(20), 
    -> partition p03 values less than(30), 
    -> partition p04 values less than(maxvalue) 
    -> ); 
Query OK, 0 rows affected (0.16 sec) 
Records: 0  Duplicates: 0  Warnings: 0
再次查看表属性：
mysql> show create table t1\G 
*************************** 1. row *************************** 
       Table: t1 
Create Table: CREATE TABLE `t1` ( 
  `id` int(11) DEFAULT NULL, 
  `name` varchar(20) DEFAULT NULL, 
  `age` int(11) DEFAULT NULL 
) ENGINE=InnoDB DEFAULT CHARSET=utf8 
/*!50100 PARTITION BY RANGE (age) 
(PARTITION p01 VALUES LESS THAN (10) ENGINE = InnoDB, 
 PARTITION p02 VALUES LESS THAN (20) ENGINE = InnoDB, 
 PARTITION p03 VALUES LESS THAN (30) ENGINE = InnoDB, 
 PARTITION p04 VALUES LESS THAN MAXVALUE ENGINE = InnoDB) */ 
1 row in set (0.04 sec)
二、RANGE&LIST分区之删除指定分区
删除方法很简单，但是注意删除分区也同时删除了该分区中所有的数据。
例子：
删除表分区：（同时也会删除分区内数据）
 alter table t1 drop partition p01;
只清空分区数据不删除分区：
 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）
mysql> show create table t1\G 
*************************** 1. row *************************** 
       Table: t1 
Create Table: CREATE TABLE `t1` ( 
  `id` int(11) DEFAULT NULL, 
  `name` varchar(20) DEFAULT NULL, 
  `age` int(11) DEFAULT NULL 
) ENGINE=InnoDB DEFAULT CHARSET=utf8 
/*!50100 PARTITION BY RANGE (age) 
(PARTITION p01 VALUES LESS THAN (10) ENGINE = InnoDB, 
 PARTITION p02 VALUES LESS THAN (20) ENGINE = InnoDB, 
 PARTITION p03 VALUES LESS THAN (30) ENGINE = InnoDB) */ 
1 row in set (0.02 sec) 

mysql> alter table t1 add partition(partition p04 values less than(40)); 
Query OK, 0 rows affected (0.10 sec) 
Records: 0  Duplicates: 0  Warnings: 0
成功增加一个分区。
LIST分区之增加分区
对于LIST分区增加分区的语法和RANGE类似。
注意：增加新分区时，不可以包含现在分区值列表中的任意值。
例子：
mysql> show create table t2\G 
*************************** 1. row *************************** 
       Table: t2 
Create Table: CREATE TABLE `t2` ( 
  `id` int(11) DEFAULT NULL, 
  `cid` int(11) DEFAULT NULL, 
  `name` varchar(20) DEFAULT NULL, 
  `pos_date` datetime DEFAULT NULL 
) ENGINE=InnoDB DEFAULT CHARSET=utf8 
/*!50100 PARTITION BY LIST (cid) 
(PARTITION p01 VALUES IN (1,4,12) ENGINE = InnoDB, 
 PARTITION p02 VALUES IN (2,6,9) ENGINE = InnoDB) */ 
1 row in set (0.00 sec)
上面分区中没有被分区到的序列（3,5,7,8等。）
增加分区方法：
mysql> alter table t2 add partition(partition p03 values in(3,5,7));  
Query OK, 0 rows affected (0.06 sec) 
Records: 0  Duplicates: 0  Warnings: 0
四、分区重组
1、使用REORGANIZE可以对现有的分区进行重组。这样可以实现将一个已存在的分区重分成多个分区，也可以实现将多个分区合并成一个分区。
2、注意：新分区模式不能有任何重叠的区间（适用于按照RANGE分区的表）或值集合（适用于重新组织按照LIST分区的表），也必须覆盖原有区间。
3、另外，对于按照RANGE分区的表，只能重新组织相邻的分区；不能跳过RANGE分区。
例子：（拆分新的子分区）
mysql> show create table t1\G 
*************************** 1. row *************************** 
       Table: t1 
Create Table: CREATE TABLE `t1` ( 
  `id` int(11) DEFAULT NULL, 
  `name` varchar(20) DEFAULT NULL, 
  `age` int(11) DEFAULT NULL 
) ENGINE=InnoDB DEFAULT CHARSET=utf8 
/*!50100 PARTITION BY RANGE (age) 
(PARTITION p01 VALUES LESS THAN (10) ENGINE = InnoDB, 
 PARTITION p02 VALUES LESS THAN (20) ENGINE = InnoDB, 
 PARTITION p03 VALUES LESS THAN (30) ENGINE = InnoDB, 
 PARTITION p04 VALUES LESS THAN (40) ENGINE = InnoDB) */ 
1 row in set (0.00 sec) 

在原来p01分区基础上拆分现个小分区。 
mysql> alter table t1 reorganize partition p01 into( 
    -> partition s0 values less than(5), 
    -> partition s1 values less than(10)  
    -> ); 
Query OK, 0 rows affected (0.12 sec) 
Records: 0  Duplicates: 0  Warnings: 0
文件目录也相对改变了；
[root@agent tube]# ll |grep t1 
-rw-rw---- 1 mysql mysql  8614 Jan  6 08:41 t1.frm 
-rw-rw---- 1 mysql mysql    44 Jan  6 08:41 t1.par 
-rw-rw---- 1 mysql mysql 98304 Jan  5 21:15 t1#P#p02.ibd 
-rw-rw---- 1 mysql mysql 98304 Jan  5 21:15 t1#P#p03.ibd 
-rw-rw---- 1 mysql mysql 98304 Jan  5 21:49 t1#P#p04.ibd 
-rw-rw---- 1 mysql mysql 98304 Jan  6 08:41 t1#P#s0.ibd 
-rw-rw---- 1 mysql mysql 98304 Jan  6 08:41 t1#P#s1.ibd
例子：（合并子分区，把上面的分区再次合并）
mysql> alter table t1 reorganize partition s0,s1 into( 
    -> partition p01 values less than(10) 
    -> ); 
Query OK, 0 rows affected (0.08 sec) 
Records: 0  Duplicates: 0  Warnings: 0
再次查看表属性：已经变成原来的样子；
mysql> show create table t1\G 
*************************** 1. row *************************** 
       Table: t1 
Create Table: CREATE TABLE `t1` ( 
  `id` int(11) DEFAULT NULL, 
  `name` varchar(20) DEFAULT NULL, 
  `age` int(11) DEFAULT NULL 
) ENGINE=InnoDB DEFAULT CHARSET=utf8 
/*!50100 PARTITION BY RANGE (age) 
(PARTITION p01 VALUES LESS THAN (10) ENGINE = InnoDB, 
 PARTITION p02 VALUES LESS THAN (20) ENGINE = InnoDB, 
 PARTITION p03 VALUES LESS THAN (30) ENGINE = InnoDB, 
 PARTITION p04 VALUES LESS THAN (40) ENGINE = InnoDB) */ 
1 row in set (0.02 sec)
目录文件也相应改变了：
[root@agent tube]# ll |grep t1 
-rw-rw---- 1 mysql mysql  8614 Jan  6 08:44 t1.frm 
-rw-rw---- 1 mysql mysql    36 Jan  6 08:44 t1.par 
-rw-rw---- 1 mysql mysql 98304 Jan  6 08:44 t1#P#p01.ibd 
-rw-rw---- 1 mysql mysql 98304 Jan  5 21:15 t1#P#p02.ibd 
-rw-rw---- 1 mysql mysql 98304 Jan  5 21:15 t1#P#p03.ibd 
-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个分区）
mysql> show create table t3\G 
*************************** 1. row *************************** 
       Table: t3 
Create Table: CREATE TABLE `t3` ( 
  `id` int(11) DEFAULT NULL, 
  `cid` int(11) DEFAULT NULL, 
  `name` varchar(20) DEFAULT NULL, 
  `pos_date` datetime DEFAULT NULL 
) ENGINE=InnoDB DEFAULT CHARSET=utf8 
/*!50100 PARTITION BY HASH (cid) 
PARTITIONS 4 */ 
1 row in set (0.01 sec)
把上面的4个分区缩减为2个分区；
mysql> alter table t3 coalesce partition 2; 
Query OK, 0 rows affected (0.12 sec) 
Records: 0  Duplicates: 0  Warnings: 0
查看效果：（成功缩减分区）
mysql> show create table t3\G 
*************************** 1. row *************************** 
       Table: t3 
Create Table: CREATE TABLE `t3` ( 
  `id` int(11) DEFAULT NULL, 
  `cid` int(11) DEFAULT NULL, 
  `name` varchar(20) DEFAULT NULL, 
  `pos_date` datetime DEFAULT NULL 
) ENGINE=InnoDB DEFAULT CHARSET=utf8 
/*!50100 PARTITION BY HASH (cid) 
PARTITIONS 2 */ 
1 row in set (0.02 sec)
例子：增加分区的方法（把上面缩减后2个分区再加2个分区，就等于有4个分区了）
mysql> alter table t3 add partition partitions 2; 
Query OK, 0 rows affected (0.12 sec) 
Records: 0  Duplicates: 0  Warnings: 0
查看效果：（成功增加2个分区了，目前4个分区）
mysql> show create table t3\G 
*************************** 1. row *************************** 
       Table: t3 
Create Table: CREATE TABLE `t3` ( 
  `id` int(11) DEFAULT NULL, 
  `cid` int(11) DEFAULT NULL, 
  `name` varchar(20) DEFAULT NULL, 
  `pos_date` datetime DEFAULT NULL 
) ENGINE=InnoDB DEFAULT CHARSET=utf8 
/*!50100 PARTITION BY HASH (cid) 
PARTITIONS 4 */ 
1 row in set (0.00 sec)
重建分区
对于分区表，Mysql就不再支持check table ，optimize table ，analyze table或repair table (这些命令针对Myisam引擎，修复表用，解决碎片问题)。
那么如果真出现了分区问题，那么如何解决问题呢？REBUILD
过程繁琐，大致思路：
把保存在原来分区上的记录导出，再把分区删除，再把导出的数据记录导入，这样能有效整理分区碎片问题。
例子：（假设t1表分区p01和p02有问题，要重建）
mysql> show create table t1\G 
*************************** 1. row *************************** 
       Table: t1 
Create Table: CREATE TABLE `t1` ( 
  `id` int(11) DEFAULT NULL, 
  `name` varchar(20) DEFAULT NULL, 
  `age` int(11) DEFAULT NULL 
) ENGINE=InnoDB DEFAULT CHARSET=utf8 
/*!50100 PARTITION BY RANGE (age) 
(PARTITION p01 VALUES LESS THAN (10) ENGINE = InnoDB, 
 PARTITION p02 VALUES LESS THAN (20) ENGINE = InnoDB, 
 PARTITION p03 VALUES LESS THAN (30) ENGINE = InnoDB, 
 PARTITION p04 VALUES LESS THAN (40) ENGINE = InnoDB) */ 
1 row in set (0.00 sec)
执行重建命令：
mysql> alter table t1 rebuild partition p01,p02; 
Query OK, 0 rows affected (0.09 sec) 
Records: 0  Duplicates: 0  Warnings: 0
注意：重建过程就等于把分区删除再建立分区，要点像格式化数据一样。数据会被清空。不到万不得而不要使用。
优化分区：
1、在分区中删除了大量的行（记录），或者对一个带有可变长度的行（也就是说有varchar，blob，或者text类型的列）作了许多修改，可以用"ALTER  TABLE ... OPTIMIZE PARTITION"来回收没有使用的空间。并整理分区数据碎片问题。
例子：
mysql> show create table t1\G 
*************************** 1. row *************************** 
       Table: t1 
Create Table: CREATE TABLE `t1` ( 
  `id` int(11) DEFAULT NULL, 
  `name` varchar(20) DEFAULT NULL, 
  `age` int(11) DEFAULT NULL 
) ENGINE=InnoDB DEFAULT CHARSET=utf8 
/*!50100 PARTITION BY RANGE (age) 
(PARTITION p01 VALUES LESS THAN (10) ENGINE = InnoDB, 
 PARTITION p02 VALUES LESS THAN (20) ENGINE = InnoDB, 
 PARTITION p03 VALUES LESS THAN (30) ENGINE = InnoDB, 
 PARTITION p04 VALUES LESS THAN (40) ENGINE = InnoDB) */ 
1 row in set (0.00 sec)
在Innodb引擎中执行后返回效果：
mysql> alter table t1 optimize partition p01,p02; 
+---------+----------+----------+---------------------------------------------------------------------------------------------+ 
| Table   | Op       | Msg_type | Msg_text                                                                                    | 
+---------+----------+----------+---------------------------------------------------------------------------------------------+ 
| tube.t1 | optimize | note     | Table does not support optimize on partitions. All partitions will be rebuilt and analyzed. | 
| tube.t1 | optimize | status   | OK                                                                                          | 
+---------+----------+----------+---------------------------------------------------------------------------------------------+ 
2 rows in set (0.49 sec)
检查分区：
mysql> alter table t1 check partition p01,p02; 
+---------+-------+----------+----------+ 
| Table   | Op    | Msg_type | Msg_text | 
+---------+-------+----------+----------+ 

| tube.t1 | check | status   | OK       | 
+---------+-------+----------+----------+ 
1 row in set (0.00 sec)
修复分区：（一般在服务异常关闭，如断电造成的表分区损坏问题影响）
mysql> alter table t1 repair partition p01,p02;      
+---------+--------+----------+----------+ 
| Table   | Op     | Msg_type | Msg_text | 
+---------+--------+----------+----------+ 
| tube.t1 | repair | status   | OK       | 
+---------+--------+----------+----------+ 
1 row in set (0.00 sec)
总结：
分区是一名DBA必备的知识，根据情况改变分区的策略，不要在生产环境改变分区。在大型互联网企业中，往往将分区与分布式存储、Replication等技术结合在一起使用。

  • 安卓客户端下载
  • 微信扫一扫
  • weinxin
  • 微信公众号
  • 微信公众号扫一扫
  • weinxin
avatar