MySQL5.6分区表之分区置换

5rfgn

MySQL发展到版本5.6添加很多实用功能,对于分区的改进也很大,今天主要介绍分区置换功能.下面用例子演示:

创建一张分区表TEST

mysql> create table test (id int not null)
    -> partition by range (id) (
    -> partition p0 values less than (10),
    -> partition p1 values less than (20),
    -> partition p2 values less than (30),
    -> partition p3 values less than (40),
    -> partition p4 values less than (50),
    -> partition p6 values less than maxvalue);
Query OK, 0 rows affected (0.35 sec)

mysql>

插入表数据查看分区结构分区记录

mysql> insert into test values (1);
Query OK, 1 row affected (0.02 sec)

mysql> insert into test values (11);
Query OK, 1 row affected (0.00 sec)

mysql> insert into test values (21);
Query OK, 1 row affected (0.01 sec)

mysql> insert into test values (31);
Query OK, 1 row affected (0.00 sec)

mysql> insert into test values (41);
Query OK, 1 row affected (0.01 sec)

mysql> insert into test values (51);
Query OK, 1 row affected (0.01 sec)

mysql> insert into test values (111);
Query OK, 1 row affected (0.00 sec)

mysql> select table_name,partition_name,table_rows from information_schema.partitions where table_name = 'test';
+------------+----------------+------------+
| table_name | partition_name | table_rows |
+------------+----------------+------------+
| test       | p0             |          1 |
| test       | p1             |          1 |
| test       | p2             |          1 |
| test       | p3             |          1 |
| test       | p4             |          1 |
| test       | p6             |          2 |
+------------+----------------+------------+

创建表P7
mysql> create table p7 (id int not null);
Query OK, 0 rows affected (0.06 sec)

分区置换,把表P7跟TEST表的P6分区进行置换
mysql> alter table test exchange partition p6 with table p7;
Query OK, 0 rows affected (0.13 sec)

从新查看表分区结构分区记录
mysql> select table_name,partition_name,table_rows from information_schema.partitions where table_name = 'test';
+------------+----------------+------------+
| table_name | partition_name | table_rows |
+------------+----------------+------------+
| test       | p0             |          1 |
| test       | p1             |          1 |
| test       | p2             |          1 |
| test       | p3             |          1 |
| test       | p4             |          1 |
| test       | p6             |          0 |       --这里P6分区显示的条数为0,证明原来分区P6和表P7已经置换了
+------------+----------------+------------+
6 rows in set (0.00 sec)

现在去看表P7
mysql> select * from p7;
+-----+
| id  |
+-----+
|  51 |
| 111 |
+-----+
2 rows in set (0.00 sec)

插入一条新数据
mysql> insert into p7 values (151);
Query OK, 1 row affected (0.13 sec)

mysql> select * from p7;
+-----+
| id  |
+-----+
|  51 |
| 111 |
| 151 |
+-----+
3 rows in set (0.00 sec)

把分区P6和表P7在置换回来看结果
mysql> alter table test exchange partition p6 with table p7;
Query OK, 0 rows affected (0.07 sec)

查看分区结构分区条数
mysql> select table_name,partition_name,table_rows from information_schema.partitions where table_name = 'test';
+------------+----------------+------------+
| table_name | partition_name | table_rows |
+------------+----------------+------------+
| test       | p0             |          1 |
| test       | p1             |          1 |
| test       | p2             |          1 |
| test       | p3             |          1 |
| test       | p4             |          1 |
| test       | p6             |          2 |
+------------+----------------+------------+
6 rows in set (0.00 sec)

重新分析表继续查看分区结构分区数据
mysql> alter table test analyze partition all;
+--------------+---------+----------+----------+
| Table        | Op      | Msg_type | Msg_text |
+--------------+---------+----------+----------+
| percona.test | analyze | status   | OK       |
+--------------+---------+----------+----------+
1 row in set (0.64 sec)

mysql> select table_name,partition_name,table_rows from information_schema.partitions where table_name = 'test';
+------------+----------------+------------+
| table_name | partition_name | table_rows |
+------------+----------------+------------+
| test       | p0             |          1 |
| test       | p1             |          1 |
| test       | p2             |          1 |
| test       | p3             |          1 |
| test       | p4             |          1 |
| test       | p6             |          3 |
+------------+----------------+------------+
6 rows in set (0.00 sec)

查看P6分区和表P7数据
mysql> select * from test partition (p6);
+-----+
| id  |
+-----+
|  51 |
| 111 |
| 151 |
+-----+
3 rows in set (0.00 sec)

mysql> select * from p7;
Empty set (0.00 sec)

--注意事项
1、在置换分区的时候表的结构要与分区表的结果保持一致否则置换失败.
2、如果需要置换的表有数据需要符合置换表分区的规则否则置换失败.

例子：
表结构
mysql> create table p8 (id int not null,name varchar(10));
Query OK, 0 rows affected (0.08 sec)

mysql> alter table test exchange partition p6 with table p8;
ERROR 1736 (HY000): Tables have different definitions

不符合分区规则
mysql> alter table test exchange partition p1 with table p7;
ERROR 1737 (HY000): Found a row that does not match the partition

OK了,今天到此为止.^_^