22.3 分区管理

2020-05-11 11:13:15

参考官方文档：

https://dev.mysql.com/doc/refman/5.7/en/partitioning-management.html

MySQL 5.7提供了许多修改分区表的方法。可以添加，删除，重新定义，合并或拆分现有分区。可以使用ALTER TABLE语句来执行所有这些操作。还有一些方法可以获取有关分区表和分区的信息。我们将在后面的章节中讨论这些主题

注意：

在MySQL 5.7中，分区表的所有分区必须具有相同数量的子分区，并且一旦创建了表，就无法更改子分区。

要更改表的分区方案，只需将ALTER TABLE语句与partition_options子句一起使用即可。此子句的语法与CREATE TABLE用于创建分区表的语法相同，并且始终以关键字PARTITION BY开头。假设您有一个使用以下CREATE TABLE语句按范围分区的表：

CREATE TABLE trb3 (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) );

要重新分区此表，使用id列值作为键的基础，将其分区为两个分区，您可以使用以下语句：

ALTER TABLE trb3 PARTITION BY KEY(id) PARTITIONS 2;

这和删除表并使用CREATE TABLE trb3 PARTITION BY KEY（id）PARTITIONS 2;重新创建它，有相同的效果。

ALTER TABLE ... ENGINE = ...仅更改表使用的存储引擎，并保持表的分区方案不变。使用ALTER TABLE ... REMOVE PARTITIONING删除表的分区。

重要：

在给定的ALTER TABLE语句中，只能使用单个PARTITION BY，ADD PARTITION，DROP PARTITION，REORGANIZE PARTITION或COALESCE PARTITION子句。如果您（例如）希望删除分区并重新组织表的剩余分区，则必须在两个单独的ALTER TABLE语句中执行此操作（一个使用DROP PARTITION，另一个使用REORGANIZE PARTITION）。

在MySQL 5.7中，可以使用ALTER TABLE ... TRUNCATE PARTITION删除一个或多个所选分区中的所有行。

22.3.1 管理 RANGE 和 LIST 分区

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

可以使用带有DROP PARTITION选项的ALTER TABLE语句来完成从RANGE或LIST分区的表中删除分区。假设您已创建一个按范围分区的表，然后使用以下CREATE TABLE和INSERT语句填充10个记录:

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)

-> );

Query OK, 0 rows affected (0.28 sec)

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

Query OK, 10 rows affected (0.05 sec) Records: 10 Duplicates: 0 Warnings: 0

您可以看到哪些已经被插入到分区p2中，如下所示：

mysql> SELECT * FROM tr

-> WHERE purchased BETWEEN '1995-01-01' AND '1999-12-31';

+------+-------------+------------+

| id | name | purchased |

+------+-------------+------------+

| 2 | alarm clock | 1997-11-05 |

| 10 | lava lamp | 1998-12-25 |

+------+-------------+------------+

2 rows in set (0.00 sec)

您还可以使用分区选择获取此信息，如下所示：

mysql> SELECT * FROM tr PARTITION (p2);

+------+-------------+------------+

| id | name | purchased |

+------+-------------+------------+

| 2 | alarm clock | 1997-11-05 |

| 10 | lava lamp | 1998-12-25 |

+------+-------------+------------+

2 rows in set (0.00 sec)

要删除名为p2的分区，请执行以下命令：

mysql> ALTER TABLE tr DROP PARTITION p2; Query OK, 0 rows affected (0.03 sec)

注意：

NDBCLUSTER存储引擎不支持ALTER TABLE ... DROP PARTITION。但是，它确实支持本章中描述的ALTER TABLE的其他与分区相关的扩展。

请务必记住，删除分区时，还会删除该分区中存储的所有数据。通过重新运行上一个SELECT查询，您可以看到这种情况：

mysql> SELECT * FROM tr WHERE purchased

-> BETWEEN '1995-01-01' AND '1999-12-31';

Empty set (0.00 sec)

因此，您必须具有表的DROP权限才能在该表上执行ALTER TABLE ... DROP PARTITION。

如果希望在保留表定义及其分区方案的同时删除所有分区中的所有数据，请使用TRUNCATE TABLE语句。（请参见第13.1.34节“TRUNCATE TABLE语法”。）

