MySQL水平分区表实际操作总结

转载自：http://shuailan.iteye.com/blog/1056247

本文总结个这段时间研究MySQL水平分区表总结,列举分区表的相关操作.

在网上看了很多文章,都太过于概念,注意集中在介绍分区表的优点,而不注重时间操作,跟大学教授论文似的,唯一由于的一片文章和大家分享一下吧http://fanqiang.chinaunix.net/db/mysql/2006-05-08/4135.shtml

 

MySQL分区表操作代码 (本案例按月分区):

       1. 创建分区表

Sql代码  

1. CREATE TABLE `表名` (  
2.     `EQUIPMENTID` char(17) NOT NULL,  
3.     `ATTRIBUTEID` char(4) NOT NULL,  
4.     `VALUE` varchar(20) NOT NULL,  
5.     `COLLECTTIME` datetime NOT NULL  
6. )   
7. ENGINE=InnoDB (适用大部分引擎,可根据需要调整)  
8. DEFAULT CHARSET=latin1(编码可根据需要修改)   
9. PARTITION BY RANGE (to_days(COLLECTTIME))   
10. (PARTITION pmin VALUES LESS THAN (to_days('2010-01-01')),  
11. PARTITION p201001 VALUES LESS THAN (to_days('2010-02-01')) ,   
12. PARTITION p201002 VALUES LESS THAN (to_days('2010-03-01')) ,  
13. PARTITION p201003 VALUES LESS THAN (to_days('2010-04-01')) ,   
14. PARTITION p201004 VALUES LESS THAN (to_days('2010-05-01')) ,  
15. PARTITION p201005 VALUES LESS THAN (to_days('2010-06-01')) ,   
16. PARTITION p201006 VALUES LESS THAN (to_days('2010-07-01')) ,  
17. PARTITION p201007 VALUES LESS THAN (to_days('2010-08-01')) ,   
18. PARTITION p201008 VALUES LESS THAN (to_days('2010-09-01')) ,  
19. PARTITION p201009 VALUES LESS THAN (to_days('2010-10-01')) ,   
20. PARTITION p201010 VALUES LESS THAN (to_days('2010-11-01')),  
21. PARTITION p201011 VALUES LESS THAN (to_days('2010-12-01')),  
22. PARTITION p201012 VALUES LESS THAN (to_days('2011-01-01')),  
23. PARTITION p201101 VALUES LESS THAN (to_days('2011-02-01')),  
24. PARTITION p201102 VALUES LESS THAN (to_days('2011-03-01')),  
25. PARTITION p201103 VALUES LESS THAN (to_days('2011-04-01')),  
26. PARTITION p201104 VALUES LESS THAN (to_days('2011-05-01')),  
27. PARTITION p201105 VALUES LESS THAN (to_days('2011-06-01')),  
28. PARTITION p201106 VALUES LESS THAN (to_days('2011-07-01')),  
29. PARTITION p201107 VALUES LESS THAN (to_days('2011-08-01')),  
30. PARTITION p201108 VALUES LESS THAN (to_days('2011-09-01')),  
31. PARTITION p201109 VALUES LESS THAN (to_days('2011-10-01')),  
32. PARTITION p201110 VALUES LESS THAN (to_days('2011-11-01')),  
33. PARTITION p201111 VALUES LESS THAN (to_days('2011-12-01')),  
34. PARTITION p201112 VALUES LESS THAN (to_days('2012-01-01')),  
35. PARTITION pmax VALUES LESS THAN MAXVALUE );  

  

        2. 为现有表创建分区

Sql代码  

