mysql 在线转换分区表哈希 mysql 分区交换

转载

mob6454cc6bf0b7 2023-10-07 20:02:22

文章标签 mysql 在线转换分区表哈希 mysql 交换分区吗 mysql 数据分区表 文章分类 MySQL 数据库

MySQL 5.6开始支持ALTER TABLE … EXCHANGE PARTITION语句。该语句允许分区或子分区中的数据与另一个非分区的表中的数据进行交换。如果非分区表中的数据为空，那么相当于将分区中的数据移动到非分区表中。若分区表中的数据为空，则相当于将外部表中的数据导入到分区中。

要使用ALTER TABLE … EXCHANGE PARTITION语句，必须满足下面的条件：

1)要交换的表需和分区表有着相同的表结构，但是表不能含有分区；

2)在非分区表中的数据必须在交换的分区定义内；

3)被交换的表中不能含有外键，或者其他的表含有对该表的外键引用；

4)用户除了需要alter、insert和create权限外，还需要drop的权限。

此外，有两个小的细节需要注意：

1)使用该语句时，不会触发交换表和被交换表上的触发器；

2)auto_increment列将被重置；

接着来测试几个例子。

一、分区数据交换到表中

首先创建含有range分区的表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)
);
1
2
3
4
5
6
7
8
9
10
11
CREATETABLEe(
idINTNOTNULL,
fnameVARCHAR(30),
lnameVARCHAR(30)
)
PARTITIONBYRANGE(id)(
PARTITIONp0VALUESLESSTHAN(50),
PARTITIONp1VALUESLESSTHAN(100),
PARTITIONp2VALUESLESSTHAN(150),
PARTITIONp3VALUESLESSTHAN(MAXVALUE)
);
INSERT INTO e VALUES
(1669, "Jim", "Smith"),
(337, "Mary", "Jones"),
(16, "Frank", "White"),
(2005, "Linda", "Black");
1
2
3
4
5
INSERTINTOeVALUES
(1669,"Jim","Smith"),
(337,"Mary","Jones"),
(16,"Frank","White"),
(2005,"Linda","Black");
然后创建交换表e2，表e2的结构和表e一样，但需要注意的是表e2不能含有分区。
mysql> CREATE TABLE e2 LIKE e;
Query OK, 0 rows affected (1.34 sec)
mysql> ALTER TABLE e2 REMOVE PARTITIONING;
Query OK, 0 rows affected (0.90 sec)
Records: 0 Duplicates: 0 Warnings: 0
1
2
3
4
5
6
mysql>CREATETABLEe2LIKEe;
QueryOK,0rowsaffected(1.34sec)
mysql>ALTERTABLEe2REMOVEPARTITIONING;
QueryOK,0rowsaffected(0.90sec)
Records:0Duplicates:0Warnings:0
观察一下分区表中的数据。
mysql> SELECT PARTITION_NAME, TABLE_ROWS FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_NAME = 'e'\G
*************************** 1. row ***************************
PARTITION_NAME: p0
TABLE_ROWS: 1
*************************** 2. row ***************************
PARTITION_NAME: p1
TABLE_ROWS: 0
*************************** 3. row ***************************
PARTITION_NAME: p2
TABLE_ROWS: 0
*************************** 4. row ***************************
PARTITION_NAME: p3
TABLE_ROWS: 3
4 rows in set (0.01 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
mysql>SELECTPARTITION_NAME,TABLE_ROWSFROMINFORMATION_SCHEMA.PARTITIONSWHERETABLE_NAME='e'\G
***************************1.row***************************
PARTITION_NAME:p0
TABLE_ROWS:1
***************************2.row***************************
PARTITION_NAME:p1
TABLE_ROWS:0
***************************3.row***************************
PARTITION_NAME:p2
TABLE_ROWS:0
***************************4.row***************************
PARTITION_NAME:p3
TABLE_ROWS:3
4rowsinset(0.01sec)
因为表e2中的没有数据，使用如下语句将表e的分区p0中的数据移动到表e2中。
mysql> ALTER TABLE e EXCHANGE PARTITION p0 WITH TABLE e2; #交换分区速度非常快;
Query OK, 0 rows affected (0.28 sec)
1
2
mysql>ALTERTABLEeEXCHANGEPARTITIONp0WITHTABLEe2;#交换分区速度非常快;
QueryOK,0rowsaffected(0.28sec)
这时再观察表e中分区的数据，可以发现p0中的数据已经没有了。
mysql> SELECT PARTITION_NAME, TABLE_ROWS FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_NAME = 'e'\G
*************************** 1. row ***************************
PARTITION_NAME: p0
TABLE_ROWS: 0
*************************** 2. row ***************************
PARTITION_NAME: p1
TABLE_ROWS: 0
*************************** 3. row ***************************
PARTITION_NAME: p2
TABLE_ROWS: 0
*************************** 4. row ***************************
PARTITION_NAME: p3
TABLE_ROWS: 3
4 rows in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
mysql>SELECTPARTITION_NAME,TABLE_ROWSFROMINFORMATION_SCHEMA.PARTITIONSWHERETABLE_NAME='e'\G
***************************1.row***************************
PARTITION_NAME:p0
TABLE_ROWS:0
***************************2.row***************************
PARTITION_NAME:p1
TABLE_ROWS:0
***************************3.row***************************
PARTITION_NAME:p2
TABLE_ROWS:0
***************************4.row***************************
PARTITION_NAME:p3
TABLE_ROWS:3
4rowsinset(0.00sec)
mysql> select * from e;
+------+-------+-------+
| id | fname | lname |
+------+-------+-------+
| 1669 | Jim | Smith |
| 337 | Mary | Jones |
| 2005 | Linda | Black |
+------+-------+-------+
3 rows in set (0.00 sec)
1
2
3
4
5
6
7
8
9
mysql>select*frome;
+------+-------+-------+
|id|fname|lname|
+------+-------+-------+
|1669|Jim|Smith|
|337|Mary|Jones|
|2005|Linda|Black|
+------+-------+-------+
3rowsinset(0.00sec)
而这时可以在表e2中观察到被移动的数据。
mysql> select * from e2;
+----+-------+-------+
| id | fname | lname |
+----+-------+-------+
| 16 | Frank | White |
+----+-------+-------+
1 row in set (0.00 sec)
1
2
3
4
5
6
7
mysql>select*frome2;
+----+-------+-------+
|id|fname|lname|
+----+-------+-------+
|16|Frank|White|
+----+-------+-------+
1rowinset(0.00sec)
二、分区数据跟表中数据交换
往e表再插入一条数据，写入到p0分区中。
mysql> INSERT INTO e VALUES (41, "Michael", "Green");
Query OK, 1 row affected (0.00 sec)
1
2
mysql>INSERTINTOeVALUES(41,"Michael","Green");
QueryOK,1rowaffected(0.00sec)
mysql> select * from e;
+------+---------+-------+
| id | fname | lname |
+------+---------+-------+
| 41 | Michael | Green |
| 1669 | Jim | Smith |
| 337 | Mary | Jones |
| 2005 | Linda | Black |
+------+---------+-------+
4 rows in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
mysql>select*frome;
+------+---------+-------+
|id|fname|lname|
+------+---------+-------+
|41|Michael|Green|
|1669|Jim|Smith|
|337|Mary|Jones|
|2005|Linda|Black|
+------+---------+-------+
4rowsinset(0.00sec)
然后测试把p0分区的数据跟e2表数据交换。
mysql> ALTER TABLE e EXCHANGE PARTITION p0 WITH TABLE e2;
Query OK, 0 rows affected (0.01 sec)
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)
1
2
3
4
5
6
7
8
9
10
11
12
13
mysql>ALTERTABLEeEXCHANGEPARTITIONp0WITHTABLEe2;
QueryOK,0rowsaffected(0.01sec)
mysql>select*frome;
+------+-------+-------+
|id|fname|lname|
+------+-------+-------+
|16|Frank|White|
|1669|Jim|Smith|
|337|Mary|Jones|
|2005|Linda|Black|
+------+-------+-------+
4rowsinset(0.00sec)
mysql> select * from e2;
+----+---------+-------+
| id | fname | lname |
+----+---------+-------+
| 41 | Michael | Green |
+----+---------+-------+
1 row in set (0.00 sec)
1
2
3
4
5
6
7
mysql>select*frome2;
+----+---------+-------+
|id|fname|lname|
+----+---------+-------+
|41|Michael|Green|
+----+---------+-------+
1rowinset(0.00sec)
可以看到，p0分区跟e2表进行了数据的交换。

PS：分区交换我在实践中一般都是用来归档历史数据使用，生产中本人一个分区5千万数据几秒钟就交换走了，可以说是用着非常爽的。

如果您觉得本站对你有帮助，那么可以支付宝扫码捐助以帮助本站更好地发展，在此谢过。

本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。