如果您打算在不丢失数据的情况下更改表的分区，请改用ALTER TABLE ... REORGANIZE PARTITION。有关REORGANIZE PARTITION的信息，请参见下文或第13.1.8节“ALTER TABLE语法”。

如果现在执行SHOW CREATE TABLE语句，则可以看到表的分区组成是如何更改的

当您使用“1995-01-01”和“2004-12-31”之间的列值在已更改的表中插入新行时，这些行将存储在分区p3中。您可以按如下方式验证：

mysql> INSERT INTO tr VALUES (11, 'pencil holder', '1995-07-12');

Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM tr WHERE purchased

-> BETWEEN '1995-01-01' AND '2004-12-31';

+------+----------------+------------+

| id | name | purchased |

+------+----------------+------------+

| 1 | desk organiser | 2003-10-15 |

| 11 | pencil holder | 1995-07-12 |

+------+----------------+------------+

2 rows in set (0.00 sec)

mysql> ALTER TABLE tr DROP PARTITION p3;

Query OK, 0 rows affected (0.03 sec)

mysql> SELECT * FROM tr WHERE purchased

-> BETWEEN '1995-01-01' AND '2004-12-31';

Empty set (0.00 sec)

作为ALTER TABLE ... DROP PARTITION的结果，从表中删除的行数不会被服务器报告，因为它将被报告为等效的DELETE查询。

删除LIST分区使用与删除RANGE分区完全相同的ALTER TABLE ... DROP PARTITION语法。但是，之后对表的使用有一个重要的区别：您不能再向表中插入任何具有定义已删除分区的值列表中包含的值的行。

要将新范围或列表分区添加到先前分区的表，请使用ALTER TABLE ... ADD PARTITION语句。对于由RANGE分区的表，可以使用此范围将新范围添加到现有分区列表的末尾。假设您有一个包含组织成员资格数据的分区表，其定义如下：

CREATE TABLE members (

id INT, fname VARCHAR(25), lname VARCHAR(25), dob DATE )

PARTITION BY RANGE( YEAR(dob) ) (

PARTITION p0 VALUES LESS THAN (1980),

PARTITION p1 VALUES LESS THAN (1990),

PARTITION p2 VALUES LESS THAN (2000) );

进一步假设成员的低年龄是16岁。随着日历接近2015年底，您意识到您将很快接纳2000年（及之后）出生的成员。您可以修改成员表以适应2000年至2010年出生的新成员，如下所示：

ALTER TABLE members ADD PARTITION (PARTITION p3 VALUES LESS THAN (2010));

对于按范围分区的表，可以使用ADD PARTITION仅将新分区添加到分区列表的高端。尝试在现有分区之间或之前以这种方式添加新分区会导致错误，如下所示：

mysql> ALTER TABLE members

> ADD PARTITION (

> PARTITION n VALUES LESS THAN (1970));

ERROR 1463 (HY000): VALUES LESS THAN value must be strictly » increasing for each partition

您可以通过将个分区重新组织为两个新分区来解决这个问题，这两个分区可以分割它们之间的范围，如下所示：

ALTER TABLE members REORGANIZE PARTITION p0 INTO (

PARTITION n0 VALUES LESS THAN (1970),

PARTITION n1 VALUES LESS THAN (1980) );

您还可以使用ALTER TABLE ... ADD PARTITION将新分区添加到由LIST分区的表中。假设使用以下CREATE TABLE语句定义了表tt：

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

您可以添加一个新分区来存储具有数据列值7,14和21的行，如下所示：

ALTER TABLE tt ADD PARTITION (PARTITION p2 VALUES IN (7, 14, 21));

请记住，包含在现有分区的值列表中的任何值的分区不能被创建。如果您尝试这样做，将导致错误：

mysql> ALTER TABLE tt ADD PARTITION > (PARTITION np VALUES IN (4, 8, 12)); ERROR 1465 (HY000): Multiple definition of same constant » in list partitioning

