高性能Mysql——分区表详解

分表和分区

分表
MySQL 的分表是真正的分表&＃xff0c;一张表分成很多表后&＃xff0c;每一个小表都是完整的一张表&＃xff0c;都对应三个文件&＃xff0c;一个.MYD数据文件&＃xff0c;.MYI索引文件&＃xff0c;.frm表结构文件。

分区
一张大表进行分区后&＃xff0c;它还是一张表&＃xff0c;不会变成多张表&＃xff0c;但是存放数据的区块变多了。

分表和分区的区别

实现方式上
a) mysql的分表是真正的分表&＃xff0c;一张表分成很多表后&＃xff0c;每一个小表都是完正的一张表&＃xff0c;都对应三个文件&＃xff0c;一个 .MYD数据文件&＃xff0c;.MYI索引文件&＃xff0c;.frm表结构文件。

[root&＃64;BlackGhost test]# ls |grep user alluser.MRG alluser.frm user1.MYD user1.MYI user1.frm user2.MYD user2.MYI user2.frm


简单说明一下&＃xff0c;上面的分表呢是利用了merge存储引擎&＃xff08;分表的一种&＃xff09;&＃xff0c;alluser是总表&＃xff0c;下面有二个分表&＃xff0c;user1&＃xff0c;user2。他们二个都是独立的表&＃xff0c;取数据的时候&＃xff0c;我们可以通过总表来取。这里总表是没有.MYD,.MYI这二个文件的&＃xff0c;也就是说&＃xff0c;总表他不是一张表&＃xff0c;没有数据&＃xff0c;数据都放在分表里面。

我们来看看.MRG到底是什么东西

[root&＃64;BlackGhost test]# cat alluser.MRG |more user1 user2 #INSERT_METHOD&＃61;LAST


从上面我们可以看出&＃xff0c;alluser.MRG里面就存了一些分表的关系&＃xff0c;以及插入数据的方式。可以把总表理解成一个外壳&＃xff0c;或者是联接池。

b) 分区不一样&＃xff0c;一张大表进行分区后&＃xff0c;他还是一张表&＃xff0c;不会变成二张表&＃xff0c;但是他存放数据的区块变多了。

[root&＃64;BlackGhost test]# ls |grep user
user#P#p1.MYD
user#P#p1.MYI
user#P#p2.MYD
usera#P#p2.MYI
user.frm
user.par


我们都知道一张表对应三个文件.MYD,.MYI,.frm。分区呢根据一定的规则把数据文件和索引文件进行了分割&＃xff0c;还多出了一个.par文件&＃xff0c;打开.par文件后你可以看出他记录了&＃xff0c;这张表的分区信息&＃xff0c;根分表中的.MRG有点像。分区后&＃xff0c;还是一张&＃xff0c;而不是多张表。

数据处理上
a) 分表后&＃xff0c;数据都是存放在分表里&＃xff0c;总表只是一个外壳&＃xff0c;存取数据发生在一个一个的分表里面。看下面的例子&＃xff1a; select * from alluser where id&＃61;&＃39;12&＃39;表面上看&＃xff0c;是对表alluser进行操作的&＃xff0c;其实不是的。是对alluser里面的分表进行了操作。

b) 分区呢&＃xff0c;不存在分表的概念&＃xff0c;分区只不过把存放数据的文件分成了许多小块&＃xff0c;分区后的表呢&＃xff0c;还是一张表。数据处理还是由自己来完成。

提高性能上
a) 分表后&＃xff0c;单表的并发能力提高了&＃xff0c;磁盘I/O性能也提高了。并发能力为什么提高了呢&＃xff0c;因为查寻一次所花的时间变短了&＃xff0c;如果出现高并发的话&＃xff0c;总表可以根据不同的查询&＃xff0c;将并发压力分到不同的小表里面。磁盘I/O性能怎么搞高了呢&＃xff0c;本来一个非常大的.MYD文件现在也分摊到各个小表的.MYD中去了。

b) mysql提出了分区的概念&＃xff0c;我觉得就想突破磁盘I/O瓶颈&＃xff0c;想提高磁盘的读写能力&＃xff0c;来增加mysql性能。 在这一点上&＃xff0c;分区和分表的测重点不同&＃xff0c;分表重点是存取数据时&＃xff0c;如何提高mysql并发能力上&＃xff1b;而分区呢&＃xff0c;如何突破磁盘的读写能力&＃xff0c;从而达到提高mysql性能的目的。

实现的难易度上
a) 分表的方法有很多&＃xff0c;用merge来分表&＃xff0c;是最简单的一种方式。这种方式根分区难易度差不多&＃xff0c;并且对程序代码来说可以做到透明的。如果是用其他分表方式就比分区麻烦了。
b) 分区实现是比较简单的&＃xff0c;建立分区表&＃xff0c;根建平常的表没什么区别&＃xff0c;并且对开代码端来说是透明的。

分表和分区联系呢

