8.4 中文镜像

MySQL 8.4 参考手册 · 中文镜像

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

打开官方英文原文 ↗

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

MySQL 8.4 参考手册 / 26 分区 / 26.6 分区的约束和限制
中文点击中文段落可显示 / 隐藏英文原文本页译文覆盖 100%

26.6 分区的限制与局限26.6 Restrictions and Limitations on Partitioning

本节讨论当前 MySQL 分区支持的限制与局限。This section discusses current restrictions and limitations on MySQL partitioning support.

禁止的构造。 分区表达式中不允许使用以下构造：Prohibited constructs.  The following constructs are not permitted in partitioning expressions:

  • 存储过程、存储函数、可加载函数或插件。Stored procedures, stored functions, loadable functions, or plugins.

  • 声明变量或用户变量。Declared variables or user variables.

有关允许在分区表达式中使用的 SQL 函数列表，请参见 第 26.6.3 节，“分区与函数有关的限制”。For a list of SQL functions which are permitted in partitioning expressions, see Section 26.6.3, “Partitioning Limitations Relating to Functions”.

算术和逻辑运算符。 在分区表达式中可以使用算术运算符 +、- 和 *。然而，结果必须是整数值或 NULL（在 [LINEAR] KEY 分区情况下除外，本章其他部分有详细讨论；有关更多信息，请参见 第 26.2 节，“分区类型”）。Arithmetic and logical operators.  Use of the arithmetic operators +, -, and * is permitted in partitioning expressions. However, the result must be an integer value or NULL (except in the case of [LINEAR] KEY partitioning, as discussed elsewhere in this chapter; see Section 26.2, “Partitioning Types”, for more information).

DIV 运算符也被支持；/ 运算符不允许使用。The DIV operator is also supported; the / operator is not permitted.

位运算符 |、&、^、<<、>> 和 ~ 在分区表达式中不允许使用。The bit operators |, &, ^, <<, >>, and ~ are not permitted in partitioning expressions.

服务器 SQL 模式。 使用用户定义分区的表不会保留其创建时生效的 SQL 模式。正如本手册其他地方所讨论的（参见 第 7.1.11 节，“服务器 SQL 模式”），许多 MySQL 函数和操作符的结果可能会根据服务器 SQL 模式而变化。因此，在创建分区表之后的任何时间更改 SQL 模式都可能导致这些表的行为发生重大变化，并且很容易导致数据损坏或丢失。基于这些原因，强烈建议在创建分区表之后不要更改服务器 SQL 模式。Server SQL mode.  Tables employing user-defined partitioning do not preserve the SQL mode in effect at the time that they were created. As discussed elsewhere in this Manual (see Section 7.1.11, “Server SQL Modes”), the results of many MySQL functions and operators may change according to the server SQL mode. Therefore, a change in the SQL mode at any time after the creation of partitioned tables may lead to major changes in the behavior of such tables, and could easily lead to corruption or loss of data. For these reasons, it is strongly recommended that you never change the server SQL mode after creating partitioned tables.

对于服务器 SQL 模式中的一个变化导致分区表无法使用的情况，请考虑以下 CREATE TABLE 语句，只有在 NO_UNSIGNED_SUBTRACTION 模式生效时才能成功执行：For one such change in the server SQL mode making a partitioned tables unusable, consider the following CREATE TABLE statement, which can be executed successfully only if the NO_UNSIGNED_SUBTRACTION mode is in effect:

mysql> SELECT @@sql_mode;
+------------+
| @@sql_mode |
+------------+
|            |
+------------+
1 row in set (0.00 sec)

mysql> CREATE TABLE tu (c1 BIGINT UNSIGNED)
    ->   PARTITION BY RANGE(c1 - 10) (
    ->     PARTITION p0 VALUES LESS THAN (-5),
    ->     PARTITION p1 VALUES LESS THAN (0),
    ->     PARTITION p2 VALUES LESS THAN (5),
    ->     PARTITION p3 VALUES LESS THAN (10),
    ->     PARTITION p4 VALUES LESS THAN (MAXVALUE)
    -> );
ERROR 1563 (HY000): Partition constant is out of partition function domain