因为任何具有数据列值12的行已经分配给分区p1，所以无法在表tt上创建一个新值，该值在其值列表中包含12。要完成此操作，您可以删除p1，然后添加np，然后添加具有修改定义的新p1。但是，如前所述，这将导致丢失存储在p1中的所有数据 - 而且通常情况下这不是您真正想要做的事情。另一个解决方案似乎是使用新分区制作表的副本，并使用CREATE TABLE ... SELECT ...将数据复制到其中，然后删除旧表并重命名新表，但在处理大量数据时可能非常耗时。在需要高可用性的情况下，这也可能不可行。

您可以在单个ALTER TABLE ... ADD PARTITION语句中添加多个分区，如下所示：

CREATE TABLE employees ( id INT NOT NULL,

fname VARCHAR(50) NOT NULL,

lname VARCHAR(50) NOT NULL,

hired DATE NOT NULL )

PARTITION BY RANGE( YEAR(hired)

)

(

PARTITION p1 VALUES LESS THAN (1991),

PARTITION p2 VALUES LESS THAN (1996),

PARTITION p3 VALUES LESS THAN (2001),

PARTITION p4 VALUES LESS THAN (2005)

);

ALTER TABLE employees ADD PARTITION (

PARTITION p5 VALUES LESS THAN (2010),

PARTITION p6 VALUES LESS THAN MAXVALUE );

幸运的是，MySQL的分区实现提供了重新定义分区而不会丢失数据的方法。让我们首先看一下涉及RANGE分区的几个简单示例。回想一下现在定义的 members 表，如下所示：

mysql> SHOW CREATE TABLE members\G

*************************** 1. row ***************************

Table: members

Create Table: CREATE TABLE `members` (

`id` int(11) DEFAULT NULL,

`fname` varchar(25) DEFAULT NULL,

`lname` varchar(25) DEFAULT NULL,

`dob` date DEFAULT NULL )

ENGINE=InnoDB DEFAULT CHARSET=latin1

/*!50100 PARTITION BY RANGE ( YEAR(dob)) (

PARTITION n0 VALUES LESS THAN (1970) ENGINE = InnoDB,

PARTITION n1 VALUES LESS THAN (1980) ENGINE = InnoDB,

PARTITION p1 VALUES LESS THAN (1990) ENGINE = InnoDB,

PARTITION p2 VALUES LESS THAN (2000) ENGINE = InnoDB,

PARTITION p3 VALUES LESS THAN (2010) ENGINE = InnoDB) */

1 row in set (0.00 sec)

假设您想将表示1960年之前出生的成员的所有行移动到单独的分区中。正如我们已经看到的，使用ALTER TABLE ... ADD PARTITION无法做到这一点。但是，您可以使用另一个与ALTER TABLE相关的分区扩展来完成此任务：

ALTER TABLE members REORGANIZE PARTITION n0 INTO ( PARTITION s0 VALUES LESS THAN (1960), PARTITION s1 VALUES LESS THAN (1970) );

实际上，该命令将分区p0分成两个新分区s0和s1。它还根据两个PARTITION ... VALUES ...子句中包含的规则将存储在p0中的数据移动到新分区中，这样s0只包含YEAR（dob）小于1960的记录。 s1包含YEAR（dob）大于或等于1960但小于1970的那些行。

REORGANIZE PARTITION子句也可用于合并相邻分区。您可以反转前一个语句对成员表的影响，如下所示：

ALTER TABLE members REORGANIZE PARTITION s0,s1 INTO ( PARTITION p0 VALUES LESS THAN (1970) );

使用REORGANIZE PARTITION分割或合并分区时不会丢失任何数据。在执行上述语句时，MySQL将存储在分区s0和s1中的所有记录移动到分区p0中。

REORGANIZE PARTITION的一般语法如下所示：

ALTER TABLE tbl_name REORGANIZE PARTITION partition_list INTO (partition_definitions);

这里，tbl_name是分区表的名称，partition_list是要更改的一个或多个现有分区的名称的逗号分隔列表。 partition_definitions是以逗号分隔的新分区定义列表，它遵循与CREATE TABLE中使用的partition_definitions列表相同的规则。使用REORGANIZE PARTITION时，可以将多个分区合并为一个分区，或将一个分区拆分为多个分区。例如，您可以将members表的所有四个分区重新组织为两个，如下所示：

