mysql partition 分区功能使用详解

mysql partition自mysql 5.1.3起开始支持分区功能。mysql表中存储的记录和表对应的索引信息，最后都是以文件的方式存储在计算机的硬盘上的，有了分区功能我们就可以做比以前更多优化了。

目前分区规则有四种，分别是RANGE、LIST、HASH和KEY，另外通过DATA DIRECTORY和INDEX DIRECTORY选项可以把不同的分区数据文件分散到不同的磁盘上，从而进步一提高系统的IO吞吐量。因此按照业务逻辑设计好了分区，可以大大提高查询效率，而且删除数据可能也会很容易。但是分区也有一些限制：1、主键或者唯一索引必须包含分区字段；2、只能通过int类型的字段或者返回int类型的表达式来分区；3、单表最多只能有1024个分区。

默认mysql是开启了分区功能的,可以通过下述查询查看结果:

1. showvariableslike'%partition%';
3. +-------------------+-------+
4. |Variable_name|Value|
5. +-------------------+-------+
6. |have_partitioning|YES|
9. +-------------------+-------+

YES 表示开启下面也range规则为例介绍一下分区常用的命令。

1、创建分区,可以在创建表的同时创建,也可以在表创建后追加分区,代码如下:

1. droptableifexists`netingcn_com`;
2. createtable`netingcn_com`(
3. `id`int(11)unsignednotnullauto_increment,
4. `day`int(11)notnulldefault0,
5. primarykey(`id`,`day`)
6. )engine=innodbdefaultcharset=utf8auto_increment=1;
8. altertable`netingcn_com`partitionbyrange(`day`)(
9. partitionp_2012valueslessthan(20130000),
10. partitionp_2013valueslessthan(20140000)
11. );
13. --或
15. droptableifexists`netingcn_com`;
16. createtable`netingcn_com`(
17. `id`int(11)unsignednotnullauto_increment,
18. `day`int(11)notnulldefault0,
19. primarykey(`id`,`day`)
20. )engine=innodbdefaultcharset=utf8auto_increment=1
21. partitionbyrange(`day`)(
22. partitionp_2012valueslessthan(20130000),
23. partitionp_2013valueslessthan(20140000)
24. );

可以explain命令查看分区是否创建成功,代码如下:

1. explainpartitionsselect*fromnetingcn_comwhereday=20130412;
2. +----+-------------+--------------+------------+-------+
3. |id|select_type|table|partitions|type|
4. +----+-------------+--------------+------------+-------+
5. |1|SIMPLE|netingcn_com|p_2013|index|
6. +----+-------------+--------------+------------+-------+

2、增加或删除分区,注意,删除分区的同时,该分区的所有数据也会别删除,增加分区,代码如下:

1. altertablenetingcn_comaddpartition(
2. partitionp_2014valueslessthan(20150000)
3. );
5. --删除分区
7. altertablenetingcn_comdroppartitionp_2012;
9. --3、重新分区。注意：hash和key分区规则不能用REORGANIZE.来重新分区
11. altertablenetingcn_comreorganizepartitionp_2013,p_2014into(partitionp_2014valueslessthan(20150000));

[分区表和未分区表试验过程],*创建分区表,按日期的年份拆分,代码如下:

1. mysql>CREATETABLEpart_tab(c1intdefaultNULL,c2varchar(30)defaultNULL,c3datedefaultNULL)engine=myisam
2. PARTITIONBYRANGE(year(c3))(PARTITIONp0VALUESLESSTHAN(1995),
3. PARTITIONp1VALUESLESSTHAN(1996),PARTITIONp2VALUESLESSTHAN(1997),
4. PARTITIONp3VALUESLESSTHAN(1998),PARTITIONp4VALUESLESSTHAN(1999),
5. PARTITIONp5VALUESLESSTHAN(2000),PARTITIONp6VALUESLESSTHAN(2001),
6. PARTITIONp7VALUESLESSTHAN(2002),PARTITIONp8VALUESLESSTHAN(2003),
7. PARTITIONp9VALUESLESSTHAN(2004),PARTITIONp10VALUESLESSTHAN(2010),
8. PARTITIONp11VALUESLESSTHANMAXVALUE);

