Mysql 分区表管理

范围和列表分区的管理

范围和列表分区的添加和删除都以类似的方式处理，因此我们在本节中讨论这两种分区的管理。

创建测试表
CREATE TABLE tr (id INT, name VARCHAR(50), purchased DATE)
 PARTITION BY RANGE( YEAR(purchased) ) (
     PARTITION p0 VALUES LESS THAN (1990),
     PARTITION p1 VALUES LESS THAN (1995),
     PARTITION p2 VALUES LESS THAN (2000),
     PARTITION p3 VALUES LESS THAN (2005),
     PARTITION p4 VALUES LESS THAN (2010),
     PARTITION p5 VALUES LESS THAN (2015)
 );

INSERT INTO tr VALUES
 (1, 'desk organiser', '2003-10-15'),
 (2, 'alarm clock', '1997-11-05'),
 (3, 'chair', '2009-03-10'),
 (4, 'bookcase', '1989-01-10'),
 (5, 'exercise bike', '2014-05-09'),
 (6, 'sofa', '1987-06-05'),
 (7, 'espresso maker', '2011-11-22'),
 (8, 'aquarium', '1992-08-04'),
 (9, 'study desk', '2006-09-16'),
 (10, 'lava lamp', '1998-12-25');

CREATE TABLE tt (
    id INT,
    data INT
)
PARTITION BY LIST(data) (
    PARTITION p0 VALUES IN (5, 10, 15),
    PARTITION p1 VALUES IN (6, 12, 18)
);

删除某个分区

ALTER TABLE tr DROP PARTITION p2;
记住非常重要的一点是，当你删除一个分区时，你也删除了存储在该分区中的所有数据。

添加分区

--范围分区添加分区
ALTER TABLE members ADD PARTITION (PARTITION p3 VALUES LESS THAN (2010));
--列表分区添加分区
ALTER TABLE tt ADD PARTITION (PARTITION p2 VALUES IN (7, 14, 21));
ALTER TABLE tt ADD PARTITION (PARTITION np VALUES IN (4, 8));

拆分分区

ALTER TABLE members
    REORGANIZE PARTITION p0 INTO (
        PARTITION n0 VALUES LESS THAN (1970),
        PARTITION n1 VALUES LESS THAN (1980)
);

合并分区

--范围分区合并分区
ALTER TABLE members REORGANIZE PARTITION n0, n1 INTO (
    PARTITION p0 VALUES LESS THAN (1980)
);
--列表分区合并分区
ALTER TABLE tt REORGANIZE PARTITION p1,np INTO (
    PARTITION p1 VALUES IN (6, 18),
    PARTITION np VALUES in (4, 8, 12)
);

HASH分区和KEY 分区管理

减少分区

创建hash 分区
CREATE TABLE clients (
    id INT,
    fname VARCHAR(30),
    lname VARCHAR(30),
    signed DATE
)
PARTITION BY HASH( MONTH(signed) )
PARTITIONS 12;
要将分区数从十二个减少到八个
ALTER TABLE clients COALESCE PARTITION 4;
创建key 分区
CREATE TABLE clients_lk (
     id INT,
     fname VARCHAR(30),
     lname VARCHAR(30),
     signed DATE
 )
 PARTITION BY LINEAR KEY(signed)
 PARTITIONS 12;
ALTER TABLE clients_lk COALESCE PARTITION 4;

增加分区

将clients表格的分区数量 从12增加到18.使用 ALTER TABLE ... ADD PARTITION如下所示：
ALTER TABLE clients ADD PARTITION PARTITIONS 6;

与表交换分区和子分区

在MySQL 5.7中，可以使用表来交换表分区或子分区，其中 分区表是分区表，并且 是要与未分区表交换的分区或子 分区，前提是以下语句是正确的： ALTER TABLE pt EXCHANGE PARTITION p WITH TABLE ntptpptnt
1.表格nt本身并未分区。
2.表格nt不是临时表格。
3.表格的结构pt和 nt其他方面是相同的。
4.表不nt包含外键引用，并且其他表中没有任何引用的外键nt。
5.nt该分区定义的边界之外 没有行p。如果使用该WITHOUT VALIDATION选项，则此条件不适用。该[{WITH|WITHOUT} VALIDATION]选项已添加到MySQL 5.7.5中。

与非分区表交换分区

1）创建分区表
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");
2）创建非分区表
MariaDB [test]> CREATE TABLE e2 LIKE e;
Query OK, 0 rows affected (0.19 sec)

