MySQL 8.0 参考手册  / 第24章分区  /  22.6 分区的约束和限制

22.6 分区的约束和限制

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

禁止构造。  分区表达式中不允许使用以下构造:

  • 存储过程、存储函数、可加载函数或插件。

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

有关分区表达式中允许的 SQL 函数的列表,请参阅 第 22.6.3 节,“与函数相关的分区限制”

算术和逻辑运算符。  在分区表达式中允许 使用算术运算符 +-和 。*但是,结果必须是整数值或NULL[LINEAR] KEY分区的情况除外,如本章其他部分所述;有关更多信息,请参见 第 22.2 节,“分区类型”)。

DIVoperator 也是支持的, operator/是不允许的。(错误#30188,错误#33182)

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

处理程序语句。  以前,HANDLER分区表不支持该语句。这个限制从 MySQL 5.7.1 开始被移除。

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

例子。  以下示例说明了由于服务器 SQL 模式的更改而导致的分区表行为的一些更改:

  1. 错误处理。  假设您创建了一个分区表,其分区表达式为 column DIV 0column MOD 0,如下所示:

    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
  2. 表可访问性。  有时,服务器 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)

    如果 NO_UNSIGNED_SUBTRACTION 在创建后删除服务器 SQL 模式tu,您可能无法再访问此表:

    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 模式也会影响分区表的复制。源和副本上不同的 SQL 模式可能导致分区表达式的计算方式不同;这可能会导致分区之间的数据分布在给定表的源副本和副本副本中不同,甚至可能导致插入分区表在源上成功但在副本上失败。为获得最佳结果,您应该始终在源和副本上使用相同的服务器 SQL 模式。

性能考虑。  以下列表给出了分区操作对性能的一些影响:

  • 文件系统操作。  分区和重新分区操作(例如 ALTER TABLEwith PARTITION BY ...REORGANIZE PARTITIONREMOVE PARTITIONING)的实现取决于文件系统操作。这意味着这些操作的速度受文件系统类型和特性、​​磁盘速度、交换空间、操作系统的文件处理效率以及与文件处理相关的 MySQL 服务器选项和变量等因素的影响。特别是,您应该确保它 large_files_support已启用并且 open_files_limit设置正确。MyISAM对于使用存储引擎 的分区表 ,增加myisam_max_sort_file_size 可能会提高性能;涉及InnoDB表的分区和重新分区操作可以通过启用来提高效率 innodb_file_per_table

    另请参阅 最大分区数

  • MyISAM 和分区文件描述符的使用。  对于分区MyISAM表,MySQL 为每个分区使用 2 个文件描述符,用于每个打开的此类表。这意味着您需要更多的文件描述符来在分区 MyISAM表上执行操作,而不是在与其相同的表上执行操作,除了后一个表未分区,特别是在执行 ALTER TABLE操作时。

    假设一张MyISAMt 有 100 个分区,比如这条 SQL 语句创建的表:

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

    为简洁起见,我们KEY对该示例中显示的表使用分区,但此处描述的文件描述符用法适用于所有分区 MyISAM表,而不管所采用的分区类型如何。使用其他存储引擎的分区表 InnoDB不受此问题的影响。

    现在假设您希望t 使用此处显示的语句重新分区以使其具有 101 个分区:

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

    为了处理这个ALTER TABLE语句,MySQL 使用了 402 个文件描述符——也就是说,100 个原始分区中的每个分区有两个,另外 101 个新分区中的每个分区都有两个。这是因为在重组表数据期间必须同时打开所有分区(旧的和新的)。建议,如果您希望执行此类操作,则应确保 open_files_limit系统变量没有设置得太低而无法容纳它们。

  • 表锁。  通常,对表执行分区操作的进程会对该表进行写锁定。从这些表中读取相对不受影响;pending INSERTUPDATE操作在分区操作完成后立即执行。有关InnoDB此限制的特定例外情况,请参阅 分区操作

  • 存储引擎。  分区操作、查询和更新操作通常使用MyISAM 表比使用InnoDBNDB表更快。

  • 索引;分区修剪。  与非分区表一样,正确使用索引可以显着加快分区表的查询速度。此外,设计分区表和对这些表的查询以利用 分区修剪可以显着提高性能。有关详细信息,请参阅 第 22.4 节,“分区修剪”

    以前,分区表不支持索引条件下推。MySQL 5.7.3 中删除了此限制。请参阅 第 8.2.1.5 节,“索引条件下推优化”

  • 负载数据的性能。  在 MySQL 5.7 中,LOAD DATA使用缓冲来提高性能。您应该知道缓冲区使用每个分区 130 KB 内存来实现此目的。

