如何深入解析MySQL分区Partition功能


这篇文章将为大家详细讲解有关如何深入解析MySQL分区Partition功能,文章内容质量较高,因此小编分享给大家做个参考,希望大家阅读完这篇文章后对相关知识有一定的了解。 自5.1开始对分区(Partition)有支持

= 水平分区(根据列属性按行分)=
举个简单例子:一个包含十年发票记录的表可以被分区为十个不同的分区,每个分区包含的是其中一年的记录。

=== 水平分区的几种模式:===
*Range(范围)– 这种模式允许DBA将数据划分不同范围。例如DBA可以将一个表通过年份划分成三个分区,80年代(1980’s)的数据,90年代(1990’s)的数据以及任何在2000年(包括2000年)后的数据。

*Hash(哈希)– 这中模式允许DBA通过对表的一个或多个列的Hash Key进行计算,最后通过这个Hash码不同数值对应的数据区域进行分区,。例如DBA可以建立一个对表主键进行分区的表。

*Key(键值)– 上面Hash模式的一种延伸,这里的Hash Key是MySQL系统产生的。

*List(预定义列表)– 这种模式允许系统通过DBA定义的列表的值所对应的行数据进行分割。例如:DBA建立了一个横跨三个分区的表,分别根据2004年2005年和2006年值所对应的数据。

* Composite(复合模式)– 很神秘吧,哈哈,其实是以上模式的组合使用而已,就不解释了。举例:在初始化已经进行了Range范围分区的表上,我们可以对其中一个分区再进行hash哈希分区。

= 垂直分区(按列分)=
举个简单例子:一个包含了大text和BLOB列的表,这些text和BLOB列又不经常被访问,这时候就要把这些不经常使用的text和BLOB了划分到另一个分区,在保证它们数据相关性的同时还能提高访问速度。

[分区表和未分区表试验过程]

*创建分区表,按日期的年份拆分
[sql]view plaincopy mysql>CREATETABLEpart_tab(c1intdefaultNULL,c2varchar(30)defaultNULL,c3datedefaultNULL)engine=myisamPARTITIONBYRANGE(year(c3))(PARTITIONp0VALUESLESSTHAN(1995),PARTITIONp1VALUESLESSTHAN(1996),PARTITIONp2VALUESLESSTHAN(1997),PARTITIONp3VALUESLESSTHAN(1998),PARTITIONp4VALUESLESSTHAN(1999),PARTITIONp5VALUESLESSTHAN(2000),PARTITIONp6VALUESLESSTHAN(2001),PARTITIONp7VALUESLESSTHAN(2002),PARTITIONp8VALUESLESSTHAN(2003),PARTITIONp9VALUESLESSTHAN(2004),PARTITIONp10VALUESLESSTHAN(2010),PARTITIONp11VALUESLESSTHANMAXVALUE);注意最后一行,考虑到可能的最大值

*创建未分区表
[sql]view plaincopy mysql>createtableno_part_tab(c1int(11)defaultNULL,c2varchar(30)defaultNULL,c3datedefaultNULL)engine=myisam;
*通过存储过程灌入800万条测试数据

mysql> set sql_mode=”; /* 如果创建存储过程失败,则先需设置此变量, bug? */
MySQL> delimiter // /* 设定语句终结符为 //,因存储过程语句用;结束 */[sql]view plaincopy mysql>CREATEPROCEDUREload_part_tab()begindeclarevintdefault0;whilevdoinsertintopart_tabvalues(v,’testingpartitions’,adddate(‘1995-01-01’,(rand(v)*36520)mod3652));setv=v+1;endwhile;end//mysql>delimiter;mysql>callload_part_tab();Query OK, 1 row affected (8 min 17.75 sec)
[sql]view plaincopy mysql>insertintono_part_tabselect*frompart_tab;Query OK, 8000000 rows affected (51.59 sec)
Records: 8000000 Duplicates: 0 Warnings: 0