注意最后一行,考虑到可能的最大值,创建未分区表,代码如下:

mysql> create table no_part_tab (c1 int(11) default NULL,c2 varchar(30) default NULL,c3 date default NULL) engine=myisam;

通过存储过程灌入800万条测试数据,代码如下:

1. mysql>setsql_mode='';/*如果创建存储过程失败，则先需设置此变量,bug?*/
3. mysql>delimiter///*设定语句终结符为//，因存储过程语句用;结束*/
4. mysql>CREATEPROCEDUREload_part_tab()
5. begin
6. declarevintdefault0;
7. whilev<8000000
8. do
9. insertintopart_tab
10. values(v,'testingpartitions',adddate('1995-01-01',(rand(v)*36520)mod3652));
11. setv=v+1;
12. endwhile;
13. end
14. //
15. mysql>delimiter;
16. mysql>callload_part_tab();
17. QueryOK,1rowaffected(8min17.75sec)
18. mysql>insertintono_part_tabselect*frompart_tab;
19. QueryOK,8000000rowsaffected(51.59sec)
20. Records:8000000Duplicates:0Warnings:0

测试SQL性能,代码如下:

1. mysql>selectcount(*)frompart_tabwherec3>date'1995-01-01'andc3<date'1995-12-31';
2. +----------+
3. |count(*)|
4. +----------+
5. |795181|
6. +----------+
7. 1rowinset(0.55sec)
8. mysql>selectcount(*)fromno_part_tabwherec3>date'1995-01-01'andc3<date'1995-12-31';
9. +----------+
10. |count(*)|
11. +----------+
12. |795181|
13. +----------+
14. 1rowinset(4.69sec)

结果表明分区表比未分区表的执行时间少90%.

通过explain语句来分析执行情况:

1. mysql>explainselectcount(*)fromno_part_tabwherec3>date'1995-01-01'andc3<date'1995-12-31'G
2. /*结尾的G使得mysql的输出改为列模式*/
3. ***************************1.row***************************
4. id:1
5. select_type:SIMPLE
6. table:no_part_tab
7. type:ALL
8. possible_keys:NULL
9. key:NULL
10. key_len:NULL
11. ref:NULL
12. rows:8000000
13. Extra:Usingwhere
14. 1rowinset(0.00sec)
16. mysql>explainselectcount(*)frompart_tabwherec3>date'1995-01-01'andc3<date'1995-12-31'G
17. ***************************1.row***************************
18. id:1--phpfensi.com
19. select_type:SIMPLE
20. table:part_tab
21. type:ALL
22. possible_keys:NULL
23. key:NULL
24. key_len:NULL
25. ref:NULL
26. rows:798458
27. Extra:Usingwhere
28. 1rowinset(0.00sec)

explain语句显示了SQL查询要处理的记录数目,代码如下:

