mysql分表分区的区别与联系.docx
- 文档编号:25752769
- 上传时间:2023-06-12
- 格式:DOCX
- 页数:24
- 大小:25.98KB
mysql分表分区的区别与联系.docx
《mysql分表分区的区别与联系.docx》由会员分享,可在线阅读,更多相关《mysql分表分区的区别与联系.docx(24页珍藏版)》请在冰豆网上搜索。
mysql分表分区的区别与联系
mysql分表,分区的区别与联系
一,什么是mysql分表,分区
什么是分表,从表面意思上看呢,就是把一张表分成N多个小表,具体请看mysql分表的3种方法
什么是分区,分区呢就是把一张表的数据分成N多个区块,这些区块可以在同一个磁盘上,也可以在不同的磁盘上,具体请参考mysql分区功能详细介绍,以及实例
二,mysql分表和分区有什么区别呢
1,实现方式上
a),mysql的分表是真正的分表,一张表分成很多表后,每一个小表都是完正的一张表,都对应三个文件,一个.MYD数据文件,.MYI索引文件,.frm表结构文件。
1.[root@BlackGhost test]# ls |grep user
2.alluser.MRG
3.alluser.frm
4.user1.MYD
5.user1.MYI
6.user1.frm
7.user2.MYD
8.user2.MYI
9.user2.frm
[root@BlackGhosttest]#ls|grepuser
alluser.MRG
alluser.frm
user1.MYD
user1.MYI
user1.frm
user2.MYD
user2.MYI
user2.frm
简单说明一下,上面的分表呢是利用了merge存储引擎(分表的一种),alluser是总表,下面有二个分表,user1,user2。
他们二个都是独立的表,取数据的时候,我们可以通过总表来取。
这里总表是没有.MYD,.MYI这二个文件的,也就是说,总表他不是一张表,没有数据,数据都放在分表里面。
我们来看看.MRG到底是什么东西
1.[root@BlackGhost test]# cat alluser.MRG |more
2.user1
3.user2
4.#INSERT_METHOD=LAST
[root@BlackGhosttest]#catalluser.MRG|more
user1
user2
#INSERT_METHOD=LAST
从上面我们可以看出,alluser.MRG里面就存了一些分表的关系,以及插入数据的方式。
可以把总表理解成一个外壳,或者是联接池。
b),分区不一样,一张大表进行分区后,他还是一张表,不会变成二张表,但是他存放数据的区块变多了。
1.[root@BlackGhost test]# ls |grep aa
2.aa#P#p1.MYD
3.aa#P#p1.MYI
4.aa#P#p3.MYD
5.aa#P#p3.MYI
6.aa.frm
7.aa.par
[root@BlackGhosttest]#ls|grepaa
aa#P#p1.MYD
aa#P#p1.MYI
aa#P#p3.MYD
aa#P#p3.MYI
aa.frm
aa.par
从上面我们可以看出,aa这张表,分为二个区,p1和p3,本来是三个区,被我删了一个区。
我们都知道一张表对应三个文件.MYD,.MYI,.frm。
分区呢根据一定的规则把数据文件和索引文件进行了分割,还多出了一个.par文件,打开.par文件后你可以看出他记录了,这张表的分区信息,根分表中的.MRG有点像。
分区后,还是一张,而不是多张表。
2,数据处理上
a),分表后,数据都是存放在分表里,总表只是一个外壳,存取数据发生在一个一个的分表里面。
看下面的例子:
select*fromalluserwhereid='12'表面上看,是对表alluser进行操作的,其实不是的。
是对alluser里面的分表进行了操作。
b),分区呢,不存在分表的概念,分区只不过把存放数据的文件分成了许多小块,分区后的表呢,还是一张表。
数据处理还是由自己来完成。
3,提高性能上
a),分表后,单表的并发能力提高了,磁盘I/O性能也提高了。
并发能力为什么提高了呢,因为查寻一次所花的时间变短了,如果出现高并发的话,总表可以根据不同的查询,将并发压力分到不同的小表里面。
磁盘I/O性能怎么搞高了呢,本来一个非常大的.MYD文件现在也分摊到各个小表的.MYD中去了。
b),mysql提出了分区的概念,我觉得就想突破磁盘I/O瓶颈,想提高磁盘的读写能力,来增加mysql性能。
在这一点上,分区和分表的测重点不同,分表重点是存取数据时,如何提高mysql并发能力上;而分区呢,如何突破磁盘的读写能力,从而达到提高mysql性能的目的。
4),实现的难易度上
a),分表的方法有很多,用merge来分表,是最简单的一种方式。
这种方式根分区难易度差不多,并且对程序代码来说可以做到透明的。
如果是用其他分表方式就比分区麻烦了。
b),分区实现是比较简单的,建立分区表,根建平常的表没什么区别,并且对开代码端来说是透明的。
三,mysql分表和分区有什么联系呢
1,都能提高mysql的性高,在高并发状态下都有一个良好的表面。
2,分表和分区不矛盾,可以相互配合的,对于那些大访问量,并且表数据比较多的表,我们可以采取分表和分区结合的方式(如果merge这种分表方式,不能和分区配合的话,可以用其他的分表试),访问量不大,但是表数据很多的表,我们可以采取分区的方式等。
Mysql表分区
2010-10-1915:
51:
06| 分类:
mysql| 标签:
|字号大中小 订阅
一、什么是表分区
通俗地讲表分区是将一大表,根据条件分割成若干个小表。
mysql5.1开始支持数据表分区了。
如:
某用户表的记录超过了600万条,那么就可以根据入库日期将表分区,也可以根据所在地将表分区。
当然也可根据其他的条件分区。
二、为什么要对表进行分区
为了改善大型表以及具有各种访问模式的表的可伸缩性,可管理性和提高数据库效率。
分区的一些优点包括:
1)、与单个磁盘或文件系统分区相比,可以存储更多的数据。
2)、对于那些已经失去保存意义的数据,通常可以通过删除与那些数据有关的分区,很容易地删除那些数据。
相反地,在某些情况下,添加新数据的过程又可以通过为那些新数据专门增加一个新的分区,来很方便地实现。
通常和分区有关的其他优点包括下面列出的这些。
MySQL分区中的这些功能目前还没有实现,但是在我们的优先级列表中,具有高的优先级;我们希望在5.1的生产版本中,能包括这些功能。
3)、一些查询可以得到极大的优化,这主要是借助于满足一个给定WHERE语句的数据可以只保存在一个或多个分区内,这样在查找时就不用查找其他剩余的分区。
因为分区可以在创建了分区表后进行修改,所以在第一次配置分区方案时还不曾这么做时,可以重新组织数据,来提高那些常用查询的效率。
4)、涉及到例如SUM()和COUNT()这样聚合函数的查询,可以很容易地进行并行处理。
这种查询的一个简单例子如“SELECTsalesperson_id,COUNT(orders)asorder_totalFROMsalesGROUPBYsalesperson_id;”。
通过“并行”,这意味着该查询可以在每个分区上同时进行,最终结果只需通过总计所有分区得到的结果。
5)、通过跨多个磁盘来分散数据查询,来获得更大的查询吞吐量。
三、分区类型
·RANGE分区:
基于属于一个给定连续区间的列值,把多行分配给分区。
·LIST分区:
类似于按RANGE分区,区别在于LIST分区是基于列值匹配一个离散值集合中的某个值来进行选择。
·HASH分区:
基于用户定义的表达式的返回值来进行选择的分区,该表达式使用将要插入到表中的这些行的列值进行计算。
这个函数可以包含MySQL中有效的、产生非负整数值的任何表达式。
·KEY分区:
类似于按HASH分区,区别在于KEY分区只支持计算一列或多列,且MySQL服务器提供其自身的哈希函数。
必须有一列或多列包含整数值。
RANGE分区
基于属于一个给定连续区间的列值,把多行分配给分区。
这些区间要连续且不能相互重叠,使用VALUESLESSTHAN操作符来进行定义。
以下是实例。
Sql代码
CREATE TABLE employees (
id INT NOT NULL,
fname VARCHAR(30),
lname VARCHAR(30),
hired DATE NOT NULL DEFAULT '1970-01-01',
separated DATE NOT NULL DEFAULT '9999-12-31',
job_code INT NOT NULL,
store_id INT NOT NULL
)
partition BY RANGE (store_id) (
partition p0 VALUES LESS THAN (6),
partition p1 VALUES LESS THAN (11),
partition p2 VALUES LESS THAN (16),
partition p3 VALUES LESS THAN (21)
);
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语法的要求;在这点上,它类似于C或Java中的“switch...case”语句。
对于包含数据(72,'Michael','Widenius','1998-06-25',NULL,13)的一个新行,可以很容易地确定它将插入到p2分区中,但是如果增加了一个编号为第21的商店,将会发生什么呢?
在这种方案下,由于没有规则把store_id大于20的商店包含在内,服务器将不知道把该行保存在何处,将会导致错误。
要避免这种错误,可以通过在CREATETABLE语句中使用一个“catchall”VALUESLESSTHAN子句,该子句提供给所有大于明确指定的最高值的值:
Sql代码
CREATE TABLE employees (
id INT NOT NULL,
fname VARCHAR(30),
lname VARCHAR(30),
hired DATE NOT NULL DEFAULT '1970-01-01',
separated DATE NOT NULL DEFAULT '9999-12-31',
job_code INT NOT NULL,
store_id INT NOT NULL
)
PARTITION BY RANGE (store_id) (
PARTITION p0 VALUES LESS THAN (6),
PARTITION p1 VALUES LESS THAN (11),
PARTITION p2 VALUES LESS THAN (16),
PARTITION p3 VALUES LESS THAN MAXVALUE
);
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),PARTITIONp3VALUESLESSTHANMAXVALUE);
MAXVALUE表示最大的可能的整数值。
现在,store_id列值大于或等于16(定义了的最高值)的所有行都将保存在分区p3中。
在将来的某个时候,当商店数已经增长到25,30,或更多,可以使用ALTERTABLE语句为商店21-25,26-30,等等增加新的分区。
在几乎一样的结构中,你还可以基于雇员的工作代码来分割表,也就是说,基于job_code列值的连续区间。
例如——假定2位数字的工作代码用来表示普通(店内的)工人,三个数字代码表示办公室和支持人员,四个数字代码表示管理层,你可以使用下面的语句创建该分区表:
Sql代码
CREATE TABLE employees (
id INT NOT NULL,
fname VARCHAR(30),
lname VARCHAR(30),
hired DATE NOT NULL DEFAULT '1970-01-01',
separated DATE NOT NULL DEFAULT '9999-12-31',
job_code INT NOT NULL,
store_id INT NOT NULL
)
PARTITION BY RANGE (job_code) (
PARTITION p0 VALUES LESS THAN (100),
PARTITION p1 VALUES LESS THAN (1000),
PARTITION p2 VALUES LESS THAN (10000)
);
CREATETABLEemployees(idINTNOTNULL,fnameVARCHAR(30),lnameVARCHAR(30),hiredDATENOTNULLDEFAULT'1970-01-01',separatedDATENOTNULLDEFAULT'9999-12-31',job_codeINTNOTNULL,store_idINTNOTNULL)PARTITIONBYRANGE(job_code)(PARTITIONp0VALUESLESSTHAN(100),PARTITIONp1VALUESLESSTHAN(1000),PARTITIONp2VALUESLESSTHAN(10000));
在这个例子中,店内工人相关的所有行将保存在分区p0中,办公室和支持人员相关的所有行保存在分区p1中,管理层相关的所有行保存在分区p2中。
在VALUESLESSTHAN子句中使用一个表达式也是可能的。
这里最值得注意的限制是MySQL必须能够计算表达式的返回值作为LESSTHAN(<)比较的一部分;因此,表达式的值不能为NULL。
由于这个原因,雇员表的hired,separated,job_code,和store_id列已经被定义为非空(NOTNULL)。
除了可以根据商店编号分割表数据外,你还可以使用一个基于两个DATE(日期)中的一个的表达式来分割表数据。
例如,假定你想基于每个雇员离开公司的年份来分割表,也就是说,YEAR(separated)的值。
实现这种分区模式的CREATETABLE语句的一个例子如下所示:
Sql代码
CREATE TABLE employees (
id INT NOT NULL,
fname VARCHAR(30),
lname VARCHAR(30),
hired DATE NOT NULL DEFAULT '1970-01-01',
separated DATE NOT NULL DEFAULT '9999-12-31',
job_code INT,
store_id INT
)
PARTITION BY RANGE (YEAR(separated)) (
PARTITION p0 VALUES LESS THAN (1991),
PARTITION p1 VALUES LESS THAN (1996),
PARTITION p2 VALUES LESS THAN (2001),
PARTITION p3 VALUES LESS THAN MAXVALUE
);
CREATETABLEemployees(idINTNOTNULL,fnameVARCHAR(30),lnameVARCHAR(30),hiredDATENOTNULLDEFAULT'1970-01-01',separatedDATENOTNULLDEFAULT'9999-12-31',job_codeINT,store_idINT)PARTITIONBYRANGE(YEAR(separated))(PARTITIONp0VALUESLESSTHAN(1991),PARTITIONp1VALUESLESSTHAN(1996),PARTITIONp2VALUESLESSTHAN(2001),PARTITIONp3VALUESLESSTHANMAXVALUE);
在这个方案中,在1991年前雇佣的所有雇员的记录保存在分区p0中,1991年到1995年期间雇佣的所有雇员的记录保存在分区p1中,1996年到2000年期间雇佣的所有雇员的记录保存在分区p2中,2000年后雇佣的所有工人的信息保存在p3中。
RANGE分区在如下场合特别有用:
1)、当需要删除一个分区上的“旧的”数据时,只删除分区即可。
如果你使用上面最近的那个例子给出的分区方案,你只需简单地使用“ALTERTABLEemployeesDROPPARTITIONp0;”来删除所有在1991年前就已经停止工作的雇员相对应的所有行。
对于有大量行的表,这比运行一个如“DELETEFROMemployeesWHEREYEAR(separated)<=1990;”这样的一个DELETE查询要有效得多。
2)、想要使用一个包含有日期或时间值,或包含有从一些其他级数开始增长的值的列。
3)、经常运行直接依赖于用于分割表的列的查询。
例如,当执行一个如“SELECTCOUNT(*)FROMemployeesWHEREYEAR(separated)=2000GROUPBYstore_id;”这样的查询时,MySQL可以很迅速地确定只有分区p2需要扫描,这是因为余下的分区不可能包含有符合该WHERE子句的任何记录。
注释:
这种优化还没有在MySQL5.1源程序中启用,但是,有关工作正在进行中。
LIST分区
类似于按RANGE分区,区别在于LIST分区是基于列值匹配一个离散值集合中的某个值来进行选择。
LIST分区通过使用“PARTITIONBYLIST(expr)”来实现,其中“expr”是某列值或一个基于某个列值、并返回一个整数值的表达式,然后通过“VALUESIN(value_list)”的方式来定义每个分区,其中“value_list”是一个通过逗号分隔的整数列表。
注释:
在MySQL5.1中,当使用LIST分区时,有可能只能匹配整数列表。
Sql代码
CREATE TABLE employees (
id INT NOT NULL,
fname VARCHAR(30),
lname VARCHAR(30),
hired DATE NOT NULL DEFAULT '1970-01-01',
separated DATE NOT NULL DEFAULT '9999-12-31',
job_code INT,
store_id INT
);
CREATETABLEemployees(idINTNOTNULL,fnameVARCHAR(30),lnameVARCHAR(30),hiredDATENOTNULLDEFAULT'1970-01-01',separatedDATENOTNULLDEFAULT'9999-12-31',job_codeINT,store_idINT);
假定有20个音像店,分布在4个有经销权的地区,如下表所示:
====================
地区 商店ID号
------------------------------------
北区 3,5,6,9,17
东区 1,2,10,11,19,20
西区 4,12,13,14,18
中心区 7,8,15,16
====================
要按照属于同一个地区商店的行保存在同一个分区中的方式来分割表,可以使用下面的“CREATETABLE”语句:
Sql代码
CREATE TABLE employees (
id INT NOT NULL,
fname VARCHAR(30),
lname VARCHAR(30),
hired DATE NOT NULL DEFAU
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- mysql 分区 区别 联系