MySQL 5.6和5.7分区交换笔记

Posted on by mark blue, mark

1.概述
分区表在生产环境用的太多太多，当分区增长到一定数量的时候，我们需要将数据归档，我见过有些运维在oracle环境下是这么做的，将需要归档的数据插入到其他表，于是执行这么一条语句:

insert into archive_tbl select * from live_tbl where "过滤条件"/partition 分区名;

当分区数据量有几十个GB，甚至上百GB的时候，这种操作是非常要命的，需要把交换的分区读进来，写出去，浪费非常多的IO资源，对于OLTP缓解来讲，业务高峰期，这是致命的，各种等待事件一下就上来了，搞得你目瞪口呆。
当我们使用分区交换技术的时候，将数据归档到其他表，都是妙级操作，更改的只是数据字典。在mysql中，分区交换技术主要在5.7这个版本有变化，5.7以前和5.7实现交换，略有不同。5.6的分区表交换分区比较绕，而且需要拷贝分区文件，效率不是特别高，不过话又说回来，拷贝分区文件比导入导出快多了，最后，我们也并不是特别推荐在5.6里面使用分区表，mysql内部一个分区使用一个handler，可能最后弊大于利。
本文没有深入分析分区交换的原理，也没有任何拓展。只是看到oracle环境用分区交换比较多，一时兴起记下mysql分区交换。

2.mysql5.7以前分区交换
2.1环境交代
这里我们借用percona的题材
mysql版本:5.6.34
live_tbl:生产表
archive_tbl:归档表
p201203,这个分区准备从生产表交换到归档表
dest_tbl_tmp:临时表
2.2数据准备

