mysql交换分区_MySQL 5.6和5.7分区交换笔记

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)

Posted in MySQL