1. *试验创建索引后情况
2. mysql>createindexidx_of_c3onno_part_tab(c3);
3. QueryOK,8000000rowsaffected(1min18.08sec)
4. Records:8000000Duplicates:0Warnings:0
6. mysql>createindexidx_of_c3onpart_tab(c3);
7. QueryOK,8000000rowsaffected(1min19.19sec)
8. Records:8000000Duplicates:0Warnings:0
9. 创建索引后的数据库文件大小列表：
10. 2008-05-2409:238,608no_part_tab.frm
11. 2008-05-2409:24255,999,996no_part_tab.MYD
12. 2008-05-2409:2481,611,776no_part_tab.MYI
13. 2008-05-2409:250part_tab#P#p0.MYD
14. 2008-05-2409:261,024part_tab#P#p0.MYI
15. 2008-05-2409:2625,550,656part_tab#P#p1.MYD
16. 2008-05-2409:268,148,992part_tab#P#p1.MYI
17. 2008-05-2409:2625,620,192part_tab#P#p10.MYD
18. 2008-05-2409:268,170,496part_tab#P#p10.MYI
19. 2008-05-2409:250part_tab#P#p11.MYD
20. 2008-05-2409:261,024part_tab#P#p11.MYI
21. 2008-05-2409:2625,656,512part_tab#P#p2.MYD
22. 2008-05-2409:268,181,760part_tab#P#p2.MYI
23. 2008-05-2409:2625,586,880part_tab#P#p3.MYD
24. 2008-05-2409:268,160,256part_tab#P#p3.MYI
25. 2008-05-2409:2625,585,696part_tab#P#p4.MYD
26. 2008-05-2409:268,159,232part_tab#P#p4.MYI
27. 2008-05-2409:2625,585,216part_tab#P#p5.MYD
28. 2008-05-2409:268,159,232part_tab#P#p5.MYI
29. 2008-05-2409:2625,655,740part_tab#P#p6.MYD
30. 2008-05-2409:268,181,760part_tab#P#p6.MYI
31. 2008-05-2409:2625,586,528part_tab#P#p7.MYD
32. 2008-05-2409:268,160,256part_tab#P#p7.MYI
33. 2008-05-2409:2625,586,752part_tab#P#p8.MYD
34. 2008-05-2409:268,160,256part_tab#P#p8.MYI
35. 2008-05-2409:2625,585,824part_tab#P#p9.MYD
36. 2008-05-2409:268,159,232part_tab#P#p9.MYI
37. 2008-05-2409:258,608part_tab.frm
38. 2008-05-2409:2568part_tab.par
40. *再次测试SQL性能
41. mysql>selectcount(*)fromno_part_tabwherec3>date'1995-01-01'andc3<date'1995-12-31';+----------+
42. |count(*)|
43. +----------+
44. |795181|
45. +----------+
46. 1rowinset(2.42sec)/*为原来4.69sec的51%*/

重启mysql(net stop mysql, net start mysql)后,查询时间降为0.89 sec,几乎与分区表相同,代码如下:

1. mysql>selectcount(*)frompart_tabwherec3>date'1995-01-01'andc3<date'1995-12-31';
2. +----------+
3. |count(*)|
4. +----------+
5. |795181|
6. +----------+
7. 1rowinset(0.86sec)
9. *更进一步的试验
10. **增加日期范围
11. mysql>selectcount(*)fromno_part_tabwherec3>date'1995-01-01'andc3<date'1997-12-31';
12. +----------+
13. |count(*)|
14. +----------+
15. |2396524|
16. +----------+
17. 1rowinset(5.42sec)
19. mysql>selectcount(*)frompart_tabwherec3>date'1995-01-01'andc3<date'1997-12-31';
20. +----------+
21. |count(*)|
22. +----------+
23. |2396524|
24. +----------+
25. 1rowinset(2.63sec)
26. **增加未索引字段查询
27. mysql>selectcount(*)frompart_tabwherec3>date'1995-01-01'andc3<date
28. '1996-12-31'andc2='hello';
29. +----------+
30. |count(*)|
31. +----------+
32. |0|
33. +----------+
34. 1rowinset(0.75sec)
36. mysql>selectcount(*)fromno_part_tabwherec3>date'1995-01-01'andc3<da
37. te'1996-12-31'andc2='hello';
38. +----------+
39. |count(*)|
40. +----------+
41. |0|
42. +----------+
43. 1rowinset(11.52sec)

= 初步结论 =

* 分区和未分区占用文件空间大致相同,数据和索引文件.

* 如果查询语句中有未建立索引字段,分区时间远远优于未分区时间

* 如果查询语句中字段建立了索引,分区和未分区的差别缩小,分区略优于未分区.

上一页 1 2 下一页