*测试SQL性能[sql]view plaincopy mysql>selectcount(*)frompart_tabwherec3>date’1995-01-01’andc3+———-+
| count(*) |
+———-+
| 795181 |
+———-+
1 row in set (0.55 sec)[sql]view plaincopy mysql>selectcount(*)fromno_part_tabwherec3>date’1995-01-01’andc3+———-+
| count(*) |
+———-+
| 795181 |
+———-+
1 row in set (4.69 sec)
结果表明分区表比未分区表的执行时间少90%。

* 通过explain语句来分析执行情况
[sql]view plaincopy mysql>explainselectcount(*)fromno_part_tabwherec3>date’1995-01-01’andc3/* 结尾的G使得mysql的输出改为列模式 */
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: no_part_tab
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 8000000
Extra: Using where
1 row in set (0.00 sec)
[sql]view plaincopy mysql>explainselectcount(*)frompart_tabwherec3>date’1995-01-01’andc3*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: part_tab
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 798458
Extra: Using where
1 row in set (0.00 sec)
explain语句显示了SQL查询要处理的记录数目

* 试验创建索引后情况
[sql]view plaincopy mysql>createindexidx_of_c3onno_part_tab(c3);Query OK, 8000000 rows affected (1 min 18.08 sec)
Records: 8000000 Duplicates: 0 Warnings: 0

[sql]view plaincopy mysql>createindexidx_of_c3onpart_tab(c3);Query OK, 8000000 rows affected (1 min 19.19 sec)
Records: 8000000 Duplicates: 0 Warnings: 0
创建索引后的数据库文件大小列表:
2008-05-24 09:23 8,608 no_part_tab.frm
2008-05-24 09:24 255,999,996 no_part_tab.MYD
2008-05-24 09:24 81,611,776 no_part_tab.MYI
2008-05-24 09:25 0 part_tab#P#p0.MYD
2008-05-24 09:26 1,024 part_tab#P#p0.MYI
2008-05-24 09:26 25,550,656 part_tab#P#p1.MYD
2008-05-24 09:26 8,148,992 part_tab#P#p1.MYI
2008-05-24 09:26 25,620,192 part_tab#P#p10.MYD
2008-05-24 09:26 8,170,496 part_tab#P#p10.MYI
2008-05-24 09:25 0 part_tab#P#p11.MYD
2008-05-24 09:26 1,024 part_tab#P#p11.MYI
2008-05-24 09:26 25,656,512 part_tab#P#p2.MYD
2008-05-24 09:26 8,181,760 part_tab#P#p2.MYI
2008-05-24 09:26 25,586,880 part_tab#P#p3.MYD
2008-05-24 09:26 8,160,256 part_tab#P#p3.MYI
2008-05-24 09:26 25,585,696 part_tab#P#p4.MYD
2008-05-24 09:26 8,159,232 part_tab#P#p4.MYI
2008-05-24 09:26 25,585,216 part_tab#P#p5.MYD
2008-05-24 09:26 8,159,232 part_tab#P#p5.MYI
2008-05-24 09:26 25,655,740 part_tab#P#p6.MYD
2008-05-24 09:26 8,181,760 part_tab#P#p6.MYI
2008-05-24 09:26 25,586,528 part_tab#P#p7.MYD
2008-05-24 09:26 8,160,256 part_tab#P#p7.MYI
2008-05-24 09:26 25,586,752 part_tab#P#p8.MYD
2008-05-24 09:26 8,160,256 part_tab#P#p8.MYI
2008-05-24 09:26 25,585,824 part_tab#P#p9.MYD
2008-05-24 09:26 8,159,232 part_tab#P#p9.MYI
2008-05-24 09:25 8,608 part_tab.frm
2008-05-24 09:25 68 part_tab.par

* 再次测试SQL性能[sql]view plaincopy mysql>selectcount(*)fromno_part_tabwherec3>date’1995-01-01’andc3+———-+
| count(*) |
+———-+
| 795181 |
+———-+
1 row in set (2.42 sec) /* 为原来4.69 sec 的51%*/重启mysql ( net stop mysql, net start mysql)后,查询时间降为0.89 sec,几乎与分区表相同。