mysql> SET sql_mode='NO_UNSIGNED_SUBTRACTION';
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT @@sql_mode;
+-------------------------+
| @@sql_mode              |
+-------------------------+
| NO_UNSIGNED_SUBTRACTION |
+-------------------------+
1 row in set (0.00 sec)

mysql> CREATE TABLE tu (c1 BIGINT UNSIGNED)
    ->   PARTITION BY RANGE(c1 - 10) (
    ->     PARTITION p0 VALUES LESS THAN (-5),
    ->     PARTITION p1 VALUES LESS THAN (0),
    ->     PARTITION p2 VALUES LESS THAN (5),
    ->     PARTITION p3 VALUES LESS THAN (10),
    ->     PARTITION p4 VALUES LESS THAN (MAXVALUE)
    -> );
Query OK, 0 rows affected (0.05 sec)

如果在创建 tu 后移除 NO_UNSIGNED_SUBTRACTION 服务器 SQL 模式，你可能将无法再访问此表：If you remove the NO_UNSIGNED_SUBTRACTION server SQL mode after creating tu, you may no longer be able to access this table:

mysql> SET sql_mode='';
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT * FROM tu;
ERROR 1563 (HY000): Partition constant is out of partition function domain
mysql> INSERT INTO tu VALUES (20);
ERROR 1563 (HY000): Partition constant is out of partition function domain

另请参见 第 7.1.11 节，“服务器 SQL 模式”。See also Section 7.1.11, “Server SQL Modes”.

服务器 SQL 模式也会影响分区表的复制。源服务器和副本服务器的 SQL 模式不一致可能导致分区表达式的计算结果不同；这可能导致源服务器和副本服务器的表中数据在分区之间的分布不同，甚至可能导致在源服务器上成功的分区表插入操作在副本服务器上失败。为了获得最佳效果，您应始终在源服务器和副本服务器上使用相同的服务器 SQL 模式。Server SQL modes also impact replication of partitioned tables. Disparate SQL modes on source and replica can lead to partitioning expressions being evaluated differently; this can cause the distribution of data among partitions to be different in the source's and replica's copies of a given table, and may even cause inserts into partitioned tables that succeed on the source to fail on the replica. For best results, you should always use the same server SQL mode on the source and on the replica.

性能考虑。 分区操作对性能的一些影响如下所示：Performance considerations.  Some effects of partitioning operations on performance are given in the following list:

  • 文件系统操作。 分区和重新分区操作（例如 ALTER TABLE 使用 PARTITION BY ...，REORGANIZE PARTITION 或 REMOVE PARTITIONING）依赖于文件系统操作来实现。这意味着这些操作的速度会受到文件系统类型和特性、磁盘速度、交换空间、操作系统的文件处理效率以及与文件处理相关的 MySQL 服务器选项和变量等因素的影响。特别是，你应该确保 large_files_support 已启用，并且 open_files_limit 已正确设置。涉及 InnoDB 表的分区和重新分区操作可以通过启用 innodb_file_per_table 来提高效率。File system operations.  Partitioning and repartitioning operations (such as ALTER TABLE with PARTITION BY ..., REORGANIZE PARTITION, or REMOVE PARTITIONING) depend on file system operations for their implementation. This means that the speed of these operations is affected by such factors as file system type and characteristics, disk speed, swap space, file handling efficiency of the operating system, and MySQL server options and variables that relate to file handling. In particular, you should make sure that large_files_support is enabled and that open_files_limit is set properly. Partitioning and repartitioning operations involving InnoDB tables may be made more efficient by enabling innodb_file_per_table.

    另请参见 最大分区数。See also Maximum number of partitions.

  • 表锁。 通常，对表执行分区操作的进程会对该表加写锁。来自这些表的读取操作受影响较小；待处理的 INSERT 和 UPDATE 操作会在分区操作完成后立即执行。有关 InnoDB 特定的此限制的例外，请参见 分区操作。Table locks.  Generally, the process executing a partitioning operation on a table takes a write lock on the table. Reads from such tables are relatively unaffected; pending INSERT and UPDATE operations are performed as soon as the partitioning operation has completed. For InnoDB-specific exceptions to this limitation, see Partitioning Operations.

  • 索引；分区裁剪。 与非分区表一样，合理使用索引可以显著加快分区表的查询速度。此外，设计分区表及其查询以利用分区裁剪可以大大提高性能。更多信息请参见第 26.4 节，“分区裁剪”。Indexes; partition pruning.  As with nonpartitioned tables, proper use of indexes can speed up queries on partitioned tables significantly. In addition, designing partitioned tables and queries on these tables to take advantage of partition pruning can improve performance dramatically. See Section 26.4, “Partition Pruning”, for more information.

    分区表支持索引条件下推功能。详见第 10.2.1.6 节，“索引条件下推优化”。Index condition pushdown is supported for partitioned tables. See Section 10.2.1.6, “Index Condition Pushdown Optimization”.

  • 使用 LOAD DATA 的性能。 在 MySQL 8.4 中，LOAD DATA 使用缓冲来提高性能。您应该注意，为了实现这一点，每个分区的缓冲区会使用 130 KB 的内存。Performance with LOAD DATA.  In MySQL 8.4, LOAD DATA uses buffering to improve performance. You should be aware that the buffer uses 130 KB memory per partition to achieve this.

