禁止构建
分区表达式不支持以下几种构建:
存储过程,存储函数,UDFS或者插件
声明变量或者用户变量
可以参考分区不支持的SQL函数
算术和逻辑运算符
分区表达式支持+,-,*算术运算,但是不支持DIV和/运算(还存在,可以查看Bug #30188, Bug #33182)。但是,结果必须是整形或者NULL(线性分区键除外,想了解更多信息,可以查看分区类型)。
分区表达式不支持位运算:|,&,^,<<,>>,~ .
HANDLER语句
在MySQL 5.7.1之前的分区表不支持HANDLER语句,以后的版本取消了这一限制。
服务器SQL模式
如果要用用户自定义分区的表的话,需要注意的是,在创建分区表时的SQL模式是不保留的。在服务器SQL模式一章中已经讨论过,大多数MySQL函数和运算符的结果可能会根据服务器SQL模式而改变。所以,一旦SQL模式在创建分区表后改变,可能导致这些表的行为发生重大变化,很容易导致数据丢失或者损坏。基于以上原因,强烈建议你在创建分区表后千万不要修改服务器的SQL模式。
举个例子来说明下上述情况:
1.错误处理
1
2
3
4
5
6
|
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默认除以0的结果是NULL,而不是报错:
1
2
3
4
5
6
7
8
9
10
11
|
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模式的话,就会报错:
1
2
3
4
5
|
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模式可能会导致分区表不可用。比如有些表只有在SQL模式为NO_UNSIGNED_SUBTRACTION才发挥作用,比如:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
|
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) |
如果你在创建tu后,修改SQL模式,就可能再也不能访问这个表了:
1
2
3
4
5
6
7
|
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模式可能会导致分区表达式主备上执行是不同的结果(而在阿里主备切换是很正常的操作);这也会导致在主备复制过程中,不同分区间的数据分布不同;也有可能导致在主库上的分区表insert成功,而备库上失败。基于上述情况,最好的解决办法是保证主备间的SQL模式要保持一致(这个是DBA在运维过程中需要注意的)。
性能注意事项
下面是一些会影响分区操作性能的因素:
文件系统操作
分区或者重新分区(比如ALTER TABLE ...PARTITION BY ..., REORGANIZE PARTITION, 或者REMOVE PARTITIONING )操作取决于文件系统的实现。意思是说上述操作会受操作系统上,比如:文件系统的类型和特性,磁盘速度,swap空间,操作系统上的文件处理效率,以及MySQL服务器上的和文件句柄相关的选项,变量等因素影响。需要特别说明的是,你需要保证large_files_support是enabled的,open_files_limit设置是合理的。对于MyISAM引擎的分区表来说,需要增加myisam_max_sort_file_size以提高性能;对于InnoDB表来说,分区或者重新分区操作通过enabled innodb_file_per_table效率会更快。
也可以参考分区的最大数量。
MyISAM和分区文件描述符
对于MyISAM分区表来说,MySQL为每个打开的表,每个分区使用两个文件描述符。这也就意味着,在MyISAM分区表上想执行操作(特别是ALTER TABLE操作)比相同的表没有分区,需要更多的文件描述符。
假设我们要创建有100个分区的MyISAM表,语句如下:
1
2
3
|
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语句需要402个文件描述符,原来100个分区*2个+101个新分区*2。这是因为在重新组织表数据时,必须打开所有的(新旧)分区。所以建议在执行这些操作时,要确保--open-files-limit要设置的大些。
表锁
对表执行分区操作的进程会占用表的写锁,不影响读,例如在这些分区上的INSERT和UPDATE操作只有在分区操作完成后才能执行。
存储引擎
分区操作,比如查询,和更新操作通常情况下用MyISAM引擎要比InnoDB和NDB快。
索引;分区修剪
分区表和非分区表一样,合理的利用索引可以显著地提升查询速度。另外,设计分区表以及在这些表上的查询,可以利用分区修剪来显著提升性能。
在MySQL 5.7.3版本之前,分区表不支持索引条件下推,之后的版本可以支持了。
load data性能
在MySQL 5.7,load data 使用buffer提高性能。你需要知道的是buffer会占用每个分区的130KB来达到这个目的。
分区的最大个数
如果不是用NDB作为存储引擎的分区表,支持分区(这里子分区也包含在内)最大个数是8192。
如果使用NDB作为存储引擎的用户自定义分区的最大分区个数,取决于MySQL Cluster的版本, 数据节点和其他因素。
如果你创建一个非常多(比最大分区数要少)的分区时,遇到诸如Got error ... from storage engine: Out of resources when opening file类的错误,你可能需要增加open_files_limit。但是open_files_limit其实也依赖操作系统,可能不是所有的平台都可以建议调整。还有一些其他情况,不建议使用巨大或者成百上千个分区,所以使用越来越多的分区并不见得能带来好结果。
不支持Query cache
分区表不支持query cache,在分区表的查询中自动避开了query cache。也就是说在分区表的查询语句中query cache是不起作用的。
每个分区一个key caches
在MySQL 5.7版本中,可以通过CACHE INDEX和LOAD INDEX INTO CACHE来使用MyISAM分区表的key cache。可以为一个,几个或者所有分区都定义key cache,这样可以把一个,几个或者所有分区的索引预加载到key cache中。
不支持InnoDB分区表的外键
使用InnoDB引擎的分区表不支持外键。下面的两种具体情况来阐述:
在InnoDB表不能使用包含有外键的自定义分区;如果已经使用了外键的InnoDB表,则不能被分区。
InnoDB表不能包含一个和用户自定义分区表相关的外键;使用了用户自定义分区的InnoDB表,不能包含和外键相关的列。
刚刚列出的限制的范围包括使用InnoDB存储引擎的所有表。违反这些限制的CREATE TABLE和ALTER TABLE语句是不被允许的。
ALTER TABLE ... ORDER BY
如果在分区表上执行ALTER TABLE ... ORDER BY的话,会导致每个分区的行排序。
REPLACE语句在修改primary key上的效率
在某些情况下是需要修改表的primary key的,如果你的应用程序使用了REPLACE语句,这些语句的结果可能会被大幅度修改。
全文索引
分区表不支持全文索引或者搜索,即使分区表的存储引擎是InnoDB或者MyISAM也不行。
空间列
分区表不支持空间列,比如点或者几何。
临时表
不能对临时表进行分区(Bug #17497)。
日志表
不能对日志表进行分区,如果强制执行ALTER TABLE ... PARTITION BY ... 语句会报错。
分区键的数据类型
分区键必须是整形或者结果是整形的表达式。不能用结果为ENUM类型的表达式。因为这种类型的表达式可能是NULL。
下面两种情况是例外的:
当用LINER分区时,可以使用除TEXT或者BLOBS以外的数据类型作为分区键,因为MySQL内部的 hash函数会从这些列中产生正确的数据类型。例如,下面的创建语句是合法的:
1
2
3
4
5
6
7
8
|
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,LIST,DATE或者DATETIME列分区的话,可能会用string。例如,下面的创建语句是合法的:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
|
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可以省略(这种情况下会默认使用表的primary key)。例如,如果是这样创建表:
1
2
3
4
|
CREATE TABLE ts ( id INT NOT NULL AUTO_INCREMENT PRIMARY KEY , name VARCHAR (30) ); |
你也可以使用相同的列的创建分区表(以KEY分区),使用下面语句:
1
2
3
4
5
6
|
CREATE TABLE ts ( id INT NOT NULL AUTO_INCREMENT PRIMARY KEY , name VARCHAR (30) ) PARTITION BY KEY () PARTITIONS 4; |
前面的语句其实和下面的语句是一样的:
1
2
3
4
5
6
7
|
CREATE TABLE ts ( id INT NOT NULL AUTO_INCREMENT PRIMARY KEY , name VARCHAR (30) ) PARTITION BY KEY (id) PARTITIONS 4; |
但是,如果尝试使用缺省列作为子分区列,创建子分区表的话,以下语句将失败,必须指定该语句才能执行成功,如下所示:(bug已知 Bug #51470)。
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
|
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 #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操作。而在MySQL5.7.5之前的版本就已经引入REBUILD语法,只是不起作用(可以参考Bug #19075411, Bug #73130)。
分区表不支持mysqlcheck, myisamchk, 和 myisampack操作。
导出选项
在MySQL 5.7.4以前的版本,不支持InnoDB分区表的FLUSH TABLES语句的导出选项(Bug #16943907)。
参考资料
https://dev.mysql.com/doc/refman/5.7/en/partitioning-limitations.html
https://www.percona.com/blog/2010/12/11/mysql-partitioning-can-save-you-or-kill-you/