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) 

= 初步結論 =

* 分區和未分區占用文件空間大致相同,數據和索引文件.

* 如果查詢語句中有未建立索引字段,分區時間遠遠優于未分區時間

* 如果查詢語句中字段建立了索引,分區和未分區的差別縮小,分區略優于未分區.