22.6 分区的限制和限制

本节讨论了对 MySQL 分区支持的当前限制。

禁止的构造. 分区构造中不允许以下构造：

  • 存储过程，存储函数，UDF 或插件。

  • 声明的变量或用户变量。

有关分区 table 达式中允许的 SQL 函数的列 table，请参见第 22.6.3 节“与功能有关的分区限制”。

算术和逻辑运算符. 在分区 table 达式中允许使用算术运算符+，-和*。但是，结果必须是整数值或NULL(除非[LINEAR] KEY分区，如本章其他地方所述；有关更多信息，请参见第 22.2 节“分区类型”)。

还支持DIV运算符，并且不允许/运算符。 (缺陷号 30188，缺陷号 33182)

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

HANDLER 语句. 以前，分区 table 不支持HANDLER语句。从 MySQL 5.7.1 开始删除了此限制。

服务器 SQL 模式. 采用用户定义分区的 table 在创建时不会保留有效的 SQL 模式。如第 5.1.10 节“服务器 SQL 模式”中所述，许多 MySQL 函数和运算符的结果可能会根据服务器 SQL 模式而改变。因此，在创建分区 table 之后随时更改 SQL 模式可能会导致此类 table 的行为发生重大变化，并且很容易导致数据损坏或丢失。由于这些原因，强烈建议您不要在创建分区 table 之后更改服务器 SQL 模式。

示例. 以下示例说明了由于服务器 SQL 模式的更改而导致的分区 table 行为的某些更改：

  • 错误处理. 假设您创建了一个分区 table，其分区 table 达式为诸如column DIV 0或column MOD 0之类的 table 达式，如下所示：
mysql> CREATE TABLE tn (c1 INT)
    ->     PARTITION BY LIST(1 DIV c1) (
    ->       PARTITION p0 VALUES IN (NULL),
    ->       PARTITION p1 VALUES IN (1)
    -> );
Query OK, 0 rows affected (0.05 sec)

MySQL 的默认行为是对除以零的结果返回NULL，而不会产生任何错误：

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

mysql> INSERT INTO tn VALUES (NULL), (0), (1);
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

但是，更改服务器 SQL 模式以将零除视为错误并执行严格的错误处理会导致相同的INSERT语句失败，如下所示：

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

mysql> INSERT INTO tn VALUES (NULL), (0), (1);
ERROR 1365 (22012): Division by 0
  • table 可访问性. 有时，服务器 SQL 模式的更改会使分区 table 无法使用。仅当NO_UNSIGNED_SUBTRACTION模式有效时，以下CREATE TABLE语句才能成功执行：
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 模式，则可能不再能够访问此 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

另请参见第 5.1.10 节“服务器 SQL 模式”。

服务器 SQL 模式还会影响分区 table 的复制。主服务器和从服务器上不同的 SQL 模式可能导致对分区 table 达式的求值方式有所不同。这可能会导致分区之间的数据分配在给定 table 的主副本和从属副本中有所不同，甚至可能导致对成功在主副本上的分区 table 的插入在从属副本上失败。为了获得最佳结果，您应该始终在主服务器和从服务器上使用相同的服务器 SQL 模式。

性能注意事项. 下 table 列出了分区操作对性能的一些影响：

  • 文件系统操作. 分区和重新分区操作(例如ALTER TABLE的PARTITION BY ...，REORGANIZE PARTITION或REMOVE PARTITIONING)取决于文件系统操作的实现。这意味着这些操作的速度受以下因素的影响：文件系统类型和 Feature，磁盘速度，交换空间，os 的文件处理效率以及与文件处理有关的 MySQL 服务器选项和变量。特别是，您应确保已启用large_files_support并且已正确设置open_files_limit。对于使用MyISAM存储引擎的分区 table，增加myisam_max_sort_file_size可能会改善性能；通过启用innodb_file_per_table可以使涉及InnoDBtable 的分区和重新分区操作更加有效。

另请参见最大分区数。

  • MyISAM 和分区文件 Descriptors 的用法. 对于已分区的MyISAMtable，MySQL 对于每个打开的此类 table，每个分区使用 2 个文件 Descriptors。这意味着，要对已分区的MyISAMtable 执行操作要比对与其相同的 table 执行更多的文件 Descriptors，除了后者 table 未分区外，尤其是在执行ALTER TABLE操作时。

假定具有 100 个分区的MyISAMtablet，例如此 SQL 语句创建的 table：

CREATE TABLE t (c1 VARCHAR(50))
PARTITION BY KEY (c1) PARTITIONS 100
ENGINE=MYISAM;