最大分区数。 对于未使用 NDB 存储引擎的给定表，最大可能的分区数为 8192。此数字包括子分区。Maximum number of partitions.  The maximum possible number of partitions for a given table not using the NDB storage engine is 8192. This number includes subpartitions.

使用 NDB 存储引擎的表可定义的最大用户分区数量取决于所使用的 NDB Cluster 软件版本、数据节点数量及其他因素。有关更多信息，请参见 NDB 和用户定义分区。The maximum possible number of user-defined partitions for a table using the NDB storage engine is determined according to the version of the NDB Cluster software being used, the number of data nodes, and other factors. See NDB and user-defined partitioning, for more information.

如果在创建具有大量分区（但少于最大值）的表时，遇到类似 Got error ... from storage engine: Out of resources when opening file 的错误信息，您可以尝试通过增加 open_files_limit 系统变量的值来解决此问题。不过，这取决于操作系统，在所有平台上可能不可行或不建议；有关更多信息，请参见 B.3.2.16 节，“文件未找到及类似错误”。在某些情况下，由于其他方面的考虑，使用大量（数百个）分区也可能不建议，因此使用更多分区并不自动意味着结果更好。If, when creating tables with a large number of partitions (but less than the maximum), you encounter an error message such as Got error ... from storage engine: Out of resources when opening file, you may be able to address the issue by increasing the value of the open_files_limit system variable. However, this is dependent on the operating system, and may not be possible or advisable on all platforms; see Section B.3.2.16, “File Not Found and Similar Errors”, for more information. In some cases, using large numbers (hundreds) of partitions may also not be advisable due to other concerns, so using more partitions does not automatically lead to better results.

另请参见 文件系统操作。See also File system operations.

分区的 InnoDB 表不支持外键。 使用 InnoDB 存储引擎的分区表不支持外键。更具体地说，这意味着以下两个语句成立：Foreign keys not supported for partitioned InnoDB tables.  Partitioned tables using the InnoDB storage engine do not support foreign keys. More specifically, this means that the following two statements are true:

  1. 使用用户定义分区的 InnoDB 表的定义中不得包含外键引用；其定义中包含外键引用的 InnoDB 表不得分区。No definition of an InnoDB table employing user-defined partitioning may contain foreign key references; no InnoDB table whose definition contains foreign key references may be partitioned.

  2. 没有 InnoDB 表定义可以包含对用户分区表的外键引用；没有用户定义分区的 InnoDB 表可以包含被外键引用的列。No InnoDB table definition may contain a foreign key reference to a user-partitioned table; no InnoDB table with user-defined partitioning may contain columns referenced by foreign keys.

刚列出的这些限制的适用范围包括所有使用 InnoDB 存储引擎的表。CREATE TABLE 和 ALTER TABLE 语句如果会导致表违反这些限制，则不允许执行。The scope of the restrictions just listed includes all tables that use the InnoDB storage engine. CREATE TABLE and ALTER TABLE statements that would result in tables violating these restrictions are not allowed.

