MySQL :: MySQL 8.4 Reference Manual :: 26.6 Restrictions and Limitations on Partitioning

version 8.4

8.0 current
5.7

8.0 Japanese

MySQL 8.4 Reference Manual / Partitioning / Restrictions and Limitations on Partitioning

26.6 分区限制和限制

26.6.1 分区键、主键和唯一键
26.6.2 与存储引擎相关的分区限制
26.6.3 与函数相关的分区限制

本节讨论MySQL分区支持中的当前限制和限制。

禁止的构造。 以下构造在分区表达式中不被允许：

存储过程、存储函数、可加载函数或插件。
声明变量或用户变量。

要查看哪些SQL函数在分区表达式中被允许，请参阅第26.6.3节，“与函数相关的分区限制”。

算术和逻辑运算符。 在分区表达式中使用加法、减法和乘法运算符是允许的。然而，结果必须是一个整数值或NULL(除了在[线性]键分区中讨论的地方；参见第26.2节，“分区类型”，以获取更多信息)。

除法运算符DIV也被支持，但不允许使用/运算符。

位运算符|、&、^、<<、>>和~在分区表达式中不被允许。

服务器SQL模式。 使用自定义分区的表不会保留在创建时有效的SQL模式。正如本手册其他地方讨论的（参见第7.1.11节，“服务器SQL模式”），许多MySQL函数和运算符的结果可能会根据服务器SQL模式的变化而改变。因此，在创建分区表后更改SQL模式可能导致这些表的行为发生重大变化，并且可能导致数据损坏或丢失。因此，强烈建议您在创建分区表之后永远不要更改服务器SQL模式。

要了解一个在服务器SQL模式中更改使得分区表变得不可用，请考虑以下创建表语句，该语句只能在NO_UNSIGNED_SUBTRACTION模式下执行：

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后移除服务器SQL模式中的NO_UNSIGNED_SUBTRACTION，则可能无法再访问此表：

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模式”。

服务器SQL模式也会影响分区表的复制。源和副本上的不同SQL模式可能导致分区表达式在不同的方式被评估，这可能会导致数据在源和副本之间在给定表中的分布不一致，并且可能会导致在源上成功的插入操作在副本上失败。为了获得最佳结果，您应该始终在源和副本上使用相同的服务器SQL模式。

性能考虑因素。 分区操作对性能的影响如下列表所示：

文件系统操作。 分区和重新分区操作（如 ALTER TABLE with PARTITION BY ...，REORGANIZE PARTITION 或 REMOVE PARTITIONING）依赖于文件系统操作来实现。这意味着这些操作的速度受到诸如文件系统类型和特性、磁盘速度、交换空间、操作系统对文件处理效率以及与文件处理相关的MySQL服务器选项和变量等因素的影响。在特别是，您应该确保 large_files_support 被启用，并且 open_files_limit 被设置得当。分区和重新分区操作涉及 InnoDB 表可能会通过启用 innodb_file_per_table 来变得更加高效。

请参阅最大分区数。
表锁定。 通常，执行分区操作的进程会对表获取写锁。从这样的表读取数据是相对无影响的；等待中的 INSERT 和 UPDATE 操作在分区操作完成后立即进行。对于 InnoDB-特定的例外，请参阅分区操作。
索引；分区裁剪。 与非分区表一样，合理使用索引可以显著加快对分区表的查询。另外，设计分区表和针对这些表的查询以利用分区裁剪可以大幅提高性能。请参阅第26.4节，“分区裁剪”，获取更多信息。

支持对分区表的索引条件推送。请参阅第10.2.1.6节，“索引条件推送优化”。
LOAD DATA 性能。 在MySQL 8.4中，LOAD DATA 使用缓冲区来提高性能。您应该知道，这个缓冲区每个分区使用130KB内存以实现这一点。

最大分区数。 对于不使用 NDB 存储引擎的表，最大可能分区数为8192。这包括子分区。

对于使用 NDB 存储引擎的表，用户定义的最大分区数由NDB集群软件版本、数据节点数量以及其他因素决定。请参阅NDB和用户定义分区，获取更多信息。

