MySQL分区表的局限和限制详解
禁止构建
分区表达式不支持以下几种构建:
存储过程,存储函数,UDFS或者插件
声明变量或者用户变量
可以参考分区不支持的SQL函数
算术和逻辑运算符
分区表达式支持+,-,*算术运算,但是不支持DIV和/运算(还存在,可以查看Bug#30188,Bug#33182)。但是,结果必须是整形或者NULL(线性分区键除外,想了解更多信息,可以查看分区类型)。
分区表达式不支持位运算:|,&,^,<<,>>,~.
HANDLER语句
在MySQL5.7.1之前的分区表不支持HANDLER语句,以后的版本取消了这一限制。
服务器SQL模式
如果要用用户自定义分区的表的话,需要注意的是,在创建分区表时的SQL模式是不保留的。在服务器SQL模式一章中已经讨论过,大多数MySQL函数和运算符的结果可能会根据服务器SQL模式而改变。所以,一旦SQL模式在创建分区表后改变,可能导致这些表的行为发生重大变化,很容易导致数据丢失或者损坏。基于以上原因,强烈建议你在创建分区表后千万不要修改服务器的SQL模式。
举个例子来说明下上述情况:
1.错误处理
mysql>CREATETABLEtn(c1INT) ->PARTITIONBYLIST(1DIVc1)( ->PARTITIONp0VALUESIN(NULL), ->PARTITIONp1VALUESIN(1) ->); QueryOK,0rowsaffected(0.05sec)
MySQL默认除以0的结果是NULL,而不是报错:
mysql>SELECT@@sql_mode; +------------+ |@@sql_mode| +------------+ || +------------+ 1rowinset(0.00sec) mysql>INSERTINTOtnVALUES(NULL),(0),(1); QueryOK,3rowsaffected(0.00sec) Records:3Duplicates:0Warnings:0
然而如果我们修改SQL模式的话,就会报错:
mysql>SETsql_mode='STRICT_ALL_TABLES,ERROR_FOR_DIVISION_BY_ZERO'; QueryOK,0rowsaffected(0.00sec) mysql>INSERTINTOtnVALUES(NULL),(0),(1); ERROR1365(22012):Divisionby0
2.表辅助功能
有时候修改SQL模式可能会导致分区表不可用。比如有些表只有在SQL模式为NO_UNSIGNED_SUBTRACTION才发挥作用,比如:
mysql>SELECT@@sql_mode; +------------+ |@@sql_mode| +------------+ || +------------+ 1rowinset(0.00sec) mysql>CREATETABLEtu(c1BIGINTUNSIGNED) ->PARTITIONBYRANGE(c1-10)( ->PARTITIONp0VALUESLESSTHAN(-5), ->PARTITIONp1VALUESLESSTHAN(0), ->PARTITIONp2VALUESLESSTHAN(5), ->PARTITIONp3VALUESLESSTHAN(10), ->PARTITIONp4VALUESLESSTHAN(MAXVALUE) ->); ERROR1563(HY000):Partitionconstantisoutofpartitionfunctiondomain mysql>SETsql_mode='NO_UNSIGNED_SUBTRACTION'; QueryOK,0rowsaffected(0.00sec) mysql>SELECT@@sql_mode; +-------------------------+ |@@sql_mode| +-------------------------+ |NO_UNSIGNED_SUBTRACTION| +-------------------------+ 1rowinset(0.00sec) mysql>CREATETABLEtu(c1BIGINTUNSIGNED) ->PARTITIONBYRANGE(c1-10)( ->PARTITIONp0VALUESLESSTHAN(-5), ->PARTITIONp1VALUESLESSTHAN(0), ->PARTITIONp2VALUESLESSTHAN(5), ->PARTITIONp3VALUESLESSTHAN(10), ->PARTITIONp4VALUESLESSTHAN(MAXVALUE) ->); QueryOK,0rowsaffected(0.05sec)
如果你在创建tu后,修改SQL模式,就可能再也不能访问这个表了:
mysql>SETsql_mode='';QueryOK,0rowsaffected(0.00sec) mysql>SELECT*FROMtu; ERROR1563(HY000):Partitionconstantisoutofpartitionfunctiondomain mysql>INSERTINTOtuVALUES(20); ERROR1563(HY000):Partitionconstantisoutofpartitionfunctiondomain
服务器端的SQL模式也会影响分区表的复制。在主备间使用不同的SQL模式可能会导致分区表达式主备上执行是不同的结果(而在阿里主备切换是很正常的操作);这也会导致在主备复制过程中,不同分区间的数据分布不同;也有可能导致在主库上的分区表insert成功,而备库上失败。基于上述情况,最好的解决办法是保证主备间的SQL模式要保持一致(这个是DBA在运维过程中需要注意的)。
性能注意事项
下面是一些会影响分区操作性能的因素:
文件系统操作
分区或者重新分区(比如ALTERTABLE...PARTITIONBY...,REORGANIZEPARTITION,或者REMOVEPARTITIONING)操作取决于文件系统的实现。意思是说上述操作会受操作系统上,比如:文件系统的类型和特性,磁盘速度,swap空间,操作系统上的文件处理效率,以及MySQL服务器上的和文件句柄相关的选项,变量等因素影响。需要特别说明的是,你需要保证large_files_support是enabled的,open_files_limit设置是合理的。对于MyISAM引擎的分区表来说,需要增加myisam_max_sort_file_size以提高性能;对于InnoDB表来说,分区或者重新分区操作通过enabledinnodb_file_per_table效率会更快。
也可以参考分区的最大数量。
MyISAM和分区文件描述符
对于MyISAM分区表来说,MySQL为每个打开的表,每个分区使用两个文件描述符。这也就意味着,在MyISAM分区表上想执行操作(特别是ALTERTABLE操作)比相同的表没有分区,需要更多的文件描述符。
假设我们要创建有100个分区的MyISAM表,语句如下:
CREATETABLEt(c1VARCHAR(50)) PARTITIONBYKEY(c1)PARTITIONS100 ENGINE=MYISAM;
简单来讲,在这个例子中,虽然我们用的KEY分区,但是文件描述符的问题,在所有使用表引擎是MyISAM的分区里都会遇到,不管是分区类型是哪种。但是使用其他存储引擎(比如InnoDB)的分区表没有这个问题。
假设你想对t重新分区,想让它有101个分区的话,使用下面的语句:
ALTERTABLEtPARTITIONBYKEY(c1)PARTITIONS101;
如果要处理ALTERTABLE语句需要402个文件描述符,原来100个分区*2个+101个新分区*2。这是因为在重新组织表数据时,必须打开所有的(新旧)分区。所以建议在执行这些操作时,要确保--open-files-limit要设置的大些。
表锁
对表执行分区操作的进程会占用表的写锁,不影响读,例如在这些分区上的INSERT和UPDATE操作只有在分区操作完成后才能执行。
存储引擎
分区操作,比如查询,和更新操作通常情况下用MyISAM引擎要比InnoDB和NDB快。
索引;分区修剪
分区表和非分区表一样,合理的利用索引可以显著地提升查询速度。另外,设计分区表以及在这些表上的查询,可以利用分区修剪来显著提升性能。
在MySQL5.7.3版本之前,分区表不支持索引条件下推,之后的版本可以支持了。
loaddata性能
在MySQL5.7,loaddata使用buffer提高性能。你需要知道的是buffer会占用每个分区的130KB来达到这个目的。
分区的最大个数
如果不是用NDB作为存储引擎的分区表,支持分区(这里子分区也包含在内)最大个数是8192。
如果使用NDB作为存储引擎的用户自定义分区的最大分区个数,取决于MySQLCluster的版本,数据节点和其他因素。
如果你创建一个非常多(比最大分区数要少)的分区时,遇到诸如Goterror...fromstorageengine:Outofresourceswhenopeningfile类的错误,你可能需要增加open_files_limit。但是open_files_limit其实也依赖操作系统,可能不是所有的平台都可以建议调整。还有一些其他情况,不建议使用巨大或者成百上千个分区,所以使用越来越多的分区并不见得能带来好结果。
不支持Querycache
分区表不支持querycache,在分区表的查询中自动避开了querycache。也就是说在分区表的查询语句中querycache是不起作用的。
每个分区一个keycaches
在MySQL5.7版本中,可以通过CACHEINDEX和LOADINDEXINTOCACHE来使用MyISAM分区表的keycache。可以为一个,几个或者所有分区都定义keycache,这样可以把一个,几个或者所有分区的索引预加载到keycache中。
不支持InnoDB分区表的外键
使用InnoDB引擎的分区表不支持外键。下面的两种具体情况来阐述:
在InnoDB表不能使用包含有外键的自定义分区;如果已经使用了外键的InnoDB表,则不能被分区。
InnoDB表不能包含一个和用户自定义分区表相关的外键;使用了用户自定义分区的InnoDB表,不能包含和外键相关的列。
刚刚列出的限制的范围包括使用InnoDB存储引擎的所有表。违反这些限制的CREATETABLE和ALTERTABLE语句是不被允许的。
ALTERTABLE...ORDERBY
如果在分区表上执行ALTERTABLE...ORDERBY的话,会导致每个分区的行排序。
REPLACE语句在修改primarykey上的效率
在某些情况下是需要修改表的primarykey的,如果你的应用程序使用了REPLACE语句,这些语句的结果可能会被大幅度修改。
全文索引
分区表不支持全文索引或者搜索,即使分区表的存储引擎是InnoDB或者MyISAM也不行。
空间列
分区表不支持空间列,比如点或者几何。
临时表
不能对临时表进行分区(Bug#17497)。
日志表
不能对日志表进行分区,如果强制执行ALTERTABLE...PARTITIONBY...语句会报错。
分区键的数据类型
分区键必须是整形或者结果是整形的表达式。不能用结果为ENUM类型的表达式。因为这种类型的表达式可能是NULL。
下面两种情况是例外的:
当用LINER分区时,可以使用除TEXT或者BLOBS以外的数据类型作为分区键,因为MySQL内部的hash函数会从这些列中产生正确的数据类型。例如,下面的创建语句是合法的:
CREATETABLEtkc(c1CHAR) PARTITIONBYKEY(c1) PARTITIONS4; CREATETABLEtke (c1ENUM('red','orange','yellow','green','blue','indigo','violet')) PARTITIONBYLINEARKEY(c1) PARTITIONS6;
当用RANGE,LIST,DATE或者DATETIME列分区的话,可能会用string。例如,下面的创建语句是合法的:
CREATETABLErc(c1INT,c2DATE) PARTITIONBYRANGECOLUMNS(c2)( PARTITIONp0VALUESLESSTHAN('1990-01-01'), PARTITIONp1VALUESLESSTHAN('1995-01-01'), PARTITIONp2VALUESLESSTHAN('2000-01-01'), PARTITIONp3VALUESLESSTHAN('2005-01-01'), PARTITIONp4VALUESLESSTHAN(MAXVALUE) ); CREATETABLElc(c1INT,c2CHAR(1)) PARTITIONBYLISTCOLUMNS(c2)( PARTITIONp0VALUESIN('a','d','g','j','m','p','s','v','y'), PARTITIONp1VALUESIN('b','e','h','k','n','q','t','w','z'), PARTITIONp2VALUESIN('c','f','i','l','o','r','u','x',NULL) );
上述异常都不适用于BLOB或TEXT列类型。
子查询
即使子查询避开整形值或者NULL值,分区键不能子查询。
子分区的问题
子分区必须使用HASH或者KEY分区。只有RANGE和LIST分区支持被子分区;HASH和KEY不支持被子分区。
SUBPARTITIONBYKEY要求显示指定子分区列,不像PARTITIONBYKEY可以省略(这种情况下会默认使用表的primarykey)。例如,如果是这样创建表:
CREATETABLEts( idINTNOTNULLAUTO_INCREMENTPRIMARYKEY, nameVARCHAR(30) );
你也可以使用相同的列的创建分区表(以KEY分区),使用下面语句:
CREATETABLEts( idINTNOTNULLAUTO_INCREMENTPRIMARYKEY, nameVARCHAR(30) ) PARTITIONBYKEY() PARTITIONS4;
前面的语句其实和下面的语句是一样的:
CREATETABLEts( idINTNOTNULLAUTO_INCREMENTPRIMARYKEY, nameVARCHAR(30) ) PARTITIONBYKEY(id) PARTITIONS4;
但是,如果尝试使用缺省列作为子分区列,创建子分区表的话,以下语句将失败,必须指定该语句才能执行成功,如下所示:(bug已知Bug#51470)。
mysql>CREATETABLEts( ->idINTNOTNULLAUTO_INCREMENTPRIMARYKEY, ->nameVARCHAR(30) ->) ->PARTITIONBYRANGE(id) ->SUBPARTITIONBYKEY() ->SUBPARTITIONS4 ->( ->PARTITIONp0VALUESLESSTHAN(100), ->PARTITIONp1VALUESLESSTHAN(MAXVALUE) ->); ERROR1064(42000):YouhaveanerrorinyourSQLsyntax;checkthemanualthat correspondstoyourMySQLserverversionfortherightsyntaxtousenear') mysql>CREATETABLEts( ->idINTNOTNULLAUTO_INCREMENTPRIMARYKEY, ->nameVARCHAR(30) ->) ->PARTITIONBYRANGE(id) ->SUBPARTITIONBYKEY(id) ->SUBPARTITIONS4 ->( ->PARTITIONp0VALUESLESSTHAN(100), ->PARTITIONp1VALUESLESSTHAN(MAXVALUE) ->); QueryOK,0rowsaffected(0.07sec)
数据字典和索引字典选项
分区表的数据字典和索引字典受以下因素制约:
表级的数据字典和索引字典被忽略(Bug#32091)
在Windows系统上,MyISAM分区表不支持独立分区或子分区的数据字典和索引字典选项。但是支持InnoDB分区表的独立分区或者子分区的数据字典。
修复和重建分区表
分区表支持CHECKTABLE,OPTIMIZETABLE,ANALYZETABLE,和REPAIRTABLE语句。
另外,你也可以用ALTERTABLE...REBUILDPARTITION在一个分区表上重建一个或多个分区;用ALTERTABLE...REORGANIZEPARTITION同样可以重建分区。
从MySQL5.7.2开始,子分区支持ANALYZE,CHECK,OPTIMIZE,REPAIR,和TRUNCATE操作。而在MySQL5.7.5之前的版本就已经引入REBUILD语法,只是不起作用(可以参考Bug#19075411,Bug#73130)。
分区表不支持mysqlcheck,myisamchk,和myisampack操作。
导出选项
在MySQL5.7.4以前的版本,不支持InnoDB分区表的FLUSHTABLES语句的导出选项(Bug#16943907)。
参考资料
https://dev.mysql.com/doc/refman/5.7/en/partitioning-limitations.html
https://www.slideshare.net/datacharmer/mysql-partitions-tutorial/34-Partition_pruning_unpartitioned_tableexplain_partitions
https://www.percona.com/blog/2010/12/11/mysql-partitioning-can-save-you-or-kill-you/