mysql数据库分区功能及实例详解

分区听起来怎么感觉是硬盘呀,对没错除了硬盘可以分区数据库现在也支持分区了,分区可以解决大数据量的处理问题,下面一起来看一个mysql数据库分区功能及实例详解.

一,什么是数据库分区

前段时间写过一篇关于mysql分表的的文章，下面来说一下什么是数据库分区，以mysql为例。mysql数据库中的数据是以文件的形势存在磁盘上的，默认放在/mysql/data下面（可以通过my.cnf中的datadir来查看），一张表主要对应着三个文件，一个是frm存放表结构的，一个是myd存放表数据的，一个是myi存表索引的。如果一张表的数据量太大的话，那么myd,myi就会变的很大，查找数据就会变的很慢，这个时候我们可以利用mysql的分区功能，在物理上将这一张表对应的三个文件，分割成许多个小块，这样呢，我们查找一条数据时，就不用全部查找了，只要知道这条数据在哪一块，然后在那一块找就行了。如果表的数据太大，可能一个磁盘放不下，这个时候，我们可以把数据分配到不同的磁盘里面去。

分区的二种方式

1，横向分区

什么是横向分区呢？就是横着来分区了，举例来说明一下，假如有100W条数据，分成十份，前10W条数据放到第一个分区，第二个10W条数据放到第二个分区，依此类推。也就是把表分成了十分，根用merge来分表，有点像哦。取出一条数据的时候，这条数据包含了表结构中的所有字段，也就是说横向分区，并没有改变表的结构。

2，纵向分区

什么是纵向分区呢？就是竖来分区了，举例来说明，在设计用户表的时候，开始的时候没有考虑好，而把个人的所有信息都放到了一张表里面去，这样这个表里面就会有比较大的字段，如个人简介，而这些简介呢，也许不会有好多人去看，所以等到有人要看的时候，在去查找，分表的时候，可以把这样的大字段，分开来。

感觉数据库的分区好像是切苹果，到底是横着切呢，还是竖着切，根据个人喜好了，mysql提供的分区属于第一种，横向分区，并且细分成很多种方式。下面将举例说明一下。

二，mysql的分区

我觉着吧，mysql的分区只有一种方式，只不过运用不同的算法，?则将数据分配到不同的区块中而已。

1，mysql5.1及以上支持分区功能

安装安装的时候,我们就可以查看一下,代码如下:

1. [root@BlackGhostmysql-5.1.50]#./configure--help|grep-A3Partition
2. ===PartitionSupport===
3. PluginName:partition
4. Description:MySQLPartitioningSupport
5. Supportsbuild:static
6. Configurations:max,max-no-ndb

查看一下，如果发现有上面这个东西，说明他是支持分区的，默认是打开的。如果你已经安装过了mysql的话,代码如下:

1. mysql>showvariableslike"%part%";
2. +-------------------+-------+
3. |Variable_name|Value|
4. +-------------------+-------+
5. |have_partitioning|YES|
6. +-------------------+-------+
7. 1rowinset(0.00sec)

查看一下变量，如果支持的话，会有上面的提示的。

2，range分区

按照RANGE分区的表是通过如下一种方式进行分区的,每个分区包含那些分区表达式的值位于一个给定的连续区间内的行,代码如下:

1. //创建range分区表
3. mysql>CREATETABLEIFNOTEXISTS`user`(
4. ->`id`int(11)NOTNULLAUTO_INCREMENTCOMMENT'用户ID',
5. ->`name`varchar(50)NOTNULLDEFAULT''COMMENT'名称',
6. ->`sex`int(1)NOTNULLDEFAULT'0'COMMENT'0为男，1为女',
7. ->PRIMARYKEY(`id`)
8. ->)ENGINE=MyISAMDEFAULTCHARSET=utf8AUTO_INCREMENT=1
9. ->PARTITIONBYRANGE(id)(
10. ->PARTITIONp0VALUESLESSTHAN(3),
11. ->PARTITIONp1VALUESLESSTHAN(6),
12. ->PARTITIONp2VALUESLESSTHAN(9),
13. ->PARTITIONp3VALUESLESSTHAN(12),
14. ->PARTITIONp4VALUESLESSTHANMAXVALUE
15. ->);
16. QueryOK,0rowsaffected(0.13sec)
18. //插入一些数据
19. mysql>INSERTINTO`test`.`user`(`name`,`sex`)VALUES('tank','0')
20. ->,('zhang',1),('ying',1),('张',1),('映',0),('test1',1),('tank2',1)
21. ->,('tank1',1),('test2',1),('test3',1),('test4',1),('test5',1),('tank3',1)
22. ->,('tank4',1),('tank5',1),('tank6',1),('tank7',1),('tank8',1),('tank9',1)
23. ->,('tank10',1),('tank11',1),('tank12',1),('tank13',1),('tank21',1),('tank42',1);
24. QueryOK,25rowsaffected(0.05sec)
25. Records:25Duplicates:0Warnings:0
27. //到存放数据库表文件的地方看一下，my.cnf里面有配置，datadir后面就是
29. [root@BlackGhosttest]#ls|grepuser|xargsdu-sh
30. 4.0Kuser#P#p0.MYD
31. 4.0Kuser#P#p0.MYI
32. 4.0Kuser#P#p1.MYD
33. 4.0Kuser#P#p1.MYI
34. 4.0Kuser#P#p2.MYD
35. 4.0Kuser#P#p2.MYI
36. 4.0Kuser#P#p3.MYD
37. 4.0Kuser#P#p3.MYI
38. 4.0Kuser#P#p4.MYD
39. 4.0Kuser#P#p4.MYI
40. 12Kuser.frm
41. 4.0Kuser.par
43. //取出数据
45. mysql>selectcount(id)ascountfromuser;
46. +-------+
47. |count|
48. +-------+
49. |25|
50. +-------+
51. 1rowinset(0.00sec)
53. //删除第四个分区
55. mysql>altertableuserdroppartitionp4;
56. QueryOK,0rowsaffected(0.11sec)
57. Records:0Duplicates:0Warnings:0
59. /**存放在分区里面的数据丢失了，第四个分区里面有14条数据，剩下的3个分区
61. 只有11条数据，但是统计出来的文件大小都是4.0K，从这儿我们可以看出分区的
62. 最小区块是4K
63. */
64. mysql>selectcount(id)ascountfromuser;
65. +-------+
66. |count|
67. +-------+
68. |11|
69. +-------+
70. 1rowinset(0.00sec)
72. //第四个区块已删除
74. [root@BlackGhosttest]#ls|grepuser|xargsdu-sh
75. 4.0Kuser#P#p0.MYD
76. 4.0Kuser#P#p0.MYI
77. 4.0Kuser#P#p1.MYD
78. 4.0Kuser#P#p1.MYI
79. 4.0Kuser#P#p2.MYD
80. 4.0Kuser#P#p2.MYI
81. 4.0Kuser#P#p3.MYD
82. 4.0Kuser#P#p3.MYI
83. 12Kuser.frm
84. 4.0Kuser.par
86. /*可以对现有表进行分区,并且会按?则自动的将表中的数据分配相应的分区
87. 中，这样就比较好了，可以省去很多事情，看下面的操作*/
88. mysql>altertableaapartitionbyRANGE(id)
89. ->(PARTITIONp1VALUESlessthan(1),
90. ->PARTITIONp2VALUESlessthan(5),
91. ->PARTITIONp3VALUESlessthanMAXVALUE);
92. QueryOK,15rowsaffected(0.21sec)//对15数据进行分区
93. Records:15Duplicates:0Warnings:0
95. //总共有15条
96. mysql>selectcount(*)fromaa;
97. +----------+
98. |count(*)|
99. +----------+
100. |15|
101. +----------+
102. 1rowinset(0.00sec)
104. //删除一个分区
105. mysql>altertableaadroppartitionp2;
106. QueryOK,0rowsaffected(0.30sec)
107. Records:0Duplicates:0Warnings:0
109. //只有11条了，说明对现有的表分区成功了
110. mysql>selectcount(*)fromaa;
111. +----------+
112. |count(*)|
113. +----------+
114. |11|
115. +----------+
116. 1rowinset(0.00sec)

3，list分区

LIST分区中每个分区的定义和选择是基于某列的值从属于一个值列表集中的一个值，而RANGE分 区是从属于一个连续区间值的集合,代码如下:

1. //这种方式失败
3. mysql>CREATETABLEIFNOTEXISTS`list_part`(
4. ->`id`int(11)NOTNULLAUTO_INCREMENTCOMMENT'用户ID',
5. ->`province_id`int(2)NOTNULLDEFAULT0COMMENT'省',
6. ->`name`varchar(50)NOTNULLDEFAULT''COMMENT'名称',
7. ->`sex`int(1)NOTNULLDEFAULT'0'COMMENT'0为男，1为女',
8. ->PRIMARYKEY(`id`)
9. ->)ENGINE=INNODBDEFAULTCHARSET=utf8AUTO_INCREMENT=1
10. ->PARTITIONBYLIST(province_id)(
11. ->PARTITIONp0VALUESIN(1,2,3,4,5,6,7,8),
12. ->PARTITIONp1VALUESIN(9,10,11,12,16,21),
13. ->PARTITIONp2VALUESIN(13,14,15,19),
14. ->PARTITIONp3VALUESIN(17,18,20,22,23,24)
15. ->);
16. ERROR1503(HY000):APRIMARYKEYmustincludeallcolumnsinthetable'spartitioningfunction
18. //这种方式成功
19. mysql>CREATETABLEIFNOTEXISTS`list_part`(
20. ->`id`int(11)NOTNULLCOMMENT'用户ID',
21. ->`province_id`int(2)NOTNULLDEFAULT0COMMENT'省',
22. ->`name`varchar(50)NOTNULLDEFAULT''COMMENT'名称',
23. ->`sex`int(1)NOTNULLDEFAULT'0'COMMENT'0为男，1为女'
24. ->)ENGINE=INNODBDEFAULTCHARSET=utf8
25. ->PARTITIONBYLIST(province_id)(
26. ->PARTITIONp0VALUESIN(1,2,3,4,5,6,7,8),
27. ->PARTITIONp1VALUESIN(9,10,11,12,16,21),
28. ->PARTITIONp2VALUESIN(13,14,15,19),
29. ->PARTITIONp3VALUESIN(17,18,20,22,23,24)
30. ->);
31. QueryOK,0rowsaffected(0.33sec)

