本节讨论 MySQL 分区支持的当前约束和限制。
禁止构造。 分区表达式中不允许使用以下构造:
存储过程、存储函数、可加载函数或插件。
声明的变量或用户变量。
有关分区表达式中允许的 SQL 函数的列表,请参阅 第 19.6.3 节,“与函数相关的分区限制”。
算术和逻辑运算符。
在分区表达式中允许
使用算术运算符
+
、
-
和
。*
但是,结果必须是整数值或NULL
(
[LINEAR] KEY
分区的情况除外,如本章其他部分所述;有关更多信息,请参见
第 19.2 节,“分区类型”)。
DIV
operator 也是支持的,
operator/
是不允许的。(错误#30188,错误#33182)
分区表达式中不允许
使用位运算符
|
,
&
,
^
,
<<
,
>>
和
。~
处理程序语句。
在 MySQL 5.6 中,
HANDLER
分区表不支持该语句。
服务器 SQL 模式。 使用用户定义分区的表不会保留创建它们时有效的 SQL 模式。如第 5.1.10 节“服务器 SQL 模式”中所述,许多 MySQL 函数和运算符的结果可能会根据服务器 SQL 模式而改变。因此,在创建分区表后随时更改 SQL 模式可能会导致此类表的行为发生重大变化,并且很容易导致数据损坏或丢失。由于这些原因,强烈建议您永远不要在创建分区表后更改服务器 SQL 模式。
例子。 以下示例说明了由于服务器 SQL 模式的更改而导致的分区表行为的一些更改:
错误处理。 假设您创建了一个分区表,其分区表达式为
或column
DIV 0
,如下所示:column
MOD 0mysql> 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
表可访问性。 有时,服务器 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
服务器 SQL 模式也会影响分区表的复制。源和副本上的不同 SQL 模式可能导致分区表达式的计算方式不同;这可能会导致分区之间的数据分布在给定表的源副本和副本副本中不同,甚至可能导致插入分区表在源上成功但在副本上失败。为获得最佳结果,您应该始终在源和副本上使用相同的服务器 SQL 模式。
性能考虑。 以下列表给出了分区操作对性能的一些影响:
文件系统操作。 分区和重新分区操作(例如
ALTER TABLE
withPARTITION BY ...
、REORGANIZE PARTITION
或REMOVE 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
操作时。假设一张
MyISAM
表t
有 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
系统变量没有设置得太低而无法容纳它们。算法和修改表。 分区表不支持
ALTER TABLE
带有ALGORITHM=DEFAULT
、ALGORITHM=INPLACE
或 的语句ALGORITHM=COPY
。请参阅 第 13.1.7.1 节,“ALTER TABLE 分区操作”。表锁。 通常,对表执行分区操作的进程会对该表进行写锁定。从这些表中读取相对不受影响;pending
INSERT
和UPDATE
操作在分区操作完成后立即执行。有关InnoDB
与此限制相关的特定信息,请参阅 分区操作。LOCK
ALTER TABLE
分区表上 的语句不支持 。存储引擎。 分区操作、查询和更新操作通常使用
MyISAM
表比使用InnoDB
或NDB
表更快。索引;分区修剪。 与非分区表一样,正确使用索引可以显着加快分区表的查询速度。此外,设计分区表和对这些表的查询以利用 分区修剪可以显着提高性能。有关详细信息,请参阅 第 19.4 节,“分区修剪”。
分区表不支持索引条件下推。请参阅 第 8.2.1.5 节,“索引条件下推优化”。
负载数据的性能。 在 MySQL 5.6 中,
LOAD DATA
使用缓冲来提高性能。您应该知道缓冲区使用每个分区 130 KB 内存来实现此目的。
最大分区数。
在 MySQL 5.6.7 之前,不使用NDB
存储引擎的给定表的最大可能分区数为 1024。从 MySQL 5.6.7 开始,此限制增加到 8192 个分区。无论 MySQL 服务器版本如何,此最大值都包括子分区。
使用存储引擎的表的用户定义分区的最大可能数量NDB
取决于所使用的 NDB Cluster 软件的版本、数据节点的数量和其他因素。有关详细信息,请参阅
NDB 和用户定义的分区。
如果在创建具有大量分区(但少于最大值)的表时遇到错误消息,例如
Got error ... from storage engine: Out of resources when opening file,您也许可以解决该问题通过增加
open_files_limit
系统变量的值。但是,这取决于操作系统,可能并非在所有平台上都可行或不可取;有关详细信息,请参阅
第 B.3.2.17 节“未找到文件和类似错误”。在某些情况下,出于其他考虑,使用大量(数百个)分区可能也是不可取的,因此使用更多分区不会自动带来更好的结果。
另请参阅 文件系统操作。
不支持查询缓存。 分区表不支持查询缓存。从 MySQL 5.6.5 开始,查询缓存对于涉及分区表的查询自动禁用,并且不能为此类查询启用。(漏洞 #53775)
每个分区键缓存。 在 MySQL 5.6 中,使用and
语句
支持分区MyISAM
表的键缓存。可以为一个、几个或所有分区定义键缓存,并且可以将一个、几个或所有分区的索引预加载到键缓存中。
CACHE INDEX
LOAD INDEX INTO
CACHE
分区 InnoDB 表不支持外键。
使用存储引擎的分区表InnoDB
不支持外键。更具体地说,这意味着以下两个陈述是正确的:
InnoDB
使用用户定义分区 的表的定义不得包含外键引用;不能InnoDB
对定义包含外键引用的表进行分区。任何
InnoDB
表定义都不能包含对用户分区表的外键引用;没有InnoDB
带有用户定义分区的表可能包含外键引用的列。
刚刚列出的限制范围包括所有使用InnoDB
存储引擎的表。
不允许使用会导致表违反这些限制的语句
CREATE
TABLE
。ALTER TABLE
更改表 ... 排序依据。 针对分区表运行
的语句会导致仅在每个分区内对行进行排序。ALTER TABLE ... ORDER BY
column
通过修改主键对 REPLACE 语句的影响。
在某些情况下(请参阅
第 19.6.1 节,“分区键、主键和唯一键”)可能需要修改表的主键。请注意,如果您的应用程序使用REPLACE
语句并且您这样做,则这些语句的结果可能会发生巨大变化。有关更多信息和示例,
请参阅第 13.2.8 节,“REPLACE 语句” 。
全文索引。
分区表不支持FULLTEXT
索引或搜索,即使对于使用
InnoDB
或
MyISAM
存储引擎的分区表也是如此。
空间列。 分区表中不能使用
POINT
或
等空间数据类型的列。GEOMETRY
日志表。
无法对日志表进行分区;对此类表的
ALTER
TABLE ... PARTITION BY ...
语句失败并出现错误。
分区键的数据类型。
分区键必须是整数列或解析为整数的表达式。ENUM
不能使用使用列的表达式
。列或表达式值也可以是NULL
。(参见第 19.2.7 节,“MySQL 分区如何处理 NULL”。)
此限制有两个例外:
当按 分区时
[LINEAR] KEY
,可以使用除TEXT
或 之外的任何有效 MySQL 数据类型的列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) );
子查询。
分区键可能不是子查询,即使该子查询解析为整数值或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 的未来版本中将被完全删除。)
有关按键分区表的一般信息,请参阅 第 19.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)。
不支持延迟选项。
不支持使用INSERT DELAYED
将行插入分区表。尝试这样做失败并出现错误。
数据目录和索引目录选项。
DATA DIRECTORY
并且INDEX
DIRECTORY
在与分区表一起使用时受到以下限制:
修复和重建分区表。 分区表支持
语句CHECK TABLE
、
OPTIMIZE TABLE
、
ANALYZE TABLE
和
。REPAIR TABLE
此外,您可以使用ALTER TABLE ... REBUILD
PARTITION
重建分区表的一个或多个分区;ALTER TABLE ... REORGANIZE
PARTITION
也会导致分区重建。有关这两个语句的更多信息,
请参见
第 13.1.7 节,“ALTER TABLE 语句” 。
分区表不支持 mysqlcheck、 myisamchk和 myisampack 。
FOR EXPORT 选项(FLUSH TABLES)。 MySQL 5.6.16 及更早版本中
的分区表不支持
该
FLUSH
TABLES
语句的选项。(漏洞 #16943907)
FOR EXPORT
InnoDB