当前位置：首页 > Mysql教程 > 列表

mysql partition 分区功能使用详解

发布:smiling　来源: PHP粉丝网 　添加日期：2014-09-29 17:20:26　浏览: 评论：0　

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

= 初步结论 =

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

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

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

上一页 1 2 下一页

Tags: partition mysql分区功能

分享到：

相关文章

推荐文章

热门文章

最新评论文章