ALTER TABLE members REORGANIZE PARTITION p0,p1,p2,p3 INTO ( PARTITION m0 VALUES LESS THAN (1980),

PARTITION m1 VALUES LESS THAN (2000)

);

您还可以对由LIST分区的表使用REORGANIZE PARTITION。让我们回到向列表分区的tt表添加新分区并失败的问题，因为新分区的值已经存在于其中一个现有分区的值列表中。我们可以通过添加仅包含非冲突值的分区来处理此问题，然后重新组织新分区和现有分区，以便将现有分区中存储的值移动到新分区：

ALTER TABLE tt ADD PARTITION (PARTITION np VALUES IN (4, 8));

ALTER TABLE tt REORGANIZE PARTITION p1,np INTO (

PARTITION p1 VALUES IN (6, 18),

PARTITION np VALUES in (4, 8, 12)

);

以下是使用ALTER TABLE ... REORGANIZE PARTITION重新分区由RANGE或LIST分区的表时要记住的一些要点：

用于确定新分区方案的PARTITION选项遵循与CREATE TABLE语句使用的规则相同的规则。

新的RANGE分区方案不能有任何重叠范围; 新的LIST分区方案不能具有任何重叠的值集。

partition_definitions列表中的组合分区应该考虑与partition_list中指定的组合分区相同的范围或整体值集。

例如，分区p1和p2一起涵盖1980年至1999年在成员表中用作本节中的示例。这两个分区的任何重组都应涵盖相同的年份范围。

对于由RANGE分区的表，您只能重新组织相邻的分区; 你不能跳过范围分区。

例如，您无法使用以ALTER TABLE members REORGANIZE PARTITION p0，p2 INTO开头的语句重新组织 members 表...因为p0涵盖了1970年之前的年份，而p2涵盖了从1990年到1999年的年份，所以这些不是相邻的分区。（在这种情况下，您不能跳过分区p1。）

您不能使用REORGANIZE PARTITION来更改表使用的分区类型; 例如，您不能将RANGE分区更改为HASH分区或反之。您也无法使用此语句来更改分区表达式或列。要在不删除和重新创建表的情况下完成这些任务中的任何一个，可以使用ALTER TABLE ... PARTITION BY ...，如下所示：

ALTER TABLE members PARTITION BY HASH( YEAR(dob) ) PARTITIONS 8;

22.3.2 管理HASH 和KEY 分区

对于在分区设置中进行更改，通过hash或按key分区的表彼此非常相似，并且两者在与按范围或列表分区的表的方式上有很多不同。

您不能以与通过RANGE或LIST分区的表相同的方式从HASH或KEY分区的表中删除分区。但是，您可以使用ALTER TABLE ... COALESCE PARTITION语句合并HASH或KEY分区。假设您有一个包含客户端数据的表，该表分为十二个分区。 clients表的定义如下所示：

CREATE TABLE clients ( id INT, fname VARCHAR(30), lname VARCHAR(30), signed DATE )

PARTITION BY HASH( MONTH(signed) )

PARTITIONS 12;

要将分区数从12减少到8，请执行以下ALTER TABLE命令：

mysql> ALTER TABLE clients COALESCE PARTITION 4;

Query OK, 0 rows affected (0.02 sec)

COALESCE同样适用于由HASH，KEY，LINEAR HASH或LINEAR KEY分区的表。这是一个类似于前一个示例的示例，区别仅在于表由LINEAR KEY分区：

mysql> CREATE TABLE clients_lk (

-> id INT,

-> fname VARCHAR(30),

-> lname VARCHAR(30),

-> signed DATE

-> )

-> PARTITION BY LINEAR KEY(signed)

-> PARTITIONS 12;

Query OK, 0 rows affected (0.03 sec)

mysql> ALTER TABLE clients_lk COALESCE PARTITION 4;

Query OK, 0 rows affected (0.06 sec) Records: 0 Duplicates: 0 Warnings: 0

COALESCE PARTITION之后的数字是要合并到其余部分的分区数 - 换句话说，它是要从表中删除的分区数。

如果您尝试删除比该表更多的分区，结果将显示如下所示的错误：

mysql> ALTER TABLE clients COALESCE PARTITION 18;

