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 100
ENGINE=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必读