深入解析MySQL分区（Partition）功能

[sql] view plain copy

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 );   

注意最后一行，考虑到可能的最大值

*创建未分区表

[sql] view plain copy

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

*通过存储过程灌入800万条测试数据

mysql> set sql_mode=‘‘; /* 如果创建存储过程失败，则先需设置此变量, bug? */

MySQL> delimiter //   /* 设定语句终结符为 //，因存储过程语句用;结束 */

[sql] view plain copy

1. mysql> CREATE PROCEDURE load_part_tab()  
2.        begin  
3.     declare v int default 0;  
4.     while v < 8000000  
5.     do  
6.         insert into part_tab  
7.         values (v,‘testing partitions‘,adddate(‘1995-01-01‘,(rand(v)*36520) mod 3652));  
8.          set v = v + 1;  
9.     end while;  
10.     end  
11.     //  
12. mysql> delimiter ;  
13. mysql> call load_part_tab();  

Query OK, 1 row affected (8 min 17.75 sec)

[sql] view plain copy

1. mysql> insert into no_part_tab select * from part_tab;  

Query OK, 8000000 rows affected (51.59 sec)
Records: 8000000 Duplicates: 0 Warnings: 0

* 测试SQL性能

 

[sql] view plain copy

1. mysql> select count(*) from part_tab where c3 > date ‘1995-01-01‘ and c3 < date ‘1995-12-31‘;  

+----------+
| count(*) |
+----------+
|   795181 |
+----------+

1 row in set (0.55 sec)

[sql] view plain copy

1. mysql> select count(*) from no_part_tab where c3 > date ‘1995-01-01‘ and c3 < date ‘1995-12-31‘;   

+----------+
| count(*) |
+----------+
|   795181 |
+----------+
1 row in set (4.69 sec)
结果表明分区表比未分区表的执行时间少90%。

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

[sql] view plain copy

1. mysql > explain select count(*) from no_part_tab where c3 > date ‘1995-01-01‘ and c3 < date ‘1995-12-31‘\G  

/* 结尾的\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 plain copy

1. mysql> explain select count(*) from part_tab where c3 > date ‘1995-01-01‘ and c3 < date ‘1995-12-31‘\G   

*************************** 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 plain copy

1. mysql> create index idx_of_c3 on no_part_tab (c3);  

Query OK, 8000000 rows affected (1 min 18.08 sec)
Records: 8000000 Duplicates: 0 Warnings: 0

[sql] view plain copy

1. mysql> create index idx_of_c3 on part_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 plain copy

1. mysql> select count(*) from no_part_tab where c3 > date ‘1995-01-01‘ and c3 < date ‘1995-12-31‘;   

+----------+
| count(*) |