最大分区数。  不使用存储引擎的给定表的最大可能分区数NDB是 8192。这个数字包括子分区。

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

如果在创建具有大量分区(但少于最大值)的表时遇到错误消息,例如 Got error ... from storage engine: Out of resources when opening file,您也许可以解决该问题通过增加 open_files_limit系统变量的值。但是,这取决于操作系统,可能并非在所有平台上都可行或不可取;有关详细信息,请参阅 第 B.3.2.16 节“未找到文件和类似错误”。在某些情况下,出于其他考虑,使用大量(数百个)分区可能也是不可取的,因此使用更多分区不会自动带来更好的结果。

另请参阅 文件系统操作

不支持查询缓存。  分区表不支持查询缓存,并且对于涉及分区表的查询会自动禁用。无法为此类查询启用查询缓存。

每个分区键缓存。 在 MySQL 5.7 中,使用and 语句 支持分区MyISAM表的键缓存。可以为一个、几个或所有分区定义键缓存,并且可以将一个、几个或所有分区的索引预加载到键缓存中。 CACHE INDEXLOAD INDEX INTO CACHE

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

  1. InnoDB使用用户定义分区 的表的定义不得包含外键引用;不能InnoDB对定义包含外键引用的表进行分区。

  2. 任何InnoDB表定义都不能包含对用户分区表的外键引用;没有 InnoDB带有用户定义分区的表可能包含外键引用的列。

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

更改表 ... 排序依据。 针对分区表运行 的语句会导致仅在每个分区内对行进行排序。ALTER TABLE ... ORDER BY column

通过修改主键对 REPLACE 语句的影响。  在某些情况下(请参阅 第 22.6.1 节,“分区键、主键和唯一键”)可能需要修改表的主键。请注意,如果您的应用程序使用REPLACE 语句并且您这样做,则这些语句的结果可能会发生巨大变化。有关更多信息和示例, 请参阅第 13.2.8 节,“REPLACE 语句” 。

全文索引。  分区表不支持FULLTEXT 索引或搜索,即使对于使用 InnoDBMyISAM存储引擎的分区表也是如此。

空间列。 分区表中不能使用 POINT 或 等空间数据类型的列。GEOMETRY

临时表。  临时表不能分区。(漏洞 #17497)

日志表。  无法对日志表进行分区;对此类表的 ALTER TABLE ... PARTITION BY ...语句失败并出现错误。

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

此限制有两个例外:

  1. 当按 [ LINEAR] 分区时KEY,可以使用除 TEXTBLOB作为分区键之外的任何有效 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;
  2. 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)
    );

上述异常均不适用于 BLOBTEXT列类型。

子查询。  分区键可能不是子查询,即使该子查询解析为整数值或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> 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

当更改或升级此类表时也会发生这种情况,包括分区函数中使用的列通过使用空PARTITION BY KEY()子句隐式定义为表主键中的列的情况。

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

有关按键分区表的一般信息,请参阅 第 22.2.5 节,“KEY 分区”

子分区的问题。  子分区必须使用HASHKEY分区。只有 RANGELIST分区可以被子分区;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 DIRECTORYINDEX DIRECTORY选项被忽略(参见错误 #32091)。

  • 在 Windows 上,单个分区或表的子分区不支持 DATA DIRECTORY和 选项。但是,您可以将其用于单个分区或 表的子分区。 INDEX DIRECTORYMyISAMDATA DIRECTORYInnoDB

修复和重建分区表。 分区表支持 语句CHECK TABLEOPTIMIZE TABLEANALYZE TABLE和 。REPAIR TABLE

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

从 MySQL 5.7.2 开始,子分区支持ANALYZECHECKOPTIMIZEREPAIRTRUNCATE 操作。 REBUILD在 MySQL 5.7.5 之前也被接受语法,尽管这没有效果。(缺陷 #19075411,缺陷 #73130)另请参见第 13.1.8.1 节,“ALTER TABLE 分区操作”

分区表不支持 mysqlcheck myisamchkmyisampack 。

FOR EXPORT 选项(FLUSH TABLES)。 MySQL 5.7.4 及更早版本中 的分区表不支持 该 FLUSH TABLES语句的选项。(漏洞 #16943907) FOR EXPORTInnoDB

分区和子分区的文件名分隔符。  表分区和子分区文件名包括生成的分隔符,例如#P##SP#。此类分隔符的字母大小写可能会有所不同,不应依赖于此。