参考官方文档:
https://dev.mysql.com/doc/refman/5.7/en/partitioning-limitations.html

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

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

存储过程，存储函数，UDF或插件。
被声明变量或用户变量。

算术和逻辑运算符。在分区表达式中允许使用算术运算符+， - 和*。但是，结果必须是整数值或NULL（[LINEAR] KEY分区的情况除外）。

DIV运算符也被支持，不允许/运算符。（Bug＃30188，Bug＃33182）

分区表达式中不允许使用位运算符|，＆，^，<<，>>和〜。

HANDLER语句。以前，分区表不支持HANDLER语句。从MySQL 5.7.1开始，此限制将被删除。

服务器 SQL 模式

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

服务器SQL模式也会影响分区表的复制。主服务器和从服务器上的不同SQL模式可能导致分区表达式的计算方式不同; 这可能导致分区之间的数据分布在给定表的主服务器和从服务器副本中不同，甚至可能导致插入到主服务器上分区表中成为而在从服务器上失败。为获得最佳结果，应始终在主服务器和从服务器上使用相同的服务器SQL模式。

性能考虑因素

以下列表给出了分区操作对性能的一些影响：

文件系统操作

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

MyISAM和分区文件描述符用法

对于分区的MyISAM表，对于每个打开的表，MySQL为每个分区使用2个文件描述符。这意味着您需要更多的文件描述符来对分区的MyISAM表执行操作，而不是在与其相同的表上执行操作。

表锁

通常，对表执行分区操作的进程会对表执行写锁定。这些表的读相对不受影响; 一旦分区操作完成，就执行挂起的INSERT和UPDATE操作。

存储引擎

对于MyISAM表而言，分区操作，查询和更新操作通常比使用InnoDB或NDB表更快。

索引分区剪裁

与非分区表一样，正确使用索引可以显着加快对分区表的查询速度。此外，在这些表上设计分区表和查询以利用分区修剪可以显着提高性能。

以前，分区表不支持索引条件下推。 MySQL 5.7.3中删除了此限制。

LOAD DATA的性能

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

最大分区数。

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

使用NDB存储引擎的表的最大可能用户定义分区数是根据所使用的NDB Cluster软件的版本，数据节点的数量和其他因素确定的。

查询缓存不被支持

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

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

1.使用用户定义分区的InnoDB表的定义不能包含外键引用; 非InnoDB表，其定义包含外键引用可以被分区。

2.InnoDB 表定义不能包含外键引用到被用户分区的表；InnoDB使用用户定义的分区不能包含关联的外键字段

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

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

通过REPLACE语句修改主键的影响

在某些情况下（参见第22.6.1节“分区键，主键和唯一键”）可能需要修改表的主键。请注意，如果您的应用程序使用REPLACE语句并执行此操作，则这些语句的结果可能会发生巨大变化。

FULLTEXT索引

分区表不支持FULLTEXT索引或搜索，即使对于使用InnoDB或MyISAM存储引擎的分区表也是如此。

空间列
具有空间数据类型（如POINT或GEOMETRY）的列不能在分区表中使用。

临时表
临时表无法分区。（bug ＃17497）

日志表

无法对日志表进行分区; 这样的表上的ALTER TABLE ... PARTITION BY ...语句失败并显示错误。

分区键的数据类型

分区键必须是整数列或解析为整数的表达式。不能使用使用ENUM列的表达式。列或表达式值也可以为NULL。

此限制有两个例外：

1.当通过[LINEAR] KEY进行分区时，可以使用除TEXT或BLOB之外的任何有效MySQL数据类型的列作为分区键，因为MySQL的内部key hash函数可以从这些类型生成正确的数据类型。例如，以下两个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进行分区时，可以使用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)
);

前面的两个例外都不适用于BLOB或TEXT列类型。

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

子分区的问题

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

这是一个已知问题（参见Bug＃51470）。

DATA DIRECTORY和INDEX DIRECTORY选项。

与分区表一起使用时，DATA DIRECTORY和INDEX DIRECTORY受以下限制：