ERROR 1478 (HY000): Cannot remove all partitions, use DROP TABLE instead

要将clients表的分区数从12增加到18.使用ALTER TABLE ... ADD PARTITION，如下所示：

ALTER TABLE clients ADD PARTITION PARTITIONS 6;

22.3.3 使用表交换分区和子分区

在MySQL 5.7中，可以使用ALTER TABLE pt EXCHANGE PARTITION p WITH TABLE nt将表分区或子分区与表交换，其中pt是分区表，p是要与未分区表nt交换的pt的分区或子分区，只要满足以下陈述：

表nt本身不是分区的。
表nt不是临时表。
表pt和nt的结构在其他方面是相同的。
表nt不包含外键引用，并且没有其他表具有引用nt的任何外键。
nt中没有位于p的分区定义边界之外的行。如果使用WITHOUT VALIDATION选项，则此条件不适用。 MySQL 5.7.5中添加了[{WITH | WITHOUT} VALIDATION]选项。
对于InnoDB表，两个表都使用相同的行格式。要确定InnoDB表的行格式，请查询INFORMATION_SCHEMA.INNODB_SYS_TABLES。
nt没有任何使用DATA DIRECTORY选项的分区。 MySQL 5.7.25及更高版本中的InnoDB表解除了此限制。

除了ALTER TABLE语句通常需要的ALTER，INSERT和CREATE权限之外，还必须具有DROP权限才能执行ALTER TABLE ... EXCHANGE PARTITION。

您还应该了解ALTER TABLE ... EXCHANGE PARTITION的以下影响：

执行ALTER TABLE ... EXCHANGE PARTITION不会在分区表或被交换的表上调用任何触发器。
被交换表中的任何AUTO_INCREMENT列都将被重置。
与ALTER TABLE ... EXCHANGE PARTITION一起使用时，IGNORE关键字。

此处显示了ALTER TABLE ... EXCHANGE PARTITION语句的语法，其中pt是分区表，p是要交换的分区或子分区，nt是要与p交换的非分区表：

ALTER TABLE pt EXCHANGE PARTITION p WITH TABLE nt;

（可选）您可以附加WITH VALIDATION或WITHOUT VALIDATION子句。指定WITHOUT VALIDATION时，ALTER TABLE ... EXCHANGE PARTITION操作在将分区更换为非分区表时不执行逐行验证，从而允许数据库管理员承担确保行位于分区定义边界内的责任。 WITH VALIDATION是默认行为，无需明确指定。 MySQL 5.7.5中添加了[{WITH | WITHOUT} VALIDATION]选项。

--和oracle 一样可以加速分区交换

在单个ALTER TABLE EXCHANGE PARTITION语句中只能用且仅一个非分区表交换且仅一个分区或子分区。要交换多个分区或子分区，请使用多个ALTER TABLE EXCHANGE PARTITION语句。 EXCHANGE PARTITION不能与其他ALTER TABLE选项组合使用。分区表使用的分区和（如果适用）子分区可以是MySQL 5.7中支持的任何类型。

使用非分区表交换子分区

您还可以使用ALTER TABLE ... EXCHANGE PARTITION语句将表的子分区（请参见第22.2.6节“子分区”）与非分区表进行交换。在下面的示例中，我们首先创建一个由RANGE分区并由KEY子分区的表es，在表e中填充此表，然后创建表的空的非分区副本es2，如下所示：

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

-> );

Query OK, 0 rows affected (2.76 sec)

mysql> INSERT INTO es VALUES

-> (1669, "Jim", "Smith"),

-> (337, "Mary", "Jones"),

-> (16, "Frank", "White"),

-> (2005, "Linda", "Black");

Query OK, 4 rows affected (0.04 sec) Records: 4 Duplicates: 0 Warnings: 0

mysql> CREATE TABLE es2 LIKE es;

Query OK, 0 rows affected (1.27 sec)

mysql> ALTER TABLE es2 REMOVE PARTITIONING;

Query OK, 0 rows affected (0.70 sec) Records: 0 Duplicates: 0 Warnings: 0

虽然我们在创建表格时没有明确命名任何子分区，但是我们可以通过在从该表中选择时包含INFORMATION_SCHEMA中PARTITIONS表的SUBPARTITION_NAME来获取这些子分区的生成名称，如下所示：

