运维利器之mysql的分区管理和维护
  zZHnZavbRDNq 2023年11月02日 16 0


运维利器之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)

结束语

还是那句话,明天的美好都是自己创造出来的,加油吧,少年


【版权声明】本文内容来自摩杜云社区用户原创、第三方投稿、转载，内容版权归原作者所有。本网站的目的在于传递更多信息，不拥有版权，亦不承担相应法律责任。如果您发现本社区中有涉嫌抄袭的内容，欢迎发送邮件进行举报，并提供相关证据，一经查实，本社区将立刻删除涉嫌侵权内容，举报邮箱： cloudbbs@moduyun.com
  1. 分享：
最后一次编辑于 2023年11月08日 0

暂无评论

推荐阅读
  google pwa   2024年04月23日   96   0   0 数据数据数据数据
  Smartproxy   2024年05月06日   82   0   0 linux数据python数据Javalinux数据python数据
  Smartproxy   2024年04月19日   59   0   0 linux数据java数据JavaMySQL资讯linux数据数据
  google pwa   2024年04月19日   104   0   0 数据数据数据数据
  google pwa   2024年04月16日   103   0   1 数据数据数据数据
  google pwa   2024年04月15日   187   0   4 数据数据数据数据
  google pwa   2024年04月22日   83   0   0 数据数据数据数据
  Smartproxy   2024年04月15日   229   0   2 linux数据pythonjava数据Javalinux数据python数据
  Smartproxy   2024年04月29日   62   0   0 linux数据pythonjava数据Javalinux数据python数据
  Smartproxy   2024年04月26日   78   0   0 linux数据java数据MySQL资讯redislinux数据数据
  Smartproxy   27天前   82   0   0 数据pythonjava数据Javaredis数据python数据
  Smartproxy   2024年05月09日   123   0   0 linux数据数据linux数据数据
  google pwa   2024年04月17日   104   0   1 数据数据数据数据
  Smartproxy   2024年05月09日   222   0   0 数据java数据Java数据数据
  google pwa   2024年04月18日   225   0   0 数据数据数据数据
  Smartproxy   2024年04月17日   90   0   0 数据java数据数据数据
  Smartproxy   2024年05月10日   117   0   0 linux数据python数据Javalinux数据python数据
  Smartproxy   2024年04月18日   88   0   0 linux数据python数据Javalinux数据python数据
  Smartproxy   2024年05月10日   122   0   0 数据pythonjava数据Java信息系统数据python数据
  Smartproxy   2024年04月29日   81   0   0 数据pythonjava数据Java数据python数据
zZHnZavbRDNq
zZHnZavbRDNq
作者其他文章 更多
用别人的网站多不舒服，自己手撸一个密码批量生成器网站

2023-11-13

如此简单的k8s，快速玩转ingress

2023-11-13

海洋馆一日游

2023-11-13

原来都是crontab惹的祸(inode 100%处理解决)，服务器系统差点重置了/呜呜呜

2023-11-13

kubernetes v1.20项目之二进制部署Nginx(四层)负载均衡器出现unknown directive “stream“ in /etc/nginx/nginx.conf问题的解决

2023-11-13

python制作的点名神器（代码简单，适合初学者），老师再也不用发愁点名了

2023-11-13

经过了那么长的时间使用python不自己做个平台可惜了（逐步完善中）

2023-11-13

nifi之将已经定义好的数据进行取值并拼装成sql语句(5)

2023-11-13

dell服务器出现Configuring Memory...........Done解决方法

2023-11-13

nifi小试牛刀至第一次

2023-11-13

最新推荐 更多
终于搞懂了！原来 Vue 3 的 generate 是这样生成 render 函数的

2024-05-20

博客园美化：增加顶部炫彩loading进度条

2024-05-20

lodash已死？radash库方法介绍及源码解析 —— 函数柯里化 + Number篇

2024-05-20

TypeScript入门介绍

2024-05-20

XML Schema 复杂元素类型详解：定义及示例解析

2024-05-20

什么是单点登录？如何实现？

2024-05-20

基于uniapp+vue3自定义增强版table表格组件「兼容H5+小程序+App端」

2024-05-18

解释下什么是事件代理？应用场景？

2024-05-18

Vue项目中有封装过axios吗？主要是封装哪方面的？

2024-05-17

浅谈Vue.js与原生开发

2024-05-17

vue要做权限管理该怎么做？如果控制到按钮级别的权限怎么做？

2024-05-17

Vue模板语法、属性绑定、条件渲染的学习

2024-05-17

vue3编译优化之“静态提升”

2024-05-17

VUE-局部使用

2024-05-17

你是怎么处理vue项目中的错误的？

2024-05-17

实现抖音 “视频无限滑动“效果

2024-05-17

说说webpack proxy工作原理？为什么能解决跨域?

2024-05-17

我为什么还要造一个前端轮子？

2024-05-17

一款摸鱼神器！帮助你利用上班时间背单词！

2024-05-17

next-route

2024-05-17

常用标签
数据数据数据数据javalinuxlinuxpythonCentOSredisJavapython区块链技术区块链信息系统字符串MySQL资讯项目管理mysql教程MySQL