表级DATA DIRECTORY和INDEX DIRECTORY选项被忽略（参见Bug＃32091）。
在Windows上，MyISAM表的各个分区或子分区不支持DATA DIRECTORY和INDEX DIRECTORY选项。但是，您可以将DATA DIRECTORY用于InnoDB表的各个分区或子分区。

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

此外，您可以使用ALTER TABLE ... REBUILD PARTITION来重建分区表的一个或多个分区; ALTER TABLE ... REORGANIZE PARTITION也会导致重建分区。

从MySQL 5.7.2开始，子分区支持ANALYZE，CHECK，OPTIMIZE，REPAIR和TRUNCATE操作。在MySQL 5.7.5之前，REBUILD也被接受了，尽管没有效果。（Bug＃19075411，Bug＃73130）

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

FOR EXPORT选项（FLUSH TABLES）。 MySQL 5.7.4及更早版本中的分区InnoDB表不支持FLUSH TABLES语句的FOR EXPORT选项。（bug ＃16943907）

22.6.1 分区键，主键和唯一键

本节讨论将分区键与主键和唯一键的关系。管理此关系的规则可以表示如下：分区表的分区表达式中使用的所有列必须是表可能具有的每个唯一键的一部分。

换句话说，表上的每个唯一键必须使用表的分区表达式中的每一列。（这也包括表的主键，因为它根据定义是唯一键。这个特殊情况将在本节后面讨论。）例如，以下每个表创建语句都是无效的：

CREATE TABLE t1 (
col1 INT NOT NULL,
col2 DATE NOT NULL,
col3 INT NOT NULL,
col4 INT NOT NULL,
UNIQUE KEY (col1, col2)
) PARTITION BY HASH(col3)
PARTITIONS 4;

CREATE TABLE t2 (
col1 INT NOT NULL,
col2 DATE NOT NULL,
col3 INT NOT NULL,
col4 INT NOT NULL,
UNIQUE KEY (col1),
UNIQUE KEY (col3)
)
PARTITION BY HASH(col1 + col3)
PARTITIONS 4;

在每种情况下，这些表至少有一个唯一键，不包括分区表达式中使用的所有列。

以下每个语句都是有效的，并且表示可以使相应的无效表创建语句起作用的一种方式：

CREATE TABLE t1 (
col1 INT NOT NULL,
col2 DATE NOT NULL,
col3 INT NOT NULL,
col4 INT NOT NULL,
UNIQUE KEY (col1, col2, col3)
) PARTITION BY HASH(col3)
PARTITIONS 4;

CREATE TABLE t2 (
col1 INT NOT NULL,
col2 DATE NOT NULL,
col3 INT NOT NULL,
col4 INT NOT NULL,
UNIQUE KEY (col1, col3)
)
PARTITION BY HASH(col1 + col3)
PARTITIONS 4;

在这种情况下，建议的分区键col3是两个唯一键的一部分，并且表创建语句成功。

无法对下表进行分区，因为无法在分区键中包含属于这两个唯一键的任何列：

CREATE TABLE t4 (
col1 INT NOT NULL,
col2 INT NOT NULL,
col3 INT NOT NULL,
col4 INT NOT NULL,
UNIQUE KEY (col1, col3),
UNIQUE KEY (col2, col4)
);

由于每个主键根据定义是唯一键，因此该限制还包括表的主键（如果有）。例如，接下来的两个语句无效：

CREATE TABLE t5 (
col1 INT NOT NULL,
col2 DATE NOT NULL,
col3 INT NOT NULL,
col4 INT NOT NULL,
PRIMARY KEY(col1, col2) )
PARTITION BY HASH(col3)
PARTITIONS 4;

CREATE TABLE t6 (
col1 INT NOT NULL,
col2 DATE NOT NULL,
col3 INT NOT NULL,
col4 INT NOT NULL,
PRIMARY KEY(col1, col3),
UNIQUE KEY(col2) )
PARTITION BY HASH( YEAR(col2) )
PARTITIONS 4;

在这两种情况下，主键都不包括分区表达式中引用的所有列。但是，接下来的两个语句都是有效的：