[sql]view plaincopy mysql>selectcount(*)frompart_tabwherec3>date’1995-01-01’andc3+———-+
| count(*) |
+———-+
| 795181 |
+———-+
1 row in set (0.86 sec)

* 更进一步的试验
** 增加日期范围
[sql]view plaincopy mysql>selectcount(*)fromno_part_tabwherec3>date’1995-01-01’andc3+———-+
| count(*) |
+———-+
| 2396524 |
+———-+
1 row in set (5.42 sec)

[sql]view plaincopy mysql>selectcount(*)frompart_tabwherec3>date’1995-01-01’andc3+———-+
| count(*) |
+———-+
| 2396524 |
+———-+
1 row in set (2.63 sec)** 增加未索引字段查询
[sql]view plaincopy mysql>selectcount(*)frompart_tabwherec3>date’1995-01-01’andc3’1996-12-31’andc2=’hello’;+———-+
| count(*) |
+———-+
| 0 |
+———-+
1 row in set (0.75 sec)

[sql]view plaincopy mysql>selectcount(*)fromno_part_tabwherec3>date’1995-01-01’andc3+———-+
| count(*) |
+———-+
| 0 |
+———-+
1 row in set (11.52 sec)

= 初步结论 =
* 分区和未分区占用文件空间大致相同 (数据和索引文件)
* 如果查询语句中有未建立索引字段,分区时间远远优于未分区时间
* 如果查询语句中字段建立了索引,分区和未分区的差别缩小,分区略优于未分区。

= 最终结论 =
* 对于大数据量,建议使用分区功能。
* 去除不必要的字段
* 根据手册, 增加myisam_max_sort_file_size 会增加分区性能

[分区命令详解]

= 分区例子 =
* RANGE 类型
[sql]view plaincopy CREATETABLEusers(uidINTUNSIGNEDNOTNULLAUTO_INCREMENTPRIMARYKEY,nameVARCHAR(30)NOTNULLDEFAULT”,emailVARCHAR(30)NOTNULLDEFAULT”)PARTITIONBYRANGE(uid)(PARTITIONp0VALUESLESSTHAN(3000000)DATADIRECTORY=’/data0/data’INDEXDIRECTORY=’/data1/idx’,PARTITIONp1VALUESLESSTHAN(6000000)DATADIRECTORY=’/data2/data’INDEXDIRECTORY=’/data3/idx’,PARTITIONp2VALUESLESSTHAN(9000000)DATADIRECTORY=’/data4/data’INDEXDIRECTORY=’/data5/idx’,PARTITIONp3VALUESLESSTHANMAXVALUEDATADIRECTORY=’/data6/data’INDEXDIRECTORY=’/data7/idx’);在这里,将用户表分成4个分区,以每300万条记录为界限,每个分区都有自己独立的数据、索引文件的存放目录,与此同时,这些目录所在的物理磁盘分区可能也都是完全独立的,可以提高磁盘IO吞吐量。

* LIST 类型
[sql]view plaincopy CREATETABLEcategory(cidINTUNSIGNEDNOTNULLAUTO_INCREMENTPRIMARYKEY,nameVARCHAR(30)NOTNULLDEFAULT”)PARTITIONBYLIST(cid)(PARTITIONp0VALUESIN(0,4,8,12)DATADIRECTORY=’/data0/data’INDEXDIRECTORY=’/data1/idx’,PARTITIONp1VALUESIN(1,5,9,13)DATADIRECTORY=’/data2/data’INDEXDIRECTORY=’/data3/idx’,PARTITIONp2VALUESIN(2,6,10,14)DATADIRECTORY=’/data4/data’INDEXDIRECTORY=’/data5/idx’,PARTITIONp3VALUESIN(3,7,11,15)DATADIRECTORY=’/data6/data’INDEXDIRECTORY=’/data7/idx’);分成4个区,数据文件和索引文件单独存放。