如果在创建包含大量分区（但少于最大值）的表时遇到错误消息，如从存储引擎获取错误...：打开文件时无资源，您可能可以通过增加 open_ files_limit 系统变量的值来解决问题。然而，这取决于操作系统，并且在所有平台上都不一定可行或建议；请参阅第 B.3.2.16 节，“文件未找到和类似错误”，获取更多信息。在某些情况下，使用大量（数百个）分区也可能不 advisible 由于其他考虑，因此使用更多分区并不自动导致更好的结果。

请参阅文件系统操作。

外键不支持分区的 InnoDB 表。 使用 InnoDB 存储引擎的分区表不支持外键。更具体地说，这意味着以下两个陈述都是真的：

没有定义为使用用户自定义分区的 InnoDB 表可以包含外键引用；没有定义包含外键引用的 InnoDB 表可以被分区。
没有定义为包含对用户分区表的外键引用的 InnoDB 表定义；没有使用用户定义分区的 InnoDB 表可以包含被外键引用列。

上述限制的范围包括所有使用 InnoDB 存储引擎的表。CREATE TABLE 和 ALTER TABLE 语句，如果它们将导致违反这些限制的表，则不允许。

ALTER TABLE ... ORDER BY. 对一个分区表执行 ALTER TABLE ... ORDER BY <column> 语句仅在每个分区内对行进行排序。

ADD COLUMN ... ALGORITHM=INSTANT. 一旦对一个分区表执行了 ALTER TABLE ... ADD COLUMN ... ALGORITHM=INSTANT，就无法再使用该表进行交换分区操作。

REPLACE 语句的修改主键的影响. 在某些情况下（请参阅第 26.6.1 节，“分区键、主键和唯一键”），修改表的主键可能是有用的。请注意，如果您的应用程序使用 REPLACE 语句，并且您这样做了，这些语句的结果可能会大不相同。请参阅第 15.2.12 节，“REPLACE 语句”，获取更多信息和示例。

全文索引. 分区表不支持 FULLTEXT 索引或搜索。

空间列. 包含空间数据类型（如 POINT 或 GEOMETRY）的列不能用于分区表中。

临时表. 临时表不能被分区。

日志表. 对于日志表，执行 ALTER TABLE ... PARTITION BY ... 语句会失败并返回错误。

分区键的数据类型. 分区键必须是整数列或解析为整数的表达式。使用 ENUM 列的表达式不能用于分区键。此外，列或表达式值也可以是 NULL；请参阅第 26.2.7 节，“MySQL 分区如何处理 NULL”。

有两个例外情况：

当使用 [LINEAR] KEY 进行分区，它允许使用除 TEXT 和 BLOB 类型之外的任何有效 MySQL 数据类型的列作为分区键，因为内部的键哈希函数能够正确处理这些类型。例如，以下两个 [CREATE TABLE] 语句是有效的：
```
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;
```

当使用 RANGE COLUMNS 或 LIST COLUMNS 进行分区时，可以使用字符串、DATE 和 DATETIME 类型的列。例如，每个以下 [CREATE TABLE] 语句都是有效的：

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 类型的列。

子查询。 分区键不能是一个子查询，即使该子查询解析为整数值或 NULL。

索引前缀不支持用于键分区。 当创建一个使用键进行分区的表时，任何在分区键中使用的列的索引前缀都不会被用于表的分区函数。考虑以下 [CREATE TABLE] 语句，该语句有三个 VARCHAR 列，其中主键使用所有三列，并为两个列指定了前缀：

CREATE TABLE t1 (
    a VARCHAR(10000),
    b VARCHAR(25),
    c VARCHAR(10),
    PRIMARY KEY (a(10), b, c(2))
) PARTITION BY KEY() PARTITIONS 2;

这个语句是有效的，但生成的表实际上被创建得就像你发出了以下语句一样，仅使用不包含任何前缀的主键列（列 b）作为分区键：

CREATE TABLE t1 (
    a VARCHAR(10000),
    b VARCHAR(25),
    c VARCHAR(10),
    PRIMARY KEY (a(10), b, c(2))
) PARTITION BY KEY(b) PARTITIONS 2;

