MySQL :: MySQL 8.4 参考手册 :: 26.3.4 分区的维护

MySQL 8.4 参考手册 · 中文镜像

正文为中文，英文原文默认隐藏。点击任意中文段落即可展开 / 收起对应英文原文。

译文由机器翻译生成，仅供理解参考；有疑问请以官方英文原文为准。

MySQL 8.4 参考手册 / 26 分区 / 26.3 分区管理 / 26.3.4 分区的维护

中文点击中文段落可显示 / 隐藏英文原文本页译文覆盖 100%

26.3.4 分区的维护26.3.4 Maintenance of Partitions

可以使用用于此类目的 SQL 语句对分区表执行许多表和分区维护任务。A number of table and partition maintenance tasks can be carried out on partitioned tables using SQL statements intended for such purposes.

分区表的表维护可以使用以下语句完成：CHECK TABLE、OPTIMIZE TABLE、ANALYZE TABLE 和 REPAIR TABLE，这些语句支持分区表。Table maintenance of partitioned tables can be accomplished using the statements CHECK TABLE, OPTIMIZE TABLE, ANALYZE TABLE, and REPAIR TABLE, which are supported for partitioned tables.

您可以使用对 ALTER TABLE 的多种扩展，直接对一个或多个分区执行此类操作，如下列表所述：You can use a number of extensions to ALTER TABLE for performing operations of this type on one or more partitions directly, as described in the following list:

重建分区。 重建分区；这与删除分区中存储的所有记录然后重新插入它们具有相同的效果。这对于碎片整理是有用的。Rebuilding partitions. Rebuilds the partition; this has the same effect as dropping all records stored in the partition, then reinserting them. This can be useful for purposes of defragmentation.
示例：Example:
```
ALTER TABLE t1 REBUILD PARTITION p0, p1;
```
优化分区。 如果您从分区中删除了大量行，或者对具有可变长度行的分区表（即具有 VARCHAR、BLOB 或 TEXT 列）进行了多次更改，您可以使用 ALTER TABLE ... OPTIMIZE PARTITION 来回收未使用的空间并对分区数据文件进行碎片整理。Optimizing partitions. If you have deleted a large number of rows from a partition or if you have made many changes to a partitioned table with variable-length rows (that is, having VARCHAR, BLOB, or TEXT columns), you can use ALTER TABLE ... OPTIMIZE PARTITION to reclaim any unused space and to defragment the partition data file.
示例：Example:
```
ALTER TABLE t1 OPTIMIZE PARTITION p0, p1;
```
在给定分区上使用 OPTIMIZE PARTITION 相当于对该分区运行 CHECK PARTITION、ANALYZE PARTITION 和 REPAIR PARTITION。Using OPTIMIZE PARTITION on a given partition is equivalent to running CHECK PARTITION, ANALYZE PARTITION, and REPAIR PARTITION on that partition.
一些 MySQL 存储引擎，包括 InnoDB，不支持按分区优化；在这些情况下，ALTER TABLE ... OPTIMIZE PARTITION 会分析并重建整个表，并发出相应警告。（Bug #11751825, Bug #42822）请改用 ALTER TABLE ... REBUILD PARTITION 和 ALTER TABLE ... ANALYZE PARTITION 来避免此问题。Some MySQL storage engines, including InnoDB, do not support per-partition optimization; in these cases, ALTER TABLE ... OPTIMIZE PARTITION analyzes and rebuilds the entire table, and causes an appropriate warning to be issued. (Bug #11751825, Bug #42822) Use ALTER TABLE ... REBUILD PARTITION and ALTER TABLE ... ANALYZE PARTITION instead, to avoid this issue.
分析分区。 这会读取并存储分区的键分布。Analyzing partitions. This reads and stores the key distributions for partitions.
示例：Example:
```
ALTER TABLE t1 ANALYZE PARTITION p3;
```
修复分区。 这会修复损坏的分区。Repairing partitions. This repairs corrupted partitions.
示例：Example:
```
ALTER TABLE t1 REPAIR PARTITION p0,p1;
```
通常，当分区包含重复键错误时，REPAIR PARTITION 会失败。你可以在此选项下使用 ALTER IGNORE TABLE，在这种情况下，所有因存在重复键而无法移动的行都会从分区中移除（Bug #16900947）。Normally, REPAIR PARTITION fails when the partition contains duplicate key errors. You can use ALTER IGNORE TABLE with this option, in which case all rows that cannot be moved due to the presence of duplicate keys are removed from the partition (Bug #16900947).
检查分区。 你可以以类似于对非分区表使用 CHECK TABLE 的方式检查分区中的错误。Checking partitions. You can check partitions for errors in much the same way that you can use CHECK TABLE with nonpartitioned tables.
示例：Example:
```
ALTER TABLE trb3 CHECK PARTITION p1;
```
此语句可以告诉您表 t1 的分区 p1 中的数据或索引是否已损坏。如果是这种情况，请使用 ALTER TABLE ... REPAIR PARTITION 来修复分区。This statement tells you whether the data or indexes in partition p1 of table t1 are corrupted. If this is the case, use ALTER TABLE ... REPAIR PARTITION to repair the partition.
通常，当分区中存在重复键错误时，CHECK PARTITION 会失败。您可以使用带有此选项的 ALTER IGNORE TABLE，在这种情况下，该语句会返回在分区中发现重复键违规的每一行的内容。仅报告表分区表达式中列的值。(Bug #16900947)Normally, CHECK PARTITION fails when the partition contains duplicate key errors. You can use ALTER IGNORE TABLE with this option, in which case the statement returns the contents of each row in the partition where a duplicate key violation is found. Only the values for the columns in the partitioning expression for the table are reported. (Bug #16900947)

上面列表中的每个语句也支持使用关键字 ALL 来替代分区名称列表。使用 ALL 会使该语句作用于表中的所有分区。Each of the statements in the list just shown also supports the keyword ALL in place of the list of partition names. Using ALL causes the statement to act on all partitions in the table.

您也可以使用 ALTER TABLE ... TRUNCATE PARTITION 来截断分区。该语句可以用来删除一个或多个分区中的所有行，其方式与 TRUNCATE TABLE 删除表中所有行类似。You can also truncate partitions using ALTER TABLE ... TRUNCATE PARTITION. This statement can be used to delete all rows from one or more partitions in much the same way that TRUNCATE TABLE deletes all rows from a table.

ALTER TABLE ... TRUNCATE PARTITION ALL 会截断表中的所有分区。ALTER TABLE ... TRUNCATE PARTITION ALL truncates all partitions in the table.

首页上一级下一节