* HASH 类型
[sql]view plaincopy CREATETABLEusers(uidINTUNSIGNEDNOTNULLAUTO_INCREMENTPRIMARYKEY,nameVARCHAR(30)NOTNULLDEFAULT”,emailVARCHAR(30)NOTNULLDEFAULT”)PARTITIONBYHASH(uid)PARTITIONS4(PARTITIONp0DATADIRECTORY=’/data0/data’INDEXDIRECTORY=’/data1/idx’,PARTITIONp1DATADIRECTORY=’/data2/data’INDEXDIRECTORY=’/data3/idx’,PARTITIONp2DATADIRECTORY=’/data4/data’INDEXDIRECTORY=’/data5/idx’,PARTITIONp3DATADIRECTORY=’/data6/data’INDEXDIRECTORY=’/data7/idx’);分成4个区,数据文件和索引文件单独存放。

例子:
[sql]view plaincopy CREATETABLEti2(idINT,amountDECIMAL(7,2),tr_dateDATE)ENGINE=myisamPARTITIONBYHASH(MONTH(tr_date))PARTITIONS6;CREATEPROCEDUREload_ti2()begindeclarevintdefault0;whilevdoinsertintoti2values(v,’3.14′,adddate(‘1995-01-01’,(rand(v)*3652)mod365));setv=v+1;endwhile;end//* KEY 类型
[sql]view plaincopy CREATETABLEusers(uidINTUNSIGNEDNOTNULLAUTO_INCREMENTPRIMARYKEY,nameVARCHAR(30)NOTNULLDEFAULT”,emailVARCHAR(30)NOTNULLDEFAULT”)PARTITIONBYKEY(uid)PARTITIONS4(PARTITIONp0DATADIRECTORY=’/data0/data’INDEXDIRECTORY=’/data1/idx’,PARTITIONp1DATADIRECTORY=’/data2/data’INDEXDIRECTORY=’/data3/idx’,PARTITIONp2DATADIRECTORY=’/data4/data’INDEXDIRECTORY=’/data5/idx’,PARTITIONp3DATADIRECTORY=’/data6/data’INDEXDIRECTORY=’/data7/idx’);分成4个区,数据文件和索引文件单独存放。

* 子分区
子分区是针对 RANGE/LIST 类型的分区表中每个分区的再次分割。再次分割可以是 HASH/KEY 等类型。例如:
[sql]view plaincopy CREATETABLEusers(uidINTUNSIGNEDNOTNULLAUTO_INCREMENTPRIMARYKEY,nameVARCHAR(30)NOTNULLDEFAULT”,emailVARCHAR(30)NOTNULLDEFAULT”)PARTITIONBYRANGE(uid)SUBPARTITIONBYHASH(uid%4)SUBPARTITIONS2(PARTITIONp0VALUESLESSTHAN(3000000)DATADIRECTORY=’/data0/data’INDEXDIRECTORY=’/data1/idx’,PARTITIONp1VALUESLESSTHAN(6000000)DATADIRECTORY=’/data2/data’INDEXDIRECTORY=’/data3/idx’);对 RANGE 分区再次进行子分区划分,子分区采用 HASH 类型。
或者
[sql]view plaincopy CREA开发云主机域名TETABLEusers(uidINTUNSIGNEDNOTNULLAUTO_INCREMENTPRIMARYKEY,nameVARCHAR(30)NOTNULLDEFAULT”,emailVARCHAR(30)NOTNULLDEFAULT”)PARTITIONBYRANGE(uid)SUBPARTITIONBYKEY(uid)SUBPARTITIONS2(PARTITIONp0VALUESLESSTHAN(3000000)DATADIRECTORY=’/data0/data’INDEXDIRECTORY=’/data1/idx’,PARTITIONp1VALUESLESSTHAN(6000000)DATADIRECTORY=’/data2/data’INDEXDIRECTORY=’/data3/idx’);对 RANGE 分区再次进行子分区划分,子分区采用 KEY 类型。

= 分区管理 =