mysql> 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 |

+----------------+-------------------+------------+

8 rows in set (0.00 sec)

以下ALTER TABLE语句将子分区p3sp0表与非分区表es2进行交换：

mysql> ALTER TABLE es EXCHANGE PARTITION p3sp0 WITH TABLE es2; Query OK, 0 rows affected (0.29 sec)

您可以通过发出以下查询来验证是否已交换行：

mysql> SELECT PARTITION_NAME, SUBPARTITION_NAME, TABLE_ROWS

-> FROM INFORMATION_SCHEMA.PARTITIONS

-> WHERE TABLE_NAME = 'es';

mysql> SELECT * FROM es2;

如果表是子分区的，则只能使用未分区的表交换表的子分区（而不是整个分区），如下所示：

mysql> ALTER TABLE es EXCHANGE PARTITION p3 WITH TABLE es2; ERROR 1704 (HY000): Subpartitioned table, use subpartition instead of partition

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

22.3.4 分区的维护

可以在MySQL 5.7中的分区表上使用用于此类目的的SQL语句执行许多表和分区维护任务。

可以使用分区表支持的语句CHECK TABLE，OPTIMIZE TABLE，ANALYZE TABLE和REPAIR TABLE来完成分区表的表维护。

您可以使用ALTER TABLE的许多扩展来直接在一个或多个分区上执行此类型的操作，如以下列表中所述：

重建分区

重建分区; 这与删除存储在分区中的所有记录，然后重新插入它们具有相同的效果。这可用于碎片整理的目的。

例如：

ALTER TABLE t1 REBUILD PARTITION p0, p1;

优化分区

如果已从分区中删除了大量行，或者如果对具有可变长度行的分区表（即具有VARCHAR，BLOB或TEXT列）进行了许多更改，则可以使用ALTER TABLE ... OPTIMIZE PARTITION回收任何未使用的空间并对分区数据文件进行碎片整理。

例子：

ALTER TABLE t1 OPTIMIZE PARTITION p0, p1;

在给定分区上使用OPTIMIZE PARTITION相当于在该分区上运行CHECK PARTITION，ANALYZE PARTITION和REPAIR PARTITION。

一些MySQL存储引擎，包括InnoDB，不支持按分区优化; 在这些情况下，ALTER TABLE ... OPTIMIZE PARTITION分析并重建整个表，并导致发出适当的警告。（Bug＃11751825，Bug＃42822）请使用ALTER TABLE ... REBUILD PARTITION和ALTER TABLE ... ANALYZE PARTITION，以避免此问题。

分析分区 这将读取并存储分区的key。

ALTER TABLE t1 ANALYZE PARTITION p3;

修复分区。这修复被损坏的分区。

例如：

ALTER TABLE t1 REPAIR PARTITION p0,p1;

通常，当分区包含重复键错误时，REPAIR PARTITION会失败。在MySQL 5.7.2及更高版本中，您可以将ALTER IGNORE TABLE与此选项一起使用，在这种情况下，由于存在重复键而无法移动的所有行都将从分区中删除（Bug＃16900947）。

检查分区。

您可以检查分区是否存在错误，其方式与使用CHECK TABLE 非分区表的方式非常相似。

ALTER TABLE trb3 CHECK PARTITION p1;

此命令将告诉您表t1的分区p1中的数据或索引是否已损坏。如果是这种情况，请使用ALTER TABLE ... REPAIR PARTITION来修复分区。

通常，当分区包含重复键错误时，CHECK PARTITION会失败。在MySQL 5.7.2及更高版本中，您可以将ALTER IGNORE TABLE与此选项一起使用，在这种情况下，该语句将返回找到重复键冲突的分区中每行的内容。仅报告表的分区表达式中的列的值。（bug＃16900947）

刚刚显示的列表中的每个语句也支持关键字ALL代替分区名称列表。使用ALL会导致语句作用于表中的所有分区。

分区表不支持使用mysqlcheck和myisamchk。

在MySQL 5.7中，您还可以使用ALTER TABLE ... TRUNCATE PARTITION截断分区。此语句可用于从一个或多个分区中删除所有行，其方式与TRUNCATE TABLE从表中删除所有行的方式非常相似。

