本节讨论MySQL分区支持的当前限制和限制。
禁止结构。分区表达式中不允许使用以下结构:
存储过程、存储函数、udf或插件。
声明变量或用户变量。
有关分区表达式中允许使用的SQL函数的列表,请参见第24.6.3节,与函数相关的分区限制.
算术和逻辑运算符。算术运算符的使用+
,-
,*
在分区表达式中允许。但是,结果必须是整数值或零
的情况除外(线性)的关键
分区,如本章其他地方所讨论的;看到第24.2节“分区类型”,以了解更多资料)。
Server SQL模式。使用用户定义分区的表不会保留创建时有效的SQL模式。如本手册其他部分所述(见章节5.1.11,“Server SQL模式”),许多MySQL函数和操作符的结果可能会根据服务器的SQL模式而改变。因此,在创建分区表之后任何时候对SQL模式的更改都可能导致这些表的行为发生重大变化,并且很容易导致数据损坏或丢失。基于这些原因,强烈建议在创建分区表后不要更改服务器SQL模式.
对于在服务器SQL模式中导致分区表不可用的这样一个更改,可以考虑以下内容创建表
语句,该语句只有在NO_UNSIGNED_SUBTRACTION
Mode生效:
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) ->);mysql> SET sql_mode='NO_UNSIGNED_SUBTRACTION';mysql> SELECT @@sql_mode;+-------------------------+ | @@ sql_mode | +-------------------------+ | NO_UNSIGNED_SUBTRACTION | +-------------------------+ 1行组(0.00秒)mysql >创建表涂(c1 BIGINT无符号)- >分区的范围(c1 - 10) (p0 - >分区值小于(5),- - - - - - >分区p1值小于(0)- >分区p2值小于(5),- - - - - - >分区p3值小于(10),- - - - - - >分区p4值小于(执行)- >);查询OK, 0行受影响(0.05秒)
如果你移除NO_UNSIGNED_SUBTRACTION
server SQL模式后创建你
,您可能不再能够访问此表:
mysql> SET sql_mode= ";mysql> SELECT * FROM tu;错误1563 (HY000):分区常量超出分区函数域mysql> INSERT INTO tu VALUES (20);错误1563 (HY000):分区常量超出了分区函数域
Server SQL模式也会影响分区表的复制。源和副本上的不同SQL模式可能导致分区表达式的计算方式不同;这可能导致给定表的源副本和副本的分区之间的数据分布不同,甚至可能导致在源上成功插入分区表的操作在副本上失败。为了获得最佳效果,您应该始终在源和副本上使用相同的服务器SQL模式。
性能考虑。分区操作对性能的影响如下表所示:
文件系统操作。分区和重新分区操作(例如
ALTER TABLE
与分区…
,重组分区
,或删除分区
)依赖于文件系统操作来实现它们。这意味着这些操作的速度受到文件系统类型和特征、磁盘速度、交换空间、操作系统的文件处理效率以及MySQL服务器选项和与文件处理相关的变量等因素的影响。特别是,你应该确保large_files_support
是启用的open_files_limit
设置正确。涉及的分区和重新分区操作InnoDB
启用可以提高表的效率innodb_file_per_table
.另请参阅最大分区数.
表锁。通常,在表上执行分区操作的进程需要对表进行写锁。从这些表读取的数据相对不受影响;等待
插入
而且更新
分区操作完成后立即执行操作。为InnoDB
特定于此限制的例外,请参见分区操作.索引;分区修剪。与非分区表一样,正确使用索引可以显著加快对分区表的查询速度。此外,在这些表上设计分区表和查询分区修剪可以显著提高性能。看到第24.4节,“分区修剪”,以获取更多资料。
分区表支持索引条件下推。看到章节8.2.1.6,“索引条件下推优化”.
负载数据的性能。在MySQL 8.0中,
加载数据
使用缓冲来提高性能。您应该意识到,为了实现这一点,缓冲区每个分区使用130 KB内存。
最大分区数。属性的给定表的最大可能分区数NDB
存储引擎为8192。这个数字包括子分区。
属性用于表的用户定义分区的最大可能数目NDB
存储引擎根据NDB集群软件版本、数据节点数量等因素确定。看到NDB和用户自定义分区,以获取更多资料。
如果在创建具有大量分区(但小于最大分区)的表时,会遇到如下错误消息错误…从存储引擎:当打开文件时资源不足的值,就可以解决这个问题open_files_limit
系统变量。然而,这取决于操作系统,可能不可能或不可取的所有平台;看到B.3.2.16节“未找到文件和类似错误”,以获取更多资料。在某些情况下,出于其他考虑,使用大量(数百个)分区可能也不可取,因此使用更多分区并不会自动带来更好的结果。
另请参阅文件系统操作.
分区InnoDB表不支持外键。分区表使用InnoDB
存储引擎不支持外键。更具体地说,这意味着以下两种说法是正确的:
没有定义
InnoDB
使用用户定义分区的表可能包含外键引用;没有InnoDB
定义中包含外键引用的表可以被分区。没有
InnoDB
表定义可以包含对用户分区表的外键引用;没有InnoDB
用户定义分区的表可能包含外键引用的列。
属性的限制范围包括所有使用InnoDB
存储引擎。创建表
而且ALTER TABLE
不允许出现导致表违反这些限制的语句。
修改表…ORDER BY。一个修改表…命令
针对分区表运行的语句导致仅在每个分区内对行进行排序。列
修改主键对REPLACE语句的影响。在某些情况下,这可能是可取的(参见第24.6.1节“分区键、主键和唯一键”)来修改表的主键。注意,如果您的应用程序使用取代
语句,你这样做,这些语句的结果会被彻底改变。看到第13.2.9节“REPLACE语句”,以获取更多信息和示例。
空间列。具有空间数据类型的列,例如点
或几何
不能在分区表中使用。
日志表。对日志表进行分区是不可能的;一个修改表…分区…
语句在这样的表上失败并报错。
分区键的数据类型。分区键必须是整数列或解析为整数的表达式。表达式使用枚举
不能使用列。列或表达式值也可以是零
;看到第24.2.7节,“MySQL分区如何处理NULL”.
这个限制有两个例外:
当按[进行分区时
线性
]关键
,可以使用任何有效的MySQL数据类型的列文本
或团
作为分区键,因为内部键哈希函数从这些类型生成正确的数据类型。例如,下面两个创建表
语句有效:CREATE TABLE tkc (c1 CHAR) PARTITION BY KEY(c1) PARTITION 4;CREATE TABLE tke (c1 ENUM('red', 'orange', 'yellow', 'green', 'blue', 'indigo', 'violet')) PARTITION BY LINEAR KEY(c1) PARTITIONS 6;
当分区时
范围列
或名单列
,可以使用字符串,日期
,DATETIME
列。例如,下面的每一个创建表
语句有效:CREATE TABLE rc (c1 INT, c2 DATE) PARTITION BY RANGE COLUMNS(c2)(分区p0值小于('1990-01-01'),分区p1值小于('1995-01-01'),分区p2值小于('2000-01-01'),分区p3值小于('2005-01-01'),分区p4值小于(MAXVALUE));创建表lc (INT c1, c2 CHAR(1)分区通过列表列(c2)(分区p0值(' a ', ' d ', ' g ', ' j ', ' m ', ' p ', ' s ', ' v ', ' y '),分区p1值(“b”、“e”、‘h’,‘k’,‘n’,‘问’,‘t’,‘w’,' z '),分区p2值(“c”,‘f’,‘我’,‘l’,‘o’,‘r’,‘你’,‘x’,NULL));
子查询。分区键可能不是子查询,即使该子查询解析为整数值或零
.
键分区不支持列索引前缀。当创建一个按键分区的表时,分区键中任何使用列前缀的列都不会在表的分区函数中使用。考虑以下几点创建表
语句,它有3VARCHAR
列,并且其主键使用所有三个列,并为其中两个列指定前缀:
CREATE TABLE t1 (a VARCHAR(10000), b VARCHAR(25), c VARCHAR(10), PRIMARY KEY(a(10), b, c(2)))
该语句被接受,但实际创建的结果表就像您发出了以下语句一样,只使用不包括前缀(column . column)的主键列b
)作为分区键:
CREATE TABLE t1 (a VARCHAR(10000), b VARCHAR(25), c VARCHAR(10), PRIMARY KEY(a(10), b, c(2))) PARTITION BY KEY(b) PARTITION 2;
在MySQL 8.0.21之前,没有发出任何警告或任何其他指示来提供这种情况,除非在事件中为分区键指定的所有列都使用前缀,在这种情况下,语句失败,但会出现误导性的错误消息,如下所示:
mysql> CREATE TABLE t2 (-> a VARCHAR(10000), -> b VARCHAR(25), -> c VARCHAR(10), -> PRIMARY KEY(a(10), b(5), c(2)) ->) PARTITION BY KEY() PARTITION 2;错误1503 (HY000):主键必须包含表分区函数中的所有列
这在表演时也会发生ALTER TABLE
或者在升级这些表时。
从MySQL 8.0.21开始,这种允许行为已弃用(并且在MySQL的未来版本中可能会被移除)。从MySQL 8.0.21开始,在分区键中使用一个或多个具有前缀的列会对每个这样的列产生警告,如下所示:
mysql> CREATE TABLE t1 (-> a VARCHAR(10000), -> b VARCHAR(25), -> c VARCHAR(10), -> PRIMARY KEY(a(10), b, c(2)) ->) PARTITION BY KEY() PARTITION 2;查询好,0行影响,2(1.25秒)mysql >显示警告警告\ G *************************** 1。行 *************************** 级别:警告代码:1681信息:“test.t1列。有前缀键部分的' A(10)'将被分区函数忽略。在PARTITION BY KEY()子句中使用带前缀的列已弃用,并将在未来的版本中删除。*************************** 2。行***************************级别:警告代码:1681消息:列'test.t1.c'前缀键部分'c(2)'被分区函数忽略。在PARTITION BY KEY()子句中使用带前缀的列已弃用,并将在未来的版本中删除。set中的2行(0.00秒)
这包括分区函数中使用的列通过使用empty隐式定义为表主键中的列的情况按键()分区
条款。
在MySQL 8.0.21及以后版本中,如果为分区键指定的所有列都使用前缀,则创建表
使用的语句失败,并显示正确识别问题的错误消息:
mysql> CREATE TABLE t1 (-> a VARCHAR(10000), -> b VARCHAR(25), -> c VARCHAR(10), -> PRIMARY KEY(a(10), b(5), c(2)) ->) PARTITION BY KEY() PARTITION 2;错误1503 (HY000): PRIMARY KEY必须包含表分区函数中的所有列(不考虑带前缀的列)。
有关按键划分表的一般信息,请参见第24.2.5节,“键分区”.
子分区问题。子分区必须使用哈希
或关键
分区。只有范围
而且列表
分区可以再分区;哈希
而且关键
分区不能再分区。
按键分区
的情况不同,要求显式指定子分区列按键分区
,其中可以省略它(在这种情况下,默认使用表的主键列)。考虑由这条语句创建的表:
创建表ts (id INT NOT NULL AUTO_INCREMENT主键,名称VARCHAR(30));
您可以创建一个具有相同列的表,按关键
,使用这样的语句:
CREATE TABLE ts (id INT NOT NULL AUTO_INCREMENT主键,name VARCHAR(30))分区
前面的语句被当作是这样写的,表的主键列被用作分区列:
CREATE TABLE ts (id INT NOT NULL AUTO_INCREMENT PRIMARY KEY, name VARCHAR(30))
然而,下面的语句试图使用默认列作为子分区列创建一个子分区表,但失败了,必须指定列才能使语句成功,如下所示:
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) ->);错误1064(42000):你有一个错误的SQL语法;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) ->);查询OK, 0行受影响(0.07秒)
这是一个已知的问题(参见Bug #51470)。
数据目录和索引目录选项。表级数据目录
而且索引目录
选项被忽略(参见Bug #32091)。的单独分区或子分区可以使用这些选项InnoDB
表。从MySQL 8.0.21开始,a中指定的目录数据目录
条款必须知道InnoDB
.有关更多信息,请参见使用DATA DIRECTORY子句.
修复和重建分区表。的语句检查表
,优化表
,分析表
,修理表
支持分区表。
另外,可以使用修改表…重建分区
重建分区表的一个或多个分区;修改表…重组分区
还会导致重新构建分区。看到第13.1.9节“ALTER TABLE语句”,以获得有关这两个语句的更多信息。
分析
,检查
,优化
,修复
,截断
子分区支持操作。看到章节13.1.9.1,“ALTER TABLE分区操作”.
分区和子分区的文件名分隔符。表分区和子分区文件名包括生成的分隔符,如# P #
而且# SP #
.这种分隔符的字母大小写可以变化,不应该依赖于它。