当前位置:   article > 正文

Mysql分区_error in list of partitions to reorganize

error in list of partitions to reorganize

                                                                               Mysql分区

1、什么是mysql分区

    

使用myisam引擎的一张表主要对应着三个文件,一个是frm存放表结构的,一个是myd存放表数据的,一个是myi存表索引的。如果一张表的数据量太大的话,那么myd,myi就会变的很大,查找数据就会变的很慢,这个时候我们可以利用mysql的分区功能,在物理上将这一张表对应的三个文件,分割成许多个小块,这样呢,我们查找一条数据时,就不用全部查找了,只要知道这条数据在哪一块,然后在那一块找就行了。如果表的数据太大,可能一个磁盘放不下,这个时候,我们可以把 数据分配到不同的磁盘里面去。
使用innoDB引擎的/mysql/data/数据库名目录下一张表有一个frm文件存放数据结构,其他的数据部分全部都存在在/mysql/data目录下的ibdata文件中。

2、mysql分区的有点和缺点

 

MySQL分区带来的好处太多了,这里强调两点好处:

性能的提升(Increased performance) - 在扫描操作中,如果MySQL的优化器知道哪个分区中才包含特定查询中需要的数据,它就能直接去扫描那些分区的数据,而不用浪费很多时间扫描不需要的地方 了。需要举个例子?好啊,百万行的表划分为10个分区,每个分区就包含十万行数据,那么查询分区需要的时间仅仅是全表扫描的十分之一了,很明显的对比。同 时对十万行的表建立索引的速度也会比百万行的快得多得多。如果你能把这些分区建立在不同的磁盘上,这时候的I/O读写速度就“不堪设想”(没用错词,真的 太快了,理论上100倍的速度提升啊,这是多么快的响应速度啊,所以有点不堪设想了)了。

对数据管理的简化(Simplified data management) - MySQL分区技术可以让DBA对数据的管理能力提升。通过优良的MySQL分区,DBA可以简化特定数据操作的执行方式。例如:DBA在对某些分区的内容进行删除的同时能保证余下的分区的数据完整性(这是跟对表的数据删除这种大动作做比较的)。 此外分区是由MySQL系统直接管理的,DBA不需要手工的去划分和维护。

在下面的场景中,分区可以起到非常大的作用:

A:表非常大以至于无法全部都放在内存中,或者只在表的最后部分有热点数据,其他都是历史数据

B:分区表的数据更容易维护,如:想批量删除大量数据可以使用清除整个分区的方式。另外,还可以对一个独立分区进行优化、检查、修复等操作

C:分区表的数据可以分布在不同的物理设备上,从而高效地利用多个硬件设备

D:可以使用分区表来避免某些特殊的瓶颈,如:innodb的单个索引的互斥访问,ext3文件系统的inode锁竞争等

E:如果需要,还可以备份和恢复独立的分区,这在非常大的数据集的场景下效果非常好

F:优化查询,在where字句中包含分区列时,可以只使用必要的分区来提高查询效率,同时在涉及sum()和count()这类聚合函数的查询时,可以在每个分区上面并行处理,最终只需要汇总所有分区得到的结果。

分区本身也有一些限制:

A:一个表最多只能有1024个分区(mysql5.6之后支持8192个分区)

B:在mysql5.1中分区表达式必须是整数,或者是返回整数的表达式,在5.5之后,某些场景可以直接使用字符串列和日期类型列来进行分区(使用varchar字符串类型列时,一般还是字符串的日期作为分区)。

C:如果分区字段中有主键或者唯一索引列,那么所有主键列和唯一索引列都必须包含进来,如果表中有主键或唯一索引,那么分区键必须是主键或唯一索引

D:分区表中无法使用外键约束

E:mysql数据库支持的分区类型为水平分区,并不支持垂直分区,因此,mysql数据库的分区中索引是局部分区索引,一个分区中既存放了数据又存放了索引,而全局分区是指的数据库放在各个分区中,但是所有的数据的索引放在另外一个对象中

F:目前mysql不支持空间类型和临时表类型进行分区。不支持全文索引



3、mysql分区的几种方式

