MYSQL 表分区Word下载.docx
- 文档编号:22058541
- 上传时间:2023-02-02
- 格式:DOCX
- 页数:33
- 大小:31.79KB
MYSQL 表分区Word下载.docx
《MYSQL 表分区Word下载.docx》由会员分享,可在线阅读,更多相关《MYSQL 表分区Word下载.docx(33页珍藏版)》请在冰豆网上搜索。
但是,必须记住[STORAGE]ENGINE(和其他的表选项)必须列在用在CREATETABLE语句中的其他任何分区选项之前。
分区的一些优点包括:
·
与单个磁盘或文件系统分区相比,可以存储更多的数据。
对于那些已经失去保存意义的数据,通常可以通过删除与那些数据有关的分区,很容易地删除那些数据。
相反地,在某些情况下,添加新数据的过程又可以通过为那些新数据专门增加一个新的分区,来很方便地实现。
一些查询可以得到极大的优化,这主要是借助于满足一个给定WHERE
语句的数据可以只保存在一个或多个分区内,这样在查找时就不用查找其他剩余的分区。
因为分区可以在创建了分区表后进行修改,所以在第一次配置分区方案时还不曾这么做时,可以重新组织数据,来提高那些常用查询的效率。
涉及到例如SUM()
和
COUNT()这样聚合函数的查询,可以很容易地进行并行处理。
这种查询的一个简单例子如“SELECTsalesperson_id,COUNT(orders)asorder_totalFROMsalesGROUPBYsalesperson_id;
”。
通过“并行”,
这意味着该查询可以在每个分区上同时进行,最终结果只需通过总计所有分区得到的结果。
通过跨多个磁盘来分散数据查询,来获得更大的查询吞吐量。
2:
分区类型:
RANGE
分区:
基于属于一个给定连续区间的列值,把多行分配给分区。
LIST
类似于按RANGE分区,区别在于LIST分区是基于列值匹配一个离散值集合中的某个值来进行选择。
HASH分区:
基于用户定义的表达式的返回值来进行选择的分区,该表达式使用将要插入到表中的这些行的列值进行计算。
这个函数可以包含MySQL
中有效的、产生非负整数值的任何表达式。
KEY
类似于按HASH分区,区别在于KEY分区只支持计算一列或多列,且MySQL
服务器提供其自身的哈希函数。
必须有一列或多列包含整数值。
无论使用何种类型的分区,分区总是在创建时就自动的顺序编号,且从0开始记录,记住这一点非常重要。
当有一新行插入到一个分区表中时,就是使用这些分区编号来识别正确的分区。
例如,如果你的表使用4个分区,那么这些分区就编号为0,
1,
2,
和3。
对于RANGE和LIST分区类型,确认每个分区编号都定义了一个分区,很有必要。
对HASH分区,使用的用户函数必须返回一个大于0的整数值。
对于KEY分区,这个问题通过MySQL服务器内部使用的哈希函数自动进行处理。
分区的名字基本上遵循其他MySQL
标识符应当遵循的原则,例如用于表和数据库名字的标识符。
但是应当注意,分区的名字是不区分大小写的。
2.1RANGE分区
按照RANGE分区的表是通过如下一种方式进行分区的,每个分区包含那些分区表达式的值位于一个给定的连续区间内的行。
这些区间要连续且不能相互重叠,使用VALUESLESSTHAN操作符来进行定义。
例如:
你可能决定通过添加一个PARTITIONBYRANGE子句把这个表分割成4个区间
CREATETABLEemployees(
idINTNOTNULL,
fnameVARCHAR(30),
lnameVARCHAR(30),
hiredDATENOTNULLDEFAULT'
1970-01-01'
separatedDATENOTNULLDEFAULT'
9999-12-31'
job_codeINTNOTNULL,
store_idINTNOTNULL
)
PARTITIONBYRANGE(store_id)(
PARTITIONp0VALUESLESSTHAN(6),
PARTITIONp1VALUESLESSTHAN(11),
PARTITIONp2VALUESLESSTHAN(16),
PARTITIONp3VALUESLESSTHAN(21)
);
按照这种分区方案,在商店1到5工作的雇员相对应的所有行被保存在分区P0中,商店6到10的雇员保存在P1中,依次类推。
注意,每个分区都是按顺序进行定义,从最低到最高。
这是PARTITIONBYRANGE
语法的要求.
如果有store_id大于21的ID,上述的语法创建的分区会在服务器报错,解决办法是:
PARTITIONp3VALUESLESSTHANMAXVALUE
MAXVALUE
表示最大的可能的整数值。
当超过16的值都放p3分区中。
2.2LIST分区
MySQL中的LIST分区在很多方面类似于RANGE分区。
和按照RANGE分区一样,每个分区必须明确定义。
它们的主要区别在于,LIST分区中每个分区的定义和选择是基于某列的值从属于一个值列表集中的一个值,而RANGE分区是从属于一个连续区间值的集合。
LIST分区通过使用“PARTITIONBYLIST(expr)”来实现,其中“expr”
是某列值或一个基于某个列值、并返回一个整数值的表达式,然后通过“VALUESIN(value_list)”的方式来定义每个分区,其中“value_list”是一个通过逗号分隔的整数列表。
假定有20个音像店,分布在4个有经销权的地区,如下表所示:
地区
商店ID
号
北区
3,5,6,9,17
东区
1,2,10,11,19,20
西区
4,12,13,14,18
中心区
7,8,15,16
job_codeINT,
store_idINT
PARTITIONBYLIST(store_id)
PARTITIONpNorthVALUESIN(3,5,6,9,17),
PARTITIONpEastVALUESIN(1,2,10,11,19,20),
PARTITIONpWestVALUESIN(4,12,13,14,18),
PARTITIONpCentralVALUESIN(7,8,15,16)
这使得在表中增加或删除指定地区的雇员记录变得容易起来。
例如,假定西区的所有音像店都卖给了其他公司。
那么与在西区音像店工作雇员相关的所有记录(行)可以使用查询“ALTERTABLEemployeesDROPPARTITIONpWest;
”来进行删除,它与具有同样作用的DELETE
(删除)查询“DELETE
query
DELETEFROMemployeesWHEREstore_idIN(4,12,13,14,18);
”比起来,要有效得多。
2.3HASH分区
ASH分区主要用来确保数据在预先确定数目的分区中平均分布。
在RANGE和LIST分区中,必须明确指定一个给定的列值或列值集合应该保存在哪个分区中;
而在HASH分区中,MySQL
自动完成这些工作,你所要做的只是基于将要被哈希的列值指定一个列值或表达式,以及指定被分区的表将要被分割成的分区数量。
要使用HASH分区来分割一个表,要在CREATETABLE
语句上添加一个“PARTITIONBYHASH(expr)”子句,其中“expr”是一个返回一个整数的表达式。
它可以仅仅是字段类型为MySQL
整型的一列的名字。
此外,你很可能需要在后面再添加一个“PARTITIONS
num”子句,其中num
是一个非负的整数,它表示表将要被分割成分区的数量
store_idINT
PARTITIONBYHASH(store_id)
PARTITIONS4;
“expr”还可以是一个返回一个整数的SQL表达式。
例如,也许你想基于雇用雇员的年份来进行分区。
这可以通过下面的语句来实现:
PARTITIONBYHASH(YEAR(hired))
“expr”还可以是MySQL
中有效的任何函数或其他表达式,只要它们返回一个既非常数、也非随机数的整数。
(换句话说,它既是变化的但又是确定的)。
但是应当记住,每当插入或更新(或者可能删除)一行,这个表达式都要计算一次;
这意味着非常复杂的表达式可能会引起性能问题,尤其是在执行同时影响大量行的运算(例如批量插入)的时候。
最有效率的哈希函数是只对单个表列进行计算,并且它的值随列值进行一致地增大或减小,因为这考虑了在分区范围上的“修剪”。
也就是说,表达式值和它所基于的列的值变化越接近,MySQL就可以越有效地使用该表达式来进行HASH分区。
2.4LINEARHASH分区
MySQL还支持线性哈希功能,它与常规哈希的区别在于,线性哈希功能使用的一个线性的2的幂(powers-of-two)运算法则,而常规哈希使用的是求哈希函数值的模数。
线性哈希分区和常规哈希分区在语法上的唯一区别在于,在“PARTITIONBY”
子句中添加“LINEAR”关键字,如下面所示:
PARTITIONBYLINEARHASH(YEAR(hired))
2.5KEY分区
按照KEY进行分区类似于按照HASH分区,除了HASH分区使用的用户定义的表达式,而KEY分区的哈希函数是由MySQL
服务器提供。
簇(Cluster)使用函数MD5()来实现KEY分区;
对于使用其他存储引擎的表,服务器使用其自己内部的哈希函数,这些函数是基于与PASSWORD()一样的运算法则。
“CREATETABLE...PARTITIONBYKEY”的语法规则类似于创建一个通过HASH分区的表的规则。
它们唯一的区别在于使用的关键字是KEY而不是HASH,并且KEY分区只采用一个或多个列名的一个列表。
通过线性KEY分割一个表也是可能的。
下面是一个简单的例子:
CREATETABLEtk(
col1INTNOTNULL,
col2CHAR(5),
col3DATE
)
PARTITIONBYLINEARKEY(col1)
PARTITIONS3;
在KEY分区中使用关键字LINEAR和在HASH分区中使用具有同样的作用,分区的编号是通过2的幂(powers-of-two)算法得到,而不是通过模数算法。
2.6子分区
子分区是分区表中每个分区的再次分割。
例如,考虑下面的CREATETABLE
语句:
CREATETABLEts(idINT,purchasedDATE)
PARTITIONBYRANGE(YEAR(purchased))
SUBPARTITIONBYHASH(TO_DAYS(purchased))
SUBPARTITIONS2
(
PARTITIONp0VALUESLESSTHAN(1990),
PARTITIONp1VALUESLESSTHAN(2000),
PARTITIONp2VALUESLESSTHANMAXVALUE
);
表ts
有3个RANGE分区。
这3个分区中的每一个分区——p0,
p1,
p2
——又被进一步分成了2个子分区。
实际上,整个表被分成了3*2=6个分区。
但是,由于PARTITIONBYRANGE子句的作用,这些分区的头2个只保存“purchased”列中值小于1990的那些记录。
在MySQL5.1中,对于已经通过RANGE或LIST分区了的表再进行子分区是可能的。
子分区既可以使用HASH希分区,也可以使用KEY分区。
这也被称为复合分区(compositepartitioning)。
为了对个别的子分区指定选项,使用SUBPARTITION
子句来明确定义子分区也是可能的。
例如,创建在前面例子中给出的同一个表的、一个更加详细的方式如下:
PARTITIONp0VALUESLESSTHAN(1990)
SUBPARTITIONs0,
SUBPARTITIONs1
),
PARTITIONp1VALUESLESSTHAN(2000)
SUBPARTITIONs2,
SUBPARTITIONs3
SUBPARTITIONs4,
SUBPARTITIONs5
)
);
几点要注意的语法项:
每个分区必须有相同数量的子分区。
如果在一个分区表上的任何分区上使用SUBPARTITION
来明确定义任何子分区,那么就必须定义所有的子分区。
换句话说,下面的语句将执行失败:
CREATETABLEts(idINT,purchasedDATE)
PARTITIONBYRANGE(YEAR(purchased))
SUBPARTITIONBYHASH(TO_DAYS(purchased))
(
PARTITIONp0VALUESLESSTHAN(1990)
SUBPARTITIONs0,
SUBPARTITIONs1
),
PARTITIONp1VALUESLESSTHAN(2000),
PARTITIONp2VALUESLESSTHANMAXVALUE
SUBPARTITIONs2,
即便这个语句包含了一个SUBPARTITIONS2子句,但是它仍然会执行失败。
每个SUBPARTITION
子句必须包括
(至少)子分区的一个名字。
否则,你可能要对该子分区设置任何你所需要的选项,或者允许该子分区对那些选项采用其默认的设置。
在每个分区内,子分区的名字必须是唯一的,但是在整个表中,没有必要保持唯一。
例如,下面的CREATETABLE
语句是有效的:
PARTITIONp1VALUESLESSTHAN(2000)
子分区可以用于特别大的表,在多个磁盘间分配数据和索引。
假设有6个磁盘,分别为/disk0,
/disk1,
/disk2等。
现在考虑下面的例子:
SUBPARTITIONs0
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- MYSQL 表分区 分区