上面的这个创建list分区时，如果有主?的话,分区时主键必须在其中,不然就会报错。如果我不用主键,分区就创建成功了,一般情况下,一个张表肯定会有一个主键,这算是一个分区的局限性吧.

如果对数据进行测试,请参考range分区的测试来操作.

4，hash分区

HASH分区主要用来确保数据在预先确定数目的分区中平均分布,你所要做的只是基于将要被哈希的列值指定一个列值或表达式,以 及指定被分区的表将要被分割成的分区数量,代码如下:

1. mysql>CREATETABLEIFNOTEXISTS`hash_part`(
2. ->`id`int(11)NOTNULLAUTO_INCREMENTCOMMENT'评论ID',
3. ->`comment`varchar(1000)NOTNULLDEFAULT''COMMENT'评论',
4. ->`ip`varchar(25)NOTNULLDEFAULT''COMMENT'来源IP',
5. ->PRIMARYKEY(`id`)
6. ->)ENGINE=INNODBDEFAULTCHARSET=utf8AUTO_INCREMENT=1//开源代码phpfensi.com
7. ->PARTITIONBYHASH(id)
8. ->PARTITIONS3;
9. QueryOK,0rowsaffected(0.06sec)

测试请参考range分区的操作

5，key分区

按照KEY进行分区类似于按照HASH分区,除了HASH分区使用的用 户定义的表达式,而KEY分区的哈希函数是由MySQL 服务器提供,代码如下:

1. mysql>CREATETABLEIFNOTEXISTS`key_part`(
2. ->`news_id`int(11)NOTNULLCOMMENT'新闻ID',
3. ->`content`varchar(1000)NOTNULLDEFAULT''COMMENT'新闻内容',
4. ->`u_id`varchar(25)NOTNULLDEFAULT''COMMENT'来源IP',
5. ->`create_time`DATENOTNULLDEFAULT'0000-00-0000:00:00'COMMENT'时间'
6. ->)ENGINE=INNODBDEFAULTCHARSET=utf8
7. ->PARTITIONBYLINEARHASH(YEAR(create_time))
8. ->PARTITIONS3;
9. QueryOK,0rowsaffected(0.07sec)

测试请参考range分区的操作

6，子分区

子分区是分区表中每个分区的再次分割,子分区既可以使用HASH希分区,也可以使用KEY分区,这 也被称为复合分区,composite partitioning.

1，如果一个分区中创建了子分区,其他分区也要有子分区

2，如果创建了了分区,每个分区中的子分区数必有相同

3，同一分区内的子分区,名字不相同,不同分区内的子分区名子可以相同,5.1.50不适用,代码如下:

1. mysql>CREATETABLEIFNOTEXISTS`sub_part`(
2. ->`news_id`int(11)NOTNULLCOMMENT'新闻ID',
3. ->`content`varchar(1000)NOTNULLDEFAULT''COMMENT'新闻内容',
4. ->`u_id`int(11)NOTNULLDEFAULT0sCOMMENT'来源IP',
5. ->`create_time`DATENOTNULLDEFAULT'0000-00-0000:00:00'COMMENT'时间'
6. ->)ENGINE=INNODBDEFAULTCHARSET=utf8
7. ->PARTITIONBYRANGE(YEAR(create_time))
8. ->SUBPARTITIONBYHASH(TO_DAYS(create_time))(
9. ->PARTITIONp0VALUESLESSTHAN(1990)(SUBPARTITIONs0,SUBPARTITIONs1,SUBPARTITIONs2),
10. ->PARTITIONp1VALUESLESSTHAN(2000)(SUBPARTITIONs3,SUBPARTITIONs4,SUBPARTITIONgood),
11. ->PARTITIONp2VALUESLESSTHANMAXVALUE(SUBPARTITIONtank0,SUBPARTITIONtank1,SUBPARTITIONtank3)
12. ->);
13. QueryOK,0rowsaffected(0.07sec)

