尘封网

让学习成为一种习惯!

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等技术结合在一起使用。

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