Note

为简便起见，我们在此示例中显示的 table 使用KEY分区，但是此处描述的文件 Descriptors 用法适用于所有已分区的MyISAMtable，而与采用的分区类型无关。使用其他存储引擎(例如InnoDB)的分区 table 不受此问题的影响。

现在，假设您希望使用以下所示的语句对t重新分区，使其具有 101 个分区：

ALTER TABLE t PARTITION BY KEY (c1) PARTITIONS 101;

为了处理此ALTER TABLE语句，MySQL 使用 402 个文件 Descriptors-也就是说，对于 100 个原始分区中的每个分区，两个 Descriptors，对于 101 个新分区中的每个分区，两个 Descriptors。这是因为在重组 table 数据期间必须同时打开所有分区(旧分区和新分区)。建议如果希望执行此类操作，则应确保open_files_limit系统变量的设置值不要太小而不能容纳它们。

  • table 锁. 通常，对 table 执行分区操作的进程对 table 进行写锁定。从此类 table 读取的数据相对不受影响；分区操作完成后，将立即执行暂挂的INSERT和UPDATE操作。有关此限制的特定于InnoDB的 exception，请参见Partitioning Operations。

  • 存储引擎. 使用MyISAMtable 的分区操作，查询和更新操作通常比使用InnoDB或NDBtable 的速度更快。

  • 索引； 与非分区 table 一样，正确使用索引可以显着加快对分区 table 的查询。此外，设计分区 table 和对这些 table 的查询以利用分区修剪可以大大提高性能。有关更多信息，请参见第 22.4 节“分区修剪”。

以前，分区 table 不支持索引条件下推。此限制已在 MySQL 5.7.3 中删除。参见第 8.2.1.5 节“索引条件下推优化”。

  • 具有 LOAD DATA 的性能. 在 MySQL 5.7 中，LOAD DATA使用缓冲来提高性能。您应该注意，缓冲区为每个分区使用 130 KB 内存来实现此目的。

最大分区数. 不使用NDB存储引擎的给定 table 的最大分区数为 8192.此数目包括子分区。

使用NDB存储引擎的 table 的用户定义分区的最大可能数目取决于所使用的 NDB Cluster 软件的版本，数据节点的数目以及其他因素。有关更多信息，请参见NDB 和用户定义的分区。

如果在创建具有大量分区(但小于最大值)的 table 时，遇到错误消息(例如，来自存储引擎的错误...)：打开文件时资源不足，则可以解决该问题通过增加open_files_limit系统变量的值。但是，这取决于 os，并非在所有平台上都是可行的或不可取的。有关更多信息，请参见B.4.2.17 节，“找不到文件和类似错误”。在某些情况下，由于其他原因，也建议不要使用大量(数百个)分区，因此使用更多分区不会自动导致更好的结果。

另请参见文件系统操作。

不支持查询缓存. 分区 table 不支持查询缓存，对于涉及分区 table 的查询会自动禁用。无法为此类查询启用查询缓存。

每分区键缓存. 在 MySQL 5.7 中，分区MyISAMtable 使用CACHE INDEX和将索引加载到缓存中语句支持键缓存。可以为一个，几个或所有分区定义密钥缓存，并且可以将一个，几个或所有分区的索引预加载到密钥缓存中。

分区的 InnoDBtable 不支持外键. 使用InnoDB存储引擎的分区 table 不支持外键。更具体地说，这意味着以下两个 Statements 是正确的：

  • 使用用户定义的分区的InnoDBtable 的定义不得包含外键引用；不能对定义包含外键引用的InnoDBtable 进行分区。

  • InnoDBtable 定义不得包含对用户分区 table 的外键引用；具有用户定义分区的InnoDBtable 不能包含外键引用的列。

刚刚列出的限制范围包括所有使用InnoDB存储引擎的 table。不允许CREATE TABLE和ALTER TABLE语句导致 table 违反这些限制。

ALTER TABLE ... ORDER BY. 对分区 table 运行的ALTER TABLE ... ORDER BY column语句仅导致每个分区内的行排序。

通过修改主键对 REPLACE 语句的影响. 在某些情况下(请参见第 22.6.1 节“分区键，主键和唯一键”)，希望修改 table 的主键。请注意，如果您的应用程序使用REPLACE语句并且执行了此操作，则这些语句的结果可能会发生巨大变化。有关更多信息和示例，请参见第 13.2.8 节“ REPLACE 语句”。