ALTER TABLE ... ORDER BY。 对分区表运行 ALTER TABLE ... ORDER BY column 语句只会在每个分区内对行进行排序。ALTER TABLE ... ORDER BY.  An ALTER TABLE ... ORDER BY column statement run against a partitioned table causes ordering of rows only within each partition.

ADD COLUMN ... ALGORITHM=INSTANT。 一旦在分区表上执行 ALTER TABLE ... ADD COLUMN ... ALGORITHM=INSTANT，就无法再与此表交换分区。ADD COLUMN ... ALGORITHM=INSTANT.  Once you perform ALTER TABLE ... ADD COLUMN ... ALGORITHM=INSTANT on a partitioned table, it is no longer possible to exchange partitions with this table.

修改主键对 REPLACE 语句的影响。 在某些情况下（参见 第 26.6.1 节，“分区键、主键和唯一键”）修改表的主键可能是可取的。请注意，如果您的应用程序使用 REPLACE 语句并进行此操作，这些语句的结果可能会发生巨大变化。有关更多信息和示例，请参见 第 15.2.12 节，“REPLACE 语句”。Effects on REPLACE statements by modification of primary keys.  It can be desirable in some cases (see Section 26.6.1, “Partitioning Keys, Primary Keys, and Unique Keys”) to modify a table's primary key. Be aware that, if your application uses REPLACE statements and you do this, the results of these statements can be drastically altered. See Section 15.2.12, “REPLACE Statement”, for more information and an example.

全文索引。 分区表不支持 FULLTEXT 索引或搜索。FULLTEXT indexes.  Partitioned tables do not support FULLTEXT indexes or searches.

空间列。 具有空间数据类型的列，例如 POINT 或 GEOMETRY，不能用于分区表。Spatial columns.  Columns with spatial data types such as POINT or GEOMETRY cannot be used in partitioned tables.

临时表。 临时表不能进行分区。Temporary tables.  Temporary tables cannot be partitioned.

日志表。 无法对日志表进行分区；对这样的表执行 ALTER TABLE ... PARTITION BY ... 语句会出错。Log tables.  It is not possible to partition the log tables; an ALTER TABLE ... PARTITION BY ... statement on such a table fails with an error.

分区键的数据类型。 分区键必须是整数列或解析为整数的表达式。不能使用包含 ENUM 列的表达式。列或表达式的值也可以为 NULL；请参阅 第 26.2.7 节，“MySQL 分区如何处理 NULL”。Data type of partitioning key.  A partitioning key must be either an integer column or an expression that resolves to an integer. Expressions employing ENUM columns cannot be used. The column or expression value may also be NULL; see Section 26.2.7, “How MySQL Partitioning Handles NULL”.

此限制有两个例外：There are two exceptions to this restriction:

  1. 当按 [LINEAR] KEY 分区时，可以使用除 TEXT 或 BLOB 之外的任何有效 MySQL 数据类型的列作为分区键，因为内部键哈希函数可以从这些类型生成正确的数据类型。例如，以下两个 CREATE TABLE 语句是有效的：When partitioning by [LINEAR] KEY, it is possible to use columns of any valid MySQL data type other than TEXT or BLOB as partitioning keys, because the internal key-hashing functions produce the correct data type from these types. For example, the following two CREATE TABLE statements are valid:

    CREATE TABLE tkc (c1 CHAR)
PARTITION BY KEY(c1)
PARTITIONS 4;

CREATE TABLE tke
    ( c1 ENUM('red', 'orange', 'yellow', 'green', 'blue', 'indigo', 'violet') )
PARTITION BY LINEAR KEY(c1)
PARTITIONS 6;

  2. 在按 RANGE COLUMNS 或 LIST COLUMNS 进行分区时，可以使用字符串、DATE 和 DATETIME 列。例如，以下每个 CREATE TABLE 语句都是有效的：When partitioning by RANGE COLUMNS or LIST COLUMNS, it is possible to use string, DATE, and DATETIME columns. For example, each of the following CREATE TABLE statements is valid:

    CREATE TABLE rc (c1 INT, c2 DATE)