* 删除分区
[sql]view plaincopy ALERTTABLEusersDROPPARTITIONp0; 删除分区 p0。 * 重建分区
o RANGE 分区重建
[sql]view plaincopy ALTERTABLEusersREORGANIZEPARTITIONp0,p1INTO(PARTITIONp0VALUESLESSTHAN(6000000)); 将原来的 p0,p1 分区合并起来,放到新的 p0 分区中。
o LIST 分区重建
[sql]view plaincopy ALTERTABLEusersREORGANIZEPARTITIONp0,p1INTO(PARTITIONp0VALUESIN(0,1,4,5,8,9,12,13)); 将原来的 p0,p1 分区合并起来,放到新的 p0 分区中。
o HASH/KEY 分区重建
[sql]view plaincopy ALTERTABLEusersREORGANIZEPARTITIONCOALESCEPARTITION2; 用 REORGANIZE 方式重建分区的数量变成2,在这里数量只能减少不能增加。想要增加可以用 ADD PARTITION 方法。
* 新增分区
o 新增 RANGE 分区
[sql]view plaincopy ALTERTABLEcategoryADDPARTITION(PARTITIONp4VALUESIN(16,17,18,19)DATADIRECTORY=’/data8/data’INDEXDIRECTORY=’/data9/idx’); 新增一个RANGE分区。
o 新增 HASH/KEY 分区
[sql]view plaincopy ALTERTABLEusersADDPARTITIONPARTITIONS8; 将分区总数扩展到8个。

[ 给已有的表加上分区 ]
[sql]view plaincopy altertableresultspartitionbyRANGE(month(ttime))(PARTITIONp0VALUESLESSTHAN(1),PARTITIONp1VALUESLESSTHAN(2),PARTITIONp2VALUESLESSTHAN(3),PARTITIONp3VALUESLESSTHAN(4),PARTITIONp4VALUESLESSTHAN(5),PARTITIONp5VALUESLESSTHAN(6),PARTITIONp6VALUESLESSTHAN(7),PARTITIONp7VALUESLESSTHAN(8),PARTITIONp8VALUESLESSTHAN(9),PARTITIONp9VALUESLESSTHAN(10),PARTITIONp10VALUESLESSTHAN(11),PARTITIONp11VALUESLESSTHAN(12),PARTITIONP12VALUESLESSTHAN(13));

默认分区限制分区字段必须是主键(PRIMARY KEY)的一部分,为了去除此
限制:
[方法1] 使用ID
[sql]view plaincopy mysql>ALTERTABLEnp_pk->PARTITIONBYHASH(TO_DAYS(added))->PARTITIONS4;ERROR 1503 (HY000): A PRIMARY KEY must include all columns in the table’s partitioning function

However, this statement using the id column for the partitioning column is valid, as shown here:

[sql]view plaincopy mysql>ALTERTABLEnp_pk->PARTITIONBYHASH(id)->PARTITIONS4;Query OK, 0 rows affected (0.11 sec)
Records: 0 Duplicates: 0 Warnings: 0

[方法2] 将原有PK去掉生成新PK
[sql]view plaincopy mysql>altertableresultsdropPRIMARYKEY;Query OK, 5374850 rows affected (7 min 4.05 sec)
Records: 5374850 Duplicates: 0 Warnings: 0

[sql]view plaincopy mysql>altertableresultsaddPRIMARYKEY(id,ttime);Query OK, 5374850 rows affected (6 min 14.86 sec)
Records: 5374850 Duplicates: 0 Warnings: 0关于如何深入解析MySQL分区Partition功能就分享到这里了,希望以上内容可以对大家有一定的帮助,可以学到更多知识。如果觉得文章不错,可以把它分享出去让更多的人看到。

相关推荐: 超详细LAMP环境手动编译安装实例(PHP以模块方式工作)

LAMP编译安装实例: 一:HTTPD编译安装:下载软件包:需要依赖最新版apr和apr-utilapr:Apache Porttable Runtime:可移植Apache运行环境,用于把C语言编写的Apache程序运行在不同的操作系统上,即有Windows…

免责声明:本站发布的图片视频文字,以转载和分享为主,文章观点不代表本站立场,本站不承担相关法律责任;如果涉及侵权请联系邮箱:360163164@qq.com举报,并提供相关证据,经查实将立刻删除涉嫌侵权内容。

(0)
打赏 微信扫一扫 微信扫一扫
上一篇 06/27 11:08
下一篇 06/27 11:09

相关推荐