CREATE TABLE t7 (
col1 INT NOT NULL,
col2 DATE NOT NULL,
col3 INT NOT NULL,
col4 INT NOT NULL,
PRIMARY KEY(col1, col2) )
PARTITION BY HASH(col1 + YEAR(col2))
PARTITIONS 4;
CREATE TABLE t8 ( col1 INT NOT NULL, col2 DATE NOT NULL, col3 INT NOT NULL, col4 INT NOT NULL,
PRIMARY KEY(col1, col2, col4),
UNIQUE KEY(col2, col1) )
PARTITION BY HASH(col1 + YEAR(col2))
PARTITIONS 4;

如果表没有唯一键 - 这包括没有主键 - 则此限制不适用，并且只要列类型与分区类型兼容，您就可以使用分区表达式中的任何列。

出于同样的原因，除非键包含表的分区表达式使用的所有列，否则以后不能向分区表添加唯一键。考虑如下所示创建的分区表：

mysql> CREATE TABLE t_no_pk (c1 INT, c2 INT)
-> PARTITION BY RANGE(c1) (
-> PARTITION p0 VALUES LESS THAN (10),
-> PARTITION p1 VALUES LESS THAN (20),
-> PARTITION p2 VALUES LESS THAN (30),
-> PARTITION p3 VALUES LESS THAN (40)
-> );
Query OK, 0 rows affected (0.12 sec)

可以使用以下任一ALTER TABLE语句向t_no_pk添加主键：

# possible PK
mysql> ALTER TABLE t_no_pk ADD PRIMARY KEY(c1); Query OK, 0 rows affected (0.13 sec) Records: 0 Duplicates: 0 Warnings: 0 # drop this PK

mysql> ALTER TABLE t_no_pk DROP PRIMARY KEY; Query OK, 0 rows affected (0.10 sec) Records: 0 Duplicates: 0 Warnings: 0

# use another possible PK
mysql> ALTER TABLE t_no_pk ADD PRIMARY KEY(c1, c2); Query OK, 0 rows affected (0.12 sec) Records: 0 Duplicates: 0 Warnings: 0

# drop this PK
mysql> ALTER TABLE t_no_pk DROP PRIMARY KEY; Query OK, 0 rows affected (0.09 sec) Records: 0 Duplicates: 0 Warnings: 0

但是，下一个语句失败，因为c1是分区键的一部分，但不是建议的主键的一部分：

# fails with error 1503
mysql> ALTER TABLE t_no_pk ADD PRIMARY KEY(c2); ERROR 1503 (HY000): A PRIMARY KEY must include all columns in the table's partitioning function

由于t_no_pk在其分区表达式中只有c1，因此单独尝试在c2上添加唯一键失败。但是，您可以添加同时使用c1和c2的键。

这些规则也适用于您希望使用ALTER TABLE ... PARTITION BY进行分区的现有非分区表。考虑如下所示创建的表np_pk：

mysql> CREATE TABLE np_pk (
-> id INT NOT NULL AUTO_INCREMENT,
-> name VARCHAR(50),
-> added DATE,
-> PRIMARY KEY (id)
-> );
Query OK, 0 rows affected (0.08 sec)

以下ALTER TABLE语句失败并显示错误，因为添加的列不是表中任何唯一键的一部分：

mysql> ALTER TABLE np_pk
-> PARTITION BY HASH( TO_DAYS(added) )
-> PARTITIONS 4; ERROR 1503 (HY000): A PRIMARY KEY must include all columns in the table's partitioning function

但是，使用id列作为分区字段的此语句有效，如下所示：

mysql> ALTER TABLE np_pk
-> PARTITION BY HASH(id)
-> PARTITIONS 4;
Query OK, 0 rows affected (0.11 sec) Records: 0 Duplicates: 0 Warnings: 0

在np_pk这个例子中，唯一可以用作分区表达式一部分的列是id; 如果您希望使用分区表达式中的任何其他列对此表进行分区，则必须先修改表，方法是将所需的一列或多列添加到主键，或者完全删除主键。

22.6.2 存储引擎相关的分区限制

MERGE存储引擎。

用户定义的分区和MERGE存储引擎不兼容。无法对使用MERGE存储引擎的表进行分区。分区表无法合并

FEDERATED 存储引擎

不支持对FEDERATED表进行分区; 无法创建分区的FEDERATED表。

CSV 存储引擎

