Mysql应用MySQL分区表的局限和限制详解

概述介绍《Mysql应用MySQL分区表的局限和限制详解》开发教程，希望对您有用。

《MysqL应用MysqL分区表的局限和限制详解》要点：
本文介绍了MysqL应用MysqL分区表的局限和限制详解，希望对您有用。如果有疑问，可以联系我们。

禁止构建
MysqL必读

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

存储过程,存储函数,UDFS或者插件MysqL必读

声明变量或者用户变量MysqL必读

可以参考分区不支持的sql函数MysqL必读

算术和逻辑运算符
MysqL必读

分区表达式支持+,-,*算术运算,但是不支持div和/运算(还存在,可以查看BUG #30188,BUG #33182).但是,结果必须是整形或者NulL(线性分区键除外,想了解更多信息,可以查看分区类型).MysqL必读

分区表达式不支持位运算：|,&,^,<<,>>,~ .MysqL必读

HANDLER语句
MysqL必读

在MysqL 5.7.1之前的分区表不支持HANDLER语句,以后的版本取消了这一限制.MysqL必读

服务器sql模式
MysqL必读

如果要用用户自定义分区的表的话,需要注意的是,在创建分区表时的sql模式是不保留的.在服务器sql模式一章中已经讨论过,大多数MysqL函数和运算符的结果可能会根据服务器sql模式而改变.所以,一旦sql模式在创建分区表后改变,可能导致这些表的行为发生重大变化,很容易导致数据丢失或者损坏.基于以上原因,强烈建议你在创建分区表后千万不要修改服务器的sql模式.MysqL必读

举个例子来说明下上述情况：MysqL必读

1.错误处理MysqL必读

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,而不是报错：MysqL必读

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模式的话,就会报错：MysqL必读

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),(1);ERROR 1365 (22012): division by 0

2.表辅助功能MysqL必读

有时候修改sql模式可能会导致分区表不可用.比如有些表只有在sql模式为NO_UNSIGNED_SUBTRACTION才发挥作用,比如：MysqL必读

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 p4 VALUES LESS THAN (MAXVALUE)  -> );   query OK,0 rows affected (0.05 sec)

如果你在创建tu后,修改sql模式,就可能再也不能访问这个表了：MysqL必读

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在运维过程中需要注意的).MysqL必读

性能注意事项
MysqL必读

下面是一些会影响分区 *** 作性能的因素：MysqL必读

文件系统 *** 作
分区或者重新分区(比如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效率会更快.MysqL必读

也可以参考分区的最大数量.MysqL必读

MyISAM和分区文件描述符MysqL必读

对于MyISAM分区表来说,MysqL为每个打开的表,每个分区使用两个文件描述符.这也就意味着,在MyISAM分区表上想执行 *** 作(特别是ALTER table *** 作)比相同的表没有分区,需要更多的文件描述符.MysqL必读

假设我们要创建有100个分区的MyISAM表,语句如下：MysqL必读

CREATE table t (c1 VARCHAR(50))PARTITION BY KEY (c1) PARTITIONS 100ENGINE=MYISAM;

简单来讲,在这个例子中,虽然我们用的KEY分区,但是文件描述符的问题,在所有使用表引擎是MyISAM的分区里都会遇到,不管是分区类型是哪种.但是使用其他存储引擎(比如InnoDB)的分区表没有这个问题.MysqL必读

假设你想对t重新分区,想让它有101个分区的话,使用下面的语句：MysqL必读

ALTER table t PARTITION BY KEY (c1) PARTITIONS 101;
MysqL必读

如果要处理ALTER table语句需要402个文件描述符,原来100个分区*2个+101个新分区*2.这是因为在重新组织表数据时,必须打开所有的(新旧)分区.所以建议在执行这些 *** 作时,要确保--open-files-limit要设置的大些.MysqL必读

表锁MysqL必读

对表执行分区 *** 作的进程会占用表的写锁,不影响读,例如在这些分区上的INSERT和UPDATE *** 作只有在分区 *** 作完成后才能执行.MysqL必读

存储引擎MysqL必读

分区 *** 作,比如查询,和更新 *** 作通常情况下用MyISAM引擎要比InnoDB和NDB快.MysqL必读

索引；分区修剪MysqL必读

分区表和非分区表一样,合理的利用索引可以显著地提升查询速度.另外,设计分区表以及在这些表上的查询,可以利用分区修剪来显著提升性能.MysqL必读

在MysqL 5.7.3版本之前,分区表不支持索引条件下推,之后的版本可以支持了.MysqL必读

load data性能MysqL必读

在MysqL 5.7,load data 使用buffer提高性能.你需要知道的是buffer会占用每个分区的130KB来达到这个目的.MysqL必读

分区的最大个数
MysqL必读

如果不是用NDB作为存储引擎的分区表,支持分区(这里子分区也包含在内)最大个数是8192.MysqL必读

如果使用NDB作为存储引擎的用户自定义分区的最大分区个数,取决于MysqL Cluster的版本,数据节点和其他因素.MysqL必读

如果你创建一个非常多(比最大分区数要少)的分区时,遇到诸如Got error ... from storage engine: Out of resources when opening file类的错误,你可能需要增加open_files_limit.但是open_files_limit其实也依赖 *** 作系统,可能不是所有的平台都可以建议调整.还有一些其他情况,不建议使用巨大或者成百上千个分区,所以使用越来越多的分区并不见得能带来好结果.MysqL必读

不支持query cache
MysqL必读

分区表不支持query cache,在分区表的查询中自动避开了query cache.也就是说在分区表的查询语句中query cache是不起作用的.MysqL必读

每个分区一个key caches
MysqL必读

在MysqL 5.7版本中,可以通过CACHE INDEX和LOAD INDEX INTO CACHE来使用MyISAM分区表的key cache.可以为一个,几个或者所有分区都定义key cache,这样可以把一个,几个或者所有分区的索引预加载到key cache中.MysqL必读

不支持InnoDB分区表的外键
MysqL必读

使用InnoDB引擎的分区表不支持外键.下面的两种具体情况来阐述：MysqL必读

在InnoDB表不能使用包含有外键的自定义分区；如果已经使用了外键的InnoDB表,则不能被分区.MysqL必读

InnoDB表不能包含一个和用户自定义分区表相关的外键；使用了用户自定义分区的InnoDB表,不能包含和外键相关的列.MysqL必读

刚刚列出的限制的范围包括使用InnoDB存储引擎的所有表.违反这些限制的CREATE table和ALTER table语句是不被允许的.MysqL必读

ALTER table ... ORDER BY
MysqL必读

如果在分区表上执行ALTER table ... ORDER BY的话,会导致每个分区的行排序.MysqL必读

REPLACE语句在修改primary key上的效率
MysqL必读

在某些情况下是需要修改表的primary key的,如果你的应用程序使用了REPLACE语句,这些语句的结果可能会被大幅度修改.MysqL必读

全文索引
MysqL必读

分区表不支持全文索引或者搜索,即使分区表的存储引擎是InnoDB或者MyISAM也不行.MysqL必读

空间列
MysqL必读

分区表不支持空间列,比如点或者几何.MysqL必读

临时表
MysqL必读

不能对临时表进行分区(BUG #17497).MysqL必读

日志表
MysqL必读

不能对日志表进行分区,如果强制执行ALTER table ... PARTITION BY ... 语句会报错.MysqL必读

分区键的数据类型
MysqL必读

分区键必须是整形或者结果是整形的表达式.不能用结果为ENUM类型的表达式.因为这种类型的表达式可能是NulL.MysqL必读

下面两种情况是例外的：MysqL必读

当用liNER分区时,可以使用除TEXT或者BLOBS以外的数据类型作为分区键,因为MysqL内部的 hash函数会从这些列中产生正确的数据类型.例如,下面的创建语句是合法的：MysqL必读

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.例如,下面的创建语句是合法的：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));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列类型.MysqL必读