这种宽松的行为是已弃用的（并且在 MySQL 的未来版本中可能会被移除）。在分区键中使用一个或多个列的前缀将导致对每个这样的列的警告，正如这里所示：

mysql> CREATE TABLE t1 (
    ->     a VARCHAR(10000),
    ->     b VARCHAR(25),
    ->     c VARCHAR(10),
    ->     PRIMARY KEY (a(10), b, c(2))
    -> ) PARTITION BY KEY() PARTITIONS 2;
Query OK, 0 rows affected, 2 warnings (1.25 sec)

mysql> SHOW WARNINGS\G
*************************** 1. row ***************************
  Level: Warning
   Code: 1681
Message: Column 'test.t1.a' having prefix key part 'a(10)' is ignored by the
partitioning function. Use of prefixed columns in the PARTITION BY KEY() clause
is deprecated and will be removed in a future release.
*************************** 2. row ***************************
  Level: Warning
   Code: 1681
Message: Column 'test.t1.c' having prefix key part 'c(2)' is ignored by the
partitioning function. Use of prefixed columns in the PARTITION BY KEY() clause
is deprecated and will be removed in a future release.
2 rows in set (0.00 sec)

这包括那些在表的主键中定义为分区函数中的列的情况，即通过使用一个空的 PARTITION BY KEY() 子句来隐式指定这些列。

如果所有用于分区键的列都使用了前缀，用于创建表的 [CREATE TABLE] 语句将失败，并且会出现一个错误消息，该消息正确地指出了问题：

mysql> CREATE TABLE t1 (
    ->     a VARCHAR(10000),
    ->     b VARCHAR(25),
    ->     c VARCHAR(10),
    ->     PRIMARY KEY (a(10), b(5), c(2))
    -> ) PARTITION BY KEY() PARTITIONS 2;
ERROR 1503 (HY000): A PRIMARY KEY must include all columns in the table's
partitioning function (prefixed columns are not considered).

有关如何通过键进行表分区的通用信息，请参阅 Section 26.2.5, “KEY Partitioning”。

子分区的限制。 子分区必须使用 HASH 或 KEY 分区。只有 RANGE 和 LIST 分区可以被子分区；HASH 和 KEY 分区不能被子分区。

SUBPARTITION BY KEY 需要明确指定子分区列或列，和 PARTITION BY KEY 不同，它可以省略（在这种情况下，默认情况下使用表的主键列）。考虑以下语句创建的表：

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

你可以创建一个具有相同列的表，该表分区为 KEY，使用类似于以下语句的一个 [CREATE TABLE] 语句：

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

前面的语句被视为如果它是这样写的，就像这样使用了表的主键列作为分区键一样：

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

然而，尝试创建一个子分区表并使用默认列作为子分区列的以下语句失败，并且必须指定列才能成功，如下所示：

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）。

数据目录和索引目录选项。 表级别的 DATA DIRECTORY 和 INDEX DIRECTORY 选项会被忽略（参见BUG #32091）。您可以在单个分区或子分区上使用这些选项，特别是对于InnoDB 表。指定在 DATA DIRECTORY 子句中的目录必须已知给 InnoDB。有关更多信息，请参阅使用 DATA DIRECTORY 子句。

修复和重建分区表。 语句 CHECK TABLE、OPTIMIZE TABLE、ANALYZE TABLE 和 REPAIR TABLE 支持分区表。

此外，您可以使用 ALTER TABLE ... REBUILD PARTITION 来重建一个或多个分区的分区表；ALTER TABLE ... REORGANIZE PARTITION 也会导致分区被重建。有关这两个语句的更多信息，请参阅第15.1.9节，“ALTER TABLE 语句”。

ANALYZE、CHECK、OPTIMIZE、REPAIR 和 TRUNCATE 操作支持子分区。有关这些操作的更多信息，请参阅第15.1.9.1节，“ALTER TABLE 分区操作”。

分区和子分区文件名的分隔符。 表分区和子分区的文件名包括生成的分隔符，如 #P# 和 #SP#。这些分隔符的字母大小写可以不同，并且不应该依赖于它们。

PREV HOME UP NEXT