- A+
所属分类：未分类
为什么要对表进行分区？
为了改善大型表以及具有各种访问模式的表的可伸缩性，可管理性和提高数据库效率。
分区的一些优点包括：
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等技术结合在一起使用。
- 安卓客户端下载
- 微信扫一扫
-
- 微信公众号
- 微信公众号扫一扫
-