1. 都能提高mysql的性高&＃xff0c;在高并发状态下都有一个良好的表面。
2. 分表和分区不矛盾&＃xff0c;可以相互配合的&＃xff0c;对于那些大访问量&＃xff0c;并且表数据比较多的表&＃xff0c;我们可以采取分表和分区结合的方式&＃xff08;如果merge这种分表方式&＃xff0c;不能和分区配合的话&＃xff0c;可以用其他的分表试&＃xff09;&＃xff0c;访问量不大&＃xff0c;但是表数据很多的表&＃xff0c;我们可以采取分区的方式等。

分区表分区方式

HASH分区

HASH分区的一大特点就是可以更平均的将数据分散到各个分区中&＃xff0c;不会像RANGE分区一样数据可能会集中在一个分区中。HASH分区简单来说是基于取模运算结果进行的分区。HASH分区分为常规分区和线性分区。但是常规分区在分区管理上过于麻烦&＃xff0c;所以我们一般使用线性分区。

CREATE TABLE C (ID INT NOT NULL,NAME VARCHAR(20),SEX VARCHAR(20))PARTITION BY LINEAR HASH (ID) PARTITIONS 4;


KEY分区
KEY分区和HASH分区类似&＃xff0c;不过KEY分区只支持服务器提供的HASH函数&＃xff0c;HASH分区只支持整数&＃xff0c;KEY分区支持除BLOB ,Text类型外的其他类型作为分区键。

RANGE分区
按照RANGE分区的表示利用取值范围将数据分成区&＃xff0c;区间是连续且不能相互叠加的。
RANGE分区一般用作日志归档&＃xff0c;并按照时间来进行分区

CREATE TABLE B(ID INT NOT NULL,NAME VARCHAR(30),JOB VARCHAR(30),TIME DATE NOT NULL DEFAULT &＃39;1998-01-01&＃39;)PARTITION BY RANGE(YEAR(TIME))(PARTITION P0 VALUES LESS THAN(1999),PARTITION P1 VALUES LESS THAN(2000),PARTITION P2 VALUES LESS THAN(2001));


LIST分区
LIST分区和RANGE分区比较相似&＃xff0c;不过LIST是以一个集合的形式建立分区的。使用LIST分区建立表的操作如下&＃xff1a;

Columns分区
Columns分区是mysql5.5以后加入的&＃xff0c;用来解决mysql5.1以前的RANGE和LIST分区只支持整数分区&＃xff0c;造成的需要进行函数换算得到的整数值。Columns分区包括RANGE Columns和LIST Columns。同时支持整数&＃xff0c;时间&＃xff0c;字符串三大类型。
利用Columns分区&＃xff0c;我们上边RANGE的按时间进行分区的表就可以不添加时间转换函数。具体操作如下&＃xff1a;

CREATE TABLE B(ID INT NOT NULL,NAME VARCHAR(30),JOB VARCHAR(30),TIME DATE NOT NULL DEFAULT &＃39;1998-01-01&＃39;)PARTITION BY RANGE COLUMNS(TIME)(PARTITION P0 VALUES LESS THAN(&＃39;1999&＃39;),PARTITION P1 VALUES LESS THAN(&＃39;2000&＃39;),PARTITION P2 VALUES LESS THAN(&＃39;2001&＃39;));


分区管理

删除分区&＃xff1a;

ALTER TABLE A DROP PARTITION P2;


以上语句从表A中删除了P2分区&＃xff0c;删除分区后&＃xff0c;分区内的数据也会被删除。
在RANGE分区中&＃xff0c;再次加入原属于分区P2的数据时&＃xff0c;会插入到P3分区内。但在LIST分区中&＃xff0c;由于删除的是一个指定的集合&＃xff0c;所以再次插入属于此集合的数据时会因找不到分区而出错。

加入分区&＃xff1a;

ALTER TABLE A ADD PARTITION (PARTITION P3 VALUES LESS THAN (40));


以上语句在A中添加了p3分区&＃xff0c;数值为小于40。在RANGE分区中&＃xff0c;只能在分区列表的最大一端添加分区。但在LIST中可以添加在任意地方&＃xff0c;不过新建的分区不能包括已有分区包含的元素

转移分区&＃xff08;归档&＃xff09;

ALTER TABLE A exchange PARTITION P1 WITH TABLE ARCH_A;


另外归档的ARCH_A表可以使用归档引擎archieve&＃xff0c;归档引擎的好处是比INNODB所占的空间更小&＃xff0c;但是只允许读操作&＃xff0c;不允许写操作。

ALTER TABLE ARCH_A ENGINE&＃61;ARCHIEVE


分区表注意事项

1. 结合业务场景选择分区方式。
2. 避免跨分区查询。
3. 对分区表进行查询最好在WHERE从句中包含分区键&＃xff08;避免跨分区扫描&＃xff09;。
4. 具有主键或者唯一索引的表&＃xff0c;主键或唯一索引必须是分区键的一部分。