MariaDB [test]> ALTER TABLE e2 REMOVE PARTITIONING;
Query OK, 0 rows affected (0.20 sec)               
Records: 0  Duplicates: 0  Warnings: 0
3）查看分区表包含行数
MariaDB [test]>  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）要p0在表中 e与表交换分区e2
MariaDB [test]> ALTER TABLE e EXCHANGE PARTITION p0 WITH TABLE e2;
Query OK, 0 rows affected (0.07 sec)
5）分区中行p0不再存在：
MariaDB [test]>  SELECT PARTITION_NAME, TABLE_ROWS
    ->    FROM INFORMATION_SCHEMA.PARTITIONS
    ->    WHERE TABLE_NAME = 'e';
+----------------+------------+
| PARTITION_NAME | TABLE_ROWS |
+----------------+------------+
| p0             |          0 |
| p1             |          0 |
| p2             |          0 |
| p3             |          3 |
+----------------+------------+
6）数据被交换到e2 表中
MariaDB [test]> SELECT * FROM e2;
+----+-------+-------+
| id | fname | lname |
+----+-------+-------+
| 16 | Frank | White |
7）向分区表e 插入数据
MariaDB [test]> INSERT INTO e VALUES (41, "Michael", "Green"); 
Query OK, 1 row affected (0.01 sec)

MariaDB [test]> 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)
8）与表e2 交换分区
MariaDB [test]> ALTER TABLE e EXCHANGE PARTITION p0 WITH TABLE e2;
Query OK, 0 rows affected (0.04 sec)
9）发现分区表p0分区和e 表数据进行交换
MariaDB [test]>  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)

MariaDB [test]> SELECT * FROM e2;
+----+---------+-------+
| id | fname   | lname |
+----+---------+-------+
| 41 | Michael | Green |
+----+---------+-------+
1 row in set (0.00 sec)

不匹配的行
您应该记住，在发布ALTER TABLE ... EXCHANGE PARTITION语句之前在非分区表中找到的任何行 必须满足它们存储在目标分区中所需的条件; 否则，声明失败。要了解这种情况发生时，先插入一行到e2这是分区的分区定义的边界之外 p0表e。例如，插入一个id列值太大的行; 然后，尝试再次与该分区交换表：

mysql> INSERT INTO e2 VALUES (51, "Ellen", "McDonald");
Query OK, 1 row affected (0.08 sec)

mysql> ALTER TABLE e EXCHANGE PARTITION p0 WITH TABLE e2;
ERROR 1707 (HY000): Found row that does not match the partition
只有WITHOUT VALIDATION选项才能使此操作成功：

mysql> ALTER TABLE e EXCHANGE PARTITION p0 WITH TABLE e2 WITHOUT VALIDATION;
Query OK, 0 rows affected (0.02 sec)
当分区与包含与分区定义不匹配的行的表交换时，数据库管理员有责任修复不匹配的行，这可以使用REPAIR TABLE或 执行 ALTER TABLE ... REPAIR PARTITION

与非分区表交换子分区

