Mysql应用MySQL分区表的局限和限制详解
|
《Mysql应用MySQL分区表的局限和限制详解》要点： 禁止构建 分区表达式不支持以下几种构建：MYSQL必读 存储过程,存储函数,UDFS或者插件MYSQL必读 声明变量或者用户变量MYSQL必读 可以参考分区不支持的SQL函数MYSQL必读 算术和逻辑运算符 分区表达式支持+,-,*算术运算,但是不支持DIV和/运算(还存在,可以查看Bug #30188,Bug #33182).但是,结果必须是整形或者NULL(线性分区键除外,想了解更多信息,可以查看分区类型).MYSQL必读 分区表达式不支持位运算：|,&,^,<<,>>,~ .MYSQL必读 HANDLER语句 在MySQL 5.7.1之前的分区表不支持HANDLER语句,以后的版本取消了这一限制.MYSQL必读 服务器SQL模式 如果要用用户自定义分区的表的话,需要注意的是,在创建分区表时的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必读 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语句需要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必读 分区的最大个数 如果不是用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 分区表不支持query cache,在分区表的查询中自动避开了query cache.也就是说在分区表的查询语句中query cache是不起作用的.MYSQL必读 每个分区一个key caches 在MySQL 5.7版本中,可以通过CACHE INDEX和LOAD INDEX INTO CACHE来使用MyISAM分区表的key cache.可以为一个,几个或者所有分区都定义key cache,这样可以把一个,几个或者所有分区的索引预加载到key cache中.MYSQL必读 不支持InnoDB分区表的外键 使用InnoDB引擎的分区表不支持外键.下面的两种具体情况来阐述：MYSQL必读 在InnoDB表不能使用包含有外键的自定义分区；如果已经使用了外键的InnoDB表,则不能被分区.MYSQL必读 InnoDB表不能包含一个和用户自定义分区表相关的外键；使用了用户自定义分区的InnoDB表,不能包含和外键相关的列.MYSQL必读 刚刚列出的限制的范围包括使用InnoDB存储引擎的所有表.违反这些限制的CREATE TABLE和ALTER TABLE语句是不被允许的.MYSQL必读 ALTER TABLE ... ORDER BY 如果在分区表上执行ALTER TABLE ... ORDER BY的话,会导致每个分区的行排序.MYSQL必读 REPLACE语句在修改primary key上的效率 在某些情况下是需要修改表的primary key的,如果你的应用程序使用了REPLACE语句,这些语句的结果可能会被大幅度修改.MYSQL必读 全文索引 分区表不支持全文索引或者搜索,即使分区表的存储引擎是InnoDB或者MyISAM也不行.MYSQL必读 空间列 分区表不支持空间列,比如点或者几何.MYSQL必读 临时表 不能对临时表进行分区(Bug #17497).MYSQL必读 日志表 不能对日志表进行分区,如果强制执行ALTER TABLE ... PARTITION BY ... 语句会报错.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必读 子查询 即使子查询避开整形值或者NULL值,分区键不能子查询.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 that corresponds 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必读 表级的数据字典和索引字典被忽略(Bug #32091)MYSQL必读 在Windows系统上,MyISAM分区表不支持独立分区或子分区的数据字典和索引字典选项.但是支持InnoDB分区表的独立分区或者子分区的数据字典.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 5.7.4以前的版本,不支持InnoDB分区表的FLUSH TABLES语句的导出选项(Bug #16943907).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必读 （编辑：李大同） 【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容! |