mysql分区

一、什么是分区？分区跟之前的手工分表有什么区别？

1、分区是mysql自动进行的，而手工分表为自己手工操作的。

2、分区可以很好地将数据分布到各个磁盘中。

3、分区只需要维护一个表，而分表则要维护好多表。

二、查看是否支持分区

mysql5.1及以上支持分区功能

查看：

mysql> show variables like "%partition%";
+-------------------+-------+
| Variable_name     | Value |
+-------------------+-------+
| have_partitioning | YES   |
+-------------------+-------+
1 row in set (0.00 sec)

三、分区的种类

所有的分区类型只能针对INT，不是INT的字段需要通过函数转化为int型，如to_days()转化等。

分区的参照字段只能包含在主键里。复合主键里也可以。这点不是很好，但mysql这样限制也没有办法，有兴趣的同学可以看看mysql源码改动的量大不大。

1、range

2、list

3、hash

4、key

5、子分区

range分区:就是按字段值的区间分区。

mysql> CREATE TABLE `user` (  
 ->   `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '用户ID',  
 ->   `name` varchar(50) NOT NULL DEFAULT '' COMMENT '名称',  
 ->   `sex` int(1) NOT NULL DEFAULT '0' COMMENT '0为男，1为女',  
 ->   PRIMARY KEY (`id`)  
 -> ) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=1  
 -> PARTITION BY RANGE (id) (  
 ->     PARTITION p0 VALUES LESS THAN (3),  
 ->     PARTITION p1 VALUES LESS THAN (6),  
 ->     PARTITION p2 VALUES LESS THAN (9),  
 ->     PARTITION p3 VALUES LESS THAN (12),  
 ->     PARTITION p4 VALUES LESS THAN MAXVALUE  
 -> );  
Query OK, 0 rows affected (0.13 sec)  
  
//插入数据  
mysql> INSERT INTO `test`.`user` (`name` ,`sex`)VALUES ('tank', '0')  
 -> ,('zhang',1),('ying',1),('张',1),('映',0),('test1',1),('tank2',1)  
 -> ,('tank1',1),('test2',1),('test3',1),('test4',1),('test5',1),('tank3',1)  
 -> ,('tank4',1),('tank5',1),('tank6',1),('tank7',1),('tank8',1),('tank9',1)  
 -> ,('tank10',1),('tank11',1),('tank12',1),('tank13',1),('tank21',1),('tank42',1);  
Query OK, 25 rows affected (0.05 sec)  
Records: 25  Duplicates: 0  Warnings: 0  
  
//到存放数据库表文件的地方看一下，my.cnf里面有配置，datadir后面就是  
[root@test]# ls |grep user |xargs du -sh  
4.0K    user#P#p0.MYD  
4.0K    user#P#p0.MYI  
4.0K    user#P#p1.MYD  
4.0K    user#P#p1.MYI  
4.0K    user#P#p2.MYD  
4.0K    user#P#p2.MYI  
4.0K    user#P#p3.MYD  
4.0K    user#P#p3.MYI  
4.0K    user#P#p4.MYD  
4.0K    user#P#p4.MYI  
12K    user.frm  
4.0K    user.par  

list分区：就是按字段的值列表分区

mysql> CREATE TABLE `list_part` ( 
 ->   `id` int(11) NOT NULL  COMMENT '用户ID', 
 ->   `province_id` int(2) NOT NULL DEFAULT 0 COMMENT '省', 
 ->   `name` varchar(50) NOT NULL DEFAULT '' COMMENT '名称', 
 ->   `sex` int(1) NOT NULL DEFAULT '0' COMMENT '0为男，1为女'  
 -> ) ENGINE=INNODB  DEFAULT CHARSET=utf8  
 -> PARTITION BY LIST (province_id) (  
 ->     PARTITION p0 VALUES IN (1,2,3,4,5,6,7,8),  
 ->     PARTITION p1 VALUES IN (9,10,11,12,16,21),  
 ->     PARTITION p2 VALUES IN (13,14,15,19),  
 ->     PARTITION p3 VALUES IN (17,18,20,22,23,24)  
 -> );  
Query OK, 0 rows affected (0.33 sec) 

hash分区：就是系统会把你指定的字段给HASH分区，该种方式比较平均

mysql> CREATE TABLE `hash_part` (  
 ->   `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '评论ID',  
 ->   `comment` varchar(1000) NOT NULL DEFAULT '' COMMENT '评论',  
 ->   `ip` varchar(25) NOT NULL DEFAULT '' COMMENT '来源IP',  
 ->   PRIMARY KEY (`id`)  
 -> ) ENGINE=INNODB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=1  
 -> PARTITION BY HASH(id)  
 -> PARTITIONS 3;  
Query OK, 0 rows affected (0.06 sec)  

partitions 3    为分为多少个区

 key分区：其实就是hash分区给合mysql函数进行hash分

mysql> CREATE TABLE `key_part` (  
 ->   `news_id` int(11) NOT NULL  COMMENT '新闻ID',  
 ->   `content` varchar(1000) NOT NULL DEFAULT '' COMMENT '新闻内容',  
 ->   `u_id` varchar(25) NOT NULL DEFAULT '' COMMENT '来源IP',  
 ->   `create_time` DATE NOT NULL DEFAULT '0000-00-00 00:00:00' COMMENT '时间'  
 -> ) ENGINE=INNODB  DEFAULT CHARSET=utf8  
 -> PARTITION BY LINEAR HASH(YEAR(create_time))  
 -> PARTITIONS 3;  
Query OK, 0 rows affected (0.07 sec)  

子分区：就是分区后再分区，注：hash和key分区不能再进行子分区

mysql> CREATE TABLE `sub_part` (  
 ->   `news_id` int(11) NOT NULL  COMMENT '新闻ID',  
 ->   `content` varchar(1000) NOT NULL DEFAULT '' COMMENT '新闻内容',  
 ->   `u_id`  int(11) NOT NULL DEFAULT 0s COMMENT '来源IP',  
 ->   `create_time` DATE NOT NULL DEFAULT '0000-00-00 00:00:00' COMMENT '时间'  
 -> ) ENGINE=INNODB  DEFAULT CHARSET=utf8  
 -> PARTITION BY RANGE(YEAR(create_time))  
 -> SUBPARTITION BY HASH(TO_DAYS(create_time))(  
 -> PARTITION p0 VALUES LESS THAN (1990)(SUBPARTITION s0,SUBPARTITION s1,SUBPARTITION s2),  
 -> PARTITION p1 VALUES LESS THAN (2000)(SUBPARTITION s3,SUBPARTITION s4,SUBPARTITION good),  
 -> PARTITION p2 VALUES LESS THAN MAXVALUE(SUBPARTITION tank0,SUBPARTITION tank1,SUBPARTITION tank3)  
 -> );  
Query OK, 0 rows affected (0.07 sec)  

四、分区管理

1、删除分区：

mysql> alter table user drop partition p4; 

2、新增：

mysql> alter table user add partition(partition p4 values less than MAXVALUE);  
Query OK, 0 rows affected (0.06 sec)  
Records: 0  Duplicates: 0  Warnings: 0  

3、重新分区：重新对以前的分区进行全新分区，如下面的5个区分区后就剩下1个分区

mysql> ALTER TABLE user REORGANIZE PARTITION p0,p1,p2,p3,p4 INTO (PARTITION p0 VALUES LESS THAN MAXVALUE);  
Query OK, 11 rows affected (0.08 sec)  
Records: 11  Duplicates: 0  Warnings: 0