MySQL分区表的局限和限制详解

2024-02-27 19:08:03 55

禁止构建

分区表达式不支持以下几种构建：

存储过程，存储函数，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/

MySQL分区表的局限和限制详解

热门推荐

随机推荐