官方网站说不同分区内的子分区可以有相同的名字,但是mysql5.1.50却不行会提示以下错误:

ERROR 1517 (HY000): Duplicate partition name s1

三，分区管理,代码如下:

1，删除分区:mysql> alter table user drop partition p4;

2，新增分区:

1. //range添加新分区
2. mysql>altertableuseraddpartition(partitionp4valueslessthanMAXVALUE);
3. QueryOK,0rowsaffected(0.06sec)
4. Records:0Duplicates:0Warnings:0
6. //list添加新分区
7. mysql>altertablelist_partaddpartition(partitionp4valuesin(25,26,28));
8. QueryOK,0rowsaffected(0.01sec)
9. Records:0Duplicates:0Warnings:0
11. //hash重新分区
12. mysql>altertablehash_partaddpartitionpartitions4;
13. QueryOK,0rowsaffected(0.12sec)
14. Records:0Duplicates:0Warnings:0
16. //key重新分区
17. mysql>altertablekey_partaddpartitionpartitions4;
18. QueryOK,1rowaffected(0.06sec)//有数据也会被重新分配
19. Records:1Duplicates:0Warnings:0
21. //子分区添加新分区，虽然我没有指定子分区，但是系统会给子分区命名的
22. mysql>altertablesub1_partaddpartition(partitionp3valueslessthanMAXVALUE);
23. QueryOK,0rowsaffected(0.02sec)
24. Records:0Duplicates:0Warnings:0
26. mysql>showcreatetablesub1_partG;
27. ***************************1.row***************************
28. Table:sub1_part
29. CreateTable:CREATETABLE`sub1_part`(
30. `news_id`int(11)NOTNULLCOMMENT'新闻ID',
31. `content`varchar(1000)NOTNULLDEFAULT''COMMENT'新闻内容',
32. `u_id`varchar(25)NOTNULLDEFAULT''COMMENT'来源IP',
33. `create_time`dateNOTNULLDEFAULT'0000-00-00'COMMENT'时间'
34. )ENGINE=InnoDBDEFAULTCHARSET=utf8
35. !50100PARTITIONBYRANGE(YEAR(create_time))
36. SUBPARTITIONBYHASH(TO_DAYS(create_time))
37. (PARTITIONp0VALUESLESSTHAN(1990)
38. (SUBPARTITIONs0ENGINE=InnoDB,
39. SUBPARTITIONs1ENGINE=InnoDB,
40. SUBPARTITIONs2ENGINE=InnoDB),
41. PARTITIONp1VALUESLESSTHAN(2000)
42. (SUBPARTITIONs3ENGINE=InnoDB,
43. SUBPARTITIONs4ENGINE=InnoDB,
44. SUBPARTITIONgoodENGINE=InnoDB),
45. PARTITIONp2VALUESLESSTHAN(3000)
46. (SUBPARTITIONtank0ENGINE=InnoDB,
47. SUBPARTITIONtank1ENGINE=InnoDB,
48. SUBPARTITIONtank3ENGINE=InnoDB),
49. PARTITIONp3VALUESLESSTHANMAXVALUE
50. (SUBPARTITIONp3sp0ENGINE=InnoDB,//子分区的名子是自动生成的
51. SUBPARTITIONp3sp1ENGINE=InnoDB,
52. SUBPARTITIONp3sp2ENGINE=InnoDB))
53. 1rowinset(0.00sec)

3，重新分区

1. //range重新分区
2. mysql>ALTERTABLEuserREORGANIZEPARTITIONp0,p1,p2,p3,p4INTO(PARTITIONp0VALUESLESSTHANMAXVALUE);
3. QueryOK,11rowsaffected(0.08sec)
4. Records:11Duplicates:0Warnings:0
6. //list重新分区
7. mysql>ALTERTABLElist_partREORGANIZEPARTITIONp0,p1,p2,p3,p4INTO(PARTITIONp0VALUESin(1,2,3,4,5));
8. QueryOK,0rowsaffected(0.28sec)
9. Records:0Duplicates:0Warnings:0
11. //hash和key分区不能用REORGANIZE，官方网站说的很清楚
12. mysql>ALTERTABLEkey_partREORGANIZEPARTITIONCOALESCEPARTITION9;
13. ERROR1064(42000):YouhaveanerrorinyourSQLsyntax;checkthemanualthatcorrespondstoyourMySQLserverversionfortherightsyntaxtousenear'PARTITION9'atline1

四，分区优点

1，分区可以分在多个磁盘,存储更大一点.

2，根据查找条件,也就是where后面的条件,查找只查找相应的分区不用全部查找了.

3，进行大数据搜索时可以进行并行处理.

4，跨多个磁盘来分散数据查询,来获得更大的查询吞吐量.