ALTER TABLE ... TRUNCATE PARTITION ALL截断表中的所有分区。

在MySQL 5.7.2之前，子分区上不允许进行ANALYZE，CHECK，OPTIMIZE，REBUILD，REPAIR和TRUNCATE操作（Bug＃14028340，Bug＃65184）。

22.3.5 获取分区信息

本节讨论获取有关现有分区的信息，这可以通过多种方式完成。获取此类信息的方法包括：

使用SHOW CREATE TABLE语句查看用于创建分区表的分区子句。
使用SHOW TABLE STATUS语句确定表是否已分区。
查询INFORMATION_SCHEMA.PARTITIONS表。
使用EXPLAIN SELECT语句查看给定SELECT使用的分区。

如本章其他部分所述，SHOW CREATE TABLE在其输出中包含用于创建分区表的PARTITION BY子句。例如：

SHOW CREATE TABLE trb3\G

除了Create_options列包含已分区的字符串之外，分区表的SHOW TABLE STATUS的输出与非分区表的输出相同。 Engine列包含表的所有分区使用的存储引擎的名称。（有关此语句的更多信息，请参见第13.7.5.36节“SHOW TABLE STATUS语法”。）

您还可以从INFORMATION_SCHEMA获取有关分区的信息，其中包含PARTITIONS表。请参见第24.16节“INFORMATION_SCHEMA PARTITIONS表”。

可以使用EXPLAIN确定给定SELECT查询中涉及分区表的哪些分区。 EXPLAIN输出中的partitions列列出了查询将匹配记录的分区。

假设您创建并填充了表trb1，如下所示：

CREATE TABLE trb1 (id INT, name VARCHAR(50),

purchased DATE)

PARTITION BY RANGE(id) (

PARTITION p0 VALUES LESS THAN (3),

PARTITION p1 VALUES LESS THAN (7),

PARTITION p2 VALUES LESS THAN (9),

PARTITION p3 VALUES LESS THAN (11)

);

INSERT INTO trb1 VALUES (1, 'desk organiser', '2003-10-15'),

(2, 'CD player', '1993-11-05'),

(3, 'TV set', '1996-03-10'),

(4, 'bookcase', '1982-01-10'),

(5, 'exercise bike', '2004-05-09'),

(6, 'sofa', '1987-06-05'),

(7, 'popcorn maker', '2001-11-22'),

(8, 'aquarium', '1992-08-04'),

(9, 'study desk', '1984-09-16'),

(10, 'lava lamp', '1998-12-25');

您可以查看查询中使用的分区，例如SELECT * FROM trb1;，如下所示：

mysql> EXPLAIN SELECT * FROM trb1\G

*************************** 1. row ***************************

id: 1

select_type: SIMPLE

table: trb1

partitions: p0,p1,p2,p3

type: ALL

possible_keys: NULL

key: NULL

key_len:NULL

ref: NULL

rows: 10

Extra: Using filesort

在这种情况下，搜索所有四个分区。但是，当查询中添加了使用分区键的限制条件时，您可以看到只扫描包含匹配值的分区，如下所示：

mysql> EXPLAIN SELECT * FROM trb1 WHERE id < 5\G

*************************** 1. row ***************************

id: 1

select_type: SIMPLE

table: trb1

partitions: p0,p1

type: ALL

possible_keys: NULL

key: NULL

key_len: NULL

ref: NULL

rows: 10

Extra: Using where

如果使用EXPLAIN PARTITIONS检查针对非分区表的查询，则不会产生错误，但分区列的值始终为NULL。

EXPLAIN输出的rows列显示表中的总行数。

分享好友

分享这个小栈给你的朋友们，一起进步吧。

MySQL干货资料

创建时间：2020-05-06 14:18:32

每天都有干货输出哦

展开

订阅须知

• 所有用户可根据关注领域订阅专区或所有专区

• 付费订阅：虚拟交易，一经交易不退款；若特殊情况，可3日内客服咨询

• 专区发布评论属默认订阅所评论专区（除付费小栈外）

技术专家

查看更多

飘絮絮絮丶
专家