1. alter table 表名  
2. PARTITION BY RANGE (to_days(COLLECTTIME))   
3. (PARTITION pmin VALUES LESS THAN (to_days('2010-01-01')),  
4. PARTITION p201001 VALUES LESS THAN (to_days('2010-02-01')) ,   
5. PARTITION p201002 VALUES LESS THAN (to_days('2010-03-01')) ,  
6. PARTITION p201003 VALUES LESS THAN (to_days('2010-04-01')) ,   
7. PARTITION p201004 VALUES LESS THAN (to_days('2010-05-01')) ,  
8. PARTITION p201005 VALUES LESS THAN (to_days('2010-06-01')) ,   
9. PARTITION p201006 VALUES LESS THAN (to_days('2010-07-01')) ,  
10. PARTITION p201007 VALUES LESS THAN (to_days('2010-08-01')) ,   
11. PARTITION p201008 VALUES LESS THAN (to_days('2010-09-01')) ,  
12. PARTITION p201009 VALUES LESS THAN (to_days('2010-10-01')) ,   
13. PARTITION p201010 VALUES LESS THAN (to_days('2010-11-01')),  
14. PARTITION p201011 VALUES LESS THAN (to_days('2010-12-01')),  
15. PARTITION p201012 VALUES LESS THAN (to_days('2011-01-01')),  
16. PARTITION p201101 VALUES LESS THAN (to_days('2011-02-01')),  
17. PARTITION p201102 VALUES LESS THAN (to_days('2011-03-01')),  
18. PARTITION p201103 VALUES LESS THAN (to_days('2011-04-01')),  
19. PARTITION p201104 VALUES LESS THAN (to_days('2011-05-01')),  
20. PARTITION p201105 VALUES LESS THAN (to_days('2011-06-01')),  
21. PARTITION p201106 VALUES LESS THAN (to_days('2011-07-01')),  
22. PARTITION p201107 VALUES LESS THAN (to_days('2011-08-01')),  
23. PARTITION p201108 VALUES LESS THAN (to_days('2011-09-01')),  
24. PARTITION p201109 VALUES LESS THAN (to_days('2011-10-01')),  
25. PARTITION p201110 VALUES LESS THAN (to_days('2011-11-01')),  
26. PARTITION p201111 VALUES LESS THAN (to_days('2011-12-01')),  
27. PARTITION p201112 VALUES LESS THAN (to_days('2012-01-01')),  
28. PARTITION pmax VALUES LESS THAN MAXVALUE );  

如果表中已有数据,分区时会自动进行分区存储,不必担心数据丢失或者手动分类数据.  

 

3. 删除表中的指定分区

Sql代码  

1. ALTER TABLE 表名 DROP PARTITION 分区名;  

   删除分区会导致分区数据丢失,建议先备份.

4. 追加表分区

Sql代码  

1. ALTER TABLE 表名 DROP PARTITION pmax;  
2. ALTER TABLE 表名  
3. ADD PARTITION (  
4. PARTITION p201201 VALUES LESS THAN (to_days('2012-2-1')),  
5. PARTITION pmax VALUES LESS THAN MAXVALUE);  

需要先删除MAXVALUE分区后增加分区后再重建MAXVALUE分区,删除前需要先备份MAXVALUE分区数据.

 5. 查看标分区信息

Sql代码  

1. SELECT   
2.         partition_name part,    
3.         partition_expression expr,    
4.         partition_description descr,    
5.         table_rows    
6. FROM   
7.         INFORMATION_SCHEMA.partitions    
8. WHERE   
9.         TABLE_SCHEMA = schema()   
10.         AND TABLE_NAME='表名';  

  

6. 查看查询语句涉及分区信息

Sql代码  

1. explain partitions  
2. select … from 表名 where …;  

该操作只查看查询语句相关的分区信息,不会返回查询结果.

 

        以上是我对MySQL的初体验总结,没啥心得体会,只有一点点成就感,希望和大家分享.

另外分区表尚存在问题:

1,是否可将分区表设置在不同硬盘,innodb可行?

2,是否可根据多条件进行水平分区,类似group by 列1,列2...

3,是否能将分区设置成不同引擎,例如当前使用中的分区为innodb,老的分区使用MyISAM.

7.分区的合并

下面的SQL，将p201001 - p201009 合并为3个分区p2010Q1 - p2010Q3

mysql> ALTER TABLE sale_data

    ->   REORGANIZE PARTITION p201001,p201002,p201003,

    ->                        p201004,p201005,p201006,

    ->                        p201007,p201008,p201009 INTO

    -> (

    ->   PARTITION p2010Q1 VALUES LESS THAN (201004),

    ->   PARTITION p2010Q2 VALUES LESS THAN (201007),

    ->   PARTITION p2010Q3 VALUES LESS THAN (201010)

    -> );

Query OK, 0 rows affected (1.14 sec)

Records: 0  Duplicates: 0  Warnings: 0