分区的限制和约束条件
这里将讨论MySQL分区支持的当前限制和约束条件。
禁止结构。分区表达式中不允许使用以下结构：
.存储过程、存储函数、自定义函数或插件。
.声明的变量或用户变量
算术和逻辑运算符。分区表达式中允许使用算术运算符+、-和*。但是，结果必须是整数值或NULL （线性键分区除外）
还支持DIV操作符，不允许使用/操作符。（Bug #30188, Bug #33182）
位操作符|、&、^、< <、>>和~不允许出现在分区表达式中。
HANDLER语句。以前，分区表不支持HANDLER语句。这个限制从MySQL 5.7.1开始被移除。
服务器SQL模式。使用用户定义分区的表在创建时不会保留有效的SQL模式。正如5.1.8节所讨论的，许多MySQL函数和操作符的结果可能会根据服 务器SQL模式而改变。因此，在创建分区表之后的任何时候更改SQL模式都可能导致这些表的行为发生重大变化，并且很容易导致数据损坏或丢失 。出于这些原因，强烈建议在创建分区表后不要更改服务器 SQL模式。
例如:下面的示例说明了由于服务器SQL模式的更改而导致的分区表行为的一些变化：
1.错误处理。假设您创建了一个分区表，其分区表达式为列DIV 0或列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.03 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, 1 warning (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.01 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.02 sec)
如果在创建表后删除NO_UNSIGNED_SUBTRACTION服务器SQL模式，则可能无法再访问该表：
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
with PARTITION BY …, REORGANIZE PARTITION, or 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 表执行操作时，所需的文件描述符数量要比与之相同的未分区表多得多，尤其是在执行 ALTER TABLE 操作时 。
假设MyISAM表t有100个分区，例如下面的SQL语句创建的表：
mysql> CREATE TABLE t (c1 VARCHAR(50))
-> ENGINE=MyISAM
-> PARTITION BY KEY (c1) PARTITIONS 100;
Query OK, 0 rows affected, 1 warning (0.02 sec)
为简洁起见，我们对这个例子中显示的使用键分区表，但这里描述的文件描述符的使用适用于所有分区的MyISAM表，无论采用哪种分区类型。使 用其他存储引擎（如InnoDB）的分区表不受此影响。
现在假设您希望重新分区t，使它有101个分区，使用下面的语句：
mysql> ALTER TABLE t PARTITION BY KEY (c1) PARTITIONS 101; Query OK, 0 rows affected, 1 warning (0.05 sec) Records: 0 Duplicates: 0 Warnings: 1
为了处理ALTER TABLE语句，MySQL使用了402个文件描述符，即100个原始分区各2个，101个新分区各2个。这是因为在重组表数据期间，必须同 时打开所有分区（旧分区和新分区）。建议，如果您希望执行这样的操作，您应该确保——open-files-limit不要设置得太低，以容纳它们。
.表锁。通常，在表上执行分区操作的进程需要对表使用写锁。从这样的表中读取相对不受影响；挂起的INSERT和UPDATE操作在分区操作完成后 立即执行。
.存储引擎。分区操作、查询和更新操作通常在MyISAM表中比在InnoDB或NDB表中更快。
.索引;分区修剪。与非分区表一样，正确使用索引可以显著加快对分区表的查询速度。此外，设计分区表并对这些表进行查询以利用分区修剪可 以显著提高性能。
.加载数据的性能。在MySQL 5.7中，LOAD DATA使用缓冲来提高性能。您应该知道，缓冲区在每个分区中使用130 KB内存来实现这一点。
最大分区数。
对于一个给定的表，不使用NDB存储引擎的最大可能分区数是8192。这个数字包括子分区。
对于使用NDB存储引擎的表，用户定义的最大可能分区数是根据使用的NDB集群软件版本、数据节点数量等因素来确定的。
如果在创建具有大量分区（但小于最大分区数）的表时，您会遇到类似Got error … from storage engine: Out of resources
when opening file，可以通过增加open_files_limit系统变量的值来解决这个问题。但这取决于操作系统，在所有平台上可能不可行，也不可 取。在某些情况下，由于其他原因，使用大量（数百个）分区可能也是不可取的，因此使用更多分区并不会自动带来更好的结果。
不支持查询缓存。
对于分区表不支持查询缓存，并且对于涉及分区表的查询自动禁用查询缓存。不能为此类查询启用查询缓存。
按分区键缓存。
MySQL 5.7支持分区的MyISAM表的键缓存，在缓存语句中使用CACHE INDEX 和LOAD INDEX INTO CACHE。可以为一个、几个或所有分区定义键缓存 ，并且可以将一个、几个或所有分区的索引预装到键缓存中。
分区InnoDB表不支持外键。
使用InnoDB存储引擎的分区表不支持外键。更具体地说，这意味着以下两种说法是正确的：
1.使用用户定义的分区的InnoDB表的定义不能包含外键引用；定义中包含外键引用的InnoDB表不能被分区。
2.InnoDB表定义中不能包含用户分区表的外键引用；用户定义分区的InnoDB表不能包含外键引用的列。
刚才列出的限制范围包括所有使用InnoDB存储引擎的表。不允许CREATE TABLE和ALTER TABLE语句导致表违反这些限制。
ALTER TABLE … ORDER BY.ALTER TABLE…ORDER BY column语句对已分区的表运行会导致仅在每个分区内对行进行排序。
修改主键对REPLACE语句的影响。
在某些情况下可以修改表的主键。请注意，如果您的应用程序使用REPLACE语句，并且这样做，这些语句的结果可能会被彻底改变。
全文索引
分区表不支持全文索引或搜索，即使是使用InnoDB或MyISAM存储引擎的分区表。
空间列。具有空间数据类型（如点或几何）的列不能在分区表中使用。
临时表
临时表不能分区。(错误# 17497)
日志表
不能对日志表进行分区；ALTER TABLE … PARTITION BY …在这样的表上的语句失败并报错。
分区键的数据类型。
分区键必须是一个整数列或一个解析为整数的表达式。不能使用包含枚举列的表达式。列或表达式的值也可以是NULL。
这个限制有两个例外：
1.当按[LINEAR] KEY进行分区时，可以使用除TEXT或BLOB以外的任何有效MySQL数据类型的列作为分区键，因为MySQL内部的键散列函数会根据这 些类型生成正确的数据类型。例如，下面两个CREATE TABLE语句是有效的：
mysql> CREATE TABLE tkc (c1 CHAR)
-> PARTITION BY KEY(c1)
-> PARTITIONS 4;
Query OK, 0 rows affected (0.04 sec)
mysql> CREATE TABLE tkc (c1 CHAR)
-> PARTITION BY KEY(c1)
-> PARTITIONS 4;
Query OK, 0 rows affected (0.01 sec)
2.当按范围列或列表列分区时，可以使用string、DATE和DATETIME列。例如，下面的CREATE TABLE语句都是有效的。
mysql> 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)
-> );
Query OK, 0 rows affected (0.02 sec)
mysql> 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)
-> );
Query OK, 0 rows affected (0.03 sec)
上述两种异常都不适用于BLOB或TEXT列类型。
子查询
分区键可能不是子查询，即使该子查询解析为整数值或NULL。
子分区的问题。
子分区必须使用散列或键分区。只有范围分区和列表分区可以分区。哈希分区和键分区不能分区。
SUBPARTITION BY KEY要求显式指定子分区的列或列s，不像按键分区的情况，可以省略（默认使用表的主键列）。考虑下面这条语句创建的表：
mysql> CREATE TABLE ts (
-> id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
-> name VARCHAR(30)
-> );
Query OK, 0 rows affected (0.01 sec)
你可以创建一个具有相同列的表，并按KEY进行分区，使用如下语句：
mysql> drop table ts;
Query OK, 0 rows affected (0.01 sec)
mysql> CREATE TABLE ts (
-> id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
-> name VARCHAR(30)
-> )
-> PARTITION BY KEY()
-> PARTITIONS 4;
Query OK, 0 rows affected (0.02 sec)
前面的语句被看作是这样写的，表的主键列被用作分区列：
mysql> drop table ts;
Query OK, 0 rows affected (0.01 sec)
mysql> CREATE TABLE ts (
-> id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
-> name VARCHAR(30)
-> )
-> PARTITION BY KEY(id)
-> PARTITIONS 4;
Query OK, 0 rows affected (0.01 sec)
但是，下面的语句尝试使用默认列作为子分区列创建子分区表失败，并且必须指定该列才能成功，如下所示：
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 ')
SUBPARTITIONS 4
(
PARTITION p0 VALUES LESS THAN (100),
PARTITION p1 VALUES LES' at line 6
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.03 sec)
这是一个已知的问题（见Bug #51470）。
数据目录和索引目录选项。
当与分区表一起使用时，DATA DIRECTORY和INDEX DIRECTORY受到以下限制：
.表级数据目录和索引目录选项被忽略（见Bug #32091）。
.在Windows上，MyISAM表的单个分区或子分区不支持数据目录和索引目录选项。但是，你可以将数据目录用于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也被接受，尽管这 没有影响。
分区表不支持Mysqlcheck、myisamchk和myisampack。
导出选项（刷新表）。
在MySQL 5.7.4及更早版本中，不支持FLUSH TABLES语句的FOR EXPORT选项。(Bug# 16943907)