PARTITION BY RANGE COLUMNS(c2) (
    PARTITION p0 VALUES LESS THAN('1990-01-01'),
    PARTITION p1 VALUES LESS THAN('1995-01-01'),
    PARTITION p2 VALUES LESS THAN('2000-01-01'),
    PARTITION p3 VALUES LESS THAN('2005-01-01'),
    PARTITION p4 VALUES LESS THAN(MAXVALUE)
);

CREATE TABLE lc (c1 INT, c2 CHAR(1))
PARTITION BY LIST COLUMNS(c2) (
    PARTITION p0 VALUES IN('a', 'd', 'g', 'j', 'm', 'p', 's', 'v', 'y'),
    PARTITION p1 VALUES IN('b', 'e', 'h', 'k', 'n', 'q', 't', 'w', 'z'),
    PARTITION p2 VALUES IN('c', 'f', 'i', 'l', 'o', 'r', 'u', 'x', NULL)
);

前面提到的例外情况不适用于 BLOB 或 TEXT 列类型。Neither of the preceding exceptions applies to BLOB or TEXT column types.

子查询。 分区键不能是子查询，即使该子查询解析为整数值或NULL。Subqueries.  A partitioning key may not be a subquery, even if that subquery resolves to an integer value or NULL.

键分区不支持列索引前缀。 当创建一个按键分区的表时，分区键中的任何使用列前缀的列都不允许在表的分区函数中使用。考虑以下CREATE TABLE语句，它包含三个VARCHAR列，并且其主键使用所有三列，并为其中一列指定了前缀。该语句会被拒绝并显示错误，如下所示：Column index prefixes not supported for key partitioning.  When creating a table that is partitioned by key, any columns in the partitioning key which use column prefixes are not allowed in the table's partitioning function. Consider the following CREATE TABLE statement, which has three VARCHAR columns, and whose primary key uses all three columns and specifies a prefix for one of them. This statement is rejected with an error, as shown here:

mysql> USE d;
Database changed
mysql> CREATE TABLE t1 (
    ->     a VARCHAR(10000),
    ->     b VARCHAR(25),
    ->     c VARCHAR(10),
    ->     PRIMARY KEY (a(10), b, c)
    -> ) PARTITION BY KEY() PARTITIONS 2;
ERROR 6123 (HY000): Column 'd.t1.a' having prefix key part 'a(10)' in the
PARTITION BY KEY() clause is not supported.

有关按键分区表的一般信息，请参见 第 26.2.5 节，“KEY 分区”。For general information about partitioning tables by key, see Section 26.2.5, “KEY Partitioning”.

子分区的问题。 子分区必须使用 HASH 或 KEY 分区。只有 RANGE 和 LIST 分区可以进行子分区；HASH 和 KEY 分区不能进行子分区。Issues with subpartitions.  Subpartitions must use HASH or KEY partitioning. Only RANGE and LIST partitions may be subpartitioned; HASH and KEY partitions cannot be subpartitioned.

