MySQL :: MySQL 8.4 参考手册 :: 26.6 分区限制和局限性

版本 8.4

9.0
8.0
5.7

8.0 日语

MySQL 8.4 参考手册 / 分区 / 分区限制和局限性

26.6 分区限制和局限性

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

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

禁止的结构。 在分区表达式中不允许使用以下结构

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

有关分区表达式中允许使用的 SQL 函数列表，请参阅第 26.6.3 节，“与函数相关的分区限制”。

算术和逻辑运算符。 在分区表达式中允许使用算术运算符 +、- 和 *。但是，结果必须是整数值或 NULL（[LINEAR] KEY 分区的情况除外，本章其他地方对此进行了讨论；有关详细信息，请参阅第 26.2 节，“分区类型”）。

也支持 DIV 运算符；不允许使用 / 运算符。

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

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

例如，服务器 SQL 模式发生变化导致分区表无法使用，请考虑以下 CREATE TABLE 语句，该语句只有在 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 之后删除 NO_UNSIGNED_SUBTRACTION 服务器 SQL 模式，则您可能无法再访问此表

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

性能注意事项。 分区操作对性能的一些影响如下：

文件系统操作。 分区和重新分区操作（例如，使用 PARTITION BY ...、REORGANIZE PARTITION 或 REMOVE PARTITIONING 的 ALTER TABLE）取决于文件系统操作来实现。这意味着这些操作的速度会受到文件系统类型和特性、磁盘速度、交换空间、操作系统的文件处理效率以及与文件处理相关的 MySQL 服务器选项和变量等因素的影响。特别是，您应该确保 large_files_support 已启用，并且 open_files_limit 设置正确。通过启用 innodb_file_per_table，可以提高涉及 InnoDB 表的分区和重新分区操作的效率。
另请参阅最大分区数。
表锁。 通常，对表执行分区操作的进程会获取该表的写锁。从此类表读取数据相对不受影响；一旦分区操作完成，就会执行挂起的 INSERT 和 UPDATE 操作。有关此限制的 InnoDB 特定例外情况，请参阅分区操作。
索引；分区修剪。 与非分区表一样，正确使用索引可以显著加快对分区表的查询速度。此外，设计分区表和对这些表的查询以利用分区修剪可以极大地提高性能。有关详细信息，请参阅第 26.4 节，“分区修剪”。
分区表支持索引条件下推。请参阅第 10.2.1.6 节，“索引条件下推优化”。
使用 LOAD DATA 时的性能。 在 MySQL 8.4 中，LOAD DATA 使用缓冲来提高性能。您应该注意，缓冲区每个分区使用 130 KB 内存来实现这一点。

最大分区数。 未使用 NDB 存储引擎的给定表的最大可能分区数为 8192。此数字包括子分区。

使用 NDB 存储引擎的表的最大可能用户定义分区数取决于所使用的 NDB 集群软件版本、数据节点数以及其他因素。有关详细信息，请参阅 NDB 和用户定义的分区。

如果在创建具有大量分区（但小于最大值）的表时，遇到从存储引擎获取错误 ...：打开文件时资源不足之类的错误消息，则可以通过增加 open_files_limit 系统变量的值来解决此问题。但是，这取决于操作系统，并且在所有平台上可能都不可能或不可取；有关详细信息，请参阅第 B.3.2.16 节，“找不到文件和类似错误”。在某些情况下，由于其他原因，使用大量（数百个）分区也可能不可取，因此使用更多分区不会自动导致更好的结果。

另请参阅文件系统操作。

分区 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 索引. 分区表不支持 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 列，并且其主键使用所有三个列并为其中一个列指定了前缀。此语句将被拒绝并报错，如下所示

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 分区”。

子分区的问题. 子分区必须使用 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 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)

这是一个已知问题（请参阅错误 #51470）。

DATA DIRECTORY 和 INDEX DIRECTORY 选项. 表级别的 DATA DIRECTORY 和 INDEX DIRECTORY 选项将被忽略（请参阅错误 #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#。此类分隔符的大小写可能有所不同，不应依赖于此。