文档章节

对MySQL交换分区的实践

star_glm
 star_glm
发布于 2017/07/03 17:55
字数 1274
阅读 2132
收藏 130

前言

在介绍交换分区之前,我们先了解一下 mysql 分区。

数据库的分区有两种:水平分区和垂直分区。而MySQL暂时不支持垂直分区,因此接下来说的都是水平分区。水平分区即:以行为单位对表进行分区。比如:按照时间分区,每一年一个分区等。

在MySQL中,分区是可以交换的,可以将一个分区表中的一个分区和一个普通表中的数据互换。

 

交换分区的实现

1、交换分区的语法

alter table pt exchange partition p with table nt;

解释:

  • 将 分区表pt 的 分区p 和 一个普通表nt 中的数据 进行互换。

交换的前提条件:

  • 普通表nt 不为临时表,且不是分区表。
  • 表结构和分区表pt的结构一致。
  • 普通表nt 没有外键引用。
  • 普用表nt 若为非空。在MySQL5.6以及之前的版本,必须在分区的范围内;在MySQL5.7之后则可以不再其分区范围内,也依然会成功存入该分区中。

2、实验 交换分区

(1) 创建一个分区表,并插入数据

# 创建一个 分区表e
CREATE TABLE e (
    id INT NOT NULL,
    fname VARCHAR(30),
    lname VARCHAR(30)
)
    PARTITION BY RANGE (id) (
        PARTITION p0 VALUES LESS THAN (50),
        PARTITION p1 VALUES LESS THAN (100),
        PARTITION p2 VALUES LESS THAN (150),
        PARTITION p3 VALUES LESS THAN (MAXVALUE)
);

# 向分区表e中插入几条数据
INSERT INTO e VALUES
    (1669, "Jim", "Smith"),
    (337, "Mary", "Jones"),
    (16, "Frank", "White"),
    (2005, "Linda", "Black");

 (2) 创建一个和分区表e结构一致的普通表e2

# 创建一个表e2
mysql> create table e2 like e;

# 删除表e2的分区,使之成为一个普通表
mysql> alter table e2 remove partitioning;

(3) 查看表e的分区中各有多少行

mysql> select PARTITION_NAME, TABLE_ROWS
    ->     FROM INFORMATION_SCHEMA.PARTITIONS
    ->     WHERE TABLE_NAME = 'e';
+----------------+------------+
| PARTITION_NAME | TABLE_ROWS |
+----------------+------------+
| p0             |          1 |
| p1             |          0 |
| p2             |          0 |
| p3             |          3 |
+----------------+------------+
4 rows in set (0.00 sec)

(4) 将表e的分区p0 交换到 普通表e2中

mysql> alter table e exchange partition p0 with table e2;

(5) 查看结果

# 表e中 分区P0的数据已经没有了
mysql> SELECT PARTITION_NAME, TABLE_ROWS
    ->     FROM INFORMATION_SCHEMA.PARTITIONS
    ->     WHERE TABLE_NAME = 'e';
+----------------+------------+
| PARTITION_NAME | TABLE_ROWS |
+----------------+------------+
| p0             |          0 |
| p1             |          0 |
| p2             |          0 |
| p3             |          3 |
+----------------+------------+
4 rows in set (0.00 sec)

# 在表e2中有了 一条来自表e的分区p0 的数据
mysql> SELECT * FROM e2;
+----+-------+-------+
| id | fname | lname |
+----+-------+-------+
| 16 | Frank | White |
+----+-------+-------+
1 row in set (0.00 sec)

由此发现,表e的分区p0的数据 搬移到了 表e2中。

上面的例子,是将一个分区 和 一个普通的空表 交换,这样相当于将一个分区的数据搬移了出去,通常使用在数据的归档中。

同样,交换分区也可以 在一个分区和一个非空普通表中 发生交换,这样普通表中的数据会搬迁到指定分区,该指定分区的数据会搬迁到普通表中。如下操作:

(6) 再向分区表e的p0中添加一条数据

# 在表e中添加一条在分区p0范围内的数据
mysql> INSERT INTO e VALUES (41, "Michael", "Green");            
Query OK, 1 row affected (0.05 sec)                              

# 确认插入的数据确实以存放在分区p0
mysql> SELECT PARTITION_NAME, TABLE_ROWS
    ->     FROM INFORMATION_SCHEMA.PARTITIONS
    ->     WHERE TABLE_NAME = 'e';            
+----------------+------------+             
| PARTITION_NAME | TABLE_ROWS |             
+----------------+------------+             
| p0             |          1 |             
| p1             |          0 |             
| p2             |          0 |             
| p3             |          3 |             
+----------------+------------+             
4 rows in set (0.00 sec)

(7) 执行 交换分区

​mysql> alter table e exchange partition p0 with table e2;
Query OK, 0 rows affected (0.28 sec)

(8) 查看结果

# 之前把表e的p0交换到表e2的一条数据,现在又回到了表e
mysql> SELECT * FROM e;
+------+-------+-------+
| id   | fname | lname |
+------+-------+-------+
|   16 | Frank | White |      
| 1669 | Jim   | Smith |
|  337 | Mary  | Jones |
| 2005 | Linda | Black |
+------+-------+-------+
4 rows in set (0.00 sec)

mysql> SELECT * FROM e2;
+----+---------+-------+
| id | fname   | lname |
+----+---------+-------+
| 41 | Michael | Green |
+----+---------+-------+
1 row in set (0.00 sec)

由此看出,表e的p0 和 表e2 的数据做了互换!这种场景,可以在普通表数据搬移到分区表的某个分区中使用。

但是,有一点需要注意:

若普通表的数据 不在 要交换的分区范围内,上面交换分区的语法,不可以执行成功!请看下面的操作。

(9) 在普通表e2中插入一条超出表e的p0范围的数据,执行上面的交换语句

mysql> INSERT INTO e2 VALUES (51, "Ellen", "McDonald");
Query OK, 1 row affected (0.08 sec)

mysql> alter table e exchange partition p0 with table e2;
ERROR 1707 (HY000): Found row that does not match the partition

在MySQL 5.6 版本中,该问题无法解决;而在MySQL 5.7.5版本中,增加了一个选项 WITHOUT VALIDATION ,可以解决上面的报错。操作如下:

mysql> ALTER TABLE e EXCHANGE PARTITION p0 WITH TABLE e2 WITHOUT VALIDATION;
Query OK, 0 rows affected (0.02 sec)

查看结果:

# id为51的数据也存放在表e的p0中
mysql> select * from e partition(p0);
+------+---------+----------+
| id   | fname   | lname    |
+------+---------+----------+
|   41 | Michael | Green    |
|   51 | Ellen   | McDonald | 
+------+---------+----------+

mysql> SELECT * FROM e2;
+----+---------+-------+
| id | fname   | lname |
+----+---------+-------+
| 16 | Frank   | White |
+----+---------+-------+
1 row in set (0.00 sec)

 

总结

通过上述实践操作发现,MySQL 的交换分区的功能,有两种适用场景:

  1. 分区表的某个分区 和 一个空的普通表 交换,使得 分区表的该分区的数据搬移出去。
  2. 分区表的某个分区 和 一个非空普通表 交换,使得 普通表的数据搬移到分区表的指定分区中。

© 著作权归作者所有

star_glm
粉丝 23
博文 16
码字总数 16450
作品 0
昌平
数据库管理员
私信 提问
加载中

评论(4)

wier
wier
写的不错,不过这个技能点基本用不上
clouddyy
clouddyy
mysql搞这些高科技当心被坑死
star_glm
star_glm 博主

引用来自“mark35”的评论

mysql搞这些高科技当心被坑死
哈哈,还是不敢在mysql上乱搞的,这个尽量不要使用在核心的业务上。
mark35
mark35
mysql搞这些高科技当心被坑死
MariaDB Galera Cluster部署实践

官方文档: 一、 Galera Cluster的工作原理 主要关注点是数据一致性。 事务既可以应用于每个节点,也可以不全部应用。 所以,只要它们配置正确,数据库保持同步。 Galera复制插件不同于传统的...

IT技术栈
2018/04/24
0
0
mysql一直使用swap,导致swap空间用尽变卡

大致操作步骤: 1.调整内核参数,/proc/sys/vm/swapiness 2.调整mysql参数,innodbflushmethod 3.加mysql配置参数memlock,将mysql锁定在内存重防止被swapping out 4.手动释放swap空间,swapo...

jalyzjs
2018/06/26
0
0
linux内核机制常识

数据库系统通常是企业的核心应用,因此针对运行 MySQL 的 Linux 系统通常需要进行一些特殊的优化。 本人非 MySQL 大牛,本文不涉及 MySQL 参数优化,仅对 Linux 系统优化进行一些总结。 CPU ...

fangyiya
2017/04/04
0
0
Linux系统安装

下载地址 挂载(盘符在Linux叫挂在点,目录未挂载点,任何目录可以作为挂在点) 软件包选择

JUKE
2016/11/19
20
0
MySQL服务器硬件和操作系统优化

摘要: 1. 拥有足够的物理内存来把整个InnoDB文件加载到内存中——在内存中访问文件时的速度要比在硬盘中访问时快的多。 2. 不惜一切代价避免使用Swap交换分区 – 交换时是从硬盘读取的,它的...

姬子玉
2017/12/08
0
0

没有更多内容

加载失败,请刷新页面

加载更多

kibana汉化

kibana5 / 6 需要下载补丁包,https://github.com/anbai-inc/Kibana_Hanization 其中 v6 版本原生支持国际化,只需要添加资源文件并且配置即可 kibana7 v7版本官方内置汉化资源,在配置文件 ...

细肉云吞
14分钟前
3
0
spring boot 自定义日志 log4j2

使用默认的日志在实际开发中会存在很多问题,比如备份文件名称无法自动重命名、各个等级的日志被放在一个文件中等,所以实际开发中为了更好满足我们的需求,我们一般都会自定义采用配置的方式...

雷开你的门
18分钟前
5
0
PCB设计-Allegro软件入门系列-设计参数配置(上)

前言 经历了导入网表,和放置器件后,我们就要画板子了,但是必要的设计参数也要先准备好,磨刀不误砍柴工。 《一》显示参数 这里主要设置DRC报错标志大小和飞线显示类型 (1)DRC标志可以适当...

demyar
19分钟前
4
0
js实现微博、微信分享

html <!-- 分享 --><div class="share-box"> <b style="vertical-align: middle;">分享到:</b> <a title="分享到新浪微博" class="shareSina"><span class="share-icon"></span><......

张兴华ZHero
35分钟前
5
0
创龙TMS320DM8168浮点DSP C674x + ARM Cortex-A8的CPU、NAND FLASH、NOR FLASH

TL6678-EasyEVM是广州创龙基于SOM-TL6678核心板而研发的一款多核高性能DSP开发板。开发板采用核心板+底板方式,底板采用沉金无铅工艺的四层板设计,尺寸为200mm*106.65mm,它为用户提供了SOM...

Tronlong创龙
38分钟前
3
0

没有更多内容

加载失败,请刷新页面

加载更多

返回顶部
顶部