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. show variables like '%partition%'; 
2.  
3. +-------------------+-------+ 
4. | Variable_name     | Value | 
5. +-------------------+-------+ 
6. | have_partitioning | YES   | 
7.  
8.  
9. +-------------------+-------+ 

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

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

1. drop table if exists `netingcn_com`; 
2. create table `netingcn_com` ( 
3.   `id` int(11) unsigned not null auto_increment, 
4.   `day` int(11) not null default 0, 
5.   primary key (`id`, `day`) 
6. ) engine=innodb default charset=utf8 auto_increment=1; 
7.  
8. alter table `netingcn_com` partition by range(`day`) ( 
9.   partition p_2012 values less than (20130000), 
10.   partition p_2013 values less than (20140000) 
11. ); 
12.  
13. --或 
14.  
15. drop table if exists `netingcn_com`; 
16. create table `netingcn_com` ( 
17.   `id` int(11) unsigned not null auto_increment, 
18.   `day` int(11) not null default 0, 
19.   primary key (`id`, `day`) 
20. ) engine=innodb default charset=utf8 auto_increment=1  
21.  partition by range(`day`) ( 
22.   partition p_2012 values less than (20130000), 
23.   partition p_2013 values less than (20140000) 
24. ); 
25.  

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

1. explain partitions select * from netingcn_com where day = 20130412; 
2. +----+-------------+--------------+------------+-------+ 
3. | id | select_type | table        | partitions | type  | 
4. +----+-------------+--------------+------------+-------+ 
5. |  1 | SIMPLE      | netingcn_com | p_2013     | index | 
6. +----+-------------+--------------+------------+-------+ 

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

1. alter table netingcn_com add partition ( 
2.   partition p_2014 values less than (20150000) 
3. ); 
4.  
5. --删除分区 
6.  
7. alter table netingcn_com drop partition p_2012;  
8.  
9. --3、重新分区。注意：hash和key分区规则不能用REORGANIZE.来重新分区 
10.  
11. alter table netingcn_com reorganize partition p_2013,p_2014 into (partition p_2014 values less than (20150000)); 

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

1. mysql> CREATE TABLE part_tab ( c1 int default NULL, c2 varchar(30) default NULL, c3 date default NULL) engine=myisam  
2. PARTITION BY RANGE (year(c3)) (PARTITION p0 VALUES LESS THAN (1995), 
3. PARTITION p1 VALUES LESS THAN (1996) , PARTITION p2 VALUES LESS THAN (1997) , 
4. PARTITION p3 VALUES LESS THAN (1998) , PARTITION p4 VALUES LESS THAN (1999) , 
5. PARTITION p5 VALUES LESS THAN (2000) , PARTITION p6 VALUES LESS THAN (2001) , 
6. PARTITION p7 VALUES LESS THAN (2002) , PARTITION p8 VALUES LESS THAN (2003) , 
7. PARTITION p9 VALUES LESS THAN (2004) , PARTITION p10 VALUES LESS THAN (2010), 
8. PARTITION p11 VALUES LESS THAN MAXVALUE );  

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

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> set sql_mode=''; /* 如果创建存储过程失败，则先需设置此变量, bug? */ 
2.  
3. mysql> delimiter //   /* 设定语句终结符为 //，因存储过程语句用;结束 */ 
4. mysql> CREATE PROCEDURE load_part_tab() 
5.        begin 
6.     declare v int default 0; 
7.     while v < 8000000 
8.     do 
9.         insert into part_tab 
10.         values (v,'testing partitions',adddate('1995-01-01',(rand(v)*36520) mod 3652)); 
11.          set v = v + 1; 
12.     end while; 
13.     end 
14.     // 
15. mysql> delimiter ; 
16. mysql> call load_part_tab(); 
17. Query OK, 1 row affected (8 min 17.75 sec) 
18. mysql> insert into no_part_tab select * from part_tab; 
19. Query OK, 8000000 rows affected (51.59 sec) 
20. Records: 8000000 Duplicates: 0 Warnings: 0 

测试SQL性能,代码如下:

1. mysql> select count(*) from part_tab where c3 > date '1995-01-01' and c3 < date '1995-12-31';       
2. +----------+ 
3. | count(*) | 
4. +----------+ 
5. |   795181 | 
6. +----------+ 
7. 1 row in set (0.55 sec) 
8. mysql> select count(*) from no_part_tab where c3 > date '1995-01-01' and c3 < date '1995-12-31';  
9. +----------+ 
10. | count(*) | 
11. +----------+ 
12. |   795181 | 
13. +----------+ 
14. 1 row in set (4.69 sec) 

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

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

1. mysql > explain select count(*) from no_part_tab where c3 > date '1995-01-01' and c3 < 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: Using where 
14. 1 row in set (0.00 sec) 
15.  
16. mysql> explain select count(*) from part_tab where c3 > date '1995-01-01' and c3 < date '1995-12-31'G  
17. *************************** 1. row *************************** 
18.            id: 1 --Vevb.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: Using where 
28. 1 row in set (0.00 sec) 

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

1. * 试验创建索引后情况 
2. mysql> create index idx_of_c3 on no_part_tab (c3); 
3. Query OK, 8000000 rows affected (1 min 18.08 sec) 
4. Records: 8000000 Duplicates: 0 Warnings: 0 
5.  
6. mysql> create index idx_of_c3 on part_tab (c3); 
7. Query OK, 8000000 rows affected (1 min 19.19 sec) 
8. Records: 8000000 Duplicates: 0 Warnings: 0 
9. 创建索引后的数据库文件大小列表： 
10. 2008-05-24 09:23             8,608 no_part_tab.frm 
11. 2008-05-24 09:24       255,999,996 no_part_tab.MYD 
12. 2008-05-24 09:24        81,611,776 no_part_tab.MYI 
13. 2008-05-24 09:25                 0 part_tab#P#p0.MYD 
14. 2008-05-24 09:26             1,024 part_tab#P#p0.MYI 
15. 2008-05-24 09:26        25,550,656 part_tab#P#p1.MYD 
16. 2008-05-24 09:26         8,148,992 part_tab#P#p1.MYI 
17. 2008-05-24 09:26        25,620,192 part_tab#P#p10.MYD 
18. 2008-05-24 09:26         8,170,496 part_tab#P#p10.MYI 
19. 2008-05-24 09:25                 0 part_tab#P#p11.MYD 
20. 2008-05-24 09:26             1,024 part_tab#P#p11.MYI 
21. 2008-05-24 09:26        25,656,512 part_tab#P#p2.MYD 
22. 2008-05-24 09:26         8,181,760 part_tab#P#p2.MYI 
23. 2008-05-24 09:26        25,586,880 part_tab#P#p3.MYD 
24. 2008-05-24 09:26         8,160,256 part_tab#P#p3.MYI 
25. 2008-05-24 09:26        25,585,696 part_tab#P#p4.MYD 
26. 2008-05-24 09:26         8,159,232 part_tab#P#p4.MYI 
27. 2008-05-24 09:26        25,585,216 part_tab#P#p5.MYD 
28. 2008-05-24 09:26         8,159,232 part_tab#P#p5.MYI 
29. 2008-05-24 09:26        25,655,740 part_tab#P#p6.MYD 
30. 2008-05-24 09:26         8,181,760 part_tab#P#p6.MYI 
31. 2008-05-24 09:26        25,586,528 part_tab#P#p7.MYD 
32. 2008-05-24 09:26         8,160,256 part_tab#P#p7.MYI 
33. 2008-05-24 09:26        25,586,752 part_tab#P#p8.MYD 
34. 2008-05-24 09:26         8,160,256 part_tab#P#p8.MYI 
35. 2008-05-24 09:26        25,585,824 part_tab#P#p9.MYD 
36. 2008-05-24 09:26         8,159,232 part_tab#P#p9.MYI 
37. 2008-05-24 09:25             8,608 part_tab.frm 
38. 2008-05-24 09:25                68 part_tab.par 
39.  
40. * 再次测试SQL性能 
41. mysql> select count(*) from no_part_tab where c3 > date '1995-01-01' and c3 < date '1995-12-31';    +----------+ 
42. | count(*) | 
43. +----------+ 
44. |   795181 | 
45. +----------+ 
46. 1 row in set (2.42 sec)   /* 为原来4.69 sec 的51%*/   

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

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

= 初步结论 =

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

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

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