CREATE TABLE live_tbl (
some_id bigint(20) NOT NULL AUTO_INCREMENT,
summary_date date NOT NULL,
PRIMARY KEY (some_id,summary_date)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci
/*!50500 PARTITION BY RANGE COLUMNS(summary_date)
(PARTITION p201703 VALUES LESS THAN ('2017-04-01') ENGINE = InnoDB,
 PARTITION p201704 VALUES LESS THAN ('2017-05-01') ENGINE = InnoDB,
 PARTITION p201705 VALUES LESS THAN ('2017-06-01') ENGINE = InnoDB,
 PARTITION p201706 VALUES LESS THAN ('2017-07-01') ENGINE = InnoDB,
 PARTITION future VALUES LESS THAN (MAXVALUE) ENGINE = InnoDB) */;

INSERT INTO live_tbl(summary_date) VALUES
    ('2017-03-15'),('2017-03-18'),
	('2017-04-15'),('2017-04-18'),
	('2017-05-15'),('2017-05-18'),
	('2017-06-15'),('2017-06-18'),
	('2017-07-15'),('2017-07-18');

root@localhost [thedb] 01:10:06>>>select * from live_tbl partition(p201703);
+---------+--------------+
| some_id | summary_date |
+---------+--------------+
|       1 | 2017-03-15   |
|       2 | 2017-03-18   |
+---------+--------------+
2 rows in set (0.00 sec)

root@localhost [thedb] 01:10:26>>>select * from live_tbl partition(p201704);
+---------+--------------+
| some_id | summary_date |
+---------+--------------+
|       3 | 2017-04-15   |
|       4 | 2017-04-18   |
+---------+--------------+
2 rows in set (0.00 sec)

root@localhost [thedb] 01:10:28>>>select * from live_tbl partition(p201705);
+---------+--------------+
| some_id | summary_date |
+---------+--------------+
|       5 | 2017-05-15   |
|       6 | 2017-05-18   |
+---------+--------------+
2 rows in set (0.00 sec)

root@localhost [thedb] 01:10:30>>>select * from live_tbl partition(p201706);
+---------+--------------+
| some_id | summary_date |
+---------+--------------+
|       7 | 2017-06-15   |
|       8 | 2017-06-18   |
+---------+--------------+
2 rows in set (0.00 sec)

root@localhost [thedb] 01:10:34>>>select * from live_tbl partition(future);
+---------+--------------+
| some_id | summary_date |
+---------+--------------+
|       9 | 2017-07-15   |
|      10 | 2017-07-18   |
+---------+--------------+
2 rows in set (0.00 sec)




CREATE TABLE archive_tbl (
some_id bigint(20) NOT NULL AUTO_INCREMENT,
summary_date date NOT NULL,
PRIMARY KEY (some_id,summary_date)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci
/*!50500 PARTITION BY RANGE COLUMNS(summary_date)
(PARTITION p201609 VALUES LESS THAN ('2016-10-01') ENGINE = InnoDB,
 PARTITION p201610 VALUES LESS THAN ('2016-11-01') ENGINE = InnoDB,
 PARTITION p201611 VALUES LESS THAN ('2016-12-01') ENGINE = InnoDB,
 PARTITION p201612 VALUES LESS THAN ('2017-01-01') ENGINE = InnoDB,
 PARTITION p201701 VALUES LESS THAN ('2017-02-01') ENGINE = InnoDB,
 PARTITION p201702 VALUES LESS THAN ('2017-03-01') ENGINE = InnoDB,
 PARTITION future VALUES LESS THAN (MAXVALUE) ENGINE = InnoDB) */;
 
INSERT INTO archive_tbl(summary_date) VALUES
    ('2016-09-15'),('2016-09-18'),
	('2016-10-15'),('2016-10-18'),
	('2016-11-15'),('2016-11-18'),
	('2016-12-15'),('2016-12-18'),
	('2017-01-15'),('2017-01-18'),
	('2017-02-15'),('2017-02-18');
	

root@localhost [thedb] 01:47:42>>>select * from archive_tbl partition(p201609);
+---------+--------------+
| some_id | summary_date |
+---------+--------------+
|       1 | 2016-09-15   |
|       2 | 2016-09-18   |
+---------+--------------+
2 rows in set (0.00 sec)

root@localhost [thedb] 01:47:42>>>select * from archive_tbl partition(p201610);
+---------+--------------+
| some_id | summary_date |
+---------+--------------+
|       3 | 2016-10-15   |
|       4 | 2016-10-18   |
+---------+--------------+
2 rows in set (0.00 sec)

root@localhost [thedb] 01:47:42>>>select * from archive_tbl partition(p201611);
+---------+--------------+
| some_id | summary_date |
+---------+--------------+
|       5 | 2016-11-15   |
|       6 | 2016-11-18   |
+---------+--------------+
2 rows in set (0.00 sec)

root@localhost [thedb] 01:47:42>>>select * from archive_tbl partition(p201612);
+---------+--------------+
| some_id | summary_date |
+---------+--------------+
|       7 | 2016-12-15   |
|       8 | 2016-12-18   |
+---------+--------------+
2 rows in set (0.00 sec)

root@localhost [thedb] 01:47:42>>>select * from archive_tbl partition(p201701);
+---------+--------------+
| some_id | summary_date |
+---------+--------------+
|       9 | 2017-01-15   |
|      10 | 2017-01-18   |
+---------+--------------+
2 rows in set (0.00 sec)

root@localhost [thedb] 01:47:42>>>select * from archive_tbl partition(p201702);
+---------+--------------+
| some_id | summary_date |
+---------+--------------+
|      11 | 2017-02-15   |
|      12 | 2017-02-18   |
+---------+--------------+
2 rows in set (0.00 sec)

2.2 创建临时表

mysql> CREATE TABLE dest_tbl_tmp LIKE archive_tbl;
mysql> ALTER TABLE dest_tbl_tmp REMOVE PARTITIONING;
mysql> ALTER TABLE dest_tbl_tmp DISCARD TABLESPACE;

2.3 拷贝交换分区
为了保证数据一致性，我们flush一下。

mysql> USE thedb
mysql> FLUSH TABLE live_tbl FOR EXPORT;

然后在操作系统层面讲分区拷贝到临时表：

shell> cp live_tbl#P#p201703.ibd dest_tbl_tmp.ibd

然后import分区到临时表

mysql> unlock tables;
mysql> ALTER TABLE dest_tbl_tmp IMPORT TABLESPACE;

2.4 归档表添加分区

mysql> ALTER TABLE archive_tbl REORGANIZE PARTITION future INTO (
PARTITION p201703 VALUES LESS THAN ('2017-04-01'),
PARTITION future VALUES LESS THAN (MAXVALUE)
);

2.5 进行分区交换

mysql> ALTER TABLE archive_tbl EXCHANGE PARTITION p201703 WITH TABLE dest_tbl_tmp;

2.6 删除久分区，并验证

root@localhost [thedb] 02:17:06>>>select * from archive_tbl partition(p201703);
+---------+--------------+
| some_id | summary_date |
+---------+--------------+
|       1 | 2017-03-15   |
|       2 | 2017-03-18   |
+---------+--------------+
2 rows in set (0.01 sec)
mysql>ALTER TABLE live_tbl DROP PARTITION p201703;

3. mysql5.7的分区交换
5.7的分区交换相对容易，不需要那么绕。
表和数据准备:

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)
);

INSERT INTO e VALUES
    (1669, "Jim", "Smith"),
    (337, "Mary", "Jones"),
    (16, "Frank", "White"),
    (2005, "Linda", "Black");

准备一张结构相似的非分区表:

CREATE TABLE e2 LIKE e;
ALTER TABLE e2 REMOVE PARTITIONING;

各个分区数据的分布:

root@localhost [db01] 03:47:50>>>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)

将P0分区交换出去

ALTER TABLE e EXCHANGE PARTITION p0 WITH TABLE e2;

验证现有数据:

root@localhost [db01] 03:48:56>>>SELECT PARTITION_NAME, TABLE_ROWS  FROM INFORMATION_SCHEMA.PARTITIONS   WHERE TABLE_NAME = 'e';
+----------------+------------+
| PARTITION_NAME | TABLE_ROWS |
+----------------+------------+
| p0             |          0 |
| p1             |          0 |
| p2             |          0 |
| p3             |          2 |
+----------------+------------+
4 rows in set (0.00 sec)

查看交换出来的数据:

root@localhost [db01] 03:48:57>>>SELECT * FROM e2;
+----+-------+-------+
| id | fname | lname |
+----+-------+-------+
| 16 | Frank | White |
+----+-------+-------+
1 row in set (0.00 sec)

发表评论

电子邮件地址不会被公开。 必填项已用*标注