不支持使用CSV存储引擎的分区表; 无法创建分区的CSV表。

InnoDB 存储引擎

InnoDB外键和MySQL分区不兼容。分区的InnoDB表不能具有外键引用，也不能具有外键引用的列。具有由外键引用的InnoDB表不能被分区。

InnoDB不支持将多个磁盘用于子分区。（目前仅MyISAM支持此功能。）

此外，ALTER TABLE ... OPTIMIZE PARTITION对使用InnoDB存储引擎的分区表无法正常工作。对于此类表，请使用ALTER TABLE ... REBUILD PARTITION和ALTER TABLE ... ANALYZE PARTITION。

升级分区表

执行升级时，除NDB之外的任何存储引擎的由KEY分区的表必须重新转储。

所有分区有相同的存储引擎

分区表的所有分区必须使用相同的存储引擎，并且它必须与整个表使用的存储引擎相同。此外，如果没有在表级别上指定引擎，则在创建或更改分区表时必须执行以下任一操作：

不要为任何分区或子分区指定任何引擎
为所有分区或子分区指定引擎

22.6.3 相关函数的分区限制

在分区表达式中只允许使用以下列表中显示的MySQL函数：
ABS()
CEILING()
DATEDIFF()
DAY()
DAYOFMONTH()
DAYOFWEEK()
DAYOFYEAR()
EXTRACT()
FLOOR()
HOUR()
MICROSECOND()
MINUTE()
MOD()
MONTH()
QUARTER()
SECOND()
TIME_TO_SEC()
TO_DAYS()
TO_SECONDS()
UNIX_TIMESTAMP()
WEEKDAY()
YEAR()
YEARWEEK()

在MySQL 5.7中，TO_DAYS（），TO_SECONDS（），YEAR（）和UNIX_TIMESTAMP（）支持分区剪裁

CEILING() 和 FLOOR()
只有在传递精确数字类型的参数（例如INT类型或DECIMAL之一）时，这些函数返回一个整数。这意味着，例如，以下CREATE TABLE语句失败并显示错误，如下所示：

mysql> CREATE TABLE t (c FLOAT) PARTITION BY LIST( FLOOR(c) )(
-> PARTITION p0 VALUES IN (1,3,5),
-> PARTITION p1 VALUES IN (2,4,6)
-> ); ERROR 1490 (HY000): The PARTITION function returns the wrong type

22.6.4 分区和锁

在MySQL 5.7中，分区锁定修剪在许多情况下消除了不需要的锁定，并且从分区MyISAM表读取或更新分区的大多数语句仅导致受影响的分区被锁定。例如，来自分区MyISAM表的SELECT仅锁定那些实际包含满足SELECT语句的WHERE条件的行的分区被锁定。

DML 的影响

SELECT语句（包括那些union或join的语句）仅锁定实际需要读取的分区。这也适用于SELECT ... PARTITION。
UPDATE修剪仅针对更新非分区列的表进行锁定。
REPLACE和INSERT仅锁定那些要插入或替换行的分区。但是，如果为任何分区列生成AUTO_INCREMENT值，则会锁定所有分区。

INSERT ... ON DUPLICATE只要没有更新分区列，就会修剪KEY UPDATE。

INSERT ... SELECT仅锁定源表中需要读取的那些分区，尽管目标表中的所有分区都被锁定。

LOAD DATA语句对分区表施加的锁定无法修剪。

BEFORE INSERT或BEFORE UPDATE触发器使用分区表的任何分区列意味着更新此表的INSERT和UPDATE语句上的锁，无法被剪裁，因为触发器可以更改其值。

DDL语句的影响
CREATE VIEW不会导致任何锁定。
ALTER TABLE ... EXCHANGE PARTITION剪裁锁; 只有交换的表和交换的分区被锁定。
ALTER TABLE ... TRUNCATE PARTITION剪裁锁; 只有要清空的分区被锁定。
此外，ALTER TABLE语句在表级别上获取元数据锁。

其他语句

LOCK TABLES无法修剪分区锁。
CALL stored_procedure（expr）支持锁定修剪，但不评估expr。
DO和SET语句不支持分区锁修剪。

ITPub博客

22.6 分区的限制和条件

最新文章