SUBPARTITION BY KEY 要求明确指定子分区列，而不像 PARTITION BY KEY 那样可以省略（在省略的情况下，默认使用表的主键列）。考虑如下语句创建的表： SUBPARTITION BY KEY requires that the subpartitioning column or columns be specified explicitly, unlike the case with PARTITION BY KEY, where it can be omitted (in which case the table's primary key column is used by default). Consider the table created by this statement:

CREATE TABLE ts (
    id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(30)
);

你可以使用如下语句创建一个具有相同列、按 KEY 分区的表：You can create a table having the same columns, partitioned by KEY, using a statement such as this one:

CREATE TABLE ts (
    id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(30)
)
PARTITION BY KEY()
PARTITIONS 4;

前面的语句会被当作这样写的，使用表的主键列作为分区列：The previous statement is treated as though it had been written like this, with the table's primary key column used as the partitioning column:

CREATE TABLE ts (
    id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(30)
)
PARTITION BY KEY(id)
PARTITIONS 4;

然而，尝试使用默认列作为子分区列创建子分区表的以下语句会失败，必须指定列，语句才能成功，如下所示：However, the following statement that attempts to create a subpartitioned table using the default column as the subpartitioning column fails, and the column must be specified for the statement to succeed, as shown here:

mysql> CREATE TABLE ts (
    ->     id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    ->     name VARCHAR(30)
    -> )
    -> PARTITION BY RANGE(id)
    -> SUBPARTITION BY KEY()
    -> SUBPARTITIONS 4
    -> (
    ->     PARTITION p0 VALUES LESS THAN (100),
    ->     PARTITION p1 VALUES LESS THAN (MAXVALUE)
    -> );
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that
corresponds to your MySQL server version for the right syntax to use near ')

mysql> CREATE TABLE ts (
    ->     id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    ->     name VARCHAR(30)
    -> )
    -> PARTITION BY RANGE(id)
    -> SUBPARTITION BY KEY(id)
    -> SUBPARTITIONS 4
    -> (
    ->     PARTITION p0 VALUES LESS THAN (100),
    ->     PARTITION p1 VALUES LESS THAN (MAXVALUE)
    -> );
Query OK, 0 rows affected (0.07 sec)

这是一个已知问题（见 Bug #51470）。This is a known issue (see Bug #51470).

数据目录和索引目录选项。 表级的 DATA DIRECTORY 和 INDEX DIRECTORY 选项会被忽略（参见 Bug #32091）。您可以在 InnoDB 表的单个分区或子分区中使用这些选项。DATA DIRECTORY 子句中指定的目录必须为 InnoDB 所知。更多信息，请参见 使用 DATA DIRECTORY 子句。DATA DIRECTORY and INDEX DIRECTORY options.  Table-level DATA DIRECTORY and INDEX DIRECTORY options are ignored (see Bug #32091). You can employ these options for individual partitions or subpartitions of InnoDB tables. The directory specified in a DATA DIRECTORY clause must be known to InnoDB. For more information, see Using the DATA DIRECTORY Clause.

修复和重建分区表。 语句 CHECK TABLE、OPTIMIZE TABLE、ANALYZE TABLE 和 REPAIR TABLE 都支持用于分区表。Repairing and rebuilding partitioned tables.  The statements CHECK TABLE, OPTIMIZE TABLE, ANALYZE TABLE, and REPAIR TABLE are supported for partitioned tables.

此外，您可以使用 ALTER TABLE ... REBUILD PARTITION 来重建分区表的一个或多个分区；ALTER TABLE ... REORGANIZE PARTITION 也会导致分区被重建。有关这两个语句的更多信息，请参见 第 15.1.9 节，“ALTER TABLE 语句”。In addition, you can use ALTER TABLE ... REBUILD PARTITION to rebuild one or more partitions of a partitioned table; ALTER TABLE ... REORGANIZE PARTITION also causes partitions to be rebuilt. See Section 15.1.9, “ALTER TABLE Statement”, for more information about these two statements.

ANALYZE、CHECK、OPTIMIZE、REPAIR 和 TRUNCATE 操作支持子分区。请参见 第 15.1.9.1 节，“ALTER TABLE 分区操作”。ANALYZE, CHECK, OPTIMIZE, REPAIR, and TRUNCATE operations are supported with subpartitions. See Section 15.1.9.1, “ALTER TABLE Partition Operations”.

分区和子分区的文件名分隔符。 表分区和子分区的文件名包含生成的分隔符，例如 #P# 和 #SP#。这些分隔符的字母大小写可能会有所不同，不应依赖其大小写。File name delimiters for partitions and subpartitions.  Table partition and subpartition file names include generated delimiters such as #P# and #SP#. The lettercase of such delimiters can vary and should not be depended upon.

首页   上一级   下一节
Related Documentation
MySQL 8.4 Release Notes
Download this Manual
PDF (US Ltr) - 40.2Mb
PDF (A4) - 40.3Mb
Man Pages (TGZ) - 261.9Kb
Man Pages (Zip) - 367.4Kb
Info (Gzip) - 4.0Mb
Info (Zip) - 4.0Mb