1,range分区
按照RANGE分区的表是通过如下一种方式进行分区的,每个分区包含那些分区 表达式的值位于一个给定的连续区间内的行。
2,list分区
LIST分区中每个分区的定义和选择是基于某列的值从属于一个值列表集中的一个值,而RANGE分 区是从属于一个连续区间值的集合。
3,hash分区
HASH分区主要用来确保数据在预先确定数目的分区中平均分布,你所要做的只是基于将要被哈希的列值指定一个列值或 表达式,以 及指定被分区的表将要被分割成的分区数量。
4,key分区
按照KEY进行分区类似于按照HASH分区,除了HASH分区使用的用 户定义的 表达式,而KEY分区的 哈希函数是由MySQL 服务器提供。
5,子分区
子分区是 分区表中每个分区的再次分割,子分区既可以使用HASH希分区,也可以使用KEY分区。这 也被称为复合分区(composite partitioning)。


4、mysql分区与水平分表的区别



5、mysql分区的具体实现

RANGE 

分区:基于属于一个给定连续区间的列值,把多行分配给分区。

RANGE 

分区:基于属于一个给定连续区间的列值,把多行分配给分区。

RANGE 

分区:基于属于一个给定连续区间的列值,把多行分配给分区。

RANGE 

分区:基于属于一个给定连续区间的列值,把多行分配给分区。

RANGE 

分区:基于属于一个给定连续区间的列值,把多行分配给分区。

RANGE 

分区:基于属于一个给定连续区间的列值,把多行分配给分区。

RANGE 

分区:基于


RANGE 分区:基于属于一个给定连续区间的列值,把多行分配给分区。

复制代码代码如下:

DROP TABLE IF EXISTS `p_range`;
CREATE TABLE `p_range` (
`id` int(10) NOT NULL AUTO_INCREMENT,
`name` char(20) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=9 DEFAULT CHARSET=utf8
/*!50100 PARTITION BY RANGE (id)
(PARTITION p0 VALUES LESS THAN (8) ENGINE = MyISAM) */;

range分区就是 partition by range(id) 表示按id 1-7的数据存储在p0分区;如果id大于7了则数据不能写入了,因为没有对应的数据分区来存储;
所以这时在创建分区时需要使用maxvalues关键字了
复制代码代码如下:

PARTITION BY RANGE (id)
(
PARTITION p0 VALUES LESS THAN (8),
PARTITION p1 VALUES LESS THAN MAXVALUE)

这样就表示,所有id大于7的数据记录存在在p1分区里。
RANGE分区在如下场合特别有用:
·  当需要删除“旧的”数据时。如果你使用上面最近的那个例子给出的分区方案,你只需简单地使用 “ALTER TABLE employees DROP PARTITION p0;”来删除所有在1991年前就已经停止工作的雇员相对应的所有行。对于有大量行的表,这比运行一个如“DELETE FROM employees WHERE YEAR(separated) <= 1990;”这样的一个DELETE查询要有效得多。
·  想要使用一个包含有日期或时间值,或包含有从一些其他级数开始增长的值的列。
·  经常运行直接依赖于用于分割表的列的查询。例如,当执行一个如“SELECT COUNT(*) FROM employees WHERE YEAR(separated) = 2000 GROUP BY store_id;”这样的查询时,MySQL可以很迅速地确定只有分区p2需要扫描,这是因为余下的分区不可能包含有符合该WHERE子句的任何记录。
LIST 分区:类似于按RANGE分区,区别在于LIST分区是基于列值匹配一个离散值集合中的某个值来进行选择。
list分区可以理解为按一个键的id区间进行数据存储,比如类型表 1,2,3,4的所有记录存储在p0里面,5,6,7,8存在在p1分区里面
这里与range分区一样,如果现在有条记录typeid是9,那么这条记录是不能存入的;
需要注意的是:LIST分区没有类似如“VALUES LESS THAN MAXVALUE”这样的包含其他值在内的定义。将要匹配的任何值都必须在值列表中找到。
复制代码代码如下:

DROP TABLE IF EXISTS `p_list`;
CREATE TABLE `p_list` (
`id` int(10) NOT NULL AUTO_INCREMENT,
`typeid` mediumint(10) NOT NULL DEFAULT '0',
`typename` char(20) DEFAULT NULL,
PRIMARY KEY (`id`,`typeid`)
) ENGINE=MyISAM AUTO_INCREMENT=9 DEFAULT CHARSET=utf8
/*!50100 PARTITION BY LIST (typeid)
(PARTITION p0 VALUES IN (1,2,3,4) ENGINE = MyISAM,
PARTITION p1 VALUES IN (5,6,7,8) ENGINE = MyISAM) */;

HASH分区:基于用户定义的表达式的返回值来进行选择的分区,该表达式使用将要插入到表中的这些行的列值进行计算。这个函数可以包含MySQL 中有效的、产生非负整数值的任何表达式。
HASH分区主要用来确保数据在预先确定数目的分区中平均分布。在RANGE和LIST分区中,必须明确指定一个给定的列值或列值集合应该保存在哪个分区中;而在HASH分区中,MySQL 自动完成这些工作,你所要做的只是基于将要被哈希的列值指定一个列值或表达式,以及指定被分区的表将要被分割成的分区数量。
要使用HASH分区来分割一个表,要在CREATE TABLE 语句上添加一个“PARTITION BY HASH (expr)”子句,其中“expr”是一个返回一个整数的表达式。它可以仅仅是字段类型为MySQL 整型的一列的名字。此外,你很可能需要在后面再添加一个“PARTITIONS num”子句,其中num 是一个非负的整数,它表示表将要被分割成分区的数量。如果没有包括一个PARTITIONS子句,那么分区的数量将默认为1。
复制代码代码如下:

DROP TABLE IF EXISTS `p_hash`;
CREATE TABLE `p_hash` (
`id` int(10) NOT NULL AUTO_INCREMENT,
`storeid` mediumint(10) NOT NULL DEFAULT '0',
`storename` char(255) DEFAULT NULL,
PRIMARY KEY (`id`,`storeid`)
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8
/*!50100 PARTITION BY HASH (storeid)
PARTITIONS 4 */;

InnoDB引擎
简单点说就是数据的存入可以按 partition by hash(expr); 这里的expr可以是键名也可以是表达式比如YEAR(time),如果是表达式的情况下
“但是应当记住,每当插入或更新(或者可能删除)一行,这个表达式都要计算一次;这意味着非常复杂的表达式可能会引起性能问题,尤其是在执行同时影响大量行的运算(例如批量插入)的时候。 ”
在执行删除、写入、更新时这个表达式都会计算一次。
数据的分布采用基于用户函数结果的模数来确定使用哪个编号的分区。换句话,对于一个表达式“expr”,将要保存记录的分区编号为N ,其中“N = MOD(expr, num)”。
比如上面的storeid 为10;那么 N=MOD(10,4) ;N是等于2的,那么这条记录就存储在p2的分区里面。
如果插入一个表达式列值为'2005-09-15′的记录到表中,那么保存该条记录的分区确定如下:MOD(YEAR('2005-09-01′),4)  =  MOD(2005,4)  =  1 ;就存储在p1分区里面了。
“MySQL 5.1 还支持一个被称为“linear hashing(线性哈希功能)”的变量,它使用一个更加复杂的算法来确定新行插入到已经分区了的表中的位置。
线性哈希分区和常规哈希分区在语法上的唯一区别在于,在“PARTITION BY” 子句中添加“LINEAR”关键字;线性哈希功能使用的一个线性的2的幂(powers-of-two)运算法则
按照线性哈希分区的优点在于增加、删除、合并和拆分分区将变得更加快捷,有利于处理含有极其大量(1000GB)数据的表。
它的缺点在于,与使用常规HASH分区得到的数据分布相比,各个分区间数据的分布不大可能均衡。”
KEY 分区:类似于按HASH分区,区别在于KEY分区只支持计算一列或多列,且MySQL 服务器提供其自身的哈希函数。必须有一列或多列包含整数值。
复制代码代码如下:

DROP TABLE IF EXISTS `p_key`;
CREATE TABLE `p_key` (
`id` int(10) NOT NULL AUTO_INCREMENT,
`keyname` char(20) DEFAULT NULL,
`keyval` varchar(1000) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=12 DEFAULT CHARSET=utf8
/*!50100 PARTITION BY KEY (id)
PARTITIONS 4 */;

按照KEY进行分区类似于按照HASH分区,除了HASH分区使用的用户定义的表达式,而KEY分区的 哈希函数是由MySQL 服务器提供。MySQL 簇(Cluster)使用函数MD5()来实现KEY分区;对于使用其他存储引擎的表,服务器使用其自己内部的 哈希函数,这些函数是基于与PASSWORD()一样的运算法则。
“CREATE TABLE … PARTITION BY KEY”的语法规则类似于创建一个通过HASH分区的表的规则。它们唯一的区别在于使用的关键字是KEY而不是HASH,并且KEY分区只采用一个或多个列名的一个列表。
与hash的区别就是,hash使用用户定义的表达式如YEAR(time) ;而key分区则是由mysql服务器提供的。同样KEY也是可以使用linear线性key的,与hash linear是相同的算法。
子分区:是分区表中每个分区的再次分割。
复制代码代码如下:

DROP TABLE IF EXISTS `p_subpartition`;
CREATE TABLE `p_subpartition` (
`id` int(10) DEFAULT NULL,
`title` char(255) NOT NULL,
`createtime` date NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8
/*!50100
PARTITION BY RANGE (YEAR(createtime))
SUBPARTITION BY HASH (MONTH(createtime))
(PARTITION p0 VALUES LESS THAN (2012)
(SUBPARTITION s1 ENGINE = MyISAM,
SUBPARTITION s2 ENGINE = MyISAM),
PARTITION p1 VALUES LESS THAN (2013)
(SUBPARTITION s3 ENGINE = MyISAM,
SUBPARTITION s4 ENGINE = MyISAM),
PARTITION p2 VALUES LESS THAN MAXVALUE
(SUBPARTITION s5 ENGINE = MyISAM,
SUBPARTITION s6 ENGINE = MyISAM)) */;

可以看到p_subpartition有三个分区p0,p1,p2;而这三个分区每一个又进一步分为2个分区。那么整个表都就分为6个小分区;

可以看到代表p_sobpartitionp0.myd的文件消失了,取代的是p_subpartition#p#p0#sp#s1.myd
在MySQL 5.1中,对于已经通过RANGE或LIST分区了的表再进行子分区是可能的。
子分区是分区表中每个分区的再次分割,子分区既可以使用HASH希分区,也可以使用KEY分区。这 也被称为复合分区(composite partitioning)。
1,如果一个分区中创建了子分区,其他分区也要有子分区
2,如果创建了了分区,每个分区中的子分区数必有相同
3,同一分区内的子分区,名字不相同,不同分区内的子分区名子可以相同(5.1.50不适用)
分区注意点
1、重新分区时,如果原分区里面存在maxvalue则新的分区里面也必须包含maxvalue否则就错误。
alter table p_range2x
reorganize partition p1,p2
into (partition p0 values less than (5), partition p1 values less than maxvalue);
[Err] 1520 – Reorganize of range partitions cannot change total ranges except for last partition where it can extend the range
2、分区删除时,数据也同样会被删除
alter table p_range drop partition p0;
3、如果range分区列表里面没有maxvalue则如有新数据大于现在分区range数据值那么这个数据是无法写入到数据库表的。
4、修改表名不需要 删除分区后在进行更改,修改表名后分区存储myd myi对应也会自动更改。
如果希望从所有分区删除所有的数据,但是又保留表的定义和表的分区模式,使用TRUNCATE TABLE命令。(请参见13.2.9节,“TRUNCATE语法”)。
如果希望改变表的分区而又不丢失数据,使用“ALTER TABLE … REORGANIZE PARTITION”语句。参见下面的内容,或者在13.1.2节,“ALTER TABLE语法” 中参考关于REORGANIZE PARTITION的信息。
5、对表进行分区时,不论采用哪种分区方式如果表中存在主键那么主键必须在分区列中。表分区的局限性。
6、list方式分区没有类似于range那种 less than maxvalue的写法,也就是说list分区表的所有数据都必须在分区字段的值列表集合中。
7、在MySQL 5.1版中,同一个分区表的所有分区必须使用同一个存储引擎;例如,不能对一个分区使用MyISAM,而对另一个使用InnoDB。
8、分区的名字是不区分大小写的,myp1与MYp1是相同的。
分区的管理
range与list分区的改变动作不能适用于hash与key方式的分区。删除与添加动作是都能使用的。
以下面的例子
复制代码代码如下:

DROP TABLE IF EXISTS `p_list`;
CREATE TABLE `p_list` (
`id` int(10) NOT NULL AUTO_INCREMENT,
`typeid` mediumint(10) NOT NULL DEFAULT '0',
`typename` char(20) DEFAULT NULL,
PRIMARY KEY (`id`,`typeid`)
) ENGINE=MyISAM AUTO_INCREMENT=9 DEFAULT CHARSET=utf8
/*!50100 PARTITION BY LIST (typeid)
(PARTITION p0 VALUES IN (1,2,3,4) ENGINE = MyISAM,
PARTITION p1 VALUES IN (5,6,7,8) ENGINE = MyISAM) */;


range与list分区的管理
删除分区
ALTER TABLE tr DROP PARTITION p1;
需要注意的是删除分区后,该分区的所有数据都没有了。同时删除后存在一个重大影响也就是typeid为5,6,7,8的记录是不能写入到该表了的!
清空数据
如果想要保留表结构与分区结构可以使用 TRUNCATE TABLE 清空表
更改分区保留数据
ALTER TABLE tbl_name REORGANIZE PARTITION partition_list INTO (partition_definitions);如果想保留数据进行分区的更改
ALTER TABLE p_list REORGANIZE PARTITION p0 INTO (
 PARTITION s0 VALUES IN(1,2),
 PARTITION s1 VALUES IN(3,4),
);这样就能进行分区的合并了,那怎么进行拆分呢
ALTER TABLE p_list REORGANIZE PARTITION s0,s1 INTO (
 PARTITION p0 VALUES IN(1,2,3,4),
); 使用 REORGANIZE PARTITION进行数据的合并与拆分,数据是没有丢失的。
在使用REORGANIZE进行重新分区时,需要注意几点:
1、用来确定新分区模式的PARTITION子句使用与用在CREATE TABLE中确定分区模式的PARTITION子句相同的规则。(partition 分区子句必须与创建原分区时的规则相同)
2、partition_definitions 列表中分区的合集应该与在partition_list 中命名分区的合集占有相同的区间或值集合。 (不管是合并还是拆分,s0,s1到p0;p0到s0,s1 里面的区间或者值都必须相同)
3、对于按照RANGE分区的表,只能重新组织相邻的分区;不能跳过RANGE分区。(比如按range年份 p0 1990,p1 2000 ,p2 2013三个分区;在合并时partition p0,p2 into()
   这样是不行的,因为这两个分区不是相邻的分区;)
4、不能使用REORGANIZE PARTITION来改变表的分区类型;也就是说,例如,不能把RANGE分区变为HASH分区,反之亦然。也不能使用该命令来改变分区表达式或列。
增加分区
ALTER TABLE p_list ADD PARTITION (PARTITION p2 VALUES IN (9, 10, 11));
但是不能使用
ALTER TABLE p_list ADD PARTITION (PARTITION p2 VALUES IN (9, 14));
这样mysql 会产生错误1465 (HY000): 在LIST分区中,同一个常数的多次定义
hash与key分区的管理在改变分区设置方面,按照HASH分区或KEY分区的表彼此非常相似,但是它们又与按照RANGE或LIST分区的表在很多方面有差别。
关于添加和删除按照RANGE或LIST进行分区的表的分区
不能使用与从按照RANGE或LIST分区的表中删除分区相同的方式,来从HASH或KEY分区的表中删除分区。但是,可以使用“ALTER TABLE ... COALESCE PARTITION”命令来合并HASH或KEY分区。
查看源代码打印帮助1 DROP TABLE IF EXISTS `p_hash`;  2    3 CREATE TABLE `p_hash` (  4 `id` int(10) NOT NULL AUTO_INCREMENT,  5 `storeid` mediumint(10) NOT NULL DEFAULT '0',  6 `storename` char(255) DEFAULT NULL,  7 PRIMARY KEY (`id`,`storeid`)  8 ) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8  9 /*!50100 PARTITION BY HASH (storeid)  10 PARTITIONS 4 */; 
如p_hash的分区数为4个;
要减少分区数为2个
ALTER TABLE p_hash COALESCE PARTITION 2;
对于按照HASH,KEY,LINEAR HASH,或LINEAR KEY分区的表, COALESCE能起到同样的作用。COALESCE不能用来增加分区的数量,如果你尝试这么做,结果会出现类似于下面的错误:
mysql> ALTER TABLE clients COALESCE PARTITION 18;
错误1478 (HY000): 不能移动所有分区,使用DROP TABLE代替要增加顾客表的分区数量从12到18,使用“ALTER TABLE … ADD PARTITION”,具体如下:
ALTER TABLE clients ADD PARTITION PARTITIONS 18;注释:“ALTER TABLE … REORGANIZE PARTITION”不能用于按照HASH或HASH分区的表。
分区维护
重建分区
这和先删除保存在分区中的所有记录,然后重新插入它们,具有同样的效果。它可用于整理分区碎片。
ALTER TABLE t1 REBUILD PARTITION (p0, p1);
优化分区如果从分区中删除了大量的行,或者对一个带有可变长度的行(也就是说,有VARCHAR,BLOB,或TEXT类型的列)作了许多修改,
可以使用“ALTER TABLE … OPTIMIZE PARTITION”来收回没有使用的空间,并整理分区数据文件的碎片。
ALTER TABLE t1 OPTIMIZE PARTITION (p0, p1);
分析分区
读取并保存分区的键分布
ALTER TABLE t1 ANALYZE PARTITION (p3);
修补分区: 修补被破坏的分区。
ALTER TABLE t1 REPAIR PARTITION (p0,p1);
检查分区
可以使用几乎与对非分区表使用CHECK TABLE 相同的方式检查分区。
ALTER TABLE trb3 CHECK PARTITION (p1);
这个命令可以告诉你表t1的分区p1中的数据或索引是否已经被破坏。如果发生了这种情况,使用“ALTER TABLE ... REPAIR PARTITION”来修补该分区。获取分区信息
在mysql服务器信息数据库里面的partitions存放着服务器所有表的分区信息。
复制代码代码如下:

explain partitions命令
explain partitions select * from p_hash
+----+-------------+--------+-------------+------+---------------+------+---------+------+------+-------+
| id | select_type | table  | partitions  | type | possible_keys | key  | key_len | ref  | rows | Extra |
+----+-------------+--------+-------------+------+---------------+------+---------+------+------+-------+
|  1 | SIMPLE    | p_hash | p0,p1,p2,p3 | ALL  | NULL      | NULL | NULL   | NULL |  10 |    |
+----+-------------+--------+-------------+------+---------------+------+---------+------+------+-------+

-- 获取到p_list表的分区详细信息。
select * from information_schema.`PARTITIONS` where TABLE_NAME = 'p_list';
-- 分区的创建信息
show create table p_list;

DROP 

TABLE 

IF 

EXISTS 

`p_range`; 

CREATE 

TABLE 

`p_range` 

`id` 

int(10) 

NOT 

NULL 

AUTO_INCREMENT, 

`name` 

char(20) 

NOT 

NULL, 

PRIMARY 

KEY 

(`id`) 

ENGINE=MyISAM 

AUTO_INCREMENT=9 

DEFAULT 

CHARSET=utf8 

/*!50100 

PARTITION 

BY 

RANGE 

(id) 

(PARTITION 

p0 

VALUES 

LESS 

THAN 

(8) 

ENGINE 

MyISAM)








































 







声明:本文内容由网友自发贡献,不代表【wpsshop博客】立场,版权归原作者所有,本站不承担相应法律责任。如您发现有侵权的内容,请联系我们。转载请注明出处:https://www.wpsshop.cn/w/繁依Fanyi0/article/detail/623843
推荐阅读
相关标签
  

闽ICP备14008679号