MySQL分区 – 限制

2016年1月25日 作者：白42
暂无评论

文章目录

这一节主要说明MySQL中对分区的一些限制和不足。

禁止使用的结构

如下MySQL结构禁止在分区表达式中使用：

  • 存储过程、存储函数、UDF（用户自定函数）或者插件；
  • 已声明的变量和用户变量。

算数运算符和逻辑运算符

  • 分区表达式中是允许使用+、-和*这些算术运算符的，但是，运算结果必须是一个整型（Integer）值或NULL值（除非是采用了KEY或LINEAR KEY分区方案）；
  • DIV运算函数也是可以使用的，但是“/”运算符却是不被允许的；
  • 位运算符|、&、^、<<、>>和~也不允许在分区表达式中使用。

HANDLER声明

在MySQL5.6中，不支持对分区表使用HANDLER声明。

Server SQL mode

在创建分区表后，修改SQL mode需要慎重，不然有可能会导致脏数据、数据丢失或者分区表不可访问。

数据库服务器sql mode也会影响分区表的数据复制。如果master和slave的sql mode不一致，就有可能会导致master和slave主机上的数据不一致，甚至会导致从master复制数据到slave失败。因此，要尽量让slave主机的sql mode和master保持一致。

对性能的影响

分区操作对性能有如下影响：

文件系统操作：

分区或者重分区的操作依赖于文件系统的支持。这意味着分区的效率也会受到诸如文件系统类型、字符集、硬盘转速、交换区大小、操作系统处理效率以及一些MySQL文件处理参数的影响。最重的是要确定是否已经开启了large_files_support，并为open_files_limit设置恰当的值。如果是MyIsam引擎的话，提高myisam_max_sort_file_size 的值可以提升性能；如果要提升InnoDB分区表的性能可以开启innodb_file_per_table设置。

MyIsam和分区文件描述符：

对于一个MyIsam的分区表，MySQL会为每个打开的分区提供两个文件描述符。这意味着操作一个MyIsam的分区表要比相同的不分区的表需要太多文件描述符，尤其是在执行ALTER TABLE（比如要增加一个分区）操作时（大概会是分区数量的4倍）。这就需要注意open_files_limit是不是设置得太低了。

表锁定：

在一个表上做分区操作时会对表进行写锁定。此时读取数据不会受到影响，要执行的INSERT或UPDATE操作会在分区操作完成后执行。

存储引擎：

在MyIsam引擎的表上执行分区操作、查询或是更新操作通常都会比InnoDB或NDB快一些。

索引和分区调整：

在使用索引提升查询效率这一项上，不分区的表要比分区的表效果明显。但是使用分区本身就可以极大的提升查询效率。

分区的表不支持索引条件推送。

LOAD DATA的性能：

在MySQL5.6中，LOAD DATA使用缓冲来提升性能。需要注意的是，MySQL会为每个分区使用13KB的内存来做这些事情。

分区的最大数量

不使用NDB存储引擎的话，在MySQL5.6.7版本之前，一个表的最大分区数量是1024；在MySQL5.6.7版本以后，一个表的分区数量的上限是8192。这里提到的分区也包括二级分区。

使用NDB存储引擎的话，这个上限受到MySQL集群软件的版本、数据节点的数量、还有一些其它因素的影响。

在创建一个有大量分区的表的时候，有可能会收到这样的错误信息：Got error … from storage engine: Out of resources when opening file。此时可以尝试调高open_files_limit的值。然而，这个还要看操作系统是否支持。此外考虑到其它的因素，在某些情况下，使用大量的（数百个）分区并不能得到更好的性能提升。

不支持查询缓存

分区的表不支持查询缓存。从MySQL5.6开始，遇到对分区表的查询时，查询缓存会自动关闭，而且这个不能手动开启。

每个Partition的KEY缓存

MySQL5.6支持MyIsam分区表的KEY缓存，这个可以使用CACHE_INDEX和LOAD_INDEX_INTO_CACHE声明来实现。

可以为一个、几个或者全部分区定义KEY缓存。一个、几个或者全部分区的索引也会预加载到KEY缓存中。

在InnoDB分区表中不支持外键

使用InnoDB存储引擎的分区表不支持外键。由此可以引申出如下两个声明：

  • 一个InnoDB表中使用了分区后就不会存在外键引用，一个InnoDB表中存在外键引用就不能再被分区；
  • 在InnoDB表中不可能存在指向分区表的外键引用，在InnoDB的分区表中不会存在被外键引用的字段。

ALTER TABLE … ORDER BY

在一个分区表上执行 ALTER TABLE … ORDER BY column 语句的结果是：不会得到全表排序，只是在每个分区里完成了排序。

使用REPLACE 调整主键的影响

在某些情况下，修改一个表的主键是有必要的。但是需要注意，如果是使用了REPLACE 语句调整主键，可能会得到完全不同的结果。

全文索引

分区表不支持全文索引或者是全文检索，即使使用的存储引擎是InnoDB或MyIsam也不行。

空间字段

分区表中不可以有空间数据类型比如POINT或者GEOMETRY的字段。

临时表

临时表不可以被分区。

系统日志表

对系统日志表进行分区也是不可行的。在系统日志表执行 ALTER TABLE … PARTITION BY … 语句会报错。

分区键的数据类型

分区键必须是一个Integer字段或者返回值为Integer的表达式。采用枚举类型字段的表达式也是不可用的。字段或表达式的值也可以是NULL。

这个限制有两个例外：

1. 当采用KEY或者是LINEAR KEY分区方案时，可以使用TEXT或BLOB之外的任意数据类型，因为MySQL内置的key-hash计算函数可以从这些数据类型计算出正确的数据类型。举例，如下的两个SQL语句都是正确的：

2. 当采用RANGE COLUMNS或者LIST COLUMNS分区方案时，可使用字符串、DATE或者是DATETIME作为分区键。

这两个例外都不适用于BLOB或TEXT类型的字段。

子查询

分区键不可以是子查询，就算子查询返回的是一个整型值或者是NULL值。

关于二次分区

二次分区必须采用HASH或者KEY分区方案。只有RANGE和LIST方案的分区可以被二次分区。HASH和KEY方案的分区不可以被二次分区。

不支持延迟操作

使用延迟INSERT DELAYED向分区表中插入记录是不被允许的。执行时会报错。

关于数据目录和索引目录

在分区表上使用 DATA DIRECTORY和INDEX DIRECTORY有如下限制：

  • 表级别的 DATA DIRECTORY和INDEX DIRECTORY操作会被忽略；
  • 在Windows系统上，对MyIsam分区表的 DATA DIRECTORY和INDEX DIRECTORY操作是不被支持的。InnoDB则无类似问题。

修复或重建分区表

分区表支持 CHECK TABLE, OPTIMIZE TABLE, ANALYZE TABLE, 和 REPAIR TABLE语句。

此外也可以使用ALTER TABLE … REBUILD PARTITION或ALTER TABLE … REORGANIZE PARTITION来针对一个或多个分区进行操作。

在分区表上执行mysqlcheck, myisamchk, and myisampack等操作是不被支持的。

参考文档

分类 :  MySQL
标签 :  ,

本文链接地址：https://www.zhyea.com/2016/01/25/mysql-partition-restriction.html

发表评论

此站点使用Akismet来减少垃圾评论。了解我们如何处理您的评论数据。