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)
近期评论