1）创建分区表
CREATE TABLE es (
     id INT NOT NULL,
     fname VARCHAR(30),
     lname VARCHAR(30)
 )
   PARTITION BY RANGE (id)
   SUBPARTITION BY KEY (lname)
   SUBPARTITIONS 2 (
       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 es VALUES
  (1669, "Jim", "Smith"),
  (337, "Mary", "Jones"),
  (16, "Frank", "White"),
  (2005, "Linda", "Black");
2）创建非分区表
CREATE TABLE es2 LIKE es;
ALTER TABLE es2 REMOVE PARTITIONING;
3）查看分区表信息
MariaDB [test]>  SELECT PARTITION_NAME, SUBPARTITION_NAME, TABLE_ROWS
    ->  FROM INFORMATION_SCHEMA.PARTITIONS
    ->  WHERE TABLE_NAME = 'es';
+----------------+-------------------+------------+
| PARTITION_NAME | SUBPARTITION_NAME | TABLE_ROWS |
+----------------+-------------------+------------+
| p0             | p0sp0             |          1 |
| p0             | p0sp1             |          0 |
| p1             | p1sp0             |          0 |
| p1             | p1sp1             |          0 |
| p2             | p2sp0             |          0 |
| p2             | p2sp1             |          0 |
| p3             | p3sp0             |          3 |
| p3             | p3sp1             |          0 |
+----------------+-------------------+------------+
4）下ALTER TABLE的语句交换es 子分区 p3sp0与非分区表es2：
MariaDB [test]> ALTER TABLE es EXCHANGE PARTITION p3sp0 WITH TABLE es2;
Query OK, 0 rows affected (0.04 sec)
5）查看交换后分区行数
MariaDB [test]> SELECT PARTITION_NAME, SUBPARTITION_NAME, TABLE_ROWS  FROM INFORMATION_SCHEMA.PARTITIONS  WHERE TABLE_NAME = 'es';
+----------------+-------------------+------------+
| PARTITION_NAME | SUBPARTITION_NAME | TABLE_ROWS |
+----------------+-------------------+------------+
| p0             | p0sp0             |          1 |
| p0             | p0sp1             |          0 |
| p1             | p1sp0             |          0 |
| p1             | p1sp1             |          0 |
| p2             | p2sp0             |          0 |
| p2             | p2sp1             |          0 |
| p3             | p3sp0             |          0 |
| p3             | p3sp1             |          0 |
+----------------+-------------------+------------+
6）数据被交换到非分区表
MariaDB [test]> SELECT * FROM es2;
+------+-------+-------+
| id   | fname | lname |
+------+-------+-------+
| 1669 | Jim   | Smith |
|  337 | Mary  | Jones |
| 2005 | Linda | Black |
+------+-------+-------+
3 rows in set (0.00 sec)

如果表是子分区的，则只能交换表的子分区（不是整个分区）和未分区的表，如下所示：
MariaDB [test]>  ALTER TABLE es EXCHANGE PARTITION p3 WITH TABLE es2;
ERROR 1734 (HY000): Subpartitioned table, use subpartition instead of partition

MySQL使用的表结构的比较非常严格。分区表和非分区表的列和索引的数量，顺序，名称和类型必须完全匹配。另外，两个表都必须使用相同的存储引擎：

分区维护

重建分区。

重建分区; 这与删除存储在分区中的所有记录的效果相同，然后重新插入它们。这对于碎片整理很有用。
ALTER TABLE t1 REBUILD PARTITION p0, p1;

优化分区

如果已经从一个分区删除大量行的，或者如果你已与可变长度行许多变化，以一个分区表（即，具有VARCHAR， BLOB或 TEXT列），可以使用 ALTER TABLE ... OPTIMIZE PARTITION以回收任何未使用的空间和整理分区数据文件
ALTER TABLE t1 OPTIMIZE PARTITION p0, p1;

分析分区

 这读取和存储分区的密钥分配
ALTER TABLE t1 ANALYZE PARTITION p3;

修复分区

这修复了损坏的分区。
ALTER TABLE t1 REPAIR PARTITION p0,p1;
通常，REPAIR PARTITION分区包含重复键错误时失败。在MySQL 5.7.2和更高版本中，可以使用 ALTER IGNORE TABLE该选项，在这种情况下，由于存在重复密钥而无法移动的所有行都将从分区中删除

检查分区

可以像使用CHECK TABLE非分区表一样检查分区是否有错误
ALTER TABLE t1 REPAIR PARTITION p0,p1;

截断分区

该语句可用于删除一个或多个分区中的所有行，这几乎与TRUNCATE TABLE删除表中的所有行相同。
ALTER TABLE es TRUNCATE PARTITION p0;
截断表中的所有分区
ALTER TABLE ... TRUNCATE PARTITION ALL

获取有关分区的信息

1）使用该SHOW CREATE TABLE 语句查看用于创建分区表的分区子句。
2）使用该SHOW TABLE STATUS 语句来确定表是否已分区。
3）查询 INFORMATION_SCHEMA.PARTITIONS 表格。
4）使用该语句 EXPLAIN SELECT查看给定的分区 SELECT。

MariaDB [test]> SHOW CREATE TABLE es/G;
*************************** 1. row ***************************
       Table: es
Create Table: CREATE TABLE `es` (
  `id` int(11) NOT NULL,
  `fname` varchar(30) DEFAULT NULL,
  `lname` varchar(30) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8
 PARTITION BY RANGE (`id`)
SUBPARTITION BY KEY (`lname`)
SUBPARTITIONS 2
(PARTITION `p0` VALUES LESS THAN (50) ENGINE = InnoDB,
 PARTITION `p1` VALUES LESS THAN (100) ENGINE = InnoDB,
 PARTITION `p2` VALUES LESS THAN (150) ENGINE = InnoDB,
 PARTITION `p3` VALUES LESS THAN MAXVALUE ENGINE = InnoDB)
1 row in set (0.00 sec)