FULLTEXT 索引. 分区 table 不支持FULLTEXT索引或搜索，甚至不支持使用InnoDB或MyISAM存储引擎的分区 table。

空间列. 具有空间数据类型(例如POINT或GEOMETRY)的列不能在分区 table 中使用。

临时 table. 临时 table 无法分区。错误 17497)

日志 table. 无法对日志 table 进行分区；此类 table 上的ALTER TABLE ...分区依据...语句失败，并显示错误。

分区键的数据类型. 分区键必须是整数列或可解析为整数的 table 达式。不能使用采用ENUM列的 table 达式。列或 table 达式值也可以是NULL。 (请参见第 22.2.7 节“ MySQL 分区如何处理 NULL”。)

此限制有两个 exception：

  • 当通过[+207+] KEY进行分区时，可以使用除TEXT或BLOB以外的任何有效 MySQL 数据类型的列作为分区键，因为 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进行分区时，可以使用 string，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)
);

上述任何一个 exception 都不适用于BLOB或TEXT列类型。

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

键分区不支持列索引前缀. 创建按键分区的 table 时，分区键中任何使用列前缀的列都不会在 table 的分区功能中使用。考虑下面的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;

该语句被接受，但是实际上创建的结果 table 就像您已发出以下语句一样，仅使用不包含分区键前缀(第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;

除非已为分区键指定的所有列都使用前缀，否则不会发出警告或提供任何其他 table 明已发生警告的指示，在这种情况下，语句将失败，并显示以下错误消息：

mysql> CREATE TABLE t2 (
    ->     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

这在更改或升级此类 table 时也会发生，并且包括以下情况：通过使用空的PARTITION BY KEY()子句，将分区功能中使用的列隐式定义为 table 的主键中的列。

这是一个已知的问题，在 MySQL 8.0 中，通过弃用许可行为来解决。在 MYSQL 8.0 中，如果 table 的分区功能中包含任何使用前缀的列，则服务器会为每个此类列记录适当的警告，或者在必要时引发描述性错误。 (允许在分区键中使用带前缀的列在将来的 MySQL 版本中将完全删除.)

有关按键对 table 进行分区的一般信息，请参见第 22.2.5 节“密钥分区”。

带有子分区的问题. 子分区必须使用HASH或KEY分区。只能对RANGE和LIST分区进行子分区；不能对HASH和KEY分区进行子分区。

SUBPARTITION BY KEY要求显式指定一个或多个子分区列，与PARTITION BY KEY的情况不同，可以将其省略(在这种情况下，默认使用 table 的主键列)。考虑以下语句创建的 table：

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

您可以使用诸如此类的语句创建具有相同列并被KEY分区的 table：

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

前面的语句就好像是这样写的，table 的主键列用作分区列：

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

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

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 选项. DATA DIRECTORY和INDEX DIRECTORY与分区 table 一起使用时受到以下限制：

  • table 级DATA DIRECTORY和INDEX DIRECTORY选项将被忽略(请参见 Bug#32091)。

  • 在 Windows 上，MyISAMtable 的单个分区或子分区不支持DATA DIRECTORY和INDEX DIRECTORY选项。但是，可以将DATA DIRECTORY用于InnoDBtable 的单个分区或子分区。

修复和重建分区 table. 分区 table 支持语句CHECK TABLE，OPTIMIZE TABLE，ANALYZE TABLE和REPAIR TABLE。

此外，您可以使用ALTER TABLE ... REBUILD PARTITION重建分区 table 的一个或多个分区； ALTER TABLE ... REORGANIZE PARTITION还将导致重建分区。有关这两个语句的更多信息，请参见第 13.1.8 节“ ALTER TABLE 语句”。

从 MySQL 5.7.2 开始，子分区支持ANALYZE，CHECK，OPTIMIZE，REPAIR和TRUNCATE操作。 REBUILD在 MySQL 5.7.5 之前也被接受，但没有效果。 (缺陷号 19075411，缺陷号#73130)另请参见第 13.1.8.1 节，“ ALTER TABLE 分区操作”。

分区 table 不支持mysqlcheck，myisamchk和myisampack。

FOR EXPORT 选项(FLUSH TABLES). MySQL 5.7.4 及更低版本中的InnoDB分区 table 不支持FLUSH TABLES语句的FOR EXPORT选项。 (缺陷#16943907)

分区和子分区的文件名定界符. table 分区和子分区的文件名包含生成的定界符，例如#P#和#SP#。此类分隔符的字母大小写可以变化，因此不应依赖。

Name MySql 中文文档
Version
Language
Badge
Last Updated 2021-07-03T12:24:35