子查询
MysqL必读

即使子查询避开整形值或者NulL值,分区键不能子查询.MysqL必读

子分区的问题
MysqL必读

子分区必须使用HASH或者KEY分区.只有RANGE和List分区支持被子分区；HASH和KEY不支持被子分区.MysqL必读

SUBPARTITION BY KEY要求显示指定子分区列,不像PARTITION BY KEY可以省略(这种情况下会默认使用表的primary key).例如,如果是这样创建表：MysqL必读

CREATE table ts (  ID INT NOT NulL auto_INCREMENT PRIMARY KEY,name VARCHAR(30));

你也可以使用相同的列的创建分区表(以KEY分区),使用下面语句：MysqL必读

CREATE table ts (  ID INT NOT NulL auto_INCREMENT PRIMARY KEY,name VARCHAR(30))PARTITION BY KEY()PARTITIONS 4;

前面的语句其实和下面的语句是一样的：MysqL必读

CREATE table ts (  ID INT NOT NulL auto_INCREMENT PRIMARY KEY,name VARCHAR(30))PARTITION BY KEY(ID)PARTITIONS 4; 

但是,如果尝试使用缺省列作为子分区列,创建子分区表的话,以下语句将失败,必须指定该语句才能执行成功,如下所示：(BUG已知 BUG #51470).MysqL必读

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 thatcorresponds 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)

数据字典和索引字典选项
MysqL必读

分区表的数据字典和索引字典受以下因素制约：MysqL必读

表级的数据字典和索引字典被忽略(BUG #32091)MysqL必读

在windows系统上,MyISAM分区表不支持独立分区或子分区的数据字典和索引字典选项.但是支持InnoDB分区表的独立分区或者子分区的数据字典.MysqL必读

修复和重建分区表
MysqL必读

分区表支持CHECK table,OPTIMIZE table,ANALYZE table,和 REPAIR table语句.MysqL必读

另外,你也可以用ALTER table ... REBUILD PARTITION在一个分区表上重建一个或多个分区；用ALTER table ... REORGANIZE PARTITION同样可以重建分区.MysqL必读

从MysqL 5.7.2开始,子分区支持ANALYZE,CHECK,OPTIMIZE,REPAIR,和 TruncATE *** 作.而在MysqL5.7.5之前的版本就已经引入REBUILD语法,只是不起作用(可以参考BUG #19075411,BUG #73130).MysqL必读

分区表不支持MysqLcheck,myisamchk,和 myisampack *** 作.MysqL必读

导出选项
MysqL必读

在MysqL 5.7.4以前的版本,不支持InnoDB分区表的FLUSH tableS语句的导出选项(BUG #16943907).MysqL必读

参考资料
MysqL必读

https://dev.MysqL.com/doc/refman/5.7/en/partitioning-limitations.HTMLMysqL必读

https://www.slIDeshare.net/datacharmer/MysqL-partitions-tutorial/34-Partition_pruning_unpartitioned_tableexplain_partitionsMysqL必读

https://www.percona.com/blog/2010/12/11/MysqL-partitioning-can-save-you-or-kill-you/MysqL必读

总结

以上是内存溢出为你收集整理的Mysql应用MySQL分区表的局限和限制详解全部内容，希望文章能够帮你解决Mysql应用MySQL分区表的局限和限制详解所遇到的程序开发问题。

如果觉得内存溢出网站内容还不错，欢迎将内存溢出网站推荐给程序员好友。