Jan 06, 2016

Mysql分区与管理实践过程

为什么要对表进行分区？

为了改善大型表以及具有各种访问模式的表的可伸缩性，可管理性和提高数据库效率。
分区的一些优点包括：

与单个磁盘或文件系统分区相比，可以存储更多的数据。
通过删除与增加那些数据有关的分区，很容易地删除或增加那些数据。
一些查询可以得到极大的优化。
通过跨多个磁盘甚至服务器来分散数据查询。来获得更大的查询吞吐量。
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分区

类似于按RANGE分区，区别在于LIST分区是基于列值匹配一个离散值集合中的某个值来进行选择。
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分区

基于用户定义的表达式的返回值来进行选择的分区，该表达式使用将要插入到表中的这些行的列值进行计算。这个函数可以包含Mysql中有效的、产生非负整数值的任何表达式。
要使用HASH分区来分割一个表，要在CREATE TABLE语句上添加一个“PARTITION BY HASH(expr)”子句，其中“expr”是一个返回一个整数的表达式。它可仅仅是字段类型为Mysql整型的一列的名字。
使用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 分区

线性与常规哈希的区别在于，线性哈希功能使用的一个线性的2的幂（powers-of-two）运算法则，而常规哈希使用的是求哈希函数值的模数。
按照线性哈希分区的优点在于增加、删除、合并和拆分分区将变得更加快捷，有利于处理含有极其大量（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压力。

了分区语法要求：

每个分区必须有相同数量的子分区、
如果在一个分区表上的任何分区上使用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等技术结合在一起使用。

如本文“对您有用”，欢迎收藏或转发，让我们坚持创作！

提醒：本文最后更新于 2818 天前，文中所描述的信息可能已发生改变，请谨慎使用。

久未惹尘埃
封尘网 ,58jb.com 网站站长。

本来无一物，何处惹尘埃！水瓶座大叔，喜欢美食,喜欢技术.

本文作者

封尘网

Mysql分区与管理实践过程

RANGE分区：

LIST分区

HASH分区

LINEAR HASH 分区

KEY分区 （使用比较少）

KEY分区（使用比较少）