运维利器之mysql的分区管理和维护
上面的两个博客简单的介绍了一下什么是分区以及子分区,这篇文档主要是介绍一下分区的管理
如果你的数据量较大的话,建议不要轻易尝试下操作**,应为大量的操作会影响I/O性能,可能引发事故的发生**,建议有专业的人员操作,这边文章可以做一个了解
查看当前表分区结构
mysql> show tables;
+-----------------+
| Tables_in_test2 |
+-----------------+
| t1 |
| t11 |
| t12 |
| t2 |
| t3 |
| t4 |
| t5 |
| t6 |
+-----------------+
8 rows in set (0.00 sec)
##可以看见当前的分区结构
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.00 sec)
ERROR:
No query specified
简单的修改一下分区
生产环境建议不要使用,最好是在创建表的时候就规划好,应为这是一个DDL语句
##修改一下分区
mysql> alter table t1 partition by hash(age) partitions 2;
Query OK, 0 rows affected (0.29 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.00 sec)
ERROR:
No query specified
再修改回来之前的分区
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)
-> );
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.00 sec)
ERROR:
No query specified
分区的删除
删除指定的RANGE或者LIST分区非常的简单,但是要注意的是删除分区也同时删除了该分区里面所有的数据,如果仅仅想删除分区中的数据,应该使用TRUNCATE语句
##删除分区
mysql> alter table t1 drop partition p04;
Query OK, 0 rows affected (0.00 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) */
1 row in set (0.00 sec)
ERROR:
No query specified
##清除分区里面的数据
mysql> alter table t1 truncate partition p01;
Query OK, 0 rows affected (0.01 sec)
分区的添加
RANGE分区增加分区
rang分区的表,只可以用ADD PARTITION来添加新的分区到分区列表的高端,也就是说只能往"高的位置添加"
这个也是有需要注意的地方,那就是range分区里面包含了maxvalue这个值得时候,是添加不了分区的,已经没有最高端了,如下面的列子,maxvalue储存在了p04上面,那么这个是添加不了分区的
包含maxvalue,添加不了分区
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.00 sec)
ERROR:
No query specified
mysql> alter table t1 add partition ( partition p05 values less than (40) );
ERROR 1481 (HY000): MAXVALUE can only be used in last partition definition ###报错内容简单明了
####下面是不含maxvalue这个值的时候,添加的分区
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) */ ###此时的最高位是30,那么你添加的分区只能是30以上的
1 row in set (0.00 sec)
ERROR:
No query specified
mysql> alter table t1 add partition ( partition p04 values less than (40) );
Query OK, 0 rows affected (0.00 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.00 sec)
ERROR:
No query specified
LIST分区增加分区
增加分区的时候,不可以包含现有的分区值列表中的任意值.
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,2,3) ENGINE = InnoDB,
PARTITION p02 VALUES IN (4,5,6) ENGINE = InnoDB,
PARTITION p03 VALUES IN (7,8,9) ENGINE = InnoDB) */
1 row in set (0.00 sec)
ERROR:
No query specified
####通过上图可以看出来,这个表是一个list分区,那么在增加分区的时候,你不能在添加原有的分区中的任意值,比如上面的,你就不能添加,1,2,3,4,5,6,7,8,9这几个id值了.
###报错演示
mysql> alter table t2 add partition
-> (
-> partition p04 values in (9,10,11)
-> );
ERROR 1495 (HY000): Multiple definition of same constant in list partitioning #因为之前的分区里面已经含有9这个值了,所以在增加分区的时候不能有之前的任意值.
####添加分区
mysql> alter table t2 add partition ( partition p04 values in (10,11,12) );
Query OK, 0 rows affected (0.00 sec)
Records: 0 Duplicates: 0 Warnings: 0
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,2,3) ENGINE = InnoDB,
PARTITION p02 VALUES IN (4,5,6) ENGINE = InnoDB,
PARTITION p03 VALUES IN (7,8,9) ENGINE = InnoDB,
PARTITION p04 VALUES IN (10,11,12) ENGINE = InnoDB) */
1 row in set (0.01 sec)
ERROR:
No query specified
HASH或者KEY分区的管理
对于HASH分区和KEY分区,可以使用COALESC来缩减分区的数量(在原有的基础上减去的数量),使用ALTER…ADD PARTITION来增加分区的数量(在原有的基础上增加的分区数量)
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)
ERROR:
No query specified
###缩减分区
mysql> alter table t3 coalesce partition 2;
Query OK, 0 rows affected (0.14 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.00 sec)
ERROR:
No query specified
##增加分区
mysql> alter table t3 add partition partitions 4;~
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 6 */
1 row in set (0.00 sec)
ERROR:
No query specified
分区的重组
主要是为了避免出现"热点分区",当一个表已经分好区之后,发现就几个分区,查询操作过于频繁,这就是热点分区,当然我们也是不希望的,所以需要将这些热点分区进行重组.
RANGE分区重组
使用REORGANIZE可以对现有的分区进行重组,这样可以实现将一个已存在的分区分成多个分区,也可以实现将多个分区合并成一个分区
但是要注意䣌是,新分区的模式不能有任何的重叠的空间(适用于按照RANFGE分区的表)或值集合(适用于list分区的表),也必须覆盖原有的区间
对于RANGE分区的表,只能重新组织相邻的分区,不能跳过RANGE分区.,比如之前是10,20,30来进行分区的,但是你重组时候是5,25.也就是跳过了less than20了,这样是不允许的
###将原有的分区进行拆分
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.14 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 s0 VALUES LESS THAN (5) ENGINE = InnoDB,
PARTITION s1 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)
ERROR:
No query specified
##对拆分后的分区进行组合
mysql> alter table t1 reorganize partition s0,s1 into (
-> partition p01 valuesless than (10)
-> );
Query OK, 0 rows affected (0.04 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.00 sec)
ERROR:
No query specified
LIST分区
这里重组的时候需要注意一个问题,就是你在重组的时候,不能落下任意值,比如你的一个分区里面有1,2,3,你不能只把1,2进行了重组,而3没有地方放了,这是不允许的
####分区拆分
mysql> alter table t2 reorganize partition p01 into
-> (
-> partition s0 values in (1,2),
-> partition s1 values in (3)
-> );
Query OK, 0 rows affected (1.68 sec)
Records: 0 Duplicates: 0 Warnings: 0
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 s0 VALUES IN (1,2) ENGINE = InnoDB,
PARTITION s1 VALUES IN (3) ENGINE = InnoDB,
PARTITION p02 VALUES IN (4,5,6) ENGINE = InnoDB,
PARTITION p03 VALUES IN (7,8,9) ENGINE = InnoDB,
PARTITION p04 VALUES IN (10,11,12) ENGINE = InnoDB) */
1 row in set (0.00 sec)
ERROR:
No query specified
###分区组合
mysql> alter table t2 reorganize partition s0,s1 into
-> (
-> partition p01 values in (1,2,3)
-> );
Query OK, 0 rows affected (0.03 sec)
Records: 0 Duplicates: 0 Warnings: 0
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,2,3) ENGINE = InnoDB,
PARTITION p02 VALUES IN (4,5,6) ENGINE = InnoDB,
PARTITION p03 VALUES IN (7,8,9) ENGINE = InnoDB,
PARTITION p04 VALUES IN (10,11,12) ENGINE = InnoDB) */
1 row in set (0.00 sec)
ERROR:
No query specified
重建分区
对于分区表,mysql不支持命令check table,optimize table.analyiz table或者repair tables.作为替代,可以使用ALTER TABLE的许多扩展来进行一个或者多个分区的直接执行这些操作
原理是先保存在删除分区中的所有记录,然后重新的插入他们,也可以用来整理分区碎片,建议轻易不要使用
mysql> alter table t1 rebuild partition p01,p02;
Query OK, 0 rows affected (1.65 sec)
Records: 0 Duplicates: 0 Warnings: 0
优化分区
如果分区中删除了大量的记录,或者做了大量的操作,可能会导致大量的碎片,优化可以回收没有使用的空间,并且可以整理分区数据文件的碎片,如果数据量较大会增加io负担,建议不要轻易尝试
mysql> alter table t1 optimize partition p01,p02;
+----------+----------+----------+---------------------------------------------------------------------------------------------+
| Table | Op | Msg_type | Msg_text |
+----------+----------+----------+---------------------------------------------------------------------------------------------+
| test2.t1 | optimize | note | Table does not support optimize on partitions. All partitions will be rebuilt and analyzed. |
| test2.t1 | optimize | status | OK |
+----------+----------+----------+---------------------------------------------------------------------------------------------+
2 rows in set (0.05 sec)
检查和修复分区
###检查
mysql> alter table t1 check partition p01,p02;
+----------+-------+----------+----------+
| Table | Op | Msg_type | Msg_text |
+----------+-------+----------+----------+
| test2.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 |
+----------+--------+----------+----------+
| test2.t1 | repair | status | OK |
+----------+--------+----------+----------+
1 row in set (0.00 sec)
结束语
还是那句话,明天的美好都是自己创造出来的,加油吧,少年